【Excel】エクセルで相関分析を行うやり方（相関係数）

エクセルで売上と広告費、勉強時間とテストの点数、気温と商品の販売数など、二つの数値の関係を調べたい場面では相関分析が役立ちます。

相関係数を求めると、感覚だけでは判断しにくいデータ同士の結び付きが数値で見えるようになります。

ただし、相関係数が高いからといって、一方がもう一方の原因だと決め付けることはできません。

相関分析では、数値の読み方とグラフによる確認を組み合わせることが重要です。

この記事では、エクセルで相関分析を行うやり方と、相関係数の見方をサンプルデータとともに解説していきます。

 

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

広告費	売上	相関係数
10	120	0.96
15	155	強い正の相関

それではまず、相関係数を最も手軽に求めるCORREL関数について解説していきます。

 

CORREL関数の入力手順

CORREL関数は、二つのデータ範囲を指定してピアソンの積率相関係数を返す関数です。

たとえばA列に広告費、B列に売上を入力し、1行目を見出しにした場合、結果を表示したいセルに数式を入力します。

この数式では、A2からA11までの広告費と、B2からB11までの売上を行ごとに対応させて比較します。

二つの範囲は、必ず同じ件数の数値データを指定することが基本です。

片方だけ10件で、もう片方が9件という状態では、正しい相関係数を計算できません。

入力後にEnterキーを押すと、小数で相関係数が表示されます。

表示桁数を整えたいときは、ホームタブの数値グループで小数点以下の表示桁数を調整しましょう。

 

データ範囲を選ぶ際の注意点

続いては、CORREL関数でデータ範囲を指定するときの注意点を確認していきます。

1行目にヘッダーがある表では、範囲をA1:A11のように指定せず、実際の数値が始まるA2から指定します。

ヘッダーの文字列を含めると、計算結果が意図と異なる場合や、データの確認がしにくくなる場合があります。

空白セルや文字列が混じっているデータも、分析前に整理しておくと安心です。

広告費と売上のように、同じ期間や同じ対象に対応する値だけを並べることが大切です。

たとえば4月の広告費と5月の売上を同じ行に置くと、時間差を意識しない限り、単純な同時期の相関としては適切ではありません。

欠損値があるときは、片方だけを削除するのではなく、対応する行を一組として扱う必要があります。

元データを別シートに残し、分析用の表を作る方法も実務では便利です。

 

関数結果の確認方法

続いては、表示された相関係数を確認する方法について解説していきます。

結果が0.85なら正の相関、マイナス0.72なら負の相関があると判断するための手掛かりになります。

ただし、0.30のような値を見て、関係がまったくないと即断する必要はありません。

業種、データ件数、対象となる現象によって、実務上意味を持つ強さは変わります。

相関係数は判断の出発点であり、数値だけで結論を固定しない姿勢が必要です。

【操作のポイント】

 

相関係数の意味と読み方

相関係数	関係の向き	読み方の例
0.82	正の相関	同じ方向に動きやすい
-0.76	負の相関	反対方向に動きやすい

それではまず、相関係数が示す正負と大きさの意味について解説していきます。

 

正の相関と負の相関

正の相関とは、一方の値が大きくなるほど、もう一方の値も大きくなる傾向です。

たとえば来店者数が増えるほど売上が増えるデータでは、正の相関が出ることがあります。

一方、負の相関は、一方の値が大きくなるほど、もう一方が小さくなる傾向を指します。

割引率と利益率の関係では、割引率が高くなるほど利益率が下がるため、負の相関が表れるかもしれません。

プラスかマイナスかは良し悪しではなく、二つの変数が動く方向を示す記号です。

マイナスの値だから問題がある、プラスの値だから望ましい、と考えるのではなく、業務の文脈に当てはめて読み取ります。

 

相関の強さを判断する目安

続いては、相関の強さを判断する際の目安を確認していきます。

相関係数は符号を除いた絶対値で強さを把握します。

0.90とマイナス0.90は向きが異なりますが、どちらも非常に強い線形関係を表します。

0.10のように0に近い値なら、直線的な関係はあまり見られない可能性があります。

しかし、二次曲線のような関係や、特定の範囲だけに現れる関係は、相関係数だけでは見落とします。

強さの判定には散布図を加え、点の並び方を必ず確認することが実用的です。

データ数が少ない場合は、数件の値が結果を大きく動かすこともあります。

 

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

続いては、相関関係と因果関係の違いについて解説していきます。

相関関係は二つの数値が一緒に変化する傾向であり、因果関係は一方がもう一方の原因になる関係です。

