COUNTIFS函数处理数组？本文将详细解释COUNTIFS/SUMIFS函数的操作原理，特别是在将包含多个条件元素的数组传递给一个或多个Criteria_Range参数时。

让我们从一个例子开始，比如下面图1所示的数据。

图1

现在，要获得“雄性”为“性”，而“海狮”为“宠物”的数量，请使用以下公式：

=COUNTIFS(B2:B14，“男”，C2:C14，“海狮”)

要获得性的“雌性”和宠物的“海狮”的数量，可以使用以下公式：

=COUNTIFS(B2:B14，“女”，C2:C14，“海狮”)

那么，如果你想得到Sex为“公”或“母”，Pet为“海狮”的数字，你可以简单地加上上面两个公式：

=COUNTIFS(B2:B14，“雄性”，C2:C14，“海豹”)COUNTIFS(B2:B14，“雌性”，C2:C14，“海狮”)

此时，我们可以考虑使用数组作为参数来简化上面的公式：

=SUM(COUNTIFS(B2:B14，{“男”、“女”}，C2:C14，“Sealion”))

这将得到相同的结果。

接下来，我们添加另一个或条件：

=SUM(COUNTIFS(B2:B14，{“男”、“女”}，C2:C14，{“Sealion”、“Mite”})

结果是2。本来我们预测的结果应该是7，但实际上比前面公式得到的结果5要小。其实这个公式返回的是b栏的“公”和C栏的“海狮”或者b栏的“母”和C栏的“螨”的数量.从图1所示的表中可以看出，只有第12行和第14行符合条件。

对于这个公式，需要注意的是，两个常量数组中的每个元素都是相互对应的，“雄性”和“海狮”以及“雌性”和“螨虫”。该公式不认为b栏中的“雄性”和c栏中的“螨”是可选的，也不认为b栏中的“雌性”和c栏中的“海狮”

但是，如果我们要考虑这些交叉选择，如何才能统计出所有可能的对应条件呢？b列为“公”或“母”，c列为“海狮”或“螨”，得出满足条件数为7的结果。

在这一点上，您只需要对前面的公式做一个小的更改：

=SUM(COUNTIFS(B2:B14，{“男”、“女”}，C2:C14，{“Sealion”；“螨虫”}))

或者：

=SUM(COUNTIFS(B2:B14，{“男”；【女】}，C2:C14，{“Sealion”，“Mite”})

只需将其中一个常量数组中的逗号改为分号。

这里，一个常量数组是单列数组，另一个是单行数组，这使得Excel返回一个由这两个列数组的所有可能组合组成的二维数组，相当于下面图2所示的数组。

图2。

然后，将这四种情况的结果相加。

现在，让我们展开公式：

=SUM(COUNTIFS(B2:B14，{“男”、“女”}，C2:C14，{“Sealion”；“螨虫”}，D2:D14，{“篮球”}))

计算b栏的“男”或“女”、c栏的“海狮”或“螨虫”、d栏的“篮球”数量，结果为1。

现在，如果我们尝试给d列添加另一个条件，让我们看看会发生什么。公式：

=SUM(COUNTIFS(B2:B14，{“男”、“女”}，C2:C14，{“Sealion”；“螨虫”}，D2:D14，{“篮球”、“家谱”、“角色扮演”})

您可能希望返回5行，如下图3所示：

图3。

然而，上述公式的结果是2。

是语法错误吗？那就试试：

=SUM(COUNTIFS(B4:B16，{“男”、“女”}，C4:C16，{“Sealion”；“螨虫”}，D4:D16，{“篮球”；“家谱”；“角色扮演”}))

返回的结果是0。

这到底是怎么回事？

让我们看看前面的公式：

=SUM(COUNTIFS(B2:B14，{“男”、“女”}，C2:C14，{“Sealion”；“螨虫”}，D2:D14，{“篮球”、“家谱”、“角色扮演”})

将被转换为：

=SUM({0，1，0；1,0,0})

结果是一个由2行3列组成的数组。这个数组是怎么来的？

这里的关键是前面提到的元素“配对”。当两个(或多个)数组具有相同的“向量类型”(即要么是单列数组，要么是单行数组)时，Excel会将每个数组中对应的条件进行配对。因此，在上面的公式中，第一个数组{“男”、“女”}和第三个数组{“篮球”、“家谱”、“角色扮演”}是单行数组，Excel会将这些元素配对：第一个是B列的“男”和D列的“篮球”有多少，第二个是。

注意还有另外一个数组{“海狮”；“mite”}，这是一个单列数组，这将使我们能够构建一个二维数组。

此外，第三个数组中的第三个元素“角色扮演”在第一个数组中没有匹配的元素。

但是，Excel将继续构建一个适当大小的数组来容纳预期的返回值，即上面看到的2行3列的数组。事实上，对于两个(或多个)不同维度的数组，Excel解决冲突的方法是人为增加两个中较小的一个，使其大小等于这些数组中较大的一个。

它用零填充这些新创建的额外空间，然后根据需要对结果数组进行操作。下面，我们可以给出与上述中间结果{0，1，0；1，0，0}，分解如下图4所示。

图4

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

看看前面你想得到的第二个公式：

=SUM(COUNTIFS(B4:B16，{“男”、“女”}，C4:C16，{“Sealion”；“螨虫”}，D4:D16，{“篮球”；“家谱”；“角色扮演”}))

我们可以预期中间结果是：

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

这次是由6个元素组成的3行2列数组。此时，相同向量类型的数组是第二个数组{“海狮”；“螨虫”}和第三个数组{“篮球”；“家谱”；“角色扮演”}，那么它们的配对如下：“海狮”/“篮球”、“螨虫”/“家谱”、“？ "/“角色扮演”.

三组数据与第一个数组{“男”、“女”}的交叉运算结果如下图5所示。

图5

总结

虽然本文的主要目的是讨论SUMIFS/COUNTIFS函数系列的运算和语法，但学到的更重要的方面是，对基本方法更深层次的理解之一是通过这种结构进行计算。

有很多Exceller的工作清楚地表明了他们对标准配方技术的理解：使用FREQUENCY非常容易；写一个又长又复杂的数组公式；会记住成熟可靠的解决方案；善于在各种情况下使用大多数Excel函数。

但是你不会看到来自同一个人的许多mmult，也不会看到许多非标准和创新的数组操作(在mmult之后，也许转置位于最少使用和最少理解的函数的顶部)。然而，这两个函数本质上有相同的基本方面，即它们辅助我们处理要操纵的二维数组。不是工作表的单元格区域等可见的东西，而是只在Excel中间计算链深度内的临时的东西。