【Excel】エクセルで共分散を求める方法と関数の使い方（不偏共分散）

エクセルで売上と広告費、身長と体重、勉強時間と点数のように、2つの数値データがどのように同時に変化するかを調べたい場面では、共分散が役立ちます。

共分散は相関係数と似た言葉ですが、数値の大きさや単位の影響を受けるため、意味と関数の使い分けを理解しておくことが重要です。

特に、標本データから母集団の傾向を推定したいときは、不偏共分散を求めるCOVARIANCE.S関数を使うのが基本となります。

サンプルデータを使いながら、関数の入力方法、計算式の考え方、エラーを避ける確認ポイントまで順番に見ていきましょう。

 

エクセルで不偏共分散を求める方法

月	広告費	売上	不偏共分散
1月	12	105	=COVARIANCE.S(B2:B7,C2:C7)
2月	18	122
3月	15	115
4月	22	140
5月	26	151
6月	20	132

それではまず、COVARIANCE.S関数を使って不偏共分散を計算する方法について解説していきます。

広告費と売上のように、対応する2列の数値を用意し、結果を表示したいセルへ数式を入力するだけで計算できます。

COVARIANCE.S関数の入力

不偏共分散を求める場合は、結果を表示するD2セルを選択し、=COVARIANCE.S(B2:B7,C2:C7)と入力します。

この例では、B2からB7が広告費、C2からC7が各月の売上です。

1月の広告費は1月の売上、2月の広告費は2月の売上というように、同じ行同士のデータが1組として扱われます。

入力後にEnterキーを押すと、D2セルに不偏共分散の計算結果が表示されます。

共分散は割合ではないため、結果が10や100を超えても、ただちに異常とはいえません。

広告費を千円単位で入力するか円単位で入力するかによって、共分散の値も大きく変化するためです。

数式バーを使った範囲指定

関数名やセル範囲を手入力する方法に不安がある場合は、数式バーから関数を入力すると範囲を確認しやすくなります。

