BDAstyle

ビジネスデータ分析ツールの作成 with Excel

再計算可能な相関行列シートの作成 with Excel

1.イントロダクション

エクセルでは相関行列をアドインの「分析ツール」から簡単に作成することができます。使い勝手のいいツールですが,次のような場面となるとさすがに面倒な部分も出てきてしまいます。

ここでは,そうした場面での利用にかなう相関行列シート(一例)を作成していきたいと思います。ただし,この方法は次の点で制限をはらみますのでご注意ください。

2.工程

1. 元表(シート)の作成

Step 0シチュエーションの設定

企業情報サービス会社Xがあります。Xは同社の提供するオンライン情報サービスについて,ある期間におけるデータの参照数を会員ID別・記事項目別にまとめました(下表。ただし 後の工程で作成する別のシートと[見た目上]位置関係をあわせるため 表の始点をC4のセルとしています)。ここではこれに「browse」というシート名をつけています(以下,元表と呼びます)。

初期データ

こうしたリスト形式表をソースとして,別のシート(ここでは「相関行列」シート。以下「相関行列」シートと呼びます)に相関行列が自動的に作られるしくみを整えることがここでの目的です(下表)。

仕様として,フォーマットが同じであれば別のシートに置かれたデータもそのまま計算に利用できること1 点と…

直上のデータは 高橋 信(2006)『マンガでわかる統計学 [因子分析編]』井上いろは作画, トレンド・プロ制作, オーム社 の67頁に掲載のものを引用しています。

元表の変数(ここでいう「記事項目」)を追加したり削除したり

データサイズ(ここでいう「顧客ID」のレコード)を増やしたり減らしたりしたときに,「相関行列シート」が再計算されることが1点の,あわせて2点を組み込みます。

Step 1データサイズのカウント

元表についてデータサイズをカウントします。ここでは 顧客IDの数をデータサイズとみなしますので,表側頭(左上角)は空欄のままとしておきます(青色の囲み部分)。

セルB1 =COUNTA(C:C)

2. 相関行列シートの作成

Step 2あたらしいシートを用意

シート「相関行列」を作成し,元表と同じC4のセルを始点に表組を作成します。

このとき,“運用に至ってから 変数を最大でどの程度まで扱うことがありうるか”についておおまかに想定しておくといいかもしれません(配列数式を使うので修正が面倒です)。

ここでは元表に9個の変数があります(「browse」シート・列見出しの項目)。ここでは1つだけですが汎用性に鑑みて(変数がより多いケースでも適応できるようにするため),タテ・ヨコ10個のセルを用意しました。

Step 3元表 “シート名” の入力

セルB1にソースとなる元表のあるシートの名前を “ !記号を末尾に加えて指定します。この例では「browse!」です。

セルB1 (元表のシート名)!

Step 4変数名の転記(1)

行・列 見出し部分に変数名を表示します。

まずは列見出しから,セルD4に次の式を入力し表最右列までコピーします。

セルD4 =IF(INDIRECT($B$1&ADDRESS(ROW(D4), COLUMN(D4)))=0, "", INDIRECT($B$1&ADDRESS(ROW(D4), COLUMN(D4))))
ことばによる
説明
  • 元表のセル D4 の indirect 関数による戻り値が 0 でない その内容を転記
  • 元表のセル D4 の indirect 関数による戻り値が 0 空欄のまま

Step 5変数名の転記(2)

列見出しの内容が行見出しにも表示されるよう設定します。

セル範囲C5:C14を選択し(=行見出しの領域すべて),次の式を入力したら,キーボードの[Ctrl]+[Shift]+[Enter]を押して配列数式とします。

セルC5:C14
(配列数式)
=TRANSPOSE(D4:M4)

Step 6作業用の行・列の作成(1)

ここからしばらく 元表のシート(「browse」)の 見出し部分を除くデータ範囲を,変数ごとにアドレスで定義していきます。

最初に,開始セルを定義します。セルD2に次の式を入れ,表最右列までコピーします。

セルD2 =ADDRESS(ROW(D5), COLUMN(D5))

Step 7作業用の行・列の作成(2)

つづいて終了セルを定義します。セルD3に次の式を入れ,表最右列までコピーします。

セルD3 =ADDRESS(ROW(D4)+INDIRECT($B$1&"$B$1"), COLUMN(D5))
ことばによる
説明
  • [上から 4 行目, 左から 4 列目]つまりセル D4 を基準に,元表の n(データサイズ)を行方向に加算した[上から 4 行目+n, 左から 4 列目]のセルのアドレスを返す

Step 8作業用の行・列の作成(3)

Step 6のセル範囲をコピーし,セルA5を基点に行方向へ貼り付けます。

具体的には形式を選択して貼り付けから値のみ行列を入れ替えて貼り付けします。

Step 9作業用の行・列の作成(4)

Step 7のセル範囲を,セルB5を基点にして行方向に転記します。

