Excelで複数の条件に合致するデータの合計を求める際、SUMIF関数だけでは限界を感じることはありませんか？部署、日付、商品コードなど、複雑な条件で絞り込んだ売上を集計したい。そんな時に役立つのがSUMIFS関数です。この記事では、経理部門や営業管理でExcelを15年使い込んできた筆者が、実務経験に基づいたSUMIFS関数の使い方を徹底解説します。SUMIFS 複数条件 合計のスキルを身につけて、集計作業を効率化しましょう。
SUMIFS関数で複数条件の合計をマスターしよう
SUMIFS関数とは？基本構文を理解する
SUMIFS関数は、複数の条件をすべて満たすセルの値を合計する関数です。基本構文は以下の通りです。
=SUMIFS(合計対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], ...)
- 合計対象範囲： 合計したい数値が入力されているセル範囲を指定します。
- 条件範囲1： 1つ目の条件を評価するセル範囲を指定します。
- 条件1： 1つ目の条件を、条件範囲1に適用する条件として指定します。
- 条件範囲2, 条件2, …： 2つ目以降の条件とその範囲を必要に応じて指定します。最大127組まで指定可能です。
Microsoft公式サイトでも、SUMIFS関数の詳細な構文や使用例が紹介されています。(https://support.microsoft.com/ja-jp/office/sumifs-%E9%96%A2%E6%95%B0-c9e748f5-7ea7-455d-9406-611cebce642b)
SUMIF関数との違い：複数条件の重要性
SUMIF関数も条件付きで合計を計算しますが、指定できる条件は1つだけです。一方、SUMIFS関数は複数の条件を指定できるため、より複雑な集計に対応できます。例えば、「営業部で、かつ商品Aの売上」のように、複数の条件を組み合わせた集計を行う場合に力を発揮します。
初心者がつまずきやすいポイント：範囲指定のミス
研修で教えていると、SUMIFS関数を使う際に、合計対象範囲と条件範囲の行数が一致していないためにエラーが発生するケースをよく見かけます。例えば、合計対象範囲がA1:A10なのに、条件範囲がB1:B9になっている、といった具合です。この場合、#VALUE!エラーが表示されます。範囲を正確に指定することが重要です。
売上データを活用したSUMIFS関数の実例
部署別・商品別の売上集計
営業部の売上データを例に、SUMIFS関数の具体的な使い方を見ていきましょう。以下の表のようなデータがあるとします。
| 日付 | 部署 | 商品コード | 売上金額 |
|---|---|---|---|
| 2024/01/01 | 営業部 | A-001 | 100,000 |
| 2024/01/01 | 経理部 | B-002 | 50,000 |
| 2024/01/02 | 営業部 | A-001 | 120,000 |
| 2024/01/02 | 営業部 | B-002 | 80,000 |
| 2024/01/03 | 経理部 | A-001 | 60,000 |
このデータから、「営業部」で「A-001」の売上金額を合計するには、以下の数式を使用します。
=SUMIFS(D2:D6, B2:B6, "営業部", C2:C6, "A-001")
日付範囲を指定した売上集計
次に、日付範囲を指定して売上を集計する方法を見てみましょう。「2024/01/01から2024/01/02」の期間における「営業部」の売上金額を合計するには、以下の数式を使用します。
=SUMIFS(D2:D6, B2:B6, "営業部", A2:A6, ">=2024/01/01", A2:A6, "<=2024/01/02")
経理の現場では、この日付設定を誤って集計期間がズレるケースをよく見かけます。日付の入力形式や、不等号の向きに注意しましょう。
ワイルドカードを使った部分一致検索
商品コードが完全に一致しない場合でも、ワイルドカードを使って部分一致検索を行うことができます。例えば、商品コードに「A」を含む商品の売上を合計するには、以下の数式を使用します。
=SUMIFS(D2:D6, C2:C6, "A", B2:B6, "営業部")
この数式では、商品コードに「A」が含まれているすべての商品の売上が合計されます。
勤怠管理への応用：SUMIFS関数で残業時間を集計する
部署ごとの残業時間集計
SUMIFS関数は、売上データだけでなく、勤怠管理にも応用できます。例えば、部署ごとの残業時間を集計することができます。
| 社員名 | 部署 | 残業時間 |
|---|---|---|
| 田中 | 営業部 | 2 |
| 佐藤 | 経理部 | 3 |
| 鈴木 | 営業部 | 1 |
| 高橋 | 経理部 | 4 |
このデータから、「営業部」の残業時間を合計するには、以下の数式を使用します。
=SUMIFS(C2:C5, B2:B5, "営業部")
特定の社員の残業時間集計
特定の社員の残業時間を集計することもできます。例えば、「田中」さんの残業時間を集計するには、以下の数式を使用します。
=SUMIFS(C2:C5, A2:A5, "田中")
複数条件での残業時間集計
部署と社員名を指定して残業時間を集計することも可能です。例えば、「営業部」の「田中」さんの残業時間を集計するには、以下の数式を使用します。
=SUMIFS(C2:C5, B2:B5, "営業部", A2:A5, "田中")
このように、SUMIFS関数を使うことで、様々な条件で残業時間を集計することができます。Microsoft 365では、さらに高度な集計や分析機能が利用可能です。 (Microsoft公式サイト)
SUMIFS関数をさらに使いこなすための応用テクニック
参照セルを使った柔軟な条件設定
条件を数式に直接入力するのではなく、セルを参照することで、条件を柔軟に変更することができます。例えば、E1セルに「営業部」と入力されている場合、数式は以下のようになります。
=SUMIFS(D2:D6, B2:B6, E1)
E1セルの値を変更するだけで、集計対象の部署を簡単に切り替えることができます。
エラー処理：#VALUE!エラーを回避する
SUMIFS関数で#VALUE!エラーが発生した場合、合計対象範囲と条件範囲のサイズが一致しているか確認してください。また、日付の形式が正しく入力されているか、数値として認識されているかなども確認が必要です。
SUMIFS 複数条件 合計 と他関数との組み合わせ
SUMIFS関数は、他の関数と組み合わせることで、さらに高度な集計を行うことができます。例えば、AVERAGEIFS関数と組み合わせることで、条件に合致するデータの平均値を計算することができます。
プロのコツ：SUMIFS関数をさらに効率的に使う
ショートカットキーを活用する
Excelの操作を効率化するために、ショートカットキーを活用しましょう。例えば、SUMIFS関数を入力する際に、数式バーで「=SUMIFS」と入力し、Tabキーを押すと、関数の引数が表示されます。また、範囲を選択する際に、Ctrl + Shift + 方向キーを使うと、連続したデータを一気に選択できます。
数式のコピー＆ペーストを使いこなす
SUMIFS関数を使った数式を他のセルにコピー＆ペーストする際に、参照セルがずれないように、絶対参照（$マーク）を活用しましょう。例えば、=SUMIFS(D$2:D$6, B$2:B$6, E1)のように、行番号を固定することで、数式をコピーしても参照範囲が変わりません。実務でよく見かけるのは、この絶対参照を忘れて、意図しない範囲を集計してしまうケースです。
テーブル機能を活用する (Excel 2019以降)
Excelのテーブル機能を使うと、SUMIFS関数の数式が自動的に調整され、データの追加や削除があっても、常に正しい集計結果が得られます。テーブルに名前を付けることで、数式がより分かりやすくなります。テーブル機能は、Microsoft公式サイトでも詳しく解説されています。 (https://learn.microsoft.com/ja-jp/office/client-developer/excel/excel-tables)
まとめ
この記事では、SUMIFS関数を使ったSUMIFS 複数条件 合計の方法について解説しました。要点をまとめると以下の通りです。
- SUMIFS関数は、複数の条件をすべて満たすセルの値を合計する関数である。
- SUMIF関数とは異なり、複数の条件を指定できる。
- 日付範囲やワイルドカードを使った部分一致検索も可能である。
- 他の関数と組み合わせることで、さらに高度な集計を行うことができる。
- ショートカットキーやテーブル機能を活用することで、作業効率を向上させることができる。
SUMIFS 複数条件 合計 をマスターして、日々の業務を効率化しましょう。
コメント