条件集計(複数条件)

条件集計で、SUMIF関数までは使える方も多いと思います。しかし、条件判断が複数列のAND条件になるとお手上げになってしまいます。これを解決するのが、配列数式やSUMPRODUCT関数やSUMIFS関数です。
複数条件判断の元は「配列数式」のようです．．．．   「配列数式」についてズバリと的確に説明することはできませんが、複数条件判断の例として取り上げてみます。 「SUMPRODUCT関数」や「SUMIFS関数」も原理は「配列数式」であって、これを使いやすくしたものだと思います。
マイクロソフトのサポートにも「配列数式のガイドラインと例」というページがあるはずなので、検索してみて下さい。



 なお、説明の都合上、このページの説明はすべて「AND条件」での説明になりますのでその前提をご理解の上でご覧下さい。



 「配列数式」「SUMPRODUCT関数」「SUMIFS関数」は下記画像の通り算出結果は同じです。 原理的な説明は無視して手早く新しい算出方法へ進みたい方は「SUMIFS関数」に進んで下さい。
複数条件判断の原理
ここでの説明は複数条件判断の実際の処置についてです。



 「SUMIF関数」なら解るという方は、下記画像のサンプルの左「デ－タ明細」で

 科目(B列)が「数学」の点数(D列)を合計する
という式は組み立てられると思います。



 ここで複数条件判断となると

 科目(B列)が「数学」でかつ名前(C列)が「鈴木」の点数(D列)を合計する
となるわけです。



 「SUMIFS関数」が無かった世代の「配列数式」や「SUMPRODUCT関数」ではこの条件判断の部分の結果を「1」「0」にしてしまうという方法を採りました。
つまり、画像の選択セル(G3)についてであれば

 科目(B列)については「数学」なら検査値は「1」そうでなければ「0」、
 名前(C列)については「鈴木」なら検査値は「1」そうでなければ「0」
とするわけです。計算式上の判断では正が「1」、誤が「0」となるのでこれを利用しています。



 下記画像のサンプルの左「デ－タ明細」のそれぞれの行について

 点数 × 科目の検査値 × 名前の検査値
を算出して合計すれば良いわけです。検査値はここでは２種類ですが、掛け算ですから条件が何種類になっても全て「1」でなければ合計に加算されないわけです。 下記の「配列数式」や「SUMPRODUCT関数」のカッコ内に掛け算の「*」があるのはそのためです。

配列数式

 配列数式のサンプル
(画像をクリックすると、このページのサンプルがダウンロードできます)
セルに登録された「配列数式」はこのように中カッコ{}に囲われて表示されます。 但し、この中カッコ{}は直接入力するものではなく、内部の数式を入力してCtrl＋Shift＋Enterを押すことで配列数式として登録されるものです。
中カッコ{}の内部は「SUM関数」ですが引数が上で説明したように「点数 × 科目の検査値 × 名前の検査値」となっていて、複数条件集計が実現されます。



 「配列数式」はExcelのかなり古いバージョンから搭載されていたもので、ここではいまさらな説明になってしまいますが、新しい関数の「原理」としてご理解下さい。 このページの説明のレベルでは以降の関数が理解できれば必ずしも「配列数式」の理解は必要なものではなくなってきていると思います。



 但し「SUM関数」のカッコ内が「何でこういう式になるのか」はこの前の「複数条件判断の原理」を理解する必要があります。
画像の式での「G$2」は実際の値は「数学」、「$F3」は「鈴木」ですから、単一セルの説明上では

{=SUM(($D$3:$D$38)*($B$3:$B$38="数学")*($C$3:$C$38="鈴木"))}
と表記した方が理解しやすいかも知れません。

SUMPRODUCT関数

 SUMPRODUCT関数のサンプル

 このページでは先に「配列数式」とそれに関わる複数条件集計の原理を説明してしまったので、ここで登場する「SUMPRODUCT関数」は「配列数式」の「置き換え手段」のようになってしまいます。 現に「SUMPRODUCT関数」の引数となるカッコ内を見ると、「配列数式」と全く同じなのです。
