複数列による重複チェックと条件付き書式 (テーブル)

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

今回は対象の表がテーブルに変換されている前提とします。

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

1. 3 つの列 ([項目1]、[項目2]、[項目3]) の値を結合した文字列を表示する [check] という列を作成します。テーブルの場合は [@項目1] のように構造化参照で位置が指定されます。

数式 =[@項目1] & [@項目2] & [@項目3]

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

COUNTIF 関数を使って同じ値 (同じ組み合わせ) が 1 つなのか、2 つ以上なのかを確認できます。
数式 =COUNTIF([check] , [@check])

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

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

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

ダイアログボックスの中で編集しにくいのなら、メモ帳などで貼り付けて編集したり、セルの中で数式を編集してからコピーしたりしてもよいです。ダイアログボックスの中で、「[check] 列は [Sample] テーブルの列である」ということを認識させるために、INDIRECT 関数による処理が必要です。

また、この COUNTIF の結果が 1 より大きい (2 以上) の場合に条件付き書式を設定したいので、末尾に「>1」を追加しています。

編集前 =COUNTIF([check] , [@check])

編集後 =COUNTIF(INDIRECT("Sample[check]") , INDIRECT("Sample[@check]"))>1

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

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

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

1. COUNTIFS 関数を使って、3 つの列 ([項目1]、[項目2]、[項目3]) の各行の値を条件とし、すべての条件をクリアしている行がいくつあるのかをカウントする [数式] という列を作成します。(この列は条件付き書式の作成後に削除します。確認およびコピー用に作っています。) テーブルの場合は [@項目1] のように構造化参照で位置が指定されます。

数式 =COUNTIFS([項目1] , [@項目1] , [項目2] , [@項目2] , [項目3] , [@項目3])

2. 1 つ前の手順で作成した [数式] 列の列名などをテーブルの項目として認識させるため、数式を編集します。

たとえば、「[項目1]」の部分は「INDIRECT("Sample[項目1]")」とします。なお、のちほど条件付き書式を作成するときにこの数式を使うので、末尾に「>1」を追加して TRUE/FALSE で結果が返るようにしています。

3. [数式] 列に作成した数式をコピーします。(画像では見やすいように改行していますが、改行がない状態でよいです。)

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

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

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

どちらかというと、テーブルの場合は INDIRECT 関数を組み合わせないといけない、というところがポイントでしょうか。また、これだと重複が見つかっているところまでなので 1 つだけ残してほかは削除したい、ということなら追加の作業が必要です。

UNIQUE 関数で抽出してもよいけど、[No] 列の扱いが微妙なので、Power Query で加工するのがスマートかな、と思います。

テーブルではない標準のセル範囲の場合については別で記載します。

石田かのこ