SUMIF関数の構文 -検索条件に一致したデータの合計値を求める-
えくせるサイトウ
記事『【SUM関数】合計値を求める』では、Excel関数の最も使用頻度の高い「SUM関数」を紹介しました。
本日は、SUM関数の機能を拡張した「SUMIF関数」を紹介します。
SUM関数を使っていると、条件ごとに合計を求めたいというシーンがあります。
例えば、男性の合計、女性の合計を別々に求めたい等です。
以下の手順で説明してますので適宜、読み飛ばしください。
「例題」→「解答」→「構文解説」です。
問題
「Date」ごとに、「Sales」の合計値を求めてみます。
下図のイメージです。
サンプルデータ
オリジナルデータ
今回利用するサンプルデータは、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)
解答付きサンプルデータ
解答付きサンプルデータが欲しい方は、下記からダウンロードしてください。
解答
SUMIF関数を使用します。
SUMIFS関数でも解答を得ることができます。
なお、SUM関数でも、参照範囲を適宜変更すれば、Dateごとの合計値は求まります。
ただ、大変ですし、間違いやすいですので、お勧めしません。
関数式
次の関数式を記述すると、DateごとのSales合計値が求まります。
=SUMIF($M$2:$M$701,$R3&$S3,$J$2:$J$701）解説
ざっくり解説
関数の構文は以下です。
=SUMIF(範囲,検索条件,合計範囲）関数の構文に本問題に当てはめると・・・
=SUMIF(「Date」,年&月,「Sales」）では、実際のセル範囲を指定すると・・・
=SUMIF($M$2:$M$701,$R3&$S3,$J$2:$J$701）となります。
各引数の参照範囲がどのデータに対応しているかは、下図でご確認ください。
年&月 の「＆」の意味：
「Date」の表記が「2014年1月」となっているため、「&」で文字列を結合をして、年&月で指定しています。
完全一致をしないと、検索にヒットしないためです。
くわしく解説
解説を読む前に、巻末の参考記事もチェックしておくと良いため、時間があれば覗いてみてください。
SUMIF関数とは
Excel標準機能の[関数の挿入]ダイアログ/[関数の引数]ダイアログを大体のことは書かれているので、
活用して詳しく解説します。
ダイアログの使い方は、巻末の参考記事を参照していただければと思います。
指定した検索策条件に一致するセルの値を合計します
[関数の挿入]ダイアログより
SUMは「和」、IFは「条件」ですから、その名の通りです。
SUMにIFという機能を追加した関数となっているわけです。
SUMIF関数の構文
=SUMIF(範囲,検索条件,合計範囲）範囲から、検索条件を合致したデータに関して、合計範囲のデータから合計するということです。
第1引数：範囲
範囲 には値を求める対象となるセル範囲を指定します
[関数の引数]ダイアログより
上記に補足すると、
範囲は、「検索条件」の範囲を指します。
本問題では、Date列の$R3&$S3 が範囲になります。
第2引数：検索条件
検索条件 には計算の対象となるセルを定義する条件を数値、式、または、文字列で指定します
[関数の挿入]ダイアログより
その名の通り、「検索条件」を指します。
本問題では、Date列の$M$2:$M$701 が検索条件になります
第3引数：合計範囲
合計範囲 には実際に計算の対象となるセル範囲を指定します。合計範囲を省略すると、範囲内で検索条件を満たすセルが合計されます
[関数の挿入]ダイアログより
その名の通り、「合計範囲」を指します。
本問題では、Sales列の$J$2:$J$701 が合計範囲になります。
各引数の入力結果
ダイアログボックスに沿って、引数を入力します。
引数の入力結果や数式の結果まで表示されます。
ダイアログボックスの良い所です。
フォローアップ
外観上は数値のケース
関数では、”引数に文字列を設定してならない”と記載されています。
ただし、外観上は数値の場合には正常に動作します。
例えば、以下の2つのケースではどちらも「999」という値が数値に見えます。
しかし、2行目の「999」は書式が文字列です。
本来ならばルール違反であり、関数はエラーを返すべきですが、
実際には正常に動作します。
おそらく、裏で数値変換が行われているものと考えられます。
そのため、書式が文字列でも外観上数値であれば、関数は正常に動作するようです。
最後に
SUMIF関数を紹介しました。
SUMIF関数は、SUM関数に検索条件という機能を拡張した関数でした。
条件ごとに合計を求めることができます。
SUMIF関数を使えば、ピボットテーブルのように、合計値を求めることができます。
ご拝読ありがとうございました。
—————————————
参考記事
—————————————
原理原則に関する記事
Excel関数自体の原理原則に関する記事です。こちらを参照すると、個別の関数の理解がさらに深まるでしょう。
構文に関する記事
Excel関数の引数と戻り値は何だっけ？という時に、役立つ記事です。
Excel関数にはそれぞれ構文がある
Excel関数には構文があります。それが、引数と戻り値です。構文に沿えばいいだけです。言わば、数学の公式のようなものです。理論を知らなくても、公式に当てはめれば答え…
Excel関数の引数とは
Excelの引数は、料理を完成させるための材料のようなものです。しかも、材料に種類があります。その種類を理解すると、Excel関数を上手く使いこなせるようになります。
ダイアログボックスの使い方に関する記事
Excel関数名は何だっけ？VLOOKUP関数の使い方は何だっけ？という時に、ダイアログボックスを使うと便利です。
下記はそのダイアログボックスに関する記事です。
Excelの検索ダイアログから、関数を探す
Excelの検索ダイアログに使えば、目的の関数を発見しやすくなります。WEB検索する前に、Excel関数の検索ダイアログを使ってみましょう。
参照形式に関する記事
関数式をコピーする時はどうしたらいい？
$マークは、どう付けたらいいだろう？
といった悩みをお持ちの方は以下をご覧ください。
