Excelでデータのばらつきを調べるとき、標本分散と不偏分散のどちらを使うべきか迷うことがあります。
関数名が似ているため混同しやすいものの、対象となるデータの意味を整理すれば、使い分けは難しくありません。
この記事では、1行目を見出しとしたサンプルデータを使い、標本分散をExcelで計算する方法、不偏分散との違い、数式の見方、実務での判断基準を解説します。
標本分散は、手元のデータそのもののばらつきを表す指標です。
不偏分散は、標本から母集団のばらつきを推定するときに用いる指標です。
Excelでは標本分散にVAR.P関数、不偏分散にVAR.S関数を使用します。
関数の選択理由まで理解しておくと、レポートや品質管理の集計でも数値を説明しやすくなります。
Excelで標本分散を求める関数入力
それではまず、Excelで標本分散を計算する基本操作について解説していきます。
| A列 | B列 | C列 |
|---|---|---|
| 1 | 測定番号 | 測定値 |
| 2 | 1 | 12 |
| 3 | 2 | 15 |
| 4 | 3 | 13 |
| 5 | 4 | 16 |
| 6 | 5 | 14 |
VAR.P関数の入力式
標本分散を求めたいセルを選び、B2からB6までの数値を対象にする場合は、数式バーへVAR.P関数を入力します。
=VAR.P(B2:B6)
この式では、B2からB6までの5件を手元にある全データとして扱い、その範囲内でどの程度値が散らばっているかを計算します。
計算結果は2となります。
数値が大きいほど平均値から離れたデータが多く、ばらつきが大きい状態です。
分散は元データを二乗して計算するため、単位も二乗になる点を覚えておきましょう。
オートSUMから関数を選ぶ操作
数式を直接入力しなくても、リボンから関数を選択して標本分散を求められます。
結果を表示したいセルをクリックし、数式タブからその他の関数、統計の順に進み、VAR.Pを選択します。
関数の引数ダイアログが開いたら、数値1にB2:B6を指定してOKを選びます。
範囲をドラッグして指定する方法なら、セル番地の入力ミスを抑えやすいでしょう。
ただし、見出しであるB1を範囲に含めないことが重要です。
文字列の見出しは通常は無視されますが、意図しない文字や別列まで選んでしまうと集計条件が分かりにくくなります。
計算結果の確認方法
VAR.P関数で表示された値だけを見ても、正しいか不安になる場合があります。
そのときはAVERAGE関数で平均値を別セルに求め、各データとの差を確認すると計算の意味を追いやすくなります。
平均値の式は=AVERAGE(B2:B6)です。
この例の平均値は14です。
12、15、13、16、14と平均値との差を二乗して合計し、データ数5で割ると標本分散の2になります。
VAR.Pは、平均との差を単純に平均した値ではなく、差を二乗して平均した値です。
そのため、平均との差が正と負で打ち消し合うことはありません。
【操作のポイント】標本分散を確認するときは、数式バーで参照範囲がB2:B6になっているかを確認し、見出し行や空白行を不用意に含めないようにします。
標本分散と不偏分散の計算基準
続いては、標本分散と不偏分散の違いを確認していきます。
| 項目 | 標本分散 | 不偏分散 |
|---|---|---|
| Excel関数 | VAR.P | VAR.S |
| 割る数 | データ数 n | nから1を引いた数 |
| 用途 | 対象データの記述 | 母集団の推定 |
分母の違い
標本分散は、平均との差の二乗和をデータ数nで割ります。
標本分散 = Σ（各データ − 平均値）の二乗 ÷ n
一方の不偏分散は、同じ二乗和をnから1を引いた数で割る計算です。
不偏分散 = Σ（各データ − 平均値）の二乗 ÷ （n − 1）
分母が小さい不偏分散のほうが、同じデータなら計算結果は標本分散より大きくなります。
先ほどの二乗和は10なので、標本分散は10÷5で2、不偏分散は10÷4で2.5です。
両者の差は関数の誤りではなく、分母の定義が異なることによる正常な差です。
母集団と標本の考え方
母集団とは、調べたい対象全体を指す統計用語です。
たとえば、ある工場が1日に生産した全製品の寸法をすべて記録できたなら、その全記録は母集団として扱えます。
この全記録のばらつきを知りたい場合は、VAR.P関数による標本分散を使います。
対して、生産された製品すべてを測定できず、無作為に抜き取った一部だけから全体のばらつきを推測するなら、その一部が標本です。
標本から母集団の分散を推定する場面では、VAR.S関数による不偏分散が適しています。
データが一部しかないから必ず不偏分散というわけではなく、何を目的に数値を出すかで判断することが大切です。
Excel関数名の読み分け
ExcelのPはPopulationの頭文字で、母集団として扱うデータに対応します。
SはSampleの頭文字で、標本に基づく推定に対応する関数です。
旧バージョンのExcelではVAR関数やVARP関数を見かける場合もありますが、新しく数式を作るならVAR.SとVAR.Pを使うと意図が伝わりやすくなります。
AVERAGE、STDEV.P、STDEV.Sなどの関連関数も、PとSの意味を共通して考えられます。
分散の平方根である標準偏差を求める際にも、分散と同じ基準で関数をそろえましょう。
【操作のポイント】データ全件の記録を要約するならVAR.P、抽出データから全体傾向を推定するならVAR.Sを候補にします。
VAR.P関数の引数と対象範囲
続いては、VAR.P関数で指定する引数とセル範囲を確認していきます。
| A列 | B列 | C列 | |
|---|---|---|---|
| 1 | 日付 | 売上個数 | 標本分散 |
| 2 | 月曜 | 80 | =VAR.P(B2:B6) |
| 3 | 火曜 | 95 | |
| 4 | 水曜 | 88 | |
| 5 | 木曜 | 102 | |
| 6 | 金曜 | 85 |
連続したセル範囲の指定
最も基本的な指定は、B2:B6のような連続範囲です。
この書き方では、先頭セルB2から末尾セルB6までをまとめて計算対象にできます。
数式を入力する前にデータ件数を確認し、末尾の行まで正しく選択しましょう。
データが追加される表なら、Excelのテーブル機能を使う方法も便利です。
テーブル名を使った数式では、新しい行が増えたときに対象範囲を追従させやすくなります。
固定のセル範囲で月ごとのデータを扱う場合は、翌月に古い範囲のまま残っていないかを確認してください。
離れたセルと複数範囲の指定
対象が連続していない場合は、VAR.P(B2:B6,D2:D6)のように複数の範囲をカンマで区切って指定できます。
ただし、別の品目や別条件のデータを安易に合算すると、分散の意味が薄れることがあります。
たとえば午前と午後、製品Aと製品Bのように条件が異なる数値は、まず列を分けて個別にばらつきを確認するほうが分析しやすいでしょう。
統合する理由がある場合は、同じ単位、同じ測定条件、同じ期間のデータかを先に確かめます。
分散は平均値の周辺での散らばりを見る指標なので、異質な集団を混ぜると大きな値になりやすい特徴があります。
空白セルと文字列の扱い
参照範囲に空白セルがあっても、VAR.P関数は空白を数値として数えずに計算します。
文字列や見出しも通常は計算から除外されますが、数値が文字列として入力されている場合は想定外の結果になる可能性があります。
セル左上の緑色の三角や、数式バーで文字列扱いになっていないかを確認しましょう。
エラー値が範囲内にあると、VAR.P関数もエラーになることがあります。
欠損データを空白にするのか、0として扱うのかは、集計ルールを決めてから入力する必要があります。
空白と0は統計上まったく異なる意味を持つため、都合よく置き換えないことが重要です。
【操作のポイント】集計前に数値列をフィルターで確認し、文字列化した数値、エラー値、不要な空白行がないかを点検します。
標本分散の数式と実務判断
続いては、計算結果を実務でどう読み取り、どのように使うかを確認していきます。
| A列 | B列 | C列 | |
|---|---|---|---|
| 1 | 月 | 問い合わせ件数 | 分散 |
| 2 | 4月 | 31 | =VAR.P(B2:B6) |
| 3 | 5月 | 28 | |
| 4 | 6月 | 46 | |
| 5 | 7月 | 35 | |
| 6 | 8月 | 30 |
数式コピーと相対参照
複数の部署や商品ごとに標本分散を出すときは、最初のセルに数式を入力してからオートフィルでコピーできます。
たとえばC2に=VAR.P(B2:B6)を入力し、別の列に同じ行構成のデータがある場合は、フィルハンドルを右へドラッグします。
相対参照では、コピー先に応じてB列がC列、D列へ自動的にずれます。
行番号を固定したい場合は、B$2:B$6のようにドル記号を使う方法もあります。
数式のコピー後は、参照範囲が意図した列に移っているか、数式バーで必ず確認しましょう。
分散と標準偏差の使い分け
分散は計算上の基礎となる指標ですが、単位が二乗になるため、現場では大きさを直感的につかみにくい場合があります。
元データと同じ単位でばらつきを示したいときは、標準偏差を確認します。
標本分散を使ったなら、標準偏差にはSTDEV.P関数を使います。
=STDEV.P(B2:B6)
この値はVAR.Pの計算結果を平方根にしたものです。
分散は統計分析や比較の土台、標準偏差は説明や報告で理解しやすい指標として併用されることが多いです。
どちらか片方だけが正しいのではなく、報告の目的によって見せ方を選びます。
外れ値を含むデータの確認
1件だけ極端に大きい値や小さい値があると、分散は大きく変化します。
これは分散の計算で差を二乗するため、平均から遠い値の影響が強まるからです。
大きな分散が出た場合に、すぐ入力ミスと決めつける必要はありません。
一方で、桁の誤り、単位の混在、重複入力がないかを確認するきっかけにはなります。
分散の数値だけで原因を断定せず、元データを並べ替えたりグラフ化したりして背景を確認する姿勢が有効です。
【操作のポイント】数式をコピーした後は、VAR.PとSTDEV.Pの基準を混在させず、対象範囲とデータの単位をそろえて比較します。
集計表での標本分散の活用
続いては、標本分散を日常的な集計表に活用する考え方を確認していきます。
| 比較対象 | 平均値 | 標本分散 |
|---|---|---|
| 店舗A | 120 | 36 |
| 店舗B | 121 | 144 |
平均値と組み合わせる見方
平均値が近い2つの店舗でも、標本分散が異なれば日ごとの安定度は変わります。
表の店舗Aと店舗Bは平均値がほぼ同じですが、店舗Bは分散が大きく、日別の変動が大きいと読めます。
平均値だけでは見えない変動の大きさを補える点が、標本分散を確認する利点です。
売上、作業時間、測定寸法、問い合わせ件数など、継続して記録する数値で役立ちます。
比較時の単位と期間
分散を比較する前に、データの単位と対象期間をそろえる必要があります。
日別売上と月別売上、円と千円、個数と金額を同じ表で単純比較しても、意味のある判断にはつながりません。
また、データ数が大きく違う集団では、分散の背景を平均値や標準偏差と合わせて読むことが重要です。
必要に応じて、平均値に対するばらつきを見る変動係数も検討できます。
ただし、平均値が0に近いデータでは変動係数が不安定になりやすいため注意しましょう。
レポートへの記載方法
レポートでは、数式だけでなく、対象期間とデータ件数を併記すると再現性が高まります。
たとえば、4月から8月までの月次件数5件を母集団として扱い、VAR.Pで算出した標本分散と説明できます。
推定目的なら、抽出した5件に対してVAR.Sを使用した不偏分散であることを記載します。
関数名、参照範囲、対象データの位置付けを残すことが、後から数値を見直す際の助けになります。
【操作のポイント】比較表には平均値と分散を並べ、期間、単位、件数が同じ条件であるかを注記します。
まとめ 不偏分散との違いとExcelの標本分散
Excelで標本分散を求める場合は、データ全体のばらつきを確認するVAR.P関数を使います。
基本式は=VAR.P(B2:B6)のように、1行目の見出しを除いた数値セルの範囲を指定する形です。
標本分散はデータ数nで割り、不偏分散はnから1を引いた数で割るため、同じデータでは不偏分散のほうが大きくなります。
手元の全記録を要約するならVAR.P、抽出データから母集団を推定するならVAR.Sという基準で考えると、選択しやすくなります。
分散の数値は平均値や標準偏差と組み合わせ、外れ値や入力ミスも含めて元データを確認しましょう。
用途に合う関数と参照範囲を選び、ばらつきを正しくExcelの集計へ活かしていきましょう。
