COUNTIFS函数如何处理以数组方式提供的-英雄云拓展知识分享

这篇文章将详细解COUNTIFS/SUMIFS函数的运行原理，特别是将包括多个作为条件的元素的数组传递给一个或多个Criteria_Range参数时。

先看一个示例，以下所示的数据。

现在，想要得到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”的数量。从所示的表中可以看到，仅第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维数组，同等于下所示。

然后，对这4种情形所得到的结果求和。

下面，我们再来扩大一下，公式：

=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”}))

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

但是，上述公式的结果为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”}和第3个数组{“Basketball”,”Genealogy”,”Roleplaying”}都是单行数组，Excel将配对这些元素：第一个是有多少是列B中为“Male”并且列D中是“Basketball”，第2个是有多少是列B中为“Female”并且列D中是“Genealogy”。

注意到还有另外一个数组{“Sea lion”;”Mite”}，那是一个单列数组，这将会让我们能够构造一个2维数组。

并且，第3个数组中的第3个元素“Roleplaying”在第一个数组中并没有相配对的元素。

但是，Excel会继续构造适当大小的数组以容纳预设的返回值，即上面看到的2行3列的数组。事实上，对两个（或更多个）不同维度的数组，Excel解决冲突的方法是人为地增加两个中的较小者，以便使其尺寸等于这些数组中的较大者。

它是使用零填充这些新创建的过剩空间，然后针对需要对结果数组进行操作。下面，可以给出一个与上面中间结果{0,1,0;1,0,0}的等效表达式，其分解起来以下所示。

可以看出，先将3个数组中相同向量类型配对，然后与第3个数组交叉计算得到结果。

再看看前面想得到结果的第2个公式：

=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个元素组成。此时，相同向量类型的数组分别是第2个数组{“Sea lion”;”Mite”}和第3个数组{“Basketball”;”Genealogy”;”Roleplaying”}，因此配对以下：”Sea lion”/” Basketball”、”Mite”/”Genealogy”、”???”/”Roleplaying”。

这3组数据和第一个数组{“Male”,”Female”}交叉运算的结果以下所示。

小结

虽然本文的主要目的是讨论SUMIFS/COUNTIFS函数系列的操作和语法，但学习到的更重要的方面是对基本方法更深入的理解之一是经过这类结构来计算。

有许多的Exceller，其工作清楚地表示了他们对标准公式技术的理解：使用FREQUENCY非常容易；编写出长而复杂的数组公式；会记住经过实践检验的成熟的解决方案；善于在各种情况下使用绝大多数Excel函数。

但是你不会看到来自同一个人的许多MMULT，而且也不会看到许多非标准的、创新的数组操作（在MMULT以后，或许TRANSPOSE居于最少使用和了解最少的函数的之首）。但是，这两个函数从本质上具有相同的基本方面，也就是说，它们辅助我们处理要操纵的2维数组。不是像工作表单元格区域那样的那些可见的东西，而是那些仅位于Excel中间计算链深度以内并且是临时的东西。

此时，最重要的是，我们要了解可以操纵、查询乃至重新定义内涵这些2维数组构造的基本方法。但是，我们当中有多少人对这些基本原理如此了解？就像为何这么少的人在解决方案中使用MMULT？

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