エクセルのCOUNTIFで16桁の番号を数えたら、最後の1桁だけちがう番号まで同じと出たんだよ。文字列にしてあってもまちがえた

「1桁ちがうのに、4件」の見出し。白いボードに9999…0001、0002、0003、0001の4つの番号が並び、最後の1桁がオレンジの丸で囲まれて、どれも数が4と出ている。左の人物が番号を指差し、右の人物が「別の番号なのに!」とおどろいている A君の成長

おはよう、後輩A君。

16桁の会員番号の列で、重複をCOUNTIFで数えたら、ちがう番号まで「同じ」と出た。今日はこの話だよ。

このブログでは、指数表示の記事で「16桁の番号は文字列にして守ろう」と書いたよね。重複を消す前に数える記事では「重複はCOUNTIFで数えよう」と書いたんだ。⚠️ その2つを両方守っても、まちがえたんだよ。

先に答えを言うね。

① 文字列にしてある16桁の番号でも、ふつうの =COUNTIF($A$2:$A$10,A2) は、最後の1桁だけちがう番号をまとめて同じと数えた(本当は2件なのに4)
② 条件付き書式の「重複する値」も、同じ4行に色を付けた
③ ⭐ 正しく数えたのは =SUMPRODUCT(–($A$2:$A$10=A2)) だけだった
④ ⭐ 「重複の削除」は正しく見分けた。消えたのは本当に同じ1件だけだった
⑤ ハイフン入り(9999-0000-0000-0001の形)は、どの式でも正しかった

⇒ 16桁の番号の重複は、ふつうのCOUNTIFの答えをそのまま信じないで、SUMPRODUCTの答えとくらべる1列を足そうか。

11月17日に、Mac版のエクセル(16.113.2)で測ったよ。式は命令(プログラム)で入れたんだ。「重複の削除」は、わたしが自分の手で押したよ。

1. ⚠️ ふつうのCOUNTIFは、最後の1桁だけちがう16桁を「同じ」と数えたんだよ

まず、いちばん大事な結果から見てね。B2に入っているのは、重複の記事で勧めたのと同じ形の式だよ。

=COUNTIF($A$2:$A$10,A2)

会員番号の表。数式バーは =COUNTIF($A$2:$A$10,A2)。A2からA10に会員番号、B列はCOUNTIF、C列はCOUNTIF(&"*")、D列はSUMPRODUCT、E列はMATCH、F列は確かめ、G列はメモ。B2からB5が4で、A2からA5に色が付き、F2からF5に「要確認」と出ている

A2〜A5の4つの番号は、こうなっているよ。

A2 9999000000000001
A3 9999000000000002 ← 最後の1桁だけちがう
A4 9999000000000003 ← 最後の1桁だけちがう
A5 9999000000000001 ← A2と本当に同じ

本当に同じなのは、A2とA5の2つだけだよね。だから正しい答えは、A2とA5が2、A3とA4が1なんだ。

でも、ふつうのCOUNTIFは4行とも4と出したんだよ。⚠️ 最後の1桁がちがう番号まで、まとめて同じと数えたんだ。

A列の表示形式は、ちゃんと文字列にしてあったよ。それでも、こうなったんだ。

一方で、6行目の 9999000000000011(15桁目がちがう番号)は、ちゃんと1だったよ。⇒ 結果から見ると、前から15桁までしか比べていないように見えるんだ。ただ、エクセルの中でなぜそうなるのかまでは、確かめていないよ。

2. 測った表は、作り物の会員番号9つだよ

見本は、9999で始まる作り物の会員番号だよ。ファイルは命令(python)で作って、A2〜A10の表示形式は文字列にしたんだ。

2行目 9999000000000001 16桁
3行目 9999000000000002 16桁・最後だけちがう
4行目 9999000000000003 16桁・最後だけちがう
5行目 9999000000000001 16桁・2行目と同じ
6行目 9999000000000011 16桁・15桁目がちがう
7行目 999900000000001 15桁
8行目 999900000000002 15桁・最後だけちがう
9行目 9999-0000-0000-0001 ハイフン入り
10行目 9999-0000-0000-0002 ハイフン入り・最後だけちがう

本当に同じ番号は、2行目と5行目の1組だけだよ。だから正しい数は、2行目と5行目が2、ほかは全部1なんだ。

3. 4つの式と、色の付き方をくらべたよ

B〜Eの列に、ちがう式を入れてくらべたんだ。

B =COUNTIF($A$2:$A$10,A2) (ふつうのCOUNTIF)
C =COUNTIF($A$2:$A$10,A2&”*”) (うしろに &”*” を付けたCOUNTIF)
D =SUMPRODUCT(–($A$2:$A$10=A2))
E =MATCH(A2,$A$2:$A$10,0) (同じ番号が、上から何番目に最初に出るか)

A2〜A10には、条件付き書式の「重複する値」も付けておいたよ。結果はこうだったんだ。

