DAVERAGEとDGETで平均と抽出を行う

このレッスンでは、最後に残った2つのデータベース関数を扱います。

• DAVERAGE - 一致するレコードについて、数値フィールドの平均を求めます。
• DGET - 条件に一致する1行から、単一の値を取得します。

どちらも、すでに学んだ(database, field, criteria)というパターンを再利用します。そのため、新しい構文ではなく、どのような値を返すかを主に学ぶことになります。

DAVERAGE(database, field, criteria)は、条件範囲に一致する行について、フィールド列の平均値を求めます。

• database - ヘッダーを含む完全なテーブルです。
• field - 平均を求める数値列です。
• criteria - 一致条件を指定する範囲です。

セルから条件を読み取る点を除けば、AVERAGEIFSに似ています。

A1:C13に、Region、Rep、Amountというヘッダーを持つ売上データがあるとします。E1にRegion、E2にEastを入力します。

この数式は、Eastの行にあるAmountの平均を返します。Eastの注文金額が1200、800、1000の場合、平均は1000です。E2をWestに変更すると、平均も自動的に更新されます。

DAVERAGEでは、条件に関する機能をすべて利用できます。Eastの注文のうち高額なものだけの平均を求めるには、2列の条件範囲を作成します。ヘッダーにRegionとAmountを入力し、その下にEastと>1000を入力します。

同じ行の条件はANDなので、この関数はEastにあり、かつ1000を超える行の金額を平均します。空白のフィールドは無視され、数値だけが平均の対象になります。

0を返すDSUMとは異なり、DAVERAGEは一致する行がない場合に#DIV/0!エラーを返します。0個の値の平均は求められないためです。

先に件数を確認して対処するか、IFERRORで囲んで、エラーコードの代わりに分かりやすいメッセージを表示してください。

DGETは少し異なり、条件に一致する1つの行のフィールドから、単一の値を返します。

ルックアップのように使用します。たとえばOrder IDのような一意のキーがある場合、DGETはその正確なレコードから1つのフィールドを取得します。完全に1件だけ一致することを求める点が、DGETの強みであり、注意点でもあります。

テーブルに一意の担当者が登録されていて、担当者「Sara」のAmountを取得したいとします。E1にRep、E2にSaraを入力します。

DGETはSaraの1行を見つけ、そのAmountを返します。一度に複数の条件列で一致させられるため、DGETは通常のVLOOKUPでは扱えない複合キーによるルックアップにも対応できます。

DGETは、ちょうど1行に一致することを厳密に求めます。

• 一致する行がない場合は、#VALUE!を返します。
• 複数の行に一致する場合は、#NUM!を返します。

これらのエラーは実際には役立ちます。キーが存在しない、または一意でないことを知らせてくれるためです。ちょうど1件のレコードだけが条件を満たすように、条件を絞り込んでください。

一致する行を1つに限定するには、条件を追加します。East地域にいるSaraのAmountを取得したい場合は、2列の条件範囲を使用します。ヘッダーにRepとRegionを入力し、その下にSaraとEastを入力します。

AND条件によって結果が1行に絞り込まれ、DGETはそのAmountを返します。これが、DGETをすっきりした複合キーのルックアップとして使う方法です。

DGETは一致する行が0件または複数件の場合にエラーになるため、より扱いやすい表示になるように結果を囲みます。IFERRORを使うと、どちらのエラーも読みやすいメッセージに変えられます。

重複と未検出を異なる形で示したい場合は、まずDCOUNTAで件数を確認し、その結果に応じてメッセージを分岐できます。

適切な関数を選ぶための簡単な指針を示します。

• 一致する行の合計が必要ですか。DSUMを使います。
• 一致する行の件数が必要ですか。DCOUNTまたはDCOUNTAを使います。
• 一致する行の平均が必要ですか。DAVERAGEを使います。
• 一致する1行から1つの値が必要ですか。DGETを使います。

4つの関数はすべて同じ条件範囲を使えるため、同じ条件セルの組み合わせで、合計、件数、平均、ルックアップを行うレポートを作成できます。

DGETの条件がテーブル内の3行に一致しています。DGETは何を返しますか。

D関数ファミリーの学習を終えました。

• DAVERAGEは一致する行の数値フィールドの平均を求めます。一致する行がない場合は#DIV/0!になります。
• DGETは、ちょうど1行に一致した場合に、その行から1つの値を返します。一致する行が0件の場合は#VALUE!、複数件の場合は#NUM!になります。
• どちらも(database, field, criteria)というパターンと、条件範囲におけるAND/ORのルールを共有しています。
• IFERRORで囲むと、すっきりしたプロフェッショナルな出力にできます。

DSUMとDCOUNTも合わせることで、構造化されたテーブルから、条件に基づく完全なレポートを作成できるようになりました。