「かつ」や「または」の条件を自由に指定できる関数DSUM、DAVERAGE

前回の連載では、複数の条件を「または」で結んで平均を求める方法を紹介した。とはいえ、少し強引な解決法であったかもしれない。複数の条件を指定して「合計」や「平均」を求めるときは、xxxIFS系の関数ではなく、Dxxx系の関数を使用するのも効果的だ。ということで、今回は「関数DSUM」と「関数DAVERAGE」の使い方を紹介していこう。

関数DSUMや関数DAVERAGEを使うための準備

前回の連載で紹介したAVERAGEIFSのようにxxxIFS系の関数は、それぞれの条件を「かつ」で結ぶ仕様になっている。このため、複数の条件を「または」で結ぶには何らかの工夫が必要となり、少し使いづらい面もある。

このような場合に活用したいのが、今回の連載で紹介するDSUMやDAVERAGEといった関数だ。DSUMやDAVERAGEを使うと、複数の条件を「かつ」や「または」で自由に連結して合計や平均を算出することが可能となる。

• 

  条件を柔軟に指定できる関数、DSUM（合計）とDAVERAGE（平均）の使い方

今回も、ある店舗の売上をまとめた表を使って具体的な使い方を紹介していこう。この表には全部で30日分（11/1～11/30）のデータが記録されており、ワークシートの10行目に「フィールド名」、11～40行目に「データ」が入力されている。

• 

  日別の売り上げをまとめた表

それでは、関数DSUMと関数DAVERAGEの使い方を紹介していこう。それぞれの関数は、以下のような書式で記述する仕様になっている。

■関数DSUMの書式
　=DSUM(データの範囲, 合計する列, 条件の範囲)

■関数DAVERAGEの書式
　=DAVERAGE(データの範囲, 平均する列, 条件の範囲)

第1引数には「データ表のセル範囲」を見出し（フィールド名）を含めた形で指定する。続いて、第2引数に「合計や平均を算出する列」を指定する。最後に、第3引数として「条件を記したセル範囲」を指定する。

文章による説明だけでは理解しにくいと思うので、簡単な例を示していこう。DSUMやDAVERAGEを使用するときは、あらかじめ「条件を指定する表」を作成しておく必要がある。具体的には、データ表と同じ「フィールド名」を記した表を数行ほど用意しておけばよい。なお、今回の例では「売上」を条件に指定することはないので、「日付」～「P倍率」（ポイント倍率）のフィールド名を記した空白の表を作成した。

• 

  条件を指定するための表を作成

これで準備は完了。あとは書式に従って関数を記述していくだけだ。「条件付きの合計」を求める関数DSUMの場合、以下のように関数を記述すればよい。

• 

  関数DSUMの入力

念のため、補足しておこう。第1引数となる「データの範囲」には、データ表の範囲であるB10:G40を指定する（この表は40行目までデータが入力されている）。

続いて、「合計する列」を指定する。今回は「売上」の数値について「条件付きの合計」を求めたいので、そのフィールド名である「合計」を「"」（ダブルクォーテーション）で囲んで記述する。なお、「合計する列」を数値で指定することも可能となっている。この場合は、「データ表の何列目になるか？」を数値で記述すればよい。今回の例ではデータ表の6列目に「売上」があるので、単に「6」と記述しても構わない。

最後に、「条件の範囲」を指定する。こちらは先ほど作成した条件表の範囲をフィールド名を含むように指定すればよい。今回の例ではB3:F8となる。

同様の手順で「条件付きの平均」を求める関数DAVERAGEを入力すると、以下の図のようになる。

• 

  関数DAVERAGEの入力

関数の入力が済むと、それぞれのセルに「合計」と「平均」の計算結果が表示されるのを確認できるはずだ。

• 

  関数DSUMと関数DAVERAGEの計算結果