行 番号のちがい 正しい数 B ふつうのCOUNTIF C &”*”付き D SUMPRODUCT E MATCH 重複する値の色
2 16桁 2 ⚠️ 4 2 2 1 色あり
3 16桁・最後だけちがう 1 ⚠️ 4 1 1 2 ⚠️ 色あり
4 16桁・最後だけちがう 1 ⚠️ 4 1 1 3 ⚠️ 色あり
5 16桁・2行目と同じ 2 ⚠️ 4 2 2 1 色あり
6 16桁・15桁目がちがう 1 1 1 1 5 なし
7 15桁 1 1 ⚠️ 2 1 6 なし
8 15桁・最後だけちがう 1 1 1 1 7 なし
9 ハイフン入り 1 1 1 1 8 なし
10 ハイフン入り・最後だけちがう 1 1 1 1 9 なし

⭐ 「正しい数」の列と9行ぜんぶ合ったのは、D列のSUMPRODUCTだけだったよ。

4. ⭐ 正しく数えたのは、SUMPRODUCTの式だけだったんだよ

=SUMPRODUCT(–($A$2:$A$10=A2))

この式は、A2〜A10の番号を1つずつA2とくらべて、同じだった数を足す式だよ。

今日の結果では、2行目と5行目が2、ほかは全部1だったんだ。最後の1桁だけちがう3行目・4行目も、ちゃんと1と数えたよ。

⇒ 16桁の番号の重複を数えるなら、この式を使おうか。範囲の前に$を付けるのは、COUNTIFのときと同じだよ。

5. ⚠️ &”*”を付けたCOUNTIFは、16桁は合ったけど、15桁をまちがえたんだよ

C列は、探す番号のうしろに &”*” を付けたCOUNTIFだよ。”*” は「うしろに何が続いてもいい」という印なんだ。

=COUNTIF($A$2:$A$10,A2&”*”)

16桁の2〜5行目は、2・1・1・2と正しく数えたよ。

⚠️ でも、7行目の15桁の番号を2と数えたんだ。本当は1件しかないのにね。

7行目の番号  999900000000001
6行目の番号  9999000000000011 ← 前の15桁が7行目と同じ

「999900000000001のうしろに何かが続く番号」として、6行目の16桁の番号まで拾ったんだよ。⇒ &”*” は、前が同じで、もっと長い番号があると数をまちがえるんだ。だから今日は、直し方としては勧めないよ。

6. ⚠️ 条件付き書式の「重複する値」も、同じまちがいをしたんだよ

A列に付けた条件付き書式の「重複する値」は、2〜5行目の4行に色を付けたよ。1章の写真で、A2〜A5に色が付いているところだね。

本当に同じなのは2行目と5行目だけだから、3行目と4行目の色はまちがいなんだ。ふつうのCOUNTIFが4と出した4行と、ぴったり同じだったよ。

⇒ 16桁の番号では、色が付いたから重複、と決めないでね。

7. ⭐「重複の削除」は、本当に同じ1件だけを消したんだよ

同じ9つの番号を別のシート(会員番号とメモの2列)に写して、わたしが手で「データ」→「重複の削除」を押したよ。見る列は、会員番号の列だけにしたんだ。

出た画面はこれだよ(わたしが撮った写真だよ)。

重複の削除のあとに出た画面。「1個の重複する値が見つかり、削除されました。8個の一意の値が残ります。カウントには空のセルやスペースなどが含まれる場合があることに注意してください。」とOKボタン

1個の重複する値が見つかり、削除されました。8個の一意の値が残ります。

消えたあとの表がこれだよ。

重複の削除のあとの表。会員番号とメモの2列で、2行目から9行目までの8行が残っている。9999000000000002と9999000000000003(最後だけちがう16桁)、15桁目がちがう番号、15桁の2つ、ハイフン入りの2つが残っている

9行あった番号が8行になったよ。消えたのは、2つ目の 9999000000000001(2行目と本当に同じ番号)だけだったんだ。最後だけちがう16桁の2つ、15桁目がちがう番号、15桁の2つ、ハイフン入りの2つは、全部残っていたよ。命令で読み直して、残った番号も確かめたんだ。

⇒ 「重複の削除」は、COUNTIFや条件付き書式とちがって、16桁を正しく見分けたんだよ。

8. MATCHと、ハイフン入りの番号も、まちがいはなかったんだよ

E列のMATCHは、同じ番号が上から何番目に最初に出るかを返す式だよ。

5行目 → 1(同じ番号が最初に出るのは、1番目の2行目)
ほかの行 → 自分の番目(2行目は1、3行目は2…10行目は9)

最後だけちがう3行目・4行目も、自分の番目を返したよ。まちがいはなかったんだ。

それから、ハイフン入りの9行目・10行目は、B〜Dのどの式でも1で、色も付かなかったよ。⇒ 最後だけちがうハイフン入りの番号は、どの式でも正しく別の番号と数えたんだ。

9. 重複の記事で勧めた式のままだと、16桁ではまちがうんだよ

正直に書くね。重複を消す前に数える記事で勧めたのは、この式だったよ。

