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＝「群間変動」÷「全変動」)

その他の参照