Excel函數詳解之COUNTIFS

這篇文章為大家帶來了關於excel的相關知識，其中主要介紹了COUNTIFS函數的基礎語法相關知識，該函數的語法為「COUNTIFS(criteria_range1,criteria1,[criteria_range2, criteria2]…)”，下面一起來看一下，希望對大家有幫助。

相關學習推薦：excel教學

#基礎語法

COUNTIFS函數的基礎語法為：

COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)

其中，criteria_range代表要統計的條件區域，criteria代表要統計的參數，用來定義將對哪些儲存格進行計數。

每一個criteria_range參數的區域都必須具有相同的行數和列數。這裡要注意，它的參數是「成對」出現的。另外，COUNTIFS支援127對條件統計，這個知識點僅作了解即可，日常的工作不會用到這麼複雜的條件。

分別對字串、數字、日期進行統計

如圖19-1所示，C11:F24單元格區域為基礎資料來源，C列為組別，D列為姓名，E列為銷售日期，F列為銷售金額，接下來對這部分數據做對應的統計。

1．案例：統計漢字

首先來統計一組的人數。換成Excel的語言，就可以翻譯成C列有多少個單元格是「一組」。如圖19-2所示，在I12單元格中輸入公式：=COUNTIFS(C12:C24,”一組”)

在統計漢字的時候，可以直接輸入對應的文字，並在文字兩側加上英文狀態的雙引號。這是統計一組的情況，要統計多個組，不用每個參數都手動輸入，可以提前在單元格中輸入對應的參數，如圖19-3所示，H14:H16單元格區域是需要統計的組別訊息，在I14儲存格中輸入以下公式並向下複製到I16儲存格。

=COUNTIFS($C$12:$C$24,H14)

它的計算過程：首先，引用H14單元格的值，將H14變成文字字串”一組”；其次，公式變為“=COUNTIFS($C$12:$C$24,”一組”)”；最後，進一步完成統計。

這裡再次提示，只要涉及公式複製，就一定要想到「圖釘」的問題。以上就是最基礎的COUNTIFS函數的統計。

2．案例：統計數字

條件統計函數不僅可以統計漢字，還可以統計數字。以下將資料來源中F列的銷售金額進行統計，分別統計「大於5000」「等於5000」「小於等於5000」的條件下各有多少人。在I19單元格中輸入公式：=COUNTIFS($F$12:$F$24,”>”&5000)

可以看到資料來源F列中有5個是大於5000的。在統計數字的時候，透過加入比較運算子統計數字的範圍。注意一個細節，這裡COUNTIFS的第2個參數使用的是”>”&5000，將比較運算子和數字兩部分分開，中間用“膠水”（&）連接。那麼，此處是否可以不用&，直接連在一起呢？

當然可以，公式可以寫成“=COUNTIFS($F$12:$F$24,”>5000″)”，但是在函數公式初學階段，連在一起寫很容易出現錯誤，本節後面會講到這個問題。如果碰到比較運算符號，還是建議將它與對應的參數分開寫入。

在I20儲存格中輸入下列公式，統計等於5000的人數：=COUNTIFS($F$12:$F$24,”=”&5000)

#將比較運算子和參數分開寫，當然在統計「等於」的時候可以將等號去掉，變成：

=COUNTIFS($F$12:$F$24,5000)

#在I21儲存格中輸入下列公式，統計小於等於5000的人數：=COUNTIFS($F$12:$F$24,”

計算結果如圖19-4所示。

提示：並不是只有統計數字的時候可以使用比較運算符，統計漢字的時候也是可以的，如公式「=COUNTIFS(D12:D24,」> ;”&”徐庶”)”，回傳結果為2，因為漢字一般是根據每個字的漢語拼音讀法，按照26個英文字母的順序從小到大排列的，數據源中比“徐庶”大的有“許褚”和“張飛”，所以結果為2。不過工作中很少會用到這種方式統計漢字。

做數字統計時，不僅可以直接在公式中輸入條件，還可以將條件放在單元格中，然後直接引用，如圖19-5所示，H23:H25單元格區域分別為“> 5000”“5000”“

#可以看到與先前的統計結果完全一致，這樣做有一個好處，以後如果需要修改統計條件，可以不用修改公式，直接在H23:H25的對應儲存格中修改即可，既直觀又快速。在實際工作中，也盡量把問題考慮全面，做到函數公式一步到位，以後只要在表格對應的參數區域修改就可以。

