メインコンテンツへスキップ
見出し画像

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手で終わります

    1. 重複を見る列の下に、=SUMPRODUCT(--(範囲=セル)) を1つだけ置く(先頭の行の右隣でかまいません)

    2. COUNTIFの答えと並べる

    3. 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://lin.ee/qBV6yLi

    同じ「見た目は同じなのに合わない」話を、突合で書いた回はこちらです。

    https://note.com/ai_memo_room/n/nbdbdd6c8c765

    画像
    画像
     
     
    AIとの付き合い方を、ノートに書き留めていく部屋です。 明日の机が軽くなる——そんなメモを置いていきます。 「AIで、机に余白を作る」がコンセプトです。

    あなたへのおすすめ