Home / 相関

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

INTRO いろいろな相関係数

相関比

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

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

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

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

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

以下,このデータをもとに相関比η²を求めます。

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

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

STEP 2 別表見出しの作成

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

ここではセルD1から右方に順に,(Aコース-ave[Aコース])², (Bコース-ave[Bコース])², (Cコース-ave[Cコース])²としています(つまりこれらの列は,見出しそのまま各コースの利用者ごとに偏差の2乗を求めるために使用します)。

STEP 3 相関比の計算(1)

η²は次の式で求めます。

  • 級間変動 ÷ (級内変動+級間変動)

上の式にいう「級内変動」はStep 2であらたに用意した列の総和です。

ということで具体的にこれを求めます。D・E・F各列について下表の式を入力し(絶対参照に注意),それぞれ残りの同グループのデータ範囲にコピーします。

セルD2 =(B2-$B$11)^2
セルE12 =(B12-$B$19)^2
セルF20 =(B20-$B$27)^2
STEP 4 相関比の計算(2)

目的の級内変動を求めます。

STEP 5 相関比の計算(3)

Step 3式のもう片方,「級間変動」を求めます。ひらたく言えば,これは平均が全体とグループとでどれだけかい離しているかを見ます。このケースの場合,具体的には次の式で求めます。

セルE27 =B10*(B11-B29)^2+B18*(B19-B29)^2+B26*(B27-B29)^2
STEP 6 相関比の計算(4)

η²を求めます。Step 3式の参照構造をExcel上で組み立てると,下表のようになります。

セルE28 =E27/(E26+E27)
STEP 7 相関比・計算完了

結果,η²は0.17となりました。

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

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

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

⇧