前回の連載では、関数CORREL()を使って相関係数を求める方法を紹介した。今回は、相関係数を自分の手で算出する方法を紹介してみよう。計算方法を知ることで、相関係数にどのような意味があり、どのような点に注意すべきか、の理解を深められるだろう。

関数CORREL()を使った相関係数の算出

前回の連載では、Web広告の「表示回数」と「売上」にどれくらいの相関性があるかを調べるために、関数関数CORREL()を使って相関係数を求めた。その結果は以下の図のようになり、「商品A」はWeb広告の表示回数と売上に「強い相関性がある」という結論を導き出した。

  • 関数CORREL()で相関係数を算出した表

しかし、関数CORREL()が「どのような計算をしているか？」を知らないままでは気持ちが悪い、という方もいるだろう。関数は非常に便利なものであるが、その処理内容をよく理解しないまま利用すると、大きなミスを犯してしまう恐れがある。そこで、相関係数を自分の手で計算する方法を学んでおこう。データの相関性を調べるときの参考にしていただければ幸いだ。

偏差（平均との差）の算出

相関係数を算出するには、最初に「偏差」を求めておく必要がある。偏差というと難しく聞こえるかもしれないが、要は「平均値との差」のことである。

まずは、関数SUM()と関数AVERAGE()を使って、それぞれのデータの「合計」と「平均」を求める。

  • 合計と平均の算出

続いて、各データの偏差を求めていく。この値は（データ）－（平均値）で計算できる。数式をオートフィルでコピーできるように、（平均値）の部分は絶対参照で指定しておくとよい。たとえば、1週目の「表示回数」の場合、偏差を求める数式は以下の図のようになる。

  • 偏差の算出（表示回数）

同様に、1週目の「売上」についても偏差を算出する。この数式は以下の図のようになる。

  • 偏差の算出（売上）

これらの数式をオートフィルでコピーすると、全データの「偏差」を求めることができる。

  • 偏差を算出した表

※上記の例では、「偏差」のセルの「小数点以下の表示桁数」を0に設定している。このため、小数点以下が省略（四捨五入）された数値が表示されることになる。なお、実際に保持されている値は、小数点以下を含む数値となる。

相関係数の算出方法

これで相関係数を求めるための準備は完了。さっそく、相関係数を求めていこう。相関係数を求める計算式は、以下のように示されるのが一般的だ。

  • 相関係数を求める数式

とはいえ、これでは内容を理解できない方もいるだろう。そこで、もっと簡単に示した計算式を示しておこう。以下の計算式では、各データの「偏差」をXとYで示している。今回の例の場合、「表示回数の偏差」がX、「売上の偏差」がYになると考えればよい。

  • 相関係数を求める数式（簡易版）

まずは、「分子」の部分から求めていこう。（X×Y）は「表示回数の偏差」×「売上の偏差」となる。これを数式で示すと以下のようになる。

  • 「偏差の積」の算出（X×Y）

この数式をオートフィルでコピーし、関数SUM()で合計を求めると、「（X×Y）の合計」を算出できる。

  • 「偏差の積」の算出（X×Yの合計）

今回の例では、「（X×Y）の合計」は448億7037万5,000という数値になった。ここで（X×Y）について少し考察を加えておこう。それぞれの偏差（X、Y）は「平均からの差」を示している。よって、以下のような関係が成り立つ。

■どちらも平均以上
　　（プラス）×（プラス）＝プラスの値

■どちらも平均以下
　　（マイナス）×（マイナス）＝プラスの値

■どちらか一方のみ平均以上
　　（プラス）×（マイナス）＝マイナスの値

2つのデータに相関性がある場合、

　・一方のデータが「平均以上」であれば、もう一方のデータも「平均以上」
　・一方のデータが「平均以下」であれば、もう一方のデータも「平均以下」

という関係性になると考えられる。この場合、（X×Y）はいずれも「プラスの値」になる。つまり、相関性があるほど「（X×Y）の合計」は大きな値になっていくことになる。

逆に、相関性がない場合は、「プラスの値」と「マイナスの値」が入り乱れることになり、その合計は0（ゼロ）に近づいていく。

ちなみに、2つのデータが「負の相関性」にある場合は、

　・一方のデータが「平均以上」であれば、もう一方のデータは「平均以下」

という関係性になる。この場合、（X×Y）は「マイナスの値」になる。よって、「マイナスの値」が多くあり、その合計が「大きなマイナスの値」になるときは「負の相関性」があると考えられる。

以上が、相関係数の基本的な考え方となる。ただし、「（X×Y）の合計」だけでは、相関性を調べることはできない。というのも、基準となる目安がないからである。実際、先ほど計算した「（X×Y）の合計」は、448億7037万5,000という膨大な数値になっているが、これが「大きな値」なのか、それとも「0に近い値」なのかを判断することはできない。

そこで、相関係数が必ず-1～1の範囲に収まるように調整する役割を担うのが、先ほど示した計算式の「分母」の部分となる。

「分母」の部分は、「Xの2乗の合計」と「Yの2乗の合計」を掛け算し、その平方根を求めると算出できる。まずは、「Xの2乗」と「Yの2乗」から求めていこう。それぞれの値を求める数式は、以下の図のようになる。

  • 「偏差の2乗」の算出（Xの2乗）

  • 「偏差の2乗」の算出（Yの2乗）

これらの数式をオートフィルでコピーし、関数SUM()で合計を求めると、「Xの2乗の合計」と「Yの2乗の合計」を算出できる。

  • 「偏差の2乗」の算出（Xの2乗の合計、Yの2乗の合計）

あとは、相関係数の計算式に従って数式を記述していくだけだ。平方根の計算には関数SQRT()を利用すればよい。

  • 相関係数を算出する数式

この計算結果は以下の図のようになる。計算方法を間違えていなければ、関数CORREL()と同じ値が算出されるはずだ。

  • 相関係数の計算結果

少し難しい部分もあったかもしれないが、計算方法を知ることで「相関係数のおおよその考え方」を把握できたのではないだろうか。

相関係数を使用するときの注意点

前回の連載で紹介した「商品B」についても、同様の手順で相関係数を算出してみよう。もちろん、この結果は関数CORREL()を使った場合と同じになる。これは当然といえば当然の話だ。その結果は0.3776・・・という値になり、「弱い相関性がある」という結論になる。

  • 商品Bの相関係数

では、「5週目のデータ」を以下の図のように変更した場合、どのような結果になるだろうか？

  • 5週目のデータを変更した場合

この場合、変更後の相関係数は0.7588・・・という値になり、「強い相関性がある」という結論になる。わずか1組のデータを変更しただけなのに、まったく違う結論が導き出されてしまうことに驚きを感じる方もいるだろう。これが相関係数の怖いところである。

参考までに、変更後のデータを散布図で示すと以下の図のようになる。

  • 変更後のデータの散布図

相関係数は、その計算方法を見ると分かるように、「平均から大きく離れたデータ」の影響力が極めて大きい指数となる。今回の例の場合、「5週目のデータ」はいずれも平均値を大きく上回っている。よって、（X×Y）も大きな値となり、他の（X×Y）は誤差のような値になってしまう。

このように、わずか1組のデータが原因で相関係数が大きく変化してしまうケースもあり得る。相関係数を利用するときは、こういった点に十分に注意しなければならない。よって、関数CORREL()に頼るだけでなく、散布図を描いて確認してみることも大切である。