Excelデータ分析の基本ワザ (43) 相関係数の計算方法と注意点

前回の連載では、関数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乗」から求めていこう。それぞれの値を求める数式は、以下の図のようになる。