Excelで売上と広告費、気温と飲料販売数など、二つの数値にどの程度の関係があるのかを確認したい場面では、相関係数が役立ちます。
相関係数は数値を眺めるだけでは見えにくい傾向を、マイナス1から1までの値で表せる指標です。
CORREL関数は二つの数値範囲の相関係数を求める基本機能です。
相関係数が1に近いほど同じ方向、マイナス1に近いほど反対方向、0に近いほど直線的な関係が弱いと読み取れます。
この記事では、サンプルデータを使ったCORREL関数の入力方法、散布図での確認、R2との使い分けまで解説します。
エクセルでCORREL関数を入力する方法
それではまず、相関係数を最短で計算するCORREL関数の使い方について解説していきます。
| A列 | B列 | C列 |
|---|---|---|
| 広告費 | 売上 | 相関係数 |
| 10 | 120 | 数式を入力 |
| 15 | 145 | |
| 20 | 160 |
CORREL関数の構文
CORREL関数は、対応する二つのデータ範囲を指定して使用します。
=CORREL(配列1,配列2)
たとえばA2からA11に広告費、B2からB11に売上が入っている場合、結果を表示したいセルに次の数式を入力します。
=CORREL(A2:A11,B2:B11)
一行目がヘッダーの場合は、範囲をA2から指定する点が重要です。
配列1と配列2は同じ件数で、同じ行が同じ時点や同じ対象を表すように整えておきましょう。
関数の入力と結果の表示
数式バーまたはセルへ等号から入力し、Enterキーを押すと結果が表示されます。
関数の挿入ダイアログを使う場合は、数式タブから関数の挿入を選び、統計カテゴリ内のCORRELを探します。
セル参照をドラッグして指定すると入力ミスを減らせますが、見出しセルまで含めないよう注意が必要です。
結果が0.92なら、広告費が増える局面で売上も増える強い正の相関があるという読み方になります。
エラーと空白セルの確認
数値ではない文字列や、片方だけに存在するデータが混ざると、意図しない判定になることがあります。
特に「未入力」と文字で記録したセルは数値として扱われません。
欠測値は両方の列で同じ行を除外し、比較対象をそろえてから計算するのが基本です。
【操作のポイント】
相関係数を求める前に、二列の開始行、終了行、データ件数が一致しているか確認します。
相関係数の数値と方向の読み方
続いては、計算結果をどのように解釈するか確認していきます。
| 相関係数 | 傾向 |
|---|---|
| 0.7から1.0 | 強い正の相関 |
| マイナス0.3から0.3 | 弱い相関または相関なし |
| マイナス1.0からマイナス0.7 | 強い負の相関 |
正の相関の判断
相関係数がプラスなら、一方が大きくなるほどもう一方も大きくなる傾向です。
0.8を超える数値は強い関係の目安になりますが、業種やデータ数も考慮しましょう。
ただし、強い相関があっても広告費が売上増加の原因だと断定できるわけではありません。
負の相関の判断
相関係数がマイナスなら、一方が増えると他方が減る傾向を示します。
たとえば気温と暖房費では、気温が上がるほど暖房費が下がるため、負の相関になりやすいでしょう。
符号は関係の向き、絶対値は関係の強さを表します。
相関なしと注意点
0に近い結果は、直線的な関係が明確ではない状態を意味します。
曲線的な関係や、外れ値が一件だけあるケースでは、相関係数だけでは実態をつかめないかもしれません。
【操作のポイント】
数値だけで結論を急がず、散布図と元データを併せて確認します。
散布図によるデータ分布の確認
続いては、相関係数の背景を視覚的に確認する散布図について解説していきます。
| 広告費 | 売上 |
|---|---|
| 10 | 120 |
| 20 | 160 |
| A | B | C | |
|---|---|---|---|
| 1 | 広告費 | 売上 | |
| 2 | 10 | 120 | ➤ 散布図 |
散布図の挿入手順
A1からB11までを選択し、挿入タブの散布図からマーカーのみの散布図を選択します。
横軸には左列、縦軸には右列の値が配置されるため、列の並びを確認しましょう。
近似曲線の追加
グラフ上の点を選択して近似曲線を追加すると、データの大まかな方向を確認できます。
線形近似が適さない形なら、相関係数の解釈も慎重に行う必要があります。
外れ値の見つけ方
点群から大きく離れた一点は、入力ミス、特殊要因、重要な事例のいずれかかもしれません。
外れ値を削除する前に、理由を記録して分析目的に照らして判断します。
【操作のポイント】
散布図は相関係数の補助ではなく、数値の信頼性を確かめる重要な確認画面です。
CORREL関数とR2の違い
続いては、混同されやすいCORREL関数とR2の違いを確認していきます。
| 指標 | 値の範囲 | 特徴 |
|---|---|---|
| CORREL | マイナス1から1 | 方向と強さ |
| RSQ | 0から1 | 説明できる割合 |
R2の意味
R2は決定係数とも呼ばれ、回帰式がデータのばらつきをどの程度説明できるかを示します。
R2にはマイナスがなく、関係の方向は判断できません。
RSQ関数の入力
=RSQ(B2:B11,A2:A11)
RSQ関数では既知のyに売上、既知のxに広告費を指定します。
使い分けの基準
増減の向きを知りたいときはCORREL、回帰モデルの当てはまりを評価したいときはR2が適しています。
【操作のポイント】
単回帰ではR2は相関係数を二乗した値になります。
相関分析で精度を高める準備
続いては、信頼できる分析にするためのデータ準備を確認していきます。
| 確認項目 | 内容 |
|---|---|
| 期間 | 同一期間にそろえる |
| 単位 | 円と千円を統一する |
データ件数の確保
少ない件数では偶然の変動に左右されやすくなります。
可能なら複数月や複数店舗など、目的に見合う観測数を集めましょう。
期間と単位の統一
月次売上と日次広告費のように粒度が異なるデータは、そのまま比較できません。
集計単位をそろえることで、相関係数の意味が明確になります。
因果関係との区別
相関関係は因果関係を証明しません。
季節、価格改定、販促施策など第三の要因も検討する姿勢が大切です。
【操作のポイント】
分析前に対象期間、除外条件、データの出所をメモしておくと再現性が高まります。
まとめ エクセルの相関係数の求め方（R2との違い・CORREL関数）
Excelで相関係数を求めるときは、対応した二つの数値範囲をCORREL関数に指定します。
=CORREL(A2:A11,B2:B11)のように、一行目のヘッダーを除いた同数のデータ範囲を選ぶことが基本です。
プラスとマイナスは関係の方向、絶対値は関係の強さを示します。
R2は方向を示さない決定係数であり、CORRELとは目的が異なります。
散布図で外れ値や分布も確認し、相関が原因を意味するとは限らない点を意識しながら、業務データの分析に活用していきましょう。
