【Excel】エクセル相関係数の出し方は？CORREL関数で2つのデータの関係を調べる方法（相関・p値）

Excelで2つの数値データにどの程度の関係があるのかを確かめるには、CORREL関数で相関係数を求める方法が便利です。

売上と広告費、勉強時間とテスト得点、気温と飲料の販売数など、片方が増減したときにもう片方がどう動くかを、感覚ではなく数値で確認できます。

ただし、相関係数だけでは偶然の関係かどうかを断定できません。

この記事では、CORREL関数による相関係数の出し方から、散布図、p値、結果の読み方までを順番に確認していきましょう。

 

エクセルでCORREL関数を入力する方法

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

それではまず、CORREL関数を使って2つのデータ範囲から相関係数を表示する基本操作について解説していきます。

 

CORREL関数の基本構文

CORREL関数の構文は、CORREL配列1,配列2です。

配列1と配列2には、比較したい数値データのセル範囲を同じ件数で指定します。

この数式では、A2からA11までの広告費と、B2からB11までの売上を1行ずつ対応させて計算します。

1行目を見出しにしている場合、数式の範囲はA2やB2から始めることが重要です。

見出しの文字列を含めても、CORREL関数は文字を基本的に無視しますが、データ範囲の指定を統一しておくと集計ミスを防ぎやすくなります。

結果が0.92のように表示された場合、広告費が増えるほど売上も増える傾向が強いことを示します。

一方で、相関係数が高いからといって、広告費だけが売上増加の原因とは限りません。

季節、商品の価格、キャンペーン、店舗数など、別の要因も結果に影響するためです。

 

数式を入力するセルの選び方

相関係数を表示するセルは、元データと重ならない場所に用意します。

たとえばC2を選択し、数式バーまたはセルにCORREL関数を入力してEnterキーを押しましょう。

結果のセルには通常、小数点以下を含む数値が表示されます。

小数点以下の桁数を整えたいときは、ホームタブの数値グループから表示形式を変更できます。

相関係数はパーセント表示にせず、小数のまま確認すると意味を読み取りやすくなります。

計算結果がエラーになるときは、2つの範囲の行数が一致しているかを最初に確認します。

A2:A11とB2:B10のように件数が異なる指定では、正しい比較になりません。

 

オートフィル時の絶対参照

複数の列の組み合わせを比較するときは、数式を横方向または下方向へコピーする場面があります。

その場合、固定したい範囲にはドル記号を付けた絶対参照を使うと便利です。

この式を右へコピーしても、基準となるA列の範囲は変わりません。

たとえば広告費と、売上、来店者数、問い合わせ数を順に比較する場合に役立ちます。

相関分析では、行の対応関係が崩れると結果そのものが変わるため、並べ替えを行う際にも全列をまとめて選択する必要があります。

空白セルが混ざるデータでは、片方だけに欠損がないかも確認しましょう。

無関係な行を含めず、同じ期間、同じ顧客、同じ商品という単位をそろえることが分析の土台です。

 

相関係数の数値と関係性の読み方

相関係数	関係の目安	例
0.70以上	強い正の相関	広告費と売上
0付近	直線的な関係が弱い	靴のサイズと読書冊数
マイナス0.70以下	強い負の相関	気温と暖房需要

続いては、計算された相関係数をどのように解釈すればよいかを確認していきます。

 

正の相関と負の相関

相関係数がプラスなら、片方の値が大きくなると、もう片方も大きくなりやすい正の相関です。

0.85であれば、比較的強い正の関係があると考えられます。

反対にマイナスなら、片方が増えるほどもう片方が減りやすい負の相関です。

気温と暖房費のように、季節によって逆向きに動く数値では負の値になることがあります。

符号は増減する向き、絶対値は関係の強さの目安として読み取ります。

相関係数がちょうど1またはマイナス1になるケースは、実務データでは多くありません。

すべての値が完全に一直線上に並ぶ場合だけに近い状態です。

 

相関係数の強さの目安

相関の強さには、すべての分野に共通する絶対的な基準があるわけではありません。

一般的には絶対値が0.2程度なら弱く、0.4程度なら中程度、0.7以上なら強い相関として扱われることがあります。

ただし医療、製造、金融、Webマーケティングなどでは、データのばらつきや意思決定の目的によって必要な水準が異なります。

0.3という値でも、業務上の予測や改善に使える関係が見つかる場合があります。

数値だけで強弱を決めず、データ数、散布図、現場の事情も一緒に確認する姿勢が大切です。

 

相関関係と因果関係の違い

相関関係は、2つの変数が一緒に動く傾向を表すものです。

因果関係は、片方の変数がもう片方を直接変化させる関係を表します。

たとえば夏にアイスの売上と日焼け止めの売上が同時に増えても、アイスが日焼け止めの売上を増やしているとは限りません。

この場合は気温や季節という共通の要因が両方に影響している可能性があります。

相関が見つかった後に、なぜその関係が現れたのかを検証する工程が必要です。

施策の効果を判断したい場合は、期間比較、グループ比較、回帰分析なども組み合わせると判断材料を増やせます。

 

散布図によるデータ分布の確認

A列	B列
勉強時間	テスト得点
1.0	48
2.5	66
4.0	79

続いては、CORREL関数の数値だけでは見えにくい分布を散布図で確認していきます。

 

散布図を挿入するデータ範囲

散布図を作成するには、比較する2列の見出しを含めて選択します。

1行目にヘッダーがある表なら、A1:B11のように列名と数値データをまとめて指定しましょう。

次に挿入タブを開き、グラフグループから散布図を選び、マーカーのみの散布図をクリックします。

横軸には通常A列、縦軸には通常B列の値が割り当てられます。

散布図では各行のデータが1つの点として表示されるため、外れ値や集まり方を視覚的に確認できます。

 

