エクセルで別シートと重複チェック！条件付き書式で色をつける方法を解説【初心者向け】

大量のデータを扱っていると、「あれ？これってさっきも入力したような……」と、うっかり同じ情報を何度も登録してしまうことってありますよね。
申し込みの名簿やアンケート集計などで、重複データが混ざっていると正確な判断ができなくなるため、早めにチェックしておくことが大切です。

でも、目でひとつずつ確認していくのはとても大変。
そんなときに便利なのが、別のシートにあるデータと重複しているかを自動でチェックして、色をつけてくれる方法です。

この記事では、初心者の方でも簡単に使える関数と条件付き書式の組み合わせを使って、別シートのデータと重複しているセルに色をつける方法をご紹介します。
さらに、作業を効率化したい方向けに、VBAを使った自動化の方法もあわせて解説します。

エクセルで別シートと重複しているデータを見つけるには？

重複データのチェックが必要になる場面とは？

エクセルを使っていると、「同じデータが2回以上入力されていないか」を確認したい場面がよくあります。
たとえば、こんなときに重複チェックが役立ちます。

• 会員登録や申込データで、同じ人が複数回申し込んでいないかを確認したい

• 2つの名簿に、同じ名前やIDが登録されていないかを照合したい

• 既存データと新規データを比較して、追加登録すべきかを判断したい

手作業で探すのはとても手間がかかるため、自動で色をつけてくれる仕組みがあると、作業がぐっと楽になります。

重複チェックの考え方｜「別シート」と「同じ内容」

別のシートにあるデータと重複しているかをチェックしたいとき、まず考えるべきは「何を基準に重複と判断するか」です。

たとえば、以下のようなパターンが考えられます：

• 名前がまったく同じ → 名前列で一致をチェック

• メールアドレスが重複していないか → メール列で一致をチェック

• ID番号が一意かどうか → ID列で一致をチェック

ここでポイントになるのが、「比較対象が別シートにある」という点です。

例：別シートのデータと照らし合わせたいとき

仮に、以下のような構成だとします：

• Sheet1：新しく入力したデータ一覧（名前など）

• Sheet2：すでに登録済みの名簿

このとき、「Sheet1のA列（名前）が、Sheet2のA列に存在するか？」を調べたい場合、COUNTIF関数が使えます。

=COUNTIF(顧客リスト!A:A, A2)

この数式を使えば、別シートの氏名と一致する件数を表示できます。
実際の画面は下図のようになります。

「1」と表示されていれば、その名前が顧客リストに登録済みです。

このCOUNTIF関数を条件付き書式と組み合わせれば、自動で色がつくように設定できます。次の項ではその手順を詳しく解説します！

COUNTIF関数で重複を見つけて色をつける方法

別シートにあるデータと照らし合わせて、重複しているセルだけに自動で色をつけるには、「COUNTIF関数 × 条件付き書式」の組み合わせがとても便利です。

以下の手順で設定していきましょう。

【手順①】条件付き書式を設定したい範囲を選択する

まずは、重複チェックをしたいセル範囲（例：Sheet1の名前リスト）を選択します。

例：新規登録!A2:A100 を選択

【手順②】「条件付き書式」を開く

1. 「ホーム」タブ → 「条件付き書式」→「新しいルール」をクリック
   

2. 「数式を使用して、書式設定するセルを決定」を選ぶ
   

【手順③】COUNTIF関数を入力

次のように、別シートのデータと比較する数式を入力します。

=COUNTIF(顧客リスト!A:A, A2)>0

この数式の意味：

• 顧客リスト!A:A：名簿が記載されたシートの参照先

• A2：チェックしたい対象セル

• >0：同じデータが見つかったらTRUE（＝色がつく）

【手順④】書式（塗りつぶしの色など）を設定する

「書式」ボタンから、お好きな背景色を設定します（例：黄色など）。
設定が終わったら「OK」を押して完了です。

↓新しい書式ルールダイアログの書式ボタンをクリックします。

↓書式設定を行います。(今回はセルの色を黄色に塗りつぶし)設定したら、OKボタンを押します。

↓プレビューにて設定した書式を確認し、OKボタンを押します。

 結果：別シートに同じ値があるセルに自動で色がつく！

これで顧客リストにある名前が新規登録にも存在していれば、自動で色がついて視覚的にわかりやすくなります。

よくあるミスと注意点

条件付き書式を使った重複チェックはとても便利ですが、思ったように動作しないこともあります。
ここでは、ありがちなミスとその対処法を解説します。

相対参照と絶対参照の指定ミス

COUNTIF関数を条件付き書式で使う際、セルの参照が相対になっているかに注意しましょう。

