Excelで重複データを見つける方法|色付け・COUNTIF・削除まで解説

Excelで名簿や商品一覧を整理していて、「同じデータが2回入っているかも」と気になったことはありませんか?

数件なら目で確認できますが、何十件、何百件もあると、重複しているデータを探すだけでも時間がかかります。

そんなときは、Excelの「条件付き書式」を使えば、同じ値が入力されているセルを簡単に色付けできます。

関数を入力する必要がないので、Excelに慣れていない人でも試しやすい方法です。

重複したデータを文字で判定したい場合はCOUNTIF関数、氏名と電話番号などを組み合わせて調べたい場合はCOUNTIFS関数も使えます。

この記事では、次の内容を順番に説明します。

  • 条件付き書式で重複セルを色付けする方法
  • COUNTIF関数で重複や2件目以降を判定する方法
  • COUNTIFS関数で複数列を組み合わせて確認する方法
  • 不要な重複データを安全に削除する手順
  • 重複が見つからないときの確認ポイント

同じ顧客管理表を使って説明するので、Excelを開きながら一緒に試せます。

※この記事は、Windows版のExcel for Microsoft 365・Excel 2024を中心に説明しています。Excel 2021・2019・2016でも基本的な機能は利用できますが、画面表示が異なる場合があります。

目次

Excelで重複データを見つける3つの方法

Excelで重複データを見つけるには、調べたい範囲を選択し、「ホーム」→「条件付き書式」→「セルの強調表示ルール」→「重複する値」を選びます。

この操作だけで、関数を使わずに重複しているセルを色付けできます。

重複を調べる方法は、目的によって使い分けられます。

  • 条件付き書式:同じ値を色付けする
  • COUNTIF関数:重複する行に文字を表示する
  • COUNTIFS関数:複数列を組み合わせて重複を判定する

どの方法を使えばいい?目的別の選び方

やりたいことおすすめの方法
重複しているデータを色で見つけたい条件付き書式
重複する行に「重複」と表示したいCOUNTIF関数
2件目以降だけを判定したいCOUNTIF関数
氏名と電話番号の両方が一致するデータを探したいCOUNTIFS関数
確認後に重複行を削除したい重複の削除

重複があるか確認したいだけなら、条件付き書式で十分です。重複した行を区別したいときに、COUNTIF関数を使いましょう。

重複チェックに使うサンプル表

今回は、次のような顧客管理表を使います。

行A列:顧客IDB列:氏名C列:電話番号
2C001田中090-0000-0001
3C002佐藤090-0000-0002
4C003鈴木090-0000-0003
5C001田中090-0000-0001
6C004高橋090-0000-0004
7C002佐藤090-0000-0002
顧客IDが重複しているExcelのサンプル表

Excelの新しいシートを開き、A1に「顧客ID」、B1に「氏名」、C1に「電話番号」と入力します。

続いて、表を見ながらA2~C7に6件のデータを入力してください。A列・B列・C列はExcelの上部に表示される列名、1~7は左側に表示される行番号です。

電話番号を入力する前に、C2からC7までを選択します。Excelの「ホーム」タブにある表示形式の選択欄から「文字列」を選んでください。その後、電話番号を入力します。

電話番号や顧客コードは計算するための数字ではないので、文字列として管理しておくと、先頭の「0」が消えるのを防げます。

この表には、同じ顧客IDが2組あります。

  • C001:2行目と5行目
  • C002:3行目と7行目

この2組をExcelで見つけていきましょう。

なお、顧客IDが同じだからといって、必ず削除してよいとは限りません。複数回の購入履歴など、同じIDが必要な場合もあります。今回は、重複チェックを練習するための架空データとして使用します。

方法1:条件付き書式で重複データに色を付ける

重複しているデータを手軽に見つけたいときは、条件付き書式を使います。

条件付き書式とは、セルの値が指定した条件に当てはまるとき、自動で色などを付けるExcelの機能です。

サンプル表のA列にある顧客IDで、重複を調べてみましょう。たとえば、C001が2回入力されていれば、その2つのセルに色が付きます。

ただし、色が付くのは選択したA列のセルです。氏名や電話番号を含む行全体が自動で色付けされるわけではありません。行全体を色付けするには、別の条件付き書式を設定する必要があります。

重複するセルを色付けする手順

先ほど作成した顧客管理表を使って、重複する顧客IDを探します。

ステップ1|顧客IDの範囲を選択する

A2セルをクリックし、マウスを押したままA7まで動かしてください。A2~A7の6つのセルが選択された状態になれば準備完了です。

