
Excelの重複チェックで、6行すべてが重複と出ました。本当の重複は2行でした|16桁の番号を数えたあなたの表は大丈夫ですか
請求番号や伝票番号の列で、重複を数えたことはありますか。COUNTIFで数えて、「2」以上が出た行を重複として扱っている方に、確かめてほしいことがあります。
番号が長いと、違う番号が重複と出ます。エラーは出ません。数字が並んでいるだけなので、目では気づけません。
今回は、Excelで実際に作って確かめました。16桁の番号が6行ある表で、COUNTIFは6行すべてを重複と判定しました。本当に重複しているのは2行だけでした。
6行の中身と、COUNTIFの答え
A列を文字列にして、次の6行を入れました。
上から1行目:1234567890123451
上から2行目:1234567890123452
上から3行目:1234567890123453
上から4行目:1234567890123451(1行目と同じ)
上から5行目:9876543210987654
上から6行目:9876543210987655
本当の重複は、1行目と4行目の2行だけです。残りの4行は、末尾の1桁がちがう別の番号です。
ところが、各行に「=COUNTIF($A$1:$A$6,A1)」を入れると、答えはこうなりました。
4・4・4・4・2・2
1行目は本当は2件なのに4件。2行目と3行目は本当は1件なのに4件。5行目と6行目は本当は1件なのに2件です。「2以上なら重複」と読むと、6行すべてが重複になります。
違いは16桁目にありました
どの番号も、最初の15桁が同じ組と同じ組です。ちがうのは16桁目だけでした。実測では、15桁の番号(123456789012345と123456789012346)は、COUNTIFが1と1で正しく数えました。16桁の番号では、最初の15桁が同じなら同じ番号として数えられていました。
なぜそうなるのかは、Microsoftの公式のCOUNTIFのページで確認できませんでした。ページにあったのは、255文字より長い文字列で間違った結果になるという注意だけです。ここまでは、手元のExcelで起きたことの記録です。
同じことは、条件付き書式でも起きました。「重複する値」を設定すると、6行すべてが赤く色が付きました。色が付いた行を目で見て「重複が多い」と判断すると、同じ誤りを踏みます。
SUMIFでも起きました。1234567890123452の金額だけを足す式で、本来は200のところ1,000が返りました。重複チェックだけでなく、番号をキーにした集計も同じ影響を受けます。
正しく数えられたのは、この3つでした
同じ表で、別の方法を試しました。
方法1・SUMPRODUCT:式は =SUMPRODUCT(--($A$1:$A$6=A1))。答えは 2・1・1・2・1・1(正しい)
方法2・COUNTIFの条件の後ろに「*」を付ける:式は =COUNTIF($A$1:$A$6,A1&"*")。答えは 2・1・1・2・1・1(正しい)
方法3・MATCH(完全一致):式は =MATCH(A1,$A$1:$A$6,0)。答えは 1・2・3・1・5・6(正しい。1行目と4行目はどちらも1が返る)
SUMIFも、条件の後ろに「*」を付けた形では200が返り、正しくなりました。
「データ」→「重複の削除」は、6行を5行にしました。重複していた1行だけが消え、正しい動きでした。
ただし「*」を付ける方法は、番号の後ろに続く文字があるセルも数えます(1234567890123451の後ろに別の桁が続く番号など)。桁数がそろっている番号の列で使ってください。
点検は、次の3手で終わります
重複を見る列の下に、=SUMPRODUCT(--(範囲=セル)) を1つだけ置く(先頭の行の右隣でかまいません)
COUNTIFの答えと並べる
2つの答えがちがう行があれば、その列は桁数を確かめてから使う
桁数は =LEN(A1) で数えられます。16桁以上の番号があるかどうかは、=MAX(LEN(範囲)) で一度に分かります(配列の数式なので、古いExcelではCtrl+Shift+Enterが要ります)。
AIに頼むなら、結果の突き合わせまで自分でやる
重複を調べる数式をAIに作ってもらうときは、桁数を先に渡すと早いです。
A列に16桁の請求番号が入っています(文字列)。重複している行だけを見つける数式を、COUNTIFを使わない方法で教えてください。
見本:(A列の数件を貼る)
返ってきた数式がCOUNTIFだったら、そのまま使わず、上のSUMPRODUCTの結果と並べてください。AIの答えが合っていたかどうかは、この突き合わせで分かります。
ここまでの手順は、Excel 16.0の日本語版で確かめました。Mac版とGoogleスプレッドシートでは確かめていません。数値として入力した16桁の番号(Excelは15桁までしか保持しません)も、今回は試していません。
まとめ
重複の数え間違い:16桁の番号をCOUNTIFで数えると、違う番号が重複と出た。6行すべてが重複扱いで、本当の重複は2行
桁数の境目:15桁の番号では正しく数えられた。最初の15桁が同じ16桁の番号が、同じものとして数えられた
条件付き書式の「重複する値」とSUMIFにも、同じことが起きた
正しい方法:SUMPRODUCT、COUNTIFの条件に「*」を付ける方法、MATCHは正しかった
「重複の削除」は正しく動いた
点検は、SUMPRODUCTの結果とCOUNTIFの結果を並べるだけ
いま重複を数えている番号の列は、何桁ありますか。
自分の業務で、AIに渡せる工程を見つけたい方は、LINE公式「AIメモ室」で「AI用途の棚卸しシート」をお渡ししています。
自分の業務を工程に5〜8個へ割って、AIに渡せる1つを見つける、記入式の1枚です。所要5分、書くのは3か所だけです。
・工程に割る記入欄(記入例つき)
・その工程をAIに渡せるか判定する3つの条件
・「整える時間」を1週間だけ測る記録欄
同じ「見た目は同じなのに合わない」話を、突合で書いた回はこちらです。
https://note.com/ai_memo_room/n/nbdbdd6c8c765