現時点では何も条件を指定していないので、この計算結果は「全データの売上」の合計ならびに平均となる。大雑把にその数値を紹介しておくと、売上の合計は約550万円、平均は約18万4,500円となる。

条件を入力するときの注意点

続いては、今回の本題である「条件」の指定方法を紹介していこう。まずは、平日についてのみ「売上」の合計と平均を算出する場合だ。

今回のデータ表には「平日」と「休日」を区別する列（D列）も用意されているが、単に条件を「平日」と指定するだけでは複数条件にならないので、「曜日」の列を使って条件を指定した例を紹介していこう。

平日になる曜日は「月」～「金」の5種類ある。これらを条件表に記していくと、平日についてのみ「売上」の合計と平均を算出できる。

• 

  平日になる条件の入力と計算結果

このように各条件を「縦」に入力した場合は、それぞれの条件が「または」で結ばれる仕組みになっている。上図の例では、曜日が「月」または「火」または「水」または「木」または「金」、という条件になる。

同様の手順で、休日についてのみ「売上」の合計と平均を求めてみよう。休日になる曜日は「土」、「日」、「祝」の3種類なので、以下のように条件を記述すればよい、と思うかもしれない。

• 

  休日になる条件の入力と計算結果（失敗例）

しかし、これは正しくない条件の指定方法となる。というのも、関数DSUMやDAVERAGEの第3引数には「条件の範囲」としてB3:F8が指定されているからだ。つまり、8行目までが条件として認識されることになる。

上図の例では、条件表の7行目と8行目が「空白」になっている。この場合、これらのセルは「条件なし」とみなされる。先ほど紹介したように、条件を「縦」に入力すると、それぞれの条件は「または」で結ばれる。つまり、上図の条件指定は、曜日が「土」または「日」または「祝」または「条件なし」または「条件なし」となる。

または「条件なし」ということは、結局のところ「何も条件を指定していない」のと同じことになる。よって、全データを対象に合計と平均が算出される。このことは、合計が約550万円、平均が約18万4,500円と算出されていることからも確認できるだろう。

では、どうすればよいのか？　このような場合は、関数DSUMや関数DAVERAGEの第3引数（条件の範囲）を指定し直すのが基本だ。とはいえ、いちいち関数を修正するのは面倒なので、別の対策法を覚えておくとよい。それは「絶対にありえない条件」を追加することだ。

たとえば、以下のように条件を指定すると、曜日が「土」または「日」または「祝」または「★」または「★」、という条件になる。

• 

  休日になる条件の入力と計算結果（成功例）

データ表を見るとわかるように「曜日」が「★」のデータは1件も存在しないので、条件表の7行目と8行目は「絶対に合致しない条件」となる。その結果、曜日が「土」または「日」または「祝」だけが有効な条件になり、休日についてのみ「売上」の合計と平均を求めることが可能となる。

このように「絶対に合致しない条件」を追加して、未使用の条件（行）を無効化する方法もある。第3引数を修正する手間を省くテクニックとして、覚えておくと役に立つだろう。

「かつ」と「または」を自由に指定した条件入力

続いては、関数DSUMと関数DAVERAGEの条件を色々と変化させた例を紹介していこう。Dxxx系の関数は、条件表の記述を書き換えるだけで自由自在に条件を変化させられるのが特長の一つとなる。

たとえば、「最高気温が20℃以上」の日についてのみ合計と平均を算出したい場合は、以下の図のように条件表を書き換えればよい。なお、「★」の記述は、以降の条件行（5～8行目）を無効化する役割を担っている。この仕組みは先ほど説明した通りだ。

• 

  「最高気温20℃以上」の条件を指定した場合

計算結果をみると、平均は約19万2,000円で、全体の平均（約18万4,500円）より少しだけ高くなっている。やはり暖かい日は人出が多いのかもしれない。

さらに条件を追加することも可能だ。たとえば、条件表に「平日」と記述すると、「平日」かつ「最高気温が20℃以上」の日についてのみ合計と平均を算出できる。