マウスで範囲を選択するのが難しければ、数式バーの左にある名前ボックスに A2:A7 と入力してEnterキーを押す方法もあります。見出しのA1は選択しません。

ステップ2|条件付き書式を開く

  1. 「ホーム」タブ
  2. 「条件付き書式」
  3. 「セルの強調表示ルール」
  4. 「重複する値」

ステップ3|色を選ぶ

表示された画面で「重複」が選ばれていることを確認し、好きな色を選択します。たとえば「濃い赤の文字、明るい赤の背景」を選び、「OK」をクリックしてください。これで設定は完了です。

Excelの条件付き書式でA2からA7の顧客IDを選択し、重複するC001とC002の4つのセルを色付けする手順

Microsoft公式でも、条件付き書式を使って重複する値を強調表示する方法が案内されています。

出典:Microsoft サポート|重複を検索して削除する

色付けされたデータの確認方法

正しく設定できていれば、A列の次のセルに色が付きます。

セル顧客ID結果
A2C001色が付く
A3C002色が付く
A4C003色は付かない
A5C001色が付く
A6C004色は付かない
A7C002色が付く

条件付き書式では、同じ値が複数ある場合、1件目も2件目も色が付きます。今回なら、C001とC002が入力されている4つのセルが対象です。

なお、色を付けても元のデータは削除されません。2件目以降だけを区別したい場合は、次のCOUNTIF関数を使います。

方法2:COUNTIF関数で重複をチェックする

COUNTIF関数を使うと、同じ顧客IDが何回入力されているかを数えられます。その結果をもとに、重複する行へ「重複」と表示したり、2件目以降だけを見つけたりできます。

重複するデータを「重複」と表示する

先ほどの顧客管理表に、D列を追加しましょう。D1には「重複チェック」と入力します。続いてD2に、次の数式を入力してください。

=IF(COUNTIF($A$2:$A$7,A2)>1,"重複","")

Enterキーを押したら、もう一度D2セルをクリックします。選択したセルの右下にある小さな四角形が「フィルハンドル」です。その部分にマウスを合わせ、下方向へD7までドラッグしてください。

D2~D7に数式がコピーされ、それぞれの顧客IDについて重複が判定されます。

  • $A$2:$A$7:重複を調べる範囲
  • A2:今調べている顧客ID
  • COUNTIF(...)>1:同じIDが2回以上あるか確認する
  • "重複":2回以上あれば表示する文字
  • "":重複していない場合、判定結果を空欄に見せる

なお、D列には数式が入っているため、見た目が空欄でも、何も入力されていないセルとは異なります。$は、数式をコピーしても検索範囲がずれないようにするための記号です。

行顧客IDD列:重複チェック
2C001重複
3C002重複
4C003空欄
5C001重複
6C004空欄
7C002重複

条件付き書式と同じく、1件目も含めて重複するすべての行に「重複」と表示されます。

顧客IDが空白の行がある場合

今回のサンプル表では、すべての行に顧客IDが入力されています。実際の名簿に空白セルがある場合は、空白まで重複として判定されることがあります。

顧客IDが空白の行を除いて重複を調べたい場合は、D2に入力した式を次の式に置き換えます。

=IF(A2="","",IF(COUNTIF($A$2:$A$7,A2)>1,"重複",""))

入力したら、D7までコピーしてください。この式では、A列の顧客IDが空白ならD列も空欄になり、入力されている顧客IDだけを重複判定します。

なお、今回のサンプル表には空白の顧客IDがないため、最初に紹介した式と結果は変わりません。

2件目以降だけを重複と判定する

「Excelで重複している2件目以降だけを見つけたい」という場合は、COUNTIF関数の検索範囲を少し変えます。

先ほどの式では、重複している1件目にも「重複」と表示されました。今回は、同じ顧客IDが2回目以降に登場した行を判定します。この数式では3回目、4回目に登場した場合も「重複」と表示されます。

D2の数式を、次のように変更します。

=IF(COUNTIF($A$2:A2,A2)>1,"重複","")

入力したら、D7までコピーしてください。結果はこうなります。

行顧客IDD列:重複チェック
2C001空欄
3C002空欄
4C003空欄
5C001重複
6C004空欄
7C002重複
COUNTIF関数で2件目以降の重複データを判定する図解

先ほどとは違い、5行目と7行目だけが「重複」と表示されます。ポイントは、次の部分です。

$A$2:A2

範囲の開始位置はA2に固定していますが、終了位置は固定していません。そのため、数式を下にコピーすると、検索する範囲が次のように広がります。

  • D2:A2まで確認
  • D3:A3まで確認
  • D4:A4まで確認
  • D5:A5まで確認

