BDAstyle

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

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

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

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

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

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

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

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

DL
:Info

DL
:EC

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

なお数式どおりに手続きを踏むのが面倒な場合,いずれのケースも順位づけした後,PearsonまたはCorrel関数でより簡素な手続きに代えることも可能です。

それもまた面倒な場合,別頁のデータをコピペするだけで計算可能なサービス「スピアマンの順位相関係数の計算 on Web」にて済ませるか,あるいは次のリンク先(外部サイト)のようなオープンなユーザー定義関数をお借りするなどしてもいいかもしれません。

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つを目視しながら修正を加えていくのも手間がかかります。その場合,

物販サイト|同順位あり

……下のように,Rank.Avg関数を利用して処理すれば効率的です。

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

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

「CTR」も同じようにして降順でデータを並べ替え,連続データを振ったのち修正を加えるか,あるいはダイレクトにRank.Avg関数を使ってするかして順位をつくります(下の表は順位を振った後の値)。

セルE3 =RANK.AVG(C3, $C$3:$C$30, 0)

物販サイト|同順位あり

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

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

r_{s}=1-\dfrac {6 \cdot \sum_{i=1}^{n} d_{i}^{2}} {n^{3}-n}

n:データ対の数, d:順位の差

計算の準備として,式のdi2を求めるための領域をあらたに用意します(F列)。この見出しの直下のセルに両順位の差の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)…同順位あり

対象を再び移し,同順位のある「物販サイト」の相関係数を求めます。なお説明の都合,以下では便宜的に「順位:平均滞在時間」を変数x,「順位:CTR」を変数yと呼ぶことにします

同順位のある場合,次の式でスピアマンの順位相関係数を求めます。

r_{s}=\dfrac {T_{x}+T_{y}-\sum d_{i}^{2}} {2\sqrt {T_{x} T_{y}}}

d:順位の差

このとき,Tx, Tyの内容はそれぞれ次のとおりです。

\begin{align*} & T_{x}=\dfrac {n^{3}-n-\sum \left( t_{i}^{3}-t_{i}\right) } {12}\\ & T_{y}=\dfrac {n^{3}-n-\sum \left( t_{j}^{3}-t_{j}\right) } {12} \end{align*}

n:データ対の数, ti,tj:順位が同じ組について,その組がもつデータの数

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

ここで変数xの昇順でデータを並べ替えます。

つづいてE列にあたらしい列を挿入し,E・G列に下のような見出しを作成します。この両列はStep 9式・Tx, TyΣを計算するために利用します。

物販サイト|同順位あり

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

「物販サイト」の変数xについては,同順位の組が4つあります。したがって,これら各組のti3-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の操作を繰り返します(tj3-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変数の差の2di2を計算します。

ここではH列で計算します。見出しの直下のセルに

セルH3 =(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.

参考にした書籍およびWebページ

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

その他の参照