重複の削除で件数が合わない。同じ300行で種類が4通りに割れた

重複の削除で減った行数と、COUNTIFで数えた重複の数が合わないのは、同じと見なす決まりが機能ごとに違うからだ。

同じ300行に4つの数え方を当てたら、種類の数が200から275まで割れた。どれも正しく動いていて、同じと見る範囲だけが違う。

以下の数は、Microsoft 365のExcel（バージョン16）で実際に数えたものだ。確認日は2026年9月22日。Windows 11で確かめている。使った300行は、200個のコードに、まったく同じもの25件、小文字にしたもの25件、全角にしたもの25件、うしろに空白を付けたもの25件を足して作った。本当の取引先は200社になる。

同じ300行で、種類の数が200から275まで割れた

4つの数え方を当てた結果はこうなった。

EXACTで1文字ずつ比べると275種類だった。小文字も全角も空白付きも全部別として数える。まったく同じ25件だけが1つにまとまる。

COUNTIFで数えると250種類になった。重複の削除をかけたあとに残った行数も250行で、同じ数になる。この2つは小文字を同じものとして扱う。

UNIQUEで数えると225種類だった。3つの中でいちばん少ない。

そろえてから数えると200種類で、これが本当の取引先の数と合う。

75件の差がどこから出たかは、ゆれの種類ごとに見ると分かる。

重複の削除はABCとabcを同じにするが、全角と半角は別にする

2つだけの表を7通り作って、それぞれの数え方が同じと見るか別と見るかを調べた。

重複の削除は大文字と小文字を区別しない。C0001とc0001は同じ行として片方が消える。これは意図どおりのことが多いが、コードの体系で大文字と小文字を使い分けている表では消してはいけない行が消える。

全角と半角は別として扱う。ＣＯＯＯ１とC0001は両方残る。目で見ると同じに見えるので、消えなかったことに気づかない。

UNIQUEだけが全角と半角を同じと見る。同じブックの中で、重複の削除とUNIQUEに違う答えが出る理由がここにある。

前後の空白はどの方法でも別になる。名簿の突き合わせで一致率を測ったときも、全角スペースの除去がいちばん効いた。重複でも同じで、空白は先に落とす。

COUNTIFは16桁の数字を1違いでも同じと数える

表の中でいちばん危ないのが16桁のところだ。

1234567890123456と1234567890123457と1234567890123458の3つを並べて、COUNTIFで種類を数えると1と出る。3つとも同じものとして数えられる。

原因は、COUNTIFが比べるときに15桁までしか見ないことにある。Excelが数値として持てる有効桁が15桁なので、16桁目から先は比べる前に落ちる。請求番号や口座番号でこの桁になると、別の伝票が同じ扱いになる。

同じ3つに重複の削除をかけると、3行とも残った。こちらは16桁目まで見ている。COUNTIFで重複ありと判定して重複の削除を押しても1行も減らない、という食い違いが起きる。

文字列の0012と数値の12も、COUNTIFは同じものとして数える。こちらも重複の削除は別として残す。取り込んだ表で型が混ざっていると、この組み合わせが出る。

16桁以上のコードを扱う表では、COUNTIFで重複を探さない。EXACTで1文字ずつ比べる。

=SUMPRODUCT(--EXACT($A$3:$A$302,$A3))

同じ値が何件あるかが返る。1なら重複していない。配った表はこの式で数えている。

色を付けて探すと、消えない行にも色が付く

重複を探すときに、条件付き書式の重複する値で色を付ける人は多い。このルールの決まりも調べた。

同じ7通りに当てると、色が付いたのは、まったく同じ、大文字と小文字、文字列の0012と数値の12、16桁の1違いの4通りだった。全角と半角、前後の空白には付かない。

6通りとも、COUNTIFで数えたときと同じ答えになった。内部の作りは確認していないが、結果は同じ決まりで動いている。

ここで食い違いが起きる。色で重複を探してから重複の削除を押すと、色が付いているのに消えない行が出る。16桁の1違いと、文字列の0012と数値の12がそれだ。色が残ったままなので、削除が効かなかったように見える。

食い違うのはこの向きだけになる。色が付いていないのに消える行はなかった。全角と半角は色も付かず、重複の削除でも残る。大文字と小文字は色も付いて、削除でも消える。

色は当たりを付けるために使い、消す判断はそろえた列でする。この順番にしておくと、見落としと消しすぎの両方が減る。

フィルターで絞っても、隠れている行が消える

8行の表を作り、区分で絞って4行だけ見えるようにしてから重複の削除をかけた。

残ったのは4行で、区分はどれも見えていたほうだった。隠れていた4行は消えている。画面に出ていない行も範囲に入る。

これは戻せない。元に戻すで1回は戻るが、閉じたあとでは分からない。絞り込んだ状態で押してしまうと、どの行が消えたのかも追えない。

避け方は2つある。消す前にフィルターを解除して全体を見る。あるいは、消したい範囲だけを別のシートに写してから当てる。写したほうが安全で、元の表がそのまま残る。

そもそも行を消さずに、印を付けて残すほうが後戻りできる。配った表は消す代わりに、初出か2件目以降かの印を付けている。

数える前にそろえる

COUNTIFの1つめの引数には、計算した配列を渡せない。そろえながら数えることができないので、そろえた値をいったん別の列に置く。

=IF($A3="","",TRIM(ASC(UPPER($A3))))

UPPERが小文字を大文字にして、ASCが全角を半角にして、TRIMが前後の空白を落とす。この3つで、7通りのゆれのうち4通りが吸収される。

そろえた列で数えると、どの数え方でも同じ答えになる。300行でそろえてから数えた結果は200種類で、作ったときの取引先の数と合った。

消してよい行かどうかは、上から数えて何番目かで決める。

=IF(COUNTIF($B$3:$B3,$B3)=1,"初出","2件目以降")

範囲の始まりだけを固定して、終わりを固定しない。この形にすると、その行までに同じ値が何件あったかが返る。1なら初出で、2以上なら2件目以降になる。2件目以降の行だけを消せば、初出は必ず残る。

残す行を選びたいときは、そろえる前に並べ替える。日付の新しい順に並べてから印を付ければ、新しい行が初出になる。

配ったブックの使い方

A列は表示形式を文字列にしてある。貼っても値が変わらない。B列にそろえた値、C列に厳密に同じ数、D列にそろえて同じ数、E列に印が出る。

見本の12行では、行数12に対して厳密に数えると10種類、COUNTIFで数えると9種類、そろえて数えると6種類になる。同じ12行で3つの答えが出るところを、そのまま見られるようにしてある。

C列とD列の数が違う行が、ゆれで重なっている行だ。この2つを並べておくと、消す前にどのゆれが効いたのかが分かる。

VLOOKUPが合わないときの直し方と同じで、元の列は壊さずに、そろえた列を別に作る。元の値を消してしまうと、間違って消した行を戻せない。

確かめていないこと

Excel 2016以前と、Web版のExcelでは試していない。UNIQUEは365以降の関数なので、古い版では別の式になる。

Googleスプレッドシートの重複の削除とUNIQUEも確認していない。同じと見る決まりが同じかどうかは分からない。

ひらがなとカタカナのゆれは確認していない。ASCは全角と半角を変えるが、ひらがなをカタカナにはしない。関数でそろえる方法がないので、置換の表を別に作ることになる。