エクセルで相関係数を計算しグラフ(散布図)を表示する

ある事象と事象がどの程度、相関しているかを数字で示す相関係数。エクセルのCORREL関数を使うと手軽に計算できて便利です。

相関係数とは

相関係数が示す値は以下のようになります。

  • ある2つの事象の関係性(相関)の数値化したものが相関係数
  • 取る値の範囲は-1.0〜1.0
  • 1.0に近いほど正の相関があるとみなされる
  • -1.0に近いほど負の相関があるとみなされる
  • 0に近いほど相関はないとみなされる
  • 正の相関の場合は、事象の値が上がればもう一方の事象も上がる傾向にある。また一方が下がれば同じくもう一方も下がる傾向にある
  • 負の相関の場合は、事象の値が上がればもう一方の事象は下がる傾向にある。またはその逆

エクセルでの相関係数の求め方

CORRELという関数を使うと簡単に求められます。

まずは正の相関から。こんなふうに1〜5の値をAとBの列に入力します。

そして、セルC1を選択して、

=CORREL(A1:A5,B1:B5)

と入力します。これでA列の数字すべてとB列の数字すべての相関係数を計算結果がセルに表示されます。

結果は1になるはずです。同じ値どうしの相関係数だから完全な正の相関になるわけです。

次は負の相関。こんなふうにA列に1〜5、B列に5〜1を入力します。するとC1に相関係数が-1と表示されるはずです。値が逆の方向に増減するので完全な負の相関になるわけです。

完全な相関ではつまらないので、以下のように値を適当に変えてみましょう。すると相関係数が変わるはずです。似たような並びにすると相関係数が高くなり、似てない並びにすると相関係数が低くなるのを確認できるはずです。

散布図を作る

相関をグラフ化したものを散布図と言います。これもエクセルなら簡単に作れます。

このようにデータを比較するA、B列を選択します。

メニューの挿入>グラフ>散布図を選択。散布図が作られます。

この場合、正の相関があるので右肩上がりになってますね。

散布図は参照しているセルを覚えているので、セルの値を変更すると散布図も変わります。試しにB列を以下のように変更し、負の相関となるようにします。

すると散布図も右肩下がりになります。

もっとデータにばらつきがあると散布図はこのようになります。