ExcelのDVARP関数の使い方｜条件一致行の母分散

スポンサーリンク

「全社の売上データの母分散を見たい」「特定ラインの製品データ全体で品質のばらつきを統計的に評価したい」。こんな場面で、フィルタで絞り込んでからVAR.P関数を使っていませんか。

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

そんなときに便利なのがExcelのDVARP関数です。条件を別のセル範囲に書いておくだけで、該当するレコードの母分散（ばらつきの指標）を自動で返してくれます。条件セルを書き換えれば結果も切り替わるので、簡易ダッシュボードのような使い方もできますよ。

この記事では、ExcelのDVARP関数の基本構文から実務での使い方まで、まとめて紹介します。
複数条件・OR条件の応用、VAR.P関数やDVAR関数との使い分け、よくあるエラーの対処法も実例付きで解説しますね。

ExcelのDVARP関数とは？

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

名前は「Database VARiance Population（データベースの母分散）」の略です。DSUM関数（条件付き合計）やDAVERAGE関数（条件付き平均）と同じ「データベース関数（D関数）」の仲間になります。対象データを「全体（母集団）」とみなして分散を求めるのがDVARP関数の特徴です。

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

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

NOTE

DVARP関数はExcel 2003以降のすべてのバージョンで使えます。Microsoft 365、Excel for Web、Mac版でも同じ動作です。

母分散と標本分散の違い

DVARP関数の話に入る前に、母分散と標本分散の関係を整理しておきます。どちらも「データのばらつき」を表す指標ですが、データの扱い方が違います。

比較項目母分散（Population Variance）標本分散（Sample Variance）
扱いデータは「母集団（全体）」データは「標本（サンプル）」
計算式偏差二乗和をnで割る偏差二乗和をn-1で割る
結果やや小さめやや大きめ（不偏分散）
用途対象データそのものを評価したいサンプルから全体を推定したい

母分散は対象データそのものをまるごと評価するときに使います。一方、標本分散は手元のデータをサンプルとみなし、そこから「全体（母集団）」を推定するときに使います。

実務では「すべて手元にあるデータ」を扱うケースと「一部のサンプルから全体を推定したい」ケースの両方があります。DVARP関数とDVAR関数を使い分けられるようにしておくと安心ですよ。

VAR.P関数との違い（全体のばらつき vs 条件付きのばらつき）

DVARP関数とVAR.P関数の最大の違いは「条件を付けられるかどうか」です。

比較項目VAR.P関数DVARP関数
条件指定できない（範囲全体が対象）できる（条件範囲で絞り込み）
構文=VAR.P(範囲)=DVARP(データベース, フィールド, 条件)
用途シンプルに範囲全体の母分散を見る特定の条件に合うデータだけの母分散を見る

たとえば「範囲全体の母分散を知りたい」ならVAR.P関数で十分です。「営業部の担当分だけの母分散を見たい」のように条件を付けたいなら、DVARP関数の出番になります。

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

基本構文

=DVARP(database, field, criteria)

引数は3つで、すべて必須です。日本語Excelの数式バーには =DVARP(データベース, フィールド, 条件) と表示されます。

引数の説明

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

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

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

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

フィールド（第2引数）

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

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

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

条件（第3引数）

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

条件範囲の作り方は次のセクションで詳しく説明しますね。

TIP

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

ExcelのDVARP関数の基本的な使い方

サンプルデータ

次のような売上データを使って説明します。担当者ごとの月間売上をまとめた表です。

 ABCD
1月部署担当売上
21営業部田中120
32営業部田中150
43営業部佐藤180
54営業部佐藤90
65総務部鈴木100
76総務部伊藤110
87総務部伊藤105

条件範囲の設定方法

DVARP関数の条件は、別のセル範囲に書きます。ここがVAR.P関数との大きな違いです。

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

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

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

 F
1部署
2営業部

数式はこうなります。

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

結果は営業部の4件（120、150、180、90）の母分散で 1125.00 が返ります。売上の平均からどれくらいばらついているかを、二乗の単位で求められます。

TIP

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

比較演算子を使った条件

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

条件値の書き方意味
営業部「営業部」と前方一致
>=100100以上
<150150未満
<>営業部「営業部」以外

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

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

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

NOTE

文字列の条件値（例: 「営業部」）は既定では前方一致になり、「営業1課」なども一致してしまいます。完全一致にしたい場合は ="=営業部" のように先頭に = を付けてダブルクォートで囲んでください。

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

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

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

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

 FG
1部署売上
2営業部>=100
=DVARP(A1:D8, "売上", F1:G2)

結果は営業部で売上100以上の3件（120、150、180）の母分散で 600.00 が返ります。営業部の中でも好調な案件だけに絞ったばらつきの指標を確認できます。

OR条件で母分散を求める

「営業部」または「総務部」のように、どちらかの条件に合うレコードから母分散を求めたい場合です。

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

 F
1部署
2営業部
3総務部
=DVARP(A1:D8, "売上", F1:F3)

結果は営業部と総務部のすべてのレコード7件分の売上の母分散 約870.41 が返ります。

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

部署ごとに売上のばらつきを比較する

同じ条件範囲の部署セルだけを書き換えれば、部署ごとの母分散をサッと比較できます。

 F
1部署
2営業部
=DVARP(A1:D8, "売上", F1:F2)

営業部の売上4件（120、150、180、90）で 1125.00 が返ります。
F2セルを「総務部」に書き換えると、総務部の売上3件（100、110、105）の母分散 約16.67 に切り替わります。

同じ売上規模でも、営業部のほうが案件ごとの差が大きく、総務部は安定している。こうした傾向をひと目で比較できるのがDVARP関数の便利なところです。

