【Excel】エクセルで分散を求める方法（データの散らばり）

Excelで複数の数値を扱っていると、平均値だけでは見えないデータの特徴を知りたくなる場面があります。

たとえば平均点が同じ二つのクラスでも、全員の点数が平均付近に集まるクラスと、高得点者と低得点者が混在するクラスでは、結果の受け取り方が変わります。

こうした数値の散らばり具合を数値化する代表的な指標が分散です。

Excelには分散を計算する関数が用意されているため、数式を正しく選べば大量の売上、成績、測定値、作業時間なども効率よく分析できます。

この記事では、Excelで分散を求める数式、VAR.S関数とVAR.P関数の違い、手入力による計算方法、標準偏差との関係を順番に解説します。

 

Excelで分散を求める基本関数

それではまず、Excelでデータの分散を求める基本的な方法について解説していきます。

氏名	テスト得点
田中	72
佐藤	81
鈴木	65
高橋	77
伊藤	85

上のようにA列に氏名、B列に得点を入力し、1行目を見出しとして使う場合を考えます。

分散を表示したいセルに関数を入力すれば、B2からB6までの数値をまとめて集計できます。

 

VAR.S関数による標本分散

調査対象が全体の一部である場合は、VAR.S関数を使うのが一般的です。

たとえばアンケート回答者の一部、抽出した製品の検査結果、特定期間だけの売上などは、より大きな集団の傾向を推定する標本として扱われることがあります。

この数式を入力すると、B2からB6にある五人分の得点を基に標本分散が計算されます。

VAR.SのSはSampleを意味し、抽出したデータからばらつきを推定する用途に向く関数です。

旧バージョンのExcelではVAR関数も使われていましたが、現在は用途が明確なVAR.S関数を使うと、数式を見た人にも意味が伝わりやすくなります。

セル範囲には見出しであるB1を含めず、実際の数値が始まるB2から指定することが大切です。

文字列や空白セルは通常は計算対象から除外されますが、入力漏れが多い表では件数そのものが意図どおりか確認しましょう。

 

VAR.P関数による母分散

対象となるデータをすべて集めており、その集合全体の散らばりを知りたい場合はVAR.P関数を使用します。

たとえば一つの部署に所属する全社員の残業時間、今月に出荷した全製品の重量、クラス全員の得点を集計する場合が該当します。

VAR.PのPはPopulationを意味し、母集団全体の分散を返します。

全件データなのか、一部を抽出したデータなのかによってVAR.PとVAR.Sを使い分けることが、正しい分析の第一歩です。

両関数は同じ数値範囲を指定しても、通常は異なる結果になります。

VAR.Sでは標本から母集団を推定する補正が入るため、データ数が二件以上であればVAR.Pより少し大きい値になる傾向があります。

どちらが正しいという話ではなく、集計の目的に合う関数を選ぶことが重要です。

 

関数の入力手順

分散を表示したい空白セルを選択し、数式バーまたはセル内にVAR.S関数かVAR.P関数を入力します。

たとえばD2セルに標本分散を出すなら、=VAR.S(B2:B6)と入力してEnterキーを押します。

関数の引数にセル範囲を指定するときは、マウスでB2からB6までをドラッグしても構いません。

離れた範囲の数値をまとめる場合は、=VAR.S(B2:B6,D2:D6)のように、複数の範囲をカンマで区切って指定できます。

関数を入力したセル自身を集計範囲に含めると循環参照になるため、結果を置く位置は元データの外側にすると安全です。

計算結果の小数点以下が長く表示される場合は、ホームタブの小数点表示桁数を減らす操作で見た目を整えられます。

 

分散の意味と計算の仕組み

続いては、分散がどのような考え方で作られている数値なのかを確認していきます。

データ番号	値	平均との差
1	10	-2
2	12	0
3	14	2

この例では平均値が12であり、それぞれのデータが平均からどれだけ離れているかを確認できます。

 

平均からの差を二乗する理由

分散は、各データと平均値との差を求め、その差を二乗して平均する考え方で計算されます。

