エクセルにおける相関係数の求め方｜グラフ作成から活用事例までを解説

相関係数は、2つのデータにある関係性を数値化したものです。収集したデータの規則性や性質を明らかにして、分析する際に活用されています。この記事では、相関係数の意味やExcelを使った求め方、相関係数の活用事例を紹介します。データ分析をする際には、ぜひお役立てください。

サンプルデータを使って練習がしたい・・・という方向けに、本記事で使用しているデータをダウンロードできるようにご準備しております！こちらからダウンロードしてみてください。

相関係数とは2つのデータにおける相関関係を示す指標

相関係数とは2つのデータにおける相関関係を示す数値です。相関関係には以下の3つの視点があります。

• 正の相関関係
• 負の相関関係
• 無関係

正の相関関係では、片方が増加（減少）すると、もう片方も増加（減少）するというように、相関性を持った2つのデータは同じ変化をします。一方、負の相関係数では、片方が増加するともう片方が減少するように逆の動きを取ることが特徴です。相関係数は相関関係を-1から1の数値で表します。求めた相関係数が0に近ければ関係性は薄く、0から離れると関係性が強いことを示しています。

相関係数をExcel関数を用いて求める方法

相関係数は、平均値・偏差・分散・標準偏差・共分散を、順に計算することで求められます。計算工程が多くなるため、手計算で相関係数を求めるには大変な手間がかかってしまいます。Excel関数を利用すれば、効率的にこれらの計算を行うことが可能です。相関係数を求めるために利用できる関数は、「CORREL関数」と「PEARSON関数」の2種類です。

ここからは、Excel関数を用いた相関係数の算出方法を解説します。

①CORREL関数を使う方法

CORREL関数を利用して相関係数を求める手順を解説します。CORREL関数は、2組のデータを用意して、その組み合わせの相関係数を返します。計算結果は-1から1までの数値です。正の値であれば正の相関を、負の値であれば負の相関を持つことが示されます。数式の記載方法は、以下の通りです。

［=CORREL（配列1, 配列2）］
配列1、配列2は相関性を求めたいそれぞれのデータが該当します。

②PEARSON関数を使う方法

次に、PEARSON関数を利用して相関係数を求める手順を解説します。CORREL関数同様に、PEARSON関数でも2組のデータを用意して、その組み合わせの相関係数を返します。計算結果は-1から1までの数値となり、正の値であれば正の相関を、負の値であれば負の相関を持つことが示されます。

計算式の記載は以下の通りです。
［=PEARSON（配列1, 配列2）］
配列1、配列2は相関性を求めたいそれぞれのデータが該当します。

③関数を用いてエラーが出る場合とその解決方法

CORREL関数やPEARSON関数を利用した際、エラーが起こることがあります。ここでは、起こりやすいエラーの事例と解決方法をご紹介します。

■データ個数が異なる場合（結果に#N/Aと表記）
配列1と配列2でデータの個数に差がないかを確認してください。

■データが入力されていない場合（結果に#DIV/0!と表記）
配列1または配列2が空白の可能性があります。データが間違いなく入力されているかを確認しましょう。

■配列1,2のどちらかの数値（またはどちらも）が全く同じである場合（結果に#DIV/0!と表記）
配列1,2のどちらかが全く同じ数値である場合、標準偏差の計算は行えず、無効とされます。相関係数の計算結果として無相関（0）の値をとる場合は、結果に0が表示されるため、まずはデータ入力のミスを疑い、内容を確認しましょう。

CORREL関数とPEARSON関数の違いは表記のみ

MicrosoftのCORREL関数とPEARSON関数の説明を読んでも、違いが分かりづらく感じることはないでしょうか？この2つの関数は、同じ数値を結果として返しています。つまり、「どちらの関数を用いても、同じ数値の回答が得られる」といえます。

Excelのデータ分析で相関係数を求める方法

Excelの関数を利用せずに相関係数を求める方法も存在します。Excelの機能の［データ分析］というツールを用いることで、相関係数の計算が可能です。［データ分析］は、Excel上部の［データ］メニューを選択すると、多くの場合で右端に表示される項目です。

