BDAstyle

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

相関係数の計算 with Excel 3/3 [スピアマンの順位相関係数]

3. スピアマンの順位相関係数

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

健康食品製造販売会社X社は,メインサイトの他に現在2つのウェブサイトを運営しています。その1つは情報サイトで,残る1つは物販サイトです。

X4週間前,この両サイトにクリック保証型広告を導入しました。サイト管理者がサイト利用者の行動分析目的でこの期間のアクセスログを確認していたとき,ふとあることが気になりました。

「当社サイトの場合,平均滞在時間とCTR(Click Through Rate)に関連があるのかもしれない」

―――早速,サイト管理者はサイトの平均滞在時間(秒)とCTRの各データを抽出してみます。その結果が次の表です。

初期データ 初期データ

以下,これらのデータからスピアマンの順位相関係数を求めます。

(なお数式どおりに手続きを踏むのが面倒な場合,いずれのケースも順位づけした後PearsonまたはCorrel関数でどうぞ)

Step 1順位づけ(1)…同順位なし

ここでは,「情報サイト」「物販サイト」両データを並行して処理していくものとします。

まず「情報サイト」の「平均滞在時間」および「CTR」値をもとに順位づけをおこなっていきます(あたらしい列を作成します)。ここでは「平均滞在時間」が長い順で順位をつけていくことにします。データを降順で並べ替え,連続データで下のように順位を振ります。

情報サイト(同順位なし)

Step 2順位づけ(2)…同順位なし

つづいて「CTR」も値の大きな順で順位をつけていくことにします。先ほどと同様,データを降順で並べ替え,連続データで下のように順位を振ります。

情報サイト(同順位なし)

Step 3順位づけ(3)…同順位あり

ここで対象を「物販サイト」表に移し,「平均滞在時間」および「CTR」値をもとに順位づけをおこなっていくことにします(あたらしい列を作成します)。Step 1・2と同様,「平均滞在時間」の長い順で順位をつけていくものとして,データを降順で並べ替え,連続データで順位を振ると…

「平均滞在時間」値とその順位との関係に矛盾が生じた状態にあります。

物販サイト(同順位あり)

Step 4順位づけ(4)…同順位あり

ということで同じ値の場合には同順位を振ってやる必要があります。このルールとして,該当する順位の和をデータ数で除した値を順位として用いるものとします(上表4・5行目を例とすると,(2位+3位)/2個=2.5位)。

ただデータ数が多い場合には,11つを目視しながら順位を修正していくのも手間がかかります。バージョン2010以降に限っては…

物販サイト(同順位あり)

…下のように,Rank.Avg関数により求めたほうが効率的かもしれません。

セルD3 =RANK.AVG(B3,$B$3:$B$30,0)

Step 5順位づけ(5)…同順位あり

「CTR」も同じようにして降順でデータを並べ替え,連続データで下のように順位を振ります。

加えて,同じ「CTR」値の順位を先のステップと同じように調整します(下の表は調整後の値。なおバージョン2010以降の式は次のとおり)。

セルE3 =RANK.AVG(C3,$C$3:$C$30,0)
物販サイト(同順位あり)

Step 6スピアマンの順位相関係数の計算(1)…同順位なし

同順位のない「情報サイト」のスピアマンの順位相関係数(rs)を求めます。同順位のない場合,計算式は次のとおりです。

rs=1-6Σd^2/N^3-N

計算の準備として,式のd^2を求めるための領域をあらたに作成します。この見出しの直下のセルに両順位の差の2乗を求める式を入力し(ここでは =(D3-E3)^2),コピーします。

情報サイト(同順位なし)

Step 7スピアマンの順位相関係数の計算(2)…同順位なし

任意のセルでスピアマンの順位相関係数を求めます。

Step 6の式に対応する計算式を,ここでは,

分子(セルB33) =6*SUM(F3:F30)
分母(セルB34) =COUNT(D3:D30)^3-COUNT(D3:D30)

と分けて入力しています。これらの値から,目的の相関係数(rs)が求められます。

rs(セルB35) =1-B33/B34
情報サイト(同順位なし)

Step 8スピアマンの順位相関係数(同順位なし)・計算完了

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

情報サイト(同順位なし)

Step 9スピアマンの順位相関係数の計算(1)…同順位あり

再び対象を移し,同順位のある「物販サイト」の相関係数を求めます。同順位のある場合,次の式でスピアマンの順位相関係数を求めます。

rs=Tx+Ty-Σd^2/2√TxTy ただし,Tx=N^3-N-Σ(ti^3-ti)/12, Ty=N^3-N-Σ(tj^3-tj)/12

