以前の記事では、『SUM関数：使い方と解説』を解説し、
SUM関数を拡張させた『【SUMIF関数】検索条件に一致したデータの合計値を求める』を解説しました。
本記事では、SUMIF関数の機能を拡張した「SUMIFS関数」を解説します。
SUMIF関数は「指定条件に一致するセルの値を合計します」でした。
ただ、SUMIF関数は指定条件の数は、あくまで1つです。
指定条件の数を1つ以上にしたい場合に、SUMIFS関数の威力を発揮します。
例えば、男性20代の合計、女性20代の合計を別々に求めたい等です。
言わば、疑似ピボットテーブルです。
SUMIFS関数を使えば・・・
クロス集計表のように、表側(ひょうそく)≒縦軸、表頭(ひょうとう)≒横軸を設定して集計が可能になります。
クロス集計表とは、詳しく解説しませんが下記のイメージです。
SUM関数とSUMIF関数とSUMIFS関数の違いは、下記のイメージです。
SUM関数→SUMIF関数→SUMIFS関数にかけて、より複雑になるが、機能は増えるイメージです。
いずれ、別記事で詳しく解説したいと考えています。
Contents
記事の読み方
説明の順は以下です。
まずは、「問題」を与えて、
次に、「解答」を提示します。
最後に、「解説」です。
よくある、結論→理由の構成順に沿いました。
先に「解答」を知りたい人を考慮したためです。
「解法」の説明は不要で、とりあえずビジネス現場で使えればいいと思う方もいるためです。
また、ある程度の経験者であれば、
「問題」と「解答」だけで、関数を使いこなせてしまうためです。
そのために、即利用できるように、ダウンロードファイルを用意していますので、
活用してみてください。
（経験者であれば、ダウンロードファイルだけで、実務に応用できてしまうのではないでしょうか）
問題
サンプルデータから、「Date」「Product」に一致した「Sales」の合計値を求め、
「Date×Product別 Salesの合計値」を作成してみます。
サンプルデータは、財務サンプルの Excel ブックを一部改変したものですので、ダウンロードして活用ください。
解答付きです。
いかがでしょうか。
まさに、ピボットテーブルからクロス集計表を作成したかのようです。
これをExcel関数で実現させます。
解答
SUMIFS関数を使用します。
ピボットテーブルでも可能ですが、関数の方が連動性に優れています。
(ピボットテーブルは更新する必要がある)
関数式
次の関数式を記述すると、Dateごとの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月」となっているため、「&」で文字列を結合をして、年&月で指定しています。
完全一致をしないと、検索にヒットしないためです。
$マークによる絶対参照/複合参照/相対参照を正しく設定してください。
分からない方は、下記記事ををご覧いただければと思います。
くわしく解説
SUMIF関数とは
記事『Excel関数の検索テクニック』で説明した通り、Excel標準機能の[関数の挿入]ダイアログ/[関数の引数]ダイアログを大体のことは書かれているので、活用して詳しく解説します。
ダイアログの使い方は、上記記事を参照していただければと思います。
特定の条件に一致した数値の合計を求めます
[関数の挿入]ダイアログより
特定の表現が紛らわしいですが、１つ以上の条件という意味と考えて問題ないです。
SUMは「和」、IFは「条件」、Sは「複数形」ですから、その名の通りです。
複数形だがら、IF＋Ｓになっていそうですね。
Excel関数名が分からない場合は日本語訳してみると、発見できるかもしれません。
SUMIF関数の構文
=SUMIFS(合計対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ...）条件範囲1を合致したデータに関して、条件1の中で、合計対象範囲のデータから合計するいうことです。
SUMIFS関数は1つ以上の条件を設定できるため、条件分を引数を設定できます。
条件範囲と条件で1セットになっているイメージです。
条件範囲1と条件1 → 条件範囲2と条件2 → 条件範囲3と条件3 のように、条件を1つ以上することができます。
第1引数：合計対象範囲
合計対象範囲 には合計対象の実際のセルを指定します
[関数の挿入]ダイアログより
その名の通り、「合計対象範囲」を指します。
本問題では、Sales列の$J$2:$J$701 が合計対象範囲になります。
第2引数：条件範囲1
条件範囲1 には、特定の条件で値を求める対象となるセル範囲を指定します
上記に補足すると、
条件範囲1は、検索条件の範囲を指します。
本問題では、Date列の$M$2:$M$701 が条件範囲1になります。
第3引数：条件1
条件1 には計算の対象となるセルを定義する条件を数値、式、または、文字列で指定します
「条件1」には、検索条件を設定します。
本問題では、表側の$R3&$S3 が条件1になります。
第4引数：条件範囲2
『条件範囲と条件で1セットになっているイメージです。】とお伝えしたように、
1つ以上の条件がある場合は、第4引数も設定しています。
本問題では、Product列の$C$2:$C$701 が条件範囲2になります。
第3引数：条件2
『条件範囲と条件で1セットになっているイメージです。】とお伝えしたように、
1つ以上の条件がある場合は、第4引数も設定しています。
「条件2」には、検索条件を設定するため、
本問題では、Product列の$C$2:$C$701 が条件2になります。
引数を入力しみてる
ダイアログボックスに沿って、引数を入力しみてます。
基本的に、直接入力よりも、使いやすく、間違いに気づきやすいです。
ただし、引数オプションを確認する場合などは直接入力も有効です。
引数オプションとは、Excel関数の引数の中で、予め用意されており、関数の特定の振る舞いや設定を制御できます。
そのため、状況に応じての使い分けが最も有効です。
参考記事は末尾に記載されており、全て拝読いただきたいのですが、特に関連のある記事は次の通りです。
小話：外観上は数値のケース
SUM関数では、引数に文字列を設定してならないとルールがあります。
ただし、外観上は数値の場合には正常に動作します。
例えば、以下の2つのケースではどちらも「999」という値が数値に見えます。
しかし、2行目の「999」は書式が文字列です。
本来ならばルール違反であり、SUM関数はエラーを返すべきですが、
実際には正常に動作します。
おそらく、裏で数値変換が行われているものと考えられます。
そのため、書式が文字列でも外観上数値であれば、SUM関数は正常に動作するようです。
最後に
SUMIFS関数を紹介しました。
SUMIFS関数は、SUM関数を拡張させたSUMIF関数を拡張させた関数でした。
具体的に復習すると・・・
SUM関数に検索条件を追加し、拡張させたのが、SUMIF関数
さらに、
SUMIF関数に検索条件を1つ以上にできるようにし、拡張させたのが、SUMIFS関数
といった具合です。
1つ以上の条件の元で、合計を求めることができます。
SUMIFS関数を使えば、ピボットテーブルのように、合計値を求めることができます。
ご拝読ありがとうございました。
参考記事
Excel関数自体の原理原則に関する記事です。こちらを参照すると、個別の関数の理解がさらに深まるでしょう。
