名簿や申込一覧を受け取ったとき、同じ名前や番号が複数行に入っていないか目で追うのは手間がかかります。Excelの条件付き書式には「重複する値」を自動で色付けする機能があり、数式を書かずに設定できます。この記事では、その設定手順と、「どこまでを重複とみなすか」という判断基準を具体的に示します。
重複の色付けは、対象の列を選んでから条件付き書式のメニューを開くだけで設定できます。ただし、「1列だけで重複を探す」のか「複数列の組み合わせで重複を探す」のかによって設定方法が変わります。まずその違いを整理してから、それぞれの手順に進みます。
画面の表示はExcelの版や設定により異なります。以下の操作名は一般的な呼び名にとどめていますので、手元の画面と異なる場合は公式のヘルプで確認してください。
1列の重複を色付けする基本手順
最も多い使い方は、「メールアドレス列に同じアドレスが2件以上ある」「会員番号列に重複がある」といった、1列だけを対象にするケースです。
手順
- 色付けしたいセル範囲を選択する(例:A2:A100)。ヘッダー行は含めないほうが誤検知を防げます。
- リボンの「ホーム」タブにある「条件付き書式」をクリックする。
- 「セルの強調表示ルール」→「重複する値」を選ぶ。
- ダイアログで「重複」と書式(色)を選んで「OK」をクリックする。
これで、範囲内に2回以上現れる値のセルに色が付きます。1回しか現れない値は色付けされません。
架空の例です。 以下のようなメールアドレス列があるとします。
表は横にスクロールできます
| 行 | A列(メールアドレス) |
|---|---|
| 2 | tanaka@example.com |
| 3 | sato@example.com |
| 4 | tanaka@example.com |
| 5 | inoue@example.com |
A2:A5に上記のルールを設定すると、A2とA4(tanaka@example.com)に色が付きます。A3とA5は1件だけなので色付けされません。
「重複」と「一意」の違いを使い分ける
先ほどのダイアログでは「重複」と「一意」を切り替えられます。
- 重複:2回以上現れる値のセルに色を付ける。重複している行を全部見つけたいときに使います。
- 一意:1回だけ現れる値のセルに色を付ける。「ほとんど重複している中で、例外的に1件だけの行を探したい」ときに使います。
通常は「重複」を選べばよいですが、名簿の整理で「元から1件しかない行を残したい」ときは「一意」のほうが早く見分けられます。
複数列の組み合わせで重複を探す方法
「氏名と電話番号の両方が同じ行を重複とみなしたい」場合、1列ずつ色付けするだけでは足りません。氏名が同姓同名でも電話番号が違えば別人のことがあるからです。この場合は、COUNTIFS関数を使った数式ルールで対応します。
考え方
「A列とB列の両方が、他の行と一致している行」を探すには、COUNTIFS で条件を2つ重ねます。
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1
$A$2:$A$100はA列の検索範囲(絶対参照)A2は「現在の行のA列の値」(相対参照)- B列も同様に重ねる
>1は「2件以上ある=重複」を意味します
手順
- 色付けしたい範囲全体を選択する(例:A2:B100)。
- 「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選ぶ。
- 数式欄に上記の数式を入力する(参照先のセルは選択範囲の先頭行に合わせる)。
- 書式を設定して「OK」をクリックする。
架空の例です。 以下の名簿で試すとします。
表は横にスクロールできます
| 行 | A列(氏名) | B列(電話番号) |
|---|---|---|
| 2 | 山田 花子 | 090-1111-2222 |
| 3 | 山田 花子 | 090-3333-4444 |
| 4 | 鈴木 一郎 | 090-5555-6666 |
| 5 | 山田 花子 | 090-1111-2222 |
A2とA5は氏名が同じでも電話番号が違うため、B列を加えたCOUNTIFSではA2・B2の組み合わせとA5・B5の組み合わせだけが重複とみなされます(A3は氏名は同じでも電話番号が違うので重複にならない)。A列だけで判定した場合とは結果が変わります。
迷いやすい例外と対処
空白セルが重複扱いになる
空白セルが複数あると、空白同士が「重複」として色付けされることがあります。名簿にまだ入力されていない行が多い場合は、先に空白行を取り除いてから設定するか、COUNTIFS の数式に A2<>"" の条件を追加して空白を除外します。
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$A$2:$A$100,"<>")>1
大文字・小文字を区別したい
条件付き書式の「重複する値」ルールは、大文字・小文字を区別しません。「ABC」と「abc」は同じ値とみなされます。大文字・小文字を区別して重複を探したい場合は、EXACT関数を使った数式ルールが必要になります。通常の名簿整理では区別しなくてよいことが多いですが、英字のコードや識別子を扱う場合は事前に確認してください。
先頭・末尾のスペースによる見逃し
「tanaka@example.com」と「tanaka@example.com 」(末尾に半角スペース)は、Excelでは異なる値として扱われるため重複と判定されません。目視では気づきにくい原因の一つです。色付けをした後も重複が疑わしい場合は、TRIM関数でスペースを取り除いた補助列を作り、そちらで確認することを検討してください。
設定前後に確認しておくこと
条件付き書式を設定する前に、元のファイルを別名でコピーしておくと安心です。設定を誤った場合や、後でルールを変更したくなったときに、コピー元に戻せます。
既存のルールと重なる場合は、「条件付き書式」→「ルールの管理」から優先順位を確認してください。複数のルールが同じセルに適用されているとき、上位のルールが優先されます。重複の色付けが期待どおりに動かないときは、ルールの管理画面でほかのルールが干渉していないか確認するのが早道です。
個人情報を含む名簿を外部サービスや生成AIに入力して重複確認する場合は、情報の取り扱いルールを事前に確認してください。
次の行動
まず1列だけを対象に「重複する値」ルールを設定し、色付けの動きを確認してください。複数列の組み合わせで重複を探す必要があると判断したら、COUNTIFS を使った数式ルールに切り替えます。設定後は空白セルや先頭・末尾のスペースによる誤検知がないかも合わせて確認すると、見逃しを減らせます。
参考資料
この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。
