静粛に、只今統計勉強中

仕事でデータ分析をすることになったバリバリ文系アラフィフのおっさんが、独学で統計の勉強を始めました。

Excel VBAで分析ツールの重回帰分析に信頼区間と予測区間をちょい足ししてみた

以前の記事で、エクセルExcel分析ツールの回帰分析では信頼区間と予測区間の推定がされないことを確認するとともに、出力された標準化残差の値が間違っていることを発見したわけですが、

  • エクセルExcelが推定しないならVBAでしちゃえばいいじゃない。
  • エクセルExcelが間違ったならVBAで直しちゃえばいいじゃない。

が、このブログの基本方針だったりするので、今回もそうするのです。

 

ソースコード

'分析ツールの重回帰分析に信頼区間と予測区間を加える
Sub Multiple_Regression()

    Application.ScreenUpdating = False

    Dim c, n, Avg, i, j, k, e, S, D2, F, CI, PI
    Dim y As Range, x As Range, Rng As Range, x_ As Range
    
    c = Selection.Columns.Count - 1             '説明変数の数
    n = Selection.Rows.Count - 1                '観測数
    Set y = Selection.Resize(, 1).Offset(, c)   '目的変数
    Set x = Selection.Resize(, c)               '説明変数

    '分析ツール「回帰分析」実行
    Application.Run "ATPVBAEN.XLAM!Regress", y, x, False, True, 99, "", _
        True, True, True, True, , True

    '正規確率グラフの元データを移動
    Set Rng = Range("A1048576").End(xlUp)
    Range(Cells.Find(What:="確率"), Cells(Range("G1048576").End(xlUp).Row, 7)).Cut _
        Destination:=Rng.Offset(3)

    '信頼区間と予測区間の見出しを入力&書式設定
    With Range("E24:H24").Offset(c - 1)
        .Value = Array("信頼区間" & vbLf & "下限95%", "信頼区間" & vbLf & "上限95%", _
            "予測区間" & vbLf & "下限95%", "予測区間" & vbLf & "上限95%")
        .HorizontalAlignment = xlCenter
        With .Borders(xlEdgeTop)
            .LineStyle = True
            .Weight = xlMedium
        End With
        .Borders(xlEdgeBottom).LineStyle = True
        With .Offset(Rng.Row - 24 - c + 1).Borders(xlEdgeBottom)
            .LineStyle = True
            .Weight = xlMedium
        End With
    End With
    Range("A:I").EntireColumn.AutoFit

    '標準化残差を修正(分析ツールの実行結果は間違いなので)
    Set Rng = Cells.Find(What:="標準残差")
    Rng.Value = "標準化残差"
    Rng.Offset(, -3).Value = "観測(ID)"
    Set Rng = Rng.Offset(1)
    Do Until Rng.Value = ""
        Rng.Value = Rng.Offset(, -1) / Range("B7")
        If Abs(Rng.Value) > 3 Then Rng.Font.Color = vbRed
        Set Rng = Rng.Offset(1)
    Loop
    
    'マハラノビスの汎距離を計算する
    ReDim e(1 To n, 1 To c)
    ReDim S(1 To c, 1 To c)
    ReDim D2(1 To n)
    With Application.WorksheetFunction
        For j = 1 To c
            Set x_ = x.Resize(, 1).Offset(, j - 1)
            Avg = .Average(x_)
            For i = 1 To n
                e(i, j) = x_(i + 1, 1) - Avg
            Next
        Next
        For i = 1 To c
            For j = 1 To c
                For k = 1 To n
                    S(i, j) = S(i, j) + e(k, i) * e(k, j)
                Next
            Next
        Next
        Range("H11").Value = "行列式"
        Range("H12").Value = .MDeterm(S)
        S = .MInverse(S)
        For i = 1 To n
            For j = 1 To c
                For k = 1 To c
                    D2(i) = D2(i) + e(i, j) * e(i, k) * S(j, k) * (n - 1)
                Next
            Next
        Next
        
        F = .F_Inv_RT(0.05, 1, n - c - 1) '区間推定用のF値
    End With

    '信頼区間と予測区間を計算する
    ReDim CI(1 To n)
    ReDim PI(1 To n)
    For i = 1 To n
        '信頼区間の幅
        CI(i) = Sqr(F * (1 / n + D2(i) / (n - 1)) * Range("C13").Value / (n - c - 1))
        '予測区間の幅
        PI(i) = Sqr(F * (1 + 1 / n + D2(i) / (n - 1)) * Range("C13").Value / (n - c - 1))
        With Range("E25").Offset(c - 1)
            .Offset(i - 1).Value = .Offset(i - 1, -3).Value - CI(i)
            .Offset(i - 1, 1).Value = .Offset(i - 1, -3).Value + CI(i)
            .Offset(i - 1, 2).Value = .Offset(i - 1, -3).Value - PI(i)
            .Offset(i - 1, 3).Value = .Offset(i - 1, -3).Value + PI(i)
        End With
    Next

    'グラフの位置調整
    For i = 1 To c * 2 + 1
        With ActiveSheet.ChartObjects(i).Chart
            j = 10 * (i - 1)
            If i >= 4 Then j = j - 1
            .Parent.Top = Range("K2").Offset(j).Top   '位置調整(上端)
            .Parent.Left = Range("K2").Offset(j).Left '位置調整(左端)
        End With
    Next
    
    Range("A1").Select
    
    ActiveWindow.DisplayGridlines = False       'グリッド除去
    Application.ScreenUpdating = True

