Excelで売上表や注文一覧を扱っていると、同じ商品名、担当者名、部門名などが複数行に分かれ、項目ごとの合計を確認しにくくなることがあります。
同じ項目をまとめる方法には、SUMIF関数で集計する方法、ピボットテーブルで一覧化する方法、重複の削除や並べ替えでデータを整える方法があります。
同じ項目をまとめる基本手順は、元データの列を確認し、集計したい項目を決め、関数またはピボットテーブルで合計を出す流れです。
元データを消さずに別の場所へ集計表を作る方法なら、あとから明細を確認しやすく、安全に作業できます。
この記事では、関数、合計、重複データの処理を使い分けながら、Excelで同じ項目を見やすくまとめる方法を解説します。
エクセルで同じ項目をまとめる方法1【SUMIF関数による合計】
それではまず、同じ項目ごとの金額をSUMIF関数で合計する方法について解説していきます。
| 商品名 | 売上金額 |
|---|---|
| りんご | 1,200 |
| みかん | 800 |
| りんご | 1,500 |
| バナナ | 900 |
上の例では、A列に商品名、B列に売上金額があり、1行目は見出しです。
集計用の項目一覧
SUMIF関数で同じ項目をまとめるときは、まず元データとは別の場所に、集計したい商品名や担当者名を1回ずつ並べます。
たとえばD1に商品名、E1に合計金額という見出しを入力し、D2以降にりんご、みかん、バナナと入力しましょう。
項目名の表記が元データと完全に一致していることが、正しい集計結果につながります。
りんごとリンゴ、または末尾に空白があるりんごは別の文字列として扱われるため、入力規則や置換で表記を整えておくと安心です。
集計表は元データの右側または別シートに作成すると、明細と集計値を見比べやすくなります。
項目一覧を手入力する件数が多い場合は、後述する重複の削除やUNIQUE関数を使って作成する方法も便利です。
SUMIF関数の入力
続いて、E2セルを選択し、りんごの売上を合計する数式を入力していきます。
=SUMIF($A$2:$A$5,D2,$B$2:$B$5)
SUMIF関数は、指定した条件に合う行だけを探し、その行の数値を合計する関数です。
最初のA2からA5は商品名を検索する範囲、D2はりんごという条件、最後のB2からB5は合計する売上金額の範囲です。
$記号を付けた絶対参照にしておくと、数式を下へコピーしても元データの範囲がずれません。
この例では、りんごに該当する1,200円と1,500円が合計され、E2には2,700と表示されます。
【操作のポイント】条件範囲と合計範囲は、必ず同じ行数・同じ開始行で指定します。
オートフィルによる数式のコピー
次に、E2セル右下の小さな四角にマウスポインターを合わせ、下方向へドラッグして数式をコピーしましょう。
これがオートフィルであり、D3のみかん、D4のバナナを条件にしたSUMIF関数へ自動的に変わります。
コピー後は、みかんが800、バナナが900となっているかを確認してください。
数式をコピーしたあとに合計が0になる場合は、条件セルの文字列、対象範囲、数値の形式を確認します。
SUMIF関数は、商品別売上、部署別経費、担当者別件数など、条件が1つの集計に向いています。
エクセルで同じ項目をまとめる方法2【ピボットテーブルによる集計】
続いては、データ量が多いときに便利なピボットテーブルで同じ項目を確認していきます。
| 担当者 | 商品名 | 金額 |
|---|---|---|
| 田中 | りんご | 1,200 |
| 佐藤 | みかん | 800 |
| 田中 | りんご | 1,500 |
ピボットテーブルは、元の一覧を集計用に並べ替えず、項目ごとの合計、件数、平均などを短時間で表示できる機能です。
元データの範囲選択
ピボットテーブルを作成する前に、データの1行目に商品名や金額などの見出しがあり、途中に空白行や空白列がないかを確認します。
表内の任意のセルを選択してから、リボンの挿入タブにあるピボットテーブルを選びましょう。
元データをテーブル形式にしておくと、後から行を追加した場合にも集計範囲を管理しやすくなります。
表示される画面でテーブルまたは範囲が正しいことを確認し、新規ワークシートを選択してOKを押します。
【操作のポイント】結合セルがある表や見出しが空欄の表は、ピボットテーブルの集計前に修正します。
フィールドの配置
続いて、右側のピボットテーブルのフィールド一覧から、商品名を行へ、金額を値へドラッグします。
商品名が重複なしで縦に並び、それぞれの売上金額の合計が表示されれば設定は完了です。
金額が件数として表示された場合は、値エリア内の項目をクリックし、値フィールドの設定から合計を選択してください。
商品名を行、金額を値へ配置すると、同じ商品名が自動的に1つへまとまり、数値は合計として計算されます。
担当者をフィルターや列に追加すれば、商品別かつ担当者別という複数の切り口でも集計できます。
更新と並べ替え
元データを変更または追加したあとは、ピボットテーブル内を右クリックし、更新を選択します。
更新をしない限り、ピボットテーブルには元データの変更がすぐ反映されない点に注意しましょう。
集計結果の金額を大きい順にしたい場合は、値のセルを右クリックし、並べ替えから降順を選びます。
ピボットテーブルは関数をコピーする手間がなく、数千行を超える一覧の集計にも使いやすい方法です。
【操作のポイント】明細を追加した後は、集計結果を見る前に更新を実行する習慣を付けます。
エクセルで同じ項目をまとめる方法3【重複データの削除と抽出】
続いては、重複データを整理して同じ項目を1つだけ残す方法を確認していきます。
| 顧客名 | 都道府県 |
|---|---|
| 青木商店 | 東京都 |
| 青木商店 | 東京都 |
| 森田商事 | 大阪府 |
重複の削除は、同一の顧客名や商品名を1件だけにしたい場合に使う機能です。
重複の削除機能
削除したい範囲を選択し、データタブから重複の削除を選択します。
1行目に見出しがある場合は先頭行をデータの見出しとして使用するにチェックが入っていることを確認しましょう。
顧客名だけで重複を判定したいなら顧客名の列だけを選び、顧客名と都道府県の組み合わせで判定したいなら両方の列を選びます。
先頭行をデータの見出しとして使用する ☑
☑ 顧客名 ☐ 都道府県
OK キャンセル
| A | B | |
|---|---|---|
| 1 | 顧客名 | 都道府県 |
| 2 | 青木商店 | 東京都 |
重複の削除は元データの行を実際に消す機能なので、作業前にファイルのコピーを保存しておくと安全です。
【操作のポイント】削除の基準となる列を間違えると必要な明細まで消えるため、列のチェック状態を確認します。
UNIQUE関数による重複なし一覧
Microsoft 365やExcel 2021以降では、UNIQUE関数を使って元データを残したまま重複なしの項目一覧を作れます。
たとえばD2セルに次の数式を入力すると、A2からA100にある商品名が重複しない形で表示されます。
=UNIQUE(A2:A100)
数式を入力したセルの下へ結果が自動的に広がるため、結果の範囲にはあらかじめ文字や数値を入力しないようにしましょう。
UNIQUE関数は元データに新しい商品名を追加すると、重複なし一覧にも自動で反映されます。
【操作のポイント】UNIQUE関数で作った一覧をSUMIF関数の条件欄に使うと、更新しやすい集計表になります。
条件付き書式による重複確認
重複をすぐには削除せず、どのデータが重複しているかだけを確認したい場合は条件付き書式を利用します。
対象列を選択し、ホームタブの条件付き書式からセルの強調表示ルール、重複する値を選択します。
重複したセルだけに色が付き、入力ミスや二重登録を目で確認できます。
重複を発見する段階では条件付き書式、不要な行を消す段階では重複の削除という使い分けが実用的です。
同じ項目でも日付や注文番号が異なる明細は、安易に削除せず、業務上本当に重複かを確認しましょう。
【操作のポイント】色が付いたデータは、フィルターで絞り込んでから内容を確認すると見落としを防げます。
エクセルで同じ項目をまとめる方法4【複数条件による集計】
続いては、商品名と担当者など、複数の条件を指定して同じ項目をまとめる方法を確認していきます。
| 担当者 | 商品名 | 売上 |
|---|---|---|
| 田中 | りんご | 1,200 |
| 佐藤 | りんご | 1,500 |
SUMIFS関数の構成
SUMIFS関数は条件を複数指定して数値を合計する関数です。
=SUMIFS($C$2:$C$100,$A$2:$A$100,E2,$B$2:$B$100,F2)
この数式では、C列の売上を合計し、A列がE2の担当者、かつB列がF2の商品名である行だけを対象にします。
SUMIFS関数では合計範囲を最初に指定し、その後に条件範囲と条件を組み合わせます。
【操作のポイント】条件範囲はすべて合計範囲と同じ行数にそろえます。
日付条件を含む集計
月別の売上をまとめたいときもSUMIFS関数が役立ちます。
開始日以上、終了日未満という2つの日付条件を指定すると、月末の日付の違いを意識せずに月単位で集計できます。
=SUMIFS($C$2:$C$100,$A$2:$A$100,E2,$D$2:$D$100,”>=”&G2,$D$2:$D$100,”<“&EDATE(G2,1))
G2には集計したい月の初日を入力し、日付列はExcelが認識する日付形式にしておきます。
【操作のポイント】日付が文字列になっていると条件に一致しないため、表示形式だけでなく値の形式も確認します。
エラーと空白の確認
集計結果が想定より少ないときは、項目名の空白、全角半角、数値が文字列になっている状態を確認します。
TRIM関数で不要な空白を取り除き、VALUE関数で文字列の数値を数値へ変換する方法もあります。
集計式が正しくても元データの表記ゆれが残っていると、同じ項目としてまとまりません。
【操作のポイント】元データを整える作業は、集計式を複雑にする前に行うのが基本です。
エクセルで同じ項目をまとめる方法5【並べ替えとテーブル機能】
続いては、一覧を見やすく保ちながら同じ項目を確認しやすくする並べ替えとテーブル機能を確認していきます。
| 商品名 | 数量 | 金額 |
|---|---|---|
| みかん | 4 | 800 |
| りんご | 6 | 1,200 |
商品名での並べ替え
商品名の列で昇順に並べ替えると、同じ商品名の行が近くに集まり、内容を確認しやすくなります。
表内のセルを選択し、データタブの並べ替えから商品名を指定して昇順を選びましょう。
並べ替えは同じ項目を1行へ集約する機能ではありませんが、重複や入力ミスを見つける前処理として有効です。
【操作のポイント】1列だけを選択して並べ替えると他列との対応が崩れるため、表全体を対象にします。
テーブル形式への変換
元データを選択し、挿入タブのテーブルを使うと、見出しごとのフィルターと自動拡張が利用できます。
テーブルにしたデータは行を追加しても範囲が広がるため、SUMIFやピボットテーブルの元データとして管理しやすくなります。
テーブル名を付けておけば、数式内でセル番地ではなく列名を使った分かりやすい参照も可能です。
【操作のポイント】テーブル化する前に、複数行の見出しや結合セルを解除しておきます。
フィルターによる確認
フィルターの一覧から特定の商品名だけにチェックを入れると、その項目の明細だけを表示できます。
抽出した明細を確認してからSUM関数で合計を出す方法は、少量のデータを目視確認したい場面で役立ちます。
並べ替えとフィルターは確認向け、SUMIFとSUMIFSは計算向け、ピボットテーブルは多角的な集計向けという違いがあります。
【操作のポイント】フィルターで非表示の行を含めずに合計したい場合はSUBTOTAL関数を使います。
まとめ エクセルで合計・重複データをまとめる方法
Excelで同じ項目をまとめるには、目的に合わせて集計方法を選ぶことが大切です。
商品別や担当者別の合計を別表に表示するならSUMIF関数、複数条件で集計するならSUMIFS関数が適しています。
大量の明細を項目別にすばやく一覧化したい場合は、ピボットテーブルが便利です。
重複を1件だけにしたい場合は重複の削除、元データを残して項目一覧を作りたい場合はUNIQUE関数を使いましょう。
集計前には、空白、表記ゆれ、文字列の数値、日付形式を確認することも重要です。
元データを整え、用途に合う機能を選べば、同じ項目が多いExcelの一覧も見やすく、正確に管理できるようになります。
