Excelで相関係数と回帰直線を求める

Mac OSX(Excel:mac2011)を使用している場合は、 ここをクリックしてください。

1. 散布図を描く

Windows7、Windows Vista(Excel2007〜)の場合

  1. 散布図を作成したいデータ範囲を選択する。
  2. メニューバーの「挿入」をクリックする。グラフタブの「散布図」をクリックし、折れ線のない図を選択する。
  3. グラフのレイアウトタブで適当なレイアウトを選択し、タイトルや横軸、縦軸のラベルを書き入れる。


Windows XP以前(Excel97〜2003)の場合

  1. 散布図を作成したいデータ範囲を選択する。
  2. ツールバーの「グラフウィザードボタン」をクリックし、折れ線のない「散 布図」を選択する。または、メニューバーの挿入をクリックしグラフを選択し、同様に進む。
  3. 最初にデータ範囲を指定しなかった場合は、データ範囲を指定する。または、「データの範囲(D)」で、X軸(横軸)のデータの範囲を指定し、カンマ(,)の 後にY軸(縦軸)のデータの範囲を指定する(例、「F1:F60,G1:G60」と入力する)。系列が「列(L)」になっていることを確認する。そうでなかったら「列」を選択する。「次へ」をクリックする。
  4. グラフのタイトルとラベルを記入する。「グラフタイトル(T)」、「X/項目軸(C)」、「Y/項目軸(V)」のところで、グラフのタイトル、横軸のラベル、縦軸のラベルをそれぞれ記入し、「完了」をクリックする。

2. 相関係数を計算する

関数を使用して計算する

  1. 相関係数を表示したいセルをアクティブにしておく。
  2. メニューバーの数式をクリックする。
  3. ツールバーの関数[fx]をクリックし、「関数の分類 (C)」で「統計」を選択し、「関数名(N)」で「CORREL」を選択し、「OK」を クリックする。

  4. 配列1のところにxのデータのセルを範囲指定する。
  5. 配列2のところにyのデータのセルを範囲指定する。
  6. 「OK」をクリックする。


データ分析ツールを使用して計算する

Windows7、Windows Vista(Excel2007〜)の場合

  1. メニューバーの「データ」をクリックし、「データ分析」を選択する。
  2. 「分析ツール(A)」メニューの中から「相関」を選択し、「相関」画面の設定を行う。
  3. 「入力範囲(I)」には、処理したいデータが入っているセルの範囲を指定する。
  4. 「データ方向」は、「列」を選択する。
  5. もし、指定した入力範囲に項目名などの註釈行が含まれている場合は、「先頭行をラベルとして使用(L)」をクリックする。
  6. 「出力オプション」は、「出力先(O)」でセルを指定するか、「新規又は次のワークシート(P)」を選択する。
  7. 「OK」ボタンをクリックする。


Windows XP以前(Excel97〜2003)の場合

  1. メニューバーから「ツール(T)」-「分析ツール(D)」を選択する。
  2. 「分析ツール(A)」メニューの中から「相関」を選択し、「相関」ウィンド ウの設定を行う。
  3. 「入力範囲(I)」には、処理したいデータが入っているセルの範囲を指定する。
  4. 「データ方向」は、「列」を選択する。
  5. もし、指定した入力範囲に項目名などの註釈行が含まれている場合は、「先頭行をラベルとして使用(L)」をクリックする。
  6. 「出力オプション」は、「出力先(O)」でセルを指定するか、「新規又は次のワークシート(P)」を選択する。
  7. 「OK」ボタンをクリックする。

3. 回帰直線を散布図の中に描く

Excel2007以降の場合

  1. 作成した散布図のデータ点を右クリックして、「近似曲線の追加(R)」を選択する。または、作成した図を選択し、タブの「レイアウト」→「分析」のところで「近似曲線」→「その他の近似曲線オプション(M)」をクリックする。

  2. 「近似曲線の書式設定」画面が現れるので、「線形近似(L)」のグラフを選択する。
  3. 「グラフに数式を表示する(E)」と「グラフにR-2乗値を表示する(R)」の前にチェックを入れる。

  4. 「OK」をクリックする。


Excel97〜2003の場合

  1. 作成した散布図のどこかをクリックして、そのグラフをアクティブにする。
  2. メニューバーの「グラフ(C)」をクリックし、「近似曲線の追加(R)」を選択 する。
  3. 「近似曲線の追加」ウィンドウが現れるので、「線形近似(L)」のグラフを選択する。
  4. 「グラフに数式を表示する(E)」と「グラフにR-2乗値を表示する(R)」の前にチェックを入れる。
  5. 「OK」をクリックする。

Excel97以前の場合

  1. 作成した散布図中の点をダブルクリックして、そのグラフの点をアクティブにする。
  2. メニューバーの「挿入(I)」をクリックし、「近似曲線の追加(R)」を選択する。
  3. 「近似曲線の追加」ウィンドウが現れるので、希望する近似曲線の種類を選 択する。
  4. 「グラフに数式を表示する(E)」と「グラフにR-2乗値を表示する(R)」の前にチェックを入れる。
  5. 「OK」をクリックする。



Kazushi Neichi
Department of Business Administration, Tohoku Gakuin University.