平均との差をそのまま合計すると、平均より大きい値と小さい値が打ち消し合い、合計がゼロになってしまいます。

そこで差を二乗し、すべてを正の値に変換してから集計します。

平均との差が大きいほど二乗した値も大きくなるため、分散が大きいほどデータの散らばりは大きいと判断できます。

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

得点の分散なら点の二乗、長さの分散なら長さの二乗という扱いです。

 

標本分散でデータ数から一を引く理由

VAR.S関数で計算される標本分散では、二乗差の合計をデータ数ではなく、データ数から一を引いた数で割ります。

五件のデータなら五ではなく四で割る仕組みです。

これは標本だけから母集団の散らばりを推定する際に、値が小さく見積もられやすい傾向を補うためです。

VAR.Sはデータ数が一件しかない場合には計算できず、エラーになる点にも注意が必要です。

一件では散らばりを比較できないため、これはExcelの不具合ではありません。

一方、VAR.Pは母集団全体を対象とするため、分母にデータ数を使います。

 

分散がゼロになるケース

すべての数値が同じ場合、平均からの差はすべてゼロです。

たとえば五人全員の得点が80点なら、VAR.P関数の結果もVAR.S関数の結果もゼロになります。

分散がゼロであることは、データにばらつきがない状態を意味します。

反対に、平均値が同じでも各数値の幅が大きいほど分散は大きくなります。

平均だけを比較すると似た集団に見えても、分散を見ることで安定性や個人差を読み取れることがあります。

製造現場では寸法の安定性、営業では月ごとの売上変動、教育では理解度の個人差を確認する材料になります。

 

手計算の式をExcelで再現する方法

続いては、分散の計算過程を確認したいときに役立つ、Excelでの手計算式の再現方法を確認していきます。

氏名	売上	平均との差	差の二乗
田中	120
佐藤	150
鈴木	90
高橋	140

上の表のように途中計算用の列を作ると、関数だけでは見えにくい計算内容を確認できます。

 

平均値を別セルに求める設定

まず、売上データがB2からB5に入力されていると仮定し、F2セルに平均値を表示します。

AVERAGE関数を使うと、範囲内にある数値の平均を自動で求められます。

平均値を別セルに置く方法には、計算内容を見やすくし、後からデータ範囲を確認しやすい利点があります。

F2セルの平均値を後続の式で固定参照するため、列記号と行番号の前にドル記号を付ける絶対参照を使います。

平均値のセルを固定しないまま数式を下へコピーすると、参照先がずれて計算結果が変わるため注意しましょう。

 

平均との差と二乗差の数式

C2セルには、各行の売上から平均値を引く数式を入力します。

次にD2セルには、平均との差を二乗する数式を入力します。

二乗は、数値を二回掛ける演算です。

=C2*C2と書いても同じ結果になりますが、^2と書くと二乗していることが読み取りやすくなります。

D2の数式を入力したら、セル右下のフィルハンドルを下へドラッグし、D5までオートフィルでコピーします。

これで各売上の平均からの距離を二乗した値が一覧で表示されます。

 

二乗差の合計から分散を求める計算

母分散を求める場合は、二乗差の合計をデータ数で割ります。

SUM関数は二乗差の合計を求め、COUNT関数はB列の数値が入力されたセル数を数えます。

標本分散を同じ手順で求めたい場合は、分母をCOUNT(B2:B5)-1に変更します。

この結果は、同じ範囲に対してVAR.P関数またはVAR.S関数を使った結果と一致します。

途中計算を作る方法は、関数の仕組みを学ぶときや、異常に大きい値が発生した原因を調べるときに特に便利です。

ただし通常の集計では、VAR.S関数やVAR.P関数を使うほうが短い数式で済み、入力ミスも減らせます。

 

分散関数を使った実務データの集計

続いては、実務で扱いやすい形に整えたデータから分散を算出する方法を確認していきます。

月	商品A売上	商品B売上	商品C売上
4月	310	280	350
5月	340	300	320
6月	290	330	370
7月	360	290	340

