エクセルで2つの数値データの関係を確認したいとき、平均からどのように同時に離れる傾向があるかを数値化できるのが共分散です。
売上と広告費、気温と飲料の販売数、勉強時間とテスト得点など、片方が増えたときにもう片方も増えやすいのかを把握したい場面で役立ちます。
ExcelではCOVARIANCE.S関数を使うと、標本データの共分散を数式だけで素早く計算できます。
ただし、引数の並び、データ範囲の大きさ、母集団用の関数との使い分けを誤ると、意図した分析にならないことがあります。
この記事のポイント
・COVARIANCE.S関数は標本データの共分散を求める関数です。
・同じ行に対応する2組の数値を指定して計算します。
・単位の影響を受けるため、関係の強さまで比較したいときは相関係数も確認します。
ここでは、サンプルデータを使いながら、エクセルで共分散を求める手順と数式の意味を詳しく解説していきます。
エクセルで共分散を求める方法【COVARIANCE.S関数の入力】
| A列 | B列 | C列 |
|---|---|---|
| 月 | 広告費 | 売上 |
| 4月 | 12 | 86 |
| 5月 | 15 | 94 |
| 6月 | 18 | 105 |
| 7月 | 20 | 109 |
それではまず、COVARIANCE.S関数をセルに入力して共分散を計算する基本手順について解説していきます。
COVARIANCE.S関数の書式
COVARIANCE.S関数の書式は、=COVARIANCE.S(配列1,配列2)です。
=COVARIANCE.S(B2:B5,C2:C5)
上の例では、B列の広告費とC列の売上について、2行目から5行目までの対応する数値を使って標本共分散を求めます。
見出しが1行目にある前提では、数式にB1やC1を含めず、最初の数値が入った2行目から範囲指定することが大切です。
結果を表示したい空白セルを選び、数式バーまたはセルに数式を入力してEnterキーを押せば計算は完了します。
関数名の末尾にあるSはSampleの頭文字であり、調査したデータが全体の一部である標本を対象とする考え方を示しています。
通常の業務では、すべての顧客やすべての期間を完全に集めたデータではなく、一部の観測値を分析することが多いため、COVARIANCE.Sを使う場面が多くなります。
入力先セルと範囲選択
たとえばD2セルに結果を出すなら、D2セルをクリックしてから数式を入力します。
数式を手入力する代わりに、数式タブの関数の挿入から統計の分類を選び、COVARIANCE.Sを探す方法もあります。
関数の引数ダイアログが表示された場合は、配列1に広告費のB2:B5、配列2に売上のC2:C5を指定します。
2つの範囲は同じ件数で、各行のデータ同士が正しく対応している必要があります。
4月の広告費をB2、4月の売上をC2に配置しているなら、B2とC2が1組として扱われます。
片方だけ開始行をずらしてB2:B5とC3:C6のように指定すると、月が異なる値を組み合わせることになり、分析結果の意味が失われます。
データを追加する予定があるときは、Excelテーブルに変換して列名で参照する方法も便利です。
テーブルなら行が増えても参照範囲を更新しやすく、月次集計を継続するケースで入力漏れを防げます。
計算結果の読み取り
今回のデータでは、広告費も売上も平均より大きい月と、両方が平均より小さい月が多いため、計算結果は正の値になります。
共分散が正なら、一般に2つの値は同じ方向へ動きやすい傾向です。
一方が平均より大きいときにもう一方が平均より小さい組み合わせが多ければ、計算結果は負の値になります。
共分散が0に近い場合は、平均からのずれが一緒に動く明確な傾向が弱いと考えられます。
共分散の符号の目安
正の値は同方向に動きやすい傾向です。
負の値は逆方向に動きやすい傾向です。
0に近い値は直線的な連動が目立ちにくい状態です。
ただし、正の共分散が出たからといって、広告費の増加だけが売上増加の原因であるとは断定できません。
季節、価格改定、販促企画、商品の供給量など、ほかの要因も合わせて検討する姿勢が必要です。
【操作のポイント】数式は見出しを除いた数値範囲だけを指定し、同じ行が同じ対象を表しているか確認します。
共分散の意味と計算式
| 観測月 | 広告費の平均との差 | 売上の平均との差 |
|---|---|---|
| 4月 | マイナス | マイナス |
| 5月 | マイナス | マイナス |
| 6月 | プラス | プラス |
| 7月 | プラス | プラス |
続いては、共分散が何を表す数値なのか、計算式とともに確認していきます。
平均からの差を掛け合わせる考え方
共分散は、各データがそれぞれの平均値からどれだけ離れているかを確認し、その差を組ごとに掛け合わせて求める統計量です。
広告費が平均より大きく、売上も平均より大きい場合は、プラスとプラスを掛けるため結果はプラスになります。
広告費と売上がともに平均より小さい場合も、マイナスとマイナスを掛けるため結果はプラスです。
逆に、広告費だけが平均より大きく売上が平均より小さい場合は、プラスとマイナスの積となり、負の方向へ働きます。
共分散は、2つの変数が平均から同じ方向へ離れたか、反対方向へ離れたかを積み重ねた指標と考えると理解しやすいでしょう。
単に値が増えたか減ったかではなく、平均を基準にして関係を見る点が特徴です。
そのため、月ごとの金額が大きくても、平均からの離れ方に規則性がなければ共分散は大きくなりません。
標本共分散の数式
標本共分散 ＝ Σ｛各X値－Xの平均｝×｛各Y値－Yの平均｝÷（データ数－1）
COVARIANCE.S関数は、この標本共分散の考え方に従って計算します。
Σは各行で得られた掛け算の結果をすべて合計する記号です。
最後にデータ数そのものではなく、データ数から1を引いた値で割るところが、標本共分散の重要な特徴です。
これは、限られたデータから全体の傾向を推定するときに、偏りを補正するための扱いです。
4件のデータなら、合計した値を4ではなく3で割ります。
Excelが自動的に計算してくれるため、利用者が手作業で分母を調整する必要はありません。
ただし、COVARIANCE.Pとの違いを理解していないと、意図せず異なる分母で計算する可能性があります。
単位が結果に与える影響
共分散の値には、元データの単位がそのまま影響します。
広告費を万円で入力するか円で入力するかによって、同じ傾向を表すデータでも共分散の絶対値は大きく変化します。
売上を千円単位から円単位に変更した場合も同様です。
共分散の数値だけを見て、別の組み合わせと強さを単純比較するのは適していません。
たとえば広告費と売上の共分散が大きくても、単位が大きいことによる影響を受けているかもしれません。
共分散を活用しやすい場面
・正負の方向を知りたいときです。
・回帰分析や相関係数を計算する前の確認をしたいときです。
・同じ単位体系のデータを継続的に比較するときです。
関係の強さをマイナス1から1の範囲で比較したい場合は、後述するCORREL関数による相関係数を使うとよいでしょう。
【操作のポイント】共分散は符号と変動の方向を見る指標であり、金額などの単位を変えると値の大きさも変わります。
COVARIANCE.S関数とCOVARIANCE.P関数の違い
| 関数 | 対象 | 分母 |
|---|---|---|
| COVARIANCE.S | 標本 | データ数－1 |
| COVARIANCE.P | 母集団 | データ数 |
続いては、似た名前のCOVARIANCE.P関数との違いと、適切な選び方を確認していきます。
標本データで使うCOVARIANCE.S
COVARIANCE.S関数は、全体の中から抽出した一部のデータをもとに傾向を調べるときに使います。
たとえば、全国の顧客全員ではなく任意の100件だけを調査した場合や、長い販売期間のうち直近12か月だけを分析した場合が該当します。
現実の分析では、すべての対象を把握できないことが少なくありません。
そのため、迷ったときはまずCOVARIANCE.Sを候補にすると、一般的な標本分析に対応しやすくなります。
部署で作成したアンケート結果、抜き取り検査の結果、数か月分だけの実績データなども、全体を推定する目的なら標本として扱えます。
標本の数が少ないほど偶然の影響を受けやすいため、結果を判断するときは件数もあわせて見る必要があります。
母集団データで使うCOVARIANCE.P
COVARIANCE.P関数は、分析対象となる全データを漏れなく持っている場合に使う関数です。
=COVARIANCE.P(B2:B5,C2:C5)
たとえば、ある小規模な店舗で、対象年度に営業したすべての日の広告費と売上を完全に記録しており、その年度だけの実績を確定的に集計したい場合が考えられます。
このように、手元のデータが推測のための一部ではなく、分析対象そのものの全件であるならCOVARIANCE.Pが適しています。
COVARIANCE.Pでは、平均との差の積の合計をデータ数で割ります。
同じデータを使うと、多くの場合はCOVARIANCE.SのほうがCOVARIANCE.Pより少し大きな絶対値になります。
これは分母がデータ数より1小さいためであり、関数の計算ミスではありません。
旧関数との互換性
古いExcelファイルや過去の資料では、COVAR関数を見かけることがあります。
COVAR関数は旧形式の共分散関数であり、現在のExcelではCOVARIANCE.Pに相当する扱いです。
新しく数式を作成するなら、用途が伝わりやすいCOVARIANCE.SまたはCOVARIANCE.Pを使うのが安心です。
既存のブックにCOVAR関数が残っていても、直ちにエラーになるとは限りません。
しかし、ほかの人がファイルを見るときに標本か母集団かを判断しにくくなるため、更新時には関数の意図をコメントや見出しで補足すると親切です。
関数選択の目安
調査した一部のデータから傾向を推測するならCOVARIANCE.Sです。
対象全件の数値を確定集計するならCOVARIANCE.Pです。
古いCOVAR関数は、既存ファイルの互換性確認時に意味を把握します。
【操作のポイント】標本か母集団かを先に決めてから関数を選び、同じ資料内で計算基準を統一します。
共分散の計算手順と数式コピー
| A列 | B列 | C列 | D列 |
|---|---|---|---|
| 月 | 広告費 | 売上 | 共分散 |
| 4月 | 12 | 86 | 計算結果 |
| 5月 | 15 | 94 | ― |
| 6月 | 18 | 105 | ― |
続いては、実際のワークシートでCOVARIANCE.S関数を入力し、参照範囲を確認する手順を解説していきます。
データ表の準備
共分散を求める前に、比較したい2種類の数値を同じ行にそろえて入力します。
今回なら、A列に月、B列に広告費、C列に売上を入力します。
1行目はヘッダーにし、数値データは2行目以降に配置する形式です。
月の並びが片方の列だけずれていたり、途中に別の対象の数値が混ざったりすると、関数は誤った対応関係のまま計算します。
数式を入力する前に、各行が同じ月、同じ商品、同じ担当者などの同一対象を示しているかを確認しましょう。
空白セルがある場合も注意が必要です。
欠測値を0として扱うべきなのか、分析から外すべきなのかは、データの意味によって異なります。
単に空欄を残したまま範囲指定するのではなく、欠損理由を確認してから整形することが信頼できる集計につながります。
関数入力と確定操作
結果を表示するセルとしてD2を選択し、数式バーに=COVARIANCE.S(B2:B5,C2:C5)と入力します。
Enterキーを押すと、D2セルに共分散が表示されます。
共分散は2つのデータ範囲全体から1つの値を求める関数なので、行ごとの結果を出す関数ではありません。
そのため、D2に数式を入れたあと、通常はD3以下へコピーする必要はありません。
複数の商品カテゴリごとに共分散を出す場合など、列ごとに同じ計算パターンを繰り返すときには、相対参照と絶対参照の設定が重要になります。
複数列で横方向へコピーする例
=COVARIANCE.S(B$2:B$13,C$2:C$13)
行番号の前にドル記号を付けると、数式を横へコピーしても開始行と終了行が動きません。
列記号にはドル記号を付けていないため、右へコピーするとB列とC列の組み合わせがC列とD列へ移るなど、列だけを自動調整できます。
Excel画面での操作イメージ
画面上では、選択したD2セルと数式バーの内容を確認してからEnterキーで確定します。
赤枠で示したように、数式バーにはCOVARIANCE.S関数と2つの範囲が表示されます。
範囲をドラッグして指定する場合も、数式バーの参照先がB2:B5とC2:C5になっているか最後に確かめてください。
セル内にハッシュ記号やエラー表示が出たときは、列幅だけでなく関数の入力内容も確認しましょう。
【操作のポイント】共分散は範囲全体に対する1つの結果です。入力セルは集計表の見やすい位置に置き、数式バーで参照範囲を確認します。
共分散と相関係数の使い分け
| 比較項目 | 共分散 | 相関係数 |
|---|---|---|
| 確認しやすい内容 | 動く方向 | 関係の強さ |
| 値の範囲 | 単位により変化 | マイナス1から1 |
| Excel関数 | COVARIANCE.S | CORREL |
続いては、共分散と相関係数の違いを確認し、分析目的に応じて使い分ける方法を解説していきます。
CORREL関数による相関係数
相関係数を求めるときは、CORREL関数を使います。
=CORREL(B2:B5,C2:C5)
相関係数はマイナス1から1までの範囲で表されるため、単位が異なるデータ同士でも比較しやすい特徴があります。
1に近いほど強い正の相関、マイナス1に近いほど強い負の相関、0に近いほど直線的な関係が弱い状態です。
広告費を円で管理しても万円で管理しても、相関係数は基本的に変わりません。
複数の指標のうち、どの組み合わせがより連動しているかを比べたい場合には、共分散より相関係数が向いています。
ただし、相関係数が高いことも因果関係の証明ではありません。
分析目的に応じた選択
2つの数値が同方向か逆方向かを確認し、後続の統計処理に使うなら共分散が役立ちます。
一方で、売上と広告費、売上と来店客数、売上と気温のように複数の組み合わせを横断比較したい場合は、相関係数を使うと判断しやすくなります。
回帰分析では、共分散が傾きの計算に関係します。
データのばらつきを扱う分散や標準偏差と組み合わせることで、予測モデルの基礎となる考え方へ発展します。
まず共分散で方向を確認し、その後に相関係数で比較可能な強さを確認する流れにすると、数字の意味を取り違えにくくなります。
使い分けの考え方
共分散は、平均からの動きが同方向かを把握する段階です。
相関係数は、異なる指標間で関係の強さを比較する段階です。
どちらも因果関係を断定する数値ではありません。
散布図による見える化
数式の結果だけでは判断しにくい場合は、散布図を作成してデータの分布を確認します。
B列とC列の数値範囲を選択し、挿入タブから散布図を選べば、広告費と売上の組み合わせを点で表示できます。
右上がりに点が並ぶなら正の関係、右下がりなら負の関係が疑われます。
一部の点だけが大きく離れている場合は、外れ値が共分散や相関係数に強く影響している可能性があります。
散布図を併用すると、数値だけでは見逃しやすい外れ値、曲線的な関係、データ入力ミスを見つけやすくなります。
たとえば広告費が低いのに売上だけが非常に高い月があれば、特別なキャンペーン、季節要因、転記ミスなどを確認するきっかけになります。
【操作のポイント】共分散で方向をつかみ、比較にはCORREL関数、データの形の確認には散布図を組み合わせます。
COVARIANCE.S関数で起こりやすいエラーと注意点
| 確認項目 | 問題例 | 確認方法 |
|---|---|---|
| 範囲の件数 | 片方だけ1件少ない | 開始行と終了行を比較 |
| 数値形式 | 文字列の数値が混在 | 左寄せやエラー表示を確認 |
| データ数 | 有効な組が1件以下 | 数値データを数える |
続いては、COVARIANCE.S関数でエラーになった場合や、結果が想定と異なる場合の確認ポイントを解説していきます。
配列サイズが一致しないケース
COVARIANCE.S関数で代表的な問題は、配列1と配列2のデータ数が一致していないケースです。
たとえばB2:B10とC2:C9のように、一方の範囲だけが短いと、対応する組を正しく作れません。
数式を編集するときは、開始セルと終了セルを両方見比べ、範囲の行数が一致しているか確認します。
途中の空白行を含むデータでは、見た目の件数とExcelが認識する有効な数値の件数が異なることもあります。
月ごとのデータを追加した後は、片方の列だけ参照範囲が更新されていないというミスも起こりがちです。
数式バーをクリックし、色分けされた枠線がそれぞれどこまで伸びているかを見ると、範囲のずれを見つけやすくなります。
数値以外のデータが含まれるケース
セルに見える値が数字でも、文字列として保存されている場合があります。
たとえば先頭にアポストロフィが付いた数値、全角の記号が混在した金額、数値に見える空白文字などです。
データをほかのシステムからコピーした直後は、表示形式だけでは判別できないことがあります。
セルを選択して数式バーの内容を確認し、必要に応じてVALUE関数、区切り位置、貼り付けの形式を使って数値へ変換します。
エラー値が含まれている場合は、COVARIANCE.S関数もエラーを返す可能性があります。
集計前にSUM関数やCOUNT関数で数値として認識されているかを確認する習慣を付けると、原因の切り分けが早くなります。
データ数と外れ値の確認
標本共分散を求めるには、少なくとも2組以上の有効な数値が必要です。
1組しかない場合は、平均との差のばらつきを計算できないため、適切な結果は得られません。
また、データ数が極端に少ないと、たまたま含まれた1件の大きな値に結果が左右されやすくなります。
特別な月だけ大型案件が発生した、入力時に桁を誤った、単位が一部だけ異なるといった事情がないかを確認しましょう。
確認時の順番
最初に2つの範囲の開始行と終了行を確認します。
次に数値以外やエラー値が混ざっていないかを確認します。
最後に散布図や元資料を見て、外れ値の理由を調べます。
外れ値を削除する場合は、単に都合の悪い値を除くのではなく、入力ミスや対象外である根拠を記録しておくことが重要です。
【操作のポイント】エラー時は関数名を打ち直す前に、範囲の件数、数値形式、対応行、外れ値の順に確認します。
まとめ エクセルで共分散を求める方法（COVARIANCE.S関数）
エクセルで標本共分散を求める基本の数式は、=COVARIANCE.S(配列1,配列2)です。
1行目にヘッダーがある表では、B2:B5とC2:C5のように、数値が入っている2行目以降を指定します。
同じ行に対応する2つのデータを配置し、範囲の件数を一致させることが正しい計算の基本です。
結果が正なら同方向、負なら逆方向に平均から離れやすい傾向を読み取れます。
ただし共分散は単位の影響を受けるため、複数の関係の強さを比べたいときはCORREL関数による相関係数も確認しましょう。
分析対象の全件を使う母集団ならCOVARIANCE.P、一部のデータから傾向を調べる標本ならCOVARIANCE.Sを選びます。
数式の範囲、数値形式、外れ値を確認しながら、売上分析や品質管理、アンケート分析などに共分散を活用していきましょう。
