首发公众号:EXCEL办公实战
在EXCEL中近300个函数,真要选出一个最强,那么我愿意将他给你“我愿称你为最强:” - AGGREGATE
如果你使用过SUBTOTAL,可能觉得他第一参数,支持22种统计,为此而感觉强大,那么AGGREGATE19*8=152种,虽然其中可能有个别无效组合,但是这上百种组合足够让你感觉到惊艳。先来看一下AGGREGATE简介,感受一下强者的装备:
版本出现:Excel新增功能说明:返回列表或数据库中的合计。AGGREGATE 函数可将不同的聚合函数应用于列表或数据库,并提供忽略隐藏行和错误值的选项。语法说明:
1、引用形式:AGGREGATE(function_num, options, ref1, [ref2], …)
2、数组形式:AGGREGATE(function_num, options, array, [k])
数组方式可以数组公式不用三键,这点会让我们以往很多需要三键的公式得到解放。
参数说明:
1、function_num,共计19中,其中常用,已标出
2、options:共8种
0-3:强化可以忽略区域中已经使用的AGGREGATE和SUBTOTAL利用这点我们可以做一下向下求和的问题
在数组中K值,是获取结果中的第几个!第一参数为如下函数是配合使用函数LARGE(array,k),SMALL(array,k),PERCENTILE.INC(array,k),QUARTILE.INC(array,quart),PERCENTILE.EXC(array,k),QUARTILE.EXC(array,quart)
实际案例
案例中我们统一使用agg简称
案例1:忽略错误的应用-MAX最大值
传统方式,Max不会忽略错误值,所以直接报错,agg则轻松搞定
案例2:可见和全部的应用
第一和第二参数都支持常量数组,这样我们就可以同时按照各种维度统计这里只用了一个公式,Ctrl+Enter录入,区域数组
案例3:数组及k参数用法-一式搞定最大最小agg数组用法,如何非区域数组公式,我们可以直接不用三键
案例4:参数2忽略AGG和SUBTOTAL的用法这里我们利用2参数:1这里不仅能忽略下方的agg,还能在筛选情况下,只求可见区域。
案例5:也谈1对多查找
万金油公式大家已经使用很多年了,但是其需要三键,对于新手并不友好,经常忘记三键保存,前面我们知道agg可以数组用法且不用三键,在配合k参数那么,我们就可以这么玩!
公式:=IFERROR(INDEX($C:$D,AGGREGATE(15,6,ROW($C$3:$C$13)/($B$3:$B$13=$G$2),ROW(B1)),MATCH(G$4,$C$2:$D$2,)),"")
篇幅限制,我们就不再举更多的例子,想必想必大家已经感受到了他的强大!!
为了更好的与大家分享,原创一篇,整理了三个小时!!!
Excel办公实战,高效办公,每天进步一点点!