2011年第2期技術講座：COUNTIF函數經典應用技巧

　　分享題目：COUNTIF函數的經典應用技巧　　主講老師：簡單老師　　特邀嘉賓：吳姐、無言的人、盧子等excel界精英。

　　本期技術沙龍分享，由簡單老師為大家分享COUNTIF函數的經典應用技巧。感謝"簡單"老師，感謝網友們的參與和互動。下面是本次技術分享的完整內容。

　　COUNTIF函數是一個比較簡單的函數，但真正使用好了也是非常實用的。countif函數的功能就是用來計算個數，表示計算區域中滿足給定條件的單元格的個數。

　　countif函數的語法為：COUNTIF(range,criteria)，其中Range參數：是為需要計算其中滿足條件的單元格數目的單元格區域，Criteria參數：為確定哪些單元格將被計算在內的條件，其形式可以為數字、表達式或文本。

　　下圖是一個電腦配件銷售表，本次分享我們要完成下面幾個知識點的學習。

　　第一，統計上圖數據源中「數量」大於30的個數以及「單價」小於100的個數。

　　先看看「數量」大於30的個數，套用COUNTIF函數的語法：countif(區域，條件)，得出公式：=COUNTIF(D6:D35,">30")，結果為：8個。

　　說明：在公式中的> < = 都要用" "，而引用單元格就不需要 " "。

　　此題另外還可以使用數組公式：=COUNT(IF(D6:D35>30,1))，按下【Ctrl】+【Shift】+【Enter】三鍵，完成數組的輸入。

　　也可以使用這樣的公式：=COUNTIF(D6:D35,">"&D7)。「&」此符號就是文本粘貼符，後面的是單元格地址，意思就是連接D7單元格的內容。大家可以在工作表中查看D7單元格的內容就是30。如果大於 後面沒有函數，就沒有比對的目標，所以要用&D7。

　　有一種查看公式中部分內容的方法，就是按下F9鍵，俗稱「抹黑」。 F9鍵在學習函數與公式中，對我們來說，有很大的幫助作用，幫助我們理解公式。

在本公式中，如果在公式編輯欄選中後面的">"&D7，按下F9鍵，可以看出變為了">30"，就和設計的第一種公式一樣的，即：=COUNTIF(D6:D35,">30")

　　當然解決一個問題，設計的公式也許不只一種，只要大家根據自己的理解，靈活使用就可以了，得出的答案都是一樣的。

　　用同樣方法可以得出「單價」小於100的個數，公式為：=COUNTIF(D6:D35,"<100")，得到正確答案為：30。

　　第二，統計上圖數據源中，「營業部」中含「河」字的個數，以及在「商品」這列中是否有鍵盤。

　　要求解出答案，首先，需要領會COUNTIF 函數中通配符的使用規則，COUNTIF 函數是支持通配符的，在COUNTIF函數中可以引用通配符。其中通配符?號代表單個字元， *號代表多個字元。

　　因此，統計「營業部」中含「河」字的個數，公式為：=COUNTIF(A6:A35,"*河")，得到正確答案為：12。

　　另外一種公式寫法：=COUNTIF(A6:A35,"*河*")，也可以得到答案。

　　其實，通配符*號和find函數差不多，因此還可以這樣設計公式：=COUNT(FIND("河",A6:A35))，然後按下【Ctrl】+【Shift】+【Enter】三鍵，完成數組的輸入。

　　在「商品」這列中是否有鍵盤，可以這樣設計公式：=IF(COUNTIF(B6:B35,"鍵盤"),"是","否")。

　　第三，一次行列出營業部中「天河」、「黃埔河」、「黃埔」、「越秀」、「荔灣」、「超秀」出現的次數。

　　對於一次性統計多單元格出現次數，可以使用數組公式。方法是：先選中L19：L24單元格區域，然後在編輯欄中輸入公式=COUNTIF(A6:A35,K19:K24)，然後按下【Ctrl】+【Shift】+【Enter】三鍵，完成數組的輸入。得到答案為：9、3、4、7、6、1次。

　　另外此題，也可以使用普通公式：=COUNTIF($A$6:$A$35,K19)，然後往下拉，複製公式即可。

　　第四，統計「銷售日期」列下面的日期總共出現次數。

　　計算數據源中日期總共出現的次數，也需要用到數組公式，這個公式在網上也是個經典例子。公式為：=SUM(1/COUNTIF(C6:C35,C6:C35))，然後三鍵結束。結果為：30。

　　公式分析：公式中，COUNTIF(C6:C35,C6:C35)的結果為1，大家可以在上面公式中把這部分抹黑，得到結果為1。就是統計C6:C35每一個單元格內容出現的次數。

　　在將公式中1/COUNTIF(C6:C35,C6:C35)部分抹黑，執行公式，得到數組形式的{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}，一共有30個1。1/就是每一個數值佔1的百分比，來相加。抹黑查看完公式結果，可以按Esc鍵返回公式。最後再用SUM 函數合計。

　　此題，還可以有下面兩種公式設計，都可以實現結果。

　　第一：=SUM(--(MATCH(C6:C35,C6:C35,)=ROW(C6:C35)-5))，三鍵結束。

　　第二，設計普通公式：=COUNT(1/FREQUENCY(C6:C35,C6:C35))。

　　有網友說到此題也可以使用公式：=COUNTIF(C6:C35,"<>0")。這個公式實質有點問題，只是算C6：C35區域的數據，在本題中，雖然答案是一樣的，是因為日期不重複，如果源數據中有兩個相同的日期，結果就不對了。大家可以更改一下源數據裡面的日期做個小試驗。

　　第五，統計「商品」列中不重複的有哪幾個?

　　此題可以理解為提取不重複值，最簡單的方法是使用高級篩選，操作步驟：單擊菜單「數據」——「篩選」——「高級篩選」，在「方式」下面選中：將篩選結果複製到其他位置，列表區域為：$B$6:$B$35，複製到：K32，勾選「選擇不重複的記錄」。

　　說明：高級篩選的不好之處，就是工作表中的源數據更新後，篩選出來的結果是不會更新的。

　　本題也可以使用公式來求解。下面設計的三種公式均能實現最終結果。

　　公式一：=INDEX($B$6:$B$35,MATCH(,COUNTIF($L$31:L31,$B$6:$B$35),))，然後三鍵結束公式輸入，下拉。

　　公式二：=INDEX($B$6:$B$35,SMALL(IF(MATCH($B$6:$B$35,$B$6:$B$35,)=ROW($B$6:$B$35)-5,ROW($B$6:$B$35)-5,1000),ROW(A1)))，然後三鍵結束公式輸入，下拉。

　　公式三：=LOOKUP(1,0/(NOT(COUNTIF($K$31:K31,$B$6:$B$35))),$B$6:$B$35)，然後往下拉，複製公式，直到出現#N/A錯誤值。本題的答案是：硬碟，顯示器，滑鼠。

　　以上內容就是2010年第2期技術分享：COUNTIF函數的經典應用技巧的全部內容。本次課程分享就全部結束了。　　

技術分享的excel源文件，請在下面各群的共享里下載。