• 公開日:
  • 更新日:

Excel(エクセル)の関数による相関係数の求め方とグラフの作り方

Excelで相関係数を求める方法をご紹介します。関数を使用すれば簡単に求めることができます。

またアドインである分析ツールで相関係数を求める方法や、エクセルで散布図のグラフを作成する方法も解説しています。

相関係数とは?

相関係数とは、2種類のデータの直線的な関係の強さを表す指標です。−1から1までの値をとります。

1に近いほど正の相関がある、-1に近いほど負の相関がある、0に近いほど相関がないことを意味します。

相関係数(r)相関の強さ
−1.0 ≤ 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.0強い正の相関

相関係数を求める公式

相関係数は「r」の記号で表し、以下の公式で求められます。

相関係数

相関係数は、「xとyの共分散」を「xの標準偏差とyの標準偏差の積」で割ることで求めることができます。

標準偏差については以下の記事でご紹介しています。

標準偏差をエクセルの関数で求める方法とグラフの作り方(散布図)

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

手順で使用するフォーマットのダウンロードはこちら

CORREL関数を使う方法

ExcelでCORREL関数を使って相関係数を求める方法をご紹介します。CORREL関数は2つの配列の相関係数を返す関数です。

関数名を入力する

英語と数学の相関係数を求めるとします。相関係数を入力したいセル(F2)を選択し、『=CORREL(』と関数名を入力します。

配列1を指定する

配列1を指定します。ドラッグするか、『B3:B20,』と入力します。

配列2を指定する

配列2を指定します。ドラッグするか、『C3:C20)』と入力します。数式が入力できたら【Enter】キーを押します。

相関係数の結果

F2セルに相関係数「0.404142498」が表示されました。CORREL関数を使って相関係数を求めることができました。

分析ツールを使う方法

アドインの分析ツールでも相関係数を求めることができます。

分析ツールについては以下の記事でご紹介しています。

Excel(エクセル)で分析ツールを使用してデータ分析する方法

まず分析ツールを使用できるように設定します。

ファイル

【ファイル】タブを選択します。

オプション

【オプション】を選択します。

アドイン

「Excelのオプション」ダイアログボックスが表示されます。①【アドイン】、②【分析ツール】の順に選択し、③【設定】ボタンを押します。

分析ツール

「アドイン」ダイアログボックスが表示されます。①【分析ツール】にチェックし、②【OK】ボタンを押します。

分析ツールが使用できるようになりました。

分析ツール

①【データ】タブ、②【データ分析】の順に選択します。

相関

「データ分析」ダイアログボックスが表示されます。①【相関】を選択し、②【OK】ボタンを押します。

入力範囲

「相関」ダイアログボックスが表示されます。入力範囲右端の【↑】ボタンを押します。

入力範囲

①【相関係数を出したいデータ】を選択し、②【↓】ボタンを押します。

相関

「相関」ダイアログボックスに戻りました。【OK】ボタンを押します。

相関係数

新しいシートに相関係数が表示されました。

グラフ(散布図)の作り方

散布図

①【相関係数を出したいデータ】、②【挿入】タブ、③【散布図(X,Y)またはバブルチャートの挿入】、④【散布図】の順に選択します。

散布図

散布図が挿入されました。グラフのタイトルなどを適宜変更します。