Excelで相関係数をフィルタ集計するハック
顧客や従業員調査の回答データをExcelで集計するシーンは少なくない。その際、店舗やエリアごとの数値やグラフ確認に便利なのがオートフィルタ機能だ。オートフィルタを使うとなるとSubtotal関数か Aggregate関数を使うことになる。
ところが設定できる集計方法がSUM関数やAVERAGE関数などに限られていて、われわれの大好物である相関係数を算出する関数、CORREL（PEASON関数も同様）が含まれていない。
相関係数は４象限分析（NPSドライバーチャート）作成の要である。オートフィルタが使えないのは作業効率を極端に下げる。CX・EXをExcelでの分析してみよう、という担当者が挫けてしまうポイントになっているかもしれない。ひいてはCXやEXの普及を妨げている気さえしてくる。
というわけで、今回は相関係数をオートフィルタするハック（テクニック）だ。
※ ↓参考までにSubtotal関数やAggregate関数についてはこちらを参照。
４象限分析用の基本数表作成
最初にオートフィルタのことは考えず、単純に４象限分析（NPSドライバーチャート）作成のための数表を作るところから始める。
下にサンプルとなるスプレッドシートを貼り付けておく。
エクセルシートの設定
● 回答のサンプルサイズは100と想定
● 回答データは項目名を10行目に、11行目以降に数値が入るものとする。
1〜9行目は算出する数表ために空けておく。ちなみにこのような値が入る
・1行目：項目名
・2行目：平均値
・3行目：相関係数
・4行目：データの個数
・5行目：検定用のt値
・6行目：検定用のp値
・7行目：データラベル
● 回答データは以下の通りシートに配置する。
・ 集計グループ名をA列に配置
・ 究極の質問の回答値をB列に設置
・ 体験評価回答値をC列、D列・・・G列に設置
下の図のようになる。回収したデータの上に集計エリアを作っておくと、評価する体験数が増えた場合でも表を横に伸ばせば対応できるので便利だ。
体験評価の平均値
● それぞれの体験の評価平均値を算出する関数を設定する
C列の場合、セルC2に以下の関数を設定し、D2からG2までドラッグコピー。
= SUBTOTAL(101, C11:C111)
各体験の究極の質問との相関係数
● それぞれの体験の究極の質問回答との相関係数を算出する関数を設定する セルC3に以下の関数を入力し、D3からG3までドラッグコピー。
= CORREL($B11:$B111,C11:C111）
・究極の質問の数値の回答とそれぞれの体験の回答が参照されている
・究極の質問の数値の回答とそれぞれの体験の回答が参照されている
CORREL関数についての詳細はこちらを参照してもらいたい。データを2列指定すれば相関係数（-1〜1）を算出してくれる。絶対値が1に近いほど相関性は高い
ちなみに相関係数は、算出された相関係数の信頼度をp値という数値で検定してから利用する。p値が0.05以下のものを「95%以上の信頼度のある相関係数」として利用するのが一般的だ。95%に満たないもの（p値が0.05より大きいもの）は信頼度が低いデータとされる。
相関係数の信頼度チェック用項目を作る
● B1に「究極の質問」、C1〜G1に評価体験の名前（項目名）を記入する
● 相関係数の信頼度（p値）を測るための式を設定する
・C4にデータの個数 =SUBTOTAL(103,C11:C111)
・C5にt値 =ABS(ROUND((C3*SQRT(C4-2))/SQRT(1-(C3^2)),3))
・C6にp値 =TDIST(C5,C4-2,2)
・C7に項目ラベル =IF(C6<=0.05,C1,C1&"(*less than 95%)")
を入力したら、C4:C7を範囲選択してまとめてG列までドラッグコピー。
※ C7はp値が0.05より大きい時に項目ラベルに注書きする仕掛けだ
「Aの体験評価」-> 「Aの体験評価（less than 95%）」
となる。散布図作成時にC7〜G7部分をチャートラベルに利用する
これで4象限分析に必要な数表は完成する。
・X軸にC2〜G2の数値
・Y軸にC3〜G3の数値
・プロット項目名にC7〜G7
を指定してグラフ挿入から散布図を作成れば4象限分析が出来上がる。
ただし、グループ名でオートフィルタをかけても相関係数は変化しない。
オートフィルタできるように改造する
次のような操作をしてオートフィルタに対応するよう改造する。
● 究極の質問のデータの右側に3列挿入する。
これによってこれまでC列〜G列にあったデータはF列〜J列にスライド移動する
・C列に挿入：数字 C11〜C111に 1 （0以外ならOK）を入力
・D列に挿入：判定 D11に=SUBTOTAL(109,C11）を入力しD111までコピぺ
・E列に挿入 ：究極の質問_f =IF(D11=0,"",B11 ) を入力しE111までコピぺ
● 相関係数を算出するセルを以下のように変更する
F3に現在入っている式 = CORREL($B11:$B111,F11:F111） を
右のように変更する = CORREL($E11:$E111,F11:F111）
そしてG3〜J3にコピペ
どんな操作をしているのか？
CORREL関数は引数に指定した2つのデータのうち片方が空の場合、そのセットを無視して相関係数を算出する。
そこでフィルタによって不可視化された時に「究極の質問」の回答値をない状態になるような関数を仕込んだ列（E列：「究極の質問_f」）を作成し、その列と体験評価回答の列から相関係数を算出する。
データの1行目（C11〜J11）に注目して見てみよう。
・D11ではSUBTOTAL関数を使ってC1の値を演算している。
SUBTOTAL関数はフィルタで不可視になったセルを0として扱うのでD11は
- フィルタ時に可視化状態になったとき、
上の例で「うさぎ」を選択してフィルタしたとき、値は 1 になる
- フィルタ時に不可視状態になった場合、
上の例で「うさぎ」以外を選択してフィルタしたとき、値は 0になる
・D11の値に応じて、E11には究極の質問の回答か、空データが入る
- フィルタ時に可視化状態になった場合、値はB11＝究極の質問の評価値に
- フィルタ時に不可視状態になった場合、値は空に
CORREL関数はフィルタとは無関係に演算を行うが、フィルタを実行したとき、参照先のE列「究極の質問_f」の値は不可視行では値を持たないため、実質的にフィルタで可視化した行だけの相関係数を表示する
説明が下手で申し訳ないのだが、こんなハックである。
サンプルファイルには散布図も付けたのでダウンロードして利用可能だ。
CXやEXのテスト導入時など「専用ツールに予算が取れない」というとき、無料のアンケートツール（Google Forms、Microsft Forms、Surveymonkeyなど）とこうしたハックを仕込んだエクセルシートで分析するのも一手だ。
トリムタブジャパン代表
中谷健一
お気が向いたらサポートをお願いします。