気温とアイスクリームの売上に正の相関があっても、アイスの売上が気温を上げているわけではありません。

共通の要因である季節や天候が、両方に影響していると考えられます。

相関分析は仮説を作るための有効な手段であり、原因を断定するためだけの手法ではありません。

【操作のポイント】

 

データ分析ツールによる相関行列

	広告費	来店者数	売上
広告費	1.00	0.78	0.81
来店者数	0.78	1.00	0.92
売上	0.81	0.92	1.00

それではまず、複数の項目を一度に比較できるデータ分析ツールの相関機能について解説していきます。

三つ以上の変数を比較する場合は、相関行列を作成すると関係を一覧で把握できます。

 

分析ツールの有効化

相関機能がデータタブに見当たらない場合は、最初に分析ツールを有効にします。

ファイルタブからオプションを開き、アドインを選択します。

画面下部の管理でExcelアドインを選び、設定をクリックするとアドインの一覧が表示されます。

その中の分析ツールにチェックを入れ、OKを選択しましょう。

設定後はデータタブの右側にデータ分析ボタンが追加されます。

分析ツールはエクセルの標準機能ですが、初期状態では表示されていない環境があります。

一度有効にすれば、通常は次回以降も同じパソコンで利用できます。

 

相関機能の実行手順

続いては、相関行列を作成する具体的な手順を確認していきます。

データタブのデータ分析をクリックし、分析ツールの一覧から相関を選択します。

入力範囲には、ヘッダーを含むA1:C11のように、比較したい複数列をまとめて指定します。

1行目に項目名がある場合は、ラベルにチェックを入れましょう。

出力先は新規ワークシートか、現在のシート内の空いているセルを指定します。

OKを押すと、各項目の組み合わせの相関係数が表として出力されます。

 

相関行列の確認方法

続いては、完成した相関行列の見方について解説していきます。

表の対角線上には、同じ項目同士を比較した結果として1が表示されます。

広告費と売上の交点、売上と広告費の交点には同じ値が表示されるため、表の上下は対称になります。

複数の数値の中から、絶対値が大きい組み合わせを探すと、優先して確認したい関係を見付けやすくなります。

ただし、似た意味の説明変数同士に非常に強い相関がある場合は、多重共線性にも注意が必要です。

回帰分析などへ進む場合には、相関行列を事前確認として活用するとよいでしょう。

【操作のポイント】

 

散布図による相関関係の確認

勉強時間	テスト得点
1	52
3	68
5	82

それではまず、相関係数では見えにくいデータの分布を確認できる散布図について解説していきます。

 

散布図の作成手順

勉強時間とテスト得点のような二列の表を、ヘッダーを含めて選択します。

挿入タブを開き、グラフグループにある散布図を選択します。

最初はマーカーのみの散布図を選ぶと、各データの位置を確認しやすくなります。

折れ線グラフではなく散布図を選ぶことで、横軸を数値として正しい間隔で扱えます。

横軸が勉強時間、縦軸が得点になっているかを確認し、必要ならグラフのデータ選択で修正します。

データの増減が右上がりなら正の相関、右下がりなら負の相関が視覚的に読み取れます。

 

近似曲線の追加方法

続いては、散布図に近似曲線を追加する方法を確認していきます。

グラフ内の点をクリックし、グラフ要素の追加から近似曲線を選択します。

単純な直線傾向を確認したい場合は、線形近似を選びます。

グラフ上に数式とR二乗値を表示する設定にすると、傾向をさらに確認しやすくなります。

R二乗値は決定係数と呼ばれ、単回帰では相関係数を二乗した値と対応します。

近似曲線は相関の有無を決める機能ではなく、データの傾向を視覚的に補助する機能です。

 

外れ値と非線形データの確認

続いては、散布図で確認すべき外れ値と非線形の関係について解説していきます。

ほとんどの点がまとまっている場所から大きく離れた点があれば、入力ミス、特殊な事例、計測条件の違いなどを確認します。

外れ値を安易に消すのではなく、なぜその値が生じたかを調べることが分析の質につながります。

また、点がU字や逆U字に並んでいる場合は、関係があるにもかかわらず相関係数が0付近になることがあります。

相関係数は直線的な関係を中心に表すため、散布図で形を見る工程を省かないことが大切です。

必要に応じて期間別、商品別、店舗別に色分けして、グループごとの差も確認しましょう。

【操作のポイント】

 

相関分析用データの整え方

月	広告費	売上
4月	100	850
5月	120	910

