COUNTIFS函数如何处理以数组方式提供的
发布时间:2023-09-20 21:01:44
这篇文章将详细解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返回一个由这两列数组的所有可能组合组成的一个二维数组,等同于下所示。
然后,对这四种情形所得到的结果求和。
下面,我们再来扩展一下,公式:
=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”}和第三个数组{“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}的等效表达式,其分解起来如下所示。
可以看出,先将三个数组中相同向量类型配对,然后与第三个数组交叉计算得到结果。
再看看前面想得到结果的第二个公式:
=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”/” Basketball”、”Mite”/”Genealogy”、”???”/”Roleplaying”。
这三组数据和第一个数组{“Male”,”Female”}交叉运算的结果如下所示。
小结
尽管本文的主要目的是讨论SUMIFS/COUNTIFS函数系列的操作和语法,但学习到的更重要的方面是对基本方法更深刻的理解之一是通过这种结构来计算。
有许多的Exceller,其工作清楚地表明了他们对标准公式技术的理解:使用FREQUENCY非常容易;编写出长而复杂的数组公式;会记住经过实践检验的成熟的解决方案;善于在各种情况下使用绝大多数Excel函数。
但是你不会看到来自同一个人的许多MMULT,而且也不会看到许多非标准的、创新的数组操作(在MMULT之后,也许TRANSPOSE居于最少使用和了解最少的函数的之首)。但是,这两个函数从本质上具有相同的基本方面,也就是说,它们辅助我们处理要操纵的二维数组。不是像工作表单元格区域那样的那些可见的东西,而是那些仅位于Excel中间计算链深度之内并且是临时的东西。
此时,最重要的是,我们要了解可以操纵、查询甚至重新定义这些二维数组构造的基本方法。然而,我们当中有多少人对这些基本原理如此了解?就像为什么这么少的人在解决方案中使用MMULT?
理解Excel如何“看到”事物,将更好地了解Excel!
猜你喜欢
- 在Word 2007文档中,用户可以通过多种方式设置图片尺寸。例如拖动Word 2007图片控制手柄、指定图片宽度和高度数值等,下面介绍最常
- 很多用户在使用Word编辑一些文档的时候,经常会去添加页眉,而在添加页眉之后就经常会出现横线,这时候就可以通过清除页眉格式来解决这个问题,那
- 如下图所示一般造成打开word出现关闭word提示发送错误报告的故障其主要原因为word程序其中的某个文件出现了错误或者被损坏的情况。故障分
- 有很多用户遇到win10不能打开设置,右键个性化,文件没有相关程序执行操作,请安装程序,或者,如果安装程序,请在默认程序控制面板上创建关闭,
- 打开excel2010,准备好需要整理的数据。调整excel格式,一般excel格式为时间格式的,需要调整成常规。选择数据,点击‘插入’菜单
- 最近有位win10系统用户使用电脑的时候,想要查看电脑主板型号不知道在哪里查看,我们只有对主板型号有一定的了解,才能装配最适合设备的软件,因
- word2016表格数字无法居中怎么办?word2016表格中输入数据以后,发现数据居中布料,点居中没反应,该怎么办呢?下面我们就来看看wo
- Win10打开文件夹闪退怎么办?其实这种问题一般出现在Win10 1909版本的Windows更新之后才会出现的。具体情况是用户使用鼠标指向
- 步骤一:鼠标左键双击计算机桌面Word2013程序图标,将其打开运行。在打开的Word2013程序窗口,点击“打开其他文档”,在文件存放的路
- Word能够给我们办公带来非常大的便捷,有了Word,我们可以轻松完成各种文档的制作。当然,前提必须是我们要熟练的掌握Word,如果您还对W
- 在使用win10家庭版的时候,如果我们担心自己的电脑被他人使用或者盗取自己的个人信息等,我们就可以给电脑设置一个开机密码,这样电脑就不会给他
- Excel里计算还贷明细非常简单,我们来看针对等额本息、等额本金两种不同方式如何计算每月应还款额。例:同事小张为了买新房准备到银行贷款30万
- 最近有用户在升级Win10系统的时候遇到了更新错误,提示错误代码0xc1900101,这是什么原因呢?这个代码是一系列的更新安装失败代码,解
- 可以使用分节符更改文档中一个或多个页面的布局或格式。例如,可以将单栏页某部分的布局设置为双栏。可以划分文档中的章,使各章的页码从 1 开始;
- 签订买卖合同是我们成交的必要经过,合同成功签订之前,买卖双方为了自己利益最大话,都希望合同的中的各个条款对自己有益,在签订合同之前,一份合同
- 今天,我将简要分析win10系统中关闭文件安全警告的方法。在使用win10系统的过程中,有时会遇到打开文件提示文件安全警告的问题,但文件是安
- win10系统是一个非常有用的智能系统,强大的智能可以帮助用户节省大量的计算机操作,但最近有很多朋友反应使用win10系统会遇到黑屏或只有鼠
- 1、笔记本或者台式机开机后,找到“wps表格”的图标。2、点击打开”wps表格“,进入”wps表格“主页面3、点击左上角的“WPS表格”后的
- 在日常工作中,经常会被一些Word小问题所干扰,影响工作进行,今天Word小编与大家分享5个实用的Word技巧。1、边框怎么加阴影?如下图所
- 你是否也对Word的打印功能十分的了解呢?尤其是你知道如何实现逆而打印吗?今天,就给大家介绍一些Word中的各种打印技巧:大家都知道,尤其是