Excel VBAで分析ツールの重回帰分析に信頼区間と予測区間をちょい足ししてみた
以前の記事で、エクセルExcel分析ツールの回帰分析では信頼区間と予測区間の推定がされないことを確認するとともに、出力された標準化残差の値が間違っていることを発見したわけですが、
が、このブログの基本方針だったりするので、今回もそうするのです。
ソースコード
'分析ツールの重回帰分析に信頼区間と予測区間を加える
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
コードの解説
大方は、以前作ったマクロ
の流用だったりするわけですが、重回帰分析の場合、区間推定にマハラノビスの汎距離が必要で、これがサッパリ分からなかったりします。
こんなヤツらしいんですが、
やっぱり分からないですね。
計算方法は、相関行列を使ったり分散共分散行列を使ったりと幾つかあるみたいですけど、『マンガでわかる統計学 回帰分析編』で解説されていたのは説明変数の偏差平方和・積和行列を使ったやり方でしたので、私もそうさせていただきました。
ついでに、この行列を使って、多重共線性チェック用の行列式も計算しています。*1
このマハラノビスの汎距離を使って区間幅を計算するのですが、その際に使うF値は第1自由度「1」固定で求めるみたいです。なぜでしょうか? 理由がわかりません。
って、解説になってませんね・・・
使い方と実行結果
使い方は、毎度おなじみ、ソースコード全行をVBEで標準モジュールにコピペしたら、データ範囲を選択して、[開発]→[マクロ]→[Multiple_Regression]を選択→【実行】をクリックするだけ、です。
使用上の注意が3つあります。
- 必ず表にラベル行を作っておいてください。
- 必ず説明変数を左に、目的変数を一番右に配置してください。
- 必ずラベル行からセル範囲を選択してください。
クリック後1~数秒で以下のように結果が出力されます。
表やグラフの読み方については、以下の記事を参考にしてください。
『マンガでわかる統計学 回帰分析編』に従ってExcel分析ツールの重回帰分析の結果を読んでみた1 - 静粛に、只今統計勉強中
『マンガでわかる統計学 回帰分析編』に従ってExcel分析ツールの重回帰分析の結果を読んでみた2 - 静粛に、只今統計勉強中
Excel分析ツールの重回帰分析のグラフも読んでみた - 静粛に、只今統計勉強中