ロジカルシンキングの知識・勉強

【5分でわかる】相関係数・回帰分析とは エクセルでの求め方

 

2つ以上の変数の相関関係を調べるための指標として相関係数と回帰分析があります。

これらが使えるようになると、2つの事象が独立しているのか依存しているのかが、わかるようになります。

この2つはエクセルでも簡単に求めることができるので、ビジネスパーソンとして最低限知っておきたい定量分析手法です。

この記事では、その相関係数と回帰分析について解説していきます。

 

相関係数とは

相関係数とは、2つの変数の相関関係を示す指標です。

 

相関係数の目安

相関係数は必ず-1~1の範囲に収まり、0.7以上あれば正の相関(片方の値が上がれば、もう片方の値が一定の割合で上がる関係)があると考えられます。

逆に-0.7以下であれば負の相関(片方の値が上がれば、もう片方の値が一定の割合で下がる関係)があるとされています。相関係数が1のときは、2つの変数は完全に正の相関があることになります。逆に-1のときは、2つの変数は完全に負の相関があることになります。

相関係数の目安

相関係数(r) 相関関係
0.7 ≦ r 強い正の相関関係
0.4 ≦ r ≦ 0.7 正の相関関係
0.2 ≦ r ≦ 0.4 弱い正の相関関係
-0.2 ≦ r ≦ 0.2 相関関係なし
-0.4 ≦ r ≦ -0.2 弱い負の相関関係
-0.7 ≦ r ≦ -0.4 負の相関関係
r ≦ -0.7 強い負の相関関係

 

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

相関係数はエクセルのCORREL関数を用いれば、簡単に求めることができます。下の表の場合は、「相関係数=CORREL(A1:J1 , A2:J2)」となります。

1 10 12 3 14 4 10 6 11 9 11
2 16 17 3 26 7 18 10 15 15 14

この場合相関係数は0.937でかなり相関が強いことになります。

エクセルでの計算式

エクセルで計算した相関係数

 

回帰分析とは

回帰分析とは、ある原因に対し、結果となる数字がどのような関係を持っているかを調べる際に用います。

例えば、原因となる値をXとして、結果となる値をYとすると、次のような式で表すことができます。

  • Y = a・X + b 
  • Y = a・Xb 
  • Y = a・eX 
  • Y = a・logX

 

回帰分析の方法

回帰分析はエクセルを使うと簡単にできます。

例えば、上記の相関係数で使った値で回帰分析用のグラフを作ると次のようになります。

回帰分析の例を示すグラフ

ここでR2値とは、ある現象がその回帰式で表される確率というように考えればOKです(したがって、例のグラフの場合9割方は描いた回帰式で説明がつくということになります)。

R2値は一般的には、0.5~0.8なら、回帰式が成立する可能性が高いとされていて、0.8以上であれば、成立する可能性がかなり高いことを示します。

なお、直線近似の場合は、R2は相関係数の2乗になります。

 

エクセルを使った回帰分析

エクセルを使って回帰分析する場合、次のようなステップになります。

グラフ上で、近似曲線を追加するを選択します。

エクセルを使った回帰分析

次に近似曲線の種類(この場合は線形近似)を選んで、「グラフに数式を表示する」と「グラフにR-2乗値を表示する」を追加します。

エクセルを使った回帰分析

 

重回帰分析の方法

重回帰分析とは、次の式のように、ある結果となる変数に対して、原因となる変数が2つ以上ある場合に行う回帰分析のことです。

Y = a1・X1 + a2・X2 + a3・X3 + ・・・ + b

列A(Y)には、列B(X1)と列C(X2)の変数からなる関係があるとします。

A B C
1 10 16 10
2 12 17 11
3 3 3 2
4 14 26 15
5 4 7 5
6 10 18 9
7 6 10 6
8 11 15 13
9 9 15 7
10 11 14 14

重回帰分析は、エクセルの標準状態では使えないので、以下のように設定する必要があります。

まず「ファイル」から「オプション」を選択します。

重回帰分析のやり方

「オプション」の中にある「アドイン」から「分析ツール」を選んで、「設定」ボタンを押します。

重回帰分析のやり方

