Excel VBAで分析ツールの相関行列に無相関検定をちょい足ししてみた2
エクセルExcelの分析ツールならあっという間に相関行列が作れることがわかりましたが、何と言うかさっぱりし過ぎてるんですね。
エクセル統計ほどの充実ぶりは無理としても(タダだし)、せめて無相関検定くらいはしてくれてもいいのに。
と思ったので、VBAで作ってみました。
今回は、関数じゃなくて実行プログラムになります。
ざっくりと要件定義
いきなり実装
ようやくpre記法を覚えました。*1
'分析ツールの相関行列に無相関検定の結果を加える Sub Correl_Matrix() Dim α Dim df Dim i, j, k, l Dim CF α = InputBox("有意水準αを1~10%の範囲で数字のみ入力してください。" & vbCrLf & _ vbCrLf & "α=0.05(5%) の場合の入力例 : 5", "相関行列+無相関検定") Select Case α Case 1 To 10 df = Selection.Rows.Count - 3 Application.Run "ATPVBAEN.XLAM!Mcorrel", Selection, "", "C", True Cells.EntireColumn.AutoFit l = Range("B1").End(xlToRight).Column For i = 2 To l Cells(i, i).Font.Color = vbWhite Cells(i, i).Interior.Color = vbBlack Next k = 2 For i = 3 To l For j = 2 To k Cells(j, i) = WorksheetFunction.T_Dist_2T((Abs(Cells(i, j)) _ * Sqr(df)) / Sqr(1 - Cells(i, j) ^ 2), df) If Cells(j, i) < α / 100 Then With Cells(i, j) .Font.Size = 11 .Font.Bold = True CF = Formatting(.Value) .Font.Color = CF(0) .Interior.Color = CF(1) Cells(j, i).Interior.Color = CF(1) End With End If Next k = k + 1 Next Case vbNullString Case Else MsgBox "有意水準αを1~10%の範囲で数字のみ入力してください。" & vbCrLf & _ vbCrLf & "α=0.05(5%) の場合の入力例 : 5", vbCritical, _ "相関行列+無相関検定" End Select End Sub '相関係数の値に応じて色指定 Private Function Formatting(Arg) Select Case Arg Case Is < -0.7: Formatting = Array(vbRed, 9737946) Case Is < -0.4: Formatting = Array(vbRed, 12040422) Case Is < -0.2: Formatting = Array(vbRed, 14408946) Case Is > 0.7: Formatting = Array(vbBlue, 14470546) Case Is > 0.4: Formatting = Array(vbBlue, 15261367) Case Is > 0.2: Formatting = Array(vbBlue, 15986394) End Select End Function
使い方と注意点
分析対象となるワークシートを用意してエクセルExcelで開きます。*2
- [開発]タブを選択して[Visual Basic]をクリックします。
- 分析対象となるブックのファイル名が記載されたVBAProject (***.xlsx) の上で右クリック→[挿入]→[標準モジュール]をクリック
- 上のソースコードを全てコピーして、VBEのコードペインに貼り付けます。
- ワークシートの画面に戻って、分析対象となるセル範囲を選択します。
注意1:あらかじめ分析ツールアドインを有効にしておいてください!
注意2:この時点で欠損値のある行は、必ず除いておいてください!*3
注意3:先頭行は必ずラベル(項目名)にしておいてください! - [開発]タブを選択して[マクロ]をクリックします。
- Correl_Matrix を選択して【実行】をクリックします。
- 有意水準αを1~10%の範囲で数字のみ入力して【OK】をクリックします。
- 新規ワークシートに相関行列が作られました!
背景色が黒のセル(対角要素)をはさんで、左下が分析ツールアドインが計算した相関係数、右上がちょい足しした統計量tのp値で、この2つは黒セルを境に対を成しています。
*1:2017/12/24コードミスを修正しました。
*2:本稿では『統計学がわかる【回帰分析・因子分析編】』から拝借しています。
*3:2018/1/1 欠損値があっても計算できるプログラムを作りました。Excel VBAで相関行列と偏相関行列をまとめて出力する実行プログラムを作ってみた - 静粛に、只今統計勉強中