【Excel】相関係数をエクセルで求める方法｜CORREL関数と散布図の使い方

Excelで売上と広告費、勉強時間とテストの点数など、2つの数値データにどの程度の関係があるかを調べたい場面では、相関係数が役立ちます。

相関係数は、片方の値が増えるともう片方も増える傾向があるのか、反対に減る傾向があるのかを、マイナス1から1までの数値で表す指標です。

この記事では、1行目に見出しがある表を例に、CORREL関数の入力方法、結果の読み方、散布図による確認方法を順番に解説します。

 

Excelで相関係数を求める方法【CORREL関数の入力】

月	広告費	売上
4月	120	860
5月	150	980
6月	180	1120

それではまず、CORREL関数を使って相関係数を求める基本操作について解説していきます。

 

CORREL関数の書式

CORREL関数の書式は、CORREL配列1,配列2です。

この数式では、広告費が入力されたB2からB13と、売上が入力されたC2からC13を比較します。

1行目の見出しであるB1とC1は、数式の範囲に含めないことが重要です。

見出しを含めても文字列が無視される場合はありますが、集計範囲を明確にするため、数値だけを指定する習慣を付けると安心です。

 

数式を入力するセル

相関係数を表示したい空白セルを選択し、数式バーまたはセルにCORREL関数を入力します。

たとえばE2セルに結果を表示するなら、E1には相関係数、E2には数式を入力すると、表の意味が後から見ても分かりやすくなります。

関数の入力後にEnterキーを押すと、0.9前後の小数が表示されることがあります。

表示形式を小数第2位または第3位に整えると、報告資料でも読み取りやすくなります。

元データを更新するとCORREL関数の結果も自動的に再計算されるため、毎月の実績比較にも向いています。

 

範囲指定で注意する点

配列1と配列2は、必ず同じ件数のデータにします。

B2:B13が12件なのにC2:C12が11件というように件数が異なると、正しい比較ができず、エラーになる可能性があります。

単位が異なっていても、広告費が円、売上が千円というように比較対象の組み合わせが一貫していれば、相関係数そのものは計算できます。

 

相関係数の数値の読み方

相関係数	関係の目安
0.82	強い正の相関
-0.64	負の相関

続いては、計算結果の数値が表す意味を確認していきます。

 

正の相関と負の相関

相関係数が1に近いほど、片方が増えるともう片方も増える正の相関が強い状態です。

広告費と売上の相関係数が0.82なら、広告費が多い月ほど売上も多い傾向があると読めます。

一方で、相関係数がマイナス1に近い場合は、片方が増えるともう片方が減る負の相関です。

気温と暖房費のように、逆方向へ動くデータでは負の値になります。

相関係数が0に近いときは、少なくとも直線的な関係は弱いと判断できます。

 

強さを判断する目安

相関の強さには分野やデータ数による違いがあるため、絶対的な基準ではありません。

たとえば-0.85は、0.85と同じくらい強い関係を示し、方向だけが反対です。

0.99のように非常に高い値が出ても、原因と結果が証明されたわけではありません。

季節、価格改定、キャンペーンなど、両方に影響する別の要因がある可能性も考えましょう。

 

相関係数だけで判断しない視点

相関係数は数値の関係を要約する便利な指標ですが、データの中身まで自動で判断するものではありません。

特定の月だけ極端に高い売上があり、その月に大型案件が計上されていた場合、1件の外れ値が結果を大きく変えることがあります。

相関係数は仮説を考える入口であり、結論を急ぐための数字ではありません。

 

PEARSON関数との違いとデータ分析ツール

関数	用途
CORREL	ピアソンの相関係数を求める
PEARSON	ピアソンの相関係数を求める

続いては、似た関数との違いと、複数項目を一度に調べる方法を確認していきます。

 

CORREL関数とPEARSON関数

CORREL関数とPEARSON関数は、通常は同じデータを指定すると同じ結果を返します。

どちらもピアソンの積率相関係数を計算する関数であり、日常的なExcel作業ではCORREL関数を選べば十分です。

既存の資料でPEARSON関数が使われていても、計算方式が異なると慌てる必要はありません。