=COUNTIF($A$2:$A$100,A2)

でも今日測ったとおり、16桁の番号では、最後の1桁だけちがう番号まで同じと数えたよ。この数を見て「2以上は重複だ」と手で消していったら、3行目と4行目の別の会員の番号まで消してしまうんだ(消すところまでは、今日は試していないよ)。

⇒ 16桁の番号を数えるときは、COUNTIFのところを =SUMPRODUCT(–($A$2:$A$100=A2)) に替えようか。

10. ⭐ 答えが食いちがう行に、印を出す1列を足そうか

今日の表のF列には、こんな式を入れておいたよ。

F2 =IF(B2<>D2,”要確認”,””)

「B(ふつうのCOUNTIF)とD(SUMPRODUCT)の答えがちがったら、要確認と出す」という式だよ。

2〜5行目 → ⚠️ 要確認
6〜10行目 → (空のまま)

1章の写真のF列に、4つの「要確認」が出ているよね。COUNTIFがまちがえた行に、ぴったり印が出たんだ。

⇒ 2つの式の答えがそろっていれば、印は出ないよ。印が出たら、その行の番号を1桁ずつ目でくらべてから、消すかどうか決めようか。

11. ⚠️ 今日測れなかったこと

・測ったのはMac版のエクセル(16.113.2)だよ。Windows版で同じになるかは、分からないんだ
・式は命令で入れたよ。手で式を打ったときは、試していないんだ
・17桁以上の番号は、測っていないよ
・2つの列をそろえて数えるCOUNTIFSは、試していないんだ

12. エクセルの記事、これで37本目だよ

36本目は、#スピル!が出たら「!」で理由を読む話だったね。あれは、エクセルが止まって知らせてくれる話だったよ。

今日のふつうのCOUNTIFは、その逆なんだ。⚠️ 止まらずに、ちがう数を出したんだよ。だから、自分で印を出す1列を足しておこうか。

結論:16桁の番号の重複は、SUMPRODUCTで数えて、ふつうのCOUNTIFとくらべる1列を足そうか

今日測った結果をまとめるとこうだよ。

① 文字列にしてある16桁でも、ふつうのCOUNTIFは最後の1桁だけちがう番号を同じと数えた(2件のはずが4)
② 条件付き書式の「重複する値」も、同じ4行に色を付けた
③ ⭐ 9行ぜんぶ正しかったのは =SUMPRODUCT(–($A$2:$A$10=A2)) だけ
④ &”*”付きのCOUNTIFは、16桁は合ったけど、15桁の番号を2と数えた
⑤ ⭐ 「重複の削除」は、本当に同じ1件だけを消した。MATCHもまちがいなし
⑥ F列の =IF(B2<>D2,”要確認”,””) で、まちがえた4行にぴったり印が出た

文字列にして守った番号でも、数える式しだいでまちがうんだ。16桁の番号を数えるときは、SUMPRODUCTの式に替えて、答えが食いちがう行に印を出す1列を足しておこうか。

よくある質問

Q1. 表示形式を文字列にしてあれば、COUNTIFでも大丈夫ですか

⚠️ 今日は大丈夫じゃなかったよ。A列は文字列にしてあったのに、ふつうのCOUNTIFは最後の1桁だけちがう16桁の番号を、まとめて4と数えたんだ。⇒ 16桁の番号なら、=SUMPRODUCT(–($A$2:$A$10=A2)) で数えようか。

Q2. 15桁の番号なら、ふつうのCOUNTIFでいいですか

今日の15桁の番号(最後だけちがう2つ)は、ふつうのCOUNTIFでどちらも1、正しかったよ。ただ、測った15桁の番号はこの2つだけなんだ。⇒ ほかの長さの番号でも心配なら、10章の「要確認」の1列を足しておくと、答えの食いちがいが目で見えるよ。

Q3. 「重複の削除」は、16桁の番号に使っても大丈夫ですか

今日の測定では、本当に同じ1件だけが消えて、最後だけちがう16桁の番号は残ったよ。⚠️ でも、何が消えたかは画面に出ないんだ。⇒ 重複の記事に書いたとおり、押す前に別名で保存しておこうか。

【本日のミッション:16桁の番号の列で、重複を数え直してみようか】

  • ☐ 16桁の番号の列のとなりに、=SUMPRODUCT(–($A$2:$A$100=A2)) を入れてみようか(範囲は自分の表に合わせてね)
  • ☐ ふつうのCOUNTIFの列があるなら、=IF(B2<>D2,”要確認”,””) のように、答えが食いちがう行に印を出してみようか
  • ☐ 条件付き書式の「重複する値」で色が付いた番号は、1桁ずつ目でくらべてみようか
  • ☐ (後輩A君へ)番号を文字列で守っても、数える式がまちがえることがあるんだ。答えを2つの式でくらべれば、まちがいに気づけるよ

📚 AI活用シリーズ(おすすめ)

▼ シリーズの全記事一覧を見る

コメント

タイトルとURLをコピーしました