excel公式： 統計滿足多個條件的條目數量
如下圖1所示，左邊的表格是一個測試表，學生要根據單元格A3:A12中的國家名，在列B和列C相應的單元格中填寫該國家的首都和使用的貨幣。右邊的表格是正確答案。
圖1
要求在單元格C1中輸入一個公式，計算學生答對的數量。在圖1所示的示例中，答案是4，也就是說左邊的表格中有四行是正確的，如表中高亮顯示的行。
公式要求：
1. 盡可能簡短，即公式不僅要能夠得到正確的答案，而且字符數盡可能少。
2. 引用的單元格區域必須同時包含行和列，不允許只出現行或列（例如3:12、A:C）。
3. 不允許使用名稱。
先不看答案，自已動手試一試。
公式
下面是兩個解決方案，去掉等號后都是56個字符。
公式1：
=COUNT(MATCH(A3:A12&B3:B12&C3:C12,E3:E12&F3:F12&G3:G12,))
公式2：
=SUM(COUNTIFS(A3:A12,E3:E12,B3:B12,F3:F12,C3:C12,G3:G12))
這兩個公式中，公式2更好些。因為公式1理論上有可能出錯，例如，如果表中同時存在France-Paris-Euro和Franc-Eparise-Uro（不存在該名字的國家、城市或貨幣），則會得出不正確的結果。然而，發生這種可能性的機率應該非常小，但為確保萬無一失，可以使用下面的公式：
=COUNT(MATCH(A3:A12&” “&B3:B12&” “&C3:C12,E3:E12&” “&F3:F12&” “&G3:G12,))
即在公式1中添加合適的分隔符。
公式解析
先看看公式1：
=COUNT(MATCH(A3:A12&B3:B12&C3:C12,E3:E12&F3:F12&G3:G12,))
這是一個簡單而優雅的解決方案，是很好的公式技巧之一，提供了一種獲取所需數量的方法，而不需要使用諸如VLOOKUP或INDEX/MATCH函數組合。
公式首先分別連接兩個表中同一行的字符串，在十個字符串中執行一系列的匹配查找。因此，公式1可轉換為：
=COUNT(MATCH({“FranceParisManat”;”SerbiaBelgradeDinar”;”LithuaniaVilniusZ?oty”;”HungaryBudapestForint”;”AndorraChisinauManat”;”UkraineSofiaHryvnia”;”ArmeniaYerevanDram”;”RomaniaRomeLeu”;”BulgariaSofiaLev”;”CroatiaBerlinLira”},{“AndorraAndorra laVellaEuro”;”ArmeniaYerevanDram”;”BulgariaSofiaLev”;”CroatiaZagrebKuna”;”FranceParisEuro”;”HungaryBudapestForint”;”LithuaniaVilniusLitas”;”RomaniaBucharestLeu”;”SerbiaBelgradeDinar”;”UkraineKievHryvnia”},))
這里，省略了MATCH函數的參數match_type，Excel默認為精確匹配即等效于該參數指定為0，這樣公式1轉換為：
=COUNT({#N/A;9;#N/A;6;#N/A;#N/A;2;#N/A;3;#N/A})
COUNT函數忽略傳遞給它的參數中的錯誤值，因此，公式1的結果為：
4
再看看更為健壯的公式2：
=SUM(COUNTIFS(A3:A12,E3:E12,B3:B12,F3:F12,C3:C12,G3:G12))
注意，當參數criteria指定的值包含多個元素時，在合理地強制轉換（例如作用在COUNTIFS上的外部函數和數組公式CSE輸入）后，Excel將分別計算數組里的每個元素。
進一步說，這里有多個參數criteria指定的值都由多個元素（E3:E12、F3:F12、G3:G12）組成，Excel執行一系列單獨的COUNTIFS計算。換句話說，公式2中的COUNTIFS表達式等同于執行以下十個單獨的計算中的每一個：
=COUNTIFS(A3:A12,E3,B3:B12,F3,C3:C12,G3)
=COUNTIFS(A3:A12,E4,B3:B12,F4,C3:C12,G4)
=COUNTIFS(A3:A12,E5,B3:B12,F5,C3:C12,G5)
…
=COUNTIFS(A3:A12,E12,B3:B12,F12,C3:C12,G12)
然后匯總結果。
以這種方式，不難理解為什么這種構造能夠為我們提供想要的結果，因為上述公式顯然分別等于：0（測試表中A列為“Andorra”且B列中對應的條目為“Andorra la Vella”且C列中的對應條目為“Euro”的行數），1（A列=“Armenia”，B列=“Yerevan”且C列為“Dram”的行數），1（A列=“Bulgaria”，B列=“Sofia”，C列=“Lev”的行數）等。這樣，公式2轉換為：
=SUM({0;1;1;0;0;1;0;0;1;0})
結果為：
4
