スプレッドシートのDVARP関数の使い方｜条件に合う母分散

スポンサーリンク

「全店舗の売上のばらつきを正確に見たい」「全在庫ロットの品質ばらつきをライン別に比較したい」「全社員の評価スコアの分散を部署別に出したい」。こんな場面、手元のデータが「対象の全て」であるケース、意外と多いですよね。

条件が変わるたびにフィルタをかけ直してVARP関数を呼び直すのは地味に手間です。共有シートだと他の人の表示にも影響してしまうので、気を使う場面も多いはずです。

そんなときに便利なのがDVARP関数です。条件を別のセル範囲に書いておくだけで、該当するデータの母分散（ばらつきの指標）を自動で返してくれます。この記事では、スプレッドシートのDVARP関数の基本から複数条件・OR条件の応用、DVAR関数との使い分けまでまとめて紹介します。

スプレッドシートのDVARP関数とは？

DVARP関数（読み方: ディーバリアンスポピュレーション）は、データベース形式の表から条件に合うレコードを探し、指定した列の値の母分散を返す関数です。

名前は「Database VARiance Population（データベースの母分散）」の略です。DSUM（条件付き合計）やDAVERAGE（条件付き平均）と同じ「データベース関数」の仲間になります。平均や合計ではなく「ばらつき」を測るのがDVARP関数の特徴です。

DVARP関数の特徴をまとめると、次のとおりです。

  • 条件をセル範囲（条件範囲）で指定するスタイル
  • 条件範囲を書き換えるだけで集計条件をすぐ切り替えられる
  • 複数条件（AND条件・OR条件）にも対応
  • 見出し付きのリスト形式のデータが前提
  • 対象データを「母集団（全体）」とみなし、nで割る母分散を返す
  • 条件に一致するレコード数が1件でも計算可能（1件の場合は0）

NOTE

DVARP関数はGoogleスプレッドシートの全バージョンで使えます。Excelにも同じ関数があるので、ファイルのやり取りでも安心です。

DVAR関数との違い（標本 vs 母集団）

DVARP関数とDVAR関数の違いは「データを母集団とみなすか、標本とみなすか」です。

比較項目DVARP関数DVAR関数
データの扱い母集団（全体）とみなす標本（サンプル）とみなす
計算式偏差二乗和を n で割る偏差二乗和を n-1 で割る
結果の傾向やや小さめの値やや大きめの値
用途手元のデータが対象全体であるとき一部のデータから全体を推定したいとき

使い分けのポイント:

  • 全社員・全店舗・全在庫・全ロットなど、対象が母集団そのもの → DVARP関数（母集団）
  • アンケートの回答者（全社員ではなく一部）・抜き取り検査など → DVAR関数（標本）
  • 判断に迷ったらDVAR関数（標本） が実務では無難

DSTDEVP関数との違い（分散 vs 標準偏差）

DVARP関数とDSTDEVP関数は「分散を返すか、標準偏差を返すか」の違いです。

比較項目DVARP関数DSTDEVP関数
何を返すか母分散母標準偏差
計算上の関係DSTDEVPの二乗DVARPの平方根
単位元データの単位の二乗元データと同じ単位
用途統計モデル・分析で使う直感的にばらつきを比較したい

標準偏差は「平均からの距離の目安」として単位が元データと同じなので直感的です。分散は数式上で便利ですが、単位が二乗になるので読み取りづらい面があります。用途に合わせて使い分けてください。

DVARP関数の書き方（構文と引数）

基本構文

=DVARP(データベース, フィールド, 条件)

引数は3つです。すべて必須で、省略はできません。

引数の説明

引数必須/任意説明
データベース必須見出し行を含むデータ範囲（例: A1:D100）
フィールド必須母分散を求める列の見出し名（文字列）または列番号（数値）
条件必須条件を記述したセル範囲（見出し行+条件行）

それぞれ詳しく見ていきましょう。

データベース（第1引数）

データベースには、見出し行を含めたデータ範囲を指定します。先頭行に列の見出し（「部署」「商品名」「売上」など）が入っている必要があります。

フィールド（第2引数）

母分散を求めたい列を指定します。指定方法は2つあります。

  • 文字列で指定: "売上" のように、見出しと同じ文字列をダブルクォーテーションで囲む
  • 数値で指定: データベースの左端列を1として、列の位置を数値で指定する（4列目なら 4）

文字列で指定するほうが、あとから見たとき何の列かわかりやすいのでおすすめです。

条件（第3引数）

条件範囲には、見出し行と条件行の2行以上のセル範囲を指定します。これがDVARP関数の最大の特徴です。

TIP