D5では、A2からA5の範囲に「C001」が2回あるため、「重複」と表示されます。

この方法なら、最初の登録を残して、後から追加された重複候補を見つけられます。ただし、先に入力されたデータが必ず正しいとは限りません。 実際に整理するときは、登録日時や顧客情報なども確認してください。

英字の大文字・小文字を含むデータの場合

なお、COUNTIF関数は英字の大文字と小文字を区別しません。たとえば「ABC001」と「abc001」は、COUNTIFでは同じ値として数えられます。英字の大文字・小文字まで区別する必要がある場合は、別の方法で確認する必要があります。

出典:Microsoft サポート|COUNTIF関数

COUNTIFの結果が正しくないときの確認方法

数式を入れたのに思ったとおりに表示されないときは、次の点を確認しましょう。

1.検索範囲が正しいか

今回の表はA2からA7です。ほかのセルまで含めたり、途中の行が抜けたりしていないか確認してください。

2.$の位置が正しいか

すべての重複をチェックする場合と、2件目以降だけをチェックする場合では、固定する範囲が違います。

全件チェック:

$A$2:$A$7

2件目以降:

$A$2:A2

3.数式が下までコピーされているか

D2だけに数式を入力しても、ほかの行は判定されません。D7までコピーされているか確認してください。

方法3:複数列を組み合わせて重複を確認する

重複チェックでは、どの列を基準にするかによって結果が変わります。たとえば、氏名だけで調べると、同姓同名の別人まで重複と判定される場合があります。

一方、氏名と電話番号の両方が一致するデータを探せば、名前だけで調べるよりも条件を絞れます。氏名と電話番号のように、2つ以上の項目をまとめてチェックしたいときに使うのがCOUNTIFS関数です。

顧客IDだけで重複を判定する場合

ここまで紹介したCOUNTIF関数では、A列の顧客IDだけを使って重複を調べました。例えばC001が2回登場すれば、氏名や電話番号の内容に関係なく「重複」と判定します。

顧客IDが一人につき一つと決まっている名簿なら、この方法が使いやすいでしょう。ただし、顧客IDを再利用する運用や、同じ顧客IDで複数の履歴を管理する場合は、IDが同じでも不要なデータとは限りません。

氏名と電話番号が両方一致するデータを探す

次に、氏名と電話番号の両方が一致する行をチェックしてみましょう。ここでは、先ほどCOUNTIFで使ったD列を、COUNTIFSの判定結果に変更します。

D1の見出しを「重複候補」に変更し、D2に次の数式を入力してください。

=IF(COUNTIFS($B$2:$B$7,B2,$C$2:$C$7,C2)>1,"重複候補","")

数式をD7までコピーすると、以前のCOUNTIFの結果はCOUNTIFSの結果に置き換わります。どちらの結果も残しておきたい場合は、新しいE列を使っても構いません。その場合は、E1に「重複候補」、E2に数式を入力してE7までコピーしてください。

COUNTIFS関数は、複数の条件をすべて満たすデータの件数を数える関数です。今回の数式は、同じ行の氏名と電話番号が、別の行でも両方一致しているかを確認するものです。

  • B列の氏名が一致する
  • C列の電話番号も一致する

この2つの条件を同時に満たす行が複数あれば、「重複候補」と表示します。今回のサンプル表では、田中さんの2行目と5行目、佐藤さんの3行目と7行目が該当します。

行氏名電話番号判定結果
2田中090-0000-0001重複候補
3佐藤090-0000-0002重複候補
4鈴木090-0000-0003空欄
5田中090-0000-0001重複候補
6高橋090-0000-0004空欄
7佐藤090-0000-0002重複候補
COUNTIFS関数で氏名と電話番号が両方一致する重複候補を確認する図解

田中さんと佐藤さんは、それぞれ氏名と電話番号が同じ組み合わせで2回登場するため、「重複候補」と表示されます。

氏名だけで判定するより詳しく調べられますが、同じ電話番号を家族で使っているケースなども考えられます。そのため、結果を「削除対象」と決めつけず、重複候補として確認することが大切です。

出典:Microsoft サポート|COUNTIFS関数

重複データを削除する方法と注意点

重複が見つかっても、すぐに削除する必要はありません。削除する前に、次の3点を確認しましょう。

  1. 同じデータが複数あることに、業務上の理由はないか
  2. どの列が一致したら重複と判断するのか
  3. 重複している場合、どの行を残すのか