• 

  「平日」かつ「最高気温20℃以上」の条件を指定した場合

上記のように、それぞれの条件を「横」に並べて記述した場合は、各条件が「かつ」で結ばれる仕組みになっている。

今度は、「P倍率」（ポイント倍率）を条件にして計算した例を紹介しておこう。たとえば、「ポイント3倍以上」の日についてのみ合計と平均を算出したい場合は、条件表を以下のように書き換えればよい。

• 

  「ポイント3倍以上」の条件を指定した場合

この計算結果は、平均が約19万4,500円という結果になった。こちらも全体の平均（約18万4,500円）より高い数値になっている。よって「ポイント倍率アップ」のキャンペーンは効果がある、と推測できるかもしれない。

ただし、「平日」に限定すると異なる側面も見えてくる。たとえば、以下のように条件を書き加えると「平日」かつ「ポイント3倍以上」の日についてのみ合計と平均を算出できる。

• 

  「平日」かつ「ポイント3倍以上」の条件を指定した場合

こちらは、平均が約17万9,600円という結果になった。この数値は全体の平均（約18万4,500円）より低い数値であり、平日の平均（約17万7,600円）と比べても大差のない数値になっている。と考えると、「ポイント倍率アップ」のキャンペーンは、それほど効果がないのかも・・・、と推測することもできる。

そのほか、以下の図のように条件表を記述して、より複雑な条件を指定することも可能だ。この場合は、「平日で20℃以上」または「平日でポイント3倍以上」という条件を指定したことになる。

• 

  「平日で20℃以上」または「平日で3倍以上」の条件を指定した場合

このように、条件を次々と変化させながら多角的にデータを分析できるのが関数DSUMや関数DAVERAGEの利点となる。

Dxxx系の関数を使うときのポイントは、条件を縦に記述すると「または」、横に記述すると「かつ」になると覚えておくこと。この点を踏まえておけば、自由自在に条件を指定できるはずだ。

Dxxx系の関数は、xxxIFS系と比べて少しマイナーな関数になるが、「手軽に条件を変更できて、見た目に分かりやすい」という点では、xxxIFS系よりも使い勝手のよい関数といえるだろう。

日付を条件にした合計や平均の計算

最後に、少し応用的な条件指定について紹介しておこう。関数DSUMや関数DAVERAGEは「日付」の期間を限定する条件指定にも対応している。たとえば、以下の図のように条件を記述すると、「11/15以前」についてのみ合計と平均を算出できる。

• 

  「11/15以前」の条件を指定した場合

では、「11/6～11/12の1週間」を条件に指定したい場合はどうであろうか？　この場合、以下のように条件表を記述する方もいるだろう。しかし、これは間違った記述方法となる。

• 

  「11/6～11/12」を条件に指定した場合（失敗例）

前述したように、「縦」に並べて記述した条件は「または」で結ばれる仕組みになっている。よって、上図は「11/6以降」または「11/12以前」という条件になる。

全データは11/1～11/30の30日分あるので、先ほどの条件は「11/6～11/30」または「11/1～11/12」とも考えられる。これらを統合すると「11/1～11/30」という条件になり、全データが計算の対象になってしまう。つまり、この条件指定は間違っていることになる。

このような場合は「日付」を条件にする列を2つ作成すると、正しく条件を指定できる。具体的には、C3セルのフィールド名を「日付」に変更して、以下のように条件表を記述すればよい。

• 

  「11/6～11/12」を条件に指定した場合（成功例）

すると、各条件が「横」に並ぶので、「11/6以降」かつ「11/12以前」という条件になる。つまり、「11/6～11/12」だけを条件に指定できたことになる。このように、同じフィールド名を繰り返すことで、「かつ」の条件を指定する方法もある。Dxxx系の関数を自由自在に活用するテクニックとして、こちらも覚えておくと、いずれ役に立つだろう。