【Excel】SUMIFS関数で月別・部署別にクロス集計!Excelの複数条件合計を実務で使う方法

Excelで月別や部署別の売上を集計したい場合、SUMIFS関数が非常に役立ちます。複数の条件を指定して合計値を算出できるため、複雑な集計も効率的に行えます。SUMIFS関数を使いこなせば、データ分析の幅が格段に広がります。この記事では、SUMIFS関数を使った月別・部署別のクロス集計の方法を具体的に解説します。

SUMIFS関数は、複数の条件に合致するデータの合計を求める関数です。例えば、特定の月であり、かつ特定の部署の売上だけを合計するといった集計が可能です。この関数を理解することで、日々の業務におけるデータ集計作業を大幅に効率化できます。本記事を読むことで、SUMIFS関数を使ったクロス集計の具体的な手順と、実務で活用するためのポイントが理解できます。

SUMIFS関数で複数条件合計を実現する仕組み

SUMIFS関数は、指定された複数の条件をすべて満たすセルの値だけを合計します。例えば、A列が「2023年1月」であり、かつB列が「営業部」である行の、C列にある売上金額を合計するといった処理が可能です。この関数がなければ、条件に合うデータを一つずつ探し出して手作業で合計する必要があり、非常に手間がかかります。SUMIFS関数は、このような煩雑な作業を自動化し、集計ミスを防ぐのに役立ちます。Excel 2019以降のバージョンで利用可能です。

SUMIFS関数を使った月別・部署別クロス集計の手順

1. 元データの準備
   集計したいデータが、1行に1レコード(1つの取引や記録)となるように整理します。各列には、日付、部署名、売上金額などの項目名が付けられている必要があります。例えば、「日付」「部署」「売上」といった列構成にします。
2. クロス集計表の作成
   新規シートまたは既存シートに、集計結果を表示するための表を作成します。表の行方向には集計したい項目(例:月)、列方向にもう一つの集計したい項目(例:部署)を設定します。例えば、行に「1月」「2月」…と月を並べ、列に「営業部」「開発部」…と部署名を並べます。表の左上隅には、合計対象となるデータ範囲の項目名(例:「売上」)を記載すると分かりやすいです。
3. 条件範囲の準備
   クロス集計表で設定した月や部署名と、元データの日付や部署名が完全に一致するように準備します。日付については、元データが日付形式になっていることを確認してください。月別集計を行う場合、元データの日付から月を抽出するための工夫が必要になります。
4. SUMIFS関数の入力(月別・部署別売上集計)
   クロス集計表の、各月・各部署の売上合計を表示したいセル(例:A2セル)を選択します。ここにSUMIFS関数を入力します。数式は以下のようになります。

   =SUMIFS(合計対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2)

   今回の例では、「合計対象範囲」は元データの「売上」列、「条件範囲1」は元データの「日付」列、「条件1」はクロス集計表の行ヘッダーにある月、「条件範囲2」は元データの「部署」列、「条件2」はクロス集計表の列ヘッダーにある部署名になります。具体的には、以下のような数式になります。

   =SUMIFS(元データシート!$C:$C, 元データシート!$A:$A, "*"&A1&"*", 元データシート!$B:$B, B$1)

   この数式では、元データシートのC列(売上)を合計対象とし、A列(日付)がA1セル(例:1月)に該当し、B列(部署)がB1セル(例:営業部)に該当するデータを集計しています。「*」ワイルドカードは、日付の表記揺れに対応するために使用していますが、日付を正確に「2023/1/1」のような形式で条件指定する場合は不要です。月別集計のためにA1セル(例:「1月」)を条件とする場合、元データのA列(日付)を「MONTH(A列のセル)=1」のように変換して比較する必要があります。より正確な月別集計のためには、元データの日付列から月を抽出する作業が必要です。例えば、元データのA列が日付の場合、D列に `=MONTH(A2)` と入力して月を抽出し、このD列をSUMIFS関数の条件範囲1として指定します。クロス集計表の行ヘッダーには、抽出した月(例:1, 2, 3…)を入力しておきます。

5. 数式のコピー
   入力したSUMIFS関数を、クロス集計表の他のセルにもコピーします。コピーする際は、絶対参照($)や複合参照を適切に設定することが重要です。上記数式例の「元データシート!$C:$C」のように、合計対象範囲は絶対参照にします。条件範囲1(日付)も絶対参照(例:`元データシート!$A:$A`)にし、条件(A1)は行方向にコピーするので複合参照(`A$1`)にします。条件範囲2(部署)は絶対参照(例:`元データシート!$B:$B`)にし、条件(B1)は列方向にコピーするので複合参照(`$B1`)にします。数式をコピーしたいセルを選択し、セルの右下隅にあるフィルハンドルをダブルクリックするか、ドラッグしてコピーします。

SUMIFS関数でよくある入力ミスと対処法

日付の条件指定で期待通りの結果にならない

SUMIFS関数で月別集計を行う際、元データの日付とクロス集計表の月(例:「1月」)を直接比較しようとすると、意図した合計値にならないことがあります。これは、Excelが「1月」という文字列と実際の日付データを別物として認識するためです。対処法としては、元データの日付列から月を抽出する列を別途作成し、その抽出した月(数値)をクロス集計表の条件として使用する方法が確実です。

