複数列による重複チェックと条件付き書式 (セル範囲)

下図のように、3 つの列 ([項目1]、[項目2]、[項目3]) の値すべてが同じ場合に、行を塗りつぶす条件付き書式を作成したい場合の設定例についてご紹介します。

今回は対象の表は標準のセル範囲 (テーブルではない) です。

判別するための列を作成して対応する (列の削除 NG)

1.  3 つの列 ([項目1]、[項目2]、[項目3]) の値を結合した文字列を表示する [check] という列を作成します。 

セル E5 の数式 =B5 & C5 & D5

2.  1 つ前の手順で作成した [check] 列の値が、[check] 列のなかにいくつ存在するのかを確認する [重複] という列を作成します。

COUNTIF 関数を使って同じ値 (同じ組み合わせ) が 1 つなのか、2 つ以上なのかを確認できます。
列の下部までセルをコピーするため、セル範囲を絶対参照にしています。

セル F5 の数式 =COUNTIF($E$5:$E$14,E5)

3.  [重複] 列に作成した数式をコピーします。

4. セル範囲の条件付書式を設定したい範囲を選択して、リボンの [ホーム] タブの [スタイル] グループの [条件付き書式] をクリックして [新しいルール] をクリックします。

5.  [数式を使用して、書式設定するセルを決定] を選択し、コピーした数式を貼り付けて編集し、[書式] をクリックして塗りつぶしの色を設定して [OK] をクリックします。

検索条件となるセル (E5) は列を固定した複合参照とし、COUNTIF の結果が 1 より大きい (2 以上) の場合に条件付き書式を設定したいので末尾に「>1」を追加しています。

編集前 =COUNTIF($E$5:$E$14 , E5)

編集後 =COUNTIF($E$5:$E$14 , $E5)>1

6.  選択していた範囲に条件付き書式が適用されます。

7.  [check] 列と [重複] 列は条件付き書式のルールを作成するときに使用しているので削除できません。列を非表示に設定します。(私はこれでも十分だと思うけれど、非表示が嫌なのであれば後半に記載している方法でどうぞ。)

判別するための列を作成して対応する (列の削除 OK)

1. COUNTIFS 関数を使って、3 つの列 ([項目1]、[項目2]、[項目3]) の各行の値を条件とし、すべての条件をクリアしている行がいくつあるのかをカウントする [数式] という列を作成します。(この列は条件付き書式の作成後に削除します。確認およびコピー用に作っています。)

セル E5 の数式 =COUNTIFS($B$5:$B$14 , B5 , $C$5:$C$14 , C5 , $D$5:$D$14 , D5)

2. [数式] 列に作成した数式をコピーします。

3. セル範囲の条件付書式を設定したい範囲を選択して、リボンの [ホーム] タブの [スタイル] グループの [条件付き書式] をクリックして [新しいルール] をクリックします。

4. [数式を使用して、書式設定するセルを決定] を選択し、コピーした数式を貼り付けて数式を編集して、[書式] をクリックして塗りつぶしの色を設定して [OK] をクリックします。

COUNTIF 関数の条件となるセルは列番号を固定した複合参照とし、COUNTIF の結果が 1 より大きい (2 以上) の場合に条件付き書式を設定したいので、末尾に「>1」を追加しています。

編集前 =COUNTIFS($B$5:$B$14 , B5 , $C$5:$C$14 , C5 , $D$5:$D$14 , D5) 

編集後 =COUNTIFS($B$5:$B$14 , $B5 , $C$5:$C$14 , $C5 , $D$5:$D$14 , $D5)>1

5. 選択していた範囲に条件付き書式が適用されます。

6. [数式] 列を削除します。

COUNTIF や COUNTIFS で範囲を絶対参照や複合参照にするところがポイントでしょうか。 テーブルの場合については別で記載します。

石田 かのこ