Excelの一覧表で、同じ番号が二重に登録されていないか確認したいことはありませんか？ 一つずつ探さなくても、条件付き書式を使えば、重複している値に色を付けて見つけやすくできます。
確認したい範囲を選び、［ホーム］→［条件付き書式］→［セルの強調表示ルール］→［重複する値］を選ぶのが基本の操作です。データを消さずに確認できるので、削除するか判断する前のチェックに向いています。
この記事では、注文番号を使った練習用データで、色付けの手順と結果の見方を紹介します。対象はWindowsのデスクトップ版Excelです。Microsoft 365版・Excel 2024などを想定しており、画面の表記はバージョンによって異なる場合があります。
まずは重複する値に色を付けてみよう
- 重複を調べたい列の、見出しを除いたデータ範囲を選びます。
- ［ホーム］→［条件付き書式］→［セルの強調表示ルール］→［重複する値］を開きます。
- 左側が［重複］になっていることを確認し、右側で色などの書式を選びます。
- ［OK］を押し、どの値に色が付いたか確認します。
ここで使うのは［重複する値］です。［データ］タブにある［重複の削除］とは別の機能なので、取り違えないようにしましょう。色付けだけなら、セルの値や行は削除されません。
練習用データで注文番号の重複を確認する
次のデータをコピーして、新しいExcelブックのA1セルに貼り付けてください。項目の間はタブで区切ってあります。これは説明用に作ったサンプルで、実際の注文データではありません。
注文番号 商品名 数量
R1001 ノート 10
R1002 ペン 5
R1001 ノート 10
R1003 ファイル 8
R1002 消しゴム 3
R1004 ふせん 12注文番号がA列、商品名がB列、数量がC列に分かれていれば準備できています。この例では、A2:A7だけを選んで、先ほどの［重複する値］を設定します。
範囲を選ぶときは、数式バーの左側にある名前ボックスへA2:A7と入力し、Enterキーを押す方法も使えます。A1の見出しや、商品名・数量の列が選ばれていないことを確認してから設定しましょう。
| 注文番号 | 出現回数 | 色付けの対象 |
|---|---|---|
| R1001 | 2回 | A2・A4 |
| R1002 | 2回 | A3・A6 |
| R1003 | 1回 | 対象外 |
| R1004 | 1回 | 対象外 |
R1001とR1002の計4セルに色が付きます。「後から登録された方だけに色が付く」と思うと結果を読み違えるので、最初のセルも含めて確認してください。
値を変えて、色付けの変化を確かめよう
- 練習用データのA4を、
R1001からR1005へ変更します。 - R1001は1回だけになるので、A2とA4の重複による色付けが外れることを確認します。R1002が入ったA3とA6は、引き続き対象です。
- 確認したら、Ctrl＋Zで入力を元に戻します。A4がR1001に戻った状態で、次の説明へ進みましょう。
色が付いても、すぐに削除しない
注文番号が同じことと、行全体が同じことは別です。このサンプルのR1001は商品名・数量も同じですが、R1002は「ペン」と「消しゴム」で内容が異なります。
一つの注文に複数の商品が含まれる一覧なら、同じ注文番号が複数行にあるのは自然です。一方、注文番号を1行に1件だけ登録する決まりなら、重複は確認すべき候補になります。先に「この一覧では何が同じなら重複扱いなのか」を決めましょう。
例えば、受付番号の二重登録チェックや、商品コード一覧の確認に使えます。ただし、同じ名前の人や、同じ商品を買った別々の注文まで不要なデータと決めつけないことが大切です。後で削除する場合は、元データを別のシートやブックにコピーしてから作業してください。
応用：注文番号を基準に行全体へ色を付ける
商品名や数量もまとめて見たいときは、注文番号が重複している行のA～C列に色を付ける方法もあります。ここでは、先ほどのサンプルを使います。基本の色付けだけで十分な場合は、この設定を追加する必要はありません。
応用を試す前に、［ホーム］→［条件付き書式］→［ルールの管理］で、先ほどA2:A7に設定した［重複する値］のルールだけを削除し、［OK］で確定してください。基本と応用の色付けが重ならないようにするためです。ほかのルールや、セルのデータは削除しません。
- A2セルを起点に、A2:C7を選びます。
- ［ホーム］→［条件付き書式］→［新しいルール］を開きます。
- ［数式を使用して、書式設定するセルを決定］を選び、下の数式を入力します。
- ［書式］で塗りつぶしの色を指定し、［OK］で確定します。
=AND($A2<>"",COUNTIF($A$2:$A$7,$A2)>1)COUNTIFは、指定した範囲に条件に合う値が何件あるかを数える関数です。この式では、A2:A7にその行の注文番号が2件以上あり、注文番号が空白ではないときに色を付けます。
$A2は「注文番号を見る列はA列のまま、行番号は各行に合わせて変える」という指定です。［ルールの管理］で適用先が=$A$2:$C$7になっていることも確認してください。適用先の先頭が2行目なので、式も$A2から始めます。ここがずれると別の行を判定してしまいます。サンプルでは2・3・4・6行目のA～C列が対象になります。
これは注文番号の重複を基準に、行へ色を付ける設定です。3列すべてが同じ行を探す式ではありません。自分の表に合わせて、判定する列・検索範囲・適用先を変えてください。番号に「*」や「?」が含まれる場合、COUNTIFでは特殊な検索文字として扱われるため、この式をそのまま使わないでください。
色が付かない・意図しないセルに付くとき
確認したい列だけを選んでいるか
［重複する値］は、選択した範囲内の値を確認します。注文番号だけを調べたいのに表全体を選ぶと、商品名や数量の重複にも色が付く可能性があります。まずは番号の列だけで試すと、結果を理解しやすくなります。
追加した行が適用範囲に入っているか
行を追加したら、［条件付き書式］→［ルールの管理］で適用先を確認します。今回の検索範囲は7行目までです。応用の数式を使う場合は、適用先だけでなく、COUNTIF内の検索範囲もデータの末尾まで広げてください。
見た目が同じでも表記がそろっているか
余分なスペースや、数字・文字の表記の違いがないか、数式バーで確認しましょう。色付けは文字の厳密な一致を保証する検査ではありません。Microsoftの説明では、日本語などの編集言語設定で、標準の［重複する値］が半角・全角を同じように扱う場合もあります。厳密な区別が必要なコードは、判定方法も含めて確認してください。
色付けをやめるときは、そのルールを削除する
対象範囲を選び、［ホーム］→［条件付き書式］→［ルールの管理］を開きます。今回追加したルールを選んで［ルールの削除］を押し、［適用］または［OK］で確定してください。見つからない場合は、ルールの表示対象を［このワークシート］に切り替えて探します。
セルの塗りつぶしを「色なし」にするだけでは、条件付き書式のルールは消えません。また、シート全体のルールをまとめて消すと、ほかの色分けも解除されます。不要になったルールだけを選ぶと安心です。
まとめ：削除する前の確認に使ってみよう
重複データを見つけたいときは、まず確認したい列を選び、［条件付き書式］の［重複する値］を試してみましょう。色が付いたセルを手掛かりに、二重登録なのか、必要な複数行なのかを判断できます。
色が付くことは「削除してよい」という意味ではありません。最初はこの記事のサンプルで結果を確かめてから、自分の一覧へ設定してみてください。
関連記事・参考資料
一覧表を管理しやすくしたい場合は、ExcelのCtrl＋Tでテーブルを作成する方法も参考にしてください。
コメント