【相関分析】エクセルでデータの相関性を調べる方法とポイントを徹底解説【CORREL関数】

 

この記事では、「エクセルでデータの相関性を調べる方法とポイント」を解説していきます。

・この記事を読むと、エクセルで相関分析を簡単に行う事ができるようになります。

「エクセルでヒストグラムを作る方法」も解説していますので、併せて確認してみてください！

まず、相関関係とは、エアコンの温度と電気代や、ウェブサイトの閲覧数と広告収入といった、2つのデータに直線的な関係があるか？を見ることをいいます。

（直線的とは変数Xが増えると変数Yの値も増える状態にことをいいます）

その相関関係を表す指標に「相関係数」があります。

相関係数とは、相関の強弱を「-1～1までの数値」で表していて、+1に近い場合は「プラスの相関」が、-1に近い場合は「マイナスの相関」があります。

エクセルで相関関係を調べたいときのポイントとしては、

ポイント

・CORREL関数で相関係数を算出

・散布図を作る

・近似曲線を引く

・近似式から予測値を推測

があります。

サンプルの例をみながら一つずつ確認していきましょう。

エクセルで相関係数を調べる方法【CORREL関数】

エクセルで2つのデータの間に相関性があるかを調べたい場合は最初に、CORREL関数を使用して「相関係数」を求めます。 

CORREL関数は

=CORREL(データ範囲1 , データ範囲2)

と入力することで、2つのデータの相関係数を算出してくれる関数です。

サンプルでは特性値Aと特性値Bのデータ範囲を参照して、相関係数を求めています。 

相関係数が「0.96」だったので、特性値Aと特性値Bにはプラスの相関があることが分かりました。

相関係数は

相関係数

-1：強いマイナスの相関（Aが増えればＢが減る）関係にある

0：相関関係はない

+1：強いプラスの相関（Aが増えればBも増える）関係にある

と相関性の強弱を数値で表してくれます。 

エクセルで相関関係を表す散布図を作る方法

相関性を表す指標に「相関係数」があることを上述で解説しましたが、CORREL関数の数値だけをみては、データに極端な数値がある場合などには誤りが生じてしまいます。

そこで、CORREL関数の数値だけではなく、散布図のグラフを作成して、2つのデータの関係性をプロットします。

まずは、特性値Aのデータを範囲選択し、Ｃtrlキーを押しながら、特性値B のデータ範囲を選択します。 

リボンの挿入タブのグラフカテゴリーにある散布図の絵をクリックしましょう。

サンプルの図で赤枠で囲っているグラフです。 

グラフが作成されました。

このグラフを見ると、プロットが右上がりになっていて、特に外れ値なども見当たりません。

CORREL関数が示した相関係数「0.96」が信頼できる数値であることが分かりました。

散布図に近似線を引く方法

相関係数を算出して、グラフ（散布図）を作成したことで、特性値Aと特性値Bの2つのデータが「プラスの相関関係」にあることが分かりました。しかし、これだけでは、「特性値Aが増えれば、特性値Bも増えます」ということが分かっただけです。

グラフに近似線を引いて、そこから、未知の値を推測できるようにしましょう。ますは、グラフのプロットをクリックします。 

グラフのプロットを選択した状態で右クリックをします。

選択メニューが表示されるので、その中から「近似曲線の追加」をクリックします。 

散布図のグラフに近似曲線を引くことができました。 

「近似曲線の書式設定」の中の「グラフに数式を表示する」と「グラフにＲ-2乗値を表示する」にチェックを入れます。 

 データから予測値を算出する方法

グラフに近似線の「数式」と「R²」が表示されました。

近似式のＸに値を代入するとＹの値、今回は特性値Ｂの値を予測することができます。

サンプルでは特性値Aに「50」と入力しました。特性値Bを予測してみましょう。

予測の方法は「FORCAST関数を使用する方法」と「近似式を使用する方法」があります。 

FORCAST関数を使用した予測値算出方法

数値を予測する関数にFORCAST関数があります。

FORCAST関数は

＝FORCAST(X , Yのデータ範囲 , Xのデータ範囲)

と入力することで、Ｘに対応したＹの値を算出してくれます。 

FORCAST関数での結果は特性値Aが「50」の場合は特性値Bは「-13.34」となりました。 

近似式を使用した予測値算出方法

近似式を使う場合は、近似式の変数Xに50を代入すればいいです。

=2.6357*A11-145.13

と近似式に特性値50を代入します。 

近似式を使用した予測値の結果は「-13.35」とFORCAST関数を使用した場合と非常に近い値となりました。 

予測値を出したい場合は、二つのデータに相関性がきちんとあると分かっていれば、「FORCAST関数」を使用し、相関性を確認して予測したい場合は「近似式」を使用すればいいですね。

まとめ　エクセルでデータの相関性を調べる方法とポイント

この記事では、「エクセルでデータの相関性を調べる方法とポイント」を解説していきました。

CORREL関数を使用すれば簡単に相関係数を算出することができます。また、グラフ【散布図）を作成して、近似式を出すことで、予測値を求めることもできました。

相関性があると分かっている場合はFORCAST関数を使用すれば、すぐに予測値を求めることができます。

以上、エクセルでデータの相関性を調べる方法の解説でした。