【Excel】エクセルで分散を出す方法（VAR関数・データのばらつき・偏差）
エクセルで売上、点数、作業時間などのデータを集計すると、平均値だけでは見えにくいデータの広がりを確認したくなることがあります。
このようなときに役立つ指標が分散です。
分散を使うと、各数値が平均からどれほど離れているのかを二乗して平均化できるため、数値のばらつきを一つの値として比較できます。
エクセルではVAR.S関数やVAR.P関数を使えば、複雑な計算を手入力せずに分散を求められます。
この記事では、VAR関数の考え方、標本分散と母分散の違い、偏差を使った計算方法、データ分析での活用場面をわかりやすく解説します。
分散を求める際のポイント
・抽出した一部のデータならVAR.S関数を使います。
・対象となる全データならVAR.P関数を使います。
・分散は値が大きいほど、平均値からのばらつきが大きいことを示します。
なお、旧バージョンのエクセルにはVAR関数とVARP関数がありますが、現在は用途が明確なVAR.S関数とVAR.P関数を選ぶ方法が基本です。
それでは、分散を正しく出すための手順と意味を確認していきましょう。
エクセルで分散を求めるVAR.S関数の入力方法
それではまず、エクセルで標本分散を求めるVAR.S関数について解説していきます。
| A列 | B列 |
|---|---|
| 1 | 点数 |
| 2 | 72 |
| 3 | 80 |
| 4 | 65 |
| 5 | 83 |
| 6 | 75 |
たとえば、B2からB6に一部の受験者から抽出した点数が入力されている場合、その点数のばらつきはVAR.S関数で計算できます。
VAR.S関数の基本構文
VAR.S関数は、標本として集めた数値データの分散を返す関数です。
VAR.S関数の書式
=VAR.S(数値1,数値2,…)
セル範囲を指定する場合
=VAR.S(B2:B6)
数値1には、分散を求めたい最初の数値、またはセル範囲を指定します。
複数の離れたセルを指定することもできますが、連続した表であればB2:B6のように範囲で指定する方法が入力ミスを減らせます。
文字列、空白セル、論理値が含まれていても、セル参照で指定した場合は通常計算の対象から除外されます。
このため、元データに見出しや未入力欄があっても、計算範囲を正しく選択していれば数値だけをもとに分散を算出できます。
ただし、エラー値が範囲内に含まれていると結果もエラーになるため、事前に確認しましょう。
セルに数式を入力する手順
分散を表示したい空白セルを選択し、数式バーまたはセルに=VAR.S(B2:B6)と入力します。
入力後にEnterキーを押すと、選択したセルに計算結果が表示されます。
数式を直接入力するほか、数式タブの関数の挿入から統計カテゴリを選び、VAR.S関数を探して指定することも可能です。
関数の引数ダイアログを使うと、範囲をマウスで選べるため、数式の入力に慣れていない場合にも便利です。
1行目がヘッダーなら、見出しを含めずB2から最終データ行までを選択することが大切です。
見出しセルを範囲に含めても文字列は無視されますが、集計範囲を明確にする習慣を付けると、ほかの関数にも応用しやすくなります。
表示された分散値の読み取り方
分散の結果は、元のデータと同じ単位ではなく、単位を二乗した大きさとして表示されます。
たとえば点数の分散は点数の二乗という扱いになるため、分散そのものを直感的に読むよりも、別のグループの分散と比較する使い方が向いています。
同じ平均点に近い二つのクラスを比べたとき、分散が小さいクラスは点数が平均付近に集まり、分散が大きいクラスは高得点者と低得点者が混在している傾向です。
分散が0になるのは、対象となるすべての数値が同じ場合です。
分散が正の値になるほど、各データと平均値の距離が大きくなります。
分散に小数点以下の桁が多く表示された場合は、ホームタブの小数点以下の表示桁数を減らして見やすくしても、元の計算精度は維持されます。
【操作のポイント】
抽出データのばらつきを知りたいときは、まず結果セルを一つ決めてからVAR.S関数にデータ範囲を指定します。
VAR.P関数とVAR.S関数の使い分け
続いては、VAR.P関数とVAR.S関数の違いを確認していきます。
| A列 | B列 | C列 | |
|---|---|---|---|
| 1 | 月 | 売上 | 集計区分 |
| 2 | 4月 | 120 | 全12か月の一部 |
| 3 | 5月 | 135 | 全12か月の一部 |
| 4 | 6月 | 110 | 全12か月の一部 |
関数名がよく似ているため迷いやすいところですが、違いは対象データを全体と考えるか、一部の標本と考えるかにあります。
母集団と標本の考え方
母集団とは、調査したい対象すべてを指します。
たとえば全従業員の残業時間をすでにすべて集めているなら、そのデータは母集団です。
一方で、全国の顧客傾向を知るために一部の顧客へアンケートを実施した場合、回答者のデータは標本になります。
手元のデータが調査対象の全件か、それとも全体を推測するための一部かで関数を選びます。
実務では、売上実績や全社員の勤怠など、集計対象がすべてそろっている表ではVAR.P関数を使う場面があります。
反対に、検査の抜き取り結果、アンケートのサンプル、将来予測のために集めた一部データにはVAR.S関数が適しています。
VAR.P関数を使う場面
VAR.P関数は、指定範囲の数値を母集団として扱い、母分散を求めます。
VAR.P関数の書式
=VAR.P(B2:B13)
旧関数を使う場合
=VARP(B2:B13)
たとえば1年間の12か月分の売上をすべて記録したB2からB13の範囲を分析するときは、=VAR.P(B2:B13)と入力します。
この場合は、1年間という対象全体の売上変動をそのまま表すため、母分散が自然な選択です。
旧関数のVARP関数も利用できますが、新しく作成するブックではVAR.P関数を使用すると意図が伝わりやすくなります。
【操作のポイント】
集計する期間や人数が対象全体であることを確認できる場合に、VAR.P関数を選択します。
標本分散で分母が一つ小さくなる理由
VAR.P関数は、偏差の二乗の合計をデータ数で割ります。
これに対してVAR.S関数は、偏差の二乗の合計をデータ数から1を引いた値で割ります。
母分散の考え方
偏差の二乗の合計 ÷ データ数
標本分散の考え方
偏差の二乗の合計 ÷ データ数から1を引いた値
標本だけで全体のばらつきを推定すると、標本内の平均値を使った計算ではばらつきが少し小さく見積もられやすくなります。
そこで分母を一つ小さくして補正する仕組みが採用されています。
同じデータをVAR.S関数とVAR.P関数で計算すると、通常はVAR.S関数の結果のほうが大きくなります。
データが少ないほど差は目立ちますが、どちらが正しいかではなく、目的に合う定義を使うことが重要です。
【操作のポイント】
抜き取り調査ならVAR.S関数、対象全件の記録ならVAR.P関数という基準で判断しましょう。
偏差と手計算で確認する分散の仕組み
続いては、偏差を使って分散がどのように計算されるかを確認していきます。
| A列 | B列 | C列 | D列 | |
|---|---|---|---|---|
| 1 | 点数 | 平均との差 | 偏差の二乗 | 平均 |
| 2 | 72 | =A2-$D$2 | =C2^2 | =AVERAGE(A2:A6) |
| 3 | 80 | =A3-$D$2 | =C3^2 | |
| 4 | 65 | =A4-$D$2 | =C4^2 |
関数だけで答えを出せるものの、計算の中身を知ると、分散の値をより適切に読み取れるようになります。
平均値と偏差の計算
偏差とは、各データから平均値を引いた値です。
たとえば平均点が75点で、ある人の点数が72点なら、偏差は72から75を引いたマイナス3です。
平均値をD2セルに出す数式
=AVERAGE(A2:A6)
B2セルで偏差を出す数式
=A2-$D$2
D2セルを固定するために、列記号と行番号の前にドル記号を付けます。
この絶対参照を使うと、B2セルの数式を下へコピーしたときも、平均値の参照先が常にD2セルのままになります。
偏差には正の値と負の値が混在しますが、平均との差そのものを把握する重要な列です。
偏差を二乗する理由
偏差をそのまま合計すると、平均より大きい値の正の偏差と、小さい値の負の偏差が打ち消し合います。
そのため、平均との差の大きさを集計するには、各偏差を二乗してすべて正の値に変換します。
C2セルで偏差の二乗を出す数式
=B2^2
または
=POWER(B2,2)
偏差がマイナス3なら二乗した値は9になり、偏差がプラス5なら二乗した値は25になります。
平均との差が大きいデータほど、二乗後の値は急激に大きくなります。
この性質により、平均から極端に離れた値があると、分散も大きく反応します。
外れ値が混ざっている可能性を確認する際にも、分散は役立つ指標です。
オートフィルによる数式のコピー
偏差と偏差の二乗を各行に計算する場合、先頭行に数式を入力してからオートフィルで下方向へコピーします。
先頭セルの右下に表示される小さな四角形を、最終データ行までドラッグします。
すると、B3以降ではA3-$D$2、A4-$D$2のように行番号だけが自動調整されます。
二乗列も同様に先頭セルからコピーできるため、データ件数が多い表でも手作業で数式を書き換える必要はありません。
最後に二乗列の合計を求め、母分散ならデータ数で割り、標本分散ならデータ数から1を引いた値で割ると、関数と同じ考え方を確認できます。
【操作のポイント】
平均値のセルは絶対参照にし、先頭の数式をオートフィルでコピーすると偏差の表を効率よく作成できます。
分散と標準偏差を比較する分析方法
続いては、分散と標準偏差を比較してデータを分析する方法を確認していきます。
| A列 | B列 | C列 | |
|---|---|---|---|
| 1 | チーム | 平均売上 | 分散 |
| 2 | A | 100 | 25 |
| 3 | B | 100 | 100 |
平均値が同じであっても、分散が異なればデータの安定性や個別の差は大きく異なります。
標準偏差を求める関数
標準偏差は分散の平方根であり、元データと同じ単位でばらつきを把握できる指標です。
標本標準偏差を求める数式
=STDEV.S(B2:B6)
母標準偏差を求める数式
=STDEV.P(B2:B6)
分散が100なら、その平方根である標準偏差は10です。
点数の標準偏差が10点なら、平均からおおむね10点程度離れたデータがあるとイメージしやすくなります。
分散を直接比較する分析と、実務で説明しやすい標準偏差を併用すると、データの特徴を伝えやすくなります。
平均値だけでは見えない安定性
二つの店舗の平均売上が同じでも、日ごとの売上の分散が小さい店舗は比較的安定しています。
一方、分散が大きい店舗では、特定の日だけ売上が突出したり、売上が大幅に落ち込んだりしているかもしれません。
分散は平均の高さではなく、平均の周囲にどの程度散らばっているかを示す値です。
売上の予算管理、品質検査、納期の所要時間、テストの得点分布など、安定性を把握したい多くの業務で活用できます。
ただし、平均値が大きく違うグループ同士では、分散だけで単純比較しにくい場合があります。
外れ値を確認する際の注意点
分散は偏差を二乗するため、平均から大きく離れた外れ値の影響を強く受けます。
たとえば入力ミスで売上を一桁多く登録していた場合、その一件だけで分散が大きく上昇する可能性があります。
分散が急に大きくなったときは、計算式だけでなく元データの誤入力や特殊な事象も確認しましょう。
フィルターで大きい値と小さい値を確認したり、並べ替えを行ったりすると、極端な数値を見つけやすくなります。
正当な外れ値なら削除せず、なぜ発生したのかを分析対象として扱う判断も必要です。
【操作のポイント】
分散が大きい結果を見たら、平均値と標準偏差を並べ、外れ値の有無も表から確認します。
分散計算で起こりやすいエラーと対処
続いては、分散計算で起こりやすいエラーと対処を確認していきます。
| A列 | B列 | C列 | |
|---|---|---|---|
| 1 | 担当者 | 処理時間 | 確認 |
| 2 | 田中 | 15 | 数値 |
| 3 | 鈴木 | 未入力 | 文字列 |
| 4 | 佐藤 | 18 | 数値 |
計算結果がエラーになる場合は、関数の選択だけでなく、データ件数やセル内容を確認することが解決への近道です。
VAR.S関数で表示されるエラー
VAR.S関数は標本分散を求めるため、計算対象となる数値が少なくとも二つ必要です。
数値が一つだけ、または数値がまったくない範囲を指定すると、データ数から1を引いた分母が0以下になるためエラーが表示されます。
VAR.S関数でエラーが出たら、指定範囲に数値が二つ以上あるかを最初に確認します。
母分散を求めるVAR.P関数は数値が一つでも計算できますが、その場合の分散は0です。
ただし一件だけのデータからばらつきを判断することは難しいため、十分なデータ数を確保することが望まれます。
文字列と数値の混在
セルに見える数字でも、文字列として保存されている場合があります。
セルの左上に緑色の三角形が表示される、左寄せになっている、SUM関数で合計できないといった状態なら、数値として認識されていない可能性があります。
エラー表示のボタンから数値に変換するか、データタブの区切り位置機能を使うと修正できる場合があります。
セル参照で指定した文字列はVAR.S関数で無視されることがありますが、意図せずデータ件数が減る原因になります。
見た目ではなく、エクセルが数値として認識しているかを確認する姿勢が重要です。
範囲指定と空白セルの確認
数式をコピーした際に、集計範囲の終端がずれているケースもあります。
たとえば新しいデータをB7に追加しても、数式が=VAR.S(B2:B6)のままなら、B7は計算に含まれません。
データを追加する機会が多い表は、範囲をテーブルとして書式設定しておくと、集計範囲が自動拡張されやすくなります。
テーブル化したデータでは、構造化参照を使って分散を求めることもできます。
=VAR.S(テーブル1[点数])
空白セルそのものは通常無視されますが、未入力と0を区別したい場合は、データ入力ルールを事前に決めておくと分析の精度が上がります。
【操作のポイント】
エラー時は数値の件数、文字列扱いのセル、集計範囲の三点を順に確認しましょう。
分散を使ったエクセルデータ分析の活用場面
続いては、分散を使ったエクセルデータ分析の活用場面を確認していきます。
| A列 | B列 | C列 | |
|---|---|---|---|
| 1 | 商品 | 週別販売数 | 分散 |
| 2 | 商品A | 12、13、11、14 | 少ない |
| 3 | 商品B | 5、22、8、19 | 大きい |
分散は統計の授業だけで使うものではなく、日常的な業務データの特徴をつかむためにも利用できます。
売上と需要の変動確認
商品別や月別の販売数に分散を求めると、需要が安定している商品と変動が大きい商品を分けて考えられます。
平均販売数が同程度でも、分散が大きい商品は在庫切れや過剰在庫が起きやすい可能性があります。
平均だけで発注量を決めず、販売数の分散も確認すると在庫管理の判断材料が増えます。
季節要因やキャンペーンの影響など、変動の理由を別の列に記録しておけば、分散が大きくなった背景も追いやすくなります。
品質管理と測定値の比較
製造や検査の現場では、製品サイズ、重量、作業時間などの測定値の分散を確認できます。
平均値が規格内であっても、分散が大きい場合は一部の製品が規格外に近づいているおそれがあります。
品質の安定性を見るには、平均値と分散をセットで確認することが有効です。
工程や担当者ごとに分散を比較すると、ばらつきが大きくなる条件を見つける手掛かりになるでしょう。
複数グループのばらつき比較
部署、店舗、クラス、広告媒体など複数のグループがある場合は、それぞれの分散を並べて比較できます。
平均値と分散を別列に配置し、条件付き書式で分散の大きいセルを目立たせると、確認すべきグループを素早く探せます。
ただし、データ数が極端に異なるグループを比べる場合は注意が必要です。
少数データでは偶然の影響を受けやすいため、件数列も一緒に表示すると判断しやすくなります。
【操作のポイント】
分散の数値だけで結論を急がず、平均値、件数、元データの内容を並べて比較しましょう。
まとめ エクセルで偏差と分散を出すVAR関数の方法
エクセルでデータのばらつきを調べたい場合は、標本データにはVAR.S関数、対象全体のデータにはVAR.P関数を使います。
VAR.S関数は=VAR.S(B2:B6)のように入力でき、1行目にヘッダーがある表では実際の数値が始まる2行目以降を指定する方法が基本です。
分散は平均からの偏差を二乗して集計した値であり、大きいほどデータの散らばりも大きいと判断できます。
偏差を確認したいときは、AVERAGE関数で平均値を求め、元データから平均値を引く数式を作成します。
平均との差を二乗することで正負の値が打ち消されず、ばらつきの大きさを計算できる仕組みです。
また、分散は外れ値の影響を受けやすいため、結果が大きいときは入力ミス、特殊な事象、集計範囲のずれがないかも確認しましょう。
平均値だけでは把握できない安定性や個々の差を知るために、標準偏差や件数と合わせて分散を活用してみてください。
