Excelで標準偏差を求める方法　STDEV.SとSTDEV.Pの違いを解説

Excelで売上や契約件数などを分析する際、平均値だけではわからない数値のばらつきを示すのが「標準偏差」です。

この記事では、標準偏差の基本や計算手順、グラフでの表示方法などを解説します。標準偏差の求め方や、STDEV.SとSTDEV.Pの使い分け、エラーが出たときの対処法にお悩みの方は、ぜひ参考にしてください。

Excelで標準偏差を求めるならSTDEV.S関数かSTDEV.P関数を使う

標準偏差とは、データが平均値からどの程度ばらついているかを示す統計指標です。値が小さいほど平均値の近くにデータが集まり、大きいほど数値にばらつきがあることを意味します。

たとえば、2人の営業担当者の月間契約件数が同じ平均10件でも、毎月9〜11件の担当者と、5〜15件の担当者では安定性が異なります。標準偏差を確認すれば、平均値だけでは見えない実績の変動を数値で比較できます。

STDEV.S関数は、一部の営業日のデータを抽出し、年間の売上傾向を推定する際に使います。一方、対象期間の全営業日など、分析対象のデータがすべてそろっている場合はSTDEV.P関数を使用します。迷ったときは、分析対象が一部か全体かを確認しましょう。

標準偏差を求めるExcel関数は用途によって使い分ける

Excelには標準偏差を計算する関数が複数あるため、分析するデータの範囲や種類によって使い分けましょう。

STDEV.S関数は一部のデータから全体のばらつきを推定するときに使う

STDEV.S関数は、母集団から抽出した一部のデータ（標本）をもとに、全体のばらつきを推定する際に使用します。

基本構文は

=STDEV.S(数値1,[数値2],…)

で、数値やセル範囲を指定します。STDEV.S関数では、データと平均値との差の二乗和を「データ数−1」で割った不偏分散の平方根を求めます。

STDEV.P関数は対象のデータがすべてそろっているときに使う

STDEV.P関数は、分析対象のすべてのデータがそろっている場合に使用します。基本構文は

=STDEV.P(数値1,[数値2],…)

で、各データと平均値の差の二乗和を「データ数」で割った分散の平方根を求めます。ただし、全データをより大きな集団の標本として扱う場合は、STDEV.S関数を使用します。

STDEV関数とSTDEVP関数は旧バージョンとの互換用として残っている

STDEV関数とSTDEVP関数は、旧バージョンのExcelで使用されていた関数です。現在も互換性を保つために利用でき、計算結果はそれぞれSTDEV.S関数、STDEV.P関数と同じです。新しく集計表を作成する場合は、用途がわかりやすいSTDEV.S関数とSTDEV.P関数を使用しましょう。

STDEVA関数とSTDEVPA関数は文字列や論理値も計算対象に含める

STDEVA関数とSTDEVPA関数は、セル参照内の文字列や論理値も計算対象に含めます。STDEVA関数は標本、STDEVPA関数は母集団の標準偏差を求めます。セル参照内のTRUEは1、FALSEや文字列は0として扱われ、空白セルは除外されます。

通常の売上や契約件数など、数値のばらつきを分析する場合は、STDEV.S関数またはSTDEV.P関数を使用しましょう。

STDEV.S関数で標準偏差を求める手順

ここからは、営業担当者の月間契約件数を例に、入力から結果の確認までを解説します。

計算したい数値データを1列にまとめる

まずA1セルに「月」、B1セルに「契約件数」と入力します。続いて、A2〜A11に1月〜10月、B2〜B11に各月の契約件数を入力しましょう。契約件数は「10件」ではなく「10」のように数値だけを入力してください。

関数を入力して計算するデータ範囲を指定する

次に、計算結果を表示するB13セルを選択し、以下のように入力します。

=STDEV.S(B2:B11)

今回のデータでは、標準偏差は約2.94と表示されます。数式内の「B2:B11」は計算対象の範囲です。なお、数式入力中にF4キーを押すと、参照範囲を絶対参照に切り替えられます。数式をほかのセルにコピーして使用する際に便利です。

AVERAGE関数で求めた平均値と並べて表示する

標準偏差は、平均値と並べることでデータのばらつきを把握しやすくなります。B12セルに

=AVERAGE(B2:B11)

と入力すると、平均契約件数が10件、標準偏差が約2.94件と並んで表示され、実績の分析や報告資料の作成に活用できます。

 

 

