Excelで売上と広告費、勉強時間と点数など、2つの数値データの関係を調べたい場面では、相関係数が役立ちます。
関数を入力すれば短時間で計算でき、散布図を組み合わせると、数値だけでは見えにくい傾向も確認できます。
相関係数は2つの数値の動き方の似ている度合いを表す指標です。
値はマイナス1から1の範囲になり、1に近いほど正の相関、マイナス1に近いほど負の相関を示します。
この記事では、1行目に見出しがある表を例に、CORREL関数、PEARSON関数、散布図を使った確認方法を解説します。
数値を正しく読み取り、業務や分析に活用していきましょう。
エクセルで相関係数を求める方法1【CORREL関数】
| A列 | B列 | C列 |
|---|---|---|
| 広告費 | 売上 | 相関係数 |
| 120 | 850 | 0.96 |
| 180 | 1120 | |
| 240 |
それではまず、最も基本的なCORREL関数で相関係数を算出する方法について解説していきます。
CORREL関数の入力位置
CORREL関数は、比較したい2つの数値範囲を指定して相関係数を返す関数です。
たとえば、A列に広告費、B列に売上があり、1行目が見出しなら、C2などの空いたセルを計算結果の表示場所にします。
見出しであるA1とB1は範囲に含めず、実際の数値が始まる2行目から指定することが重要です。
=CORREL(A2:A11,B2:B11)
この数式では、A2からA11までの広告費と、B2からB11までの売上を同じ行どうしで比較します。
片方だけの範囲をA2:A10のように短くすると、正しい比較ができないため注意しましょう。
【操作のポイント】比較する2つの範囲は、行数と列数を必ずそろえます。
数式の入力手順
数式を表示したいセルをクリックし、数式バーまたはセル内にイコールから入力します。
関数名を入力した後で、1つ目の範囲、カンマ、2つ目の範囲の順に指定し、Enterキーを押します。
たとえばC2セルに数式を入れる場合は、C2を選択してから、A2:A11とB2:B11をドラッグして指定しても構いません。
範囲を選択すると数式内にセル参照が自動入力されるため、入力ミスを減らせます。
CORREL関数の構文は、CORREL 配列1 配列2です。
配列1と配列2には、比較対象となる数値のセル範囲を入力します。
結果が小数で表示されたら、必要に応じてホームタブの小数点表示桁数を調整すると読みやすくなります。
【操作のポイント】結果セルの表示形式を小数第2位または第3位にすると、資料に載せやすくなります。
結果の確認方法
算出された値が0.96であれば、広告費が増えるほど売上も増える傾向がかなり強いと読み取れます。
相関係数が0に近い場合は、直線的な関係が弱いことを示します。
一方で、相関係数が高くても、広告費が売上増加の原因であるとは限りません。
季節、キャンペーン、店舗数など、両方の数値に影響する別の要因があるかもしれません。
そのため、相関係数は因果関係を断定する数値ではなく、データの関係を見つけるための出発点として扱うのが適切です。
【操作のポイント】高い数値が出ても、データの背景と対象期間を合わせて確認します。
相関係数の数値と判断の目安
| 相関係数 | 一般的な見方 | 関係の方向 |
|---|---|---|
| 0.7以上 | 強い関係 | 正の相関 |
| 0.3から0.7未満 | 中程度の関係 | 正の相関 |
| マイナス0.7以下 | 強い逆方向の関係 | 負の相関 |
続いては、計算結果をどう解釈するか、相関係数の数値の目安を確認していきます。
正の相関の読み取り
相関係数がプラスなら、片方が増えるともう片方も増える傾向があります。
たとえば広告費と売上、学習時間と得点、気温と飲料販売数などは、正の相関が現れる場合があります。
0.7以上は強い正の相関と表現されることが多い値です。
ただし、業種やデータ数によって適切な評価は変わるため、数値だけで結論を急がないことが大切です。
【操作のポイント】同じ期間、同じ単位で集めたデータを使うと比較の信頼性が上がります。
負の相関の読み取り
相関係数がマイナスなら、片方が増えるほどもう片方が減る傾向があります。
たとえば値引き率が上がるほど利益率が下がる場合や、欠勤日数が増えるほど出勤日数が減る場合が該当します。
マイナス0.9は、値の動く方向がかなり反対であることを表します。
負の数だから悪いという意味ではなく、逆方向の関係を把握できたという分析結果です。
相関係数の符号は関係の向き、絶対値は関係の強さを確認するために使います。
【操作のポイント】マイナス記号を見落とさず、増減の方向を文章でも説明します。
相関が低い場合の考え方
相関係数が0.1やマイナス0.1のように0に近い場合、直線的な関係はほとんど見られません。
しかし、関係がまったくないと断言できるわけではありません。
曲線的な関係、特定の条件だけで現れる関係、データ入力の誤差などが隠れている可能性があります。
相関が低いときほど、散布図で点の配置を確認する価値があります。
【操作のポイント】0に近い値でも、外れ値や条件別の傾向を確認します。
エクセルの散布図による相関の確認
| A列 | B列 |
|---|---|
| 学習時間 | テスト得点 |
| 1 | 52 |
| 2 | 61 |
| 3 | 75 |
続いては、散布図でデータの形を視覚的に確認する方法を解説していきます。
散布図の作成手順
散布図を作るには、見出しを含めてA1:B11のように2列のデータ範囲を選択します。
次に、挿入タブのグラフグループから散布図を選び、マーカーのみの散布図をクリックします。
横軸には通常、原因側として見たい数値を置き、縦軸には結果側として見たい数値を置きます。
【操作のポイント】データは途中に空白行を入れず、連続した表として選択します。
近似曲線と決定係数
散布図上の点を右クリックし、近似曲線の追加を選択すると、データの傾向を示す直線を表示できます。
近似曲線の書式設定で、グラフに数式を表示する、グラフにR-2乗値を表示する項目にチェックを入れます。
R-2乗値は決定係数とも呼ばれ、相関係数を二乗した値です。
決定係数 R-2乗 ＝ 相関係数の二乗
たとえば相関係数が0.8なら、決定係数は0.64になります。
決定係数は、作成した直線がデータのばらつきをどの程度説明できるかを見る目安です。
【操作のポイント】相関係数と決定係数は異なる数値なので、用途に応じて使い分けます。
外れ値の見つけ方
散布図では、ほかの点の集まりから大きく離れた外れ値を見つけられます。
外れ値は入力ミスの場合もありますが、特別なイベント、異常な取引、大口顧客の影響など、分析上重要な事実を示す場合もあります。
外れ値を削除する前に、元データと発生理由を必ず確認しましょう。
外れ値を含む場合と除いた場合で相関係数を比較すると、特定のデータが分析結果に与える影響を確認できます。
【操作のポイント】グラフの点にマウスポインターを重ねると、対応する値を確認できます。
PEARSON関数と分析ツールの活用
| 関数 | 数式例 | 用途 |
|---|---|---|
| CORREL | =CORREL(A2:A11,B2:B11) | 相関係数の算出 |
| PEARSON | =PEARSON(A2:A11,B2:B11) | 積率相関係数の算出 |
続いては、CORREL関数以外の選択肢と、複数項目を比較する場合の方法を確認していきます。
PEARSON関数との違い
PEARSON関数も、CORREL関数と同様にピアソンの積率相関係数を計算します。
通常の数値データを比較する目的では、両方の関数で同じ結果になります。
新しく数式を作る場合は、短く覚えやすいCORREL関数を使うと扱いやすいでしょう。
既存の帳票でPEARSON関数が使われている場合は、数式の統一性を保つため、そのまま利用しても問題ありません。
【操作のポイント】関数名の違いよりも、対象範囲が正しいかを優先して確認します。
データ分析ツールによる相関表
売上、広告費、来店者数、客単価のように3項目以上を比較する場合は、データ分析ツールが便利です。
データタブにあるデータ分析から相関を選択すると、複数列の相関係数を一覧表として出力できます。
データ分析が表示されない場合は、ファイルタブのオプションからアドインを開き、分析ツールを有効にします。
入力範囲には見出しを含む表全体を指定し、先頭行をラベルとして使用する設定を選びます。
項目数が多いときほど、相関表と散布図を組み合わせると、重要な関係を絞り込みやすくなります。
【操作のポイント】複数列の分析では、各列の単位と集計期間をそろえます。
エラーと空白セルの対処
範囲内に文字列、エラー値、対応しない空白が多く含まれると、計算結果が期待どおりにならないことがあります。
データの途中にハイフンや未確認という文字が入力されている場合は、数値として扱える形に整える必要があります。
相関分析の前に、数値の単位、欠損値、入力形式を点検することが精度を左右します。
月別データなら、広告費と売上の月がずれていないか確認します。
同じ行どうしが同じ対象を表していることが、相関係数の前提です。
【操作のポイント】エラーが出たときは、両方の範囲の行数と数値以外のセルを確認します。
相関係数を活用するときの注意点
| 確認項目 | チェック内容 |
|---|---|
| 期間 | 同じ月、同じ週などで対応しているか |
| 単位 | 円、千円、件数などが混在していないか |
| 背景 | 第三の要因が影響していないか |
続いては、相関係数を実務で誤解なく使うための注意点を解説していきます。
相関関係と因果関係
相関関係とは、2つの数値が一緒に動く傾向です。
因果関係とは、一方の出来事がもう一方の結果を生み出す関係です。
相関があることと、片方が原因であることは同じではありません。
たとえば夏にアイスの売上と水難事故がともに増えても、アイスが事故を起こしているわけではありません。
この場合は気温や季節という第三の要因が、両方に影響しています。
【操作のポイント】分析結果には、考えられる第三の要因も併記します。
データ数と対象期間
データ数が少ないと、偶然の値によって相関係数が大きく変わることがあります。
数件だけのデータで強い相関が出ても、期間を広げると傾向が変わるかもしれません。
月次なら最低でも複数年、日次なら繁忙期と閑散期を含めるなど、目的に応じた期間を選びます。
分析前には、対象件数、対象期間、集計条件を記録しておくと再現性を保てます。
【操作のポイント】比較対象の片方だけが欠けている行は、安易に補完せず理由を確認します。
分析結果の共有方法
報告書では、相関係数だけを示すより、散布図、対象期間、データ件数を併せて示すと伝わりやすくなります。
数値が0.82なら強い正の相関と書くだけでなく、どのようなデータを比較した結果なのかを明確にします。
必要であれば、外れ値を含むケースと除いたケースを分けて説明する方法も有効です。
分析の結論は、数式の結果と現場の事情を結び付けて初めて実用的になります。
【操作のポイント】グラフの軸タイトルと表の見出しを具体的に付け、第三者が読み取れる資料にします。
まとめ エクセルで相関係数を求める方法（散布図・関数）
Excelで相関係数を求める基本の方法は、CORREL関数に2つの数値範囲を指定することです。
1行目に見出しがある表では、見出しを除いたA2:A11とB2:B11のような範囲を指定します。
結果が1に近いほど正の相関、マイナス1に近いほど負の相関、0に近いほど直線的な関係が弱いと判断できます。
散布図を作成すれば、データの集まり方、外れ値、近似曲線を視覚的に確認できます。
また、相関係数は因果関係を証明するものではないため、対象期間、データ数、第三の要因も確認することが欠かせません。
CORREL関数と散布図を併用し、数値とグラフの両面からデータを読み解いていきましょう。