1. 元データに月抽出列を追加
   元データシートの空いている列(例:D列)に、日付から月を抽出する数式を入力します。例えば、元データの日付がA列にある場合、D2セルに `=MONTH(A2)` と入力し、D列全体にコピーします。
2. クロス集計表の行ヘッダーを数値にする
   クロス集計表の行ヘッダー(月)を、文字列の「1月」ではなく、数値の「1」とします。
3. SUMIFS関数の条件を変更
   SUMIFS関数の数式を、月抽出列(D列)を条件範囲1として参照するように変更します。数式は以下のようになります。

   =SUMIFS(元データシート!$C:$C, 元データシート!$D:$D, A$1, 元データシート!$B:$B, B$1)

   この場合、A$1にはクロス集計表の行ヘッダー(例:1)が、B$1には列ヘッダー(例:営業部)が入ります。

部署名などの文字列が一致しない

部署名や商品名など、文字列を条件にする場合、わずかな表記の違い(全角/半角、大文字/小文字、スペースの有無など)で条件が一致せず、合計値が0になってしまうことがあります。SUMIFS関数では、指定した条件と完全に一致するデータのみが対象となります。

1. 表記の統一
   元データとクロス集計表の条件となる文字列の表記を完全に統一します。可能であれば、元データの段階で表記揺れを修正しておくことが望ましいです。
2. ワイルドカードの使用(注意が必要)
   どうしても表記揺れが多い場合は、ワイルドカード(*)を使用することも考えられます。例えば、部署名が「営業部」と「営業部A」など、一部が一致するデータをまとめて集計したい場合、数式を以下のように変更します。

   =SUMIFS(元データシート!$C:$C, 元データシート!$B:$B, "営業部*", 元データシート!$A:$A, A$1)

   ただし、ワイルドカードは意図しないデータまで集計してしまう可能性があるため、使用には注意が必要です。

3. TRIM関数でのスペース除去
   文字列の前後に不要なスペースが含まれている場合は、TRIM関数を使って除去した列を元データに追加し、その列を条件として使用します。

   =TRIM(B2) (B2セルに部署名がある場合)

集計範囲や条件範囲の指定ミス

SUMIFS関数を入力する際、合計対象範囲や条件範囲の指定を間違えると、正しい結果が得られません。例えば、合計対象範囲と条件範囲の行数が一致していない場合や、範囲指定が意図した列になっていない場合などです。

1. 範囲の確認
   数式を入力したら、必ず指定した範囲が正しいかを確認します。数式バーで関数名をクリックすると、Excelが自動的にその範囲を色付けして表示してくれるため、視覚的に確認できます。
2. 行数の確認
   合計対象範囲と各条件範囲の行数が一致しているかを確認します。一致していない場合、Excelはエラーを返しませんが、計算結果が不正になります。
3. 絶対参照・複合参照の確認
   数式をコピーする際に、絶対参照($)や複合参照が正しく設定されていないと、コピー先のセルで範囲がずれてしまいます。数式をコピーする前に、フィルハンドルでドラッグする前に、数式バーで参照が意図した通りになっているか確認しましょう。

SUMIFS関数と他の集計方法の比較

項目	SUMIFS関数	ピボットテーブル	GETPIVOTDATA関数
概要	複数条件に合致するセルの合計を計算	ドラッグ&ドロップで直感的に集計表を作成	ピボットテーブルから特定のデータを取得
柔軟性	数式で柔軟な条件設定が可能	集計項目や条件の変更が容易	ピボットテーブルの構造に依存
学習コスト	関数の構文を理解する必要がある	基本操作は比較的容易	ピボットテーブルと関数構文の理解が必要
リアルタイム性	元データ変更時に自動で再計算	手動更新が必要な場合がある	ピボットテーブルの更新が必要
用途例	特定の条件に絞った集計、他の数式との組み合わせ	多角的なデータ分析、レポート作成	ピボットテーブルの特定の値を取り出したい場合

SUMIFS関数は、特定の条件に基づいた集計を数式で行いたい場合に非常に強力です。一方、ピボットテーブルは、より多角的にデータを分析したい場合や、集計表のレイアウトを頻繁に変更したい場合に適しています。GETPIVOTDATA関数は、既に作成したピボットテーブルから特定の値を参照したい場合に利用されます。それぞれの特性を理解し、目的に応じて使い分けることが重要です。

まとめ

SUMIFS関数を使いこなすことで、月別や部署別といった複数の条件に基づいたクロス集計をExcelで効率的に実行できます。元データの準備からクロス集計表の作成、そしてSUMIFS関数の適切な入力まで、本記事で解説した手順を踏むことで、誰でも精度の高い集計が可能になります。集計範囲や条件の指定ミス、表記揺れなどのよくある失敗例とその対処法を理解しておくことも、スムーズな集計作業には不可欠です。今後は、SUMIFS関数とVLOOKUP関数を組み合わせたり、Power Queryを活用してデータの前処理を自動化したりすることで、さらに高度なデータ分析に挑戦してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】