Range Object - Excel 97

共分散行列と回帰分析

Function Initialize50() As Range
' 行列計算を行う。
' 統計計算用サンプルデータ作成。
' y=3x-3.5 のまわりに分布するランダムなデータ群を作る。
    Dim rRet As Range
    Dim x As Single, y As Single
    Dim I As Long, data(19, 1) As Single
    Set rRet = Sheet2.Range("A2:B21")
    rRet.EntireRow.Clear
    rRet.Rows(0).EntireRow.Clear
    Randomize
    For I = 0 To 19
        x = 10 * Rnd() + 3
        y = 3 * x - 4 + Rnd()
        data(I, 0) = x
        data(I, 1) = y
    Next I
    rRet.Rows(0) = Array("x", "y")
    rRet = data
    Set Initialize50 = rRet
End Function

Sub Range51()
' 行列計算を行う。
' 共分散行列と回帰分析。
    Dim A As Range, B As Range, C As Range, D As Range
    Dim E As Range, F As Range
    Dim x As Range, y As Range, z As Range
    Dim P(3) As Double, Q(1, 1) As Double, R(2) As Double
    ' 初期化
    Set A = Initialize50
    Set B = Sheet2.Range("E2:H2")
    Set C = Sheet2.Range("E5:F6")
    Set D = Sheet2.Range("E9:F10")
    Set E = Sheet2.Range("E13:G13")
    Set F = Sheet2.Range("E16:F16")
    ' データ領域の特定
    Set x = A.Columns(1)
    Set y = A.Columns(2)
    Set z = A.Columns(3)
    ' 平均等の計算
    P(0) = Application.Count(x)
    P(1) = Application.Average(x)
    P(2) = Application.Average(y)
    P(3) = Application.Correl(x, y)
    B = P
    B.Rows(0) = Array("データ数", "X平均", "Y平均", "相関係数")
    ' 共分散行列の計算
    Q(0, 0) = Application.Var(x)
    Q(1, 1) = Application.Var(y)
    Q(0, 1) = P(0) / (P(0) - 1) * Application.Covar(x, y)
    Q(1, 0) = Q(0, 1)
    C = Q
    C.Rows(0).Cells(1) = "共分散行列"
    ' 回帰係数の計算
    R(0) = Q(0, 1) / Q(0, 0)
    R(1) = P(2) - R(0) * P(1)
    R(2) = R(0) ^ 2 * Q(0, 0) / Q(1, 1)
    E = R
    E.Rows(0) = Array("回帰係数A", "回帰係数B", "決定係数")
    z.FormulaR1C1 = "=RC[-2]*(" & R(0) & ")+(" & R(1) & ")"
    z.Rows(0) = "y.理論値"
    ' 相関行列の計算
    Q(0, 1) = Q(0, 1) / Sqr(Q(0, 0)) / Sqr(Q(1, 1))
    Q(1, 0) = Q(0, 1)
    Q(0, 0) = 1
    Q(1, 1) = 1
    D = Q
    D.Rows(0).Cells(1) = "相関行列"
    
    ' Excel 機能による回帰係数の計算(別解)
    F = Application.LinEst(y, x)
    F.Rows(0).Cells(1) = "別解"
End Sub

サンプルデータに関する説明

基本的な統計値の計算

合計     = Application.Sum(x)
データ数 = Application.Count(x)
平均     = Application.Average(x)
二乗和   = Application.SumSq(x)
内積     = Application.SumProduct(x, y)
相関係数 = Application.Correl(x, y)

共分散行列

標本の分散     = Application.Var(x)
母集団の分散   = Application.VarP(x)
母集団の共分散 = Application.CoVar(x, y)

相関行列

回帰分析

係数 A      = Vxy / Vx      = [xとyの共分散] ÷ [xの分散]
係数 B      = Sy - A * Sx   = [yの平均] − A × [xの平均]
決定係数 R2 = A^2 * Vx / Vy = [理論値yの分散] ÷ [実測値yの分散]

別解

( 係数 A , 係数 B ) = Application.LinEst(y, x)

共分散行列と重回帰分析

Function Initialize50b() As Range
' 行列計算を行う。
' 統計計算用サンプルデータ作成。
' z=2x+3y-4 のまわりに分布するランダムなデータ群を作る。
    Dim rRet As Range
    Dim x As Single, y As Single, z As Single
    Dim I As Long, data(19, 2) As Single
    Set rRet = Sheet2.Range("A2:C21")
    rRet.EntireRow.Clear
    rRet.Rows(0).EntireRow.Clear
    Randomize
    For I = 0 To 19
        x = 10 * Rnd() + 3
        y = 7 * Rnd() - 5
        z = 2 * x + 3 * y - 4 + Rnd() - 0.5
        data(I, 0) = x
        data(I, 1) = y
        data(I, 2) = z
    Next I
    rRet.Rows(0) = Array("x", "y", "z")
    rRet = data
    Set Initialize50b = rRet
End Function

