Excel公式技巧06：COUNTIFS函数如何处理以数组⽅式提供的条件excelperfect
这篇⽂章将详细讲解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”/”Basketb
all”、”Mite”/”Genealogy”、”???”/”Roleplaying”。
这三组数据和第⼀个数组{'Male','Female'}交叉运算的结果如下图5所⽰。
图5
⼩结
尽管本⽂的主要⽬的是讨论SUMIFS/COUNTIFS函数系列的操作和语法，但学习到的更重要的⽅⾯是对基本⽅法更深刻的理解之⼀是通过这种结构来计算。
有许多的Exceller，其⼯作清楚地表明了他们对标准公式技术的理解：使⽤FREQUENCY⾮常容易；编写出长⽽复杂的数组公式；会记住经过实践检验的成熟的解决⽅案；善于在各种情况下使⽤绝⼤多数Excel函数。excel数组函数的实例
但是你不会看到来⾃同⼀个⼈的许多MMULT，⽽且也不会看到许多⾮标准的、创新的数组操作（在MMULT之后，也许TRANSPOSE居于最少使⽤和了解最少的函数的之⾸）。但是，这两个函数从本质上讲具有相同的基本⽅⾯，也就是说，它们辅助我们处理要操纵的⼆维数组。不是
像⼯作表单元格区域那样的那些可见的东西，⽽是那些仅位于Excel中间计算链深度之内并且是临时的东西。
此时，最重要的是，我们要了解可以操纵、查询甚⾄重新定义这些⼆维数组构造的基本⽅法。然⽽，我们当中有多少⼈对这些基本原理如此了解？就像为什么这么少的⼈在解决⽅案中使
⽤MMULT？
理解Excel如何“看到”事物，将更好地了解Excel！
注：本技巧整理⾃excelxor，有兴趣的朋友可以研阅原⽂。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。