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