Excelの「重複の削除」は、同じ値が複数ある場合、表の上から最初に登場した行を残します。たとえば、古い情報と新しい情報が重複している場合でも、新しい行を自動で優先して残すわけではありません。

新しい登録情報を残したい場合は、削除する前に必要な情報を確認し、残す行を整理しておきましょう。今回のサンプル表では、先に登場する2行目のC001と3行目のC002を残す想定です。

たとえば、顧客名簿なら同じ顧客の二重登録かもしれませんが、購入履歴なら同じ顧客IDが何度も登場するのは自然なことです。

削除してよいデータだと確認できたら、Excelの「重複の削除」機能を使います。ただし、この操作は色付けや関数による確認とは違い、実際にデータを削除します。

顧客名簿や売上データを扱う場合は、必要な行を間違って削除しないように、次の手順で元データをコピーしてから作業しましょう。

削除前に元データをコピーする

元の表を残したまま作業できるように、まずは削除専用のシートを作ります。例えば、Excelのシート名が「顧客一覧」なら、次の手順で準備してください。

  1. Excel画面下部の「+」をクリックし、新しいワークシートを追加する
  2. 元の「顧客一覧」シートでA1:C7を選択してコピーする
  3. 新しいシートのA1セルに貼り付ける
  4. 新しいシートの名前を「重複削除用」に変更する

これで、元の顧客管理表とD列の重複チェック結果を残したまま、削除専用の表で作業できます。元のシートには手を加えず、以降の操作は「重複削除用」シートで行ってください。

重要なデータの場合は、Excelファイル自体も別名で保存しておくと、さらに安心です。

「重複の削除」で対象列を指定する

ステップ1|削除対象の表を選択する

「重複削除用」シートで、A1:C7を選択します。この表には、顧客ID・氏名・電話番号の3列だけが入っています。

A列の「顧客ID」だけで重複を判定しますが、選択するのは顧客ID・氏名・電話番号を含む表全体です。

A列だけを選んだ状態で重複の削除を実行すると、ほかの列との対応関係に影響する可能性があります。元のシートにあるD列の「重複チェック」は削除専用シートにコピーしていないため、判定結果と行の対応が崩れる心配もありません。

ステップ2|重複の削除を開く

  1. 「データ」タブ
  2. 「重複の削除」

ステップ3|重複を判定する列を指定する

表示された画面で「先頭行をデータの見出しとして使用する」にチェックが入っていることを確認してください。今回の例では「顧客ID」だけにチェックを入れ、「氏名」「電話番号」のチェックは外します。

これで、顧客IDが同じ行を重複として扱います。

Excelの重複削除で顧客IDを判定対象に指定し6件を4件に整理する図解

ステップ4|削除を実行する

設定を確認したら「OK」をクリックします。Excelには、削除した重複データの件数と、残った一意のデータの件数を知らせるメッセージが表示されます。

この表では、重複した2件が削除され、4件が残ります。メッセージの件数を確認したら「OK」をクリックしてください。

削除されるのは、顧客ID「C001」と「C002」が2回目に登場する行です。次に、残った4件の内容を確認します。

ここで注意したいのが、チェックを入れた列だけが削除されるわけではないということです。「顧客ID」だけを重複判定に使用した場合でも、削除対象になった行の氏名や電話番号も一緒に削除されます。

氏名と電話番号の両方を基準に削除したい場合

氏名と電話番号の両方が一致する行だけを重複と判断したい場合は、「重複の削除」の設定画面で「氏名」と「電話番号」にチェックを入れ、「顧客ID」のチェックを外します。

この設定では、氏名と電話番号の組み合わせが同じ行を重複として判定します。ただし、同じ氏名・電話番号でも別の記録として必要な場合があります。削除する前に、残しておくべき情報がないか確認してください。

出典:Microsoft サポート|重複しない値を抽出する、または重複する値を削除する

削除後の件数と残ったデータを確認する

重複の削除が終わったら、必ず結果を確認しましょう。今回のサンプル表で、顧客IDを基準に削除した場合は次の4件が残ります。

顧客ID氏名電話番号
C001田中090-0000-0001
C002佐藤090-0000-0002
C003鈴木090-0000-0003
C004高橋090-0000-0004

削除が終わったら、次の内容を確認します。

  • 元の6件から4件になっているか
  • C001・C002・C003・C004が1件ずつ残っているか
  • 残っている氏名と電話番号が正しいか

件数が合っていても、必要な情報が消えている場合があります。元のデータと見比べて確認しましょう。

