excel中的这个AGGREGATE函数牛到不行了,5.21%的人竟然还不会用
发布时间:2022-06-01 00:31:13
AGGREGATE函数用法与SUBTOTAL函数类似,但在功能上比SUBTOTAL函数更加强大,不仅可以实现诸如SUM、AVERAGE、COUNT、LARGE、MAX等19个函数的功能,而且还可以忽略隐藏行、错误值、空值等,并且支持常量数组。
该函数的第一参数是1到19之间的数字,用于指定要使用的汇总方式:
第二参数是介于0到7之间的数字,指定在计算区域内要忽略哪些类型的值:
接下来咱们就说说这个函数的一些典型用法:
1、多个不连续区域忽略错误值直接求和
这个函数的强大之处就是在于第2参数可以指定参数来忽略错误值直接统计。如下图,蓝色区域中包含有不同的错误值,现在要对这几个不连续的区域求和。
公式为:
=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)
2、筛选状态下忽略错误值
如下图,在筛选后的数据区域中包含有错误值,如何对可见单元格进行统计呢?
公式为:
=AGGREGATE(9,7,B6:B18)
第一参数使用9,表示求和,第二参数使用7,表示忽略隐藏行和错误值。
3、一个公式解决多种统计效果
如下图,A3:B14单元格区域中是筛选后的的数据,要分别统计在可见区域和所有数据的最大、最小、平均、总和、计数和中位数。
只要一个公式就够了:
=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)
注意是区域数组公式,先选取C17:D22区域,然后在编辑栏写上公式,最后按ctrl+shift+enter三键录入。
4、向下求和
=AGGREGATE(9,3,A4:A$18)*2-AGGREGATE(9,7,A4:A$18)
除了向下求和的方向外还有隐藏和错误值,这是subtotal+sum(if)都无法实现的统计效果(录入方法是选取区域定位空值后编辑栏写完公式ctrl+enter批量填充)
5、这条开始才是重点-条件极值统计
这个函数提早五年就实现了2016才有的maxifs和minifs函数的统计效果,而且不需要三键。
如下图,要计算1车间对应的最小值,公式为:
=AGGREGATE(15,6,B4:B15/(A4:A15="1车间"),1)
公式中的第一参数使用15,表示使用SMALL函数,第二参数使用6,表示忽略错误值。要统计的区域是B4:B15/(A4:A15="1车间")
A4:A15="1车间"部分,先对比A列的车间是不是等于指定的条件。如果A4:A15单元格区域中等于"1车间",就返回逻辑值TRUE,否则返回逻辑值FALSE。然后再用B4:B15除以这组内存数组,结果为:
{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;19;47}
最后,AGGREGATE函数忽略里面的错误值,得到第一个最小值。
如果要计算1车间对应的第三个最小值,只需要将最后的1,变成3就好了。
如果要计算1车间对应的最大值,咱们可以修改一下第一参数,使用14,就是第k个最大值了。
6、一对多查询
如果想要一对多查询,一般会用到我们上述文章中的组合INDEX+SAMLL+IF。其实,用AGGREGATE函数替代也是能实现的。
如下图,要提取出二车间的所有工号,可以使用以下公式:
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($3:$12)/(A$3:A$12=D$3),ROW(A1))),"")
这个公式的思路和第五个公式基本相同。
7、统计同一单元格中的最大值
如下图,B列多人的考核情况被写到同一个单元格内,要统计其中的最大值。公式为:
=AGGREGATE(14,6,--MID(B4,ROW($1:$50),COLUMN(A:AZ)),1)
公式中的MID(B4,ROW($1:$50),COLUMN(A:AZ))部分,使用MID函数,依次从第1~50个字符处开始,各提取长度为1~50的字符串,得到一个巨长的内存数组。再使用两个负号,把内存数组中的文本变成错误值,数值仍然是其本身的值。
最后使用AGGREGATE函数,忽略内存数组中的错误值,计算出其中的第一个最小值。
8、同时统计指定条件的最大最小值
如下图所示,要同时统计1车间对应的最大和最小值。
先同时选中F4:G4单元格,编辑栏输入以下公式,按Ctrl+Shift+回车。
=AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)
AGGREGATE第一参数使用常量数组{16,15},表示分别使用最大值和最小值的计算规则。
最终的结果也是一个内存数组,所以要同时选中两个单元格输入。
这个函数的特性在于第一参数为14~19时,可以使用第四参数,此时的第四参数是支持数组的,因此就能玩出各种应用,来替代不能直接忽略错误值的small、large等函数。
当然,这函数有一点是无法替代SUBTOTAL的,因为AGGREGATE不支持多维引用,所以目前SUBTOTAL还是无法被完全替代的,这个咱们下次再聊。
excel中的这个AGGREGATE函数牛到不行了,5.21%的人竟然还不会用的下载地址:
猜你喜欢
- 有的时候,打开 Word 文档什么也不显示只有空白,文档的中内容到那里去了?对于这个 Word打开空白的问题,几乎出现在Word的每一个版本
- 如何让word表格自动编号?在Word中自动编号是比较麻烦的,通常都是手动输入数字,其实可以利用自动编号功能,自动填充Word表格序号。今天
- Excel快速访问工具栏中怎么添加求和∑?excel中经常使用求和功能,想将求和添加到快速访问工具栏中,该怎么添加呢?下面我们就来看看详细的
- word表格拷到excel中没有问题,但是excel中表格拷到word中就有问题了,格式会变乱,那么有什么方法可以解决呢?下面小编马上就告诉
- 经常和办公软件打交道的人肯定知道,如果把加密文档的密码忘了就相当于将这个文件报废了,那将会是一件多么可怕的事情。经常和办公软件打交道的人肯定
- 对于Excel中形状、图片、图表、SmartArt 图形等对象,可以用下面的快捷键来加快操作:一、选择所有对象选择某个对象后,按快捷键“Ct
- Excel中的分栏打印具体该如何实现呢?下面是小编带来的关于excel2003实现分栏打印的方法,希望阅读过后对你有所启发!excel200
- 1、首先输入你要做线性图的数据,我这里举例输入了五组数据。 2、用鼠标选中你要做线性图的数据,再点击菜单栏的“插入
- vlookup函数应用实例一:问题:如下图,已知表sheet1中的数据如下,如何在数据表二 sheet2 中如下引用:当学号随机出现的时候,
- Excel中经常需要合并同类项之后进行求和,合并同类项后具体该如何求和呢?下面是小编带来的关于excel2003合并同类项数据求和的教程,希
- 方法一:同时点击WIN+R快捷键吗,这时候会弹出运行对话框,在输入框里面输入"reg add HKCU\Software\Micr
- 之前发过一篇文章:Excel 多工作表合并,有朋友说,这Select语句还是不太好写,那今天演示一种不用函数,也不用SQL语句,还不用VBA
- 虽然在Excel单元格中可以实现大多数的计算功能,但有时候进行辅助计算还是需要计算器的。如果每次都单击“开始→程序→附件”再选计算器显得很繁
- IMABS函数用于计算以x+yi或x+yj文本格式表示的复数的绝对值(模);IMARGUMENT函数用于计算返回以弧度表示的角θ,如x+yi
- 有朋友说这个公式真不好写啊!那交给你两个方法:1、收藏那篇文章,用到的时候,复制公式;2、学习今天这个另类方法。方法实现第一步:选定所有工作
- Win10系统是深受广大用户喜爱的优秀系统。在持续使用的过程中,用户有时会对一些无法解决的小问题做出反应。今天,小编为大家带来了win10电
- Excel2007最主要的还是数据的处理,当然数据是要精确的,但是在Excel2007怎么样设置才能使数据更加精确呢?使用“以显示精度为准”
- 我们在插入WORD图片的时候,通常一张图片右边会留下一段空白,这样看起来很不美观。怎样设置文字环绕图片呢,今天,小编就教大家在Word中进行
- 在文档中常需要创建工作流程图、组织结构图等图示,手动制作操作起来就很麻烦了。想做一个组织结构图,但是又不知道怎么操作,怎么办呢?那Word如
- 在excel里,我们可能有时候不需要一些数据显示出来,需要隐藏起来,下面是几种单元格数据隐藏的3种方法,3种方法操作不同,原理不同,但都起到