エクセルで相関係数を計算する方法！

エクセルでデータを詳しく分析することで新たなビジネスチャンスを創出したり、業務を効率化できることがあります。データ分析のための方法の一つが、「相関係数」です。ここでは、他のデータ分析の手法との違いや、エクセルのグラフや関数を使った求め方、エクセルを使う際の注意点を紹介します。

エクセルの相関係数とは？

相関係数は、2つのデータの直線的な関係の強さを示す指標です。-1から+1の間の値で表し、1に近いほど「正の相関」にあり、逆に-1に近いと「負の相関」があるとされます。そして、0に近いほど相関はありません。相関係数は、ものごとの傾向や目安を具体的に数値化して明らかにできるメリットがあります。相関係数を求める公式は複雑です。数学が得意な方でないと対応が難しいですが、エクセルを使うと比較的簡単に相関係数を求めることができるでしょう。

相関係数と回帰分析の違い

相関係数は2つの変数の関係の強さを示しますが、回帰分析は1つの変数からもう一方の変数を予測するのに使います。例えば、新規店舗出店を考えているスーパーが、その地域の人口と売上高の関係を見るのが相関係数となり、地域の人口が何人くらいだとどれほどの売上が見込めるか予測するのが回帰分析と言えます。

相関係数と因果関係の違い

相関係数はそれぞれの数値との関連性を分析することができますが、因果関係は原因と結果にフォーカスしています。

例えば、来店回数が多い顧客の方が購入金額が高いという相関関係が見られたとします。ですが、それは気に入った商品があってリピートする客が何度も足を運んだ結果であって、すべての顧客の行動結果とは言えないかもしれません。単に利用回数を増やす施策を講じても、魅力的な商品がないなどの理由で相関係数で得たのと同様の結果が得られない可能性もあります。これまで考えなかった要素を考慮し、因果関係を追求する姿勢が大事になるでしょう。

エクセルのグラフを用いて相関係数を求める方法

「相関係数」と聞くと難しそうに感じますが、視覚的に2つのデータを比較すると、データ分析の結果は一目瞭然になります。エクセルを使うとデータを活用して、容易にグラフ化できるので便利です。エクセルではさまざまなグラフの種類がありますが、相関係数は「散布図」を使うとわかりやすく表示できます。ここでは、エクセルのグラフ機能を使った相関係数の求め方をご紹介します。

エクセルの散布図を使う方法

エクセルの散布図を使って相関係数をグラフ化する際は、相関係数の計算に使用するデータそのものを使います。

前述のスーパーの例で考えると、来店客数とその日の売上高のデータなどが当てはまるかもしれません。一定期間内の2つのデータを2列あるいは2行で表示し、それらを範囲選択します。その際、見出しは範囲選択に含めません。データのみを範囲選択した状態でツールバーから「挿入」を選択し、グラフグループの中から散布図を選びます。そうすると散布図の種類が出てくるので、左端の散布図を選択するとグラフが表示されます。あとは、グラフタイトルなどの変更したい点をダブルクリックして修正し、グラフの体裁を調整すると良いでしょう。

エクセルで関数を用いて相関係数を求める方法

相関係数はエクセル関数を使って表示することもできます。数字一つで表されるため、相関関係の有無がはっきりわかるでしょう。相関係数は「CORREL関数」と「PEARSON関数」を使うと簡単に求められます。ここでは、それぞれの関数の違いについても記載します。

CORREL関数あるいはPEARSON関数を使った相関係数の求め方

CORREL関数やPEARSON関数は、同じ方法で相関係数を求められます。ここでは、CORREL関数を例にとって説明していきます。

まず、相関係数を表示させたいセルを選択します。そして、ツールバーに表示されている「オートSUM」の右下矢印をクリックし、「その他の関数」を選ぶと関数を選択できる画面に遷移します。関数の分類を「すべて表示」に変え、検索ボックスで「CORREL」と入力して該当の関数を選んで「OK」ボタンを押下します。そうするとCORREL関数実行に必要な「配列1」「配列2」を入力する欄が表示されます。

相関係数を求める場合は、関係性がある2つのデータ群を持っているはずなので、それぞれの配列に見出しを含まないデータ群を種類ごとに分けて範囲指定します。配列1と配列2の範囲指定が終わったら、「OK」で確定すると計算した相関係数の数値が表示されます。

それぞれの関数の違いとは？

CORREL関数とPEARSON関数は同様の方法で使い、得られる結果も一緒ですが、データの状況に合わせて使い分けると良いでしょう。

CORREL関数はデータに不備があっても計算結果を返しますが、PEARSON関数を使うとエラーが出ます。つまり、データが完全でなくても計算結果を知りたい場合はCORREL関数を、データの不備があるかどうかを判断するのにPEARSON関数を使うと、適切に検証できるかもしれません。

相関係数の活用事例

相関係数はさまざまな分野に活用されています。活用されている分野や内容の一部を紹介します。

マーケティング

マーケティング戦略の効果を検証するために、相関係数が用いられています。広告費と売上高、あるいはキャンペーンと売上高を相関係数で表すことで、広告の効果を見極めている場合もあります。

金融

株価や為替レートの推移を見るのに相関係数が用いられています。異なる業界や資産クラスの株価や為替レートに相関関係があることがわかれば、一方に値の変動があると他のグループのものも変動すると予測できます。このような手法で効率よく利益を生み出すことに貢献しています。

気象

気温・降水量・湿度などの気象データの関係性を評価するのに相関係数が用いられ、より精密な気象予測モデルの構築などに役立てられています。

医学

薬の投薬量と数値化した患者の状態を元に相関関係を調べ、薬の効果を割り出す取り組みが進められています。

教育

成績と勉強時間の相関関係を調査し、学習効果の把握や教育施策の策定に活用されています。

エクセルで相関係数を計算する際の注意点

エクセルのグラフや関数を使うと簡単に相関係数がわかりますが、実は落とし穴もあります。ここでは、エクセルで相関係数を計算する際に、頭に置いておいておきたい点を説明します。

データの背景を考える

データは、人為的に操作できる場合もあります。例えば、店舗売上が見込まれる日に店員の数を増やすことは当然のように行われますが、計算結果だけを見ると店員数が多い日に売り上げが伸びると勘違いする人も出てくるでしょう。因果関係を見極めてデータや計算結果を考えることは重要です。

外れ値の存在

「外れ値」とは、他のデータに比べて極端に大きい、もしくは小さい値のものを言います。データ量が少ない場合は特に、イレギュラーな数値が入り込んでいると結果に大きく影響することがあるかもしれません。例えば、特定の地域の傾向を見極めるのに相関係数を求めて、それが結果のすべてと考えてしまう、といったことはビジネス展開を考える際には避けたいものです。

グラフと関数の両方を使う

エクセルで相関係数を計算する場合は、関数だけでなくグラフも作るようにしましょう。前述で「外れ値」について記載しましたが、関数で相関の有無がわかっても、データの分布図からまったく違う結果が見られることもあります。異なる視点から検証すると、より正しい答えが得られるでしょう。

エクセルのグラフや関数を使った相関係数の求め方と注意点まとめ

相関係数は2つのデータの関係性を明らかにするデータ分析の方法で、エクセルのグラフ機能や関数を使って計算できます。エクセルでの求め方は簡単ですが、データの特性や背景を理解していないと本当に求めたい結果が得られないため、注意が必要です。