条件付き書式を使って、下図の左表に名前がある場合、右表の該当する行に色を付けるときのお話です。
シナリオのイメージとしては、左側が以前開催した研修の参加者のリストで、右側が今回の研修の参加者のリスト。
「前回も参加した人は誰かな？」というがわかりやすいように色を付けよう、という感じです。
今回のポイントは、
- 表はテーブルに変換している
- 比較対象が 1 つ (表が全部で 2 つ) ★表が 3 つの場合は話が違う
- 当たり前ですが 2 つの表で [名前] に揺れがあってはいけない (織田 信長 と 織田信長 のような違いはだめ)
です。
テーブル名の確認
今回は、左テーブルに「研修A」、右テーブルに「研修B」というテーブル名を設定しています。
数式の準備
条件付き書式で利用する数式はダイアログ ボックスを表示してから入力してもよいですが、正しく動作しているかどうかを確認するためにここでは事前にセルに作成します。
下図の [数式] 列はあとで削除するので見出しはなくてもよいです。
-
条件付き書式で色を付けたいほうのテーブルに、他方のテーブルの [名前] 列に名前が存在するかどうかを確認するための数式を作ります。
=COUNTIF(研修A[名前] , [@名前])
数式を作成している行の [名前] 列のセルと同じ値の格納されているセルが、研修Aテーブルの [名前] 列にいくつあるのかを調べています。範囲や条件となるセルは、ドラッグやクリックして選択すれば「研修A[名前]」などのように構造化参照で指定されます。
-
数式を確定します。
左表の [名前] 列に名前がある場合は 1 が、名前がない場合は 0 が返ります。(厳密には、名前があるから 1 なのではなくて、範囲内に該当するセルがいくつあるかが返ります。)
-
条件付き書式のルール作成の際には、テーブル名などをそのまま認識できません。
これはテーブル名だ、これはテーブルの列名だ、ということを Excel が認識できるように、INDIRECT 関数を使った数式に編集します。テーブル名やテーブル＋列名をダブル クォーテーションで括るのを忘れないように。
=COUNTIF(INDIRECT("研修A[名前]"),INDIRECT("研修B[@名前]"))
条件付き書式の作成
準備した数式を利用して右表の該当する行を塗りつぶす条件付き書式を作成します。
条件付き書式のルールでは、数式の処理結果が TRUE のときに書式が適用されます。そして、論理値 TRUE は「1」、FALSE は「0」という数値で処理に利用することができます。
ここでは、COUNTIF 関数による数式の結果が「1」または「0」で返るため、この「1」を TRUE として条件付き書式の数式で利用します。
-
条件付き書式のルールを作成するときに貼り付けるために、前の手順で数式を作成したセルを編集モードにして、中身の数式だけをコピーします。(注意：セルをコピーではなく、中身をコピー)
コピーが終わったら [Enter] キーなどを押して編集状態を解除してください。
-
条件付き書式を適用するセル範囲 (色を付けたい範囲) を選択して、リボンの [ホーム] タブの [スタイル] グループの [条件付き書式] をクリックして [新しいルール] をクリックします。
-
[新しい書式ルール] ダイアログ ボックスで [数式を使用して、・・・・] を選択して、下部のボックスの中をクリックして、コピーしておいた数式を貼り付けて [書式] をクリックします。
-
[セルの書式設定] ダイアログ ボックスの [塗りつぶし] タブで適用する色を選択して [OK] をクリックします。
-
[新しい書式ルール] ダイアログ ボックスの [OK] をクリックします。
-
選択していたセル範囲に条件付き書式が設定され、該当する行に書式が適用されます。
ここでのケースでは、左表の [名前] 列に名前のある行、すなわち、2 つの表で重複している名前の行に書式が適用されています。
-
数式を作成するために準備したテーブルの列は削除してよいです。
-
完成です。
おまけ
テーブルの場合は、右表にデータ (レコード、行) が増えたときに自動的に条件付き書式の適用範囲が拡張されます。
テーブルの場合は、事前に作成した構造化参照による数式をそのままでは使えないため、INDIRECT 関数による対応が必要です。なお、構造化参照の書き方がバージョンによって若干違うかもしれないのでご自身の環境で確認してみてください。
表がテーブルではない場合の数式については別途アップされているはずです。よかったらそちらもご覧ください。
石田 かのこ
