【Excel】エクセルで重複データをSUMIFで合計する方法（項目ごとの集計）
Excelの一覧表では、同じ商品名、担当者名、部門名などが複数行に現れることがあります。
そのままでは項目ごとの合計額が分かりにくいため、重複データを条件にして数値を合計できるSUMIF関数が役立ちます。
この記事では、1行目に見出しがある表を例に、重複する項目ごとに売上や数量を集計するSUMIF関数の使い方を詳しく解説します。
基本の数式は、=SUMIF(条件範囲,条件,合計範囲)です。
条件範囲に項目名の列、条件に集計したい項目名、合計範囲に金額や数量の列を指定します。
同じ項目が何度入力されていても、SUMIF関数なら必要な合計を自動で求められます。
数式をコピーするときの参照方法や、空白・表記ゆれで合計できない場合の確認方法も押さえていきましょう。
SUMIF関数で重複データを項目別に合計する方法
それではまず、重複した項目をSUMIF関数で合計する基本操作について解説していきます。
| 商品名 | 売上金額 | 集計商品 | 合計金額 |
|---|---|---|---|
| りんご | 120 | りんご | 370 |
| みかん | 200 | みかん | 290 |
| りんご | 250 | ぶどう | 180 |
| ぶどう | 180 | ||
| みかん | 90 |
集計用の項目一覧の準備
元データのA列を商品名、B列を売上金額とし、A1に商品名、B1に売上金額という見出しが入っている前提です。
まず、集計結果を置くD列に集計商品、E列に合計金額という見出しを入力します。
D2以降には、りんご、みかん、ぶどうのように、合計を確認したい項目名を1回ずつ入力します。
元データ側に同じ商品名が何行あっても、集計用の一覧では同じ項目を繰り返し入力する必要はありません。
項目数が多い場合は、元データの項目列をコピーしてから、Excelの重複の削除機能を利用して一覧を作る方法も便利です。
ただし、元データを直接加工したくないときは、集計用の別列や別シートで作業するのが安心でしょう。
項目名の文字は、元データの表記と一致させます。
たとえば「りんご」と「リンゴ」、「営業部」と「営業 部」のように表記が違うと、SUMIF関数では別の条件として扱われる点に注意が必要です。
【操作のポイント】集計用の項目一覧には、元データと同じ表記の項目を重複なしで入力します。
SUMIF関数の数式入力
次に、最初の合計欄であるE2セルを選択します。
E2セルには、次の数式を入力します。
=SUMIF($A$2:$A$6,D2,$B$2:$B$6)
この数式では、A2からA6が条件範囲、D2が条件、B2からB6が合計範囲です。
つまり、A列の商品名がD2セルの「りんご」と一致する行を探し、その行のB列の金額をすべて加算します。
結果として、120と250が合計され、E2には370と表示されます。
SUMIF関数は、条件に一致した行だけを合計範囲から取り出して計算する関数です。
条件範囲と合計範囲は同じ行数にそろえることが重要です。
A2:A6に対してB2:B6を指定するように、開始行と終了行を対応させると、意図した集計結果になります。
【操作のポイント】条件範囲は項目名の列、合計範囲は金額や数量などを足したい列に指定します。
オートフィルによる数式コピー
E2セルで正しい合計が表示されたら、セル右下の小さな四角であるフィルハンドルを下方向へドラッグします。
これにより、E3やE4にもSUMIF関数がコピーされ、D列の項目に応じた合計が自動計算されます。
数式内のA2:A6とB2:B6には$記号を付けているため、下へコピーしても集計対象の範囲は動きません。
一方で、条件であるD2には$記号を付けていないため、コピー先ではD3、D4へと自然に変化します。
範囲だけを固定し、条件セルは行ごとに変化させることが、項目別集計を素早く行うコツです。
金額欄に通貨表示を設定すると、集計表がさらに読みやすくなります。
数式を下方向へコピーすると、E3は=SUMIF($A$2:$A$6,D3,$B$2:$B$6)、E4は=SUMIF($A$2:$A$6,D4,$B$2:$B$6)となります。
【操作のポイント】F4キーで絶対参照にすると、コピー時に集計範囲がずれるのを防げます。
SUMIF関数の引数と絶対参照の仕組み
続いては、SUMIF関数の引数と、コピー時に欠かせないセル参照について確認していきます。
| 引数 | 指定内容 | 今回の例 |
|---|---|---|
| range | 条件を調べる範囲 | $A$2:$A$6 |
| criteria | 探す条件 | D2 |
| sum_range | 合計する範囲 | $B$2:$B$6 |
条件範囲と条件の指定
SUMIF関数の最初の引数は、どのセルを見て条件判定をするかを決める条件範囲です。
商品名別に合計する場合は商品名のA列、担当者別に合計する場合は担当者名の列を指定します。
2番目の引数は条件であり、通常は集計表に入力した項目名のセルを指定します。
セル参照を使うと、項目名を書き換えるだけで結果が再計算されるため、数式を作り直す必要がありません。
条件を文字列として直接入力するより、セルを参照するほうが一覧集計では扱いやすい方法です。
条件に「りんご」と直接書く場合は、=SUMIF($A$2:$A$6,”りんご”,$B$2:$B$6)のように、文字列を二重引用符で囲みます。
【操作のポイント】条件は集計表のセルを指定すると、項目ごとの数式コピーに対応しやすくなります。
絶対参照と相対参照の使い分け
セル参照には、コピー先に合わせて変化する相対参照と、固定される絶対参照があります。
$A$2:$A$6のように列記号と行番号の両方に$を付けると、どこへコピーしても同じ範囲を参照します。
SUMIFで元データの範囲が変化すると、集計結果がずれたり、一部の行が合計対象から外れたりします。
そのため、条件範囲と合計範囲は基本的に絶対参照にします。
対してD2は、D3、D4へ変わってほしい条件セルなので相対参照のままにします。
固定する範囲は$A$2:$A$6と$B$2:$B$6です。
コピーごとに変える条件はD2です。
数式のコピー後に同じ結果ばかり表示される場合は、条件セルまで固定していないかを確認しましょう。
【操作のポイント】数式入力中に参照範囲を選択し、F4キーを押すと絶対参照へ切り替えられます。
合計範囲を省略する場合
SUMIF関数では、合計範囲を省略することもできます。
ただし、省略した場合は条件範囲そのものに入力されている数値が合計されます。
たとえばA列に支店コードではなく数値があり、その数値を条件で合計する特殊なケースでは利用できます。
商品名の列を条件範囲にして売上金額を加算する一般的な表では、合計範囲の指定が必要です。
省略すると文字列の入ったA列を合計しようとするため、期待した売上合計にはなりません。
項目列と金額列が別の場合は、第3引数の合計範囲を必ず指定すると覚えておくとよいでしょう。
【操作のポイント】売上、数量、原価など、合計したい数値がある列を最後の引数に指定します。
複数条件の集計とSUMIFS関数の使い分け
続いては、商品名に加えて月や担当者も条件にする場合の集計方法を確認していきます。
| 月 | 担当者 | 商品名 | 売上金額 |
|---|---|---|---|
| 4月 | 田中 | りんご | 120 |
| 4月 | 佐藤 | りんご | 250 |
| 5月 | 田中 | りんご | 80 |
SUMIFS関数への切り替え
商品名だけではなく、月や担当者など複数の条件をすべて満たすデータを合計したい場合はSUMIFS関数を使います。
SUMIF関数は条件を1つ指定する関数ですが、SUMIFS関数は条件を2つ以上追加できます。
=SUMIFS($D$2:$D$4,$A$2:$A$4,G2,$C$2:$C$4,H2)
この例では、D列の売上金額を合計し、A列の月がG2、C列の商品名がH2に一致する行だけを対象にします。
SUMIFS関数は合計範囲を最初に指定するため、SUMIF関数とは引数の順番が異なります。
数式を入力するときは、範囲と条件の組み合わせを順番に指定しましょう。
【操作のポイント】条件が2つ以上ならSUMIFS関数を選び、合計範囲を最初に入力します。
Excel画面での数式入力操作
ここでは、SUMIFS関数を入力してオートフィルする画面のイメージを確認します。
数式バーに数式を入力したら、Enterキーで確定します。
結果セルの右下にあるフィルハンドルを下へ引くと、月や商品名の条件を行ごとに変えながら計算できます。
条件範囲と合計範囲は固定し、集計条件のセルだけを変化させる設定がここでも基本です。
【操作のポイント】数式バーで参照先を確認してから確定すると、列の選択ミスを減らせます。
日付条件と数値条件の指定
SUMIFS関数では、日付や数値を条件にして集計することも可能です。
たとえば100000円以上の売上だけを集計するなら、条件には”>=100000″を指定します。
=SUMIF($B$2:$B$100,”>=100000″,$B$2:$B$100)
セルに入力した基準値を使うなら、比較演算子とセル参照を&でつなげます。
=SUMIF($B$2:$B$100,”>=”&F2,$B$2:$B$100)
日付を条件にする場合も、見た目ではなくExcel内部の日付データとして正しく入力されているかを確認します。
数値比較の条件では、比較記号と基準値のつなぎ方が集計結果を左右します。
【操作のポイント】比較条件をセル参照する場合は、”>=”&セル番地の形で入力します。
重複項目の一覧を作成する方法
続いては、SUMIF関数の条件として使う重複なしの項目一覧を作る方法について解説していきます。
| 元データの商品名 | 重複なしの集計項目 |
|---|---|
| りんご | りんご |
| みかん | みかん |
| りんご | ぶどう |
| ぶどう |
重複の削除機能の利用
すばやく一覧を作るには、元データの項目列を別の場所へコピーし、データタブの重複の削除を使います。
コピー先の列を選択してから重複の削除を実行し、見出しがある場合は先頭行をデータの見出しとして認識させます。
対象列を確認してOKを選ぶと、同じ文字列が1件だけ残ります。
元の一覧を残したまま集計用コピーに対して重複の削除を行うと、明細データを失わずに済みます。
この操作は元に戻せるよう、作業前にファイルを保存しておくと安心です。
【操作のポイント】重複の削除は元データではなく、集計専用にコピーした列で実行します。
UNIQUE関数による自動抽出
Microsoft 365やExcel 2021以降では、UNIQUE関数を使って重複なしの項目を自動表示できます。
=UNIQUE(A2:A100)
この数式をD2セルに入力すると、A2からA100にある商品名から重複を除いた一覧が下方向へ展開されます。
新しい商品名を元データに追加した場合も、範囲内であれば集計用の一覧に反映されます。
UNIQUE関数とSUMIF関数を組み合わせると、集計表の更新作業を減らせるでしょう。
並べ替えも同時に行いたい場合は、SORT関数でUNIQUE関数を囲む方法があります。
【操作のポイント】UNIQUE関数の結果が展開される下側のセルは空けておきます。
ピボットテーブルとの比較
項目別の集計にはピボットテーブルも利用できます。
集計条件を式としてセルに残したい場合、別の表へ数式をコピーしたい場合、他の計算式と連携したい場合はSUMIF関数が向いています。
一方で、複数列を切り替えながら集計を確認したい場合や、大量データを素早く分類したい場合はピボットテーブルが便利です。
決まった帳票に合計値を表示するならSUMIF、分析軸を頻繁に変えるならピボットテーブルという考え方ができます。
用途に応じて使い分けることで、Excelでの集計作業が効率化します。
【操作のポイント】集計結果を他の数式で参照する帳票では、SUMIF関数が扱いやすい選択です。
SUMIF関数で合計できない場合の確認項目
続いては、数式を入力したのに合計が0になる、または結果が正しくない場合の確認項目を解説していきます。
| 症状 | 主な原因 | 確認方法 |
|---|---|---|
| 合計が0 | 項目名の表記違い | 空白や全角半角を確認 |
| 一部が集計されない | 範囲不足 | 最終行まで範囲を確認 |
| 値が合わない | 数値が文字列 | 表示形式と配置を確認 |
空白文字と表記ゆれの確認
SUMIF関数が0を返す代表的な原因は、見た目では気付きにくい文字の違いです。
セルの末尾に半角スペースや全角スペースが入っていると、「りんご」と「りんご 」は一致しません。
全角英数字と半角英数字、機種依存文字、表記の違いも条件不一致の原因になります。
同じように見える項目でも、セル内の文字列が完全一致している必要があります。
LEN関数で文字数を比べたり、TRIM関数で余分な空白を取り除いたりすると原因を見つけやすくなります。
=TRIM(A2)
【操作のポイント】貼り付けたデータでは、末尾の空白や全角半角の違いを優先して確認します。
数値が文字列になっている場合
合計範囲の金額が文字列として保存されていると、SUMIF関数は数値として足せない場合があります。
セル左上の緑色の三角形や、数値が左寄せで表示されている状態は、文字列の可能性を示す目安です。
エラー表示のメニューから数値に変換するか、VALUE関数や区切り位置機能を利用して数値へ変換します。
見た目が1000でも、文字列の1000は計算対象として正しく扱われないことがあります。
CSVファイルや外部システムから貼り付けたデータでは、特に確認しておきたい項目です。
【操作のポイント】合計範囲は数値形式にそろえ、文字列化した金額が混ざっていないか確認します。
集計範囲の拡張とテーブル化
新しい明細を追加したのに合計が変わらない場合は、SUMIF関数の参照範囲が追加行まで届いていない可能性があります。
たとえば$A$2:$A$100、$B$2:$B$100と指定している場合、101行目以降のデータは計算対象になりません。
定期的にデータが増える表では、Excelのテーブル機能を使う方法が便利です。
元データをテーブル化して構造化参照を利用すると、行を追加した際に集計対象が自動で広がります。
データの増加が見込まれる一覧は、固定範囲ではなくテーブルで管理すると更新漏れを防げます。
【操作のポイント】合計が不足するときは、条件範囲と合計範囲の最終行を最初に見直します。
まとめ 項目ごとにSUMIFで重複データを合計する方法
Excelで重複データを項目ごとに合計するには、SUMIF関数の条件範囲に項目列、条件に集計項目、合計範囲に金額または数量の列を指定します。
基本数式は、=SUMIF(条件範囲,条件,合計範囲)です。
元データの範囲は絶対参照にし、条件となる集計項目のセルは相対参照にすると、オートフィルで効率よく数式をコピーできます。
条件が複数になる場合はSUMIFS関数を使い、合計範囲を最初に指定します。
重複なしの項目一覧は、重複の削除機能やUNIQUE関数で作成できます。
合計が0になる場合は、項目名の空白、表記ゆれ、数値の文字列化、参照範囲の不足を確認しましょう。
SUMIF関数を使いこなせば、売上、数量、経費、勤務時間などの明細データを、必要な項目単位で見やすく集計できます。
