エクセルでデータのばらつきを数値化したいときに役立つ指標が分散です。
売上、テスト結果、作業時間、製品の寸法などを比較するとき、平均値だけではデータの安定性まで判断できません。
分散を求めれば、各数値が平均からどの程度離れているかを二乗した形で把握できます。
分散の基本式は、各値と平均値との差を二乗して合計し、データ数またはデータ数から1を引いた数で割る計算です。
母分散は全体を対象にした分散であり、標本分散は一部のデータから全体の傾向を推定するときの分散です。
この記事では、VAR.P関数、VAR.S関数、標準偏差との違い、グラフを使ったばらつきの見方まで順番に解説します。
サンプルデータは1行目に見出しがあり、2行目以降に数値が入力されているものとして説明します。
エクセルで分散を求める関数
それではまず、エクセルで分散を計算する基本的な関数について解説していきます。
| A列 | B列 | C列 |
|---|---|---|
| 1 | 月 | 売上 |
| 2 | 4月 | 120 |
| 3 | 5月 | 150 |
| 4 | 6月 | 110 |
VAR.P関数による母分散
母集団に含まれる全データを集計できている場合には、VAR.P関数を使用します。
たとえばB2からB13に1年分の売上が入力されているなら、空いているセルにVAR.P(B2:B13)と入力しましょう。
=VAR.P(B2:B13)
この数式はB2からB13までの12件を全体として扱い、母分散を返します。
VAR.PのPはPopulationの頭文字で、全体のデータを意味します。
会社全体の全店舗売上や、対象ロットの全製品を測定した結果など、抜けのないデータに向く関数です。
分散の値が大きいほど、売上や測定値の振れ幅が大きい状態と考えられます。
VAR.S関数による標本分散
続いては、抽出した一部のデータから傾向を確認する場合のVAR.S関数を見ていきます。
たとえば全国の顧客すべてではなく、無作為に選んだ100人だけの購買額を調べた場合は、標本分散が適しています。
=VAR.S(B2:B13)
VAR.Sでは、偏差平方和をデータ数から1を引いた値で割ります。
標本分散は、限られたデータから母集団のばらつきを推定するために補正された計算です。
少ない標本だけで計算すると、ばらつきが小さく見積もられやすいため、この補正が必要になります。
アンケート結果、品質検査の抜き取り、試験運用のログなどではVAR.Sを選ぶ場面が多いでしょう。
旧関数と入力時の注意点
以前のエクセルではVARP関数やVAR関数が使われていました。
VARPはVAR.P、VARはVAR.Sに相当しますが、現在は用途が明確な新しい関数を使うと管理しやすくなります。
数値以外の文字列や空白セルは基本的に計算対象から除外されます。
ただし、0は空白ではなく数値として扱われるため、売上ゼロや測定値ゼロを意図せず削除しないよう注意しましょう。
【操作のポイント】全件データならVAR.P、抽出データならVAR.Sを選び、見出し行を含めない範囲指定を行います。
分散の計算式と手計算
続いては、分散がどのような計算で求められるのかを確認していきます。
| 項目 | 数値 | 平均との差 |
|---|---|---|
| A | 10 | -2 |
| B | 12 | 0 |
| C | 14 | 2 |
平均値からの偏差
分散を理解する最初のポイントは、各データと平均値との差である偏差です。
10、12、14の平均値は12であり、それぞれの偏差は-2、0、2になります。
偏差をそのまま合計すると、負の値と正の値が打ち消し合って0になるため、ばらつきの大きさを表せません。
そこで、偏差を二乗してすべて正の値に変換します。
偏差 = 個々の数値 – 平均値
10の場合は10 – 12となり、偏差は-2です。
平均より低い値でも高い値でも、平均から遠いほど二乗後の値は大きくなります。
偏差平方和と母分散
偏差を二乗した値は偏差平方と呼ばれます。
10、12、14であれば、偏差平方は4、0、4となり、合計である偏差平方和は8です。
母分散 = 偏差平方和 ÷ データ数
この例では8 ÷ 3となるため、母分散は約2.67です。
エクセルではAVERAGE関数で平均を出し、各セルで偏差と二乗を計算してからAVERAGE関数を使っても、VAR.P関数と同じ結果を確認できます。
計算過程を表にすると、分散の意味を説明する資料としても使いやすくなります。
標本分散における自由度
標本分散では、偏差平方和をデータ数ではなくデータ数から1を引いた数で割ります。
3件のデータなら、8を3ではなく2で割るため、標本分散は4になります。
このデータ数から1を引いた数は自由度と呼ばれます。
標本の平均値を使って偏差を計算すると、最後の1つの偏差は他の偏差で決まるため、自由に動ける値が1つ減ります。
数式を手計算する機会は多くありませんが、VAR.PとVAR.Sの差を理解するうえで重要な考え方です。
【操作のポイント】分散は単なる平均との差ではなく、偏差を二乗して平均した値であることを押さえます。
標準偏差との違い
続いては、分散とセットで使われる標準偏差との違いを確認していきます。
| 指標 | 計算 | 単位 |
|---|---|---|
| 分散 | 偏差平方の平均 | 元の単位の二乗 |
| 標準偏差 | 分散の平方根 | 元の単位と同じ |
分散の単位
売上を万円で入力した場合、分散の単位は万円の二乗になります。
長さをミリメートルで測定した場合には、分散は平方ミリメートルという扱いです。
分散は統計計算に適した指標ですが、単位が二乗になるため、現場で直感的に大きさを伝えるには少し扱いにくいことがあります。
数値が大きいから問題とは限らず、平均値や元データの単位と合わせて判断することが大切です。
STDEV.P関数とSTDEV.S関数
標準偏差を求めたい場合は、母集団ならSTDEV.P関数、標本ならSTDEV.S関数を使います。
=STDEV.P(B2:B13)
=STDEV.S(B2:B13)
分散の平方根をSQRT関数で求めても同じ結果になります。
たとえばVAR.P(B2:B13)の結果が25なら、標準偏差はSQRT(25)で5です。
標準偏差は元の数値と同じ単位に戻るため、平均からおよそどれほど離れるかを読み取りやすい指標です。
用途別の使い分け
統計分析、回帰分析、分散分析などの計算途中では、分散を使うことがあります。
一方で、品質管理の報告書や売上の変動を説明する資料では、標準偏差のほうが伝わりやすいケースが少なくありません。
平均が100で標準偏差が5なら、多くの値は100付近に集まっていると読み取れます。
平均が同じでも標準偏差が20なら、データの広がりが大きい状態です。
【操作のポイント】計算ロジックを扱う場合は分散、数値の散らばりを説明する場合は標準偏差を優先します。
分散関数の入力操作
続いては、実際に関数を入力して分散を表示する操作を確認していきます。
| A列 | B列 | C列 | |
|---|---|---|---|
| 1 | 担当者 | 処理件数 | 分散 |
| 2 | 田中 | 38 | 結果表示 |
| 3 | 佐藤 | 45 |
直接入力による計算
最も簡単な方法は、結果を表示したいセルを選択して数式を直接入力する操作です。
たとえばC2を選び、=VAR.P(B2:B11)と入力してEnterキーを押します。
範囲をドラッグして指定する方法なら、セル番地の入力ミスを抑えられます。
数式内のB2:B11には見出しのB1を含めず、数値だけの範囲を指定します。
入力後にセルをクリックすると、数式バーで参照範囲を確認できます。
関数の挿入ダイアログ
関数名を覚えていない場合は、数式タブにある関数の挿入を利用すると便利です。
検索欄に分散と入力すると、VAR.PやVAR.Sなどの候補を確認できます。
関数を選択すると、数値1の入力欄が表示されるため、B2:B11を指定して実行します。
引数ダイアログでは計算結果のプレビューも確認できるため、初めて使う関数でも安心です。
数値1には、分散を計算する実データの範囲を指定します。
複数の離れた範囲を対象にする場合は、数値2以降にも範囲を追加できます。
オートフィルによる複数列の集計
月別、商品別、担当者別に分散を求める場合は、最初の数式を横または縦にコピーします。
参照範囲を列ごとにずらしたいときは、相対参照のままオートフィルを行います。
一方で、比較用の固定セルを参照する式では、ドル記号を使った絶対参照が必要になることがあります。
数式をコピーした後は、参照範囲が意図した列や行に移動しているかを必ず確認しましょう。
【操作のポイント】関数名が不明なら関数の挿入を使い、コピー後は数式バーで範囲のずれを確認します。
グラフによるばらつきの確認
続いては、分散の数値だけでは見えにくいデータの特徴をグラフで確認していきます。
| 月 | 売上 | 平均との差 |
|---|---|---|
| 4月 | 120 | -5 |
| 5月 | 150 | 25 |
| 6月 | 110 | -15 |
折れ線グラフによる時系列の確認
月ごと、日ごと、週ごとのように順序があるデータには、折れ線グラフが適しています。
月と売上の範囲を選択し、挿入タブから折れ線グラフを選ぶと変動の流れを視覚化できます。
分散が大きい場合でも、一時的な急増によるものか、上下動が継続しているのかはグラフを見ると判断しやすくなります。
分散はばらつきの大きさを示しますが、いつ変動したかまでは示しません。
棒グラフによるグループ比較
店舗別、部署別、製品別のようにカテゴリを比較する場合は、集合縦棒グラフが使いやすい形式です。
各グループの平均値と標準偏差を別表にまとめると、平均的な水準と安定性を同時に比較できます。
平均値が高くても分散が非常に大きい場合は、成果の再現性が低い可能性があります。
反対に平均値は少し低くても、分散が小さく安定しているグループには改善のヒントがあるかもしれません。
エラーバーによる標準偏差の表示
平均値の棒グラフに標準偏差をエラーバーとして追加すると、ばらつきの範囲を視覚的に示せます。
グラフを選択し、グラフ要素から誤差範囲を追加して、標準偏差を選択します。
エラーバーには通常、分散ではなく標準偏差を使うため、単位をそのまま比較できます。
発表資料では、平均だけを示すよりも、誤差範囲を併記したほうがデータの信頼性を伝えやすくなります。
【操作のポイント】分散で大きさを比較し、折れ線グラフやエラーバーで変動の理由と範囲を確認します。
まとめ エクセルで標準偏差・関数・グラフから分散を求める方法
エクセルで分散を求めるときは、全データを対象にするならVAR.P関数、抽出した標本を対象にするならVAR.S関数を使用します。
分散は平均値からの偏差を二乗して平均した値であり、データの散らばりを数値で比較するための基本指標です。
元の単位でばらつきを読み取りたい場合は、分散の平方根である標準偏差を確認しましょう。
STDEV.P関数とSTDEV.S関数は、それぞれVAR.P関数とVAR.S関数に対応する標準偏差の関数です。
数式を入力するときは、1行目の見出しを範囲に含めず、実際の数値が入ったセルだけを指定します。
また、分散の結果だけで判断せず、折れ線グラフ、棒グラフ、エラーバーも組み合わせると、変動の原因や傾向を把握しやすくなります。
平均値と分散、標準偏差を並べて確認し、データの大きさだけでなく安定性にも目を向けていきましょう。
