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”}))
将会转换为：