本記事には広告（PR）が含まれます。
くわしくは プライバシーポリシー をご確認ください。
Excelで名簿や申込データなどを管理していると、
「同じ人を2回登録していないかな？」
「別シートの名簿と重複しているデータを見つけたい」
「重複しているセルだけ自動で色をつけたい」
といったことがあります。
データが少なければ目で確認できますが、件数が増えてくると、名前順に並べ替えたり、一つずつ見比べたりするのは大変です。
Excelでは、**同じシート内の重複であれば条件付き書式の「重複する値」**を使って簡単に確認できます。
一方、別シートにある登録済みデータと照合したい場合は、COUNTIF関数と条件付き書式を組み合わせると便利です。
この記事では、Excel初心者の方向けに、
- 同じシート内の重複データに色をつける方法
- 別シートと重複しているデータを探す方法
- COUNTIF関数＋条件付き書式で自動的に色をつける方法
- 重複している行全体に色をつける方法
- VBAで重複チェックを自動化する方法
を順番に解説します。
この記事でできること
- 同じシート・別シートで重複チェックする方法がわかります。
- COUNTIF関数と条件付き書式で重複データに色をつけられます。
- 重複しているデータの行全体に色をつけられます。
- VBAで重複チェックを自動化する方法がわかります。
- 重複データの見落としを減らす方法がわかります。
Excelで重複データに色をつける方法｜同じシート・別シートの違い
Excelで重複データを探す場合は、同じシート内を確認するのか、別シートのデータと照合するのかによって方法が異なります。
同じシート内で重複している値を見つけるだけなら、条件付き書式に用意されている「重複する値」を使うのが簡単です。
一方、
- 新しく受け付けた申込データ
- すでに登録済みの顧客リスト
のように、別シート同士を比較したい場合はCOUNTIF関数を使って重複を判定できます。
同じシート内なら「重複する値」で簡単にチェックできる
1つの表の中に同じ値が複数入力されていないか確認したいだけなら、数式を作らなくても条件付き書式でチェックできます。
たとえば、A2に氏名が入力されている場合は、次の手順で設定します。
- 重複を確認したいセル範囲を選択する
- ［ホーム］タブを開く
- ［条件付き書式］をクリックする
- ［セルの強調表示ルール］→［重複する値］を選択する
- 色などの書式を指定して［OK］をクリックする
これで、選択した範囲の中に同じ値が複数ある場合、そのセルに自動で色がつきます。
「1つの名簿の中に同じ名前が何度も入力されていないか確認したい」といった場合は、この方法が手軽です。
ただし、別シートにあるデータとの重複を確認したい場合は、この方法だけでは対応できません。
そこで、COUNTIF関数を使って別シートと照合してみましょう。
エクセルで別シートと重複しているデータを見つけるには？
別シートとの重複チェックは、
「チェックしたい値が、照合先の範囲に何件存在するか」
を調べると考えるとわかりやすくなります。
たとえば、次のような2つのシートがあるとします。
- 新規登録：新しく受け付けた氏名
- 顧客リスト：すでに登録されている氏名
このような場合、
- 新規申込者がすでに顧客リストに登録されていないか
- 2つの名簿に同じ人がいないか
- 新規データを追加登録してよいか
といった確認ができます。
今回は、「新規登録」シートのA2セルに入力されている氏名が、「顧客リスト」シートのA列にも存在するか確認してみます。
次のCOUNTIF関数を入力します。
=COUNTIF(顧客リスト!A:A,A2)COUNTIF関数は、指定した範囲の中に条件と一致するセルが何個あるかを数える関数です。
今回の数式では、
顧客リスト!A:A：照合する「顧客リスト」シートのA列A2：探したい氏名
を指定しています。
顧客リストに同じ氏名が1件あれば「1」、なければ「0」が表示されます。
↓別シートの顧客リストと照合し、同じ氏名が何件あるかCOUNTIF関数で確認できます。
名前だけでなく、メールアドレス・会員ID・社員番号・商品コードなどを基準にしてチェックすることも可能です。
ただし、データが多い場合は、COUNTIF関数の結果を一つずつ確認するのも大変ですよね。
そこで、COUNTIF関数と条件付き書式を組み合わせ、重複しているデータへ自動的に色をつけてみましょう。
COUNTIF関数と条件付き書式で重複データに色をつける方法
別シートにあるデータと照らし合わせて、重複しているセルだけに自動で色をつけたい場合は、条件付き書式にCOUNTIF関数を設定します。
今回は、
- 新規登録シート：A2
- 顧客リストシート：A列
を比較する例で解説します。
【手順①】条件付き書式を設定したい範囲を選択する
まず、色をつけたいセル範囲を選択します。
今回の例では、
A2:A100を選択します。
「新規登録」シートに入力した氏名を、顧客リストと比較する想定です。
【手順②】「条件付き書式」を開く
［ホーム］タブから、
［条件付き書式］→［新しいルール］
をクリックします。
続いて、
［数式を使用して、書式設定するセルを決定］
を選択します。
【手順③】COUNTIF関数を入力する
数式の入力欄に、次の式を設定します。
=COUNTIF(顧客リスト!$A:$A,A2)>0
それぞれの意味は次のとおりです。
顧客リスト!$A:$A：照合する顧客リストのA列A2：現在チェックしている氏名>0：同じ値が1件以上あれば条件成立
つまり、
顧客リストのA列にA2と同じ値が1件以上あれば色をつける
という設定です。
【手順④】塗りつぶしの色を設定する
［書式］ボタンをクリックします。
［塗りつぶし］タブなどから、重複データに使用したい色を選択してください。
今回は、重複しているセルがわかりやすいように黄色を設定します。
設定したら［OK］をクリックします。
↓［新しい書式ルール］ダイアログの［書式］をクリックします。
↓重複データに使用する色を設定します。今回は黄色で塗りつぶしています。
↓プレビューで書式を確認し、［OK］をクリックします。
【結果】別シートに同じ値があるセルに自動で色がつく
設定が完了すると、「顧客リスト」に同じ氏名が存在する場合、「新規登録」シートの氏名に自動で色がつきます。
これならデータ数が多くても、どのデータが重複しているのか視覚的に見つけやすくなります。
新しいデータを入力したときにも条件に一致すれば色がつくため、登録前の確認にも便利です。
重複しているデータの行全体に色をつける
氏名だけでなく、
- 氏名
- 電話番号
- 住所
- 申込日
など、重複している人の行全体を目立たせたい場合もあります。
その場合は、条件付き書式を適用する範囲を広げます。
たとえば、
- A列：氏名
- B列：電話番号
となっていて、A2全体に色をつけたい場合は、
A2:B100を選択した状態で条件付き書式を設定します。
数式は次のようにします。
=COUNTIF(顧客リスト!$A:$A,$A2)>0
先ほどとの違いは、
$A2となっている点です。
$A2では、A列だけを固定し、行番号は変化するように設定しています。
そのため、
- 2行目ではA2
- 3行目ではA3
- 4行目ではA4
を基準に判定しながら、その行全体へ同じ書式を適用できます。
たとえば氏名がA列、電話番号がB列、住所がC列まである場合は、適用範囲をA2に広げても同じ考え方で設定できます。
重複しているセルだけでは見落としそうな場合は、行全体に色をつけると確認しやすくなります。
色がつかない・空白まで色がつくときの確認ポイント
COUNTIF関数と条件付き書式を設定しても、
「重複しているのに色がつかない」
「空白セルまで色がついてしまう」
といったことがあります。
その場合は、数式や参照方法を確認してみましょう。
相対参照と絶対参照の指定ミス
条件付き書式では、どこを固定し、どこを変化させるのかが重要です。
氏名セルだけに色をつける場合は、たとえば次のように設定します。
=COUNTIF(顧客リスト!$A:$A,A2)>0照合先のA列は、
$A:$Aとして固定しています。
一方、チェックするセルは、
A2として固定しません。
ここを、
$A$2としてしまうと、選択した範囲すべてでA2だけを確認することになり、正しく判定できません。
行全体を色付けする場合は、
$A2のように、判定に使う列だけ固定します。
空白セルにも色がついてしまう？
参照先にも空白セルが含まれている場合など、設定する条件によっては空白セルまで対象になることがあります。
空白セルを確実に除外したい場合は、
チェックするセルが空白ではない
という条件も加えます。
=AND(A2<>"",COUNTIF(顧客リスト!$A:$A,A2)>0)A2<>""は、
A2が空白ではない
という意味です。
これにより、
- A2が空白ではない
- 顧客リストに同じ値が存在する
という2つの条件を満たした場合だけ色をつけられます。
空白セルまで色がついてしまう場合は、この数式を試してみてください。
シート名の指定ミスに注意
別シートを数式から参照するときは、シート名にも注意が必要です。
通常は、
=COUNTIF(顧客リスト!A:A,A2)のように指定できます。
一方、シート名にスペースや一部の記号が含まれている場合は、シート名がシングルクォーテーション（'）で囲まれます。
たとえば、「登録済 データ」というシートであれば、
=COUNTIF('登録済 データ'!A:A,A2)のようになります。
シートをクリックして参照先を指定すると、必要な形式で入力されるため、手入力に不安がある場合はシートを選択しながら数式を作成するとわかりやすいです。
VBAで別シートとの重複チェックを自動化する方法
COUNTIF関数と条件付き書式を使えば、マクロを使わなくても重複データを確認できます。
ただ、
「毎回同じ範囲をチェックしている」
「ボタンを押すだけでまとめて確認したい」
という場合は、VBAで自動化する方法もあります。
ここでは、「新規登録」シートの氏名が「顧客リスト」に存在する場合、そのセルへ色をつける例を紹介します。
VBAで重複データに色をつけるコード例
次のコードを標準モジュールへ入力します。
Sub 重複チェックで色付け()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim cell As Range
Dim 検索値 As Variant
Set ws1 = Worksheets("新規登録") 'チェック対象
Set ws2 = Worksheets("顧客リスト") '照合先
Set rng1 = ws1.Range("A2:A100") 'チェックする範囲
For Each cell In rng1
If cell.Value = "" Then
cell.Interior.ColorIndex = xlNone
Else
検索値 = 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このコードでは、
Set ws1 = Worksheets("新規登録")でチェック対象のシート、
Set ws2 = Worksheets("顧客リスト")で照合するシートを指定しています。
続いて、
Set rng1 = ws1.Range("A2:A100")でチェックする範囲を指定しています。
顧客リストのA列に同じデータが見つかった場合は、該当セルを薄い赤色で塗りつぶします。
同じデータが見つからなかった場合やセルが空白の場合は、塗りつぶしを解除します。
【使い方の例】
- ［開発］タブからVisual Basic Editorを開く
- 標準モジュールを挿入する
- コードを貼り付ける
- Excelへ戻る
- マクロを実行する
シート名やセル範囲は、実際に使用するファイルに合わせて変更してください。
VBAを使う場合の注意点
VBAを使う場合は、いくつか注意があります。
まず、コード内のシート名と実際のシート名が一致している必要があります。
Set ws1 = Worksheets("新規登録")
Set ws2 = Worksheets("顧客リスト")実際のファイルで別の名前を使用している場合は書き換えてください。
チェック範囲についても、
Set rng1 = ws1.Range("A2:A100")となっているため、100行を超えるデータを確認したい場合は範囲を変更する必要があります。
また、VBAコードを保存して使い続ける場合は、通常の.xlsxではなく、**Excelマクロ有効ブック（.xlsm）**などマクロを保存できる形式を使用します。
初めてマクロを試す場合は、重要なファイルへいきなり設定せず、コピーしたファイルやサンプルデータで動作を確認してから使用すると安心です。
単純に別シートとの重複を色で確認するだけなら、COUNTIF関数＋条件付き書式の方が設定しやすく、初心者にもおすすめです。
同じ処理を何度も繰り返す場合など、必要に応じてVBAを使い分けてみてください。
複数列・抽出・重複削除は別記事で解説
この記事では、別シートと比較して、重複しているデータを見つけたり色をつけたりする方法を中心に紹介しました。
重複チェックには、このほかにも、
- 「氏名＋住所」のように複数列を組み合わせて重複を判断したい
- 別シートと一致したデータだけ一覧に抽出したい
- 入力した時点で重複していることを知らせたい
- VBAで重複データをまとめて削除したい
といったケースがあります。
特にデータの削除は、必要なデータまで誤って消してしまわないよう注意が必要です。
まずは今回紹介したように、重複しているデータを色で確認してから、必要なデータかどうか判断する方法が安心です。
複数列での重複チェック・一致データの抽出・VBAによる重複削除などについては、関連記事で詳しく紹介していきます。
Excel作業をもっと効率化したい方へ（PR）
「条件付き書式をもっと使いこなしたい」
「毎回繰り返しているExcel作業を少しでも楽にしたい」
そんな方は、数式や条件付き書式などを使ったマクロ不要のExcel自動化をまとめて学んでみる方法もあります。
COUNTIF関数だけでなく、入力規則・条件付き書式・データ集計・文字列操作など、実務で使いやすい機能をまとめて学べる講座です。
まとめ｜重複データは条件付き書式で見つけやすくできる
Excelで重複データを探す方法は、同じシート内を確認する場合と、別シートを照合する場合で異なります。
同じ範囲内の重複であれば、
［条件付き書式］→［重複する値］
を使うと簡単です。
別シートにあるデータと比較したい場合は、
=COUNTIF(顧客リスト!$A:$A,A2)>0のように、COUNTIF関数と条件付き書式を組み合わせると便利です。
また、
- セルだけではなく行全体に色をつける
- 空白セルを除外する
- VBAで繰り返し作業を自動化する
といった方法もあります。
まずは条件付き書式で重複データを見つけやすくし、必要に応じてVBAなどの自動化も取り入れてみてください。
よくある質問（FAQ）
Excelで同じシート内の重複データに色をつけるには？
同じシート・同じ範囲内の重複であれば、［ホーム］→［条件付き書式］→［セルの強調表示ルール］→［重複する値］を選択すると簡単に色をつけられます。
別シートとの重複を確認する場合は、COUNTIF関数と条件付き書式を組み合わせる方法が便利です。
Excelで別シートと重複チェックするには？
COUNTIF関数を使うことで、別シートに同じデータが存在するか確認できます。
たとえば、
=COUNTIF(顧客リスト!A:A,A2)とすれば、A2と同じ値が「顧客リスト」シートのA列に何件存在するか確認できます。
別シートと重複しているデータだけ色をつけられますか？
できます。
条件付き書式で次のような数式を設定します。
=COUNTIF(顧客リスト!$A:$A,A2)>0別シートに同じ値が1件以上ある場合だけ条件が成立し、指定した色がつきます。
重複している行全体に色をつけられますか？
できます。
たとえばA列の氏名を基準に行全体を判定する場合は、
=COUNTIF(顧客リスト!$A:$A,$A2)>0のように、判定するA列だけを固定します。
そのうえで条件付き書式の適用範囲をA2などに広げると、重複している行全体へ色をつけられます。
空白セルまで色がつく場合はどうすればよいですか？
空白セルを除外する条件を追加します。
=AND(A2<>"",COUNTIF(顧客リスト!$A:$A,A2)>0)これで、A2が空白ではなく、かつ顧客リストにも同じ値が存在する場合だけ色がつきます。
VBAで別シートとの重複チェックを自動化できますか？
できます。
VBAを使えば、別シートとデータを照合し、重複しているセルへまとめて色をつけることも可能です。
ただし、単純な重複チェックであればCOUNTIF関数と条件付き書式でも対応できるため、まずはマクロを使わない方法から試すとわかりやすいでしょう。
【関連記事】
- Excelドロップダウンリストの基本とトラブル対処法まとめ｜表示されない・複数列表示・行数制限など
- VLOOKUP×ドロップダウンリストの連携術｜商品名を選ぶだけで価格を自動表示！
- エクセルで土日・祝日に自動で色を付ける方法｜条件付き書式で表・カレンダーを見やすく！
※外部リンク※
- COUNTIF 関数 |Microsoft サポート
- Excel の条件付き書式を活用して視覚的に見やすい資料を作ろう |Microsoft For Business
- 条件付き書式を使用して Excel の情報を強調表示する |Microsoft サポート
※参考書籍 （PR）※
※本ボックスにはアフィリエイトリンク（PR）が含まれます。
くわしくは プライバシーポリシー へ。