BDAstyle

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

相関係数の計算 with Excel 2/3 [相関比]

2. 相関比

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

エステ店Xがあります。Xはこの地域の潜在顧客を開拓するため,同店で特に人気のある3つのコース(A・B・C)のうち,1つを選択的に無料で体験できるクーポン券をタウン誌を通じて一定数配布しました。

その結果,期間中に20人がこのクーポンを利用しました。エステ店Xは,体験者の年齢と利用したコースについて,手元の伝票をもとにデータ化し,次の表を作成しました。

初期データ

そのとき,担当者は「はたして利用コースと利用者の年齢には関連があるのではないか」と考えました。そこでカテゴリ別にデータをまとめてみようと思い,以下の表(コース順に並べ替え)をもとにして,グラフ(ドットプロット)を作成しました。

しかし,この担当者にとってはなにぶん初めての試みで,関連が強いようにも見え…弱いようにも見え…グラフによる判断が難しいところがありました。そこで,相関係数を求めてみたいと考えました。

以下,このデータをもとに相関比(ただしη^2)を求めます。

Step 1データ数と平均の計算(カテゴリ別/全体)

各コースごとに,任意の方法(ここではアウトライン機能を使用しています)でデータの個数と「利用者の年齢」の平均を求めます。下の図では青色のセルがデータ数,紫色のセルが平均です。

Step 2別表見出しの作成

以後,Step 1表の右方を相関比の計算過程で使用していきます。その準備として,3つの見出しを作成します。

ここではセルD1から右方に順に,(Aコース-ave[Aコース])^2,(Bコース-ave[Bコース])^2,(Cコース-ave[Cコース])^2としています(下図の赤い囲み部分)。

Step 3相関比の計算(1)

相関比(η^2)は次の式で求めます。

 =級間変動/(級内変動+級間変動)

Step 2の表は,見出しのように級内変動(カテゴリにおける値と平均の差の平方和)を計算するために使用します(ave:平均)。

そこでD・E・F各列について,左表と対応するカテゴリのデータ範囲の先頭行に,見出しに表記した計算式の構造を参考に式を入力します(絶対参照に注意)。ここでは,セルD2=(B2-$B$11)^2 ,セルE12=(B12-$B$19)^2 ,セルF20=(B20-$B$27)^2 と入力します。

これらの式を,それぞれ残りのデータ範囲にコピーします。

Step 4相関比の計算(2)

級内変動を求めます(SUM関数)。

Step 5相関比の計算(3)

級間変動(カテゴリの平均と全体の平均の差の平方和:ただしカテゴリのサイズnを乗ずる)を求めます。このケースの場合,具体的な計算式は =B10*(B11-B29)^2+B18*(B19-B29)^2+B26*(B27-B29)^2 となります。

Step 6相関比の計算(4)

相関比を求めます。Step 3の式 =級間変動/(級内変動+級間変動) に各値をあてはめます。具体的に,ここでは =E27/(E26+E27) となります。

Step 7相関比・計算完了

相関係数は0.17となりました。

相関比は,0から1の値をとります2変数の関連において,0に近いほど弱く,1に近いほど強いと考えます。

なおドットプロットは,相関比の高い場合(分母の級内変動が小さい場合)はカテゴリごとにすべてのマーカーが凝集するようなパターンが見られ…

低い場合(分子および分母の級間変動が小さい[=各カテゴリの平均が近接]上に,級内変動が相対的に大きな場合)は一例として下図のように適宜分散するような形状が見られます。

参考文献

Next

次頁は「スピアマンの順位相関係数」の例です。

この係数を計算できるexcelアドインソフト

その他の参照