|
COUNTIFS函數(shù)的基礎(chǔ)語法為：COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)其中，criteria_range代表要統(tǒng)計(jì)的條件區(qū)域，criteria代表要統(tǒng)計(jì)的參數(shù)，用來定義將對(duì)哪些單元格進(jìn)行計(jì)數(shù)。每一個(gè)criteria_range參數(shù)的區(qū)域都必須具有相同的行數(shù)和列數(shù)。這里要注意，它的參數(shù)是“成對(duì)”出現(xiàn)的。另外，COUNTIFS支持127對(duì)條件統(tǒng)計(jì)，這個(gè)知識(shí)點(diǎn)僅作了解即可，日常的工作不會(huì)用到這么復(fù)雜的條件。分別對(duì)字符串、數(shù)字、日期進(jìn)行統(tǒng)計(jì)如圖19-1所示，C11:F24單元格區(qū)域是基礎(chǔ)數(shù)據(jù)源，C列為組別，D列為姓名，E列為銷售日期，F(xiàn)列為銷售金額，接下來對(duì)這部分?jǐn)?shù)據(jù)做相應(yīng)的統(tǒng)計(jì)。首先來統(tǒng)計(jì)一組的人數(shù)。換成Excel的語言，就可以翻譯成C列有多少個(gè)單元格是“一組”。如圖19-2所示，在I12單元格中輸入公式：=COUNTIFS(C12:C24,'一組')在統(tǒng)計(jì)漢字的時(shí)候，可以直接輸入相應(yīng)的文字，并在文字兩側(cè)加上英文狀態(tài)下的雙引號(hào)。這是統(tǒng)計(jì)一個(gè)組的情況，要想統(tǒng)計(jì)多個(gè)組，不用每個(gè)參數(shù)都手動(dòng)輸入，可以提前在單元格中輸入相應(yīng)的參數(shù)，如圖19-3所示，H14:H16單元格區(qū)域是需要統(tǒng)計(jì)的組別信息，在I14單元格中輸入以下公式并向下復(fù)制到I16單元格。=COUNTIFS($C$12:$C$24,H14)它的計(jì)算過程：首先，引用H14單元格的值，將H14變?yōu)槲谋咀址?一組'；其次，公式變?yōu)椤?COUNTIFS($C$12:$C$24,'一組')”；最后，進(jìn)一步完成統(tǒng)計(jì)。這里再次提示，只要涉及公式復(fù)制，就一定要想到“圖釘”的問題。以上就是最基礎(chǔ)的COUNTIFS函數(shù)的統(tǒng)計(jì)。條件統(tǒng)計(jì)函數(shù)不僅可以統(tǒng)計(jì)漢字，還可以統(tǒng)計(jì)數(shù)字。下面對(duì)數(shù)據(jù)源中F列的銷售金額進(jìn)行統(tǒng)計(jì)，分別統(tǒng)計(jì)“大于5000”“等于5000”“小于等于5000”的條件下各有多少人。在I19單元格中輸入公式：=COUNTIFS($F$12:$F$24,'>'&5000)可以看到數(shù)據(jù)源F列中有5個(gè)是大于5000的。在統(tǒng)計(jì)數(shù)字的時(shí)候，通過添加比較運(yùn)算符統(tǒng)計(jì)數(shù)字的范圍。注意一個(gè)細(xì)節(jié)，這里COUNTIFS的第2個(gè)參數(shù)使用的是'>'&5000，將比較運(yùn)算符和數(shù)字兩部分分開，中間用“膠水”（&）連接。那么，此處是否可以不用&，直接連在一起呢？當(dāng)然可以，公式可以寫成“=COUNTIFS($F$12:$F$24,'>5000')”，但是在函數(shù)公式初學(xué)階段，連在一起寫很容易出現(xiàn)錯(cuò)誤，本節(jié)后面會(huì)講到這個(gè)問題。如果碰到比較運(yùn)算符號(hào)，還是建議將它與相應(yīng)的參數(shù)分開寫。在I20單元格中輸入以下公式，統(tǒng)計(jì)等于5000的人數(shù)：=COUNTIFS($F$12:$F$24,'='&5000)將比較運(yùn)算符和參數(shù)分開寫，當(dāng)然在統(tǒng)計(jì)“等于”的時(shí)候可以將等號(hào)去掉，變?yōu)椋?/span>=COUNTIFS($F$12:$F$24,5000)在I21單元格中輸入以下公式，統(tǒng)計(jì)小于等于5000的人數(shù)：=COUNTIFS($F$12:$F$24,'<='&5000)圖19-4統(tǒng)計(jì)數(shù)字1提示：并不是只有統(tǒng)計(jì)數(shù)字的時(shí)候可以使用比較運(yùn)算符，統(tǒng)計(jì)漢字的時(shí)候也是可以的，如公式“=COUNTIFS(D12:D24,'>'&'徐庶')”，返回結(jié)果為2，因?yàn)闈h字一般是根據(jù)每個(gè)字的漢語拼音讀法，按照26個(gè)英文字母的順序從小到大排列的，數(shù)據(jù)源中比“徐庶”大的有“許褚”和“張飛”，所以結(jié)果為2。不過工作中很少會(huì)用到這種方式統(tǒng)計(jì)漢字。做數(shù)字統(tǒng)計(jì)時(shí)，不僅可以直接在公式中輸入條件，還可以將條件放在單元格中，然后直接引用，如圖19-5所示，H23:H25單元格區(qū)域分別為“>5000”“5000”“<=5000”。在I23單元格中輸入以下公式，并向下復(fù)制到I25單元格，完成相應(yīng)的數(shù)據(jù)區(qū)間的統(tǒng)計(jì)。=COUNTIFS($F$12:$F$24,H23)圖19-5統(tǒng)計(jì)數(shù)字2可以看到與之前的統(tǒng)計(jì)結(jié)果完全一致，這樣做有一個(gè)好處，以后如果需要修改統(tǒng)計(jì)條件，可以不用修改公式，直接在H23:H25的相應(yīng)單元格中修改即可，既直觀又快捷。在實(shí)際工作中，也盡量把問題考慮全面，做到函數(shù)公式一步到位，以后只需在表格相應(yīng)的參數(shù)區(qū)域修改就可以。繼續(xù)看一種統(tǒng)計(jì)數(shù)據(jù)的方式，如圖19-6所示，在H27單元格中輸入統(tǒng)計(jì)的分隔點(diǎn)，數(shù)字5000，然后還是分別統(tǒng)計(jì)“大于”“等于”“小于等于”三組數(shù)字。在I27單元格中輸入公式：=COUNTIFS($F$12:$F$24,'>'&H27)圖19-6統(tǒng)計(jì)數(shù)字3注意觀察，這就涉及之前埋的伏筆，為什么要求大家將比較運(yùn)算符和參數(shù)分開寫。很多人會(huì)將公式寫成“=COUNTIFS($F$12:$F$24,'>H27')”。公式乍一看，好像沒問題，可是這個(gè)公式返回的結(jié)果為0。為什么呢？這就要說一下“活性”的問題了。H27沒有在雙引號(hào)中，它保持了自己的“活性”，代表引用的是相應(yīng)單元格，而一旦把它放在了雙引號(hào)中，它就變成了一個(gè)“木乃伊”，不再具有“活性”。'>H27'統(tǒng)計(jì)的并不是大于H27單元格的那個(gè)數(shù)字5000，而是大于“H27”這3個(gè)字符的數(shù)據(jù)。在COUNTIFS的統(tǒng)計(jì)中，它先判斷條件的數(shù)據(jù)類型，發(fā)現(xiàn)數(shù)據(jù)類型是文本，而F12:F24單元格區(qū)域中全都是數(shù)字，沒有文本，所以結(jié)果為0。我們使用函數(shù)是為了減少錯(cuò)誤，所以在對(duì)函數(shù)尚不熟悉的情況下，把比較運(yùn)算符和參數(shù)分開寫，中間用“膠水”（&）粘在一起，這樣能減少70%的錯(cuò)誤。繼續(xù)完成另外兩個(gè)統(tǒng)計(jì)，在I28單元格和I29單元格分別輸入公式：=COUNTIFS($F$12:$F$24,'='&H27)=COUNTIFS($F$12:$F$24,'<='&H27)當(dāng)統(tǒng)計(jì)修改為以3000為分隔點(diǎn)的時(shí)候，只需將H27單元格修改為3000，其他公式完全不用改動(dòng)，就能完成工作，如圖19-7所示。下面繼續(xù)看統(tǒng)計(jì)日期的方式，為了方便查看頁(yè)面，我們?cè)贑33:F46單元格區(qū)域建立相同的數(shù)據(jù)源，如圖19-8所示。統(tǒng)計(jì)銷售日期在2016年2月的人數(shù)。先把公式寫下來，再慢慢分析，如圖19-9所示，在I33單元格中輸入公式：=COUNTIFS($E$34:$E$46,'>='&'2016-2-1',$E$34:$E$46,'<'&'2016-3-1')這個(gè)函數(shù)需要注意以下幾個(gè)方面。（1）回顧下第10章講的日期函數(shù)，日期和時(shí)間的本質(zhì)就是數(shù)字。統(tǒng)計(jì)某一區(qū)間日期，就相當(dāng)于統(tǒng)計(jì)兩個(gè)數(shù)字之間的數(shù)量，于是用到了“掐頭去尾”的方式。（2）這種快速輸入日期的方式，必須用英文狀態(tài)下的雙引號(hào)引起來，否則它不表示日期，而只是一個(gè)普通的數(shù)字減法。如果這種方式掌握不好，那就規(guī)規(guī)矩矩使用DATE函數(shù)，如DATE(2016,2,1)，可以減少錯(cuò)誤。（3）這個(gè)數(shù)據(jù)源中的數(shù)據(jù)都是日期，不包含時(shí)間的部分，所以用“'>'&'2016-1-31'”“'<='&'2016-2-29'”等不同的固定首、尾的方式都可以。但如果數(shù)據(jù)源中的數(shù)據(jù)含有時(shí)間，如“2016-1-3115:28”“2016-2-2909:07”等，則必須采用公式中“>=本月的1日<下個(gè)月1日”的日期方式，這樣的統(tǒng)計(jì)是最準(zhǔn)確的。就好像統(tǒng)計(jì)分?jǐn)?shù)的時(shí)候，80分到90分之間為良，如果大家的得分都是整數(shù)，那么“>79”“<=89”等方式都可以，但當(dāng)分?jǐn)?shù)中包括79.5、89.5等小數(shù)的時(shí)候，只能用“>=80”“<90”來表達(dá)最準(zhǔn)確的區(qū)間。（4）COUNTIFS可以多次對(duì)同一區(qū)域進(jìn)行引用。有人問：“統(tǒng)計(jì)的公式太長(zhǎng)了，可不可以用MONTH函數(shù)把日期的月份提取出來，然后用COUNTIFS函數(shù)統(tǒng)計(jì)其中有多少月份等于2？”我們動(dòng)手試一下就知道答案了。按照此方法寫下公式“=COUNTIFS(MONTH(E34:E46),2)”，然后按下【Enter】鍵，系統(tǒng)出現(xiàn)了錯(cuò)誤提示，如圖19-10所示。公式的邏輯沒有問題，那到底是哪里出錯(cuò)了呢？MONTH(E34:E46)的結(jié)果是{2;2;2;3;2;2;3;3;3;3;1;2;2}，這是一個(gè)數(shù)組，而COUNTIFS中的第1個(gè)參數(shù)是criteria_range。注意，“range”的意思是一個(gè)區(qū)域，所以COUNTIFS的第1，3，5，7，…參數(shù)是不支持?jǐn)?shù)組的，必須是區(qū)域，即必須是在Excel表格中畫出來一片單元格區(qū)域。與它有相同要求的參數(shù)還有ref、reference。有了第一個(gè)統(tǒng)計(jì)日期的基礎(chǔ)，我們繼續(xù)操作。統(tǒng)計(jì)的時(shí)候，不可能每一個(gè)月份都手動(dòng)輸入，更多的情況是在單元格中輸入1月、2月、3月等內(nèi)容，然后完成相應(yīng)的統(tǒng)計(jì)，如圖19-11所示。在I36單元格中輸入以下公式，并向下復(fù)制到I38單元格：=COUNTIFS($E$34:$E$46,'>='&DATE(2016,LEFTB(H36,2),1),$E$34:$E$46,'<'&DATE(2016,LEFTB(H36,2)+1,1))公式LEFTB(H36,2)在7.5節(jié)中講過的，從月份中提取左側(cè)2個(gè)字節(jié)，于是只把數(shù)字提取出來，得到'1'，而這個(gè)空格并不影響DATE函數(shù)的計(jì)算。DATE(2016,'1',1)返回結(jié)果“42370”，這個(gè)數(shù)字就相當(dāng)于日期2016-1-1。最后使用COUNTIFS函數(shù)完成相應(yīng)月份的統(tǒng)計(jì)。在2003版本及以前，SUMPRODUCT函數(shù)是多條件統(tǒng)計(jì)的神器，因?yàn)镾UMIF和COUNTIF在常規(guī)狀態(tài)下只能完成單條件的統(tǒng)計(jì)，自2007版本有了“流氓三兄弟”后，SUMPRODUCT就幾乎退出歷史舞臺(tái)了，因?yàn)橛?jì)算效率太慢了。在第7篇我們就會(huì)講解SUMPRODUCT函數(shù)。
|
