【Excel】エクセルで分散・不偏分散を求める方法（VAR.S・VAR.P・母分散との違い）

エクセルでデータのばらつきを調べるときは、分散や不偏分散を使います。

ただし、関数名が似ているため、VAR.SとVAR.Pのどちらを選べばよいのか迷いやすいところです。

分散は平均値から各データがどの程度離れているかを数値化したもので、売上、点数、作業時間、測定値などの分析に役立ちます。

この記事では、1行目に見出しがある表を例に、VAR.S、VAR.P、不偏分散、母分散の違いと入力方法を順に解説します。

 

エクセルで分散を求めるVAR.SとVAR.Pの入力方法

それではまず、エクセルで分散を計算する基本操作について解説していきます。

A	B	C
1	氏名	テスト得点
2	田中	72
3	佐藤	81
4	鈴木	65
5	高橋	90
6	伊藤	77

 

VAR.S関数による不偏分散の計算

VAR.S関数は、抽出した標本データから不偏分散を求める関数です。

たとえば、全校生徒の成績傾向を知るために一部の生徒だけを抽出した場合、B列の得点範囲に対してVAR.Sを使います。

結果を表示したいセルを選択し、上記の数式を入力してEnterキーを押します。

1行目は見出しなので、計算範囲にはC1を含めずC2からC6を指定します。

VAR.Sはデータ数をnとしたとき、平均との差の二乗和をn−1で割って計算します。

このn−1という分母の扱いが、不偏分散と呼ばれる理由です。

抽出した一部のデータだけでは、全体のばらつきを少し小さく見積もりやすくなります。

そこで分母を1つ小さくして補正し、母集団の分散を推定しやすくします。

 

VAR.P関数による母分散の計算

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

たとえば、5人だけで構成されたチームの全員分の得点を分析するなら、C2からC6は母集団そのものと考えられます。

VAR.Pでは平均との差の二乗和をデータ数nで割ります。

同じ範囲を指定した場合、VAR.Pの結果はVAR.Sよりも小さくなるのが通常です。

データが5件なら、VAR.Sは4で割り、VAR.Pは5で割るためです。

全社員の給与一覧、全商品の検査記録、全店舗の月次売上のように、対象範囲の全件がそろっているならVAR.Pが適しています。

一方で、今後増える可能性がある全体を推測したい場合は、全件に見えても標本として扱う考え方があります。

 

関数の入力範囲と結果の確認

分散の計算では、数式そのものだけでなくセル範囲の指定も重要です。

空白セルはVAR.SとVAR.Pで基本的に無視されますが、数値が文字列として入力されている場合は期待した結果にならないことがあります。

金額に円、得点に点などの文字を直接入力していると、数値ではなく文字列として扱われる可能性があります。

単位はセルの表示形式で付け、セルの値には数値だけを入れると、平均や標準偏差なども正しく計算できます。

また、範囲にエラー値が含まれると分散関数もエラーになります。

必要に応じてIFERROR関数やフィルターを利用し、分析対象のデータを整えてから計算しましょう。

 

分散と不偏分散と母分散の違い

続いては、分散、不偏分散、母分散の関係を確認していきます。

A	B	C
1	区分	使用関数
2	標本から全体を推定	VAR.S
3	対象の全件を集計	VAR.P

 

平均との差の二乗で表す分散

分散は、各データが平均値からどれだけ離れているかを示す指標です。

単純に差を足し合わせると、平均より大きい値と小さい値が打ち消し合ってしまいます。

そのため、平均との差を二乗し、それらを合計して平均します。

二乗を使うため、平均との差が大きいデータほど結果に強く影響します。

分散の単位は元データの単位を二乗したものになります。

得点なら点の二乗、売上なら円の二乗となるため、日常的な大きさとして把握したい場合は標準偏差も併せて見ると便利です。

分散が大きいほど、平均値の周辺にデータが集まらず、ばらつきが大きい状態と読み取れます。

 

標本分散を補正する不偏分散

不偏分散は、標本から母集団の分散を推定するために補正した分散です。

たとえば全国の利用者すべてを調べられない場合、無作為に選んだ一部の利用者の回答を分析します。

