- wps云文档使用教程:wps表格分类汇总功能怎么使用
- 手机wps怎么做表格教程:WPS中A4文档怎么调整为A3标准试卷并打印?
- wps怎么删除页眉页脚:WPS Text 2013实现带分节符的复杂交错页面
- wps演示倒计时5分钟怎么做:如何快速筛选——教你巧用条件格式!
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”}))
将会转换为:
=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”在第一个数组中并没有相配对的元素。
上一篇:wps页眉页脚怎么全部删除:excel怎么统计分隔符的字符串 下一篇:手机wps怎么做表格教程:excel怎么筛选内容
郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。