D2セルを選択してから数式バーの入力欄をクリックし、=COVARIANCE.S(まで入力します。

続けてB2からB7をドラッグし、カンマを入力した後でC2からC7をドラッグしてください。

最後に閉じかっこを入力してEnterキーを押せば、式が完成します。

範囲を選択すると、エクセル上では参照範囲が色付きの枠で表示されます。

この表示を利用すると、広告費の列と売上の列を取り違えていないかを確認できます。

見出しであるB1やC1を含めないことも大切です。

文字列の見出しが含まれていても関数が数値だけを扱う場合はありますが、計算対象はデータ本体だけにそろえる習慣をつけると集計ミスを防げます。

関数の挿入による計算

関数の名前を覚えていない場合は、数式タブの関数の挿入を利用する方法もあります。

数式タブから統計を選び、COVARIANCE.Sを選択すると、引数を入力する画面が表示されます。

配列1と配列2の入力欄に、それぞれ対応する数値範囲を指定しましょう。

引数入力画面では、数式の計算結果を確認しながら操作できるため、初めて共分散を扱う場合にも便利です。

 

共分散の意味と正負の読み方

広告費	売上	変化の傾向
増える	増える	正の共分散
減る	減る	正の共分散
増える	減る	負の共分散
変化がばらばら	変化がばらばら	0に近い共分散

続いては、計算した共分散の値が何を示しているのかを確認していきます。

共分散は、2つの変数が平均からどの方向へ、どの程度ずれやすいかを表す指標です。

正の共分散となるケース

共分散が正の値なら、一般に一方が平均より大きいとき、もう一方も平均より大きくなりやすい傾向があります。

広告費が平均より多い月に売上も平均より高く、広告費が少ない月に売上も低いなら、各月の平均との差の積は正になりやすくなります。

その積を合計して計算するため、結果として正の共分散になります。

正の共分散は、2つのデータが同じ方向に動く傾向を示す言葉です。

ただし、広告費を増やしたことが売上増加の原因であるとまでは判断できません。

季節要因、キャンペーン、商品価格、店舗数など、別の要因が両方に影響している可能性も考える必要があります。

負の共分散となるケース

共分散が負の値なら、一方が平均より大きいときに、もう一方は平均より小さくなりやすい状態です。

たとえば商品の値引き率が大きいほど販売単価が低くなるデータでは、値引き率と単価の共分散が負になることがあります。

勉強時間と欠席日数のように、一方が増えると他方が減る関係でも負の値が現れやすくなります。

負の共分散は悪い結果を意味するものではありません。

データの動く方向が反対であることを示す、分析上の重要な手がかりです。

値の符号だけに注目するのではなく、どの項目同士を比較しているのか、業務上どのような関係が自然かを一緒に確認しましょう。

ゼロに近い値の扱い

共分散が0に近い場合は、2つの変数が直線的には連動していない可能性があります。

ただし、0に近いからといって完全に無関係とは限りません。

たとえば、気温がある水準までは売上を伸ばし、それを超えると売上が落ちるような曲線的な関係では、共分散だけでは傾向をつかみにくいことがあります。

また、データ数が少ないと偶然のばらつきに左右されやすくなります。

共分散は方向を確認する入口であり、結論を単独で決める数値ではないと考えるのが実務的です。

 

不偏共分散と母共分散の使い分け

関数	用途	分母
COVARIANCE.S	標本から傾向を推定する場合	データ数－1
COVARIANCE.P	対象全体を集計した場合	データ数

続いては、不偏共分散と母共分散の違い、そして関数の選び方を確認していきます。

エクセルにはCOVARIANCE.SとCOVARIANCE.Pがあり、似た関数名でも対象となるデータの考え方が異なります。

COVARIANCE.Sを使う場面

COVARIANCE.Sは、抽出した一部のデータから全体の性質を推定するときに使う不偏共分散の関数です。

全顧客のうち無作為に選んだ100人の購買データ、全国店舗のうち調査対象となった店舗の売上データなどが代表例です。

標本で計算したばらつきを、そのまま母集団全体のばらつきとみなすと、小さめに見積もられやすい特徴があります。

そこで不偏共分散では、データ数をnとしたときにnではなくn－1で割る補正を行います。

日常的な分析で一部の観測値を扱う場合は、COVARIANCE.Sを選ぶ場面が多くなります。

COVARIANCE.Pを使う場面

COVARIANCE.Pは、集めたデータが対象となる母集団のすべてである場合に使う母共分散の関数です。

たとえば、ある小規模なチームに所属する全社員の評価点と研修時間を分析する場合、そのチーム全員の値がそろっていれば母共分散として扱えます。

ある年度に存在した全商品の価格と販売個数を漏れなく集計する場合も、母集団全体を扱う例です。

母共分散では、平均との差の積の合計をデータ数nで割ります。

対象全体を集めたつもりでも、欠損や除外された記録があるなら、母集団と断定できないことがあります。

データの範囲だけでなく、収集条件も確認してから関数を選びましょう。

旧関数との違い

古いエクセルでは、共分散を求める関数としてCOVAR関数が使われていました。

現在のエクセルでは、標本用のCOVARIANCE.Sと母集団用のCOVARIANCE.Pを使うことで、計算の意図を明確にできます。

古いブックを開いたときにCOVAR関数が残っていても、すぐにエラーになるわけではありません。

ただし、新しく作る表や引き継ぐ資料では、不偏共分散ならCOVARIANCE.S、母共分散ならCOVARIANCE.Pと書き分けるほうが分かりやすくなります。

 

数式バーとオートフィルによる計算表

担当者	訪問件数	受注件数	平均との差の積
A	18	4	=(B2-AVERAGE($B$2:$B$7))*(C2-AVERAGE($C$2:$C$7))
B	22	6	数式を下へコピー
C	15	3	数式を下へコピー
D	27	8	数式を下へコピー
E	20	5	数式を下へコピー
F	24	7	数式を下へコピー

続いては、共分散の計算内容を目で追えるように、数式バーとオートフィルで計算表を作る方法を確認していきます。

関数だけで結果を出せますが、平均との差や積を列ごとに表示すると、共分散の仕組みを理解しやすくなります。

平均との差を求める数式

まず、訪問件数の平均と受注件数の平均を別のセルに計算してもよいですし、数式の中でAVERAGE関数を使ってもかまいません。

D2セルに入力する式は、=(B2-AVERAGE($B$2:$B$7))*(C2-AVERAGE($C$2:$C$7))です。

ドル記号を付けた$B$2:$B$7と$C$2:$C$7は絶対参照です。

この指定により、数式を下の行へコピーしても、平均を計算する範囲がずれません。

オートフィルを使う数式では、固定したい範囲を絶対参照にすることが重要です。

オートフィルで下の行へコピー

D2セルの数式を入力したら、セル右下に表示される小さな四角であるフィルハンドルにマウスポインターを合わせます。

ポインターが黒い十字に変わったことを確認し、D7まで下方向へドラッグします。

各行ではB列とC列の参照先だけがB3とC3、B4とC4のように変わり、平均を求める絶対参照の範囲は固定されたままです。

この仕組みにより、各担当者について平均との差の積を効率よく作成できます。

数式をコピーした後は、D列の値に正と負が混在することを確認してください。

両方が平均より高い、または両方が平均より低い行では正の値になりやすく、一方だけが高い行では負の値になりやすくなります。

合計値から不偏共分散を確認

D2からD7までの合計をSUM関数で求め、その結果をデータ数－1で割ると不偏共分散を確認できます。

COUNT関数は数値が入力されたセル数を数えるため、データ件数の確認にも使えます。

途中に空白セルや文字列がある場合、COUNTの結果と実際に比較したい組数がずれることがあります。

そのため、実務ではCOVARIANCE.S関数を使って集計し、必要に応じて計算表で仕組みを検証する使い方が分かりやすいでしょう。

 

共分散関数で発生しやすいエラー

表示	主な原因	確認内容
#N/A	2つの配列の件数が異なる	開始行と終了行
#DIV/0!	数値データが不足している	2組以上の数値
想定外の値	空白や単位の混在	入力形式と範囲

続いては、共分散関数を入力したのにエラーが出る場合や、値の解釈に迷う場合を確認していきます。

数式そのものは短くても、データ範囲の選び方や入力形式によって結果が変わることがあります。

配列の件数が異なる場合

COVARIANCE.S関数では、配列1と配列2に指定するデータ数を同じにする必要があります。

たとえばB2:B7は6件なのに、C2:C6は5件というように範囲の終わりが異なると、対応する組を正しく作れません。

このような場合は#N/Aエラーが表示されることがあります。

数式バーを確認し、最初のセルと最後のセルが両方の範囲でそろっているかを見直しましょう。

月別データでは、片方の列にだけ1か月分の入力漏れがあるケースもあります。

同じ行に対応する2つの値がそろっていることが、共分散を計算する前提です。

空白セルと文字列の混在

データの中に空白セル、ハイフン、未入力、文字列として保存された数字が混在すると、期待した件数で計算されない場合があります。

見た目が12でも、セル内に文字列として入力されていると、数値として扱われないことがあります。

セルの左上に緑色の三角が表示されている場合は、エラーインジケーターから数値へ変換できることがあります。

また、売上の列に円という文字を直接入力すると、計算用の数値として扱いにくくなります。

金額は数値だけを入力し、表示形式で通貨や桁区切りを設定する方法がおすすめです。

データを整えることは、共分散だけでなく平均、標準偏差、相関係数を正しく求めるためにも欠かせません。

単位が異なる値の解釈

共分散の絶対値は、データの単位によって大きく変わります。

広告費を万円単位から円単位へ変更すると、広告費の数値が1万倍になるため、共分散も大きく変わります。

同じデータでも入力単位を変えれば、共分散の数値をそのまま比較できなくなる点に注意が必要です。

複数の組み合わせの強さを比較したいときは、共分散より相関係数が適しています。

相関係数はCORREL関数で求められ、－1から1までの範囲で表されます。

共分散で方向を確認し、相関係数で関係の強さを比較する流れにすると、分析結果を説明しやすくなります。

 

相関係数と散布図を組み合わせる分析

確認方法	主な役割	エクセルでの手段
共分散	同方向か逆方向か	COVARIANCE.S
相関係数	直線的な関係の強さ	CORREL
散布図	点の分布と外れ値	挿入タブの散布図

続いては、共分散の結果をより実用的に読み解くため、相関係数と散布図を組み合わせる分析を確認していきます。

共分散だけでは単位の影響を受けるため、複数の視点からデータを確かめることが大切です。

CORREL関数による相関係数

広告費と売上の相関係数を求めるには、=CORREL(B2:B7,C2:C7)と入力します。

0に近い場合は、直線的な関係が弱いことを表します。

たとえば共分散が正でも相関係数が0に近ければ、同方向の傾向はあっても強い関係とはいえない場合があります。

反対に、単位の異なる複数の項目を比較するなら、相関係数のほうが共通の尺度で比較しやすくなります。

共分散は向き、相関係数は標準化された強さという違いを意識しましょう。

散布図による外れ値の確認

散布図を作るには、広告費と売上の2列を選択し、挿入タブから散布図を選びます。

横軸に広告費、縦軸に売上が配置され、月ごとのデータが点として表示されます。

点が右上がりに並ぶなら正の共分散と正の相関が考えられます。

右下がりなら負の共分散と負の相関が考えられます。

一部の点だけが大きく離れている場合は、外れ値が共分散や相関係数に強く影響しているかもしれません。

入力ミス、特別セール、臨時休業、集計期間の違いなど、外れ値が生まれた理由を確認してください。

外れ値を安易に削除するのではなく、理由を記録したうえで、含めた場合と除いた場合の結果を比較する考え方もあります。

分析結果を報告する視点

分析結果を報告するときは、共分散の数値だけを示すより、対象期間、データ件数、単位、相関係数、散布図の特徴を合わせて伝えると理解されやすくなります。

たとえば、広告費と売上には正の共分散があり、相関係数も高く、散布図はおおむね右上がりだったと説明できます。

一方で、共分散が正でもデータ数が少ない場合や、特定の1か月だけが大きく影響している場合は、その点も補足しましょう。

統計値は意思決定の材料であり、原因を証明するものではありません。

数値と業務上の状況を組み合わせて読むことで、改善施策につながる分析になります。

 

まとめ 不偏共分散を求めるエクセル関数の使い方

エクセルで不偏共分散を求めるときは、標本データの2つの範囲を指定してCOVARIANCE.S関数を使用します。

数式は、=COVARIANCE.S(B2:B7,C2:C7)のように入力し、1行目にある見出しを含めず、対応する数値データだけを指定することが基本です。

不偏共分散は、標本から全体の傾向を推定するときに使う値であり、分母をデータ数－1として計算します。

対象となるデータをすべて集計している場合は、COVARIANCE.P関数で母共分散を求めます。

共分散が正なら同じ方向へ動く傾向、負なら反対方向へ動く傾向、0に近いなら直線的な連動が弱い可能性を確認できます。

ただし、共分散の値は単位の影響を受けるため、数値の大きさだけで関係の強さを比較することはできません。

関係の強さを比較する場合はCORREL関数で相関係数を求め、散布図で外れ値や分布を確認しましょう。

データ件数の不一致、空白セル、文字列として入力された数字はエラーや誤った結果の原因になります。

共分散を正しく活用するには、関数の使い方だけでなく、対応するデータを同じ件数で整えることが重要です。

不偏共分散、相関係数、散布図を組み合わせて、エクセルの数値データをより深く分析していきましょう。