フィールドに列番号を使う場合、データベース範囲の左端が1です。シートのA列が1とは限らないので注意してください。

DVARP関数の基本的な使い方

サンプルデータ

次のような売上データを使って説明します。全店舗の月間売上をまとめた表です（この表が全店舗の完全なデータとします）。

 ABCD
1月エリア店舗売上
21東日本東京120
32東日本東京150
43東日本横浜180
54東日本横浜90
65西日本大阪100
76西日本神戸110
87西日本神戸105

条件範囲の設定方法

DVARP関数の条件は、別のセル範囲に書きます。

条件範囲は次のルールで作ります。

  1. 1行目に見出しを書く — データベースの見出しと完全に同じ文字列を使う
  2. 2行目に条件値を書く — 一致させたい値を入力する

たとえば「東日本」エリアの売上の母分散を求めたい場合、シートの空いているエリア（たとえばF1:F2）に次のように書きます。

 F
1エリア
2東日本

数式はこうなります。

=DVARP(A1:D8, "売上", F1:F2)

結果は東日本の4件（120、150、180、90）の母分散で 1125 です。これら4件が「対象の全て」という前提で、n（つまり4）で割った値になります。

TIP

条件範囲の見出しは、データベースの見出しと1文字でも違うと正しく動きません。コピー&ペーストで作ると確実ですよ。

DVAR関数と結果の比較

同じデータでDVAR関数と比較してみます。

=DVAR(A1:D8, "売上", F1:F2)   → 1500（n-1=3で割る）
=DVARP(A1:D8, "売上", F1:F2) → 1125（n=4で割る）

DVARP関数のほうが小さい値になります。このデータが「全体（母集団）」ならDVARP、「一部（標本）」ならDVARを使います。

4件データの場合、DVAR（標本）とDVARP（母集団）の結果比は n:n-1 = 4:3 ぶんだけ変わります。件数が少ないほど差が目立ち、件数が多いほど差は小さくなります。

VARP関数と結果の比較

条件指定なしのVARP関数と比べてみます。VARP関数は範囲を直接指定するスタイルです。

=VARP(D2:D5)                 → 1125（東日本4件のみを直接指定）
=DVARP(A1:D8, "売上", F1:F2) → 1125（条件で東日本に絞り込み）

どちらも同じ結果です。ただしVARP関数は「範囲を直接指定する」のに対して、DVARP関数は「条件を書き換えるだけで集計範囲を変えられる」という違いがあります。条件が変わる分析ではDVARP関数が圧倒的に便利です。

比較演算子を使った条件

条件値には比較演算子も使えます。

条件値の書き方意味
東日本「東日本」と完全一致
>=100100以上
<150150未満
<>東日本「東日本」以外

たとえば「売上が100以上」のレコードに絞って母分散を見たい場合は、条件範囲をこう書きます。

 F
1売上
2>=100
=DVARP(A1:D8, "売上", F1:F2)

100以上の6件（120、150、180、100、110、105）の母分散 約814.58 が返ります。

DVARP関数の実践的な使い方・応用例

複数条件（AND条件）で母分散を求める

「東日本エリア」かつ「売上が100以上」のように、複数の条件をすべて満たすレコードで母分散を求めたい場合です。

AND条件は、条件範囲の同じ行に複数の見出し・条件値を横に並べて書きます。

 FG
1エリア売上
2東日本>=100
=DVARP(A1:D8, "売上", F1:G2)

東日本で売上100以上の3件（120、150、180）の母分散 600 が返ります。

OR条件で母分散を求める

「東日本」または「西日本」のように、どちらかの条件に合うレコードから母分散を求めたい場合です。

OR条件は、条件値を別の行に書くのがポイントです。

 F
1エリア
2東日本
3西日本
=DVARP(A1:D8, "売上", F1:F3)

全7件の売上の母分散 約870.41 が返ります。

同じ行に書くとAND条件、別の行に書くとOR条件。このルールを覚えておきましょう。

全店舗の売上ばらつきをエリア別に比較する

全店舗データが手元にある場合、エリアごとの売上ばらつきをDVARP関数で比較できます。

条件範囲のF2セルを「東日本」→「西日本」と書き換えるだけで、DVARP関数の結果が切り替わります。数式を修正する必要はありません。

ドロップダウンリスト（データの入力規則）と組み合わせると、選択するだけでエリア別の母分散をサッと確認できる簡易ダッシュボードが作れますよ。

全在庫ロットの品質ばらつきを製品ライン別に比較する

製造データで、全ロットの検査データが手元にある場合、製品ライン別の品質ばらつきをDVARP関数で確認できます。

 ABC