ただし、順位データの関係を調べたい場合は、順位相関など別の考え方が必要になることがあります。

 

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

売上、広告費、訪問者数、客単価のように、3項目以上の相関をまとめて確認したい場合は、分析ツールの相関機能が便利です。

Excelのファイルタブからオプションを開き、アドインで分析ツールを有効にすると、データタブのデータ分析から相関を選べます。

出力された相関行列では、各項目の交点に相関係数が表示されます。

同じ項目同士が交わる対角線上は、必ず1になります。

 

関数選択の考え方

2列だけを確認する場合は、空白セルにCORREL関数を入力する方法が最も手軽です。

複数列を横断して比較する場合は、相関行列を出力すると全体像を把握しやすくなります。

分析の目的に合わせて、単一の数式と分析ツールを使い分けると作業時間を減らせます。

なお、相関の強い組み合わせが見つかったら、次に散布図で形を確認する流れがおすすめです。

 

散布図による相関関係の可視化

広告費	売上
120	860
150	980
180	1120

続いては、散布図を使ってデータの並び方を目で確認する方法を解説していきます。

 

散布図を挿入する手順

広告費と売上の数値範囲を、見出しを含めてB1:C13のように選択します。

挿入タブを開き、グラフグループの散布図から、マーカーのみの散布図を選びましょう。

横軸には左側の列である広告費、縦軸には右側の列である売上が配置されます。

折れ線グラフではなく散布図を選ぶことで、2つの数値の組み合わせを正確に表現できます。

 

近似曲線の追加

散布図の点を選択して右クリックし、近似曲線の追加を選ぶと、データの傾向を表す線を表示できます。

線形近似を選ぶと、点が右上がりに並ぶのか、右下がりに並ぶのかを直感的に把握できます。

相関係数とR二乗値は同じものではありませんが、単回帰の線形関係では関連して確認できます。

 

外れ値と曲線的な関係

散布図では、ほかの点から大きく離れた外れ値を見つけやすくなります。

外れ値が入力ミスなら修正が必要ですが、実際に起きた特別な事象なら、安易に削除せず理由を記録します。

また、点がU字型や山型に並ぶ場合、CORREL関数では低い値になることがあります。

相関係数が低くても、散布図に明確な曲線が見えるなら、関係がないとは限りません。

 

相関係数でエラーになる原因

確認項目	対処
範囲の件数	開始行と終了行をそろえる
数値のばらつき	同じ値だけの列がないか確認する

続いては、CORREL関数でエラーや予想外の結果が出る原因を確認していきます。

 

配列の大きさの不一致

CORREL関数の2つの範囲で行数や列数が違うと、計算できません。

数式をコピーしたときに片方だけ参照範囲がずれることもあるため、数式バーでセル範囲を見直しましょう。

行を追加する予定がある表では、Excelテーブルに変換してから構造化参照を使う方法もあります。

 

標準偏差がゼロになるデータ

広告費の列がすべて100、売上の列も変化がないというように、片方の値が完全に一定だと相関係数は定義できません。

この場合はゼロ除算に関連するエラーが表示されます。

比較対象に変化がなければ、増減の関係そのものを評価できません。

 

文字列と空白セルの扱い

数値に見えても、先頭にアポストロフィが付いた文字列として保存されていると、期待した計算にならないことがあります。

セルの表示形式を標準または数値に変更し、警告アイコンが出ていれば数値に変換を実行します。

空白が多いデータでは、どの行を比較対象にしたかを明確にしてから範囲を選びましょう。

 

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

Excelで相関係数を求めるときは、CORREL関数で2つの数値範囲を指定します。

1行目にヘッダーがある表では、たとえば広告費がB列、売上がC列なら、B2:B13とC2:C13のように数値部分だけを範囲に含めます。

結果が1に近ければ強い正の相関、マイナス1に近ければ強い負の相関、0に近ければ線形の関係は弱い状態です。

ただし、相関があることは因果関係の証明ではありません。

散布図を作成して、外れ値、曲線的な分布、データ入力の誤りがないかを合わせて確認すると、数値の解釈がより確かなものになります。

まずはCORREL関数で数値を求め、必要に応じて散布図と近似曲線で可視化する流れを実践してみましょう。