COUNTIF函數(shù)的特殊用法介紹

王志軍

COUNTIF函數(shù)，其實(shí)如果巧妙利用，可以在很大程度上完成某些特殊的任務(wù)，這里做一簡單介紹。

1.按部門錄入序號

如圖1所示，A列原來是正常的序號編排，現(xiàn)在領(lǐng)導(dǎo)要求在A列按照部門錄入序號，不同部門的序號都從1開始。這里可以在A2單元格輸入公式“=COUNTIF（B$2：B2，B2）”，COUNTIF函數(shù)的統(tǒng)計(jì)區(qū)域是B$2：B2，第一個(gè)B2表示行的絕對引用，第二個(gè)B2表示相對引用。當(dāng)公式向下復(fù)制時(shí)，就會(huì)依次變?yōu)锽$2：B3、B$2：B4……，這是一個(gè)不斷擴(kuò)展的區(qū)域，從這個(gè)動(dòng)態(tài)區(qū)域中統(tǒng)計(jì)B列部門的個(gè)數(shù)。

公式輸入之后，向下拖拽或雙擊填充柄，很快就可以得到圖2所示的序號效果。

2.統(tǒng)計(jì)不重復(fù)的人數(shù)

如圖3所示，領(lǐng)導(dǎo)要求在E列計(jì)算不重復(fù)的員工人數(shù)，如果使用排序功能或篩選功能，不利于數(shù)據(jù)的后續(xù)更新，這里可以使用公式“=SUMPRODUCT（1/COUNTIF（A2：A18，A2：A18））”，這是一個(gè)十分常用的統(tǒng)計(jì)不重復(fù)數(shù)據(jù)個(gè)數(shù)的公式，其中包含了一個(gè)簡單的數(shù)學(xué)邏輯：任意一個(gè)數(shù)據(jù)重復(fù)出現(xiàn)N次，N個(gè)1/N的和值為1。

公式中“COUNTIF（A2：A18，A2：A18）”部分是數(shù)組計(jì)算，作用是分別統(tǒng)計(jì)A2：A18單元格區(qū)域中每個(gè)元素出現(xiàn)的次數(shù)，運(yùn)算過程相當(dāng)于：

=COUNTIF（A2：A18，A2）

=COUNTIF（A2：A18，A3）

……

=COUNTIF（A2：A18，A18）

返回內(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)于計(jì)算COUNTIF函數(shù)所返回內(nèi)存數(shù)組的倒數(shù)。如果單元格的值在區(qū)域中是唯一值，這一步的結(jié)果是1;如果重復(fù)出現(xiàn)兩次，這一步的結(jié)果就有兩個(gè)1/2;如果單元格的值在區(qū)域中重復(fù)出現(xiàn)3次，結(jié)果就有三個(gè)1/3……

即每個(gè)元素對應(yīng)的倒數(shù)合計(jì)起來結(jié)果仍是1，最后使用SUMPRODUCT函數(shù)進(jìn)行求和，得出不重復(fù)的人員總數(shù)，效果如圖4所示。

3.提取不重復(fù)的名單

仍然是圖3所示的例子，現(xiàn)在需要提取出A列不重復(fù)的員工名單，這里可以使用公式“=INDEX（A：A，1+MATCH（，COUNTIF（G$1：G1，A$2：A$18），））&""”，注意這是一個(gè)數(shù)組公式，編輯完成后，需要同時(shí)按下“Ctrl+Shift+Enter”組合鍵。公式含義如下：

首先利用COUNTIF函數(shù)，在公式所在位置上方的單元格區(qū)域中，分別查找C$2：C$15單元格區(qū)域每個(gè)數(shù)據(jù)的個(gè)數(shù)。返回一個(gè)由0和1構(gòu)成的數(shù)組，如果A$2：A$18單元格區(qū)域的元素在公式上方出現(xiàn)過，結(jié)果就是1;如果沒出現(xiàn)，結(jié)果就是0。COUNTIF函數(shù)的第一參數(shù)是一個(gè)擴(kuò)展的區(qū)域，公式的提取結(jié)果會(huì)被重復(fù)利用。接下來利用MATCH函數(shù)，在COUNTIF函數(shù)返回的數(shù)組中查找第一個(gè)0的位置，也就是查找首次出現(xiàn)的數(shù)據(jù)所在的位置，由于數(shù)據(jù)表的標(biāo)題行占了1行，將這個(gè)數(shù)字加1，就是需要提取的不重復(fù)數(shù)據(jù)在數(shù)據(jù)表中列的位置。最后利用INDEX函數(shù)，以MATCH函數(shù)的計(jì)算結(jié)果作為索引值，提取C列對應(yīng)位置上的數(shù)據(jù)。

上述公式的&""表示空文本 ，這是因?yàn)镮NDEX函數(shù)提取到空白單元格的時(shí)候，會(huì)返回一個(gè)無意義的0，加上一個(gè)空文本，可以屏蔽這個(gè)無意義的0，使單元格里看起來是空白的。

公式執(zhí)行之后，向下拖曳填充柄，直至出現(xiàn)“#N/A”的錯(cuò)誤信息為止，最終效果如圖5所示。