関数概略説明
 SUMPRODUCT関数  対応する範囲または配列の積の合計を返します。 既定の演算は乗算ですが、加算、減算、除算も可能です。
 引数は1組以上の配列引数です。



このように、何やら解りにくい説明なのですが、「配列数式」の「置き換え手段」だということから入れば理解しやすいのではないでしょうか。
このページの利用方法であれば「各行の条件計算を行なった上で条件に合った行を集計する」というような説明になると思います。



 1からこの式を作成するのであれば、「複数条件判断の原理」や「配列数式」の理解が必要になります。

SUMIFS関数

 SUMIFS関数のサンプル

 「SUMIFS関数」は、複数条件集計に主眼を絞って複数の引数を配置しており、それぞれの引数の役割が理解できればこのページのこれまでの説明を理解しなくても利用できます。
関数概略説明
 SUMIFS関数  複数の検索条件に一致するすべての引数を合計します。
 引数は第1引数で算出セル範囲を指定し、以降は次の①、②を1組として1組以上で最大127組を指定できます。
 ① 条件セル範囲
 ② 条件(値や条件式：大小判定や不等号式、ワイルドカード可)



画像の式での「G$2」は実際の値は「数学」、「$F3」は「鈴木」ですから、単一セルの説明上では

=SUMIFS($D$3:$D$38,$B$3:$B$38,"数学",$C$3:$C$38,"鈴木")
と表記した方が理解しやすいかも知れません。



 「集計(合計対象)範囲」がなく、複数条件での件数だけを集計する場合は「COUNTIFS関数」が利用できます。

AVERAGEIFS関数

 AVERAGEIFS関数のサンプル

 ここから先は「○○IFS関数」となって「SUMIFS関数」からの応用です。
関数概略説明
 AVERAGEIFS関数  複数の検索条件に一致するすべてのセルの平均値 (算術平均) を返します。
 引数は第1引数で算出セル範囲を指定し、以降は次の①、②を1組として1組以上で最大127組を指定できます。
 ① 条件セル範囲
 ② 条件(値や条件式：大小判定や不等号式、ワイルドカード可)



複数条件での平均値の算出には、「AVERAGEIFS関数」を使います。
但し、「AVERAGEIFS関数」の場合は、合計値を個数で割る計算が入るため「#DIV0!(ゼロ除算エラー)」が起こりえますのでIFERROR関数をかぶせて利用するようにします。

MAXIFS関数

 MAXIFS関数のサンプル
関数概略説明
 MAXIFS関数  複数の検索条件に一致するすべてのセルから最大値を返します。
 引数は第1引数で算出セル範囲を指定し、以降は次の①、②を1組として1組以上で最大127組を指定できます。
 ① 条件セル範囲
 ② 条件(値や条件式：大小判定や不等号式、ワイルドカード可)



複数条件での最高値(最大値)の算出には、「MAXIFS関数」を使います。
「MAXIFS関数」はExcel2019(Excel2016 ProPlus)で追加された関数ですから、以前のバージョンが混在する可能性がある環境では注意が必要です。

MINIFS関数

 MINIFS関数のサンプル
関数概略説明
 MINIFS関数  複数の検索条件に一致するすべてのセルから最小値を返します。
 引数は第1引数で算出セル範囲を指定し、以降は次の①、②を1組として1組以上で最大127組を指定できます。
 ① 条件セル範囲
 ② 条件(値や条件式：大小判定や不等号式、ワイルドカード可)



複数条件での最低値(最小値)の算出には、「MINIFS関数」を使います。
「MINIFS関数」はExcel2019(Excel2016 ProPlus)で追加された関数ですから、以前のバージョンが混在する可能性がある環境では注意が必要です。