「分析ツール」を選んで「OK」を押します。

重回帰分析のやり方

これでエクセルのメニューの「データ」の一番右側に「分析ツール」が入ります。

重回帰分析のやり方

次に「データ分析」を押して、「回帰分析」を選択し、「OK」のボタンを押します。

重回帰分析のやり方

ここで、先ほどのデータを以下のように選択します。

YがA1からA10、XがB1からC10の範囲です。

重回帰分析のやり方

ここで「OK」ボタンを押すと、別シートに以下のような数字が出てきます。

重回帰分析のやり方

ここから、重解析分析の結果として、次のような関係が導かれます。

Y = 0.29・X1 + 0.43・X2 + 0.96

 

重回帰分析では、次の2つの値に注意する必要があります。(いずれも上のやり方で自動的に出てくる数字です)

■t値
t値、エクセルを使って、回帰分析をすると自動的に出てきます。この値が大きい変数は、出力側の変数(例の場合Y)との関係性が強くなります。また、この値が2を超えているかどうかが、入力側の変数(例の場合X)として採用するかどうかの判断材料になります。

■P値
P値も、エクセルと使うと自動的に出てきます。この値が、0.05よりも大きいときは、入力側の変数(例の場合X)として採用しないほうがよいとされています。

また、重回帰分析では、相関の強い変数を2つ以上採用することは避けるべきだと考えられます。

例えば、家賃の変数として、駅からの距離と地価を変数にした場合、駅からの距離と地価に強い相関があると、どちらかの数字がt値またはP値の基準がNGになってしまいます。

 

相関係数・回帰分析を用いる際の注意点

相関係数・回帰分析を用いる際に注意すべき点が4つあります。

 

分析の前提・次のアクションにつながりを意識する

回帰分析は、現象の傾向を表すのに非常に有効なツールです。

しかし、回帰分析はやり方次第で、いくらでもそれらしい線を引くことができます。

うまく近似曲線をひけたとしても、その近似線で説明できる前提を押さえたり、その近似曲線がわかることで次にどんなアクションにつなげられるかを考えたりすることが非常に重要です。

 

相関があるからといって因果関係があるわけではない

回帰分析で高い相関が発見できても、それらに因果関係があるとまでは言い切れないません。

実際にグラフに示したみたり、定性的に考えて第3の因子を考えたりすることが重要になります。(例えば、年収に対する糖尿病患者の割合に相関があっても、2者には直接的な因果はなく、それは第3の因子である食生活との因果関係である可能性が高いといえます。)

 

相関から外れたところの扱いに注意

回帰分析をしてグラフを見ると、近似線から明らかに外れたデータが出てくる場合があります。こうしたデータにはビジネス上の大きなヒントが隠されている場合があるので注意して掘り下げてみることも必要です。もちろん単なるノイズとして、データを省ける場合もあります。

 

相関の高低の判断はビジネスの種類によって違う

相関があるというためには、相関係数が上述のように一般的に絶対値で0.7くらい(R2値だと0.5くらい)必要ですが、ビジネスの性質によってはそれ以下でも相関関係を深堀して考える場合があります。

例えば相関があった場合のリスクが極めて大きい場合などは、相関係数(あるいはR2値)が低くてもしっかり内容を調査していきます。(例えば、医薬品の副作用などは、相関があった場合のリスクが高いものの代表です)

 

まとめ

相関係数と回帰分析は、事象同士の関連性を明らかにするのに有効な手法です。しかも、エクセルを使えば、すぐに求められます。

プレゼンや資料の中に相関係数と回帰分析を織り交ぜることで、内容が一段とスマートに見えるようになることでしょう。

ロジカルシンキングのおすすめ記事はこちら>>>

 

定量分析をもっと知りたい方は

 

ビジネスでよく使われるフレームワークのまとめはこちら

【必見】ビジネスでよく使われる30個超のフレームワークを一挙紹介 このブログでも多くのフレームワークを紹介していますが、1ページ分を使って紹介するまでもないフレームワークと 自サイト内の引用を合...
ABOUT ME
アバター
セーシン
元リーマン管理職+副業歴15年、海外、起業などの経験を踏まえて、仕事やキャリアに関することを発信しています。