SUMIFS関数 -1つ以上の条件を同時に満たすデータの合計値を求める- DL可

以前の記事では、『【SUM関数】合計値を求める』を解説し、

SUM関数を拡張させた、
『【SUMIF関数】検索条件に一致したデータの合計値を求める』を解説しました。

本記事では、SUMIF関数の機能を拡張した「SUMIFS関数」を解説します。

追々詳しく取り上げますが、

SUMIF関数は・・・

「指定条件に一致するセルの値を合計します」で、

指定条件の数は、あくまで1つです。


指定条件の数を1つ以上にしたい場合に、SUMIFS関数の威力を発揮します。

例えば、男性20代の合計、女性20代の合計を別々に求めたい等です。

言わば、疑似ピボットテーブルです。

SUMIFS関数を使えば・・・

クロス集計表のように、

表側(ひょうそく)≒縦軸、表頭(ひょうとう)≒横軸を設定して集計が可能になります。

なお、クロス集計表を簡単に説明すると・・・

表側(ひょうそく)≒縦軸、表頭(ひょうとう)≒横軸を設定して集計した表です。

SUM関数とSUMIF関数とSUMIFS関数の違いは、

SUM関数→SUMIF関数→SUMIFS関数にかけて、上限数が増えているだけです。

いずれ、別記事で詳しく解説したいと考えています。

問題

サンプルデータから、「Date」「Product」に一致した「Sales」の合計値を求め、

「Date×Product別 Salesの合計値」を作成してみます。

いかがでしょうか。

まさに、ピボットテーブルからクロス集計表を作成したかのようです。

これをExcel関数で実現させます。

解答付きサンプルデータ

解答付きサンプルデータが欲しい方は、下記からダウンロードしてください。

解答

SUMIFS関数を使用します。
ピボットテーブルでも可能ですが、関数の方が連動性に優れています。
(ピボットテーブルは手動更新する必要があるが、関数は自動更新)

関数式

次の関数式を記述すると、「Date×Product別 Salesの合計値」が求まります。

=SUMIFS($J$2:$J$701,$M$2:$M$701,$R3&$S3,$C$2:$C$701,T$2)

解説

ざっくり解説

関数の構文は以下です。

=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2,...）

SUMIFS関数は１つ以上の条件を設定できるため、条件分を引数を設定できます。
条件範囲と条件で1セットになっているイメージです。

具体的に、本問題に当てはめると・・・

=SUMIFS(Sales,Date,表側1&表側2,Product,表頭）

では、実際のセル範囲を指定すると・・・

=SUMIFS($J$2:$J$701,$M$2:$M$701,$R3&$S3,$C$2:$C$701,T$2）

となります。

くわしく解説

解説を読む前に、巻末の参考記事もチェックしておくと良いため、時間があれば覗いてみてください。

SUMIFS関数とは

Excel標準機能の[関数の挿入]ダイアログ/[関数の引数]ダイアログを大体のことは書かれているので、
活用して詳しく解説します。

ダイアログの使い方は、巻末の参考記事を参照していただければと思います。

特定の条件に一致した数値の合計を求めます

[関数の挿入]ダイアログより

特定の表現が紛らわしいですが、１つ以上の条件という意味と考えて問題ないです。

SUMは「和」、IFは「条件」、Sは「複数形」ですから、その名の通りです。

複数形だがら、IF＋Ｓになっていそうですね。

SUMIFS関数の構文

次の関数式を記述すると、「Date×Product別 Salesの合計値」が求まります。

=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2,...）

条件範囲1を合致したデータに関して、
条件1の中で、合計対象範囲のデータから合計するいうことです。

1つ以上の条件を指定でき、
条件の数が増えるとそれに合わせて条件範囲と条件がペアを構成します。

合計対象範囲は常に1つであり、その合計範囲は変更されません。

具体的には、

条件範囲1と条件1が最初のペアを構成し、これに基づいてデータが集計されます。

さらに、条件の数が増えると、新しい条件範囲と条件が追加され、

それに基づいてデータが絞り込まれていきます。

例えば、

条件範囲1と条件1
→　条件範囲2と条件2
→　条件範囲3と条件3
→・・・・

といったように条件の組み合わせが増えます。

第1引数：合計対象範囲

合計対象範囲　には合計対象の実際のセルを指定します

[関数の挿入]ダイアログより

その名の通り、「合計対象範囲」を指します。

具体的なデータを合計する範囲を指定します。
つまり、何を合計したいのかを指定する部分です。

本問題では、Sales列の$J$2:$J$701 が合計対象範囲になります。

第2引数：条件範囲1

条件範囲1　には、特定の条件で値を求める対象となるセル範囲を指定します

条件範囲1は、その名の通り、条件の範囲を指します。

特定の条件でデータを抽出するための基準となるセル範囲を指定します。
この範囲内で条件に合致するデータが選ばれます。

本問題では、Date列の$M$2:$M$701 が条件範囲1になります。

第3引数：条件1

条件1　には計算の対象となるセルを定義する条件を数値、式、または、文字列で指定します

「条件1」には、その名の通り、条件を設定します。

「条件1」は、具体的な条件を指定する部分です。
これによって、条件範囲1のデータを条件1で絞り込みます。

本問題では、表側の$R3&$S3 が条件1になります。

第4引数：条件範囲2

先程の引数に関する説明でお伝えした通り、

「条件範囲2」は、2つ目の条件に関する基準範囲を指定します。
これと対応する条件が「条件2」として設定されます。

例えば、本問題では、Product列の$C$2:$C$701が「条件範囲2」になります。

第5引数：条件2

先程の引数に関する説明でお伝えした通り、

2つ目の具体的な条件を指定します。
これも同様にデータを絞り込むための基準です。

本問題では、Product列の$C$2:$C$701が「条件2」になります。

ちなみに、引数の説明をしましたが、
ダイアログボックスのそのままだったのではないでしょうか。

フォローアップ

外観上は数値のケース

関数では、”引数に文字列を設定してならない”と記載されています。

ただし、外観上は数値の場合には正常に動作します。

例えば、以下の2つのケースではどちらも「999」という値が数値に見えます。

しかし、2行目の「999」は書式が文字列です。

本来ならばルール違反であり、関数はエラーを返すべきですが、
実際には正常に動作します。

おそらく、裏で数値変換が行われているものと考えられます。

そのため、書式が文字列でも外観上数値であれば、関数は正常に動作するようです。

最後に

SUMIFS関数を紹介しました。

SUMIFS関数は、SUM関数を拡張させたSUMIF関数を拡張させた関数でした。

具体的に復習すると・・・

SUM関数に検索条件を追加し、拡張させたのが、SUMIF関数

さらに、

SUMIF関数に検索条件を1つ以上にできるようにし、拡張させたのが、SUMIFS関数

といった具合です。

1つ以上の条件の元で、合計を求めることができます。

SUMIFS関数を使えば、ピボットテーブルのように、合計値を求めることができます。

ご拝読ありがとうございました。

参考記事：

Excel関数を操作するための基本概念です。基本概念を理解すると、各関数の理解がより深まります。