インタラクティブ相関テーブル
目標
今回のテーマはスライサーを使って条件をダイナミックに変えられる相関係数テーブルです。
ある二つの変数のペアに関連がある場合、分析ツールやExcel関数で相関係数を求め、その両者の関連の度合いを数値化することができます。
通常、分析ツールやExcel関数では新しい組み合わせで相関係数を求めるとき、一回一回計算をやり直さないといけませんが、今回はピボットテーブルで自由に分析できるようにします。
技術的なポイント
- 非アクティブなリレーションシップを使う
- 縦軸と横軸の数値を取ってくるメジャーを作る
- レイアウトはピボットや条件付き書式で工夫
実践
元のデータ
元のデータは以下のようなアンケートの回答を想定しています。
Name列は回答者を示すユニークなID、その他の緑色の部分は回答者の属性です。回答者の属性はのちほどスライサーをかけるときに使います。
水色の部分はそれぞれの質問に対する回答になります。
今回は「食べ物の好みに関連性が見られるか」を組み合わせごとに調べていきます。
3つのテーブルを作る
元のデータから、パワークエリで以下三つのテーブルを作ります。
テーブルを作るときは、ワークシートテーブルに読み込む必要はないので読み込み先を接続の作成のみでこのデータをデータモデルに追加するにチェックを入れて作成するのが良いでしょう。
回答者一覧テーブル（T_Participant）
緑色の列のみを残したテーブルです。
このテーブルは二つの回答を接続し、スライサーで回答をまとめてフィルタリングするのに使用します。
回答テーブル（T_Survey_X、T_Survey_Y）
回答者のIDと質問と回答をセットにしたテーブルです。
GendarとAge列を削除し、Name列を選択して、その他の列のピボット解除を実行します。
属性列は質問に、値列は回答に列名を変更しましょう。
このテーブルを二つ作り、それぞれT_Survey_XとT_Survey_Yという名前で取り込みます。
リレーションシップを作る
T_ParticipantのName列とT_Survey_X、T_Survey_YのName列をリレーションシップでつなぎます。ただし、リレーションシップでつないだ後、それぞれ非アクティブ化をクリックしてリレーションシップを非アクティブにします。
ダイアグラムビューを見ると以下のように非アクティブなリレーションシップが点線で表示されます。
X軸とY軸の回答を取得する
ワークシート上でピボットテーブルをこのブックのデータモデルから作成するで作成し、列にT_Survey_Xの質問を、行に列にT_Survey_Yの質問を配置します。
続いて以下のメジャーを作り、T_Survey_Xの回答を得られるようにします。
「IF ( HASONEVALUE ( 'T_Survey_X'[回答] ), VALUES ( 'T_Survey_X'[回答] )」は集計せずにテーブルの値を拾ってくるときの書き方です。
また、非アクティブなリレーションシップを有効にするため、USERRELATIONSHIP関数を使用します。
Value_X
=CALCULATE (
IF ( HASONEVALUE ( 'T_Survey_X'[回答] ), VALUES ( 'T_Survey_X'[回答] ) ),
USERELATIONSHIP ( T_Participant[Name], T_Survey_X[Name] )
)メジャーが完成したらピボットテーブルの値にセットして列のT_Survey_Xの回答が表示されることを確認します。
そのまま実行すると値が何も表示されませんが、それは「 IF ( HASONEVALUE ( 'T_Survey_X'[回答] ), VALUES ( 'T_Survey_X'[回答] ) )」が値が複数ある場合にブランクにしているためです。
以下のようにT_Participantの「Name」列をスライサーにし、どれか一人の回答者を選択すると値が表示されます。
このとき、行のリレーションシップは非アクティブ状態なので、T_Survey_Xの同じ値が繰り返されます。
同様にT_Survey_Yの回答を取得するメジャーを作成します。
Value_Y
=CALCULATE (
IF ( HASONEVALUE ( 'T_Survey_Y'[回答] ), VALUES ( 'T_Survey_Y'[回答] ) ),
USERELATIONSHIP ( T_Participant[Name], T_Survey_Y[Name] )
)同様にピボットテーブルの値に配置すると、T_Survey_Yの回答が各行で等しく表示されます。
これより、XとYのそれぞれの回答の組み合わせを取得できました。ここまで来れば、あとは計算するだけなのでほぼできたも同然です。
相関係数を求めるメジャーを作る
あとは計算で相関係数を求めるだけです。
たくさんありますが以下のメジャーを作成してください。
SUMXのイタレイターでT_Participant「Name」毎にそれぞの計算を行った後に合計しています。
Sum_X =SUMX(VALUES(T_Participant[Name]), [Value_X])
Sum_Y =SUMX(VALUES(T_Participant[Name]), [Value_Y])
Sum_X2 =SUMX(VALUES(T_Participant[Name]), POWER([Value_X], 2))
Sum_Y2 =SUMX(VALUES(T_Participant[Name]), POWER([Value_Y], 2))
Sum_XY
=SUMX (
VALUES ( T_Participant[Name] ),
[Value_X] * [Value_Y]
)Count_Items
=DISTINCTCOUNT('T_Participant'[Name])Pearson_Denominator_X =([Count_Items] * [Sum_X2]) - POWER([Sum_X], 2)
Pearson_Denominator_Y =([Count_Items] * [Sum_Y2]) - POWER([Sum_Y], 2)
Pearson_Numerator =([Count_Items] * [Sum_XY]) - ([Sum_X] * [Sum_Y])
Pearson_Denominator =SQRT([Pearson_Denominator_X] * [Pearson_Denominator_Y])
Pearson =DIVIDE([Pearson_Numerator], [Pearson_Denominator])
最後に作成したPearsonのメジャーの身を値に入れて相関係数が計算されるか確認してください。
今回は、Nameのスライサーは解除し、代わりにGendarとAgeのスライサーを追加し、動作を確認します。
応用
相関の強いもののみを表示する
ちなみに、相関強さによって値の表示・非表示をコントロールすることもできます。
以下のようなテーブルを作り、T_Strengthとしてデータモデルに読み込みます。
パラメーターテーブルとして使うため、読み込んだ後はリレーションシップはつながずに孤立したテーブルにしておきます。
後は以下の二つのメジャーを追加します。
Grade
=IF(HASONEVALUE('T_Strength'[Grade]), VALUES('T_Strength'[Grade]))Pearson Selected = VAR v_Pearson =DIVIDE([Pearson_Numerator], [Pearson_Denominator]) VAR v_OverGrade = ABS(v_Pearson) >= [Grade] RETURN IF( v_Pearson <> 1 && v_OverGrade , v_Pearson)
あとはスライサーを追加すれば、Strengthのスライサーを追加して出来上がりです。
条件付き書式でハイライト
条件付き書式をメジャーにセットすることで相関の強さ・正負で表示を変えることも可能です。
ピボットはレイアウトを変えられるので、行にXとYを並べたり、列に回答者の属性を並べたりすることで相関のある組み合わせを見つけやすくなります。
