今回は、ExcelのSUMIFS関数を使って、複数条件の集計を行う方法を紹介します。
SUMIFS関数が役立つ場面
Excelで売上表、経費一覧、作業実績、在庫管理などを扱うとき、「担当者がAさんで、区分が交通費の合計」「地域が東日本で、商品分類が備品の合計」のように、複数の条件で合計したい場面があります。そのようなときに使いやすいのがSUMIFS関数です。
SUMIFS関数は、条件に合う行だけを選び、その行の数値を合計します。フィルターで絞り込んで手作業で確認する方法もありますが、条件を変えながら集計したい場合は関数にしておく方が扱いやすくなります。定期的に更新する表や、複数の集計欄を作る表に向いています。
SUMIFS関数の基本形
SUMIFS関数では、最初に合計する範囲を指定し、その後に条件範囲と条件を組み合わせて指定します。
基本の考え方
合計対象が金額列、条件が部署列と月列なら、「金額を合計する」「部署が指定した値と一致する」「月が指定した値と一致する」という順で考えます。式を作る前に、どの列を合計し、どの列で判定するのかを整理しておくと間違いにくくなります。
範囲の大きさをそろえる
SUMIFS関数では、合計範囲と条件範囲の行数をそろえる必要があります。金額列だけ100行、部署列だけ120行のように範囲がずれると、意図した結果になりません。表全体をテーブル化しておくと、行の追加にも対応しやすくなります。
条件セルを使って集計を切り替える
条件を数式の中に直接書くこともできますが、集計表では条件セルを参照する形が便利です。たとえば、B2セルに部署名、C2セルに月を入力し、その値をSUMIFS関数の条件に使います。
条件セルを使うと、部署名や月を変えるだけで結果を切り替えられます。複数の部署を横に並べたり、月を縦に並べたりすると、一覧形式の集計表を作れます。後から条件を変更するたびに数式を書き換えなくてよいため、更新しやすい表になります。
文字条件を扱うコツ
部署名、担当者名、分類名などの文字条件は、表記ゆれに注意します。「営業部」と「営業」、全角と半角、余分な空白が混ざると、同じ意味でも別の値として扱われます。
文字条件で集計する表では、入力規則やドロップダウンリストを使い、入力値をそろえておくとよいでしょう。また、元データに不要なスペースが入りやすい場合は、データを整えてから集計します。SUMIFS関数は便利ですが、元データが乱れていると結果も分かりにくくなります。
日付条件を扱うコツ
日付を条件にする場合は、「指定日と一致」だけでなく、「開始日以降」「終了日以前」のように範囲で指定することがよくあります。月別集計では、月初以上、翌月初未満という形にすると扱いやすくなります。
日付が文字列として入力されていると、条件に合わないことがあります。見た目は日付でも、Excel上で日付として認識されているか確認します。日付条件を使う集計表では、日付列の形式をそろえ、入力時点で崩れないようにしておくことが大切です。
比較条件を使う
SUMIFS関数では、「以上」「以下」「より大きい」などの比較条件も使えます。たとえば、金額が一定以上のもの、期限が特定日以前のもの、数量が0より大きいものだけを合計できます。
比較条件をセル参照と組み合わせるときは、記号とセル参照をつなげて指定します。条件を直接式に埋め込むより、条件値をセルに置く方が見直しやすくなります。集計条件を表の上部にまとめておくと、他の人が見ても何を集計しているか分かりやすくなります。
よくあるミスを防ぐ
SUMIFS関数で結果が合わないときは、数式だけでなく元データも確認します。特に次の点は見落としやすい部分です。
- 合計範囲と条件範囲の行数がずれている
- 条件セルに余分な空白が入っている
- 日付が文字列になっている
- 数値が文字列として保存されている
- 条件の列を間違えている
- 非表示行も集計対象に含まれている
フィルターで同じ条件を試し、対象行が想定どおりか確認すると原因を見つけやすくなります。
集計表を見やすくする
SUMIFS関数で結果を出したら、集計表の見せ方も整えます。条件名、合計値、対象期間、単位を分かるようにすると、後で見返したときに判断しやすくなります。
見出しには「部署別合計」「月別経費」「担当者別売上」のように、集計の軸が分かる名前を付けます。金額には通貨や桁区切りを設定し、数量なら単位を添えます。結果だけでなく、どの条件で出した数字なのかが分かる表にすることが大切です。
元データを追加するときの注意
SUMIFS関数を使う集計表では、元データを追加したときに数式の範囲へ含まれるかを確認します。通常のセル範囲を参照している場合、行を追加しても集計範囲が広がらないことがあります。継続してデータを追加する表では、元データをテーブル化しておくと管理しやすくなります。
テーブル化しておくと、行を追加したときに参照範囲が自動で広がりやすく、列名を使った数式にもできます。数式を見たときに、どの列を合計しているか、どの列を条件にしているかが分かりやすくなります。月次で更新する集計表では、元データの追加方法もルール化しておくとよいでしょう。
確認用の小計を作る
集計結果が正しいか不安な場合は、確認用の小計を作ります。たとえば、条件を付けない全体合計と、部署別の合計を比べると、集計漏れの有無を確認しやすくなります。分類別や月別に分けた結果を合算して、元データ全体の合計と一致するかを見る方法もあります。
確認用の行は、提出資料では非表示にするか、作業用シートに分けます。SUMIFS関数は条件を増やせる分、列の指定間違いや条件の表記違いに気づきにくいことがあります。集計表を作ったら、結果を信じる前に確認用の視点を用意しておくことが大切です。
まとめ
ExcelのSUMIFS関数は、複数条件に合うデータを合計するための関数です。部署、担当者、日付、分類などを組み合わせて集計できるため、管理表や報告用の集計に役立ちます。
使うときは、合計範囲と条件範囲をそろえ、条件セルを活用し、元データの表記ゆれや日付形式を確認します。SUMIFS関数を使いこなすと、条件を変えながら集計できる扱いやすいExcel表を作れます。