Excelで重複データを見つけて消す｜条件付き書式・COUNTIF・重複の削除と消えない原因
Excelの重複データを、行全体とキー列の違いから整理。条件付き書式、COUNTIF・COUNTIFS、重複の削除、詳細設定、UNIQUEの使い分けと具体的な手順を紹介します。空白や全角・半角、数値と文字列、ワイルドカード、長い番号の注意点、二つの表の照合、削除前後の確認までまとめました。
名簿の同じ人が2行になっている、CSVを追加したら同じ注文が混ざった。Excelで重複を見つけても、すぐに削除してよいとは限りません。同じメールアドレスでも申込日が違うことがあり、社員番号が同じ2行に新旧の所属が分かれている場合もあります。
重複を整理するときは、「何が同じなら一件とするか」と「どの行を残すか」を先に決めます。そのうえで元データを残し、色や回数で候補を確認してから削除・抽出へ進むと、必要な情報を失いにくくなります。操作例はWindowsのデスクトップ版を中心に、見出しが1行、データが2行目から続く表を想定しています。
重複の基準と元データの保管
行全体の一致とキーの一致
キーとは、どの人・商品・記録かを見分けるために使う項目です。社員番号や注文番号などが候補ですが、番号が一意になる範囲も確認します。部署ごとに同じ番号を使うなら「部署と社員番号」、注文に複数の明細があるなら「注文番号と明細番号」の組み合わせが必要です。
行全体の重複は、比較するすべての項目が一致する状態です。一方、キーの重複は、キー以外に差があっても同じ一件として扱います。次の表では、A列をメールアドレス、B列を氏名、C列を更新日として考えてください。
| 行 | メールアドレス | 氏名 | 更新日 |
|---|---|---|---|
| 2 | a@example.com | 青木 | 2026/11/01 |
| 3 | a@example.com | 青木 | 2026/11/01 |
| 4 | a@example.com | 青木 | 2026/11/04 |
| 5 | b@example.com | 青木 | 2026/11/04 |
行全体を比べると2行目と3行目が重複です。メールアドレスだけなら2〜4行目が同じ組になります。氏名だけを選ぶと5行目まで同じになり、同姓同名の別人をまとめるおそれがあります。どの列を使うかで正解が変わることを、小さな例で確かめておきます。
申込履歴や購入履歴では、同じ人が何度も現れること自体が必要な記録です。「顧客一人につき一行の連絡先一覧」を作りたいなら、履歴を削って作り替えるより、元の履歴から別の一覧を作る方が目的に合います。重複が多いことと、データが間違っていることは同じではありません。
原本・作業用・結果を分ける
作業前にシートを複製し、「受領時」「作業用」「確認済み」など、役割が分かる名前を付けます。さらに重要なブックは別名でも保存しておくと、同じブック内で誤って原本を編集した場合に戻せます。保存先と版の付け方は、ファイル名の付け方と命名ルール｜日付・版番号・フォルダーで迷わない整理の手順の考え方も使えます。
作業用には「元の行番号」という列を追加し、2、3、4……の連番を値として入れておきます。並べ替えた後に元の記録へ戻れるためです。ROW関数などで作った番号は、そのままだと行位置に応じて変わるので、コピーして値として貼り付けてから使います。
条件付き書式で重複する値を見つける
一列の重なりを色で確認する
少量の名簿を目で確認したいときは、条件付き書式が使いやすい方法です。セルの内容を変えずに色を付けるため、同じキーの行にどのような違いがあるかを見比べられます。まずはメールアドレスなど、判定したい一列だけを対象にします。
- 見出しを除き、例えばA2:A100の範囲を選びます。
- 「ホーム」から「条件付き書式」を開きます。
- 「セルの強調表示ルール」の「重複する値」を選びます。
- 重複側の色を設定し、色の付いたセルと同じ行の内容を確認します。
このルールは、同じ値が複数あると最初の一件にも色が付きます。「色が付いている行をすべて消す」と、残すべき一件まで消えます。2回目以降だけを対象にしたい場合は、次の出現回数を使います。ルールの適用先は「ルールの管理」で確認でき、追加データが範囲に入っているかも点検します。
A列からC列までまとめて選ぶと、選択範囲内の個々のセルで重複する値を探します。行をひとまとまりに比較する機能にはなりません。同じ氏名や更新日が多い表では色だらけになるため、複数列の組み合わせを調べる用途にはCOUNTIFSが向いています。
色と削除の結果が食い違うとき
日本語などの編集言語設定では、この「重複する値」が半角と全角を同じものとして扱う場合があります。色が付いたからといって、別の方法でも同じ条件で一致するとは限りません。文字幅を区別する必要がある表では、実データの小さな範囲で確かめます。この扱いはMicrosoftの重複値に関する説明でも案内されています。
手作業で付けた色と条件付き書式の色が混ざると、何を示す色か分かりにくくなります。作業用シートで使い、確認後は対象ルールだけを消すと扱いやすいでしょう。色を付ける操作、絞り込む操作、削除する操作は、それぞれ別の操作として進めます。
COUNTIF・COUNTIFSで出現回数を数える
全体の回数を補助列に出す
出現回数を数値にすると、2件あるキーと10件あるキーを区別でき、フィルターでも絞れます。A2:A100にキーがあり、D列を補助列にする場合、D1を「出現回数」としてD2に次の式を入れ、最終行までコピーします。各例の100は実際の最終行に合わせてください。
=IF(A2="","",COUNTIF($A$2:$A$100,A2))COUNTIFは、範囲内で指定した条件に合うセルの数を返します。範囲の行・列をドル記号で固定しているので、下へコピーしても全体を数え続けます。一方、末尾のA2はA3、A4へ変わり、その行のキーが何回あるかを調べます。IFは空欄のキーを集計対象から外すために付けています。
結果が2以上の行を絞ると、最初の一件を含む重複グループが見えます。この回数を合計しても「削除できる件数」にはなりません。同じキーが3行あれば3行すべてに3と出るためです。キーが空欄の記録は、同一人物と決めずに別途確認します。
2回目以降だけを見つける範囲の固定
先頭の一行を残す候補にするなら、数える範囲を「データの先頭から現在行まで」に変えます。D2に次の式を入れて下へコピーします。
=IF(A2="","",COUNTIF($A$2:A2,A2))$A$2:A2は、始点を固定し、終点だけを動かす書き方です。2行目ではA2だけ、3行目ではA2:A3、4行目ではA2:A4を数えます。同じキーが続けて出てこなくても、1回目は1、2回目は2になります。2以上の行が、2回目以降の候補です。
結果を文字で示したい場合は、=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"重複候補","先頭"))と書けます。先に残す順番へ並べ替え、その順序で式を入れると分かりやすくなります。補助列を含めて再び並べ替えた場合は、範囲の参照が意図どおりか点検し、必要なら先頭の式から入れ直します。
複数列はCOUNTIFSで同じ行の組を数える
A列が部署、B列が社員番号なら、二列が両方とも一致した件数を数えます。二つの範囲は、開始行と終了行をそろえます。
=IF(OR(A2="",B2=""),"",COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2))2回目以降を調べる場合は、両方を$A$2:A2、$B$2:B2のように伸びる範囲へ変更します。この例ではどちらかのキーが欠けた行を空欄にしています。欠けた値も組み合わせとして扱う業務なら、その条件を別に決めてください。
単純にA列とB列を連結すると、「ABとC」と「AとBC」がどちらもABCになってしまいます。区切り記号にも元データと衝突する問題があるため、複数列の条件を合わせる用途ならCOUNTIFSの方が読み違いを防げます。記号を含むキーや長い番号には別の注意点があり、後半で扱います。