STDEV.P関数も同じ手順で入力できる

B14セルに以下を入力してみましょう。

=STDEV.P(B2:B11)

今回のデータでは、STDEV.S関数の結果が約2.94、STDEV.P関数の結果が約2.79となります。同じデータでも計算結果が異なるため、一部のデータから全体を推定する場合は「STDEV.S」、分析対象の全データを集計する場合は「STDEV.P」が適しています。

 

条件に合うデータだけで標準偏差を求める方法

部署別や担当者別など、特定の条件に一致するデータだけの標準偏差を求めたい場合は、FILTER関数やIF関数を組み合わせます。

FILTER関数と組み合わせて条件に合うデータを抽出する

FILTER関数で条件に合うデータを抽出し、STDEV.S関数で計算します。たとえば、A2〜A11に部署名、B2〜B11に契約件数がある場合、営業部の標準偏差は次の数式で求められます。

=STDEV.S(FILTER(B2:B11,A2:A11="営業部"))

条件に一致する数値データが1件以下の場合、STDEV.S関数では標準偏差を計算できません。また、条件に一致するデータが存在しない場合は、FILTER関数自体が「#CALC!」エラーを返します。なお、FILTER関数は、Microsoft 365やExcel 2021以降などの対応環境で使用できます。

FILTER関数が使えない環境ではIF関数の配列数式で代用する

FILTER関数が使えないExcelでは、IF関数とSTDEV.S関数を組み合わせた配列数式で代用できます。

=STDEV.S(IF(A2:A11="営業部",B2:B11))

この数式では、営業部に該当する契約件数だけを計算します。Excel 2019以前などの動的配列に対応していない環境では、入力後にCtrl＋Shift＋Enterキーで確定してください。
Microsoft 365などの動的配列対応版では、通常のEnterキーで計算できます。

 

標準偏差をグラフで表示する方法

標準偏差をグラフで表示すると、データのばらつきを視覚的に伝えられます。Excelでは、棒グラフに誤差範囲（エラーバー）を追加することで、平均値と標準偏差をあわせて示せます。

平均値の棒グラフを作成して誤差範囲を追加する

まず、部署別の平均契約件数と標準偏差をまとめた表を用意します。部署名と平均値のセル範囲を選択し、「挿入」タブから「集合縦棒」をクリックして棒グラフを作成しましょう。

次にグラフを選択し、右上の「＋」ボタンから「誤差範囲」にチェックを入れます。初期設定の誤差範囲は、求めたい標準偏差と一致するとは限らないため、次の手順で事前に計算した標準偏差を指定します。

 

 

誤差範囲のユーザー設定で標準偏差のセルを指定する

誤差範囲を右クリックし、「誤差範囲の書式設定」を選択します。続いて、「誤差範囲のオプション」から「ユーザー設定」→「値の指定」をクリックしましょう。

「正の誤差の値」と「負の誤差の値」に、計算済みの標準偏差のセル範囲を指定します。たとえば、標準偏差がF9:F11にある場合は、両方に「=$F$9:$F$11」を指定します。これで、各部署の平均値に対する標準偏差を誤差範囲で表示できます。

標準偏差を業務データの分析に活かす

標準偏差と平均値を併用すると、売上・契約件数・業務時間の安定性や、改善すべき業務を把握しやすくなります。

平均値±標準偏差の範囲にデータの約68％が収まる

データが正規分布に従う場合、平均値±1標準偏差の範囲に約68％のデータが含まれます。たとえば、平均契約件数が10件、標準偏差が3件なら、約68％が7〜13件の範囲に入ると考えられます。ただし、売上や契約件数が必ずしも正規分布に従うとは限りません。

実務では、この割合を絶対的な基準とせず、データの分布や外れ値も確認しましょう。平均値から大きく離れた数値があっても、直ちに異常とは判断できません。

平均値が異なるデータ同士は変動係数で比較する

標準偏差を平均値で割った「変動係数」を使えば、平均値が異なるデータのばらつきを比較できます。たとえば、B12セルに平均値、B13セルに標準偏差がある場合は、次の数式を入力します。

=B13/B12

セルをパーセント表示にすれば、平均値に対するばらつきの割合を確認できます。売上規模が異なる部署の安定性を比較する際などに便利ですが、平均値が0に近い場合やマイナスの場合は、解釈に注意が必要です。

標準偏差が正しく求められないときの対処法

