レポートの解答例

Sub report()

Dim X As Range, Y As Range, XT As Range, XTX As Range, XTY As Range, XTYI As Range, B As Range

Cells(1, 8) = "X"
For i = 1 To 20
　Cells(i + 1, 8) = 1
　For j = 1 To 3
　　　Cells(i + 1, j + 8) = Cells(i + 1, j + 3)
　Next j
Next i

Cells(1, 13) = "Y"
For i = 1 To 20
　Cells(i + 1, 13) = Cells(i + 1, 3)
Next i

Set X = Range(Cells(2, 8), Cells(21, 11))
Set Y = Range(Cells(2, 13), Cells(21, 13))
Set XT = Range(Cells(24, 1), Cells(27, 20))
Set XTX = Range(Cells(30, 1), Cells(33, 4))
Set XTY = Range(Cells(36, 1), Cells(39, 1))
Set XTYI = Range(Cells(36, 3), Cells(39, 6))
Set B = Range(Cells(36, 8), Cells(39, 8))

Cells(23, 1) = "XT"
XT = Application.Transpose(X)

Cells(29, 1) = "XTX"
XTX = Application.MMult(XT, X)

Cells(35, 1) = "XTY"
XTY = Application.MMult(XT, Y)

Cells(35, 3) = "XTYI"
XTYI = Application.MInverse(XTX)

Cells(35, 8) = "B"
B = Application.MMult(XTYI, XTY)

End Sub