エクセルでスピアマンの相関係数の簡単な求め方【コピペですぐに使える】
はじめに
エクセルには、スピアマンの相関係数を算出する専用の関数は存在しません。
下にサンプルのセルデータがあるので、
スピアマンの相関係数とは
スピアマンの相関係数は、 順位に基づく相関値 であり、
いつ使うのか？
ピアソンの相関係数の使用に必要な仮定を満たさないときに
- データが非線形関係を示す場合、または正規分布しない場合
- データが順序データの場合 (どちらか一方でも)
- 外れ値が大きい場合
相関係数の意味
データの順位間の関係性の強さとその方向性を示します。
- 1: 完全な正の相関
- -1: 完全な負の相関
- 0: 相関なし
エクセルでの算出方法
以下の 全セルをコピー ボタンをクリックし、エクセルの A1 セルに貼り付けると、 E10 セルにスピアマンの相関係数（下記の場合 -0.885714286）が算出されます。
A, B, C列のデータを書き換え、 D, E列の数式中の参照セルを書き換えることで、
| A | B | C | D | E | F | |
| 1 | データ | 順位 | ||||
| 2 | ラベル | X | Y | X (rank) | Y (rank) | |
| 3 | A | 3 | 6.5 | =RANK.AVG(B3,$B$3:$B$8,0) | =RANK.AVG(C3,$C$3:$C$8,0) | |
| 4 | B | 4 | 7 | =RANK.AVG(B4,$B$3:$B$8,0) | =RANK.AVG(C4,$C$3:$C$8,0) | |
| 5 | C | 2 | 8.5 | =RANK.AVG(B5,$B$3:$B$8,0) | =RANK.AVG(C5,$C$3:$C$8,0) | |
| 6 | D | 6 | 4.5 | =RANK.AVG(B6,$B$3:$B$8,0) | =RANK.AVG(C6,$C$3:$C$8,0) | |
| 7 | E | 4.5 | 5 | =RANK.AVG(B7,$B$3:$B$8,0) | =RANK.AVG(C7,$C$3:$C$8,0) | |
| 8 | F | 5.5 | 4 | =RANK.AVG(B8,$B$3:$B$8,0) | =RANK.AVG(C8,$C$3:$C$8,0) | |
| 9 | ||||||
| 10 | スピアマンの相関係数 | =CORREL(D3:D8, E3:E8) | ||||
| 11 |
算出手順
エクセルでスピアマンの相関係数を算出するための手順は2つです。
1. データを順位に変換
RANK.AVG 関数を使用して、各データの順位を算出します。
RANK.AVG関数
第2引数の範囲のデータ列中で、第1引数のデータが何番目に位置するかを算出。
同順位の場合は、平均値が算出される。
第3引数は0または1で、降順または昇順のどちらで順位付けするかを設定
（今回はどちらでも可）。例：
RANK.AVG(B3, $B3:$B8, 0)
- 第1引数 (
B3): 順位付けするデータ- 第2引数 (
$B3:$B8): 順位付けするデータ列の全体範囲- 第3引数 (
0): 降順(0) or 昇順(1) を設定（省略時は降順）
2. 順位データ同士の相関係数を算出
CORREL 関数を使用して、XとYの順位データ間の相関を算出します。
CORREL関数
第1引数と第2引数のデータ列間の相関係数を算出。
例：
=CORREL(D3:D8, E3:E8)