おはよう、後輩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桁を「同じ」と数えたんだよ
- 2. 測った表は、作り物の会員番号9つだよ
- 3. 4つの式と、色の付き方をくらべたよ
- 4. ⭐ 正しく数えたのは、SUMPRODUCTの式だけだったんだよ
- 5. ⚠️ &”*”を付けたCOUNTIFは、16桁は合ったけど、15桁をまちがえたんだよ
- 6. ⚠️ 条件付き書式の「重複する値」も、同じまちがいをしたんだよ
- 7. ⭐「重複の削除」は、本当に同じ1件だけを消したんだよ
- 8. MATCHと、ハイフン入りの番号も、まちがいはなかったんだよ
- 9. 重複の記事で勧めた式のままだと、16桁ではまちがうんだよ
- 10. ⭐ 答えが食いちがう行に、印を出す1列を足そうか
- 11. ⚠️ 今日測れなかったこと
- 12. エクセルの記事、これで37本目だよ
- 結論:16桁の番号の重複は、SUMPRODUCTで数えて、ふつうのCOUNTIFとくらべる1列を足そうか
- よくある質問
1. ⚠️ ふつうのCOUNTIFは、最後の1桁だけちがう16桁を「同じ」と数えたんだよ
まず、いちばん大事な結果から見てね。B2に入っているのは、重複の記事で勧めたのと同じ形の式だよ。
=COUNTIF($A$2:$A$10,A2)

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個の一意の値が残ります。
消えたあとの表がこれだよ。

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活用シリーズ(おすすめ)

コメント