Excel中COUNTIF函數的五種必學使用技巧

院長大大丨圖文

教程基於Excel 2016

提起COUNTIF函數,各位小夥伴都知道它的含義是條件計數。但這個簡單的計數函數,卻可以引申出很多使用的場景與技巧。


今天,讓院長帶大家看看COUNTIF函數的五種必學使用技巧。


COUNTIF函數Excel中對指定區域中符合指定條件的單元格計數的一個函數。該函數的語法規則如下:


COUNTIF(range,criteria)

參數:range 要計算其中非空單元格數目的區域

參數:criteria 以數字、表達式或文本形式定義的條件


1. 滿足條件的計數

這是COUNTIF函數最基礎的應用,對指定區域中符合指定條件的單元格計數。


1.1 C2公式【=COUNTIF(A2:A11,8)】,返回A2:A11區域中,等於8的單元格數量。


1.2 C3公式【=COUNTIF(A2:A11,'<0′)返回A2:A11區域中,小於0的單元格數量。


1.3 C4公式【=COUNTIF(A2:A11,A2)返回A2:A11區域中,等於單元格A2的單元格數量。


1.4 C5公式【=COUNTIF(A2:A11,’>=’&A2)返回A2:A11區域中,大於等於單元格A2內容的單元格數量。注意:在比較運算符「>=」和單元格引用「A2」之間,用文本連接符「&」進行連接。

2.比較兩列的不同

第一種引申的場景,是組合IF函數,比較兩列的不同,並提示不存在的數據,B2單元格公式為【=IF(COUNTIF($C$2:$C$5,A2)>=1,”,’不存在’)】。


案例中,通過COUNTIF函數統計C2:C5區域間等於A2值的個數,配合IF函數,當C列值不存在於A列中,提示不存在,從未判斷出兩列的不同。

3. 創建序號,多應用於建立輔助列

第二種引申的場景,是用於創建序號。案例中,我們統計的是相同地區的個數,B2單元格輸入公式【=COUNTIF($A$2:A2,A2)】。

選擇區域中,第二個A2使用了相對引用,在往下填充公式時,區域會不斷延伸,達到動態區域統計地區個數的效果。

最後,C2單元格輸入公式【=A2&B2】,為A2和B2的內容合併,多用於建立查找引用的輔助列。


4. 計算不重復值的個數

第三種引申的場景,是組合SUMPRODUCT函數,計算不重復值的個數,C2單元格公式為【=SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11&”))】。


公式中【1/COUNTIF(A2:A11,A2:A11&”)】部分,使用了數組計算,作用是分別統計A2:A11單元格區域中每個元素出現的次數。


在案例中,用1去除,即出現1次的元素返回1 ,出現2次的元素返回0.5,結果依次為:0.5;1;0.5;1;0.5;0.5;0.5;1;1;0.5。


同時,增加【&”】,如當A2:A11中出現空值時,也會統計為一個元素,而不會判斷為空值,導致除法出錯。

最後,通過SUMPRODUCT函數把數組返回結果相加,得到不重復值的個數為7。

5. 提取不重復名單

第四種引申的場景,是組合INDEX和MATCH函數,提取不重復名單,C2單元格公式為【{=INDEX(A:A,MATCH(,COUNTIF(C$1:C1,A$2:A$11),) 1)&”}】。


公式中【COUNTIF(C$1:C1,A$2:A$11)】部分,選擇區域中,第二個C1使用了相對引用,在往下填充公式時,區域會不斷延伸,達到動態區域統計姓名個數的效果。


此公式分別統計A$2:A$11單元格區域中每個元素出現的次數,返回一個由0和1構成的數組,出現過結果為1,沒出現結果為0。


利用MATCH函數,在COUNTIF函數返回的數組中查找第一個0的位置,也就是查找首次出現的數據所在的位置。由於標題行占了1行,所以在MATCH函數使用時,需要加上1。


再利用INDEX函數,以MATCH函數的計算結果作為索引值,提取A列對應位置上的數據。


好了,今天的教程就到這裡了。最後一個場景還是比較複雜的,大家好好理解一下哈~

Excel成長學院
高效有趣學Excel