Excel画面での散布図挿入

散布図の点が右上がりに並ぶほど、正の相関がある可能性を視覚的に確認できます。

右下がりなら負の相関、雲のように広がるなら直線的な相関は弱いと考えられます。

 

近似曲線と外れ値の確認

散布図上の点をクリックし、グラフ要素から近似曲線を追加すると、全体の傾向がさらに見やすくなります。

線形近似を選ぶと、直線でデータの平均的な流れを表せます。

1つだけ大きく離れた点は外れ値であり、相関係数を大きく変えることがあります。

外れ値を機械的に削除するのではなく、入力ミス、特殊なキャンペーン、異常気象などの理由を確認しましょう。

曲線状の関係では、CORREL関数が低い値になることがあります。

相関係数は直線的な関係を測る指標なので、U字型のような非線形の関係は散布図で補う必要があります。

 

p値による相関の有意性判定

項目	セル例	内容
相関係数 r	C2	=CORREL(A2:A11,B2:B11)
データ数 n	C3	=COUNT(A2:A11)
p値	C5	両側検定の結果

続いては、得られた相関係数が偶然に出た可能性を判断するためのp値について確認していきます。

 

p値と有意水準の考え方

p値は、実際には相関がないと仮定した場合に、現在の結果以上の偏りが偶然に得られる確率の目安です。

一般にはp値が0.05未満なら、5パーセント水準で統計的に有意と判断することがあります。

p値が小さいことは、関係の強さではなく、偶然だけでは説明しにくいことの目安です。

相関係数が小さくても、データ数が非常に多ければp値が小さくなることがあります。

反対に相関係数が大きく見えても、データ数が少なければ偶然の可能性を十分に除外できない場合があります。

 

t値を使ったp値の計算式

ExcelにはCORREL関数のように相関係数のp値だけを直接返す標準関数はありません。

そのため、相関係数rとデータ数nからt値を計算し、T.DIST.2T関数で両側p値を求めます。

相関係数がC2、データ数がC3にある場合、t値は次の式で計算できます。

次に、t値がC4にあると仮定し、自由度n-2を使って両側p値を求めます。

ABS関数でt値を絶対値にしてからT.DIST.2T関数へ渡すことがポイントです。

関数名はExcelのバージョンにより表示候補が異なる場合がありますが、近年のExcelではT.DIST.2Tを使用できます。

 

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

複数列のデータをまとめて比較したいときは、分析ツールの相関機能を使う方法があります。

データタブのデータ分析から相関を選択し、入力範囲と出力先を指定すると相関行列を作成できます。

行列では、各変数の組み合わせごとに相関係数が並びます。

対角線上は同じ変数どうしの比較なので、必ず1になります。

ただし分析ツールの相関出力では、通常p値は別途計算が必要です。

変数の数が多い場合は、相関が高い組み合わせだけを抽出し、散布図と業務知識で優先順位を確認すると効率的です。

 

相関分析で起こりやすいエラーと注意点

状況	確認内容
#N/Aエラー	2つの範囲のセル数
#DIV/0!エラー	値のばらつきとデータ数
予想外の結果	行の対応、欠損、外れ値

続いては、CORREL関数のエラーや誤った解釈につながりやすい注意点を確認していきます。

 

範囲の大きさが異なる場合

CORREL関数では、配列1と配列2のデータ数を同じにする必要があります。

片方だけ最終行まで選択していたり、途中で範囲がずれていたりすると、エラーや不適切な比較につながります。

たとえばA2:A20とB2:B19は、対応する行がそろっていません。

同じ月、同じ店舗、同じ回答者を同じ行に置くというルールを決めると、集計表を確認しやすくなります。

フィルターで行を非表示にしている場合も、計算対象が想定通りかを確認しましょう。

 

文字列と空白セルの扱い

数値として見えても、文字列形式になっているセルがあると分析結果に影響することがあります。

左寄せ表示、緑色のエラーインジケーター、先頭のアポストロフィなどがあるセルは注意が必要です。

空白やエラー値が混在する場合は、対象行を整理してから相関係数を計算する方法が安全です。

比較する2列のどちらかに欠損がある行は、両方のデータをそろえて扱う必要があります。

0は欠損ではなく有効な数値です。

空白の代わりに0を入力すると分析結果が変わるため、未入力と実際のゼロを区別しましょう。

 

時系列データの見かけの相関

月別売上や年別利用者数のような時系列データは、時間の経過とともに両方が増えるだけで高い相関を示すことがあります。

これは共通の上昇傾向による見かけの相関かもしれません。

前年比、前月比、平均との差などに変換して比較すると、変化どうしの関係を確認できる場合があります。

時間順のデータでは、トレンドと季節性を考慮しない相関係数をそのまま結論にしないことが大切です。

必要に応じて期間を分け、施策前後や季節ごとに別の相関係数を出す方法もあります。

 

まとめ　p値と相関係数をエクセルで調べる方法

確認項目	Excelでの方法
相関係数	=CORREL(A2:A11,B2:B11)
分布の確認	散布図
p値	t値とT.DIST.2T関数

Excelで2つのデータの関係を調べる基本は、CORREL関数で相関係数を求めることです。

数式は、1行目にヘッダーがあるデータなら=CORREL(A2:A11,B2:B11)のように入力します。

プラスの値は正の相関、マイナスの値は負の相関、0付近の値は直線的な関係が弱いことを示します。

さらに散布図を作れば、数値だけでは見落としやすい外れ値や曲線的な関係を確認できます。

p値まで求める場合は、相関係数とデータ数からt値を計算し、T.DIST.2T関数で両側検定を行います。

相関係数、p値、散布図、データの背景を組み合わせることで、Excelの分析結果をより実務的に活用できるようになります。