【Excel関数】重複データの抽出|COUNTIFとUNIQUE・FILTER

重複データの抽出。COUNTIFとUNIQUE・FILTERの数式を並べたコード風の画像 Excel関数

社員コードや取引先コードの一覧で、同じコードが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・日本語版)。

2つの値を比べた表。A001と全角のA001は、COUNTIFと「=」では別の値だが、UNIQUEでは1件にまとまる。文字列の007と数値の7は、COUNTIFでは同じと数える
COUNTIF・「=」・UNIQUEの判定の違い
データCOUNTIF「=」・SUMPRODUCTUNIQUE
A001とa001同じ同じ1件にまとめる(先の表記を残す)
A001と全角のA001別別1件にまとめる
半角スペース付きと全角スペース付き別別1件にまとめる
文字列の007・数値の7・文字列の73つとも同じと数える別別(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版は手元で検証していません。

コメント

タイトルとURLをコピーしました