ここからは、Excelのデータ分析を用いた相関係数の求め方や、関数利用との違いについて解説します。ご利用中の環境で［データ分析］というメニューが出てこない場合の対処法も記載しています。

②データ分析メニューが出てこない場合の対処方法

ご利用中のExcelに［データ分析］という項目が見つからない場合は、アドインを有効にしてください。

■アドインを有効にする手順

1. エクセル上部の［ファイル］メニューをクリック
2. 左側のメニューより［その他］をクリック
3. ［印刷］［保存］などがあるメニューの最下部、［オプション］をクリック
4. Excelオプション画面の左にあるメニューより、［アドイン］を選択
5. ［アクティブではないアプリケーション］の中にある［分析ツール］を選択
6. Excelオプション画面の最下部にある［設定］をクリック
7. 有効なツールが出きたら、［分析ツール］にチェックを入れ、画面を閉じる

これで分析ツールを利用する準備が整いました。［データ］メニューに［データ分析］が出ていることを確認しましょう。

③データ分析を用いた相関係数の求め方

データ分析を利用できるようになると、関数を使わずに相関係数を求められます。

■データ分析を用いた相関係数の求め方

1. データから［データ分析］をクリック
2. ［相関］を選択
3. ポップアップメニューの指示に従い、相関係数を求めたい範囲を選択
4. 必要に応じて、データ抽出先などを指定し、OKをクリック

以上の手順で指定した範囲の相関係数が算出されます。

関数利用とデータ分析利用の違いは比較できる情報の個数

Excelを使って相関係数を求めるには、関数を利用する方法とデータ分析を利用する方法の2種類があります。それぞれの違いは、以下の通りです。

■関数を利用する

2つのデータを比較した相関係数を求められる

■データ分析を利用する

3つ以上のデータを比較した相関係数も求められる

例えば、高校生のテスト結果をデータとして関連性を求める場合、関数で求める場合は国語と英語、数学と理科など2教科ずつの相関のみで比較が可能です。

一方、データ分析を用いる場合には、国語と英語、国語と数学、など5教科中のすべての組み合わせの相関係数を求められます。

相関係数グラフ（分布図）の作成方法

相関係数を使ったグラフの作成は、複数の変数間の関係を可視化するのに有効です。グラフを作成する際は、まず分析したいデータを選択して、［挿入］タブから［グラフ］を選択します。次にグラフのリストから［散布図］を選択して、最後にグラフ化したいデータを指定後、OKを選択します。グラフはこれで完成です。

散布図上にある点の集まりが右上がりであれば「正の相関」が、右下がりであれば「負の相関」があるといえます。ばらついている場合は、2つのデータ間には「相関関係がない」と判断できるでしょう。

相関係数の活用事例

ここまで相関係数の求め方を説明してきましたが、実際に相関係数を用いてどのような分析ができるのでしょうか。相関係数は、2つのデータ間にある因果関係を数値化できるものです。たとえば「寒くなるとおでんの材料の売上が上がる」について相関を分析したい場合、

• 各日の気温
• 各日のおでんの材料の売上額

をデータとして相関係数を求めます。この数値が1に近ければ正の相関関係があるといえます。反対に、-1に近ければ負の相関関係にあるといえるでしょう。いずれの場合も0から離れた数値であればあるほど、関係性が強くなります。

まとめ

データ間の関連性を分析する際に用いられる相関係数は、Excelを使うことで簡単に求められます。比較したいデータの個数が異なる場合はエラーとなるため、相関係数を求める際は、データ収集の漏れがないように気を付けましょう。

「面倒な業務を効率化して残業を減らしたい」
「プロのExcel技を身に着けて社内で頼られる存在になりたい」

そんな方にはユースフルの公式LINEがオススメです！元伊藤忠商事経理でExcel実務のプロ、長内孝平（おさ）が運営しており

• Excel/PowerPointなどPCショートカット288選PDF
• Excel無料講義動画480分
• ユースフルの講座割引クーポン

などLINE会員限定の豪華4大特典を無料でプレゼント中です！ぜひコチラ↓の画像をタップして受け取ってくださいね！