ここでは各商品の月別売上を比較し、どの商品が安定しているかを分散で確かめます。

 

列ごとの分散を横並びに計算する方法

各商品について母分散を求める場合、B7セルに=VAR.P(B2:B5)と入力します。

次にC7セルには=VAR.P(C2:C5)、D7セルには=VAR.P(D2:D5)を入力します。

数式を右方向へオートフィルする場合は、B7に=VAR.P(B2:B5)を入力し、右下のフィルハンドルをD7まで引っ張る方法もあります。

このとき列参照は相対参照なので、コピー先に応じてB列、C列、D列へ自動的に変化します。

分散が小さい商品は月ごとの売上変動が比較的少なく、分散が大きい商品は変動が大きいと読めます。

ただし売上規模が大きい商品ほど分散も大きくなりやすいため、単純な値だけで安定性を比較する際には注意が必要です。

 

テーブル機能を使う場合の参照範囲

データ量が増える表では、範囲をテーブルとして設定しておくと、行を追加したときに参照範囲を広げる手間を減らせます。

表内のセルを選び、挿入タブからテーブルを作成すると、列名を使った構造化参照が利用できます。

たとえば売上という列名のテーブルなら、=VAR.S(テーブル名[売上])のような式を作れます。

列名を使う式は、B2:B1000のようなセル番地よりも対象が分かりやすいことがあります。

新しい行をテーブル末尾に入力すると、集計対象へ自動的に含まれる点も便利です。

定期的に更新する月次レポートや検査記録では、入力範囲の漏れを防ぐ工夫として活用できます。

 

空白値とエラー値を含むデータの注意点

VAR.S関数とVAR.P関数は、セル範囲内の空白セルや文字列を基本的に無視して計算します。

しかし、#DIV/0!や#N/Aなどのエラー値が範囲に含まれていると、分散関数の結果もエラーになります。

データ収集の途中でエラーが出る表では、先にIFERROR関数で表示を整えるか、集計対象の範囲を分ける必要があります。

空白をゼロとして扱いたいのか、未入力として除外したいのかは、分析前に決めておくべき重要な条件です。

売上がゼロだった月と、データをまだ入力していない月では意味が異なります。

数値の見た目だけで処理せず、業務上の意味に沿って表を整えましょう。

 

標準偏差と分散の使い分け

続いては、分散と一緒に使われることが多い標準偏差との違いを確認していきます。

指標	Excel関数	特徴
標本分散	VAR.S	差を二乗した散らばり
母分散	VAR.P	全体データの散らばり
標本標準偏差	STDEV.S	元データと同じ単位
母標準偏差	STDEV.P	全体データの標準偏差

分散と標準偏差はいずれもデータのばらつきを示しますが、数値の読み取りやすさに違いがあります。

 

標準偏差が元の単位に戻る仕組み

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

分散は差を二乗しているため単位も二乗になりますが、平方根を取る標準偏差は元データと同じ単位に戻ります。

得点データなら標準偏差も点で表示されるため、実務では分散より直感的に説明しやすい場合があります。

平均からおおよそどの程度離れているかを元の単位で伝えたいときは、標準偏差が役立ちます。

ただし分散と標準偏差は優劣で選ぶものではなく、分析目的と報告相手に応じた使い分けが必要です。

 

変動係数による規模の異なる比較

売上が百万円単位の商品と、数千円単位の商品を分散だけで比較すると、規模が大きい商品の値が大きくなりやすくなります。

平均に対するばらつきの比率を比べたい場合は、標準偏差を平均値で割る変動係数を使う方法があります。

セルの表示形式をパーセンテージにすると、平均に対して何パーセント程度の変動があるかを読み取りやすくなります。

平均額が大きく異なる複数商品の安定性を比較するなら、変動係数も併せて見ると公平な判断につながります。

平均がゼロに近いデータでは変動係数が極端な値になるため、その場合は別の評価方法を検討しましょう。

 

