【Excel】エクセルの散布図で相関係数を求める方法（相関の強さ・関数）

エクセルの散布図を使うと、二つの数値データにどのような関係があるのかを視覚的に確認できます。

さらにCORREL関数やPEARSON関数を使えば、見た目だけでは判断しにくい関係の強さを相関係数として数値化できます。

売上と広告費、学習時間とテスト得点、気温と飲料販売数など、複数のデータを比較する場面で役立つ操作です。

散布図は傾向を目で確認するためのグラフであり、相関係数は関係の強さと向きを数字で確認する指標です。

この記事では、1行目に見出しがある表を例に、散布図の作成方法、CORREL関数による相関係数の計算、近似曲線の活用、結果を読む際の注意点を解説します。

数値とグラフの両方を確認すると、データに隠れた傾向をより正確に把握できるようになります。

 

エクセルの散布図で相関係数を求める方法

A列	B列	C列
広告費	売上	相関係数
10	82	=CORREL(A2:A11,B2:B11)
15	96	0.95

それではまず、散布図と関数を組み合わせて相関係数を求める基本操作について解説していきます。

 

比較対象となるデータ範囲

相関係数を正しく求めるには、比較したい二つの項目を同じ件数で並べることが大切です。

ここではA列に広告費、B列に売上を入力し、1行目にはそれぞれの見出しを入力しているものとします。

したがって、実際に計算の対象となる範囲はA2からA11、B2からB11です。

二つの範囲の行数が異なると、CORREL関数は正しい結果を返せないため、先に空白行や入力漏れを確認しましょう。

広告費が10万円の月と売上82万円の月のように、同じ月や同じ商品の値が横方向に対応していることも重要です。

対応しないデータを並べ替えてしまうと、計算自体はできても分析の意味が失われるかもしれません。

数値が文字列として入力されていると、見た目は数字でも計算対象から除外される場合があります。

セルの左上に緑色の三角形が表示されているときは、数値に変換する操作も確認しておきましょう。

 

CORREL関数の入力手順

相関係数を表示したいセルとして、たとえばC2を選択します。

そのセルに次の数式を入力してEnterキーを押します。

CORREL関数は、最初の引数に一つ目の数値範囲、二つ目の引数に比較する数値範囲を指定する関数です。

この例では広告費のA2からA11と、売上のB2からB11の間にあるピアソンの積率相関係数が求められます。

相関係数はマイナス1から1までの数値で表示され、1に近いほど強い正の相関を示します。

セルに0.95のような値が表示されたなら、広告費が増えるほど売上も増える傾向がかなり強いと考えられます。

ただし、表示桁数が少ないと差を比較しにくいため、必要に応じて小数点以下を2桁から3桁程度に設定すると見やすくなります。

 

散布図による傾向確認

相関係数を計算した後は、元のA1からB11を選択して散布図を作成します。

挿入タブを開き、グラフグループにある散布図またはバブルチャートから、マーカーのみの散布図を選びます。

横軸には通常A列の広告費、縦軸にはB列の売上が自動的に設定されます。

点が右上がりにまとまっていれば正の相関、右下がりにまとまっていれば負の相関を視覚的に確認できます。

点が広い範囲にばらばらと分布している場合は、相関係数が0に近くなる可能性があります。

一部だけ極端に離れた点がある場合は外れ値であり、相関係数を大きく変えていることもあります。

 

CORREL関数とPEARSON関数の使い分け

関数	数式例	用途
CORREL	=CORREL(A2:A11,B2:B11)	相関係数の算出
PEARSON	=PEARSON(A2:A11,B2:B11)	ピアソン相関の算出

続いては、相関係数を計算する代表的な二つの関数と、入力時に注意したい点を確認していきます。

 

CORREL関数の構文

CORREL関数の構文は、CORRELの後に二つの配列またはセル範囲を指定する形です。

配列1と配列2には、比較する二つの数値データを指定します。

たとえばA2からA11に広告費、B2からB11に売上がある場合は、=CORREL(A2:A11,B2:B11)と入力します。

見出しであるA1やB1を範囲へ含める必要はなく、数値が始まる2行目から指定する点が実務上の基本です。

セル範囲をキーボードで入力しにくいときは、数式を入力した後にドラッグで範囲を選択しても構いません。

関数名は半角英字で入力しますが、エクセルが候補を表示したらTabキーで確定すると入力ミスを抑えられます。

 

PEARSON関数との結果

PEARSON関数も、CORREL関数と同様にピアソンの積率相関係数を計算する関数です。

同じ二つのデータ範囲を指定した場合、一般的なエクセルの利用ではCORREL関数とPEARSON関数の結果は同じになります。

既存の資料や社内のテンプレートでPEARSON関数が使われているなら、その形式を引き継いでも問題ありません。

新しく作る集計表では、相関係数を求める用途が名前から伝わりやすいCORREL関数を選ぶと、数式の意図を共有しやすくなります。

