値を表示する方法とセルに色塗りする方法を試してみます。
- COUNTIFS関数を使って複数列からなる行全体の重複をチェックできます。ただし空白セルがあるとカウントできず、大・小文字や全角半角などの区別がつきません。
- UNIQUE関数を使った方法もあります。カウントはできませんがTRUE/FALSEで重複の有無をチェックできます。空白セルがあってもチェックでき、文字の比較も厳密です。
手順
チェック内容について
各行の3つの列に英字が入力されていて、この行全体（3つの列全体）が重複するかどうかチェックします。
「A」「B」「B」の行と「B」「B」「A」の行のように、並んでいる値が同じでも順番が異なるものは重複しないものと扱います。
COUNTIFS関数を使う方法
カウント値を表示する
F列にCOUNTIFS関数を使った式を入力して、それぞれの行と同じ行が全体でいくつずつあるかをカウントしています。
値が2以上なら重複あり、1なら重複なしと判断できます。
F3セル（下方にコピー）
=COUNTIFS(B$3:B$11,B3,C$3:C$11,C3,D$3:D$11,D3)
コピー時に条件範囲（第1,3,5引数）への参照がずれないよう、絶対参照を使用しています。
絶対参照を使わず1つの式だけで一括してカウントするときはARRAYFORMULAを使って次のようにします。
F3セル
=ARRAYFORMULA(COUNTIFS(B3:B11,B3:B11,C3:C11,C3:C11,D3:D11,D3:D11))
重複する行に色を塗る
重複している行をセルの色付けで表示する方法です。
B3:B11の範囲を選択してメニューから「表示形式」→「条件付き書式」と選択し、セルの書式設定の条件を「カスタム数式」として下記の式を入力すれば完成です。
カスタム数式
=COUNTIFS($B$3:$B$11,$B3,$C$3:$C$11,$C3,$D$3:$D$11,$D3)>1
ただし、このCOUNTIFS関数による方法では3列のセルのうち1つでも空白セルがあるとカウントすることができず、すべて0とカウントされます。
また、大文字と小文字、全角と半角、さらにひらがなとカタカナを区別せず同じものとしてカウントします。
次の方法を用いればこれらの問題を避けることができます。
UNIQUE関数を使う方法
重複の有無を表示する
F列にUNIQUE関数を使った式を入力して、それぞれの行と同じ行が他に存在するかどうか判定しています。
TRUEなら重複あり、FALSEなら重複なしと判断できます。
F3セル（下方にコピー）
=ROWS(UNIQUE(B$3:D$11,,TRUE))=ROWS(UNIQUE({B$3:D$11;B3:D3},,TRUE))考え方は次のとおりです。
- 左側の「ROWS(UNIQUE(B$3:D$11,,TRUE))」で、範囲内にもともと1つしかない行がいくつあるか数える（UNIQUEの第3引数=TRUE）。
- 右側の「ROWS(UNIQUE({B$3:D$11;B3:D3},,TRUE))」で、範囲にB3:D3の行を付け加えたときに1つしかない行がいくつになるか数える。
- B3:D3の行がもともと他の行と重複しているならば1.と2.の結果は同じ（TRUE）、もともと重複していなかったなら2.の結果は1.より1つ減る（FALSE）。
絶対参照を使わず1つの式だけで一括してカウントするときはBYROW/LAMBDAを使って次のようにします。
F3セル
=BYROW(B3:D11,
LAMBDA(x,ROWS(UNIQUE(B3:D11,,TRUE))=ROWS(UNIQUE({B3:D11;x},,TRUE)))
)重複する行に色を塗る
セルへの色付けで表示する方法です。
B3:B11の範囲を選択してメニューから「表示形式」→「条件付き書式」と選択し、セルの書式設定の条件を「カスタム数式」として下記の式を入力すれば完成です。
カスタム数式
=ROWS(UNIQUE($B$3:$D$11,,TRUE))=ROWS(UNIQUE({$B$3:$D$11;$B3:$D3},,TRUE))
式は面倒ですが空白セルが含まれていても正しくカウントすることができ、大文字と小文字などの文字種の違いについても厳密に区別をします。
列が増えても式が長くならないのもメリットといえます。