グラフと併用する分析

分散の数値は、複数のグループを比較するときに効果を発揮します。

一方で、どの月に大きく変動したのか、特定の外れ値があるのかまでは、分散だけでは把握しにくいことがあります。

月別の推移を折れ線グラフで表示し、分散や標準偏差を補助指標として添えると、数字と変化の両方を確認できます。

箱ひげ図を使えるExcelの環境では、中央値、四分位数、外れ値を視覚的に確認することも可能です。

分散は単独の答えではなく、平均値、最大値、最小値、グラフと組み合わせて使う分析指標と考えると活用しやすくなります。

 

分散計算で起こりやすいエラーと対処

続いては、Excelで分散を求める際に起こりやすいエラーや、結果の違和感を見つける確認方法を解説していきます。

表示内容	主な原因	確認事項
#DIV/0!	VAR.Sで数値が一件以下	対象件数を確認する
#VALUE!	参照や数式の不整合	セル範囲を確認する
予想より大きい値	外れ値の混入	元データを並べ替える

エラーや予想外の値が表示されたときは、関数名だけではなく、指定範囲と元データを順番に確認することが大切です。

 

VAR.Sで表示される除算エラー

VAR.S関数は標本分散を計算するため、少なくとも二つ以上の数値が必要です。

データが一件しかない場合や、範囲内に数値が一つしかない場合は、分母がゼロになり#DIV/0!が表示されます。

この場合は入力済みの数値件数をCOUNT関数で確認してみましょう。

抽出条件を設定した後に件数が一件だけになっているケースもあります。

全データの分散が必要で一件だけのデータを扱うならVAR.P関数ではゼロを返しますが、散らばりを判断できる情報はない点を理解しておきましょう。

 

外れ値による分散の増加

分散は平均からの差を二乗するため、極端に大きい値や小さい値の影響を強く受けます。

たとえば通常は売上が百前後なのに、一件だけ千のデータがあると、その一件によって分散が大幅に増えることがあります。

分散が急に大きくなったときは、計算式の誤りと決めつけず、入力ミスや特別な外れ値を確認することが重要です。

表を昇順または降順に並べ替えると、極端な値を見つけやすくなります。

外れ値が入力ミスなら修正しますが、実際に発生した重要な出来事なら、安易に除外せず原因を記録して分析に反映させます。

 

参照範囲のずれを防ぐ確認

数式をコピーしたときに参照範囲がずれると、意図しない行や列を計算対象にしてしまうことがあります。

数式を選択するとExcelでは参照している範囲が色枠で表示されるため、元データの範囲と一致しているかを確認できます。

月ごとの表を追加した後も、分散関数が新しい行を含んでいるかを見直しましょう。

合計行や平均行を誤って範囲へ含めると、分散の結果は大きく変わります。

一行目の見出し、途中の小計、最終行の合計は、通常の分散計算の範囲から除外するのが基本です。

データの構造が複雑なら、テーブル機能や名前付き範囲を使い、参照範囲を管理しやすくする方法も有効です。

 

まとめ エクセルでデータの散らばりを求める分散の方法

Excelで分散を求めるには、標本データならVAR.S関数、対象全体のデータならVAR.P関数を使います。

=VAR.S(B2:B6)または=VAR.P(B2:B6)のように、1行目の見出しを除いた数値セルの範囲を指定するだけで計算できます。

分散は平均値だけでは分からないデータの散らばりを示す指標であり、成績、売上、品質検査、作業時間など幅広いデータ分析に役立ちます。

分散の仕組みは、各値と平均値との差を二乗し、その合計をデータ数またはデータ数から一を引いた値で割る考え方です。

元の単位でばらつきを伝えたい場合は、分散の平方根である標準偏差も併せて確認しましょう。

結果が大きすぎると感じたときは、外れ値、入力ミス、合計行の混入、参照範囲のずれを見直すことが有効です。

データが全体なのか標本なのかを最初に判断し、目的に合う関数を選んで、Excelでの分析に活かしていきましょう。