[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『重複チェックが正しくされない』（食いしん坊ママ）
どなたか教授下さい
データ行60,000件あります。
Ａ列に
重複確認用のキーCD
1111a2222b333c444dのように数値＆英数が入っています。
B列の3行目〜重複チェックとして
＝ＩＦ（COUNTIF(A:A,A3)>1,"重複","")
と入れたのですが、重複されていないコードに対して
重複と表示されます。
Ａ列で重複の削除を行うと削除対象と表示される件数とも
一致しません。
件数が多いからでしょうか？
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
数字だけで18桁のものもあるのだろうか？ もしあった場合、セルの書式が文字列になっていてもCOUNTIF関数上では頭の16桁が同じものは17桁、18桁目が異なっていても 同じものとみなされてしまう。 （ねむねむ） 2021/09/24(金) 16:27
もし、すべて18桁なのであれば =IF(COUNTIF(A:A,A3&"*")>1,"重複","") ではどうだろうか？ （ねむねむ） 2021/09/24(金) 16:46
すまない。 >COUNTIF関数上では頭の16桁が同じものは17桁、18桁目が異なっていても の16桁は15桁のミスで16桁目以降が違っても同じとみなされる。 （ねむねむ） 2021/09/24(金) 16:49
20桁以上あるデータもあります。全て同じ桁数ではありません。
countif関数では17桁以上は重複チェックが出来ないのですね・・・。
英字などいれ文字列にすれば大丈夫なのかと思っていました。
他に何か方法はあるのでしょうか・・・。
（食いしん坊ママ） 2021/09/25(土) 08:49
COUNTIFの変わりに SUMPRODUCT(($A$1:$A$60000=A3)*1) （どん） 2021/09/25(土) 09:09
回答ではないのですが、ちょっと教えてください。
１．アルファベットは大文字小文字を区別して判定するのですか？ ２．間違った判定結果は別として、何秒くらい掛かったですか？ ３．マクロは使用可能ですか？
（半平太） 2021/09/25(土) 09:19
半平太様
1）アルファベットに大文字、小文字の区別はないです。
※文字列にしたかった事と、各項目コードを＆でつなげた際の区切りのわかりやすさと、
正しい重複確認をしたかった為です。
例えば、同じキーCD1120も、パターン（1）1-120 パターン（2）11-20 は重複ではないとする為
2）5秒くらいだっと思います。
※昨日、勤務先のＰＣで今日は使用出来ないのですが、確かそれくらいだったと思います
3）マクロ使用可能です。
どん様
SUMPRODUCT(($A$1:$A$60000=A3)*1)式はB列に入れればよいのでしょうか？数式の意味が分かっておらずすみません。
ねむねむ様
20桁以上でも17桁を同じ物とみなされない場合もあるのでしょうか？
自宅の環境で10000件くらいのサンプルデータで同様に実施したところ重複の判別は出来ていました。
（食いしん坊ママ） 2021/09/25(土) 10:31
速度が５秒なら、それは問題ないですね。
大文字小文字の区別が必要なく、全データが文字型だとすると、 この現象がよく分からないですねぇ。 ↓ ＞重複されていないコードに対して重複と表示されます。
具体例を１つ出して頂けませんか？
※相変わらず、回答じゃないので恐縮ですけど。
（半平太） 2021/09/25(土) 10:40
回答ありがとうございます。
やはり、本来であれば、全データが文字列だと20桁以上でも、countifで
確認できるはずなのですね！！
正しく認識できなかったデータが勤務先ＰＣのデータの為、
月曜日に事例を報告させて下さい。
（食いしん坊ママ） 2021/09/25(土) 11:20
＞countifで確認できるはずなのですね！！
いやー、Countif関数は一癖あるので、そう単純でもないです。
行 __A__ __B__ 1 10e4 重複 ←B1セル =IF(COUNTIF(A:A,A1)>1,"重複","") 2 1E5 重複
なので、どんな現象が起きているのか知りたいと思った次第です。
どんさん の案の方が問題が少ないです。列参照にすると重くなるのが悩みの種ですが。
＞=IF(COUNTIF(A:A,A3)>1,"重複","")
↓
=IF(SUMPRODUCT(($A$1:$A$60000=A3)*1)>1,"重複","")
MicroSoft365でしたらSUMでもいいと思います。 ↓ =IF(SUM(($A$1:$A$60000=A3)*1)>1,"重複","")
（半平太） 2021/09/25(土) 13:28
MicroSoft365(スピル機能あり）なら、以下でよかった。その数式の下には何も入力しない様に(スピルの邪魔になるので）
(a) =LET(r,A3:XLOOKUP("*",A:A,A:A,NA(),2,-1),IFERROR(IF(LOOKUP(r,SORT(UNIQUE(r,FALSE,TRUE)))=r,"","重複"),"重複"))
または
(b) =LET(r,A3:XLOOKUP("*",A:A,A:A,NA(),2,-1),IF(ISNUMBER(MATCH(r,UNIQUE(r,FALSE,TRUE),0)),"","重複"))
a案は重複データが少ないときに速い、 b案は重複データが多いときに速くなる傾向にあるが、それでも多分a案には敵わない。
（半平太） 2021/09/25(土) 19:03
代替案ありがとうございます。
COUNTIFには癖があるのですね！！
10e4 1E5 が同じと認識されるのは何故なのでしょう。10e4 10E4が同じとの認識なら理解できるのですが。
末の数値も違いますし、規則性がなく、どちらかというとバグのような事なのでしょうか？
英字は確か全て小文字にしていたとは思いますが、月曜日確認してみようと思います。
どん様の案も、重複の付け方が解っておらず、半平太様にご教授いただきわかりました。
自宅ＰＣでは、2019なので、勤務先のOffice365で頂いた代替案を試してみたいと思います。
（食いしん坊ママ） 2021/09/26(日) 07:55
＞10e4 1E5 が同じと認識されるのは何故なのでしょう。 よく分かってないです。
指数表示の文字列で、それを数値換算して数えているのかな、と思えば どちらも100000なので同じなのかも。
ねむねむさんのアドバイスも、数値データの有効桁数は15桁なので、 それを越えると意図しない結果となる、と言う解釈になるのかも知れません。
※Eを挟むのは危なっかしいですが、それに留まらない。(下図ご参照)
(1) B3セル =COUNTIF(A:A,A3)
下にコピー
(2) C3セル =IF(SUMPRODUCT(($A$1:$A$60000=A3)*1)>1,"重複","")
下にコピー
(3) D3セル =LET(r,A3:XLOOKUP("*",A:A,A:A,NA(),2,-1),IFERROR(IF(LOOKUP(r,SORT(UNIQUE(r,FALSE,TRUE)))=r,"","重複"),"重複"))
MicroSoft365の場合のみ。下方のセルには勝手にスピルってくれる。
行 __________A__________ _B_ __C__ __D__ 全て文字型 ↓ 3 10e4 8 重複 重複 4 1E5 8 5 0.0001E9 8 6 100000 8 7 2173/10/14 8 8 2173-10-14 8 9 100000.0000000007 8
10 100000.5000000007 2 11 100000.5000000000007 2
12 2000/1/1 3 13 2000/01/01 3 14 2000-1-1 3
15 10E4 8 重複 重複
（半平太） 2021/09/26(日) 11:53
指数っ！！文系とはいえ、遙か昔に習ったような・・。
日付もシリアル値で数値にすると100000になるなど、他の事例も
詳しく教えて頂き本当にありがとうございます。
すっきりしました。
今回の場合だけの例では、各項目のCD（整数）を英字で繋げただけのなで、
英字もｅが入っていなければ、本来は問題にはならなそうですね。
月曜日に確認です。
（2）も十分理解は出来ていないのですが、（3）は使った事のない関数や組み合わせで、
今回教えていただいた式をしっかり理解して使用させて頂きたいと思います。
本当に勉強になります。いつもありがとうございます。
（食いしん坊ママ） 2021/09/26(日) 15:10
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.