小伙伴們好啊,，我是您的老朋友流浪鐵匠,。我就問問有多少人用COUNTIF核對身份證號(hào)時(shí)出錯(cuò)了呢。

除非看過公眾號(hào)文章，新手經(jīng)常會(huì)奇怪=COUNTIF(A:A,A2)這種對長數(shù)字編碼(如身份證號(hào))計(jì)數(shù)的結(jié)果為什么會(huì)異常,。

這涉及到COUNTIF這類IF后綴條件統(tǒng)計(jì)函數(shù)的特殊統(tǒng)計(jì)機(jī)制，而這類函數(shù)的統(tǒng)計(jì)機(jī)制之復(fù)雜坑死了一堆新手,，因此借這機(jī)會(huì)詳細(xì)介紹下,。

看這部分前請先清楚2個(gè)概念：

1，數(shù)字(日期)在Excel里有文本型和數(shù)值型兩種,，前者屬于文本,，后者才能直接統(tǒng)計(jì)計(jì)算。

2,，單元格格式和數(shù)據(jù)類型是兩碼事,，后者不能用檢查單元格格式方式檢測而要用ISTEXT/ISNUMBER等函數(shù)檢測出來，與單元格格式無關(guān),。

COUNTIF這類函數(shù)的統(tǒng)計(jì)機(jī)制：

1,，文本與數(shù)值，井水不犯河水：

第二參數(shù)內(nèi)若內(nèi)容是純文本(不能為'12'這種能轉(zhuǎn)為數(shù)值的純數(shù)字文本)以及含有通配符時(shí),，統(tǒng)計(jì)時(shí)將忽略統(tǒng)計(jì)區(qū)域內(nèi)的數(shù)值內(nèi)容,。但統(tǒng)計(jì)區(qū)域中的文本型數(shù)字會(huì)被統(tǒng)計(jì)。

2,，不識(shí)數(shù),。

第2參數(shù)當(dāng)以'='作為比較運(yùn)算符或者為純字符串時(shí)，若內(nèi)容為真數(shù)值/文本型數(shù)字,。會(huì)把統(tǒng)計(jì)區(qū)域內(nèi)的所有真數(shù)值/文本型數(shù)值(以及數(shù)字兩端存在空格等情況)等全部識(shí)別為真數(shù)值進(jìn)行統(tǒng)計(jì),，無視第1、第2參數(shù)數(shù)字的數(shù)據(jù)類型,。

當(dāng)?shù)?參數(shù)內(nèi)有'>',，'<'等比較運(yùn)算符時(shí)，若統(tǒng)計(jì)區(qū)域含真數(shù)值/文本型數(shù)字,，只統(tǒng)計(jì)統(tǒng)計(jì)區(qū)域內(nèi)的真數(shù)值部分,，統(tǒng)計(jì)區(qū)域內(nèi)的文本型數(shù)字將被忽略。此時(shí)需要使用 &'*' 方式強(qiáng)制識(shí)別為文本時(shí)才會(huì)對引用區(qū)域的文本型數(shù)字統(tǒng)計(jì),。

而這就是COUNTIF這類函數(shù)統(tǒng)計(jì)身份證號(hào)等超過15位的純數(shù)字編碼出錯(cuò)的原因(直接=COUNTIF(A：A,，A2)這種寫法。

統(tǒng)計(jì)時(shí)由于2參數(shù)無通配符等,，1/2參數(shù)的數(shù)字編碼全部被識(shí)別為數(shù)值,，而EXCEL數(shù)值有15位精度限制,，超過15位的數(shù)字會(huì)被全部識(shí)別為0。

所以統(tǒng)計(jì)時(shí)第1和第2參數(shù)超過15位的數(shù)字編碼會(huì)全部被COUNTIF識(shí)別為0計(jì)數(shù),。

這個(gè)特殊性質(zhì)但凡初次用COUNTIF核對身份證號(hào)的小伙伴都被坑過吧                                        ,。

所以看完上面的機(jī)理介紹就應(yīng)該知道為什么公式要改成=COUNTIF(A：A，A2&'*')了                                         

因?yàn)?amp;'*'后2參數(shù)就不是純數(shù)字的比較了而被識(shí)別為文本,，是文本的統(tǒng)計(jì)規(guī)則,，此時(shí)忽略統(tǒng)計(jì)區(qū)域的數(shù)值，只統(tǒng)計(jì)文本                                          ,。

而由于精度限制,，被正常錄入的身份證號(hào)必然是文本型的數(shù)字了                                          。

3,，自作聰明的表達(dá)式,。

另外注意這個(gè)函數(shù)的統(tǒng)計(jì)內(nèi)容可以被識(shí)別為表達(dá)式的情況下請?zhí)砑?='&    ，避免內(nèi)容被識(shí)別為條件,。

如下圖中,，有一些帶有比較符號(hào)的字符，現(xiàn)在要統(tǒng)計(jì)字符“>4”的個(gè)數(shù),。

如果用=COUNTIF(單元格區(qū)域,'>4'),，這種用法是不正確的。

應(yīng)該寫成=COUNTIF(單元格區(qū)域,'=>4')

COUNTIF函數(shù)有自身的統(tǒng)計(jì)機(jī)制,，特別是由于新手不會(huì)分辨文本型數(shù)字和真數(shù)值,，涉及數(shù)字的統(tǒng)計(jì)在不清楚這類函數(shù)的統(tǒng)計(jì)規(guī)則的前提下，就會(huì)經(jīng)常掉進(jìn)這類函數(shù)的陷阱里,。

通過本篇的介紹,，小伙伴們可以了解COUNTIF函數(shù)的這些性質(zhì)，避免在工作中被坑了,。

好了,，今天的內(nèi)容就是這些吧，我是流浪鐵匠,，感謝小伙伴們觀看。

圖文制作：流浪鐵匠