このとき標本内の平均値を基準にすると、真の全体平均値を基準にするより差が小さくなりやすい傾向があります。

そこで、二乗和をデータ数ではなくデータ数から1を引いた値で割ります。

データ数が少ないほど、VAR.SとVAR.Pの差は目立ちます。

データ数が多くなるにつれて差は小さくなりますが、統計的な意味は異なるため、単に近い値だから同じと判断しないことが大切です。

 

母集団と標本の考え方

母集団とは、分析したい対象全体を指します。

標本とは、その母集団から取り出した一部のデータです。

たとえば、今月の全受注データを分析するなら、その月の受注全件が母集団です。

一方で、来月以降も含めた長期的な受注傾向を予測したいなら、今月の全件であっても将来を含む大きな母集団に対する標本と考えられます。

関数選びはデータ件数ではなく、どこまでを分析対象全体と定義するかで決まります。

この考え方を先に整理しておけば、VAR.SとVAR.Pを機械的に使い分ける必要がありません。

 

VAR.SとVAR.Pを使い分ける実務場面

続いては、実際の業務で関数を選ぶ基準について確認していきます。

A	B	C
1	商品名	検査値
2	A商品	102
3	B商品	99
4	C商品	105

 

全件集計に適したVAR.P

VAR.Pが適しているのは、範囲内の値が分析対象のすべてであるケースです。

たとえば、5店舗すべての月間売上、所属する全メンバーの勤務時間、製造ロット全数の測定結果などが該当します。

この場合は、表に入力された値のばらつきそのものを確認したいので、母分散を計算します。

月次の実績比較では、その時点で確定した全データの散らばりを把握する目的が中心です。

したがって、未来の全体傾向を推測するよりも、現状の記録を正確に要約するVAR.Pが自然です。

なお、部門単位や地域単位で分析対象を限定した場合も、その限定した範囲の全件があればVAR.Pを使えます。

 

抽出調査に適したVAR.S

VAR.Sは、全体の特徴を推測したい抽出調査で役立ちます。

たとえば、出荷品すべてを検査できないため一部を抜き取る場合、抜き取った測定値は標本です。

顧客満足度のアンケートや、市場調査の回答データも、多くはより大きな対象集団の一部と考えられます。

このような場面では、標本のばらつきを過小評価しないための補正としてVAR.Sを使います。

ただし、標本の抽出方法に偏りがあると、関数を正しく選んでも全体推定の精度は上がりません。

対象が偏らないように抽出条件を決めることも、分散を活用する前提になります。

 

旧関数VARと新関数の違い

古いエクセルファイルでは、VAR関数やVARP関数が使われていることがあります。

VARは現在のVAR.Sに相当し、VARPは現在のVAR.Pに相当する互換性関数です。

既存ファイルの数式がすぐに誤りになるわけではありませんが、新しく作る表ではVAR.SとVAR.Pを使う方が意図を伝えやすくなります。

SはSampleの頭文字、PはPopulationの頭文字と覚えると、関数名から用途を思い出せます。

また、STDEV.SとSTDEV.Pはそれぞれ標本標準偏差と母標準偏差を求める対応関数です。

分散と標準偏差を並べて確認する資料では、SとPを混在させないよう注意しましょう。

 

数式バーを使った分散関数の操作手順

続いては、数式バーでVAR.Sを入力して結果を確認する操作について解説していきます。

A	B	C	D
1	氏名	テスト得点	不偏分散
2	田中	72	=VAR.S(C2:C6)
3	佐藤	81
4	鈴木	65
5	高橋	90
6	伊藤	77

 

関数を入力するセルの選択

まず、分散の計算結果を表示する空白セルを選択します。

元データがC2からC6にある場合、結果用としてD2などの空いているセルを選ぶと見やすくなります。

