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関数で実現させます。
サンプルデータ
オリジナルデータ
今回利用するサンプルデータは、Microsoft社の財務サンプルの Excel ブックを一部改変したものです。
下記はオリジナルのデータです。
”Power BI 用の Excel の財務サンプル ブックのダウンロード – Power BI | Microsoft Learn” .https://learn.microsoft.com/ja-jp/power-bi/create-reports/sample-financial-download, (2024/1/15)
解答付きサンプルデータ
解答付きサンプルデータが欲しい方は、下記からダウンロードしてください。
解答
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）となります。
年&月 の「＆」の意味：
「Date」の表記が「2014年1月」となっているため、「&」で文字列を結合をして、年&月で指定しています。
完全一致をしないと、検索にヒットしないためです。
くわしく解説
解説を読む前に、巻末の参考記事もチェックしておくと良いため、時間があれば覗いてみてください。
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関数自体の原理原則に関する記事です。こちらを参照すると、個別の関数の理解がさらに深まるでしょう。
構文に関する記事
Excel関数の引数と戻り値は何だっけ？という時に、役立つ記事です。
Excel関数にはそれぞれ構文がある
Excel関数には構文があります。それが、引数と戻り値です。構文に沿えばいいだけです。言わば、数学の公式のようなものです。理論を知らなくても、公式に当てはめれば答え…
Excel関数の引数とは
Excelの引数は、料理を完成させるための材料のようなものです。しかも、材料に種類があります。その種類を理解すると、Excel関数を上手く使いこなせるようになります。
ダイアログボックスの使い方に関する記事
Excel関数名は何だっけ？VLOOKUP関数の使い方は何だっけ？という時に、ダイアログボックスを使うと便利です。
下記はそのダイアログボックスに関する記事です。
Excelの検索ダイアログから、関数を探す
Excelの検索ダイアログに使えば、目的の関数を発見しやすくなります。WEB検索する前に、Excel関数の検索ダイアログを使ってみましょう。
参照形式に関する記事
関数式をコピーする時はどうしたらいい？
$マークは、どう付けたらいいだろう？
といった悩みをお持ちの方は以下をご覧ください。