どちらの関数も直線的な関係の強さを測るためのものであり、曲線的な変化をそのまま評価するものではありません。

グラフが弧を描いている場合は、相関係数だけを見て関係がないと結論づけない配慮も必要です。

 

エラー値と空白セル

計算結果がエラーになるときは、指定した二つの範囲の大きさが一致しているかを確認します。

A2からA11は10件なのにB2からB10は9件という状態では、対応するデータを正しく比較できません。

また、対象範囲にエラー値が含まれていると、相関係数の計算にも影響します。

空白セルをそのまま残すより、欠損の理由を確認し、二つの列で対応する行をそろえてから分析することが重要です。

0はデータが存在しないことを表す値ではなく、実際にゼロだったという数値です。

欠損値を0で埋めると相関の強さが変わるため、安易な置き換えは避けましょう。

 

散布図と近似曲線の表示設定

広告費	売上	散布図上の位置
10	82	左下付近
20	112	中央付近
30	144	右上付近

続いては、散布図を見やすく整え、近似曲線と決定係数を表示する設定を確認していきます。

 

散布図の挿入操作

A1からB11の範囲を選択した状態で、挿入タブから散布図を選択します。

表示される種類のうち、最初は線なしでマーカーだけが表示される散布図を選ぶと、データの分布を読み取りやすくなります。

折れ線グラフはデータの入力順に点をつなぐため、二つの量の関係を調べる目的では散布図と意味が異なります。

横軸と縦軸が数値軸として連続的に表示されることが、散布図を選ぶ大きな理由です。

グラフを選択すると、右上にグラフ要素のプラス記号が表示されます。

ここから軸ラベルをオンにして、横軸を広告費、縦軸を売上のように書き換えると、第三者にも内容が伝わりやすくなります。

 

近似曲線の追加

散布図の点をクリックして系列全体を選択し、右クリックから近似曲線の追加を選択します。

またはグラフ要素のプラス記号をクリックし、近似曲線にチェックを入れても追加できます。

広告費と売上のように、おおむね直線的な関係を調べる場合は線形近似を選ぶ方法が基本です。

近似曲線は個々の点を結ぶ線ではなく、全体の平均的な傾向を表す線です。

点が近似曲線の周辺に集まっていれば、二つの数値の関係を直線で説明しやすいと判断できます。

大きく離れた点が複数ある場合は、季節要因、商品構成、キャンペーンなど別の要因を確認する余地があります。

 

数式とR二乗値の表示

近似曲線の書式設定では、グラフに数式を表示する、グラフにR二乗値を表示するという項目にチェックを入れられます。

R二乗値は決定係数と呼ばれ、線形の近似式がデータのばらつきをどの程度説明できるかを表す値です。

相関係数は向きを含むためマイナスになれますが、R二乗値は二乗するため原則として0から1の範囲です。

近似式は将来の値を機械的に保証するものではありませんが、データの傾向を説明する補助情報になります。

 

相関係数の強さと符号の読み方

相関係数の目安	関係の読み方
0.7から1.0	強い正の相関
0.3から0.7	弱めから中程度の正の相関
マイナス0.7以下	強い負の相関

続いては、計算した相関係数をどのように読み、散布図の形と結びつけるかを確認していきます。

 

正の相関と負の相関

相関係数がプラスの場合、一方の値が大きくなるほど、もう一方の値も大きくなる傾向を示します。

広告費と売上、学習時間と得点のように、右上がりの散布図になりやすい関係です。

反対に、相関係数がマイナスの場合は、一方が大きくなるほどもう一方が小さくなる傾向を示します。

在庫量と欠品回数のように右下がりの分布を示すケースでは、負の相関が現れることがあります。

符号は関係の方向を表し、絶対値は関係の強さを表します。

マイナス0.8は悪い結果という意味ではなく、強い逆方向の関係があるという意味です。

 

相関の強さの目安

相関係数の強さには分野やデータの性質による違いがあるため、すべての場面に共通する絶対的な基準はありません。

一般には絶対値が0.7以上なら強い相関、0.4前後なら中程度、0.2以下なら弱い相関またはほとんど相関がないと表現されることがあります。

数値の区分だけで結論を出さず、散布図の点の並び、データ件数、業務上の背景も合わせて確認しましょう。

数件だけのデータでは偶然の影響を受けやすく、係数が高くても再現性を確認する必要があります。

 

相関係数がゼロに近い場合

相関係数がゼロに近いときは、二つの数値の間に直線的な関係がほとんど見られない状態を意味します。

ただし、完全に無関係であるとは限りません。

たとえば気温と売上の関係が途中から急に変化する場合、曲線的な関係があるのにCORREL関数では低い値になることがあります。

散布図がU字型や山型を描くときは、相関係数が低くてもデータの規則性が存在する場合があります。