Sub Range52()
' 行列計算を行う。
' 共分散行列と重回帰分析。
    Dim A As Range, B As Range, C As Range, D As Range
    Dim E As Range, F As Range, G As Range, H As Range
    Dim I As Range, J As Range, K As Range
    Dim x As Range, y As Range, z As Range, z2 As Range
    Dim P(6) As Double, Q(2, 2) As Double
    Dim nQ As Double
    ' 初期化
    Set A = Initialize50b
    Set B = Sheet2.Range("F2:L2")
    Set C = Sheet2.Range("F5:H7")
    Set D = Sheet2.Range("J5:L7")
    Set E = Sheet2.Range("F10:J10")
    Set F = Sheet2.Range("F13:G14")
    Set G = Sheet2.Range("H13:I14")
    Set H = Sheet2.Range("J13:J14")
    Set I = Sheet2.Range("K13:K14")
    Set J = Sheet2.Range("L13:L14")
    Set K = Sheet2.Range("F17:H17")
    ' データ領域の特定
    Set x = A.Columns(1)
    Set y = A.Columns(2)
    Set z = A.Columns(3)
    Set z2 = A.Columns(4)
    ' 平均等の計算
    P(0) = Application.Count(x)
    P(1) = Application.Average(x)
    P(2) = Application.Average(y)
    P(3) = Application.Average(z)
    P(4) = Application.Correl(x, y)
    P(5) = Application.Correl(y, z)
    P(6) = Application.Correl(z, x)
    B = P
    B.Rows(0) = Array("データ数", "X平均", "Y平均", "Z平均", _
        "相関係数xy", "相関係数yz", "相関係数zx")
    ' 共分散行列の計算
    nQ = P(0) / (P(0) - 1)
    Q(0, 0) = Application.Var(x)
    Q(1, 1) = Application.Var(y)
    Q(2, 2) = Application.Var(z)
    Q(0, 1) = nQ * Application.Covar(x, y)
    Q(1, 2) = nQ * Application.Covar(y, z)
    Q(0, 2) = nQ * Application.Covar(z, x)
    Q(1, 0) = Q(0, 1)
    Q(2, 1) = Q(1, 2)
    Q(2, 0) = Q(0, 2)
    C = Q
    C.Rows(0).Cells(1) = "共分散行列"
    ' 回帰係数の計算
    F = Q
    F.Rows(0).Cells(1) = "説明変数の共分散行列"
    G = Application.MInverse(F)
    G.Rows(0).Cells(1) = "その逆行列"
    H(1) = Q(0, 2)
    H(2) = Q(1, 2)
    H.Rows(0).Cells(1) = "目的変数の共分散"
    I = Application.MMult(G, H)
    I.Rows(0).Cells(1) = "係数の解"
    E = Application.Transpose(I)
    J = Application.MMult(F, I)
    J.Rows(0).Cells(1) = "FG*K"
    E(3) = P(3) - I(1) * P(1) - I(2) * P(2)
    E(4) = Application.MMult(E.Range("A1:B1"), J)
    E(5) = E(4) / Q(2, 2)
    E.Rows(0) = Array("回帰係数A", "回帰係数B", "回帰係数C", _
        "理論値の分散", "決定係数")
    z2.FormulaR1C1 = "=RC[-3]*(" & E(1) & ")+RC[-2]*(" & _
        E(2) & ")+(" & E(3) & ")"
    z2.Rows(0) = "z.理論値"
    ' 相関行列の計算
    Q(0, 1) = Q(0, 1) / Sqr(Q(0, 0)) / Sqr(Q(1, 1))
    Q(1, 2) = Q(1, 2) / Sqr(Q(1, 1)) / Sqr(Q(2, 2))
    Q(0, 2) = Q(0, 2) / Sqr(Q(0, 0)) / Sqr(Q(2, 2))
    Q(1, 0) = Q(0, 1)
    Q(2, 1) = Q(1, 2)
    Q(2, 0) = Q(0, 2)
    Q(0, 0) = 1
    Q(1, 1) = 1
    Q(2, 2) = 1
    D = Q
    D.Rows(0).Cells(1) = "相関行列"

    ' Excel 機能による回帰係数の計算(別解)
    K = Application.LinEst(z, Union(x, y))
    K.Rows(0).Cells(1) = "別解"
End Sub

サンプルデータに関する説明

統計値と共分散行列

重回帰分析

[説明変数の共分散行列]・[回帰係数を成分とする行列] 
   = [目的変数と各説明変数の共分散を成分とする行列]
すなわち
[xとyの共分散行列]・[係数A B の行列] = [zとxの共分散, zとyの共分散, の行列]
これは A B についての連立方程式です。
逆行列を利用して、解 A B を求められます。
A B が求まれば、平均を利用して、次のように C を求めます。
係数 C = Sz - A * Sx - B * Sy 
   = [zの平均] − A × [xの平均] − B × [yの平均]
最後に、理論値の分散を計算して、決定係数を求めます。
[理論値zの分散] = [係数A B の行列]T・[xとyの共分散行列]・[係数A B の行列]
決定係数 R2 = [理論値zの分散] ÷ [実測値zの分散]

別解

( 係数 B , 係数 A , 係数 C ) = Application.LinEst(z, xy )