選択したセルに=VAR.S(と入力すると、エクセルが関数候補を表示します。

関数名を最後まで入力したら、括弧の中に計算対象のセル範囲を指定します。

数式を入力するセルは、元データの範囲と重ならない位置に置くことが基本です。

元データを上書きすると、あとから計算根拠を確認できなくなるため注意しましょう。

 

範囲指定とEnterキーによる確定

数式バーまたはセル上で=VAR.S(C2:C6)と入力し、Enterキーを押すと計算結果が表示されます。

マウスでC2からC6をドラッグして範囲を選択しても構いません。

数式内のC2:C6は、C2からC6までの連続したセル範囲を意味します。

離れたセルを計算したい場合は、=VAR.S(C2:C6,C8:C10)のように複数の範囲を指定できます。

ただし、別の条件のデータを一緒にすると分析目的が曖昧になることがあります。

同じ集計条件、同じ期間、同じ単位のデータを範囲に含めることが、意味のある分散を得るコツです。

 

オートフィルを使う複数列の計算

月別、商品別、担当者別など、横方向に複数の数値列がある表ではオートフィルが便利です。

たとえばC列からG列まで各月の売上があり、8行目に各列の分散を求める場合、C8に=VAR.S(C2:C7)と入力します。

その後、C8セル右下のフィルハンドルをD8からG8まで横にドラッグすると、列参照が自動的に変わります。

数式をコピーする前に、各列で行数がそろっているか確認しましょう。

オートフィル後は先頭セルだけでなく、最後のセルも選択して数式バーを見ると、参照ずれを早期に見つけられます。

 

分散の結果を読むための平均と標準偏差

続いては、分散の数値を実務で読み解くための平均と標準偏差について確認していきます。

A	B	C
1	項目	計算式
2	平均値	=AVERAGE(C2:C6)
3	不偏分散	=VAR.S(C2:C6)
4	標本標準偏差	=STDEV.S(C2:C6)

 

平均値と併せて確認する視点

分散だけでは、データの水準そのものは分かりません。

平均値が高くても分散が大きければ、結果の個人差や日ごとの変動が大きい可能性があります。

反対に、平均値が低くても分散が小さければ、値は安定していると評価できます。

平均値は中心、分散は散らばりを示す指標であり、役割が異なります。

たとえば平均売上だけで店舗を比較すると、売上の安定性までは把握できません。

平均と分散をセットで確認すれば、好調だが変動が大きい店舗と、安定している店舗の違いも見えてきます。

 

標準偏差への変換

標準偏差は、分散の平方根を取った値です。

元データと同じ単位で表されるため、分散より直感的に読みやすい場面があります。

標本データならSTDEV.S、母集団データならSTDEV.Pを使います。

すでに不偏分散をD2に計算しているなら、=SQRT(D2)でも標本標準偏差を求められます。

分散が81なら標準偏差は9という関係です。

売上の標準偏差が10万円であれば、平均的な売上からおおむね10万円程度の幅で動いているという見方ができます。

ただし、正確な分布の判断にはヒストグラムや外れ値の確認も必要です。

 

外れ値とデータ品質の確認

分散が予想より大きいときは、入力ミスや外れ値が混ざっていないか確認します。

たとえば売上欄に桁を1つ多く入力した値があると、平均との差を二乗する分散は大きく反応します。

これは単なる計算ミスではなく、データ品質を点検するきっかけにもなります。

大きな分散は必ずしも悪い結果ではなく、原因を調べるためのサインです。

新商品の販売開始、繁忙期、担当変更など、実際の業務変化がばらつきの理由になっているかもしれません。

数値を削除する前に、入力誤りなのか意味のある例外なのかを記録と照合しましょう。

 

まとめ エクセルで母分散と不偏分散を求める方法

エクセルで分散を求めるときは、対象データが母集団か標本かを最初に考えることが重要です。

全件のばらつきを集計する場合はVAR.Pを使い、全体を推定するための抽出データにはVAR.Sを使います。

VAR.Sは不偏分散、VAR.Pは母分散を計算する関数と覚えておくと、数式選びがスムーズです。

1行目が見出しの表では、=VAR.S(C2:C6)や=VAR.P(C2:C6)のように数値が始まる2行目以降を指定します。

分散の値は平均値と併せて確認し、必要に応じてSTDEV.SやSTDEV.Pで標準偏差も求めましょう。

また、分散が大きいときは外れ値や入力ミスだけでなく、実際の業務上の変化も確認する視点が大切です。

母集団と標本の意味を理解し、目的に合う関数を選んで、エクセルでのデータ分析に活用しましょう。