再計算のできる相関行列シートの作成 with Excel
イントロダクション
エクセルで相関行列を出力したいとき，アドインの「分析ツール」が役に立ちます。ただし，この便利なアドインも次のような場面となるとさすがに面倒が生じます。
- 所与の諸変数について，それらの相関を時系列(たとえば月)に沿って確認していくようなとき(月の数だけアドインを走らせる必要がある)
- その後の行列の計算などデータ処理上の要求から，対角左下・対角上・対角右上すべての要素が表示される必要があるとき(アドインの場合，対角右上は別途手作業で埋める必要があります)
ここでは，そうした場面での利用にかなう相関行列シートを作成していきたいと思います。
Step 0シチュエーションの設定
企業情報サービス会社Xがあります。X社の提供するオンライン情報サービスでは，会員が任意のページを有料で閲覧することができます。
あるときXは，ある期間のデータ参照数について，会員IDと記事のカテゴリとでクロス集計をおこないました。その結果，下の表を導くことができました(ただし会員IDについては，今回の目的では不要なため削除しています)。
このシートには，「PV」という名前をつけています(以下，「元表」と呼びます)。
DL
ここでは，このようなリスト形式の表をソースとして，別のシート(「CorMatrix」シート)に相関行列が自動的にアウトプットされるしくみをつくることを目的とします(下表)。
仕様として，フォーマットが同じであれば別のシートのデータもそのまま相関行列のソースとできることが1点と，
(直上のデータは 高橋 信(2006)『マンガでわかる統計学 ［因子分析編］』井上いろは作画, トレンド・プロ制作, オーム社 の67頁に掲載のものを引用しています)
元表の変数(ここでいう「記事カテゴリ」)を追加したり削除したり，
データのサイズ(ここでいう「会員ID」別のレコード)を増やしたり減らしたりしたときに，相関行列が再計算されることが1点の，あわせて2点を組み込みます。
工程
元表(シート)の作成
Step 1元表の構造
元表にデータを入力する際は，下の図のように必ず1行目を見出しとして，セルA1より変数の別に1列ずつ入力します。
相関行列シートの作成
Step 2あたらしいシートを用意
シート「CorMatrix」を作成し，C4のセルを始点に表組を作成します。
このとき，“運用に至ってから，変数を最大でどの程度まで扱いうるか”おおまかに想定しておくといいかもしれません(配列数式を使うのでどちらかといえば修正が面倒です)。
この事例では，元表に9個の変数があります(「PV」シート・列見出しの項目)。汎用性を鑑みて，ここでは必要よりも1つ多い，タテ・ヨコともに10個のセル(変数)を用意しました。
Step 3元表 “シート名” の入力
セルB1にソースとなる元表を含むシートの名前を指定します。この例では「PV」です。
| セルB1 | (元表のシート名) |
|---|
Step 4変数名の転記(1)
行・列 見出し部分に変数名を表示します。
まずは列見出しから，セルD4に次の式を入力し表最右列までコピーします。
| セルD4 | =IF(INDIRECT(CHAR(39) & $B$1 & CHAR(39) & CHAR(33) & ADDRESS(1, COLUMN(D1)-3))=0, "", INDIRECT(CHAR(39) & $B$1 & CHAR(39) & CHAR(33) & ADDRESS(1, COLUMN(D1)-3))) |
|---|---|
|
ことばによる 説明 |
|
scrollable
Step 5変数名の転記(2)
列見出しの内容が行見出しにも表示されるよう設定します。
セル範囲C5:C14を選択し(＝行見出しの領域すべて)，次の式を入力したら，キーボードの［Ctrl］+［Shift］+［Enter］で確定して配列数式とします。
|
セルC5:C14 (配列数式) |
=TRANSPOSE(D4:M4) |
|---|
scrollable
Step 6データ対の数を計算
表側頭でデータのサイズnを求めます。
ここでは見た目的な分かりやすさを兼ねる意味で，値のみを表示させるのではなく，次のような書式付きの文字列として対処したいと思います。
| セルC4 | =TEXT(COUNT(INDIRECT(CHAR(39) & $B$1 & CHAR(39) & CHAR(33) & "A:A")), """n: ""#") |
|---|
scrollable
Step 7作業用の行・列の作成(1)
ここからしばらく 元表(「PV」)の 見出しを除くデータ範囲を，変数ごとにアドレスで定義していきます。
最初に，開始セルを定義します。セルD2に次の式を入れ，表最右列までコピーします。
| セルD2 | =ADDRESS(2, COLUMN(D1)-3) |
|---|
scrollable
Step 8作業用の行・列の作成(2)
つづいて終了セルを定義します。セルD3に次の式を入れ，表最右列までコピーします。
| セルD3 | =ADDRESS(ROW(D1)+VALUE(MID($C$4, 4, LEN($C$4)-3)), COLUMN(INDIRECT(D2))) |
|---|---|
|
ことばによる 説明 |
|
scrollable
Step 9作業用の行・列の作成(3)
Step 7のセル範囲をコピーし，セルA5を基点に行方向へ貼り付けます。
scrollable
具体的には，形式を選択して貼り付けから値のみを行列を入れ替えて貼り付けします。
Step 10作業用の行・列の作成(4)
Step 8のセル範囲を，セルB5を基点にして行方向に転記します。
セル範囲B5:B14を選択し(＝行見出しの1つ隣の領域すべて)，次の式を入力したら，キーボードの［Ctrl］+［Shift］+［Enter］を押して配列数式とします。
|
セルB5:B14 (配列数式) |
=TRANSPOSE(D3:M3) |
|---|
scrollable
Step 11相関係数の計算
セルD5に次の式を入力し，列方向および行方向へコピーします。
| セルD5 | =IF(AND($C5<>"", D$4<>""), IF(ROW($C5)-4>COLUMN(D$4)-3, PEARSON(INDIRECT(CHAR(39) & $B$1 & CHAR(39) & CHAR(33) & $A5 & CHAR(58) & $B5), INDIRECT(CHAR(39) & $B$1 & CHAR(39) & CHAR(33) & D$2 & CHAR(58) & D$3)), IF(ROW($C5)-4<COLUMN(D$4)-3, PEARSON(INDIRECT(CHAR(39) & $B$1 & CHAR(39) & CHAR(33) & D$2 & CHAR(58) & D$3), INDIRECT(CHAR(39) & $B$1 & CHAR(39) & CHAR(33) & $A5 & CHAR(58) & $B5)), 1)), "") |
|---|
scrollable
長く雑然とした式ですが，表の対角部分の左下か右上かで，PEARSON関数の要求する第1・第2変数の序列を入れ替える仕様としたゆえです(むろん効率，の意味では入れ替える必要はありませんが，原理的な意味ではやはりもやもやが残るので)。
その他詳しい構造は下図のとおりとなっています。
scrollable
Step 12相関行列・完成
相関行列の完成です。必要により，小数点以下桁数を揃えます。
アドレスを指示するために使ったセル(D2:M3, A5:B14)は，見た目的に邪魔であれば文字色を替えるなどして不可視にするか目立たなくしてもいいかと思います。
scrollable
シート名を取りかえると(パープルの囲みの部分)，当該のシートを元表にして「CorMatrix」シートが再計算されます。
拡張
Step 13より使いやすく
カラースケール機能を使えば，相関の正負およびその強弱を直観的に把握できるような設定が可能です。
具体的には，下図のグレーの部分のような 対角要素を除くデータ領域(その時点で使用していない領域も含め)に関して，対角要素より左下の部分，右上の部分，あるいはその両方を用途に照らして選択し(この例では以下左下のみの選択とします)…
リボンの「ホーム」「条件付き書式」から「新しいルール」を作成します。
「新しい書式ルール」のダイアログを下のように設定して完了です(色は任意で変更可)。
Step 14元表切り替えをマクロでさらにイージーに
多くのシートをサクサクと切り替えながらデータを眺めていきたいときには，マクロの力を借りられればbetterです。
具体的には，下のように 任意の場所にボタン(ここでは図形)を用意し…
VBEを起動し 標準モジュールを作成して次のコードを貼り付けて…
' ［CorMatrix］シート切り替えボタン v.1.1
' bdastyle.net/tools/correlation-coefficient/page4-correlationmatrix.html
' by hawcas 2014, 2017
Sub LeftButton()
' 左
Dim SBJ As String ' 元表シート名
SBJ = ActiveSheet.Range("b1").Value
Select Case Worksheets(SBJ).Index
Case 1
Beep
Exit Sub
Case 2
If Worksheets(1).Name = "CorMatrix" Then
Beep
Exit Sub
Else
Range("b1") = Worksheets(1).Name
End If
Case Else
If Worksheets(Worksheets(SBJ).Index - 1).Name = "CorMatrix" 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 = ActiveSheet.Range("b1").Value
Select Case Worksheets(SBJ).Index
Case Worksheets.Count
Beep
Exit Sub
Case Worksheets.Count - 1
If Worksheets(Worksheets(SBJ).Index + 1).Name = "CorMatrix" Then
Beep
Exit Sub
Else
Range("b1") = Worksheets(Worksheets(SBJ).Index + 1).Name
End If
Case Else
If Worksheets(Worksheets(SBJ).Index + 1).Name = "CorMatrix" 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」のマクロをそれぞれ登録するとスムーズな切り替えが機能します(相関行列を表示させるシートの名前が「CorMatrix」でない場合，コードの中の緑字の “CorMatrix” 部分を適当な名前に変更してください)。
相関行列を出力できるExcelアドインソフト
- エクセル統計 BellCurve
- 「基本統計・相関」「相関行列と偏相関行列」
- Statcel4(4Stepsエクセル統計)
- 「ピアソンの相関係数」
その他の参照
このサイトの関連How-toです。
メインサイト「ひとりマーケティングのためのデータ分析」の相関に関するHow-toです。