Excelで顧客名、商品名、社員名などが繰り返し入力された一覧を扱うと、同じデータを1件として数えたい場面があります。
単純に行を削除すると元の明細を失うため、目的に応じて関数、ピボットテーブル、重複の削除、Power Queryを使い分けることが大切です。
重複データを1件として数える基本は、重複を残したまま集計することです。
件数だけならUNIQUE関数とCOUNTA関数、金額も合計するならピボットテーブル、元データを整理するなら重複の削除が向いています。
この記事では、1行目を見出し行としたサンプル表を使い、エクセルで重複データをまとめる方法を分かりやすく解説していきます。
エクセルで重複データを1件として数える方法【UNIQUE関数】
それではまず、重複した値を自動で取り出して1件として数えるUNIQUE関数について解説していきます。
| 顧客名 | 商品 | 売上 |
|---|---|---|
| 青木商店 | ノート | 1200 |
| 井上商事 | ペン | 800 |
| 青木商店 | ファイル | 1500 |
| 加藤企画 | ノート | 1200 |
| 井上商事 | ペン | 800 |
上のような表では、顧客名は青木商店、井上商事、加藤企画の3社です。
明細行は5行あっても、重複しない顧客数は3件として扱えます。
UNIQUE関数による重複なし一覧
UNIQUE関数は、指定した範囲から重複しない値だけを抽出する関数です。
たとえばA列の顧客名をG2セルから一覧にしたい場合は、G2セルに次の式を入力します。
=UNIQUE(A2:A6)
Enterキーを押すと、G2セルから下へ結果が自動的に展開されます。
青木商店、井上商事、加藤企画の順に表示され、同じ顧客名は2回表示されません。
この自動展開はスピルと呼ばれる仕組みです。
元データに新しい顧客を追加した場合は、参照範囲をA2:A1000のように余裕を持って設定すると管理しやすくなります。
UNIQUE関数は元の明細を消さずに別の場所へ重複なしリストを作れる点が大きな利点です。
ただし、Excel 2021以降またはMicrosoft 365で使える比較的新しい関数であることには注意しましょう。
COUNTA関数を組み合わせた件数集計
重複なしの一覧を作れたら、COUNTA関数で空白以外のセル数を数えると、ユニークな件数を求められます。
たとえばG2セルにUNIQUE関数の結果が出ている場合、別のセルに次の式を入力します。
=COUNTA(UNIQUE(A2:A6))
この式ではUNIQUE関数が重複のない顧客名を作り、その結果をCOUNTA関数が数えます。
結果は3となり、顧客名を1件ずつとして数えた件数が分かります。
式を1つのセルにまとめられるため、報告用の集計欄やダッシュボードにも便利です。
顧客名に空白セルが混じる可能性がある場合は、対象範囲を必要以上に広げないか、FILTER関数を併用する方法を検討しましょう。
複数列を条件にした重複判定
顧客名だけではなく、顧客名と商品の組み合わせを1件として数えたいこともあります。
この場合は、A列からB列までをUNIQUE関数の対象にします。
=UNIQUE(A2:B6)
顧客名と商品の両方が同じ行だけが重複として扱われます。
たとえば青木商店のノートと青木商店のファイルは商品が異なるため、別の組み合わせとして残ります。
どの列を同じと見なすかを先に決めることが、正しい重複集計の出発点です。
【操作のポイント】単一列の重複か、複数列の組み合わせ重複かによって、UNIQUE関数で指定する範囲を変えましょう。
ピボットテーブルによる重複データの集計
続いては、顧客別や商品別にデータをまとめ、件数や金額を確認できるピボットテーブルを確認していきます。
| 担当者 | 商品 | 売上 |
|---|---|---|
| 田中 | ノート | 1200 |
| 鈴木 | ペン | 800 |
| 田中 | ノート | 1200 |
| 佐藤 | ファイル | 1500 |
| 鈴木 | ペン | 800 |
ピボットテーブルは同じ項目を自動的にグループ化するため、重複した明細をまとめて確認できます。
明細を削除せずに集計表を作れるため、売上分析や担当者別の実績確認に適した機能です。
ピボットテーブルの作成手順
まず、見出しを含めた表内の任意のセルをクリックします。
次にリボンの挿入タブを選び、ピボットテーブルをクリックします。
テーブルまたは範囲が正しく選択されていることを確認し、新規ワークシートを選んでOKを押しましょう。
右側にピボットテーブルのフィールド一覧が表示されます。
元データは途中に空白行や空白列を作らず、1行目に項目名を置いた連続した表にすることが重要です。
空白の見出しがあると、フィールド名が分かりにくくなり、集計時のミスにつながるかもしれません。
行ラベルと値によるユニーク件数
フィールド一覧から顧客名または担当者を行のエリアへドラッグします。
これだけで、同じ名前は1つにまとまり、重複のない一覧のように表示されます。
さらに顧客名を値のエリアにもドラッグすると、既定では個数として集計されることがあります。
この数値は同じ名前が元データに何回登場したかを示します。
たとえば田中が2、鈴木が2、佐藤が1と出れば、重複数を含む明細件数を一目で把握できます。
行のエリアは重複をまとめる軸、値のエリアは件数や合計を計算する場所です。
売上合計と件数の見分け方
売上列を値のエリアへ入れると、通常は売上の合計が表示されます。
同じ顧客の売上を合算したい場合はこの設定で問題ありません。
一方で、売上データが文字列として入力されていると、合計ではなく個数になる場合があります。
そのときは元の売上列を数値形式へ変換し、ピボットテーブルを右クリックして更新を選びます。
件数を見たい場合は値フィールドの設定でデータの個数を選びます。
金額を見たい場合は値フィールドの設定で合計を選びます。
同じ表でも集計方法によって意味が変わるため、見出しを売上合計、顧客数、明細件数のように分かりやすく整えましょう。
【操作のポイント】ピボットテーブルを作った後に元データを変更したときは、ピボットテーブル上で右クリックして更新を実行します。
重複の削除機能によるデータ整理
続いては、同じデータそのものを1行だけ残し、リストを整理する重複の削除機能を確認していきます。
| 会員番号 | 氏名 | 都道府県 |
|---|---|---|
| 1001 | 山田花子 | 東京都 |
| 1002 | 佐々木健 | 大阪府 |
| 1001 | 山田花子 | 東京都 |
| 1003 | 高橋洋子 | 愛知県 |
この機能は、完全に同じ行を1行にまとめたい場合に便利です。
ただし、実行後は元の重複行が消えるため、集計前に必ず元データを複製して保存することをおすすめします。
対象範囲とバックアップの準備
重複の削除を行う前に、元のシートをコピーしてバックアップ用のシートを作成しましょう。
シート見出しを右クリックし、移動またはコピーを選んでコピーを作成するにチェックを入れる方法があります。
または、対象表を別シートへコピーしてから作業しても構いません。
次に、見出しを含む表全体を選択します。
表内の1セルだけを選んでもExcelが連続範囲を認識する場合がありますが、不要な列まで含めないよう範囲を確認することが安全です。
重複判定に必要な列だけを意識すると、意図しない削除を防げます。
データタブから重複の削除を実行
表を選択したら、リボンのデータタブを開き、データツールグループにある重複の削除をクリックします。
| A | B | C | |
|---|---|---|---|
| 1 | 会員番号 | 氏名 | 都道府県 |
| 2 | 1001 | 山田花子 | 東京都 |
| 3 | 1002 | 佐々木健 | 大阪府 |
| 4 | 1001 | 山田花子 | 東京都 |
表示された画面では先頭行を見出しとして扱うにチェックが入っていることを確認します。
会員番号、氏名、都道府県のすべてにチェックを入れると、3列すべてが一致する行だけが重複と判定されます。
会員番号だけで判定したい場合は、会員番号だけにチェックを残します。
OKを押すと、削除された重複数と残った一意の値の数がメッセージで表示されます。
判定列の選び方と注意点
会員番号が同じなら同一人物と判断できる運用では、会員番号だけを判定列にするのが合理的です。
一方、氏名だけでは同姓同名の別人を誤ってまとめる可能性があります。
住所や電話番号が更新されている行は、すべての列を選ぶと重複として認識されません。
完全一致を消す場合は関連する全列を選びます。
会員番号を基準に1件へ統合する場合は会員番号だけを選びます。
削除後に必要な情報が残っているかを確認し、問題があればすぐに元に戻す操作を行いましょう。
【操作のポイント】重複の削除は元データを変更する操作です。保存前にバックアップを作り、チェックを入れる列を慎重に選択します。
COUNTIF関数による重複件数の判定
続いては、各データが何回登場しているかを表示し、重複の有無を確認できるCOUNTIF関数を確認していきます。
| 顧客名 | 商品 | 重複回数 |
|---|---|---|
| 青木商店 | ノート | |
| 井上商事 | ペン | |
| 青木商店 | ファイル | |
| 加藤企画 | ノート |
COUNTIF関数を使うと、削除や集計の前に重複状況を可視化できます。
元データを残したまま確認したい場合に役立つ方法です。
COUNTIF関数の基本式
C2セルに次の式を入力し、下へオートフィルします。
=COUNTIF($A$2:$A$6,A2)
最初の引数は数える範囲で、$記号を付けて絶対参照にしています。
2番目の引数は、その行の顧客名です。
青木商店は一覧内に2回あるため2、加藤企画は1回なので1と表示されます。
2以上なら重複あり、1ならその範囲では重複なしと判断できます。
オートフィルによる数式のコピー
数式を入力したC2セルを選択し、セル右下の小さな四角を下方向へドラッグします。
これがオートフィルで、相対参照のA2だけがA3、A4のように行ごとに変化します。
一方で$A$2:$A$6は固定されるため、すべての行で同じ顧客名一覧を対象に数えられます。
数式が期待どおりに動かない場合は、範囲の$記号が抜けていないか確認しましょう。
データ件数が増える可能性があるなら、A2:A1000のように参照範囲を広く取るか、テーブル化する方法が便利です。
条件付き書式との組み合わせ
重複データを目立たせたいときは、条件付き書式の重複する値を利用できます。
顧客名の列を選び、ホームタブから条件付き書式、セルの強調表示ルール、重複する値の順に選択します。
色を指定すると、同じ顧客名だけが強調表示されます。
COUNTIF関数の結果列と組み合わせれば、重複回数と対象セルを同時に確認できます。
削除する前に色で目視確認する工程を入れると、誤操作の防止につながります。
【操作のポイント】COUNTIF関数は重複を数えるための機能であり、データを削除しません。まず現状を確認したいときに使いましょう。
Power Queryによる重複データの統合
続いては、件数の多い表を繰り返し整理したい場合に便利なPower Queryによる重複データの統合を確認していきます。
| 受注番号 | 取引先 | 商品 | 数量 |
|---|---|---|---|
| A001 | 青木商店 | ノート | 10 |
| A002 | 井上商事 | ペン | 5 |
| A001 | 青木商店 | ノート | 10 |
| A003 | 加藤企画 | ファイル | 8 |
Power Queryは、データの取り込みと変換を記録し、次回以降は更新だけで同じ処理を再実行できる機能です。
毎月届くCSVや受注一覧から重複を除きたい場合に特に力を発揮します。
テーブル化とPower Queryへの取り込み
まず元データの範囲内をクリックし、挿入タブまたはホームタブからテーブルとして書式設定を選びます。
先頭行をテーブルの見出しとして使用するにチェックを入れてテーブルを作成します。
次にデータタブのテーブルまたは範囲からをクリックすると、Power Queryエディターが開きます。
テーブル化しておくと、行が追加されても対象範囲を広げやすくなります。
行の削除による重複除去
Power Queryエディターで、重複判定に使う列を選択します。
すべての列が同じ行だけを削除するなら、すべての列を選択します。
受注番号を基準に1件だけ残すなら、受注番号列だけを選択しましょう。
ホームタブから行の削除を選び、重複の削除を実行します。
プレビュー上で同じ受注番号が1行だけになったことを確認できます。
どの行を残すかは並び順の影響を受けるため、残したい最新行が先になるように並べ替える配慮も必要です。
閉じて読み込む操作と更新
処理内容を確認したら、ホームタブの閉じて読み込むを選びます。
重複を除いた結果が別シートに表として読み込まれ、元のデータは保持されます。
次回、元データを追加または差し替えた後は、出力表を右クリックして更新を選ぶだけで処理を再実行できます。
Power Queryは、元データを直接削除せず、変換後の結果を別に出力する仕組みです。
定型作業を繰り返すほど、手作業よりもミスを減らしやすくなります。
一度作った手順を更新で再利用できる点が、通常の重複の削除との大きな違いです。
【操作のポイント】毎回同じ形式のデータを整理するなら、Power Queryで重複除去の手順を保存しておくと効率的です。
まとめ エクセルで重複データを1件として数える方法
エクセルで重複データをまとめる方法は、データを残すか、削除するか、合計まで出すかによって選択肢が変わります。
重複しない件数だけを知りたい場合は、COUNTA関数とUNIQUE関数を組み合わせる方法が簡単です。
顧客別の件数や売上合計を確認したい場合は、ピボットテーブルが向いています。
一覧そのものを整理したいときは重複の削除を使えますが、元データを変更するためバックアップを忘れないようにしましょう。
重複数を先に確認するならCOUNTIF関数、定期的に同じ整理を繰り返すならPower Queryが便利です。
データの意味に合う判定列を選び、元の明細を残す必要があるかを確認してから操作することが、正確な集計への近道になります。
