BDAstyle

Business Data Analysis & Visualization with Excel

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

相関比

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

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

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

DL

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

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

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

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

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

Step 2別表見出しの作成

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

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

Step 3相関比の計算(1)

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

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

上の式にいう「級内変動」は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)

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

セルE28 =E27/(E26+E27)

Step 7相関比・計算完了

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

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

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

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

参考にした書籍

Next

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

η2を計算できるExcelアドインソフト

  • エクセル統計 BellCurve
    • 「基本統計・相関」「層別の記述統計量・相関比」における出力項目(η2)
  • Statcel4(4Stepsエクセル統計)
    • メニュー上には存在しませんが「分散分析」「一元配置分散分析法」で出力される分散分析表の「偏差平方和」列から簡単な計算で求めることができるようになっています(η2=「群間変動」÷「全変動」)

その他の参照