日々の業務で売上や契約金額データなどから対象のデータのみ金額や数量を集計するシーンは良くあるかと思います。
単純な集計であればSum関数を使えば集計できますし、ある条件を一つ満たすデータのみを集計する際はSumif関数で対応ができます。
さて、より集計の条件が複雑になった時はどうでしょうか。
例えば、条件Aと条件Bには合致していて、条件Cには合致しないデータを集計するといった場合などです。
今回は複数条件での集計業務に対応するSumifs関数について、基本的な使い方から応用編まで詳しく解説していきます。
Sumifs関数の書式
Sumifs関数の書式は以下のとおりです。
=SUMIFS(合計対象範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)
| 引数名 | 説明 |
|---|---|
| 合計対象範囲 (必須) | 合計するセルの範囲を指定します。 |
| 条件範囲 1 (必須) | 検索したい条件が含まれている範囲を指定します。 |
| 条件 1 (必須) | 検索したい条件を指定します。 条件は、32、">32"、B4、"リンゴ"、または "32" のように入力することができます。 |
| 条件範囲 2, 条件 2, ... (任意) | 追加の範囲と対応する条件。 条件は最大127個まで指定することができます。 |
合計対象範囲に数値として入力されているもののみ集計されます。
文字列などがある際はエラーにはならずスルーされ数値のみ集計されますが、「#NAME?」や「#VALUE」などのエラー値があると計算結果もエラーとなるので注意が必要です。
Sumif関数の基本的な使い方
それでは実際に使用してみます。
サンプルデータとして日付、期、グループ、金額のデータを用意しました。
「上期」かつ「グループB」の合計金額を集計していきます。
まず合計対象範囲は金額が記載されている範囲「E3:E21」を指定します。
次に条件1の「期」を検索する対象範囲「C3:C21」を指定します。
条件1で「上期」を指定したいので「”上期”」とダブルクォーテーションで囲み検索する文字列を指定します。
最後に条件2の「グループ」を検索する対象範囲「D3:D21」を指定し、条件2で「B」を指定したいので「”B”」と同じく検索する文字列を指定します。
合計対象範囲と各検索対象範囲の行数は一致させてください。(上記例では3～21行目となっています)
55,561と計算結果が表示されました。
Sumif関数の応用編
分析データなどを作成するときはこのやり方を多用しますので、こちらも合わせて抑えていきましょう。
応用として「文字列を含む」という条件と日付や数値で「〇以上、かつ、△以下」という範囲指定条件について解説していきます。
まずは文字列についてです。
条件指定の際にワイルドカード「*」を使用します。
基本的に「●●を含む」「●●から始まる」「●●で終わる」の3パターンを押さえておくと良いでしょう。
「●●を含む」といった条件の場合
検索条件に「”*●●*”」のように記載します。
図の例では検索条件２に「"*cp*"」と記載しており、前後に何があっても「cpを含む製品コード」を２つ目の検索条件に指定しています。
このように検索したいワードをワイルドカード（*）で挟んで前後あいまい検索することがポイントです。
「●●から始まる」という条件の場合
条件に「”●●*”」のように記載します。先頭文字が決まっているので、後方のみワイルドカードを記載するのがポイントです。
図の例では「CT*」と記載しており、後方あいまい検索していくことで集計できます。
「●●で終わる」という条件の場合
条件に「”*●●”」のように記載します。語尾が決まっているので前方にワイルドカードを記載するのがポイントです。
図の例では「"*_c"」と記載し、前方あいまい検索で集計しています。
上級者編 日付や数値で範囲を指定して集計する方法
日付で期間指定をして集計するといったパターンはよくあるのでどのように設定するか解説していきます。
ワイルドカードを使ったあいまい検索よりも式が長くなってきますが、覚えると使い勝手は非常に良いです。
以下の例では、2021/04/01～2021/09/30の期間の金額を集計しています。
まず、合計対象範囲はこれまで同様に「E3:E21」を指定します。
続いて条件1と2に2021/04/01以降、2021/09/30までといった条件を指定していきます。
条件対象範囲は日付の列を指定し、検索対象の指定は「”>=2021/04/01”」、「”<=2021/09/30”」とします。
文字列内に「>=」や「<=」といった不等号を入れて指定日と結合するところがポイントです。
数値の上限・下限設定をして集計する際も同様に不等号を使用します。
Sumifの検索条件でセルを参照する
ここまでは検索条件をダブルクォーテーション「””」で囲んで直接入力していましたが、セル参照とすることもできます。
図のように集計元のデータ(B2:E21)と、集計先の表(H3:I6)があり、製品グループ別に集計をしていきます。
合計対象範囲はこれまでと同様ですが、条件指定1の部分で期間は「上期」としたいので、I2セルを選択しています。
このようにセルを参照することで、下期を集計したい場合はI2セルの中身を「下期」に変更するだけで集計してくれます。
条件指定2の部分では、表の製品グループの文字列「AT」～「CT」を含むで指定したいので、セル指定した後にアンパサンド「＆」でワイルドカード「”*”」を結合しています。
「AT」の金額集計の関数が組み終わったら、「BT」「CT」の行に関数をコピペして集計データを完成させたいところですが、絶対参照「$」を付けないとコピペした際に集計対象範囲と条件範囲がズレてしまい正しく集計できないので注意が必要です。
Sumif関数のまとめ
今回は複数条件での集計業務に対応するSumifs関数について、基本的な使い方から応用編まで詳しく解説しました。
SumやSumifでは対応しきれない集計もSumifs関数を駆使すれば可能です。
様々な条件指定の方法を知っておくと、いざ集計関数をくみ上げるときにどうやったら求めるデータが算出できるか工夫できるようになってくるかと思います。
ぜひマスターして、業務効率化を図っていきましょう。
最後までご覧いただきありがとうございました。
情シスの自由帳は、情シスマンが半径3m以内のITに関する情報を掲載してるサイトです。
Windows系を主として、ソフトや周辺機器について思い立った物を色々解説しています。
操作環境
Windows11 Home
Microsoft Office 2019
エクセルに関するその他の記事
エクセルに関するその他の記事はこちらです。ぜひご覧ください。
