COUNTIFS函數處理數組

COUNTIFS函數處理數組

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

先看一個示例，如下圖1所示的數據。

圖1

現在，想要得到Sex為“Male”，Pet為“Sea lion”的數量，使用公式：

=COUNTIFS(B2:B14,”Male”,C2:C14,”Sea lion”)

而想要得到Sex為“Female”，Pet為“Sea lion”的數量，可使用公式：

=COUNTIFS(B2:B14,”Female”,C2:C14,”Sea lion”)

那么，想要得到Sex為“Male”或“Female”，Pet為“Sea lion”的數量，可簡單地將上述兩個公式相加：

=COUNTIFS(B2:B14,”Male”,C2:C14,”Sealion”)+COUNTIFS(B2:B14,”Female”,C2:C14,”Sea lion”)

此時，我們可能會想到，使用數組作為參數來簡化上面的公式：

=SUM(COUNTIFS(B2:B14,{“Male”,”Female”},C2:C14,”Sealion”))

這將得到同樣的結果5。

下面，我們再添加一個OR條件：

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

結果為2。本來我們預測的結果應該是7，可實際比上一個公式得到的結果5還要少。其實，這個公式返回的結果是：列B中是“Male”且列C中是“Sea lion”或者列B中是“Female”且列C中是“Mite”的數量。從圖1所示的表中可以看到，僅第12行和第14行滿足條件。

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

但是，如果我們想考慮這些交叉選項，那么怎樣才能統計所有可能對應的條件？列B中是“Male”或“Female”而列C中是“Sea lion”或“Mite”，得出滿足條件的數量為7的結果。

此時，只需要對上一個公式做個小小改變：

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

或者：

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

只是將其中一個常量數組中的逗號改為分號。

這里，一個常量數組是單列數組，另一個是單行數組，這使得Excel返回一個由這兩列數組的所有可能組合組成的一個二維數組，等同于下圖2所示。

圖2

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

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

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

計算列B中是“Male”或“Female”、列C中是“Sea lion”或“Mite”且列D中是“Basketball”的數量，結果為1。

現在，如果我們試圖給列D再添加一個條件，看看會發生什么。公式：

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

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

圖3

然而，上述公式的結果為2。

是語法錯誤嗎？那么試試：

=SUM(COUNTIFS(B4:B16,{“Male”,”Female”},C4:C16,{“Sealion”;”Mite”},D4:D16,{“Basketball”;”Genealogy”;”Roleplaying”}))

返回的結果是0。

這到底是怎么回事？

讓我們看看前面的這個公式：

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

將會轉換為：

=SUM({0,1,0;1,0,0})

其中間結果為一個由2行3列組成的數組。這個數組是怎么來的？

這里的關鍵是之前提到的元素“配對”。當兩個（或多個）數組具有相同的“向量類型”（即要么都是單列數組，要么都是單行數組）時，Excel將對每個數組中相對應條件進行配對。因此，在上面的公式中第一個數組{“Male”,”Female”}和第三個數組{“Basketball”,”Genealogy”,”Roleplaying”}都是單行數組，Excel將配對這些元素：第一個是有多少是列B中為“Male”并且列D中是“Basketball”，第二個是有多少是列B中為“Female”并且列D中是“Genealogy”。

注意到還有另一個數組{“Sea lion”;”Mite”}，那是一個單列數組，這將會讓我們能夠構造一個二維數組。

并且，第三個數組中的第三個元素“Roleplaying”在第一個數組中并沒有相配對的元素。

然而，Excel會繼續構建適當大小的數組以容納預期的返回值，即上面看到的2行3列的數組。實際上，對于兩個（或更多個）不同維度的數組，Excel解決沖突的方法是人為地增加兩個中的較小者，以便使其尺寸等于這些數組中的較大者。

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

圖4

可以看出，先將三個數組中相同向量類型配對，然后與第三個數組交叉計算得到結果。

再看看前面想得到結果的第二個公式：

=SUM(COUNTIFS(B4:B16,{“Male”,”Female”},C4:C16,{“Sealion”;”Mite”},D4:D16,{“Basketball”;”Genealogy”;”Roleplaying”}))

我們可以預料到中間結果是：

=SUM({0,0;0,0;0,0})

這次是3行2列數組，由6個元素組成。此時，相同向量類型的數組分別是第二個數組{“Sea lion”;”Mite”}和第三個數組{“Basketball”;”Genealogy”;”Roleplaying”}，因此配對如下：”Sea lion”/” Basketball”、”Mite”/”Genealogy”、”???”/”Roleplaying”。

這三組數據和第一個數組{“Male”,”Female”}交叉運算的結果如下圖5所示。

圖5

小結

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

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

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

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

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