Excel。条件付き書式でシート間での重複データを塗りつぶすにはどうしたらいいの？
＜条件付き書式+COUNTIF関数＞
条件付き書式のセルの強調表示ルールに「重複する値」というのがあって、
列内に重複する値がある時には、
塗りつぶしするなど書式を設定することができて、
簡単に、視覚的に重複データを判断することができますが、
別の表や、別のシートにある表とチェックして、
重複だった場合に塗りつぶしするということをするとなると、
この「重複する値」では、対応することができません。
そこで、今回は、別シートにある表を使った場合、
どのようにしたら、条件付き書式の「重複する値」のように
塗りつぶしができるのかを紹介していきます。
では、表を確認しておきましょう。
参加者というシートには、次の表があります。
そして、支払済というシートには次の表があります。
やりたいことは、支払い済みの人がわかるように、
参加者シートの表に塗りつぶしを設定したいわけです。
考え方としてですが、
用意されている条件付き書式で設定することができないときには、
数式を使う方法で対応することが多いです。
支払済シートのB2の大船さんが、参加者シートのB列にあったら、
Yesのようなことができればいいわけですね。
また、条件付き書式の「新しいルール」に数式を使って設定する場合、
直接入力するとミスをする可能性が高くなりますので、
確認のためにも、一度数式を作ってから、
その数式をコピーするほうがいいように思います。
【COUNTIF関数で使ってみる】
IF関数で対応できそうに思えますが、支払済の表のようにアイテムが複数のため、IF関数を使うよりもここは、
COUNTIF関数を使ったほうが作業効率いいので、
今回はCOUNTIF関数を使っていきます。
参加者シートのC２をクリックして、
COUNTIF関数ダイアログボックスを表示しましょう。
範囲には、支払済!$B$2:$B$5
絶対参照も忘れずに設定しておきましょう。
検索条件には、
$B2
列を固定した、複合参照で設定していますが、
これは、条件付き書式で行での塗りつぶしをするためにしています。
C2の数式は
=COUNTIF(支払済!$B$2:$B$5,$B2)
それでは、数式をコピーしましょう。
このように算出されました。
数式の意味を考える前に、B16を大船に変更してみましょう。
B14とB16に大船が2件あるのに、
算出されている結果は、1になっていますよね。
このC列の算出結果は、重複していれば、1。
すなわち、TRUE。重複していなければ、0。
すなわち、FALSE。を意味しています。
TRUEとFALSEを算出することができれば、
あとは、この計算式を、条件付き書式に設定すればいいわけですね。
【条件付き書式の設定】
参加者シートのA2:B16を範囲選択して、条件付き書式の新しいルールをクリックしましょう。
新しい書式ルールダイアログボックスが表示されますので、
「数式を使用して、書式設定するセルを決定」をクリックします。
先ほど作成した数式をコピーして、書式を設定しましょう。
このように、重複データをシート間でも条件付き書式を
使って視覚的にわかるようにすることができました。