社員コードや取引先コードの一覧で、同じコードが2回入っていないかを確認する場面があります。見た目が同じコードでも、重複と判定されないことがあります。
重複を見つけるだけなら、条件付き書式で足ります。しかし、重複した行だけを抜き出して担当者に返す、2件目以降だけを消す、といった作業には別の道具が要ります。この記事では、次の3つを同じデータで比べます。中級者向けに、手早さより「どこで判定が食い違うか」を重く書きます。
- COUNTIFで重複フラグの列を足す
- UNIQUE・FILTER・COUNTIFで重複だけの一覧を出す(Microsoft 365など)
- 条件付き書式で色を付ける
COUNTIFで重複フラグを足す(1件目も含める・2件目以降だけ)
サンプルは、架空の社員コード10行(A001が2回、A002が3回)です。
=COUNTIF($A$2:$A$11,A2)>1
=COUNTIF($A$2:A2,A2)>1
上は範囲を固定した式で、同じコードの行にはすべてTRUEが付きます(10行中5行)。下は範囲の始点だけを固定して伸ばした式で、同じコードが2回目以降に出た行だけにTRUEが付きます(3行)。
「1件目も重複とみなす」のは、重複した行をまとめて確認したいときです。「2件目以降だけ」は、重複を削って1件に絞るときに使います。どちらも、フラグ列を足すだけで元データを変えません。フィルターでTRUEだけを選べば、行ごと扱えます。
UNIQUE・FILTER・COUNTIFで重複だけの一覧を出す
Microsoft 365では、重複だけを別の場所に並べられます。
=UNIQUE(FILTER(A2:A11,COUNTIF(A2:A11,A2:A11)>1))
結果はA001とA002の2つです。FILTERで2回以上出る行を残し、UNIQUEでまとめています。行ごと(社員コードと氏名)抜き出すときは、FILTER(A2:B11,COUNTIF(A2:A11,A2:A11)>1)とします。
1回だけ現れる値は、UNIQUE(A2:A11,,TRUE)で出せます(第3引数をTRUEにする)。今回はA003〜A007で、COUNTIFの結果が1の行をFILTERで残した式と同じでした。
2件目以降だけを抜き出すときは、COUNTIFの範囲を行ごとに伸ばす必要があります。MAPとLAMBDAを使い、次のようにしました。
=FILTER(A2:A11,MAP(A2:A11,LAMBDA(x,COUNTIF(A2:x,x)))>1)
結果はA002・A001・A002の3行で、上のフラグ列と一致します。
UNIQUEとFILTERの対応バージョンは、UNIQUE 関数(Microsoft サポート)の「適用先」に、Microsoft 365・Excel 2024・Excel 2021などが載っています。Excel 2019は載っていないため、2019以前では使えないと読み取れますが、手元では試していません。
重複が0件・スピル先が埋まっているとき
重複が1件もないと、FILTERは#CALC!を返します。第3引数に"none"のような値を渡す(またはIFERRORで包む)と、その値が表示されます。
スピル先のセルに値があると、数式のセルはエラーになります。日本語版の画面では#スピル!と表示されました。邪魔な値を消すと、そのまま展開されます。エラー値の見分け方は、【Excel関数】#N/A・#VALUE!・#REF!が出たら最初に確認も参考になります。
条件付き書式で色を付ける
数式を=COUNTIF($A$2:$A$6,$A2)>1にした場合と、組み込みの「重複する値」で、色が付く行は同じでした(5行の例で、A001の2行)。2件目以降だけにしたいときは、範囲を$A$2:$A2にします(3つ目のA001だけに色が付く)。
手間は少ないのですが、色が付くだけで一覧は出せません。また、書式の適用範囲の外に足した行には色が付きません。テーブルにしておくと、行を足したとき、計算列も動的配列の結果も自動で更新されました。テーブルの外に置いた固定範囲の式($A$2:$A$5)は伸びません。更新に強いのは、テーブルと動的配列の組み合わせです。
重複なのに一致しない・一致しすぎる落とし穴
ここまでの方法は、判定のしくみが違います。同じデータでも、結果が割れる場面がありました。次の表は、2つの値を並べて、COUNTIF・「=」・UNIQUEの結果を比べたものです(Microsoft 365・日本語版)。

| データ | COUNTIF | 「=」・SUMPRODUCT | UNIQUE |
|---|---|---|---|
| A001とa001 | 同じ | 同じ | 1件にまとめる(先の表記を残す) |
| A001と全角のA001 | 別 | 別 | 1件にまとめる |
| 半角スペース付きと全角スペース付き | 別 | 別 | 1件にまとめる |
| 文字列の007・数値の7・文字列の7 | 3つとも同じと数える | 別 | 別(3件) |
| 20桁の数字の文字列で末尾1桁が違う | 同じと数える | 別 | 別 |
| A*とAB・AC | ワイルドカードで3件と数える | 別 | 別 |
COUNTIFは大文字と小文字を区別しません。条件に数値として読める文字列や、*・?を渡すと、思わぬ行を数えます。文字列の007と数値の7も同じ扱いです。15桁を超える数字の文字列も、同じ値として数えました。256文字以上の条件は#VALUE!でした。*や?を含むコードは、~を付けて渡します。
UNIQUEは、COUNTIFと逆に、全角と半角、半角スペースと全角スペースをまとめました。このため、COUNTIFで重複と判定した行の数と、ROWS(A2:A11)-ROWS(UNIQUE(A2:A11))の数が合わないことがあります。この挙動は日本語版のExcelで測ったもので、他の言語やバージョンでは確かめていません。
対策は2つです。1つ目は、判定の前に、TRIMやASCで形をそろえた列を足し、その列で数えること。2つ目は、COUNTIFに頼らず、SUMPRODUCT(--(A2:A11=A2))のように「=」で比べることです。文字列と数値の食い違いは、結合のキーでも起きます。対処は【Power Query】キーの型がバラバラで結合できない時の対処法で扱っています。
使い分けのまとめ
| 方法 | 向く場面 | 弱い点 |
|---|---|---|
| COUNTIFのフラグ列 | 元の表に印を付けたい。旧バージョンでも使う | 型・ワイルドカード・全角半角の癖 |
| UNIQUE・FILTER | 重複だけの一覧を別に出したい | 0件の#CALC!、スピルのエラー、Excel 2019以前では不可(未検証) |
| 条件付き書式 | 目で見て確認する | 一覧は出せない。範囲外に足した行は対象外 |
中級者が実務で迷うのは、方法の選び方より、重複の定義です。コードの形をそろえてから、1つの方法で数える。複数の方法を併用するなら、結果を突き合わせる。これを決めておくと、「重複はない」と言い切れる確認になります。重複が結合で行を増やす問題は、【Power Query】結合で行が増える原因と重複の検知方法で扱っています。VBAで存在を確かめる方法は、【VBA】マスタ参照の存在チェックをDictionaryに置き換えるにあります。
確認した環境は、Excel 16.0(Microsoft 365)・日本語版です。Excel 2019以前、Excel 2021、Mac版は手元で検証していません。


コメント