countif函數(shù)的使用方法

COUNTIF函數(shù)用于統(tǒng)計滿足某個條件的單元格的數(shù)量，其基本用法為：

COUNTIF(統(tǒng)計區(qū)域,指定的條件)

如下圖所示，要統(tǒng)計A列有幾個5，可以使用以下公式：

=COUNTIF(A2:A10,5)

咱們把COUNTIF函數(shù)的第二參數(shù)稍加改動，就可以衍生出很多高效用法，例如：

統(tǒng)計大于5的單元格個數(shù)：

=COUNTIF(A2:A10,">5")

統(tǒng)計大于B2單元格數(shù)值的單元格個數(shù)：

=COUNTIF(A2:A10,">"&B2)

統(tǒng)計不等于5的單元格個數(shù)：

=COUNTIF(A2:A10,"<>5")

統(tǒng)計空白單元格的個數(shù)：

=COUNTIF(A2:A10,"=")

統(tǒng)計非空單元格的個數(shù)：

=COUNTIF(A2:A10,"<>")

統(tǒng)計以"e"或“E”開頭的單元格個數(shù)：

=COUNTIF(A2:A10,"e*")

對于身份證號碼以及銀行卡號等超長的文本型數(shù)字，使用COUNTIF函數(shù)時還需要加上一點“佐料”。

以下公式統(tǒng)計A列的身份證號碼是否有重復(fù)：

=IF(COUNTIF(A:A,A2&"*")>1,"重復(fù)","")

COUNTIF函數(shù)在計算文本型數(shù)字時，會默認(rèn)按數(shù)值型進(jìn)行處理，但是Excel中的數(shù)字精度只有15位，并且身份證號碼是18位，因此會把前15位相同的身份證全部識別為相同的內(nèi)容。

在第二參數(shù)后加上一個星號 &"*"，就是告訴Excel，要查找包含A2單元格內(nèi)容的文本，通過這樣變通的手段，COUTNIF函數(shù)就聽話啦。

前面咱們說的是COUNTIF的第二參數(shù)，下面再說說第一參數(shù)：

如下圖所示，要在A列按部門輸入序號，不同部門的序號從1開始。

在A2單元格輸入公式，向下復(fù)制：

=COUNTIF(B$2:B2,B2)

COUNTIF函數(shù)的統(tǒng)計區(qū)域是B$2:B2，第一個B2是行絕對引用，第二個B2，是相對引用。當(dāng)公式向下復(fù)制時，就會變成B$2:B3、B$2:B4……一個不斷擴展的區(qū)域，從這個動態(tài)區(qū)域中統(tǒng)計B列部門的個數(shù)。

下面咱們再分享COUNTIF函數(shù)的兩個組合應(yīng)用：

如下圖所示，要計算C列不重復(fù)的人數(shù)。

公式為：

=SUMPRODUCT(1/COUNTIF(A2:A14,A2:A14))

這是一個十分常用的統(tǒng)計不重復(fù)數(shù)據(jù)個數(shù)的公式，其中包含了一個數(shù)學(xué)邏輯：

任意一個數(shù)據(jù)重復(fù)出現(xiàn)N次，N個1/N的和值為1。

公式中“COUNTIF(A2:A14,A2:A14)”部分是數(shù)組計算，作用是分別統(tǒng)計A2:A14單元格區(qū)域中每個元素出現(xiàn)的次數(shù)。

運算過程相當(dāng)于：

=COUNTIF(A2:A14,A2)

=COUNTIF(A2:A14,A3)

……

=COUNTIF(A2:A14,A14)

返回內(nèi)存數(shù)組結(jié)果為：

{2;2;2;2;2;2;2;2;2;2;1;1;1}

再使用1除以返回的內(nèi)存數(shù)組，得到以下結(jié)果：

{0.5;0.5;0.5……;1;1;1}

用1除，即相當(dāng)于計算COUNTIF函數(shù)所返回內(nèi)存數(shù)組的倒數(shù)。

為便于理解，把這一步的結(jié)果中的小數(shù)部分使用分?jǐn)?shù)代替，結(jié)果為：

{1/2;1/2;1/2;1/2;……;1;1;1}

如果單元格的值在區(qū)域中是唯一值，這一步的結(jié)果是1。

如果重復(fù)出現(xiàn)兩次，這一步的結(jié)果就有兩個1/2。

如果單元格的值在區(qū)域中重復(fù)出現(xiàn)3次，結(jié)果就有3個1/3…

即每個元素對應(yīng)的倒數(shù)合計起來結(jié)果仍是1。

最后用SUMPRODUCT函數(shù)求和，得出不重復(fù)的人員總數(shù)。

還沒看暈？好，咱們繼續(xù)。

如下圖所示，要提取C列不重復(fù)的名單。

公式為：

=INDEX(C:C,1+MATCH(0,COUNTIF(E$1:E1,C$2:C$15),0))&""

注意是數(shù)組公式，編輯完成后，要按Ctrl+Shift+回車。

首先利用COUNTIF函數(shù)，在公式所在位置上方的單元格區(qū)域中，分別查找C$2:C$15單元格區(qū)域每個數(shù)據(jù)的個數(shù)。返回一個由0和1構(gòu)成的數(shù)組，如果C$2:C$15單元格區(qū)域的元素在公式上方出現(xiàn)過，結(jié)果就是1；如果沒出現(xiàn)，結(jié)果就是0。

和上一個例子里一樣，COUNTIF函數(shù)的第一參數(shù)是一個擴展的區(qū)域，公式的提取結(jié)果會被重復(fù)利用。

再利用MATCH函數(shù)，在COUNTIF函數(shù)返回的數(shù)組中查找第一個0的位置，也就是查找首次出現(xiàn)的數(shù)據(jù)所在的位置。

由于數(shù)據(jù)表的標(biāo)題行占了1行，將這個數(shù)字加1，就是需要提取的不重復(fù)數(shù)據(jù)在數(shù)據(jù)表中列的位置。

最后用INDEX函數(shù)，以MATCH函數(shù)的計算結(jié)果作為索引值提取C列對應(yīng)位置上的數(shù)據(jù)。

這個函數(shù)從最后開始往上看，比較容易理解一些哦。

好了，今天咱們的內(nèi)容就是這些吧，祝各位小伙伴一天好心情！

圖文制作：祝洪忠