例：

=COUNTIF(顧客リスト!$A:$A, A2)

上記のように、**「A2」部分は固定しない（$をつけない）**のがポイントです。
固定（$A$2）してしまうと、選択範囲全体に同じセルだけを参照してしまい、正しく動作しません。

空白セルにも色がついてしまう？

Excelのバージョンや設定によっては、空白のセルにまで色がついてしまうことがあります。
特に古いバージョンでは、COUNTIF関数が""（空白文字）も一致とみなす場合があり、不要なセルまで色がついてしまう原因になります。

その対策として、以下のように「空白でない場合のみ」という条件を加えるとより安心です：

=AND(A2<>"", COUNTIF(顧客リスト!$A:$A, A2)>0)

※最新のExcel（365など）では、空白セルは自動的に除外されて色がつかないこともあります。

 シート名の指定ミスに注意

別シートを参照する場合、シート名にスペースや日本語（全角文字など）が含まれていると、自動でシングルクォーテーション（’）で囲まれることがあります。

例：

=COUNTIF('登録済データ'!A:A, A2)

手入力する場合は、必要に応じて ‘シート名’! の形式で囲むように注意しましょう。

作業をもっと効率化！VBAや便利機能で重複チェック

条件付き書式での重複チェックはとても便利ですが、「もっと一気に処理したい」「クリックひとつで自動化したい」と思うこともありますよね。
そんなときに活躍するのが VBA（マクロ） です。

ここでは、重複しているデータに色をつけるVBAコードや、重複を削除する方法もあわせてご紹介します。

VBAで重複データに色をつけるコード例

以下のVBAコードを使えば、Sheet1のA列にあるデータが、Sheet2のA列に存在する場合に、そのセルに色を自動でつけることができます。

Sub 重複チェックで色付け()

　Dim ws1 As Worksheet, ws2 As Worksheet
　Dim rng1 As Range, cell As Range
　Dim 検索値 As String

　Set ws1 = Worksheets("Sheet1") 'チェック対象のシート
　Set ws2 = Worksheets("Sheet2") '照合するシート

　Set rng1 = ws1.Range("A2:A100") 'チェック対象の範囲（変更可）

　For Each cell In rng1
　　If cell.Value <> "" Then
　　検索値 = cell.Value
　　　If Application.WorksheetFunction.CountIf(ws2.Range("A:A"), 検索値) > 0 Then
　　　　cell.Interior.Color = RGB(255, 200, 200) '薄い赤で塗りつぶし
　　　Else
　　　　cell.Interior.ColorIndex = xlNone '色をクリア
　　　End If
　　End If
　Next cell

End Sub

【使い方メモ】

• 開発タブ → 「マクロ」→「新規作成」→ 上記コードを貼り付け

• Sheet1 と Sheet2 は、実際のシート名に合わせて変更してください

別記事で詳しく解説｜重複を探す・チェックする方法

この記事では「別シートと比較して色をつける方法」に絞って解説しましたが、次のようなケースについては関連記事でより詳しく紹介する予定です：

• 複数列を対象にした重複チェック（名前＋住所の組み合わせなど）

• 一致したデータを一覧に抽出する

• 入力と同時に重複チェックを行うマクロ

• 重複データを自動で削除するマクロ

まとめ｜自分に合った方法でミスを防ごう

データ数が多くなると、「重複しているかどうか」の確認作業はとても大変になります。
しかし、エクセルの機能をうまく使えば、手作業よりもずっと簡単に、しかも確実にチェックすることができます。

• 少量データや初心者の方 → COUNTIF関数と条件付き書式の組み合わせがおすすめ

• 自動化したい・何度も使いたい → VBA（マクロ）を使って色付けや削除を自動処理

また、今回のように別シートのデータと照らし合わせてチェックすることで、名簿の更新や申込データの照合などの作業がぐっと楽になります。

ご紹介した方法を参考に、ぜひご自身の業務に合ったやり方を見つけてみてくださいね。

【関連記事】

• Excelドロップダウンリストの基本とトラブル対処法まとめ｜表示されない・複数列表示・行数制限など
• VLOOKUP×ドロップダウンリストの連携術｜商品名を選ぶだけで価格を自動表示！
• エクセルでn列おき・n行おきにデータを参照する方法｜INDEX・OFFSETで簡単抽出＆合計！

※外部リンク※

• COUNTIF 関数 |Microsoft サポート
• Excel の条件付き書式を活用して視覚的に見やすい資料を作ろう |Microsoft For Business
• 条件付き書式を使用して Excel の情報を強調表示する |Microsoft サポート