静粛に、只今統計勉強中

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

Excel VBAで分析ツールの相関行列に無相関検定をちょい足ししてみた2

エクセルExcelの分析ツールならあっという間に相関行列が作れることがわかりましたが、何と言うかさっぱりし過ぎてるんですね。
エクセル統計ほどの充実ぶりは無理としても(タダだし)、せめて無相関検定くらいはしてくれてもいいのに。

と思ったので、VBAで作ってみました。
今回は、関数じゃなくて実行プログラムになります。

ざっくりと要件定義

  1. 相関係数の有意性検定統計量はf:id:cyclo-commuter:20171222102306j:plainで計算します。
  2. 上で計算したt値(と自由度)をワークシート関数T.DIST.2Tの引数にしてp値を求めます。
    f:id:cyclo-commuter:20171222105738j:plain
  3. ユーザーが指定する有意水準αで有意となった相関係数のフォントカラーを変えて太字にします。
  4. 相関係数の値でセルの背景色を変えます。(値の大小で色をグラデーションさせます。)

いきなり実装

ようやく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

  1. [開発]タブを選択して[Visual Basic]をクリックします。
    f:id:cyclo-commuter:20171222124501j:plain
  2. 分析対象となるブックのファイル名が記載されたVBAProject (***.xlsx) の上で右クリック→[挿入]→[標準モジュール]をクリック
    f:id:cyclo-commuter:20171222134232j:plain
  3. 上のソースコードを全てコピーして、VBEのコードペインに貼り付けます。
    f:id:cyclo-commuter:20171222134248j:plain
  4. ワークシートの画面に戻って、分析対象となるセル範囲を選択します。
    注意1:あらかじめ分析ツールアドインを有効にしておいてください!
    注意2:この時点で欠損値のある行は、必ず除いておいてください!*3
    注意3:先頭行は必ずラベル(項目名)にしておいてください!
  5. [開発]タブを選択して[マクロ]をクリックします。
    f:id:cyclo-commuter:20171222135205j:plain
  6. Correl_Matrix を選択して【実行】をクリックします。
    f:id:cyclo-commuter:20171222135609j:plain
  7. 有意水準αを1~10%の範囲で数字のみ入力して【OK】をクリックします。
    f:id:cyclo-commuter:20171222141035j:plain
  8. 新規ワークシートに相関行列が作られました!
    f:id:cyclo-commuter:20171222141703j:plain

背景色が黒のセルをはさんで、左下が分析ツールアドインが計算した相関係数、右上がちょい足しした統計量tのp値で、この2つは黒セルを境に対を成しています。

  • 無相関検定で有意、かつ相関係数の絶対値が0.2より大きいセルには背景に色が付いています。
  • 相関係数が正のときは青系、負のときは赤系です。
  • 相関係数の絶対値が>0.2、>0.4、>0.7の3段階で値が大きくなるほど背景色の色が濃くなります。

*1:2017/12/24コードミスを修正しました。

*2:本稿では『統計学がわかる【回帰分析・因子分析編】』から拝借しています。

*3:2018/1/1 欠損値があっても計算できるプログラムを作りました。Excel VBAで相関行列と偏相関行列をまとめて出力する実行プログラムを作ってみた - 静粛に、只今統計勉強中