Excel で、ある列とある列の ケンドールの順位相関係数 (Kendall's τ) がほしくなることがあると思います。が、Excel の組み込み関数に Kendall's τ はありません。なので、セル式で計算すると以下のようになります (タイ補正付きの Kendall's τb を計算しています)。
scipy.stats.kendalltau の結果と一致しているので大丈夫だと思います。
from scipy.stats import kendalltau
x = [6, 1, 5, 2, 3]
y = [4, 3, 6, 3, 4]
tau_b, p_value = kendalltau(x, y)
print(tau_b, p_value)
0.6708203932499368 0.11718508719813801
セル式一覧
冒頭の Kendall's τ 計算シートは、E2 に $A$2:$A$6 のように第 1 変数の範囲を指定する文字列を、E3 に $B$2:$B$6 のように第 2 変数の範囲を指定する文字列を入力する想定です (観測を追加したときにこの文字列だけ編集すれば済む＆同じシート内の右側や下側に別変数ペア or 別標本の Kendall's τ も計算できるようにするための設計です)。
G 列以降には下記のセル式を入力しておきます。
ただし (※) については G, H 列のスピル (展開) に合わせて下までコピーします。
| セル | 式 |
|---|---|
| G2 | =LET(n,ROWS(INDIRECT($E$2)),i,SEQUENCE(n),j,TRANSPOSE(SEQUENCE(n)),HSTACK(TOCOL(IF(i<j,i,NA()),3),TOCOL(IF(i<j,j,NA()),3))) |
| I2(※) | =SIGN(INDEX(INDIRECT($E$2),G2)-INDEX(INDIRECT($E$2),H2)) |
| J2(※) | =SIGN(INDEX(INDIRECT($E$3),G2)-INDEX(INDIRECT($E$3),H2)) |
| K2(※) | =--AND(I2<>0,J2<>0,I2=J2) |
| L2(※) | =--AND(I2<>0,J2<>0,I2<>J2) |
| M2(※) | =--AND(I2=0,J2<>0) |
| N2(※) | =--AND(I2<>0,J2=0) |
| Q2 | =SUM(K2:INDEX(K:K,ROW(G2)+ROWS(G2#)-1)) |
| Q3 | =SUM(L2:INDEX(L:L,ROW(G2)+ROWS(G2#)-1)) |
| Q4 | =SUM(M2:INDEX(M:M,ROW(G2)+ROWS(G2#)-1)) |
| Q5 | =SUM(N2:INDEX(N:N,ROW(G2)+ROWS(G2#)-1)) |
| Q6 | =IFERROR((Q2-Q3)/SQRT((Q2+Q3+Q4)*(Q2+Q3+Q5)),NA()) |
p 値もほしい場合は以下も入力します。
| セル | 式 |
|---|---|
| Q8 | =LET(n,ROWS(INDIRECT($E$2)),tx,COUNTIF(INDIRECT($E$2),UNIQUE(INDIRECT($E$2))),ty,COUNTIF(INDIRECT($E$3),UNIQUE(INDIRECT($E$3))),(n*(n-1)*(2*n+5)-SUM(tx*(tx-1)*(2*tx+5))-SUM(ty*(ty-1)*(2*ty+5)))/18+SUM(tx*(tx-1))*SUM(ty*(ty-1))/(2*n*(n-1))+SUM(tx*(tx-1)*(tx-2))*SUM(ty*(ty-1)*(ty-2))/(9*n*(n-1)*(n-2))) |
| Q9 | =(Q2-Q3)/SQRT(Q8) |
| Q10 | =2*NORM.S.DIST(-ABS(Q9),TRUE) |
セル式の説明
scipy.stats.kendalltau によれば、τb の式は以下です。
tau_b = (P - Q) / sqrt((P + Q + T) * (P + Q + U))
これをセル式で以下のように計算します。
-
G2 — まず、G, H 列に観測のインデクスのペアを展開します。
- LET 関数で一気にやっていますが、下図のように 観測数×観測数 次元の行列の上三角部分にのみ行インデクスを書き込んで、それを TOCOL で 1 列に伸ばしています (ピンク色)。列インデクスも同様にしています (水色)。
- 各行が以下の観測のペアに対応します。
- $\bigl( (x_1, y_1), (x_2, y_2) \bigr)$
- $\bigl( (x_1, y_1), (x_3, y_3) \bigr)$
- $\bigl( (x_1, y_1), (x_4, y_4) \bigr)$
- $\bigl( (x_1, y_1), (x_5, y_5) \bigr)$
- $\bigl( (x_2, y_2), (x_3, y_3) \bigr)$
- $\cdots$
- LET 関数で一気にやっていますが、下図のように 観測数×観測数 次元の行列の上三角部分にのみ行インデクスを書き込んで、それを TOCOL で 1 列に伸ばしています (ピンク色)。列インデクスも同様にしています (水色)。
- I2 — 「このペアで $x$ が大きいのは前者か後者か」を出します。
- J2 — 「このペアで $y$ が大きいのは前者か後者か」を出します。
-
K2 — 「このペアは $x$ も $y$ も前者が大きい」もしくは「このペアは $x$ も $y$ も前者が小さい」場合に $1$ を立てます ($x$ と $y$ の順位が関連しているなら多くのペアで $1$ が立ってほしい)。この和が
Pです。 -
L2 — 「このペアは $x$ は前者が大きいのに $y$ は後者が大きい」もしくは「このペアは $x$ は後者が大きいのに $y$ は前者が大きい」場合に $1$ を立てます ($x$ と $y$ の順位が関連しているならあまり立ってほしくない)。この和が
Qです。 -
M2 — 「このペアは $x$ が同じ値である」場合に $1$ を立てます (このようなペアは $x$ と $y$ の大小関係が揃っているとも逆転しているとも値が等しいともいえないので、このようなペアが多いほど相関係数を小さく補正します)。この和が
Tです。 -
N2 — 「このペアは $y$ が同じ値である」場合に $1$ を立てます (このようなペアが多いほど相関係数を小さく補正します)。この和が
Uです。 -
Q2, Q3, Q4, Q5 — 実際に
P, Q, T, Uを出します (セル式はそれぞれ K, L, M, N 列のG 列の値が存在する行範囲の和をとるものですが、シートの下の方で別の集計をする予定がないなら単に=SUM(K:K)などで構いません)。 -
Q6 — 最後に
(P - Q) / sqrt((P + Q + T) * (P + Q + U))を出します。
セル式の説明 (p値)
https://en.wikipedia.org/wiki/Kendall_rank_correlation_coefficient#Significance_tests によれば、τb を標準化した検定統計量は、
\displaystyle z_{B}={n_{c}-n_{d} \over {\sqrt {v}}}
{\begin{array}{ccl}v&=&{\frac {1}{18}}v_{0}-(v_{t}+v_{u})/18+(v_{1}+v_{2})\\v_{0}&=&n(n-1)(2n+5)\\v_{t}&=&\sum _{i}t_{i}(t_{i}-1)(2t_{i}+5)\\v_{u}&=&\sum _{j}u_{j}(u_{j}-1)(2u_{j}+5)\\v_{1}&=&\sum _{i}t_{i}(t_{i}-1)\sum _{j}u_{j}(u_{j}-1)/(2n(n-1))\\v_{2}&=&\sum _{i}t_{i}(t_{i}-1)(t_{i}-2)\sum _{j}u_{j}(u_{j}-1)(u_{j}-2)/(9n(n-1)(n-2))\end{array}}
なので、この $v$ をそのまま Q8 で計算します (以下)。
=LET(
n, ROWS(INDIRECT($E$2)),
tx, COUNTIF(INDIRECT($E$2), UNIQUE(INDIRECT($E$2))),
ty, COUNTIF(INDIRECT($E$3), UNIQUE(INDIRECT($E$3))),
(
n*(n-1)*(2*n+5)
- SUM(tx*(tx-1)*(2*tx+5))
- SUM(ty*(ty-1)*(2*ty+5))
) / 18
+ SUM(tx*(tx-1))*SUM(ty*(ty-1))
/ (2*n*(n-1))
+ SUM(tx*(tx-1)*(tx-2))*SUM(ty*(ty-1)*(ty-2))
/ (9*n*(n-1)*(n-2))
)
そして、Q9 に $z_B$ を出し、Q10 に両側 p 値を出します。