End Sub

 

コードの解説

大方は、以前作ったマクロ

の流用だったりするわけですが、重回帰分析の場合、区間推定にマハラノビスの汎距離が必要で、これがサッパリ分からなかったりします。

こんなヤツらしいんですが、
f:id:cyclo-commuter:20180208141517p:plain
やっぱり分からないですね。

計算方法は、相関行列を使ったり分散共分散行列を使ったりと幾つかあるみたいですけど、『マンガでわかる統計学 回帰分析編』で解説されていたのは説明変数の偏差平方和・積和行列を使ったやり方でしたので、私もそうさせていただきました。
ついでに、この行列を使って、多重共線性チェック用の行列式も計算しています。*1

このマハラノビスの汎距離を使って区間幅を計算するのですが、その際に使うF値は第1自由度「1」固定で求めるみたいです。なぜでしょうか? 理由がわかりません。

って、解説になってませんね・・・

 

使い方と実行結果

使い方は、毎度おなじみ、ソースコード全行をVBEで標準モジュールにコピペしたら、データ範囲を選択して、[開発]→[マクロ]→[Multiple_Regression]を選択→【実行】をクリックするだけ、です。
f:id:cyclo-commuter:20180208143533j:plain

使用上の注意が3つあります。

  1. 必ず表にラベル行を作っておいてください。
  2. 必ず説明変数をに、目的変数を一番に配置してください。
  3. 必ずラベル行からセル範囲を選択してください。

クリック後1~数秒で以下のように結果が出力されます。 

f:id:cyclo-commuter:20180208152948j:plain
f:id:cyclo-commuter:20180208153003j:plain

信頼区間と予測区間の95%推定値がちょい足しされました!

表やグラフの読み方については、以下の記事を参考にしてください。
『マンガでわかる統計学 回帰分析編』に従ってExcel分析ツールの重回帰分析の結果を読んでみた1 - 静粛に、只今統計勉強中
『マンガでわかる統計学 回帰分析編』に従ってExcel分析ツールの重回帰分析の結果を読んでみた2 - 静粛に、只今統計勉強中
 Excel分析ツールの重回帰分析のグラフも読んでみた - 静粛に、只今統計勉強中
 

*1:行列式の値が0に近い場合は多重共線性があります。