セル範囲B5:B14を選択し(=行見出しの1つ隣の領域すべて),次の式を入力したら,キーボードの[Ctrl]+[Shift]+[Enter]を押して配列数式とします。

セルB5:B14
(配列数式)
=TRANSPOSE(D3:M3)

Step 10相関係数の計算

セルD5に次の式を入力し,列方向および行方向へコピーします。

セルD5 =IFERROR(PEARSON(INDIRECT($B$1&$A5&":"&$B5), INDIRECT($B$1&D$2&":"&D$3)), "")
ことばによる
説明
  • 作業用の行・列それぞれに示した開始・終了セル範囲でピアソンの積率相関係数をとったとき,エラー(おもに相関係数を求められない場合の「#Div/0!」)が発生しなかった表示
  • 同 エラーが発生空欄のまま

Step 11相関行列・完成

相関行列の完成です。必要により,小数点以下桁数を揃えます。

シート名の指定を替えると(赤色の囲みの部分),その名のシートを元表にして「相関行列」シートが再計算されます。

Step 12より見やすく

ここからはTips的な内容ですのでどうしても必要な工程ではありません。

筆者個人としては,同じ変数の交点となる対角要素について,小数点以下の桁を表示しない方が 表そのものの可読性は増すように感じます。そこでここでは,まず対角要素が整数(つまり1)で表示されるよう設定することとします。

具体的には,相関行列のデータ部分(その時点で使用していない領域も含め)を選択し…

[D5:M14]

リボンの「ホーム」「条件付き書式」から「新しいルール」を作成します。

「新しい書式ルール」のダイアログを下のように設定し,書式ボタンをクリックして…

[ルールの種類]指定の値を含むセル竹を選択/[ルールの内容]セルの値・次の値に等しい・1

「表示形式」タブ分類「数値」小数点以下の桁数「0」に設定します。

Step 13より使いやすく

次に,カラースケール機能を使って 相関の正負およびその強弱を直観的に把握できるよう設定します。

具体的には,下のように 対角要素を除くデータ領域(その時点で使用していない領域も含め)を選択し…

リボンの「ホーム」「条件付き書式」から「新しいルール」を作成します。

「新しい書式ルール」のダイアログを下のように設定して完了です(色は任意で変更可)。

[ルールの種類]セルの値に基づいてすべてのセルを書式設定/[ルールの内容]<書式スタイル>3色スケール <種類>数値・数値・数値 <値>-1・0・1

Step 14元表切り替えをマクロでさらにイージーに

多くのシートをサクサクと切り替えながらデータを眺めていきたいときには,マクロの手を借りた方がより便利です。

具体的には,下のように 任意の場所にボタン(ここではオートシェイプ)を用意し…

VBEを起動し 標準モジュールを作成して次のコードを貼り付けて…

' [相関行列]シート切り替えボタン v.1.0.0
' bdastyle.net/tools/correlation-coefficient/page4-correlationmatrix.html
' by hawcas

Sub LeftButton()
' 左
Dim SBJ As String  ' 元表シート名
SBJ = Mid(ActiveSheet.Range("b1"), 1, Len(ActiveSheet.Range("b1")) - 1)
Select Case Worksheets(SBJ).Index
Case 1
    Beep
    Exit Sub
Case 2
    If Worksheets(1).Name = "相関行列" Then
        Beep
        Exit Sub
    Else
        Range("b1") = Worksheets(1).Name & "!"
    End If
Case Else
    If Worksheets(Worksheets(SBJ).Index - 1).Name = "相関行列" Then
        Range("b1") = Worksheets(Worksheets(SBJ).Index - 2).Name & "!"
    Else
        Range("b1") = Worksheets(Worksheets(SBJ).Index - 1).Name & "!"
    End If
End Select
End Sub

Sub RightButton()
' 右
Dim SBJ As String
SBJ = Mid(ActiveSheet.Range("b1"), 1, Len(ActiveSheet.Range("b1")) - 1)
Select Case Worksheets(SBJ).Index
Case Worksheets.Count
    Beep
    Exit Sub
Case Worksheets.Count - 1
    If Worksheets(Worksheets(SBJ).Index + 1).Name = "相関行列" Then
        Beep
        Exit Sub
    Else
        Range("b1") = Worksheets(Worksheets(SBJ).Index + 1).Name & "!"
    End If
Case Else
    If Worksheets(Worksheets(SBJ).Index + 1).Name = "相関行列" Then
        Range("b1") = Worksheets(Worksheets(SBJ).Index + 2).Name & "!"
    Else
        Range("b1") = Worksheets(Worksheets(SBJ).Index + 1).Name & "!"
    End If
End Select
End Sub

左のボタンに「LeftButton」,右のボタンに「RightButton」のマクロをそれぞれ登録するとスムーズな切り替えが機能します(相関行列を表示させるシートの名前が「相関行列」でない場合,コードの中の緑字の “相関行列” 部分を適当な名前に変更してください)。

相関行列を出力できるexcelアドインソフト

その他の参照