エクセルで作業していると、「別シートの重複データに色をつけたい」「2つの表で一致するデータを強調表示したい」「重複する行全体に色付けしたい」「両方のシートで重複箇所を色分けしたい」といった場面に遭遇することはありませんか? 目視で重複を探すのは非効率で、見落としも発生しやすいでしょう。
この記事では【Excel】エクセルで別シートの重複データに色をつける（2つの表で一致・行ごとに色付けも・両方のシートも・条件付き書式とCOUNTIF関数）について解説していきます。
ポイントは
・条件付き書式とCOUNTIF関数で別シートの重複を自動色付け
・数式を使って行全体に色をつける設定
・両方のシートに異なる色で重複を強調表示
です。
それでは詳しく見ていきましょう。
エクセルで条件付き書式を使って別シートの重複データに色をつける方法1【基本の設定】
エクセルで別シートの重複データに色をつける最も基本的な方法は、条件付き書式とCOUNTIF関数を組み合わせて、一致するデータを自動的に強調表示することです。
この方法なら、データが追加・変更されても自動的に色が更新されるため非常に便利でしょう。
例えば、以下のような2つのシートがあるとします。
**シート1（顧客リストA）**
**シート2（顧客リストB）**
2つのリストで重複している顧客IDに色をつけて、どのデータが両方に存在するか一目で分かるようにしたい場合に有効です。
解決方法
まず、シート1（顧客リストA）を開き、色をつけたい範囲（例えばA2からA10）を選択しましょう。
「ホーム」タブをクリックして、「条件付き書式」→「新しいルール」を選択してください。
「新しい書式ルール」ダイアログが開いたら、「数式を使用して、書式設定するセルを決定」を選択します。
「次の数式を満たす場合に値を書式設定」のボックスに、以下の数式を入力してください。
=COUNTIF(顧客リストB!$A$2:$A$100,A2)>0
この数式の構造を説明すると、以下のようになります。
– COUNTIF(顧客リストB!$A$2:$A$100,A2)：シート2の範囲内でA2の値が何回出現するかをカウント
– >0：1回以上出現する（つまり重複している）場合にTRUEを返す
– 顧客リストB!$A$2:$A$100の範囲は絶対参照（$マーク付き）にする
– A2は相対参照（$マークなし）にして、各行で参照先が変わるようにする
「書式」ボタンをクリックして、好きな色を設定してください。例えば、「塗りつぶし」タブで黄色を選択します。
「OK」を2回クリックして設定を完了すると、シート2に存在する顧客IDだけが黄色で強調表示されるわけです。
データが追加・変更されると、自動的に色が更新されます。シート2にA001を追加すれば、シート1のA001も自動的に黄色になるでしょうか?
複数列に適用したい場合は、A2からB10までの範囲を選択してから同じ手順を実行し、数式を以下のように変更します。
=COUNTIF(顧客リストB!$A$2:$A$100,$A2)>0
$A2のように列を絶対参照にすることで、B列にも同じ条件が適用されます。
【操作のポイント:条件付き書式とCOUNTIF関数で別シートの重複を自動的に色付け】
エクセルで重複する行全体に色をつける方法2【行ごとの強調表示】
特定のセルだけでなく、重複している行全体に色をつけたい場合もあるでしょう。
そんな時に役立つのが、条件付き書式で行全体を参照して、重複行をまるごと色付けする方法です。
この方法なら、重複しているデータ行が一目で分かりやすくなります。
顧客IDが重複している場合、その行全体（顧客名や住所なども含めて）に色をつけたい場合に有効です。
解決方法
シート1（顧客リストA）を開き、色をつけたい範囲全体を選択しましょう。例えば、A2からD10までを選択します。
「ホーム」タブ→「条件付き書式」→「新しいルール」を選択してください。
「数式を使用して、書式設定するセルを決定」を選択し、以下の数式を入力します。
=COUNTIF(顧客リストB!$A$2:$A$100,$A2)>0
この数式のポイントは、$A2の部分です。列を絶対参照（$A）、行を相対参照（2）にすることで、各行でA列の値を参照しながら、B列、C列、D列にも同じ条件が適用されるわけです。
例えば、B2セルでは$A2を参照、C2セルでも$A2を参照、A3セルでは$A3を参照というように、行ごとに判定基準が変わります。
「書式」ボタンで好きな色を設定して「OK」をクリックすれば、シート2に存在する顧客IDの行全体が色付けされます。
複数の列で重複を判定したい場合は、AND関数を使います。
=AND(COUNTIF(顧客リストB!$A$2:$A$100,$A2)>0,COUNTIF(顧客リストB!$B$2:$B$100,$B2)>0)
この数式なら、顧客IDと顧客名の両方が一致する場合のみ、行全体に色がつくでしょうか?
OR条件（いずれかが一致）にしたい場合は、以下のように記述します。
=OR(COUNTIF(顧客リストB!$A$2:$A$100,$A2)>0,COUNTIF(顧客リストB!$B$2:$B$100,$B2)>0)
【操作のポイント:列を絶対参照・行を相対参照にして行全体に条件付き書式を適用】
エクセルで両方のシートの重複データに色をつける方法3【双方向の強調表示】
片方のシートだけでなく、両方のシートで重複データに色をつけたい場合もあります。
そんな時には、各シートに条件付き書式を設定して、両方のシートで重複箇所を強調表示する方法が効果的です。
異なる色を使えば、どちらのシートから見ても重複が分かりやすくなるでしょう。
シート1とシート2の両方で重複データを強調表示し、相互に確認できるようにしたい場合に有効です。
シート1の設定方法
シート1で色をつけたい範囲（A2からD10）を選択し、前述の方法で条件付き書式を設定します。
=COUNTIF(顧客リストB!$A$2:$A$100,$A2)>0
書式の色は例えば「黄色」に設定してください。
シート2の設定方法
次に、シート2に移動して、同じように色をつけたい範囲（A2からD10）を選択します。
「ホーム」タブ→「条件付き書式」→「新しいルール」を選択し、以下の数式を入力してください。
=COUNTIF(顧客リストA!$A$2:$A$100,$A2)>0
注意点として、シート2の数式ではシート1（顧客リストA）を参照します。つまり、各シートで「相手のシートに存在するか」を判定するわけです。
書式の色は例えば「緑色」に設定すれば、シート1とシート2で異なる色で重複が表示され、どちらから見ても分かりやすくなります。
同じ色にしたい場合は、両方のシートで同じ色を設定してください。
重複していないデータの色付け
逆に、重複していない（片方のシートにしかない）データに色をつけたい場合は、数式を以下のように変更します。
=COUNTIF(顧客リストB!$A$2:$A$100,$A2)=0
この数式なら、シート2に存在しないデータだけが色付けされます。両方のシートで設定すれば、各シート固有のデータを強調表示できるでしょうか?
3つ以上のシートで重複チェック
3つのシートで重複を確認したい場合は、OR関数を使います。
=OR(COUNTIF(顧客リストB!$A$2:$A$100,$A2)>0,COUNTIF(顧客リストC!$A$2:$A$100,$A2)>0)
この数式なら、シートBまたはシートCのいずれかに存在する場合に色がつきます。
【操作のポイント:各シートで相手のシートを参照する条件付き書式を設定して双方向で強調表示】
エクセルで2つの表で完全一致する行に色をつける方法4【複数列の一致判定】
単一の列だけでなく、複数の列が完全に一致する行だけに色をつけたい場合もあります。
そんな時には、COUNTIFS関数を使って複数列の一致を判定して色付けする方法が必要です。
この方法なら、より厳密な重複チェックができるでしょう。
顧客IDと顧客名の両方が完全に一致する行だけに色をつけたい場合に有効です。
解決方法
シート1で色をつけたい範囲（A2からD10）を選択し、「ホーム」タブ→「条件付き書式」→「新しいルール」を選択してください。
「数式を使用して、書式設定するセルを決定」を選択し、以下の数式を入力します。
=COUNTIFS(顧客リストB!$A$2:$A$100,$A2,顧客リストB!$B$2:$B$100,$B2)>0
この数式の構造を説明すると、以下のようになります。
– COUNTIFS関数：複数の条件をすべて満たす行をカウント
– 顧客リストB!$A$2:$A$100,$A2：シート2のA列が、シート1のA列と一致
– 顧客リストB!$B$2:$B$100,$B2：シート2のB列が、シート1のB列と一致
– 両方の条件を満たす場合のみカウントされる
「書式」ボタンで色を設定して「OK」をクリックすれば、顧客IDと顧客名の両方が一致する行だけが色付けされるわけです。
3列以上の一致判定も可能です。
=COUNTIFS(顧客リストB!$A$2:$A$100,$A2,顧客リストB!$B$2:$B$100,$B2,顧客リストB!$C$2:$C$100,$C2)>0
この数式なら、A列、B列、C列のすべてが一致する場合のみ色がつきます。
部分一致ではなく完全一致を判定したい場合は、そのままこの数式で問題ありません。大文字と小文字を区別したい場合は、EXACT関数を組み合わせますが、通常はCOUNTIFS関数で十分でしょうか?
【操作のポイント:COUNTIFS関数で複数列の完全一致を判定して厳密な重複チェック】
まとめ エクセルで別シートの重複データに色をつける（2つの表で一致・行ごとに色付けも・両方のシートも）
エクセルで別シートの重複データに色をつける方法をまとめると、条件付き書式とCOUNTIF関数を組み合わせれば別シートの重複を自動的に色付けでき、データの追加や変更時も自動で色が更新されるため非常に効率的です。
また、列を絶対参照・行を相対参照にすれば行全体に条件付き書式を適用でき、重複している行をまるごと強調表示できるため視認性が向上し、AND関数やOR関数で複数列の条件も組み合わせられます。
さらに、各シートで相手のシートを参照する条件付き書式を設定すれば双方向で強調表示でき、異なる色を使えば各シートから見ても重複が分かりやすく、COUNTIFS関数を使えば複数列の完全一致を判定して厳密な重複チェックが可能です。
これらの方法を状況に応じて使い分けていけば、ほとんどの重複データの色付け問題を解決できます。
特に条件付き書式とCOUNTIF関数の組み合わせは最も基本的で重要な手法として、データ管理の必須テクニックとしてマスターすることをおすすめします。
ただし、大量のデータに条件付き書式を適用するとファイルが重くなる可能性があるため、必要な範囲だけに限定することも大切でしょう。
エクセルの条件付き書式機能を正しく理解して、効率的な重複チェックとデータ管理を実現していきましょう!
コメント