エラーや想定外の結果が表示される際は、データ数の不足や文字列の混入、空白セルの扱いを確認しましょう。

データが1件以下だと0除算エラーが表示される

STDEV.S関数では、計算対象となる数値データが1件以下の場合、「#DIV/0!」エラーが表示されます。不偏分散の計算では「データ数−1」で割るため、数値が2件以上あるかを確認してください。なお、STDEV.P関数では、数値データが1件の場合は0が返されますが、数値が1件もない場合はエラーになります。

文字列として入力された数値は計算から除外される

STDEV.S関数やSTDEV.P関数では、参照範囲内に文字列として保存された数値があると、計算対象から除外されます。たとえば、「10件」と入力したセルや、先頭にアポストロフィが付いた数値は文字列として扱われます。

対処するには、セルの入力内容や表示形式を確認し、数値として入力し直しましょう。「=COUNT(B2:B11)」で数値セルの個数を求め、入力件数と一致するかを確認すると便利です。

空白セルと0の入ったセルでは計算結果が変わる

STDEV.S関数やSTDEV.P関数では、空白セルは計算対象から除外されますが、数値の0は計算対象に含まれます。契約件数が未入力のセルはデータがない状態であり、「0」は実績が0件だった状態です。そのため、空白を0へ置き換えると標準偏差が変わります。複数人で管理する場合も、未入力と実績0件を区別し、入力ルールを統一しましょう。

分散もExcel関数で同じように求められる

「分散」もデータのばらつきを表す指標ですが、標準偏差とは単位が異なります。

標準偏差は分散の平方根として計算される

「標準偏差」は分散の平方根を取った値です。一方、「分散」は各データと平均値との差を二乗し、平均したもので、単位も元データの2乗になります（VAR.S関数では「データ数−1」で割ります）。

たとえば、契約件数の分散は「件の2乗（件²）」ですが、標準偏差は元データと同じ「件」で表せます。そのため、標準偏差は数値の意味を直感的に理解しやすく、営業実績の分析や報告資料にも適しています。

分散はVAR.S関数とVAR.P関数で求める

分散には、VAR.S関数とVAR.P関数を使います。

一部のデータから全体の分散を推定する場合は

=VAR.S(B2:B11)

分析対象の全データがそろっている場合は

=VAR.P(B2:B11)

と入力します。

画像の例のように部署別に求める場合は、FILTER関数と組み合わせて

=VAR.S(FILTER(B2:B11,A2:A11="営業部"))

のように指定します。VAR.P関数も同様です。なお、FILTER関数が使えないExcel 2019以前では、IF関数を使った配列数式で代用できます。

よくある質問（FAQ）

Excelの標準偏差について、実務で疑問に感じやすいポイントをまとめました。

Q. STDEV.S関数とSTDEV.P関数はどちらを使えばよいですか？

A. 一部のデータから全体のばらつきを推定する場合はSTDEV.S関数、分析対象の全データがそろっている場合はSTDEV.P関数を使います。ただし、全データをさらに大きな集団の標本として扱う場合は、STDEV.S関数を選びましょう。

Q. Googleスプレッドシートでも同じ関数で標準偏差を求められますか？

A. はい。Googleスプレッドシートも基本構文や計算方法は共通なため、Excelで作成した数式（STDEV.S関数とSTDEV.P関数）を応用できます。ただし、グラフの作成方法や一部の操作手順は異なるため、使用環境に応じて確認しましょう。

Q. MacのExcelでも同じ手順で標準偏差を求められますか？

A. はい。Mac版ExcelでもSTDEV.S関数とSTDEV.P関数を使い、Windows版と同じ数式で標準偏差を計算できます。ただし、グラフのメニュー配置やショートカットキーなど、一部の操作方法は異なります。

Q. 標準偏差と標準誤差の違いは？

A. 標準偏差は、平均値からのばらつきを示す指標である一方、標準誤差は、標本から求めた平均値の推定精度を表します。たとえば、契約件数のばらつきを調べるなら標準偏差、抽出データから推定した平均契約件数の不確かさを確認するなら標準誤差を使用します。

まとめ

Excelの標準偏差は、一部のデータから全体を推定するならSTDEV.S関数、対象の全データを集計するならSTDEV.P関数で求めます。また、FILTER関数を組み合わせた条件別の計算や、グラフの誤差範囲を活用すると、部署ごとの実績比較にも役立ちます。用途に合った関数を選び、売上や契約件数などの業務分析や報告資料に活用しましょう。