BDAstyle

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

再計算の可能な相関行列シートの作成 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)))
ことばによる
説明
  • 元表の indirect 関数による見出し部分の戻り値が 0 でない(=セルに何らの文字列が入力されている) その内容を転記
  • 元表の indirect 関数による見出し部分の戻り値が 0 (=セルが空白) 空欄のまま

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)))
ことばによる
説明
  • セル D1 を基準に,n(データのサイズ)を行方向に加算した位置をアドレスで返す

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

列見出し・行見出しとも空白でなかったら,表の対角線より下の部分について,行見出しを第1・列見出しを第2変数としてピアソンの相関係数を計算する。表の対角線より上の部分について,列見出しを第1・行見出しを第2変数としてピアソンの相関係数を計算する。そして,残る領域(対角部分)には1を返す。列見出し・行見出しともに空白だった場合,計算の対象外なので空白処理をして終える。

Step 12相関行列・完成

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

アドレスを指示するために使ったセル(D2:M3, A5:B14)は,見た目的に邪魔であれば文字色を替えるなどして不可視にするか目立たなくしてもいいかと思います。

scrollable

シート名の指定を替えると(パープルの囲みの部分),その名のシートを元表にして「CorMatrix」シートが再計算されます。

拡張

Step 13より使いやすく

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

具体的には,下図のグレーの部分のような 対角要素を除くデータ領域(その時点で使用していない領域も含め)に関して,対角要素より左下の部分,右上の部分,あるいはその両方を用途に照らして選択し(この例では以下左下のみの選択とします)…

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

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

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

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

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

具体的には,下のように 任意の場所にボタン(ここでは図形)を用意し…

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アドインソフト

その他の参照