繼續看一種統計資料的方式，如圖19-6所示，在H27儲存格中輸入統計的分隔點，數字5000，然後還是分別統計「大於」「等於」「小於等於”三組數字。在I27單元格中輸入公式：

=COUNTIFS($F$12:$F$24,”>”&H27)

注意觀察，這就涉及之前埋的伏筆，為什麼要大家將比較運算子和參數分開寫。很多人會將公式寫成“=COUNTIFS($F$12:$F$24,”>H27″)”。公式乍看，好像沒問題，可是這個公式回傳的結果為0。為什麼呢？這就要說一下「活性」的問題了。

H27沒有在雙引號中，它保持了自己的“活性”，代表引用的是相應單元格，而一旦把它放在了雙引號中，它就變成了一個“木乃伊” ，不再具有「活性」。 ”>H27″統計的並不是大於H27單元格的那個數字5000，而是大於“H27”這3個字符的數據。在COUNTIFS的統計中，它先判斷條件的資料類型，發現資料類型是文本，而F12:F24單元格區域中全都是數字，沒有文本，所以結果為0。

我們使用函數是為了減少錯誤，所以在對函數尚不熟悉的情況下，把比較運算子和參數分開寫，中間用「膠水」（&）黏在一起，這樣能減少70 %的錯誤。

繼續完成另外兩個統計，在I28單元格和I29單元格分別輸入公式：=COUNTIFS($F$12:$F$24,”=”&H27)

#=COUNTIFS( $F$12:$F$24,”

當統計修改為以3000為分隔點的時候，只需將H27單元格修改為3000，其他公式完全不用改動，就能完成工作，如圖19-7。

3．案例：統計日期

下面繼續看統計日期的方式，為了方便查看頁面，我們在C33:F46單元格區域建立相同的資料來源，如圖19-8所示。

統計銷售日期在2016年2月的人數。先把公式寫下來，再慢慢分析，如圖19-9所示，在I33單元格中輸入公式：=COUNTIFS($E$34:$E$46,”>=”&”2016-2-1 ″,$E$34:$E$46,”

這個函數需要注意以下幾個面向。

（1）回顧下第10章講的日期函數，日期和時間的本質就是數字。統計某一區間日期，就相當於統計兩個數字之間的數量，於是用到了「掐頭去尾」的方式。

（2）這種快速輸入日期的方式，必須用英文狀態下的雙引號引起來，否則它不表示日期，而只是一個普通的數字減法。如果這種方式掌握不好，那就規規矩矩使用DATE函數，如DATE(2016,2,1)，可以減少錯誤。

（3）這個資料來源中的資料都是日期，不包含時間的部分，所以用“”>”&”2016-1-31″”“”=本月的1日79”“=80」「

（4）COUNTIFS可以多次對同一區域進行引用。有人問：「統計的公式太長了，可不可以用MONTH函數把日期的月份提取出來，然後用COUNTIFS函數統計其中有多少月份等於2？」我們動手試一下就知道答案了。依照此方法寫下公式“=COUNTIFS(MONTH(E34:E46),2)”，然後按下【Enter】鍵，系統出現了錯誤提示，如圖19-10所示。圖19-10公式錯誤提示 公式的邏輯沒有問題，那到底是哪裡出錯了呢？ MONTH(E34:E46)的結果是{2;2;2;3;2;2;3;3;3;3;1;2;2}，這是一個數組，而COUNTIFS中的第1個參數是criteria_range。注意，「range」的意思是一個區域，所以COUNTIFS的第1，3，5，7，…參數是不支援數組的，必須是區域，即必須是在Excel表格中畫出來一片單元格區域。與它有相同要求的參數還有ref、reference。有了第一個統計日期的基礎，我們繼續操作。統計的時候，不可能每月份都手動輸入，更多的情況是在儲存格中輸入1月、2月、3月等內容，然後完成對應的統計，如圖19-11所示。在I36儲存格中輸入下列公式，並向下複製到I38儲存格：=COUNTIFS($E$34:$E$46,”>=”&DATE(2016,LEFTB(H36,2),1),$E $34:$E$46,”

公式看上去很長，我們分步解讀。

公式LEFTB(H36,2)在7.5節中講過的，從月份中提取左側2個字節，於是只把數字提取出來，得到」1″，而這個空格並不影響DATE函數的計算。 DATE(2016,”1″,1)傳回結果“42370”，這個數字就相當於日期2016-1-1。最後使用COUNTIFS函數完成對應月份的統計。

相關學習推薦：excel教學

以上是Excel函數詳解之COUNTIFS的詳細內容。更多資訊請關注PHP中文網其他相關文章！