名簿の重複を関数ひとつで見つける：COUNTIFの使い方と3つの落とし穴

顧客名簿や申込みリストを扱っていると、「同じ人が2回入っていないかな？」と気になる場面がよくあります。目で探すと時間がかかるうえ、見落としも出ます。ここでは、Excel でも Google スプレッドシートでも使える COUNTIF 関数で、重複に印を付ける方法を紹介します。

1. まずは「重複している行」に印を付ける

A列の2行目から10行目に名前が入っているとします。B2 に次の式を入れて、下の行までコピーします。

=IF(COUNTIF($A$2:$A$10,A2)>1,"重複","")

COUNTIF は「範囲の中に、この値がいくつあるか」を数える関数です。2つ以上あれば「重複」と表示し、1つだけなら空欄にします。

ポイントは範囲を $A$2:$A$10 のように $ で固定することです。固定しないと、下にコピーしたときに範囲までずれてしまい、正しく数えられません。

2. 「2回目以降」だけに印を付ける

1の式だと、重複している行すべてに印が付きます。「最初の1件は残して、2件目以降を消したい」ときは、こちらが便利です。

=IF(COUNTIF($A$2:A2,A2)>1,"2回目以降","")

範囲の始まりだけを固定して、終わりを A2 のままにしておくと、下にコピーするにつれて「1行目から今の行まで」の範囲で数えるようになります。最初に出てきた行は1回目なので空欄、2回目に出てきた行から印が付きます。印の付いた行を並べ替えやフィルタで集めれば、消す候補が一目で分かります。

3. 見落としやすい落とし穴

手元で見本のデータを作って試したところ、次の3点で結果が「思っていたのと違う」ことがありました。

(1) 末尾の空白は別の値になる

「田中」と「田中 」（後ろに半角スペース）は、COUNTIF では別の値として数えられました。見た目は同じなので、いちばん気づきにくい落とし穴です。

(2) 英字の大文字・小文字は区別されない

「tanaka」と「TANAKA」は、同じ値として「重複」になりました。区別したい場合は COUNTIF では足りないので注意してください。

(3) 範囲の固定忘れ

1で書いた $ の付け忘れです。コピーしたあと、いくつかの行の式を開いて範囲がずれていないか見ておくと安心です。

4. 空白の違いをまとめて吸収する

(1) の対策として、補助の列で空白を取り除いてから数える方法があります。

・B2 に =TRIM(A2) を入れて下までコピー（前後の余分な空白を取り除きます）

・C2 に =IF(COUNTIF($B$2:$B$10,B2)>1,"重複","") を入れて下までコピー

こうすると「田中」と「田中 」が同じものとして「重複」になりました。

なお、全角スペースの扱いは使っているソフトや版によって違うことがあります。気になるときは =LEN(A2) で文字数を出してみてください。見た目より文字数が多ければ、どこかに空白が混じっています。

まとめ

・重複に印を付ける：=IF(COUNTIF($A$2:$A$10,A2)>1,"重複","")

・2回目以降だけ：=IF(COUNTIF($A$2:A2,A2)>1,"2回目以降","")

・見た目が同じなのに重複にならないときは、空白を疑って TRIM と LEN

まずは名簿のコピーで試してから、本番のシートに使ってみてください。

「うちのシートだとどう書けばいいの？」「重複を見つけたら別シートに移したい」といったときは、こちらでお作りしています。

スプレッドシート関数・GASを1つお作りします（動かして確かめてからお渡しします）

https://coconala.com/services/4452351

※この記事は生成AI（Claude）を使って書き、式は見本のデータで動かして確かめています（確かめた環境: Excel 形式のファイルを計算ライブラリで計算）。