【原】countif函數(shù)用法多，坑也不少,！

劉卓學(xué)EXCEL 2021-04-02

展開全文

你好,，我是劉卓,。歡迎來到我的公號(hào)，excel函數(shù)解析,。今天接著來聊countif函數(shù),。雖然它很好用，也深受小伙伴的喜愛,，但是坑也不少,，需小心避免。

今天就簡單分享一下它坑在什么地方,，具體的表現(xiàn)就是會(huì)區(qū)分?jǐn)?shù)據(jù)類型,。countif第2參數(shù)的格式（數(shù)據(jù)類型）會(huì)限定第1參數(shù)的統(tǒng)計(jì)范圍。

如果第2參數(shù)的數(shù)據(jù)類型是數(shù)值,，countif只在第1參數(shù)的數(shù)值單元格中進(jìn)行統(tǒng)計(jì),，而忽略其他類型的數(shù)據(jù)，比如文本,、邏輯值,、錯(cuò)誤值等。

簡單來說,，就是第1參數(shù)和第2參數(shù)的數(shù)據(jù)類型要匹配才會(huì)統(tǒng)計(jì),。當(dāng)然這不是絕對的，需要自己慢慢總結(jié),。

下圖是我列舉的countif的一些用法,，對理解區(qū)分?jǐn)?shù)據(jù)類型很有用，而且還有一些統(tǒng)計(jì)真空和假空以及通配符的用法,。

其中A2:A13是數(shù)據(jù)源,，包含不同類型的數(shù)據(jù)，有數(shù)值,，文本,，邏輯值和錯(cuò)誤值，以及真空和假空,。

A4單元格的6是通過公式產(chǎn)生，是文本型的數(shù)字,。A6單元格是真空單元格,，A8單元格是假空單元格，也是通過公式產(chǎn)生的,，是一個(gè)空文本,。

說一下=COUNTIF(A2:A13,"<9")這個(gè)公式（上圖紅框標(biāo)記）的結(jié)果為什么是3。通常我們認(rèn)為小于9的個(gè)數(shù)是4,，因?yàn)橛?,，4,，6，7四個(gè)數(shù),。但統(tǒng)計(jì)出來的結(jié)果就是3,。

因?yàn)榈?參數(shù)是數(shù)值，只在第1參數(shù)的數(shù)值單元格中統(tǒng)計(jì)小于9的個(gè)數(shù),。在第1參數(shù)的區(qū)域中,，數(shù)值單元格有A2、A3,、A5三個(gè)單元格,，所以只在這3個(gè)單元格中統(tǒng)計(jì)小于9的個(gè)數(shù)。

而A4單元格的數(shù)據(jù)類型是文本,，與第2參數(shù)的數(shù)據(jù)類型不匹配,，所以把它忽略掉。

下面來看幾個(gè)案列吧,。

1.計(jì)算各位同學(xué)的成績排名

在C3單元格輸入公式=COUNTIF(B$3:B$12,">"&B3)+1,，向下填充，發(fā)現(xiàn)結(jié)果都是1,。正常來說,，這個(gè)公式是沒有問題的。那原因是什么呢,？

原因是B列的成績是文本型的數(shù)字,。在公式編輯欄中選中countif的第2參數(shù)，按F9,，結(jié)果是">63",。

這樣第2參數(shù)的數(shù)據(jù)類型是數(shù)值，第1參數(shù)的數(shù)據(jù)類型是文本,，不匹配,。所以countif最后返回的結(jié)果是0，再加1就是1,。當(dāng)把B列的文本型數(shù)字轉(zhuǎn)為數(shù)值的時(shí)候,，結(jié)果就對了。

2.判斷身份證號(hào)是否重復(fù)

首先要判斷各身份證號(hào)出現(xiàn)的次數(shù),，如果大于1,，就重復(fù)了；否則沒有重復(fù),。在C18單元格輸入公式=COUNTIF(B$18:B$27,B18),，向下填充，發(fā)現(xiàn)結(jié)果是不對的。藍(lán)色的應(yīng)該是1次,，黃色的應(yīng)該是2次,，結(jié)果卻都是3次。為什么,？

在Excel中,，數(shù)字的精度是15位，15位以后的數(shù)字都會(huì)顯示為0,。這也是為什么身份證號(hào)要以文本的形式錄入,。

如果兩個(gè)身份證號(hào)的前15位是相同的，后三位是不同的,，那么它們經(jīng)countif的運(yùn)算都會(huì)被轉(zhuǎn)化為XXXXXXXXXXXXXXX000,，這樣它們就變成相同的了。

正確的公式為=COUNTIF(B$18:B$27,B18&"*"),，將第2參數(shù)的身份證號(hào)連接通配符*,，強(qiáng)制轉(zhuǎn)為文本，這樣經(jīng)countif的運(yùn)算就不會(huì)將15位之后的數(shù)字變?yōu)?了,。

最后用if函數(shù)判斷一下,，如果countif的結(jié)果大于1，那么重復(fù),，否則不重復(fù),。

3.統(tǒng)計(jì)各型號(hào)的數(shù)量

當(dāng)統(tǒng)計(jì)的區(qū)域或條件中包含*或?時(shí)，用countif的時(shí)候要小心了,。在C32單元格輸入公式=COUNTIF(B$32:B$36,B32),，向下填充。發(fā)現(xiàn)結(jié)果又?叒叕出錯(cuò)了,。實(shí)際每個(gè)型號(hào)的數(shù)量都是1,，20*30的型號(hào)卻算出3個(gè)。為什么,？

實(shí)際上面的公式就是=COUNTIF(B$32:B$36,"20*30"),，第2參數(shù)的條件是以20開頭30結(jié)尾的文本字符串，在B32:B36這個(gè)區(qū)域中符合條件的確實(shí)有3個(gè),。剩下的2個(gè)如上圖紅色框所示,。

正確的公式為=COUNTIF(B$32:B$36,SUBSTITUTE(B32,"*","~*"))，先用substitute函數(shù)將*替換為~*,，然后再用countif函數(shù)統(tǒng)計(jì),。*代表通配符，~*代表*本身,。（你讓*代表通配符，那誰來代表*本身呢，用~*）,。