1製品ライン寸法
2製品A第110.2
3製品A第110.1
4製品A第210.5
5製品A第29.8
6製品B第120.3

「製品A・第1ライン」の全ロットデータ（これが母集団）の寸法ばらつきを確認する場合です。

=DVARP(A1:C6, "寸法", E1:F2)

条件範囲（E1:F2）には「製品」「ライン」の見出し＋「製品A」「第1」を入れておきます。全ロットが対象なので、DVARP関数（nで割る）を使います。

全社員の評価スコアばらつきを部署別に分析する

全社員の人事評価データが手元にあるなら、部署別の評価スコアのばらつきをDVARP関数で確認できます。

評価が極端に偏っている部署（分散が大きい）や、全員同じような評価になっている部署（分散が小さい）を可視化できます。評価運用の偏りを把握する材料として使えます。

条件範囲の「部署」を切り替えるだけで、部署別の母分散が順番に取得できます。

DVAR関数とDVARP関数の使い分け

最後に、分散系の4関数（DVAR/DVARP/VAR/VARP）の位置関係を整理しておきます。

関数条件指定扱い計算式使うシーン
VARできない標本n-1で割る範囲全体のばらつき、サンプルデータ
VARPできない母集団nで割る範囲全体のばらつき、全データ
DVARできる標本n-1で割る条件付きのばらつき、サンプルデータ
DVARPできる母集団nで割る条件付きのばらつき、全データ

選び方の2問フロー:

  1. 条件で絞り込みたい？ → YES なら DVAR/DVARP、NO なら VAR/VARP
  2. 手元のデータは全体か一部か？ → 全体なら VARP/DVARP、一部なら VAR/DVAR

全社員・全店舗・全ロットなど、「対象の全て」が揃っているケースでDVARPを使います。逆に一部サンプルしか手元にない場合はDVAR（標本）を選びます。判断に迷ったら標本扱いのDVARにしておくのが実務では無難です。

よくあるエラーと対処法

DVARP関数で「思った結果にならない」ケースをまとめました。

症状原因対処法
#NUM! エラー対象列に数値が1つも含まれていない数値が入っている列をフィールドに指定する
結果が0になる対象データの値がすべて同じ、または1件のみそのデータ内では母分散=0が正しい結果
結果が想定より大きくなる条件範囲に空白行が含まれている条件範囲を必要な行だけに絞る（空白行は「すべて一致」と解釈される）
#VALUE! エラーフィールドに存在しない列名を指定しているデータベースの見出しと同じ文字列を使う
DVARと結果が違う計算式の違い（n vs n-1）意図どおりの関数を選んでいるか確認する
条件が部分一致になる条件値にアスタリスクが含まれている完全一致にしたい場合は ="=東日本" のように先頭に = を付ける
文字列の列でエラーになるDVARP関数は数値列の母分散を返す関数文字列の列を指定すると計算対象にならない。数値列を指定すること
件数0件で0が返る条件に一致するレコードがない（0件）条件範囲の見出し・条件値を見直してDCOUNT関数で件数を確認する

TIP

条件に合うレコードが何件あるかを確認したいときはDCOUNT関数が便利です。DCOUNT関数で件数を確認してから、DVARP関数で母分散を求めると安心です。DVAR関数と違いDVARPは1件でも0を返すので、件数0件と「全部同じ値」の見分け材料として使えます。

まとめ

DVARP関数は、データベース形式の表から条件に合うデータの母分散（ばらつきの指標）を求める関数です。

ポイントを整理します。

  • 構文は =DVARP(データベース, フィールド, 条件) で、引数は3つ
  • 条件は数式内ではなくセル範囲（条件範囲）に書くのが特徴
  • 条件範囲は「見出し行+条件行」のセットで作る
  • 同じ行に条件を横並びにすればAND条件、別の行にすればOR条件
  • 条件セルの値を変えるだけで分析対象を切り替えられる
  • DVARP関数は母集団（nで割る）、DVAR関数は標本（n-1で割る）
  • 手元のデータが対象全体（全社員・全店舗・全在庫など）のときにDVARPを使う
  • 判断に迷ったらDVAR関数（標本）が実務では無難
  • 対象データの値がすべて同じなら結果は0（ばらつきなし）
  • 分散の単位は元データの二乗。直感的に見たいならSQRTで標準偏差に変換

全店舗の売上ばらつき比較や、全ラインの品質安定性チェックで威力を発揮する関数です。まずは簡単な表で =DVARP(A1:D8, "売上", F1:F2) から試してみてください。

関連記事

スポンサーリンク
タイトルとURLをコピーしました