今回は、ExcelのSUMIFS関数を使って、条件に合うデータだけを合計する表の作り方を紹介します。
SUMIFSでできること
Excelで売上表、経費一覧、在庫表、作業記録などを扱うとき、全体の合計だけでなく、条件に合う行だけを合計したい場面があります。部署ごとの金額、担当者ごとの件数、月別の費用、商品カテゴリごとの数量などです。SUMIFS関数を使うと、複数の条件を指定して、該当する行の数値だけを合計できます。
SUM関数は範囲全体を合計します。SUMIF関数は条件を一つ指定して合計します。SUMIFS関数は条件を複数指定できるため、実務の表で使いやすい関数です。集計表を別に作り、元データを直接加工しないようにすると、後から条件を変えたいときにも対応しやすくなります。
基本の考え方
SUMIFS関数は、合計する範囲と条件範囲、条件を組み合わせて使います。書き方は、合計対象の列を最初に指定し、その後に条件を指定します。条件が増える場合は、条件範囲と条件をセットで追加します。
- 合計対象範囲: 合計したい数値が入っている列
- 条件範囲: 判定に使う列
- 条件: 集計したい項目名や日付、数値の条件
たとえば、A列に日付、B列に担当者、C列にカテゴリ、D列に金額がある表で、担当者が「佐藤」、カテゴリが「交通費」の金額を合計したい場合、D列を合計対象にし、B列とC列を条件範囲にします。条件をセル参照にしておくと、集計表の見出しを変えるだけで結果を切り替えられます。
集計表を先に作る
SUMIFSを使う前に、どの切り口で合計したいかを決めます。元データの横に直接式を入れるより、別の場所に集計表を作るほうが見やすくなります。縦に担当者名、横にカテゴリ名を置くと、担当者別かつカテゴリ別の合計を確認できます。
集計表の見出しに条件を置き、数式ではその見出しセルを参照します。こうすると、同じ数式をコピーしても条件が変わります。固定したい範囲には絶対参照を使い、移動させたい条件セルは相対参照または複合参照にします。
- 元データの列名を確認する
- 集計したい条件を縦横の見出しに配置する
- 最初の集計セルにSUMIFSを入力する
- 範囲を固定して数式をコピーする
- 結果が元データの内容と合っているか確認する
絶対参照を使ってコピーしやすくする
SUMIFSの数式は、コピーして使うことが多い関数です。合計対象範囲や条件範囲がずれると、正しい結果になりません。そのため、元データの範囲は絶対参照にして固定します。たとえば、金額列を `$D:$D`、担当者列を `$B:$B` のように固定します。
一方、条件として使う集計表の見出しは、コピー方向に合わせて固定する部分を変えます。縦にコピーする担当者名は列を固定し、横にコピーするカテゴリ名は行を固定すると扱いやすくなります。これにより、一つの数式を表全体に広げても、条件が自然に切り替わります。
範囲は固定、条件は見出しを参照という考え方を持つと、SUMIFSの表は壊れにくくなります。数式を入力した後は、数式バーで参照先を確認し、コピー後に範囲がずれていないか確認します。
日付条件を扱うときの注意
月別や期間別に合計したい場合、日付条件を使います。日付は見た目が文字列に見えても、Excel内部では日付の値として扱われます。開始日以上、終了日未満という条件にすると、月末の日付や時刻が混ざったデータでも扱いやすくなります。
たとえば、ある月の合計を出したい場合は、開始日をその月の初日、終了条件を翌月の初日未満にします。条件には `”>=”&開始日セル`、`”<"&翌月初日セル` のように比較演算子とセル参照をつなげます。月名だけを文字として条件にするより、日付列をそのまま使うほうが元データを保ちやすくなります。
空白や表記ゆれへの対策
SUMIFSは条件と一致する行だけを合計します。そのため、担当者名やカテゴリ名に表記ゆれがあると、別の項目として扱われます。「営業部」と「営業 部」、「交通費」と「旅費交通費」のような違いがあると、合計が分かれます。集計前に入力規則や置換を使って表記をそろえると、結果を確認しやすくなります。
空白も注意点です。条件範囲に空白があると、意図した集計から漏れることがあります。未分類のデータを確認するために、空白を条件にした集計欄を作る方法もあります。空白が残っていることを見つけやすくなり、元データの整備につながります。
- カテゴリ名は一覧から選ぶ形にする
- 担当者名の姓だけ、フルネームなどのルールを決める
- 空白欄を集計対象として確認する
- コピーしたデータは余分なスペースを取り除く
テーブル機能と組み合わせる
元データをExcelのテーブルに変換しておくと、データが増えたときにSUMIFSの範囲を扱いやすくなります。テーブルでは列名を使って参照できるため、数式の意味も読み取りやすくなります。たとえば、金額列やカテゴリ列を名前で指定できるため、列の位置を見直すときにも理解しやすい数式になります。
テーブルを使う場合も、条件セルの参照は集計表側に置きます。元データは一覧として管理し、集計表は確認用として分けます。この形にしておくと、元データの行を追加しても集計表を作り直す手間が減ります。
結果を確認する方法
SUMIFSの集計結果は、元データをフィルターで絞り込んで確認すると安心です。条件に合う行だけを表示し、金額列の合計と集計表の値が合うか確認します。すべての条件を毎回確認する必要はありませんが、最初に数式を作ったときや条件を追加したときは、いくつかの結果を見比べるとミスに気づきやすくなります。
数式が長くなった場合は、条件を分けて確認します。担当者だけで合計した場合、カテゴリだけで合計した場合を一時的に作ると、どの条件で結果がずれているか判断しやすくなります。複数条件の集計ほど、確認の手順を持っておくことが大切です。
集計条件を増やすときの整理
条件を増やすほど、SUMIFSの式は長くなります。無理に一つの表へ詰め込むより、集計したい目的ごとに表を分けると見やすくなります。担当者別、カテゴリ別、月別を同時に見たい場合でも、まずは主要な条件を決め、補助的な条件は別表で確認する方法があります。
条件セルには、元データと同じ表記を使います。手入力で見出しを作ると表記ゆれが入りやすいため、元データの一覧からコピーするか、入力規則で選べる形にすると安定します。集計表の見出しが整っていると、数式の確認もしやすくなります。
まとめ
ExcelのSUMIFS関数は、複数の条件に合う行だけを合計できるため、業務の集計表で使いやすい関数です。元データはそのまま残し、別の場所に集計表を作ると管理しやすくなります。範囲は絶対参照で固定し、条件は見出しセルを参照すると、数式をコピーして使いやすくなります。日付条件、空白、表記ゆれに注意し、テーブル機能やフィルター確認と組み合わせることで、条件別合計を扱いやすい形に整えられます。