このような場合は、近似曲線の種類を変更したり、季節別や商品別にデータを分けたりして分析します。

 

分析ツールによる相関係数の算出

A列	B列	C列
広告費	売上	来店数
10	82	340
15	96	380

続いては、三項目以上の関係をまとめて確認したい場合に便利な分析ツールについて解説していきます。

 

分析ツールの有効化

データ分析の項目がデータタブに表示されていない場合は、アドインの設定を確認します。

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

管理の欄でExcelアドインを選び、設定をクリックして分析ツールにチェックを入れます。

設定後にデータタブを開くと、分析グループにデータ分析が表示されます。

分析ツールは複数列の相関係数を一度に計算できるため、項目数が多い集計表で特に便利です。

利用できるメニュー名や位置はエクセルのバージョンで多少異なる場合がありますが、基本的な手順は共通しています。

 

相関分析の入力範囲

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

入力範囲には、見出しを含めたA1からC11のような範囲を指定します。

先頭行をラベルとして使う場合は、ラベルのチェックボックスをオンにします。

列ごとにデータが入力されている今回の表では、グループは列を選びます。

出力先は新規ワークシートにすると、元データを上書きせず安全に確認できます。

指定を終えてOKをクリックすると、各列の組み合わせの相関係数が行列形式で出力されます。

 

相関行列の見方

相関行列では、同じ項目同士が交差する対角線上に1が表示されます。

広告費と売上の交差するセルには、二つの項目の相関係数が表示されます。

表は左右または上下で対称になるため、同じ組み合わせの値を二度読む必要はありません。

複数の項目を比較するときは、絶対値が大きいセルを探してから、必ず散布図で関係の形を確認する流れが有効です。

広告費と来店数、来店数と売上のように連鎖する関係が見つかる場合もあります。

ただし、複数の項目が高い相関を示しても、どれが原因なのかは相関分析だけでは断定できません。

 

相関分析で注意したい外れ値と因果関係

広告費	売上	確認内容
20	112	通常月
20	180	大型企画月
30	144	通常月

続いては、相関係数を実務で使う際に誤解しやすい外れ値、因果関係、データの分け方を確認していきます。

 

外れ値による数値の変化

外れ値とは、ほかのデータ群から大きく離れた数値です。

入力ミスで桁が一つ多い場合や、特別なキャンペーンが実施された月などに発生します。

相関係数は外れ値の影響を受けるため、計算結果が予想と異なるときは散布図上で離れた点がないか確認します。

外れ値を削除する前には、入力ミスなのか、実際に起きた重要な出来事なのかを必ず区別しましょう。

実際の出来事であれば削除せず、通常月と企画月を分けた分析も検討できます。

判断理由を表に残しておくと、後から分析結果を説明する際にも役立ちます。

 

相関と因果関係の違い

相関があることは、一方の変化がもう一方の変化を直接引き起こしたことを意味しません。

たとえば夏にアイスの売上と冷房の使用量がともに増えても、アイスの売上が冷房使用量の直接的な原因とは限りません。

気温という第三の要因が、二つの数値に影響している可能性があります。

相関係数は意思決定の材料として有用ですが、単独で因果関係を証明するものではありません。

分析結果を報告するときは、関係が見られたという表現を用い、原因と断定しない姿勢が適切です。

 

条件別データの比較

全期間のデータをまとめると弱い相関でも、季節別、店舗別、商品別に分けると明確な傾向が見えることがあります。

たとえば夏と冬を一つの表に混在させるより、季節ごとにCORREL関数を入力して比較する方法です。

フィルターで必要な行だけを表示しても、通常のCORREL関数では非表示行を含めて計算する点には注意が必要です。

分析用に別の範囲へ抽出してから計算すると、対象データを明確に管理できます。

同じ条件に近いデータを比較するほど、相関係数を業務上の判断へ結びつけやすくなります。

 

まとめ エクセルの散布図で相関係数を求める方法

確認項目	実施内容
相関係数の計算	=CORREL(A2:A11,B2:B11)
グラフの確認	散布図と近似曲線を表示
結果の判断	外れ値と条件差を確認

エクセルで散布図の相関係数を求めるときは、まず比較する二つの数値列を同じ行数で準備します。

相関係数を表示したいセルに、=CORREL(A2:A11,B2:B11)のような数式を入力すれば、二つの項目の直線的な関係を数値で確認できます。

散布図を併用すると、相関係数の数字だけでは見えない外れ値や曲線的な分布も把握できます。

正の値は同じ方向への変化、負の値は逆方向への変化を示し、絶対値が1に近いほど関係は強くなります。

複数項目をまとめて比較したいときは、データ分析の相関機能で相関行列を出力する方法も便利です。

ただし、相関が高いからといって因果関係まで証明されたわけではありません。

データの背景、対象期間、外れ値、季節性などを確認しながら、CORREL関数と散布図を実務の分析に活用していきましょう。