商品リストから「ノートだけの売上のばらつき」を知りたいのに、条件付きで標準偏差を求めるSTDEVIFSのような関数が見当たらない。実務でよくあるこの悩みは、STDEV.S関数の中にFILTER関数を入れることで解決できます。商品名がE2セルに入っているとき、=STDEV.S(FILTER(売上の範囲,商品の範囲=E2))の1つの数式で、その商品だけの標準偏差が求まります。合計や平均、中央値も同じ形で出せます。
条件付きで標準偏差を求めたい場面とSTDEVIFSがない理由
SUMIFSやAVERAGEIFS、MAXIFSには「〜IFS」という条件付きの関数が用意されていますが、標準偏差には「STDEVIFS」にあたる関数がありません。そのため、先に条件に合うデータだけを取り出し、その結果を標準偏差の関数に渡すという2段階の考え方をします。取り出す役目をするのがFILTER関数です（使い方はFILTER関数で条件に合うデータだけを抽出する方法を参照）。
標準偏差を求める商品別売上のサンプル表
日別の商品売上（A1:C11）があり、ノートとボールペンそれぞれの売上のばらつきを比べたいとします。
| A（日付） | B（商品） | C（売上） | |
|---|---|---|---|
| 2 | 10/1 | ノート | 1,200 |
| 3 | 10/1 | ボールペン | 800 |
| 4 | 10/2 | ノート | 1,500 |
| 5 | 10/2 | ボールペン | 950 |
| 6 | 10/3 | ノート | 900 |
| 7 | 10/3 | ボールペン | 1,100 |
| 8 | 10/4 | ノート | 1,800 |
| 9 | 10/4 | ボールペン | 700 |
| 10 | 10/5 | ノート | 1,300 |
| 11 | 10/5 | ボールペン | 1,050 |
以降の数式では、B列=商品、C列=売上、E2=集計したい商品名（「ノート」と入力）という対応で説明します。
STDEV.SとFILTERで条件付きの標準偏差を求める方法
E2の商品の標準偏差は、次の数式で求められます。B列=商品、C列=売上、E2=商品名です。
=STDEV.S(FILTER($C$2:$C$11,$B$2:$B$11=E2))「商品がE2と同じ行の売上だけを取り出して、その標準偏差を求める」という意味の数式です。E2が「ノート」のとき結果は336.2、「ボールペン」のとき168.1になります。
同じ形で、合計・平均・中央値も求められます。
| 求めたい値 | 数式 | ノート | ボールペン |
|---|---|---|---|
| 合計 | =SUM(FILTER($C$2:$C$11,$B$2:$B$11=E2)) |
6,700 | 4,600 |
| 平均 | =AVERAGE(FILTER($C$2:$C$11,$B$2:$B$11=E2)) |
1,340 | 920 |
| 標準偏差 | =STDEV.S(FILTER($C$2:$C$11,$B$2:$B$11=E2)) |
336.2 | 168.1 |
ノートは平均が1,340円、ボールペンが920円で、標準偏差もノートのほうが大きくなっています。つまりノートは売上が高めなだけでなく、日によるばらつきも大きい商品だと読み取れます。平均だけでは分からない「安定しているか」の違いが、標準偏差で見えてきます。平均を条件付きで求める専用の方法はAVERAGEIFSで複数条件の平均を計算する方法、中央値はエクセル関数で条件付きで中央値（MEDIAN）を計算する方法で解説しています。
STDEV.SとSTDEV.Pの違いと使い分け
標準偏差の関数には、STDEV.SとSTDEV.Pの2種類があります。違いは、データを「全体の一部（標本）」とみなすか、「全体そのもの（母集団）」とみなすかです。
| 関数 | 考え方 | ノートの結果 |
|---|---|---|
| STDEV.S | データは全体の一部とみなす（データ数−1で割る） | 336.2 |
| STDEV.P | データは全体のすべてとみなす（データ数で割る） | 300.7 |
手元の5日分のデータから「普段のノートの売上のばらつき」を推測したいならSTDEV.S、その5日間そのもののばらつきを知りたいならSTDEV.Pが目安です。データ数が多くなるほど2つの差は小さくなります。どちらを使うか社内で決まっている場合は、それに合わせましょう。
FILTER関数が使えないExcelで条件付きの標準偏差を求める方法
FILTER関数はMicrosoft 365とExcel 2021以降で使えます。それ以前のExcelでは、IF関数で条件に合う行だけを残す配列数式を使います。B列=商品、C列=売上、E2=商品名です。
=STDEV.S(IF($B$2:$B$11=E2,$C$2:$C$11))入力したあとCtrl+Shift+Enterで確定すると、数式が{ }で囲まれて配列数式として動きます。条件に合わない行はFALSEになりますが、STDEV.Sは配列に含まれる論理値を無視するので、結果は同じ336.2になります。
条件付きの標準偏差でよくある注意点
- 条件に合うデータが1件しかないと#DIV/0!になる: STDEV.Sは2件以上のデータが必要です。1件だと計算できずエラーになります。回避の方法は#DIV/0!エラーを回避する方法を参照してください。
- 条件に合うデータが0件だと#CALC!になる: FILTERが該当なしの状態を返すため、そのまま標準偏差を求めるとエラーが出ます。
=IFERROR(STDEV.S(FILTER($C$2:$C$11,$B$2:$B$11=E2)),"該当なし")のように包むと、見た目を整えられます。 - 商品名の表記ゆれ: 「ノート」と「ノート 」（末尾にスペース）は別の文字列として扱われ、条件に合いません。条件に合う件数が少ないときは、表記ゆれやスペースを疑います。
- 外れ値に影響されやすい: 標準偏差は極端に大きい（小さい）値があると大きく変わります。外れ値を除いて平均を見たいときはTRIMMEAN関数で異常値を除外して平均を求める方法の方法も参考になります。
条件付きで標準偏差を求める方法のまとめ
条件付きの標準偏差には専用の関数がないため、=STDEV.S(FILTER(売上の範囲,商品の範囲=E2))のように、FILTER関数で取り出したデータを標準偏差の関数に渡します。データを全体の一部とみなすならSTDEV.S、全体そのものならSTDEV.Pを使い分け、FILTER関数が使えない環境ではIF関数の配列数式で代用しましょう。条件で絞り込んだ集計全般については、サイトの人気記事エクセル関数で条件付きで中央値（MEDIAN）を計算する方法もあわせてご覧ください。