2つの表 重複に色を付ける (セル範囲)

条件付き書式を使って、下図の左表に名前がある場合、右表の該当する行に色を付けるときのお話です。

シナリオのイメージとしては、左側が以前開催した研修の参加者のリストで、右側が今回の研修の参加者のリスト。
「前回も参加した人は誰かな？」というがわかりやすいように色を付けよう、という感じです。

今回のポイントは、

表はテーブルに変換していない (標準のセル範囲)
比較対象が 1 つ (表が全部で 2 つ) ★表が 3 つの場合は話が違う
当たり前ですが 2 つの表で [名前] に揺れがあってはいけない (織田信長と織田信長のような違いはだめ)

です。

数式の準備

条件付き書式で利用する数式はダイアログボックスを表示してから入力してもよいですが、正しく動作しているかどうかを確認するためにここでは事前にセルに作成します。
下図の [数式] 列はあとで削除するので見出しはなくてもよいです。

条件付き書式で色を付けたいほうの表に、他方の表の [名前] 列に名前が存在するかどうかを確認するための数式を作ります。

=COUNTIF(B5:B12 , G5)

この数式では、セル B5 から B12 (左表の [名前] 列) に、セル G5 (数式を作成しているセルと同じ行の [名前] 列のセル) と同じ値の格納されているセルがいくつあるのかを調べています。
数式は右表の 2 行目以降でも使用するので、あとでセルをコピーすることを考えて範囲は絶対参照にしておきます。

=COUNTIF($B$5: $B$12 , G5)
数式を確定します。

左表の [名前] 列に名前がある場合は 1 が返ります。
(厳密には、名前があるから 1 なのではなくて、範囲内に該当するセルがいくつあるかが返ります。)
数式の含まれているセルをほかの行にコピーします。

左表の [名前] 列に名前がある場合は 1 が、名前がない場合は 0 が返ります。

条件付き書式の作成

準備した数式を利用して右表の該当する行を塗りつぶす条件付き書式を作成します。

条件付き書式のルールでは、数式の処理結果が TRUE のときに書式が適用されます。そして、論理値 TRUE は「1」、FALSE は「0」という数値で処理に利用することができます。
ここでは、COUNTIF 関数による数式の結果が「1」または「0」で返るため、この「1」を TRUE として条件付き書式の数式で利用します。

条件付き書式のルールを作成するときに貼り付けるために、前の手順で数式を作成したセルを編集モードにして、中身の数式だけをコピーします。(注意：セルをコピーではなく、中身をコピー)

コピーが終わったら [Enter] キーなどを押して編集状態を解除してください。
条件付き書式を適用するセル範囲 (色を付けたい範囲) を選択して、リボンの [ホーム] タブの [スタイル] グループの [条件付き書式] をクリックして [新しいルール] をクリックします。
[新しい書式ルール] ダイアログボックスで [数式を使用して、・・・・] を選択して、下部のボックスの中をクリックして、コピーしておいた数式を貼り付けます。
右表のどの列から見ても G 列 ([名前] 列) の値を条件にできるように、条件のセル番地の列を固定します。
忘れるとうまく動かないのでご注意を。「$」を追加できたら [書式] をクリックします。

=COUNTIF($B$5: $B$12 , $G5)

※ 数式のボックスのなかで [→] などのキーを押すとなんか数式が変になっちゃった！という方は、ちょっと画面が古いけれどこちらをどうぞ。

[セルの書式設定] ダイアログボックスの [塗りつぶし] タブで適用する色を選択して [OK] をクリックします。
[新しい書式ルール] ダイアログボックスの [OK] をクリックします。
選択していたセル範囲に条件付き書式が設定され、該当する行に書式が適用されます。

ここでのケースでは、左表の [名前] 列に名前のある行、すなわち、2 つの表で重複している名前の行に書式が適用されています。
数式を作成するために準備した列は削除してよいです。列を選択してクリアでもよいです。
完成です。

おまけ

今回の表はセル範囲なので、右表にデータ (レコード、行) が増えたときに自動的に条件付き書式の適用範囲が拡張されません。

たとえば下図では、11 人目のレコードとして左表にある名前のレコードを追加していますが塗りつぶしが適用されていません。

条件付き書式の適用範囲を変更する場合 (追加したデータのセルにも適用したい場合) は、リボンの [ホーム] タブの [スタイル] グループの [条件付き書式] － [ルールの管理] をクリックして [条件付き書式ルールの管理] ダイアログボックスを表示して [適用先] のセル範囲を編集してください。

[適用先] ボックスの中をクリックしてセル範囲をドラッグして修正することもできます。