【Excel】重複する値に色を付ける方法|全件と2回目以降の見分け方

重複する値に色を付ける方法は、元データを消さずに確認したいときに向いています。ただし、同じ顧客が複数回注文する表などでは、重複が正常なこともあります。どの列が一意であるべきかを決めてから、色の付いた行を点検します。

1. 全件を色付けするか、2回目以降だけかを決める

A2からP01、P02、P01と並ぶ場合、標準の重複ルールはP01の2セルを両方色付けします。後から追加された分だけを確認したい場合は、先頭から現在行までをCOUNTIFで数える数式ルールを使います。

確認したいこと	選ぶ方法
同じコードの組み合わせ全体	標準の「重複する値」
上から2回目以降の出現	先頭から現在行までを数える式
同じ注文の二重登録	注文番号など業務上のキーで判定

2. 標準の重複ルールを設定する

1. 見出しを除いた対象範囲、たとえばA2:A100を選びます。
2. 「ホーム」→「条件付き書式」→「セルの強調表示ルール」→「重複する値」を開きます。
3. 読みやすい文字色・背景色を選んで確定します。
4. 色の付いたセルを原本やほかの列と照合します。

列全体へ広げる前に、必要な行だけで試します。後から追加した行も対象にするには、ルールの適用先を確認する必要があります。

3. 2回目以降は数式で判定する

A2を先頭とするA2:A100へ適用するなら、「新しいルール」で数式を選び、次の式を指定します。

=AND(A2<>"",COUNTIF($A$2:A2,A2)>1)

開始の$A$2は固定し、終わりのA2だけを行に合わせて伸ばします。空欄は除外します。この式で色が付く順番は、現在の並び順に依存します。最初の行が必ず正しい登録だという意味ではありません。

4. 全角・半角や記号の判定を決めつけない

日本語などの編集言語では、標準の「重複する値」が全角・半角を同じものとして扱う場合があります。「Excelは必ず区別する」とは考えないでください。*や?もワイルドカードとして働くため、これらを含むコードは実データの例で判定を確かめます。

空白の整理やASCによる変換も、コードの区別を壊すことがあります。元の列を残し、どの違いを無視してよいか決めてから処理します。

5. 複数列を合わせて判断する

氏名だけでは同姓同名を区別できません。社員番号、注文番号、日付など、何をもって同じ登録とするかを確認します。複数条件が必要ならCOUNTIFSなどで補助列を作り、結果が正しいか見てから条件付き書式へ使います。

見た目をそろえるためだけに複数列の文字を連結すると、区切りが曖昧になる場合があります。たとえばABとC、AとBCの区別を保てるかも検討してください。

6. 色を消す操作とデータ削除を分ける

確認が終わったルールは「条件付き書式」の「ルールの管理」で適用先と条件を確認して削除します。値貼り付けは、条件付き書式を確実に消す操作ではありません。ほかの警告色まで消さないよう、対象のルールを選びます。

7. 重複の削除は必要な行を判断してから行う

色が付いた行をすぐに削除せず、重複が不要なものかを判断します。削除する場合は原本を保存し、判定に使う列、残る行、前後の件数と合計を確認してください。色付けは確認の入口であり、登録内容の正誤を自動判定する機能ではありません。

参考: Microsoft公式: 条件付き書式、重複の検索と削除

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】