それではまず、信頼しやすい相関係数を得るためのデータの整え方について解説していきます。

相関分析の結果は関数の操作よりも、分析前のデータ品質に大きく左右されます。

 

同じ単位と同じ期間の設定

比較する二つの列は、同じ期間、同じ対象、同じ集計単位でそろえます。

月別広告費と月別売上を比較するなら、どちらも同じ月の値を同じ行に入力します。

一方で、広告の効果が翌月に現れる事業では、広告費を一か月ずらして比較する仮説も考えられます。

その場合は、元の同月比較と時間差を置いた比較を分けて作成し、何を比べているかを明確にします。

金額の単位が円と千円で混在していても相関係数自体は変わりませんが、表の理解を誤らないために統一したほうが安全です。

 

空白値とエラー値の処理

続いては、空白値やエラー値を含むデータの処理について確認していきます。

未入力を0として扱うのか、欠損として除外するのかで結果が変わります。

売上が未確定で空白になっている行を0として分析すると、実際には存在しない低い売上を追加することになりかねません。

エラー値があると関数でエラーが返ることもあるため、フィルターや条件付き書式で確認します。

数値として見えても文字列になっているセルがあるため、表示形式だけで判断しないことも必要です。

 

サンプル数と偏りの確認

続いては、データ件数と偏りを確認する考え方について解説していきます。

数件だけのデータでは、偶然の増減によって相関係数が大きく動きます。

可能であれば、季節変動やキャンペーン期間を含む十分な件数を集めます。

ただし、古すぎるデータを大量に加えると、現在の市場状況と合わない結果になるかもしれません。

件数を増やすことと、比較可能な条件をそろえることを両立させる視点が重要です。

特定店舗だけ、特定商品だけに偏ったデータでは、全体へそのまま当てはめないよう注意しましょう。

【操作のポイント】

 

相関分析の実務での活用

確認項目	相関分析で分かること
広告費と売上	同時期の動きの傾向
気温と販売数	天候影響の仮説

それではまず、相関分析を実務の改善につなげる考え方について解説していきます。

 

売上分析への活用

売上と広告費、来店者数、客単価、在庫数などの相関を確認すると、注目すべき指標を見付ける手掛かりになります。

来店者数と売上の相関が強い場合でも、客単価が低下していないかは別途確認が必要です。

相関係数の高い項目を見付けたら、施策の候補として検討し、実際の施策後の変化を追います。

相関分析の価値は、数字の関係を発見し、次に検証する問いを作れる点にあります。

単に相関が高い順に施策を決めるのではなく、実行可能性、費用、顧客への影響も合わせて判断しましょう。

 

人事や品質管理への活用

相関分析は売上以外にも、研修時間と業務品質、作業時間と不良率、問い合わせ件数と対応時間などの確認に利用できます。

ただし、人に関するデータを扱う場合は、個人評価を単純な数値だけで決めない配慮が欠かせません。

部署、経験年数、担当業務などの条件によって、同じ数値でも意味が変わるためです。

品質管理では、温度、湿度、材料ロット、加工時間など複数の条件を相関行列で比較する方法が役立ちます。

現場の知見と数値の結果を照らし合わせることで、改善仮説の精度を高められます。

 

分析結果を報告する工夫

続いては、相関分析の結果を報告する際の工夫について解説していきます。

報告書では、相関係数だけを並べるよりも、元データの対象期間、件数、散布図、考えられる背景を併記すると伝わりやすくなります。

たとえば広告費と売上の相関が0.81であった場合、対象期間とキャンペーンの有無も添えます。

相関があるという表現にとどめ、因果関係が確定したような書き方は避けましょう。

【操作のポイント】

 

まとめ　エクセルで相関分析を行うやり方（相関係数）

確認順	行うこと
1	対応する数値データを整える
2	CORREL関数または分析ツールで算出する
3	散布図と背景要因を確認する

エクセルで二つの数値の関係を調べるなら、CORREL関数に二つの同じ件数の範囲を指定する方法が基本です。

複数の項目を比較したいときは、データ分析ツールの相関機能を使うと相関行列を作成できます。

相関係数は1やマイナス1に近いほど関係が強く、0に近いほど直線的な関係が弱いことを示します。

一方で、相関関係と因果関係は同じではありません。

散布図で外れ値や曲線的な分布を確認し、季節、施策、対象の偏りといった第三の要因を考慮することが大切です。

データの期間と行の対応を整えたうえで、相関分析を次の検証や業務改善のヒントとして活用していきましょう。