このTx,Tyはそれぞれ次の値を意味します。

Step 10スピアマンの順位相関係数の計算(2)…同順位あり

「順位:平均滞在時間」を変数x,「順位:CTR」を変数yとし,変数xの昇順でデータを並べ替えます。

つづいてE列にあたらしい列を挿入し,E・G列に下のような見出しを作成します。この両列はStep 9式の淡い青で囲んだ部分を計算するために利用します。

物販サイト(同順位あり)

Step 11スピアマンの順位相関係数の計算(3)…同順位あり

このケースの場合,変数xについては 同順位となる組が4つ存在しています。これら各組のti^3-tiを求めます。4組ともに2つのデータから構成されていることから,ti=2となります。

ただ,この計算も先と同様,データ数が多いと手間がかかります。ということで,関数を使って同順位のうち2番目に登場するデータ行にのみ自動で計算させてもいいかと思います(下図)。

セルE3 =IF(COUNTIF($D$3:D3,D3)=2,COUNTIF($D$3:$D$30,D3)^3-COUNTIF($D$3:$D$30,D3),"")
物販サイト(同順位あり)

Step 12スピアマンの順位相関係数の計算(4)…同順位あり

変数y(「順位:CTR」)について,Step 11の操作を繰り返します(tj^3-tjの計算:並べ替えておくと確認しやすいと思います)。

このケースの場合,5つの同順位の組があります。(並べ替えると)上から2番目の組のみtj=3となります。Step 11と同様関数を利用する場合には,COUNTIF関数の引数に変数y(「順位:CTR」)を指定します。

セルG3 =IF(COUNTIF($F$3:F3,F3)=2,COUNTIF($F$3:$F$30,F3)^3-COUNTIF($F$3:$F$30,F3),"")
物販サイト(同順位あり)

Step 13スピアマンの順位相関係数の計算(5)…同順位あり

下図のようにあらたな見出しを作成します。なおデータ対の数(n)のみ入力or計算しておきます(ここでは28)。

物販サイト(同順位あり)

Step 14スピアマンの順位相関係数の計算(6)…同順位あり

Step 9式にしたがってTx,Tyを求めます。ここでは,

Tx(セルE31) =(B34^3-B34-SUM(E3:E30))/12
Ty(セルG31) =(B34^3-B34-SUM(G3:G30))/12

となります。

物販サイト(同順位あり)

Step 15スピアマンの順位相関係数の計算(7)…同順位あり

2変数の差の2d^2を計算します。

ここではH列で計算します。見出し直下のセルに =(D3-F3)^2 と入力し,表最下行までコピーします。

物販サイト(同順位あり)

Step 16スピアマンの順位相関係数の計算(8)…同順位あり

Step 9式にしたがって,スピアマンの順位相関係数を求めます。ここでは,

分子(セルB35) =E31+G31-SUM(H3:H30)
分母(セルB36) =2*SQRT(E31*G31)

と分けて入力しています。これらの値から,目的の相関係数(rs)が求められます。

rs(セルB37) =B35/B36
物販サイト(同順位あり)

Step 17スピアマンの順位相関係数(同順位あり)・計算完了

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

なお,スピアマンの順位相関係数は,いずれの場合も-1から1の値をとります2変数の関連についての考え方は,ピアソンの積率相関係数と同じです。

物販サイト(同順位あり)

Step 18無相関検定をおこなう場合

nがある程度大きな場合(目安として>30)はピアソンの積率相関係数での手続きと同様です(t検定。ただし統計パッケージやアドインなどによってはこれと異なる方法が用いられることも)。手続きの仔細が必要であれば,メインサイト「散布図の作成と相関係数の計算(相関分析) with Excel」頁を参照ください(最下部・非表示の領域を展開)。

またnが小さな場合は,下のようなrsの有意点を示す検定表をもとに判断します。たとえばn=10のときの 両側5%検定では 少なくともrs0.649ないと帰無仮説を棄却できません。

両側検定の有意水準
n 0.1 0.05 0.01
5 0.900 1.000 -
6 0.829 0.886 1.000
7 0.715 0.786 0.929
8 0.620 0.715 0.881
9 0.600 0.700 0.834
10 0.564 0.649 0.794
11 0.537 0.619 0.764
12 0.504 0.588 0.735
13 0.484 0.561 0.704
14 0.464 0.539 0.680
15 0.447 0.522 0.658
16 0.430 0.503 0.636
17 0.415 0.488 0.618
18 0.402 0.474 0.600
19 0.392 0.460 0.585
20 0.381 0.447 0.570

[上表の引用元]岸・吉田(2010), p.290.

参考文献等

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

その他の参照