Excel。データ分析32。相関関係で値そのものが使えないときは、スピアマンの順位相関で確認
＜スピアマンの順位相関：SUM関数・COUNT関数＞
いつものように会議資料を作っているだけでは、事務職のスキルも錆びつきかねません。そこでデータ分析を通して、いつも作っている会議資料に少し数値を加えるだけで、さらに使える資料に生まれ変わることできますので、少しずつ、こんなことができるんだと知っていくことは大切なのかもしれませんね。
さて、次の表があります。
売上高と来店客数との間に関係性があるのか？ないのか？を確認する相関係数。
相関係数を求める今回の場合は、売上高と来店客数の数値は、
それぞれ、値そのものを比較することができます。
しかしながら、次の表の場合は、どうでしょうか？
人気順位と売上順位との間の相関係数を求めたくても、値そのものを比較することはできません。
相関係数を算出する、CORREL関数やPEARSON関数で算出することはできません。
そこで、順位を表す値、つまり値そのものが使えないときに、登場するのが、【スピアマンの順位相関】なのです。
このスピアマンの順位相関を直接的に算出する関数があるわけではないので、自分で算出させる必要があります。
スピアマンの順位相関の算出方法は、
対応する数値通しを減算して、その差の2乗を算出します。
算出した値の合計を算出します。
そして、次の公式に当てはめます。
1-6×合計÷（該当件数×（該当件数の2乗-1））
で、算出することができますので、早速、スピアマンの順位相関を算出していきましょう。
E列に、（人気順位－売上順位）の2乗を計算させますので、
E2の数式は、
=(A2-D2)^2
算出した合計を算出します。
E7の数式は、
=SUM(E2:E6)
そして、件数ですが、今回は、人気順位の数値を使いますので、COUNT関数で十分ですね。
E8の数式は、
=COUNT(A2:A6)
ここまで、CORREL関数やPEARSON関数なんて日ごろ見慣れない関数すら登場していませんね。
最後は、これら算出した結果を使って、スピアマンの順位相関を算出してきます。
E9の数式は、
1-6×合計÷（該当件数×（該当件数の2乗-1））
の公式に当てはめればいいので、
=1-6*E7/(E8*(E8^2)-1)
これで、スピアマンの順位相関を算出することができました。
結果をみると…正の相関があるかどうかは、いえない感じですね…
人気の高い商品が売上高も高いとは言い切れないということが、わかったというところでしょうかね。
順位相関も相関係数と同じで、
1に近くなれば、「正の相関」
0に近くなれば、「無相関」
-1に近くなれば、「負の相関」
という意味になります。
このように、日常的に使う関数と四則演算だけで、このような数値を算出することもできますので、色々、幅を広げていくといいのかもしれませんね。
