エクセルで売上と広告費、気温と飲料の販売数、勉強時間とテスト得点のように、2つの数値にどの程度の関係があるのかを調べたい場面があります。
その関係の強さを数値で表す代表的な指標が相関係数です。
ExcelではCORREL関数やPEARSON関数を使うことで、複雑な計算を手作業で行わなくても相関係数を求められます。
さらに散布図と近似曲線を組み合わせれば、データの傾向を視覚的に確認し、R2の値から当てはまり具合も判断できます。
相関係数は－1から1までの数値で表され、1または－1に近いほど関係が強いという見方が基本です。
ただし、相関があることと、一方がもう一方の原因であることは同じではありません。
この記事では、Excelで相関係数を求める関数、散布図の作成、近似曲線とR2の表示、データ分析で注意したい点までを順番に解説していきます。
エクセルで相関係数を求めるCORREL関数【最初に確認したい計算方法】
それではまず、Excelで最も手軽に相関係数を算出できるCORREL関数について解説していきます。
結論からいうと、比較したい2列の数値範囲を指定するだけで、Excelがピアソンの積率相関係数を返してくれます。
| A列 | B列 | C列 |
|---|---|---|
| 広告費 | 売上 | 相関係数 |
| 120 | 85 | |
| 150 | 92 | |
| 180 | 104 | |
| 210 | 115 |
CORREL関数の入力
1行目に見出しがあり、広告費がA2からA11、売上がB2からB11に入力されている例で考えます。
相関係数を表示したいセル、たとえばC2を選択します。
=CORREL(A2:A11,B2:B11)
この数式を入力してEnterキーを押すと、A列とB列の対応する数値を比較した相関係数が表示されます。
範囲の先頭と末尾をそろえることが重要です。
A2からA11に対してB2からB10のように件数が異なる範囲を指定すると、正しい比較ができません。
CORREL関数では、1つ目の範囲と2つ目の範囲を同じ行数にそろえることが基本となります。
【操作のポイント】列名が入った1行目は数式の範囲に含めず、数値が始まる2行目から指定しましょう。
相関係数の数値の読み方
CORREL関数の結果は、－1から1までの範囲に収まります。
たとえば0.92と表示された場合は、広告費が増えるほど売上も増える傾向がかなり強いと読み取れます。
0に近い値は線形の関係が弱い状態です。
1に近い値は正の相関です。
－1に近い値は負の相関です。
正の相関では、片方の値が大きくなるほど、もう片方の値も大きくなる傾向があります。
負の相関では、片方の値が大きくなるほど、もう片方の値は小さくなる傾向です。
相関係数が0.5であれば中程度の正の相関、－0.8であれば強い負の相関の候補として確認できます。
ただし、強さの区分に絶対的な基準はありません。
業種、標本数、分析目的を踏まえて判断する姿勢が大切です。
【操作のポイント】表示桁数は小数点以下2桁から3桁程度に設定すると、レポートや資料で読みやすくなります。
空白セルと文字列があるデータの扱い
実務のデータには、未入力の空白セル、ハイフン、集計中という文字列が混在することがあります。
CORREL関数は参照範囲内の文字列や空白を基本的に無視して計算しますが、片方の列だけが欠けている行が多いと、比較対象が想定より少なくなるかもしれません。
たとえば広告費は入力済みでも、その月の売上が未確定なら、2つの値がそろった行だけを使う必要があります。
相関係数を出す前に、同じ対象、同じ期間、同じ単位のデータになっているかを確認しましょう。
売上が円単位で、広告費が千円単位でも相関係数そのものは求められます。
一方で、列の意味を取り違えると分析の前提が崩れます。
【操作のポイント】フィルターで空白行や異常値を確認してから、分析用の範囲を決めるとミスを減らせます。
PEARSON関数と分析ツールによる相関係数【複数データの確認方法】
続いては、PEARSON関数とExcelの分析ツールを使った相関係数の確認方法を見ていきます。
2項目だけならCORREL関数で十分ですが、複数の項目を比較したい場合は相関行列を作る方法が便利です。
| A列 | B列 | C列 |
|---|---|---|
| 広告費 | 来店者数 | 売上 |
| 120 | 340 | 85 |
| 150 | 365 | 92 |
| 180 | 402 | 104 |
| 210 | 430 | 115 |
PEARSON関数との違い
PEARSON関数も、CORREL関数と同じくピアソンの積率相関係数を求める関数です。
数値だけの通常のデータを指定する場合、両者の計算結果は原則として同じになります。
=PEARSON(A2:A11,B2:B11)
関数名の由来を意識して統計資料と表記を合わせたい場合はPEARSON関数を選んでも問題ありません。
日常的なExcel作業では、短く覚えやすいCORREL関数を使う人も多いでしょう。
CORRELとPEARSONは、通常の相関係数の計算では同じ結果を返すため、計算値が違うときは指定範囲を先に見直します。
片方の式で結果を確認し、もう片方でも試すことは、範囲選択ミスを見つける補助にもなります。
【操作のポイント】関数名を入力したら、数式の候補から選択するとスペルミスを防げます。
分析ツールの相関機能
広告費、来店者数、売上のように3項目以上を一度に比較するなら、分析ツールの相関機能が便利です。
リボンのデータタブにあるデータ分析を選び、一覧から相関を選択します。
入力範囲には、見出しを含めたA1からC11のような範囲を指定します。
先頭行をラベルとして使う場合は、ラベルにチェックを入れましょう。
出力先を指定してOKを押すと、項目ごとの相関係数が表形式で出力されます。
データ分析が表示されない場合は、ファイルタブからオプションを開きます。
アドインを選び、管理でExcelアドインを指定して設定を押します。
分析ツールにチェックを入れて有効化すると、データタブから利用できます。
複数項目の関連性を一覧で見たいときは、相関行列を出力すると比較が速くなります。
【操作のポイント】入力範囲には、途中の空列や合計行を含めないようにしてください。
相関行列の見方
相関行列では、行と列に同じ項目名が並びます。
広告費と広告費の交差するセルは1になります。
これは同じデータ同士を比べているためです。
広告費と売上の交差するセルを見れば、この2項目の相関係数を確認できます。
表は対角線を境に左右対称になるため、同じ組み合わせを2回読む必要はありません。
1に近いセルと－1に近いセルだけを先に探すと、確認すべき関係を絞り込みやすくなります。
ただし、多数の項目を同時に比べると、偶然大きな相関が見つかることもあります。
気になる組み合わせは、散布図でも形を確かめるのがおすすめです。
【操作のポイント】相関行列の対角線以外に条件付き書式を使うと、強い相関を視覚的に把握できます。
散布図による相関関係の可視化【グラフで傾向を確かめる方法】
続いては、散布図を作成して相関係数だけでは見えにくいデータの形を確認していきます。
相関係数が高くても、外れ値が1つだけ影響している場合や、直線ではない曲線的な関係が隠れている場合があります。
| A列 | B列 |
|---|---|
| 広告費 | 売上 |
| 120 | 85 |
| 150 | 92 |
| 180 | 104 |
| 210 | 115 |
散布図の作成
横軸にする広告費と、縦軸にする売上の数値範囲を選択します。
見出しを含める場合はA1からB11を選択すると、グラフの系列名にも反映されやすくなります。
挿入タブを開き、グラフの中から散布図を選択します。
最初は、マーカーのみを表示する散布図を選ぶと、各データの位置を把握しやすいでしょう。
散布図では横軸と縦軸がともに数値軸として扱われるため、数値同士の関係を見る用途に向いています。
折れ線グラフはデータの入力順を横軸に並べることがあり、相関分析には散布図のほうが適しています。
【操作のポイント】横軸に置きたい説明変数を左側の列、縦軸に置きたい結果の数値を右側の列に配置すると分かりやすくなります。
散布図の点の並び方
点が左下から右上に向かって帯状に並ぶ場合は、正の相関が疑われます。
反対に左上から右下に向かって並ぶ場合は、負の相関の傾向です。
点が全体に散らばっていて方向性が見えなければ、線形の相関は弱い可能性があります。
相関係数の数値と散布図の形を必ず一緒に確認すると、分析の信頼性が上がります。
特に、1点だけ極端に離れた外れ値があるときは注意が必要です。
その1点を含めるかどうかで相関係数が大きく変わる場合があります。
外れ値を安易に削除するのではなく、入力ミス、特別なキャンペーン、季節要因などの背景を確認しましょう。
【操作のポイント】グラフタイトルと軸ラベルを設定すると、第三者にも何を比較した散布図なのか伝わります。
数式バーとオートフィルによる補助計算
散布図を作る前に、平均との差や偏差の確認をしたい場合もあります。
たとえばC列に広告費の平均との差を出すなら、C2に数式を入力して下方向へオートフィルできます。
=A2-AVERAGE($A$2:$A$11)
数式内の$記号は平均を求める範囲を固定するためのものです。
C2右下の小さな四角形であるフィルハンドルを下へドラッグすれば、各行の計算式をコピーできます。
先頭セルに正しい式を入力し、オートフィルで下へ展開すると、行ごとの計算を効率よくそろえられます。
相関係数はCORREL関数だけで求められますが、補助計算を行うと統計の仕組みを理解しやすくなります。
【操作のポイント】平均との差を計算する場合は、AVERAGE関数の参照範囲を絶対参照にして、コピー後も範囲がずれないようにします。
近似曲線とR2値の表示【グラフの当てはまりの確認】
続いては、散布図に近似曲線を追加し、R2値を表示する方法を確認していきます。
R2は決定係数と呼ばれ、作成した直線がデータのばらつきをどの程度説明できているかを見る指標です。
| 広告費 | 売上 | 近似値のイメージ |
|---|---|---|
| 120 | 85 | 84.6 |
| 150 | 92 | 94.7 |
| 180 | 104 | 104.8 |
| 210 | 115 | 114.9 |
線形近似曲線の追加
作成済みの散布図で、データ系列の点をクリックします。
右クリックして近似曲線の追加を選択すると、設定画面が開きます。
通常の相関分析では、近似曲線のオプションから線形を選択します。
グラフ上に右上がりまたは右下がりの直線が表示され、データ全体の傾向をつかめます。
相関係数が線形の関係を数値化するのに対し、近似曲線は傾向を視覚化する役割があります。
データが明らかな曲線を描く場合、線形近似だけでは実態を表しきれないことがあります。
その場合は指数、対数、多項式などの近似曲線も比較対象になります。
【操作のポイント】最初は線形近似曲線で確認し、点の並び方に曲がりが見えるときだけ別の近似曲線を検討しましょう。
R2値と数式の表示
近似曲線の書式設定で、グラフに数式を表示する、グラフにR2値を表示するにチェックを入れます。
たとえばy＝0.34x＋43.2のような式が表示されます。
この式ではxが広告費、yが売上の推定値です。
R2が1に近いほど、線形近似曲線がデータのばらつきをよく説明している状態です。
R2が0に近いほど、直線だけでは説明しにくい状態です。
単純な線形回帰では、R2は相関係数を2乗した値と一致するという関係があります。
相関係数が0.90なら、R2はおおむね0.81です。
これは売上のばらつきの約81パーセントを、広告費との線形関係で説明できるという意味合いで使われます。
【操作のポイント】R2は小数点以下2桁から3桁で十分な場合が多く、過度に細かい桁まで表示する必要はありません。
RSQ関数によるR2の計算
グラフ上ではなくセルに決定係数を表示したい場合は、RSQ関数を使えます。
売上を既知のy、広告費を既知のxとして指定します。
=RSQ(B2:B11,A2:A11)
RSQ関数では、1つ目に結果側の数値、2つ目に説明側の数値を指定する順番です。
CORREL関数では2つの範囲の順序を入れ替えても同じ相関係数になりますが、RSQ関数では意味を意識して入力すると理解しやすくなります。
R2の値だけで判断せず、散布図と残差の大きさも確認することが大切です。
R2が高くても、重要な条件が分析に含まれていない可能性は残ります。
【操作のポイント】RSQ関数とグラフに表示したR2を照合すると、範囲選択やグラフ設定の誤りに気づきやすくなります。
相関係数を使う際の注意点【誤った判断を避ける視点】
続いては、相関係数を実務で活用するときに押さえたい注意点を確認していきます。
数式で求めた値が正しくても、データの選び方や解釈が不適切なら、結論を誤るおそれがあります。
| 確認項目 | 確認内容 |
|---|---|
| 対象期間 | 両方の数値が同じ月や同じ日に対応しているか |
| 外れ値 | 入力ミスや特別要因のある値がないか |
| 因果関係 | 第三の要因が影響していないか |
相関関係と因果関係
相関係数が高いからといって、片方の数値がもう片方を直接変化させたとは限りません。
たとえば気温とアイスクリームの売上に正の相関があっても、気温以外に休日や地域イベントが影響しているかもしれません。
2つの数値に同時に影響する第三の要因がある場合、見かけ上の相関が生じることがあります。
相関は関係の発見に役立ちますが、原因の証明そのものではありません。
施策の効果を判断するなら、時期、顧客層、競合状況なども合わせて検討しましょう。
【操作のポイント】強い相関を見つけたら、なぜその関係が生じるのかを文章で説明できるか考えると分析が深まります。
データ数と期間のそろえ方
データ数が少ないと、たまたま似た動きをしただけでも相関係数が大きく出ることがあります。
数件だけのデータで結論を急がず、可能なら観測件数を増やしましょう。
また、月次データと日次データ、税込売上と税抜広告費のように、基準が異なる数値をそのまま比べるのは適切ではありません。
比較対象は同じ期間、同じ粒度、同じ対象範囲にそろえる必要があります。
月別データなら、広告費も売上も同じ月の数値を1行に置きます。
店舗別データなら、同じ店舗の数値同士を対応させます。
この対応がずれると、Excelの数式がエラーにならなくても分析結果は意味を失います。
【操作のポイント】集計単位を列見出しやシート名に明記し、データの対応関係を後から確認できるようにします。
外れ値と非線形データ
散布図の大部分がまとまっているのに、1点だけ大きく離れている場合は外れ値の影響を確認します。
入力桁の間違い、集計漏れ、返品の集中など、数値が大きく動いた理由を調べることが先決です。
また、売上が一定の広告費までは増えるものの、その後は伸びが鈍るような関係では、直線的な相関係数だけでは特徴を十分に表せません。
このような非線形のデータでは、散布図、近似曲線、区分別の集計を組み合わせると判断しやすくなります。
数字が示す結果と、現場で起きている事情を行き来して確認することが、相関分析を役立つ判断材料に変えます。
【操作のポイント】外れ値を除外して再計算する場合は、除外前後の数値を残し、処理の理由も記録しておきましょう。
まとめ エクセルで相関係数を求める方法（関数・散布図・グラフ・R2）
Excelで相関係数を求める基本的な方法は、CORREL関数に2つの数値範囲を指定することです。
PEARSON関数でも同様の計算ができ、複数項目をまとめて比較したいときは分析ツールの相関機能が役立ちます。
相関係数は1に近いほど強い正の相関、－1に近いほど強い負の相関、0に近いほど線形の関係が弱いという見方が基本です。
ただし、数値だけで終わらせず、散布図で点の並び方や外れ値を確認しましょう。
散布図に近似曲線を追加し、R2を表示すれば、直線でどの程度データを説明できるかも把握できます。
CORREL関数、散布図、R2を組み合わせることで、数値と見た目の両面から関係を確認できます。
最後に、相関関係を因果関係と決めつけず、データの期間、対象、外れ値、第三の要因を確認する姿勢を忘れないようにしましょう。
