COUNTIFS函數(shù)如何處理以數(shù)組方式提供的

COUNTIFS函數(shù)如何處理以數(shù)組方式提供的

這篇文章將詳細解COUNTIFS/SUMIFS函數(shù)的運行原理，特別是將包含多個作為條件的元素的數(shù)組傳遞給一個或多個Criteria_Range參數(shù)時。

先看一個示例，如下所示的數(shù)據(jù)。

現(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，可實際比上一個公式得到的結(jié)果5還要少。其實，這個公式返回的結(jié)果是：列B中是“Male”且列C中是“Sea lion”或者列B中是“Female”且列C中是“Mite”的數(shù)量。從所示的表中可以看到，僅第12行和第14行滿足條件。

對于這個公式，要注意的重要一點是：兩個常量數(shù)組中的每個元素彼此對應(yīng)，“Male”和“Sea lion”以及“Female”和“Mite”。該公式并未考慮B列中的“Male”和C列中的“Mite”是可選項，也未考慮B列中的“Female”和C列中的“Sea lion”。

但是，如果我們想考慮這些交叉選項，那么怎樣才能統(tǒng)計所有可能對應(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ù)組，等同于下所示。

然后，對這四種情形所得到的結(jié)果求和。

下面，我們再來擴展一下，公式：

=SUM(COUNTIFS(B2:B14,{“Male”,”Female”},C2:C14,{“Sealion”;”Mite”},D2:D14,{“Basketball”}))

計算列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”}))

可能想要返回下所示的5行：

然而，上述公式的結(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)鍵是之前提到的元素“配對”。當兩個（或多個）數(shù)組具有相同的“向量類型”（即要么都是單列數(shù)組，要么都是單行數(shù)組）時，Excel將對每個數(shù)組中相對應(yīng)條件進行配對。因此，在上面的公式中第一個數(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)建適當大小的數(shù)組以容納預(yù)期的返回值，即上面看到的2行3列的數(shù)組。實際上，對于兩個（或更多個）不同維度的數(shù)組，Excel解決沖突的方法是人為地增加兩個中的較小者，以便使其尺寸等于這些數(shù)組中的較大者。

它是使用零填充這些新創(chuàng)建的多余空間，然后根據(jù)需要對結(jié)果數(shù)組進行操作。下面，可以給出一個與上面中間結(jié)果{0,1,0;1,0,0}的等效表達式，其分解起來如下所示。

可以看出，先將三個數(shù)組中相同向量類型配對，然后與第三個數(shù)組交叉計算得到結(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”}交叉運算的結(jié)果如下所示。

小結(jié)

盡管本文的主要目的是討論SUMIFS/COUNTIFS函數(shù)系列的操作和語法，但學(xué)習(xí)到的更重要的方面是對基本方法更深刻的理解之一是通過這種結(jié)構(gòu)來計算。

有許多的Exceller，其工作清楚地表明了他們對標準公式技術(shù)的理解：使用FREQUENCY非常容易；編寫出長而復(fù)雜的數(shù)組公式；會記住經(jīng)過實踐檢驗的成熟的解決方案；善于在各種情況下使用絕大多數(shù)Excel函數(shù)。

但是你不會看到來自同一個人的許多MMULT，而且也不會看到許多非標準的、創(chuàng)新的數(shù)組操作（在MMULT之后，也許TRANSPOSE居于最少使用和了解最少的函數(shù)的之首）。但是，這兩個函數(shù)從本質(zhì)上具有相同的基本方面，也就是說，它們輔助我們處理要操縱的二維數(shù)組。不是像工作表單元格區(qū)域那樣的那些可見的東西，而是那些僅位于Excel中間計算鏈深度之內(nèi)并且是臨時的東西。

此時，最重要的是，我們要了解可以操縱、查詢甚至重新定義這些二維數(shù)組構(gòu)造的基本方法。然而，我們當中有多少人對這些基本原理如此了解？就像為什么這么少的人在解決方案中使用MMULT？

理解Excel如何“看到”事物，將更好地了解Excel！

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應(yīng)法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應(yīng)法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。