Excel2013:相関

Excel2010の人はこちらへ

まずはこのファイル(seiseki.xlsx)をダウンロードします。

相関分析 は、二つのデータが関係あるかどうかを調べることです。
データxとデータyの組み合わせがあったとき

1)まず、xとyの平均を求めます



2)次に、以下のようにSを計算します



3)そして、最後に以下のように相関係数Rを求めます。



これはExcelではCORREL関数で一発で求めることが出来ます。



このようにして求めたRは、-1から1の間の値をとります。

xが増えるとyも増えるという関係のとき(正の相関)、1に近い値が、
xが増えるとyが減るという関係のとき(負の相関)は、-1に近い値が
xとyは関係ないというときは、0に近い値が出てきます。

どの辺に境界があるかは、必ずしも決まっていませんが、だいたいの目安として、相関計数Rが

1≧R≧0.7 : 強い正の相関がある
0.7≧R≧0.4 : 正の相関がある
0.4≧R≧0.2 : 弱い正の相関がある
0.2≧R≧-0.2 : 相関がない
-0.2≧R≧-0.4 : 弱い負の相関
-0.4≧R≧-0.7 :負の相関がある
-0.7≧R≧-1 : 強い負の相関


といえます。

相関係数の実際

科目間の相関係数を調べる前に、いくつかの典型的な例を見てみます。
下の相関1をクリックして、相関1のシートを出します。

まず、このxとyの関係をグラフにしてみます。
xyのデータを選択して、挿入→散布図 で、グラフにします。

これは、xが増えると、yが増えます。
(y=2x という関係になっている) このときの相関係数を計算してみましょう。
相関係数は

=correl(データ1の範囲、データ2の範囲)

というように書きます。今の場合、具体的には

=correl(b3:b18,c3:c18)

です。
:は「から」という意味でした。だから 「B3からB18までのデータと、C3からC18までのデータを比較して、相関係数を求めなさい」という意味になります。

結果は、相関係数が1となります。



同様にして、相関2,3,4,5のシートのデータも調べてみます。

相関2のデータは、y=5x という関係になっています。
これも、相関1と同じように、相関係数は1です.
相関係数は、xが増えるとyが増えるかどうかを調べますが、何倍になるかは関係ありません。
だから、相関1のy=2xも、相関2のy=5xも、同じ相関係数になります。

相関3は、xが増えるとyが減る例です。(y=32-2x) この場合はの相関係数は-1です。

相関4は、xとyに関連性がない場合です。
こういう場合は、相関係数が0に近い値になります。

相関5は、xが増えるとyが増えるように見えますが、相関1、2ほどはっきりしない場合です。
相関係数は0.670782となりました。(正の相関はあるが、強い相関はない)



相関係数の性質がわかったので、最初のシート(「成績」と書いてあるシート)に戻ります。
国語と算数の相関係数を求めます。

=correl(e4:e23,f4:f23)

ほかの相関係数を求めましょう。

国語と理科
=correl(e4:e23,g4:g23)

国語と社会
=correl(e4:e23,h4:h23)

算数と理科
=correl(f4:f23,g4:g23)

算数と社会
=correl(f4:f23,h4:h23)

理科と社会
=correl(g4:g23,h4:h23)

「国語と社会」、「算数と理科」の相関係数が1に近い値です。
したがって、国語の成績がよい子は社会の成績もよい」、「算数の成績がよい子は理科の成績もよい」ということがわかります。
「算数と社会」、「理科と社会」は相関係数が0に近い値です。
したがって、「算数の成績がよい子は、社会の成績がよいとは限らない」「理科の成績がよい子は社会の成績がよいとは限らない」ということがわかります。
「国語と算数」、「国語と理科」の相関係数は一応0.2を超えているので「弱い相関がある」といってもいいですが、ほぼ0.2で、相関があるかないかの境 界線近くなので、「弱い相関があるように見えるが、相関があるかないかははっきりしない」というような結論になると思います。