全社員のデータで母分散を求める

DVARP関数が真価を発揮するのは、対象データが「母集団そのもの」と言える場面です。たとえば、全社員の業績データを集めた表があるとします。

 ABC
1社員部署評価点
2田中営業部80
3佐藤営業部75
4鈴木総務部70
5伊藤総務部85

全社員のデータが揃っているので、対象は「サンプル」ではなく「母集団」です。営業部の評価点の母分散を求めるならこうなります。

=DVARP(A1:C5, "評価点", E1:E2)

条件範囲（E1:E2）には「部署」と「営業部」を入れておきます。全社員という母集団の中の営業部の母分散を、そのまま評価指標として使えますよ。

品質データの安定性をチェックする

製造データや検査データなど、ある期間に作られた製品をまるごと評価したいケースでも活躍します。

 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」を入れておきます。その期間に第1ラインで作られた製品Aすべてを母集団とみなしてばらつきを評価できますよ。

条件範囲を切り替えて分析を素早く変える

DVARP関数の大きな強みは、条件をセルに書いているため、セルの値を書き換えるだけで結果が即座に変わる点です。

たとえば条件範囲のF2セルを書き換えるだけで結果が切り替わります。「営業部」を「総務部」に変えれば、DVARP関数の結果も自動的に総務部の母分散に切り替わるんです。数式を修正する必要はありません。

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

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

DVARP関数と似た関数にDVAR関数があります。どちらも条件付きで分散を求めますが、「データを母集団とみなすか標本とみなすか」が違います。

比較項目DVARP関数DVAR関数
扱いデータは「母集団（全体）」データは「標本（サンプル）」
計算式偏差二乗和を n で割る偏差二乗和を n-1 で割る
結果やや小さめの値（母分散）やや大きめの値（不偏分散）
レコード数1件でも計算可（1件なら0）2件以上必要（1件以下は #DIV/0!）
用途対象データが全体そのものであるときサンプルから全体を推定したいとき

使い分けのポイントは次のとおりです。

  • 全社員のデータや、ある期間の全製品など、対象が母集団そのもの → DVARP関数
  • アンケート結果や品質検査のサンプルなど、一部のデータから全体を推定したい → DVAR関数
  • 判断に迷ったらDVAR関数（標本とみなす方） が実務では無難

似た関係の関数も整理しておきましょう。条件なしで全体の分散を出すなら、母集団用のVAR.P関数と標本用のVAR.S関数があります。条件付きで標準偏差を出すなら、DSTDEV関数やDSTDEVP関数を使います。用途に応じて使い分けてくださいね。

TIP

DVARP関数の結果の平方根を取ると、DSTDEVP関数と同じ値になります。=SQRT(DVARP(...)) と =DSTDEVP(...) は理論上同じ値です。レポートでは標準偏差、計算の中間値では分散、と覚えておくと使い分けがしやすくなりますよ。

DVARP関数のよくあるエラーと対処法

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

症状原因対処法
#NUM! エラー対象列に数値が1つも含まれていない数値が入っている列をフィールドに指定する
#VALUE! エラーフィールドに存在しない列名を指定しているデータベースの見出しと完全一致する文字列を使う
結果が0になる対象データが1件のみ、または値がすべて同じ1件のときの母分散は0が正しい結果。複数件にしたい場合は条件を緩める
結果が想定より大きい条件範囲に空白行が含まれている条件範囲を必要な行だけに絞る（空白行は「すべて一致」と解釈される）
VAR.Pと結果が違う条件で絞った対象レコードが違う条件範囲の見出し・条件値を見直して対象レコードを確認する
DVARと値が違うDVARはn-1、DVARPはnで割るため当然違う値になる母集団ならDVARP、標本ならDVAR。データの性質で使い分ける
想定と違う列の母分散が返るフィールドの列番号を間違えている列番号ではなく見出し名（文字列）で指定するのがおすすめ
条件が部分一致になる文字列条件は既定で前方一致完全一致したい場合は ="=営業部" のように先頭に = を付ける
文字列の列でエラーになるDVARP関数は数値列の母分散を返す関数文字列の列を指定すると計算対象にならない。数値列を指定すること

TIP

DVARP関数は1件でも計算できますが、1件しかない場合の結果は必ず0になります。「ばらつきがない」のではなく「ばらつきの計算対象が足りない」ケースもあるので、件数をDCOUNT関数で確認しておくと安心ですよ。

まとめ

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

ポイントを整理します。

  • 構文は =DVARP(データベース, フィールド, 条件) で、引数は3つすべて必須
  • 条件は数式内ではなく、セル範囲（条件範囲・クライテリア）に書くのが特徴
  • 条件範囲は「見出し行+条件行」のセットで作る
  • 同じ行に条件を横並びにすればAND条件、別の行にすればOR条件
  • 条件セルの値を変えるだけで分析対象を切り替えられる
  • VAR.P関数は全体の母分散、DVARP関数は条件付きの母分散
  • DVAR関数は標本前提でn-1で割るが、DVARP関数は母集団前提でnで割る
  • 母分散は標準偏差の2乗。レポートでは標準偏差、中間計算では分散が使われる
  • 条件に一致するレコードは1件でも計算できる（1件なら結果は0）
  • 結果がおかしいときはまず「対象レコード」と「見出しの不一致」をチェック

全社の売上の母分散比較や、ある期間の全製品をまるごと評価する品質チェックで威力を発揮する関数です。まずはサンプル表で =DVARP(A1:D8, "売上", F1:F2) から試してみてくださいね。

関連記事

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