意図しない行が削除されていた場合は、すぐに「Ctrl + Z」で元に戻せることがあります。ただし、操作を重ねたりファイルを閉じたりすると元に戻せない場合もあるため、バックアップを残しておくことが重要です。

元の表を変更せず、重複のない一覧を作りたい場合

Excelの「詳細設定フィルター」やUNIQUE関数を使う方法もあります。UNIQUE関数は、Excel for Microsoft 365・Excel 2024・Excel 2021などで利用できます。

Excel 2019・2016では利用できないため、これらのバージョンでは「詳細設定フィルター」などを使って、重複のない一覧を作る方法があります。

今回は重複を見つけて確認することを中心に説明しているため、重複のない一覧を作る方法は別の機会に詳しく紹介します。

重複が見つからない・判定がおかしいときの確認ポイント

「同じデータがあるはずなのに色が付かない」「関数の結果が合わない」

そんなときは、データの入力状態や設定を確認してみましょう。

前後に余分なスペースが入っている

例えば、次の2つを比べてみてください。

田中
田中[半角スペース]

2つ目は「田中」の後ろに半角スペースが入っている例です。実際のExcelでは、このスペースが目で見えないため、同じ文字に見えることがあります。

余分なスペースが原因で、期待したとおりに照合できない場合があります。余分な半角スペースを整理したい場合は、TRIM関数を使えます。

=TRIM(B2)

TRIM関数は、文字列の前後にある通常の半角スペースを取り除き、単語間などに連続して入った半角スペースを1つに整理します。ただし、全角スペースや特殊な空白文字などは、TRIM関数だけでは取り除けない場合があります。

出典:Microsoft サポート|TRIM関数

元データを変更する前に、どのような違いがあるのか確認してください。

文字・数値・表示形式の違い

Excelでは、数字に見える値でも、文字列として保存されていることがあります。また、セルに保存されている数値が同じでも、表示形式によって見た目が変わることがあります。

たとえば、どちらも数値の「1」が入力されていても、一方は「1」、もう一方は「1.00」と表示される場合があります。

Microsoft公式によると、「重複の削除」では表示形式の違いが重複判定に影響する場合があります。

参考:Microsoft サポート|重複しない値を抽出する、または重複する値を削除する

そのため、重複を削除する前に、比較する列の表示形式がそろっているか確認してください。「見た目が同じ」「内部の値が同じ」というだけでは、すべての重複判定方法で同じ結果になるとは限りません。

結果が合わない場合は、次の点を確認してみてください。

  • 数値が文字列として保存されていないか
  • セルの表示形式が異なっていないか
  • 全角と半角が混在していないか
  • 不要なスペースが含まれていないか

特に、顧客IDや商品コードは先頭のゼロが必要な場合があります。データを数値に変換すると「00123」が「123」になることもあるため、安易に変換しないようにしましょう。

条件付き書式や重複削除の対象範囲が間違っている

条件付き書式で色が付かないときは、最初に選択した範囲を確認してください。今回ならA2:A7を選択します。

重複の削除では、チェックを入れた列の組み合わせも重要です。たとえば「氏名」だけで判定すると、同姓同名の別人まで削除対象になる可能性があります。

氏名だけで判断してよいのか、顧客IDや電話番号まで確認する必要があるのかは、使っている名簿の目的によって変わります。

重複チェックがうまくいかないときは、次の順に確認してみてください。

  1. 調べたい列・範囲を正しく選んでいるか
  2. セルに余分なスペースが入っていないか
  3. 数値と文字列が混在していないか
  4. 重複と判断する条件が適切か

まずは範囲の指定を確認し、それでも結果が合わない場合は、元のデータの入力状態を調べましょう。

まとめ|重複を見つけてから、必要なデータだけ整理しよう

Excelで重複データを調べるときは、何を確認したいかによって使う機能が変わります。

  • 同じデータがあるか確認したい:条件付き書式で色付け
  • 重複する行を判定したい:COUNTIF関数
  • 氏名と電話番号などをまとめて確認したい:COUNTIFS関数
  • 不要なデータを整理したい:「重複の削除」機能

重複しているかを調べるだけなら、条件付き書式から始めれば十分です。ただし、重複しているからといって、必ず削除してよいわけではありません。

顧客名簿の二重登録なのか、同じ顧客の購入履歴なのかによって、残しておくべきデータは変わります。

重複を見つけたら、まず内容を確認すること。 不要なデータだと判断できた場合だけ、元の表をコピーしてから削除しましょう。

最初は条件付き書式で重複を探すところから試してみてください。

目次