Excelで売上、テストの点数、作業時間などのデータを集計すると、平均値だけでは数値のばらつきが見えにくい場面があります。
そのばらつきを数値で表す代表的な指標が分散です。
分散を使うと、平均が同じデータでも、値が安定しているのか、大きく散らばっているのかを判断しやすくなります。
Excelには母分散、不偏分散、標本分散を求める関数が用意されており、数式を手入力して計算する必要はほとんどありません。
分散は各データと平均値との差を二乗し、その合計をデータ数で割って求める指標です。
全件データを扱う場合はVAR.P、標本データから全体を推定する場合はVAR.Sを使うのが基本になります。
この記事では、分散の意味、平均との関係、Excel関数の違い、実際の入力方法、数値を読むときの注意点まで順番に解説します。
サンプルデータはすべて1行目が見出し、2行目以降に数値が入力されている前提です。
Excelで分散を求める方法【VAR.P関数とVAR.S関数】
それではまず、Excelで分散を計算する具体的な方法について解説していきます。
| 行 | A列 | B列 | C列 |
|---|---|---|---|
| 1 | 担当者 | 1日の対応件数 | 備考 |
| 2 | 田中 | 18 | 入力データ |
| 3 | 佐藤 | 25 | 入力データ |
| 4 | 鈴木 | 20 | 入力データ |
| 5 | 高橋 | 17 | 入力データ |
| 6 | 伊藤 | 30 | 入力データ |
| 7 | 母分散 | =VAR.P(B2:B6) | 全件を対象 |
| 8 | 不偏分散 | =VAR.S(B2:B6) | 標本を対象 |
母分散を求めるVAR.P関数
VAR.P関数は、手元にあるデータを全体として扱うときに使う関数です。
たとえば、ある部署に所属する全社員の月間残業時間、店舗に在籍する全スタッフの売上、学年全員のテスト結果など、対象グループの数値をすべて集められている場合に適しています。
上の表では、B2からB6に5人分の対応件数が入力されています。
B7セルに=VAR.P(B2:B6)と入力すると、5人全員を母集団とみなした分散を計算できます。
入力する数式は =VAR.P(B2:B6) です。
範囲内の文字列や空白セルは基本的に計算対象から除外されますが、意図しない数値が混ざると結果が変わるため、対象範囲は確認しましょう。
以前のExcelではVARPという関数名も使われていましたが、現在はVAR.P関数を使用する方法が分かりやすく推奨されます。
データを完全に把握しているなら、全体のばらつきをそのまま表すVAR.P関数を選びましょう。
不偏分散を求めるVAR.S関数
VAR.S関数は、全体の一部だけを抽出した標本データから、母集団のばらつきを推定したいときに使います。
全国の顧客すべてにアンケートを実施する代わりに、一部の回答者だけを調査した場合が代表例です。
B8セルに=VAR.S(B2:B6)と入力すると、不偏分散が表示されます。
VAR.S関数では、偏りを補正するため、平均との差の二乗和をデータ数ではなくデータ数から1を引いた値で割ります。
そのため、同じデータ範囲を指定した場合、通常はVAR.S関数の結果のほうがVAR.P関数より大きくなります。
5件のデータであれば、VAR.Pは5で割り、VAR.Sは4で割る計算です。
標本から全体を推測する分析ではVAR.S、全数を集計する実務ではVAR.Pと考えると、関数を選びやすくなります。
ただし、社内の月次集計のように、単に現時点の全データの散らばりを知りたいだけなら、無理にVAR.S関数を使う必要はありません。
数式を入力してオートフィルする操作
部署別や商品別に分散を並べて求めるときは、最初のセルに数式を入力したあと、オートフィルでコピーすると効率的です。
たとえば、B列、C列、D列に月別の実績があり、7行目で各月の分散を比較したい場合、B7に=VAR.P(B2:B6)と入力します。
その後、B7セル右下の小さな四角であるフィルハンドルを右方向へドラッグすると、C列やD列の参照範囲へ自動的に変わります。
このとき、列を固定したい場合には、$記号を使った絶対参照が役立ちます。
たとえば、平均との差を計算する途中式で平均値がB9セルにあるなら、=B2-$B$9のように入力します。
【操作のポイント】VAR.PとVAR.Sは似た関数ですが、分母の扱いが異なります。全件か標本かを先に決めてから、集計セルへ数式を入力しましょう。
分散の意味と平均値の関係
続いては、分散が何を示す数値なのか、平均値との関係を確認していきます。
| グループ | データ | 平均 | 分散の特徴 |
|---|---|---|---|
| A | 48、50、52、49、51 | 50 | 平均の近くに集まる |
| B | 30、40、50、60、70 | 50 | 平均から大きく離れる |
| C | 50、50、50、50、50 | 50 | ばらつきがない |
平均だけでは判断しにくいデータの違い
平均値は、データ全体の中心的な水準を知るために便利な数値です。
しかし、平均が同じでも、個々の値が平均の周辺に集まっているとは限りません。
表のAグループとBグループは、どちらも平均が50です。
一方でAグループは48から52の範囲に収まっているのに対し、Bグループは30から70まで広がっています。
分散は平均からどの程度離れているかを数値化するため、平均値だけでは見落とす安定性の違いを確認できます。
売上の平均が高くても月ごとの変動が激しい場合、予算や在庫の計画を立てにくくなるかもしれません。
逆に平均が少し低くても、毎月の値が安定していれば、予測しやすい業務と判断できるでしょう。
平均との差を二乗する理由
分散を計算するときは、各データから平均値を引き、その差を二乗します。
差をそのまま足すだけでは、平均より大きい値と小さい値が打ち消し合って、合計が0になってしまうためです。
たとえば平均が50で、データが40と60なら、平均との差はそれぞれマイナス10と10になります。
このまま合計すると0ですが、差を二乗すれば100と100になり、平均から離れている事実を反映できます。
分散の基本式は、各データと平均値との差を二乗して合計し、データ数で割る形です。
母分散では 分散 = Σ（各データ − 平均）² ÷ データ数 と考えます。
二乗を使うため、平均との差が大きい値ほど分散への影響が急激に大きくなります。
極端に大きい値や小さい値が含まれている場合は、分散が大きく変わる点にも注意が必要です。
分散がゼロになるケース
すべてのデータが完全に同じ値であれば、平均との差はすべて0です。
0を二乗しても0なので、この場合の分散は0になります。
たとえば5人全員の評価点が80点なら、平均は80点、分散も0です。
分散が小さいほど値は平均付近に集まり、分散が大きいほど値は広く散らばっていると読み取れます。
ただし、分散の数値だけを別の単位のデータと直接比べることは慎重に行いましょう。
分散は元の単位を二乗した単位になるため、売上額と作業時間の分散を単純に比べるのは適切ではありません。
【操作のポイント】平均と分散はセットで確認すると、代表的な水準と数値の安定度を同時に把握できます。平均だけで結論を出さないことが大切です。
母分散・標本分散・不偏分散の違い
続いては、母分散、標本分散、不偏分散の違いと、Excelでの考え方を確認していきます。
| 種類 | 主な用途 | 分母 | Excel関数 |
|---|---|---|---|
| 母分散 | 母集団すべてのばらつき | n | VAR.P |
| 標本分散 | 標本データ内のばらつき | n | 一般的な途中計算 |
| 不偏分散 | 標本から母集団を推定 | n−1 | VAR.S |
母集団と標本の考え方
母集団とは、分析したい対象全体を指す言葉です。
たとえば、ある会社の全従業員、ある月に出荷した全商品、全国のすべての利用者などが母集団に当たります。
一方の標本は、母集団から一部を取り出したデータです。
全国の利用者を調べる代わりに1,000人へアンケートを実施した場合、その1,000人分が標本になります。
全員分の実績を管理する勤怠表なら、基本的には母集団を扱っているためVAR.P関数が自然です。
調査結果の一部から、全顧客の傾向を推定するならVAR.S関数を検討します。
Excel関数を選ぶ前に、入力済みの数値が全件なのか、一部の抽出なのかを確認しましょう。
この区別が曖昧なまま関数を選ぶと、計算自体は正しくても、分析の意味がずれる可能性があります。
標本分散と不偏分散の分母
標本分散は、取得した標本そのものの散らばりを表すときの考え方です。
平均との差の二乗和をデータ数nで割ります。
不偏分散は、その標本から未知の母集団の分散を推定する目的で、分母をn−1に変更します。
n−1で割る補正はベッセル補正と呼ばれます。
標本の平均値を使うと、ばらつきが実際より少し小さく見積もられやすいため、その偏りを補う仕組みです。
標本分散 = Σ（各データ − 標本平均）² ÷ n
不偏分散 = Σ（各データ − 標本平均）² ÷ （n − 1）
Excelでは標本分散を直接求める場面より、推定用の不偏分散としてVAR.S関数を使う場面が多くあります。
実務で迷ったときの判断基準
日報、在庫一覧、全従業員の評価一覧など、表に必要なデータがすべて揃っているならVAR.P関数を選びます。
品質検査で製造ロットから数個だけ抜き取った場合や、市場調査で回答者を抽出した場合は、VAR.S関数が候補です。
データ数が大きくなるほど、VAR.P関数とVAR.S関数の差は小さくなります。
しかし、少数のデータでは差が比較的大きくなるため、特に数件から数十件の集計では区別を意識しましょう。
関数名の末尾にあるPはPopulation、SはSampleを連想すると覚えやすいでしょう。
集計表に分散を掲載する場合は、母分散なのか不偏分散なのかも、見出しや注記で分かるようにしておくと誤解を防げます。
【操作のポイント】全員分や全件分の集計にはVAR.P、一部を抽出して全体を推定する分析にはVAR.Sを使います。用途に合わせて列見出しも明記しましょう。
分散関数の入力手順と測定データの比較
続いては、分散関数を入力し、複数のデータを比較する手順を確認していきます。
| 行 | A列 | B列 | C列 | D列 |
|---|---|---|---|---|
| 1 | 担当者 | 第1週 | 第2週 | 第3週 |
| 2 | 田中 | 18 | 20 | 19 |
| 3 | 佐藤 | 25 | 22 | 26 |
| 4 | 鈴木 | 20 | 21 | 18 |
| 5 | 高橋 | 17 | 19 | 20 |
| 6 | 伊藤 | 30 | 28 | 27 |
| 7 | 母分散 | =VAR.P(B2:B6) | オートフィル | オートフィル |
関数の挿入による数式作成
数式を覚えていない場合は、Excelの関数の挿入機能を使う方法があります。
結果を表示したいセルを選択し、数式タブから関数の挿入をクリックします。
検索欄にVAR.PまたはVAR.Sと入力すると、目的の関数を見つけやすくなります。
関数の引数画面が表示されたら、数値1の欄にB2:B6のようなデータ範囲を指定します。
複数の離れた範囲を指定したいときは、数値2、数値3の欄へ追加することも可能です。
ただし、月別や部署別の比較では、連続する1列の範囲を指定したほうが集計内容を見直しやすくなります。
関数を手入力する場合は、半角の等号から始め、関数名の後ろに半角の丸括弧を付けます。
範囲指定はB2:B6のように開始セルと終了セルを指定します。
オートフィルによる週別分散の算出
最初のB7セルに=VAR.P(B2:B6)を入力し、Enterキーを押すと第1週の分散が計算されます。
次にB7セルを選択し、セル右下のフィルハンドルをD7まで横へドラッグします。
するとC7には=VAR.P(C2:C6)、D7には=VAR.P(D2:D6)というように、参照先が自動調整されます。
関数の挿入
フォント
配置
数式をコピーしたあとは、第1週から第3週までの分散を比較します。
値が小さい週は担当者ごとの対応件数が比較的そろっており、値が大きい週は個人差が大きいと判断できます。
数式を横方向へコピーする際は、見出し行を含めず、数値が入った行だけを範囲に指定することが重要です。
関数結果を比較するときの注意点
分散が大きいからといって、必ずしも悪い状態とは限りません。
営業成績なら担当エリアや経験年数の違い、製造時間なら製品仕様の違いが、ばらつきの理由になっている場合があります。
まずは分散が大きい列を見つけ、その後に元データを確認して原因を探る流れが実務的です。
また、データの規模が違う集団を比較する場合、分散だけでは判断しにくいことがあります。
平均に対する相対的なばらつきを見たいときは、標準偏差や変動係数も合わせて検討しましょう。
分散の平方根を求める関数はSTDEV.PまたはSTDEV.Sです。
標準偏差は元データと同じ単位で表されるため、数値の大きさを直感的に把握しやすい指標です。
【操作のポイント】先頭セルの数式を正しく作成してからオートフィルを使うと、月別や部署別の集計を短時間で完成させられます。コピー後は参照範囲も確認しましょう。
平均との差を使った分散の手計算
続いては、分散の計算式を理解するために、平均との差を使った手計算を確認していきます。
| 行 | A列 | B列 | C列 | D列 |
|---|---|---|---|---|
| 1 | 番号 | 得点 | 平均との差 | 差の二乗 |
| 2 | 1 | 60 | =B2-$B$7 | =C2^2 |
| 3 | 2 | 70 | =B3-$B$7 | =C3^2 |
| 4 | 3 | 80 | =B4-$B$7 | =C4^2 |
| 5 | 4 | 90 | =B5-$B$7 | =C5^2 |
| 6 | 5 | 100 | =B6-$B$7 | =C6^2 |
| 7 | 平均 | =AVERAGE(B2:B6) | 二乗和 | =SUM(D2:D6) |
| 8 | 母分散 | =D7/COUNT(B2:B6) | 不偏分散 | =D7/(COUNT(B2:B6)-1) |
平均値を求めるAVERAGE関数
分散を手順どおりに計算するには、最初に平均値を求めます。
この例ではB7セルに=AVERAGE(B2:B6)と入力します。
60、70、80、90、100の平均は80です。
AVERAGE関数は、指定したセル範囲にある数値の平均を返します。
分散は平均からの距離を使うため、平均値を求めるセルは途中計算の基準になります。
平均値のセルを別の場所へ移す場合は、以降の数式で参照するセルも合わせて変更しましょう。
平均との差と二乗値の作成
C2セルには=B2-$B$7と入力します。
この数式は、B2の得点から平均値が入ったB7セルを引く式です。
$B$7のように列記号と行番号の両方へ$記号を付けることで、オートフィルしても平均値の参照先を固定できます。
C2の数式をC6までコピーすると、各行の平均との差が表示されます。
次にD2セルへ=C2^2と入力すると、平均との差の二乗を計算できます。
この式もD6までオートフィルします。
二乗を表す記号はハット記号で、C2^2はC2の値を2乗する数式です。
二乗値を合計するには、D7セルへ=SUM(D2:D6)と入力します。
母分散と不偏分散の完成
二乗和が求められたら、母分散は二乗和をデータ数で割って計算します。
B8セルには=D7/COUNT(B2:B6)と入力できます。
COUNT関数は、範囲内にある数値セルの件数を数える関数です。
不偏分散は、D7をデータ数から1を引いた数で割ります。
D8セルには=D7/(COUNT(B2:B6)-1)と入力しましょう。
手計算の式は分散の仕組みを理解するために役立ちます。
日常的な集計では、VAR.P関数またはVAR.S関数を使ったほうが短く、入力ミスも減らせます。
途中計算の列を作ると、どのデータが平均から大きく離れているかも確認しやすくなります。
異常値の候補を探すときにも有効な方法です。
【操作のポイント】平均との差を求める数式では、平均値のセルを絶対参照にします。最初の数式だけを作成し、フィルハンドルで下方向へコピーしましょう。
分散を使う集計とエラー対策
続いては、分散を実務の集計で活用する方法と、よくあるエラーへの対処を確認していきます。
| 確認項目 | 確認内容 | 対応の考え方 |
|---|---|---|
| 空白セル | 未入力のデータがある | 対象件数が意図どおりか確認 |
| 文字列 | 数値に見える文字が混在 | 数値へ変換してから集計 |
| エラー値 | 範囲にエラーが含まれる | 元のエラーを修正 |
| 対象範囲 | 見出しや合計行を含めた | 入力データだけを選択 |
空白セルと文字列がある場合
VAR.P関数やVAR.S関数でセル範囲を指定した場合、範囲内の空白セルや文字列は通常、数値として計算されません。
そのため、表の途中に空白があっても、数式がすぐにエラーになるとは限りません。
ただし、本来入力されるべき数値が空白になっている場合、データ件数が減るため、分散の結果が意図と異なる可能性があります。
数値に見えても、先頭にアポストロフィが付いている場合や、外部システムから文字列として貼り付けられた場合は、計算対象外になることがあります。
集計前にCOUNT関数で数値の件数を確認すると、対象データの不足に気付きやすくなります。
たとえば=COUNT(B2:B100)を別セルへ入力し、想定している件数と一致するかを確認しましょう。
少なすぎるデータによるエラー
VAR.S関数は、不偏分散を求めるために少なくとも2件の数値データが必要です。
数値が1件しかない場合、分母が0になるため、エラーが表示されます。
これは関数の不具合ではなく、ばらつきを推定できるだけのデータがないことを示しています。
VAR.P関数も、データがない範囲を指定するとエラーになることがあります。
入力途中の表で計算式を先に配置する場合は、IFERROR関数で表示を整える方法もあります。
たとえば=IFERROR(VAR.S(B2:B6),””)と入力すると、エラー時には空白を表示できます。
ただし、エラーを隠すだけでは原因の解決にはならないため、データ件数そのものを確認する姿勢が必要です。
分散と標準偏差の使い分け
分散は統計計算の基礎として重要ですが、数値の単位が二乗になるため、報告書では読み取りにくい場合があります。
たとえば時間の分散は時間の二乗、金額の分散は金額の二乗として表されます。
元の単位でばらつきを示したいなら、標準偏差を使うと理解しやすくなります。
母標準偏差は=STDEV.P(B2:B6)、標本標準偏差は=STDEV.S(B2:B6)で求められます。
分散は計算の根拠や統計分析向け、標準偏差は現場での説明や比較向けという使い分けもできます。
分散と標準偏差はどちらもばらつきを表す指標です。
標準偏差は分散の平方根であり、分散が大きければ標準偏差も大きくなります。
【操作のポイント】分散の結果だけで判断せず、データ件数、空白、文字列、異常値を確認しましょう。報告用には標準偏差を併記すると伝わりやすくなります。
まとめ Excelで分散を求める関数と計算方法
| 目的 | 使用する関数 | 考え方 |
|---|---|---|
| 全件データのばらつき | VAR.P | 母分散 |
| 一部の標本から全体を推定 | VAR.S | 不偏分散 |
| 平均値の算出 | AVERAGE | 中心的な値 |
| 元の単位でばらつきを確認 | STDEV.PまたはSTDEV.S | 標準偏差 |
Excelで分散を求めるときは、全データを集計するならVAR.P関数、標本データから全体を推定するならVAR.S関数を使います。
分散は平均からの散らばりを示す数値であり、平均値だけでは分からないデータの安定性を確認できる指標です。
数式は=VAR.P(B2:B6)や=VAR.S(B2:B6)のように、1行目の見出しを除いた数値範囲を指定して入力します。
複数列の集計では、最初のセルに数式を入力してからオートフィルを使うと、効率よく計算できます。
母分散と不偏分散の違いは、分母がデータ数か、データ数から1を引いた値かという点です。
全件なのか標本なのかを確認して関数を選べば、用途に合った結果を得やすくなります。
また、空白セル、文字列、エラー値、異常値が含まれていないかも確認しましょう。
分散の数値が大きい場合は、元データを見直し、どの値が平均との差を広げているのかを調べることが大切です。
読み手に伝わりやすい資料を作るなら、分散に加えて平均値や標準偏差も並べて表示するとよいでしょう。
