条件集計(複数条件)

条件集計で、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="鈴木"))}
と表記した方が理解しやすいかも知れません。



 ※R1C1参照形式の場合は

[月を除く合計]
{=SUM((R3C4:R38C4)*(R3C2:R38C2=R2C)*(R3C3:R38C3=RC6))}
[科目を除く合計]
{=SUM((R3C4:R38C4)*(R3C1:R38C1=R2C)*(R3C3:R38C3=RC6))}
となります。

SUMPRODUCT関数

 SUMPRODUCT関数のサンプル

 このページでは先に配列数式とそれに関わる複数条件集計の原理を説明してしまったので、ここで登場する「SUMPRODUCT関数」は配列数式の「置き換え手段」のようになってしまいます。 現にSUMPRODUCT関数の引数となるカッコ内を見ると、配列数式と全く同じなのです。



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



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



 ※R1C1参照形式の場合は

[月を除く合計]
=SUMPRODUCT((R3C4:R38C4)*(R3C2:R38C2=R2C)*(R3C3:R38C3=RC6))
[科目を除く合計]
=SUMPRODUCT((R3C4:R38C4)*(R3C1:R38C1=R2C)*(R3C3:R38C3=RC6))
となります。

SUMIFS関数

 SUMIFS関数のサンプル

 「SUMIFS関数」はExcel2007から追加された関数ですから、現在有効なExcelのバージョンであれば利用可能です。
複数条件集計に主眼を絞って複数の引数を配置しており、それぞれの引数の役割が理解できればこのページのこれまでの説明を理解しなくても利用できると思います。



 引数は先頭が「集計(合計対象)範囲」、２番目以降が「条件範囲」と「条件」を２つずつ指定する方法です。最大127件まで指定できるそうです。
「条件」はこのサンプルのように値だけの場合は完全一致となりますが、手前に大小判定や不等号式を配置させたり、ワイルドカードも利用できます。
画像の式での「G$2」は実際の値は「数学」、「$F3」は「鈴木」ですから、単一セルの説明上では

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



 ※R1C1参照形式の場合は

[月を除く合計]
=SUMIFS(R3C4:R38C4,R3C2:R38C2,R2C,R3C3:R38C3,RC6)
[科目を除く合計]
=SUMIFS(R3C4:R38C4,R3C1:R38C1,R2C,R3C3:R38C3,RC6)
となります。



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

AVERAGEIFS関数

 AVERAGEIFS関数のサンプル

 ここから先は「○○IFS関数」となってSUMIFS関数からの応用です。引数配置も同じです。
SUMIFS関数は合計でしたが、AVERAGEIFS関数は平均値の算出になります。
但し、AVERAGEIFS関数の場合は、合計値を個数で割る計算が入るため「#DIV0!(ゼロ除算エラー)」が起こりえますのでIFERROR関数をかぶせて利用するようにします。



 ※R1C1参照形式の場合は

[月を除く平均]
=IFERROR(AVERAGEIFS(R3C4:R38C4,R3C2:R38C2,R2C,R3C3:R38C3,RC6),0)
[科目を除く平均]
=IFERROR(AVERAGEIFS(R3C4:R38C4,R3C1:R38C1,R2C,R3C3:R38C3,RC6),0)
となります。

MAXIFS関数

 MAXIFS関数のサンプル

 引数の説明は「SUMIFS関数」と同じですが、「MAXIFS関数」は最高値(最大値)を算出します。
「MAXIFS関数」はExcel2019(Excel2016 ProPlus)で追加された関数ですから、以前のバージョンが混在する可能性がある環境では注意が必要です。



 ※R1C1参照形式の場合は

[月を除く最高値]
=MAXIFS(R3C4:R38C4,R3C2:R38C2,R2C,R3C3:R38C3,RC6)
[科目を除く最高値]
=MAXIFS(R3C4:R38C4,R3C1:R38C1,R2C,R3C3:R38C3,RC6)
となります。

MINIFS関数

 MINIFS関数のサンプル

 引数の説明は「SUMIFS関数」と同じですが、「MINIFS関数」は最低値(最小値)を算出します。
「MINIFS関数」はExcel2019(Excel2016 ProPlus)で追加された関数ですから、以前のバージョンが混在する可能性がある環境では注意が必要です。



 ※R1C1参照形式の場合は

[月を除く最低値]
=MINIFS(R3C4:R38C4,R3C2:R38C2,R2C,R3C3:R38C3,RC6)
[科目を除く最低値]
=MINIFS(R3C4:R38C4,R3C1:R38C1,R2C,R3C3:R38C3,RC6)
となります。