COUNTIFS函數(shù)處理數(shù)組？這篇文章將詳細(xì)講解COUNTIFS/SUMIFS函數(shù)的運(yùn)行原理，特別是將包含多個(gè)作為條件的元素的數(shù)組傳遞給一個(gè)或多個(gè)Criteria_Range參數(shù)時(shí)。
先看一個(gè)示例，如下圖1所示的數(shù)據(jù)。
圖1
現(xiàn)在，想要得到Sex為“Male”，Pet為“Sea lion”的數(shù)量，使用公式：
=COUNTIFS(B2:B14,”Male”,C2:C14,”Sea lion”)
而想要得到Sex為“Female”，Pet為“Sea lion”的數(shù)量，可使用公式：
=COUNTIFS(B2:B14,”Female”,C2:C14,”Sea lion”)
那么，想要得到Sex為“Male”或“Female”，Pet為“Sea lion”的數(shù)量，可簡(jiǎn)單地將上述兩個(gè)公式相加：
=COUNTIFS(B2:B14,”Male”,C2:C14,”Sealion”)+COUNTIFS(B2:B14,”Female”,C2:C14,”Sea lion”)
此時(shí)，我們可能會(huì)想到，使用數(shù)組作為參數(shù)來(lái)簡(jiǎn)化上面的公式：
=SUM(COUNTIFS(B2:B14,{“Male”,”Female”},C2:C14,”Sealion”))
這將得到同樣的結(jié)果5。
下面，我們?cè)偬砑右粋€(gè)OR條件：
=SUM(COUNTIFS(B2:B14,{“Male”,”Female”},C2:C14,{“Sealion”,”Mite”}))
結(jié)果為2。本來(lái)我們預(yù)測(cè)的結(jié)果應(yīng)該是7，可實(shí)際比上一個(gè)公式得到的結(jié)果5還要少。其實(shí)，這個(gè)公式返回的結(jié)果是：列B中是“Male”且列C中是“Sea lion”或者列B中是“Female”且列C中是“Mite”的數(shù)量。從圖1所示的表中可以看到，僅第12行和第14行滿足條件。
對(duì)于這個(gè)公式，要注意的重要一點(diǎn)是：兩個(gè)常量數(shù)組中的每個(gè)元素彼此對(duì)應(yīng)，“Male”和“Sea lion”以及“Female”和“Mite”。該公式并未考慮B列中的“Male”和C列中的“Mite”是可選項(xiàng)，也未考慮B列中的“Female”和C列中的“Sea lion”。
但是，如果我們想考慮這些交叉選項(xiàng)，那么怎樣才能統(tǒng)計(jì)所有可能對(duì)應(yīng)的條件？列B中是“Male”或“Female”而列C中是“Sea lion”或“Mite”，得出滿足條件的數(shù)量為7的結(jié)果。
此時(shí)，只需要對(duì)上一個(gè)公式做個(gè)小小改變：
=SUM(COUNTIFS(B2:B14,{“Male”,”Female”},C2:C14,{“Sealion”;”Mite”}))
或者：
=SUM(COUNTIFS(B2:B14,{“Male”;”Female”},C2:C14,{“Sealion”,”Mite”}))
只是將其中一個(gè)常量數(shù)組中的逗號(hào)改為分號(hào)。
這里，一個(gè)常量數(shù)組是單列數(shù)組，另一個(gè)是單行數(shù)組，這使得Excel返回一個(gè)由這兩列數(shù)組的所有可能組合組成的一個(gè)二維數(shù)組，等同于下圖2所示。
圖2
然后，對(duì)這四種情形所得到的結(jié)果求和。
下面，我們?cè)賮?lái)擴(kuò)展一下，公式：
=SUM(COUNTIFS(B2:B14,{“Male”,”Female”},C2:C14,{“Sealion”;”Mite”},D2:D14,{“Basketball”}))
計(jì)算列B中是“Male”或“Female”、列C中是“Sea lion”或“Mite”且列D中是“Basketball”的數(shù)量，結(jié)果為1。
現(xiàn)在，如果我們?cè)噲D給列D再添加一個(gè)條件，看看會(huì)發(fā)生什么。公式：
=SUM(COUNTIFS(B2:B14,{“Male”,”Female”},C2:C14,{“Sealion”;”Mite”},D2:D14,{“Basketball”,”Genealogy”,”Roleplaying”}))
可能想要返回下圖3所示的5行：
圖3
然而，上述公式的結(jié)果為2。
是語(yǔ)法錯(cuò)誤嗎？那么試試：
=SUM(COUNTIFS(B4:B16,{“Male”,”Female”},C4:C16,{“Sealion”;”Mite”},D4:D16,{“Basketball”;”Genealogy”;”Roleplaying”}))
返回的結(jié)果是0。
這到底是怎么回事？
讓我們看看前面的這個(gè)公式：
=SUM(COUNTIFS(B2:B14,{“Male”,”Female”},C2:C14,{“Sealion”;”Mite”},D2:D14,{“Basketball”,”Genealogy”,”Roleplaying”}))
將會(huì)轉(zhuǎn)換為：
=SUM({0,1,0;1,0,0})
其中間結(jié)果為一個(gè)由2行3列組成的數(shù)組。這個(gè)數(shù)組是怎么來(lái)的？
這里的關(guān)鍵是之前提到的元素“配對(duì)”。當(dāng)兩個(gè)（或多個(gè)）數(shù)組具有相同的“向量類型”（即要么都是單列數(shù)組，要么都是單行數(shù)組）時(shí)，Excel將對(duì)每個(gè)數(shù)組中相對(duì)應(yīng)條件進(jìn)行配對(duì)。因此，在上面的公式中第一個(gè)數(shù)組{“Male”,”Female”}和第三個(gè)數(shù)組{“Basketball”,”Genealogy”,”Roleplaying”}都是單行數(shù)組，Excel將配對(duì)這些元素：第一個(gè)是有多少是列B中為“Male”并且列D中是“Basketball”，第二個(gè)是有多少是列B中為“Female”并且列D中是“Genealogy”。
注意到還有另一個(gè)數(shù)組{“Sea lion”;”Mite”}，那是一個(gè)單列數(shù)組，這將會(huì)讓我們能夠構(gòu)造一個(gè)二維數(shù)組。
并且，第三個(gè)數(shù)組中的第三個(gè)元素“Roleplaying”在第一個(gè)數(shù)組中并沒(méi)有相配對(duì)的元素。
然而，Excel會(huì)繼續(xù)構(gòu)建適當(dāng)大小的數(shù)組以容納預(yù)期的返回值，即上面看到的2行3列的數(shù)組。實(shí)際上，對(duì)于兩個(gè)（或更多個(gè)）不同維度的數(shù)組，Excel解決沖突的方法是人為地增加兩個(gè)中的較小者，以便使其尺寸等于這些數(shù)組中的較大者。
它是使用零填充這些新創(chuàng)建的多余空間，然后根據(jù)需要對(duì)結(jié)果數(shù)組進(jìn)行操作。下面，可以給出一個(gè)與上面中間結(jié)果{0,1,0;1,0,0}的等效表達(dá)式，其分解起來(lái)如下圖4所示。
圖4
可以看出，先將三個(gè)數(shù)組中相同向量類型配對(duì)，然后與第三個(gè)數(shù)組交叉計(jì)算得到結(jié)果。
再看看前面想得到結(jié)果的第二個(gè)公式：
=SUM(COUNTIFS(B4:B16,{“Male”,”Female”},C4:C16,{“Sealion”;”Mite”},D4:D16,{“Basketball”;”Genealogy”;”Roleplaying”}))
我們可以預(yù)料到中間結(jié)果是：
=SUM({0,0;0,0;0,0})
這次是3行2列數(shù)組，由6個(gè)元素組成。此時(shí)，相同向量類型的數(shù)組分別是第二個(gè)數(shù)組{“Sea lion”;”Mite”}和第三個(gè)數(shù)組{“Basketball”;”Genealogy”;”Roleplaying”}，因此配對(duì)如下：”Sea lion”/” Basketball”、”Mite”/”Genealogy”、”???”/”Roleplaying”。
這三組數(shù)據(jù)和第一個(gè)數(shù)組{“Male”,”Female”}交叉運(yùn)算的結(jié)果如下圖5所示。
圖5
小結(jié)
盡管本文的主要目的是討論SUMIFS/COUNTIFS函數(shù)系列的操作和語(yǔ)法，但學(xué)習(xí)到的更重要的方面是對(duì)基本方法更深刻的理解之一是通過(guò)這種結(jié)構(gòu)來(lái)計(jì)算。
有許多的Exceller，其工作清楚地表明了他們對(duì)標(biāo)準(zhǔn)公式技術(shù)的理解：使用FREQUENCY非常容易；編寫出長(zhǎng)而復(fù)雜的數(shù)組公式；會(huì)記住經(jīng)過(guò)實(shí)踐檢驗(yàn)的成熟的解決方案；善于在各種情況下使用絕大多數(shù)Excel函數(shù)。
但是你不會(huì)看到來(lái)自同一個(gè)人的許多MMULT，而且也不會(huì)看到許多非標(biāo)準(zhǔn)的、創(chuàng)新的數(shù)組操作（在MMULT之后，也許TRANSPOSE居于最少使用和了解最少的函數(shù)的之首）。但是，這兩個(gè)函數(shù)從本質(zhì)上講具有相同的基本方面，也就是說(shuō)，它們輔助我們處理要操縱的二維數(shù)組。不是像工作表單元格區(qū)域那樣的那些可見(jiàn)的東西，而是那些僅位于Excel中間計(jì)算鏈深度之內(nèi)并且是臨時(shí)的東西。
