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

07-20

院長大大丨圖文

教程基於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