http://www.pptjcw.com

excel怎么做表格:大牛函数:AGGREGATE

    AGGREGATE函数用法与SUBTOTAL函数类似,但在功能上比SUBTOTAL函数更加强大,不仅可以实现诸如SUM、AVERAGE、COUNT、LARGE、MAX等19个函数的功能,而且还可以忽略隐藏行、错误值、空值等,并且支持常量数组。

    该函数的第一参数是1到19之间的数字,用于指定要使用的汇总方式:

    excel怎么做表格:大牛函数:AGGREGATE

    第二参数是介于0到7之间的数字,指定在计算区域内要忽略哪些类型的值:

    excel怎么做表格:大牛函数:AGGREGATE

    接下来咱们就说说这个函数的一些典型用法:

    1、多个不连续区域忽略错误值直接求和

    这个函数的强大之处就是在于2参可以指定参数来忽略错误值直接统计
    如下图,蓝色区域中包含有不同的错误值,现在要对这几个不连续的区域求和。
    公式为:
    =AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

    excel怎么做表格:大牛函数:AGGREGATE

    2、筛选状态下忽略错误值

    如下图,在筛选后的数据区域中包含有错误值,如何对可见单元格进行统计呢?
    公式为:
    =AGGREGATE(9,7,B6:B18)

    excel怎么做表格:大牛函数:AGGREGATE

    第一参数使用9,表示求和,第二参数使用7,表示忽略隐藏行和错误值。

    3、一个公式解决多种统计效果

    如下图,A3:B14单元格区域中是筛选后的的数据,要分别统计在可见区域和所有数据的最大、最小、平均、总和、计数和中位数。
    只要一个公式就够了:
    =AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)

    excel怎么做表格:大牛函数:AGGREGATE

    注意是区域数组公式,先选取c17:d22区域,然后在编辑栏写上公式,最后按ctrl+shift+enter三键录入。

    4、向上求和你们都会,哪怕是筛选下的,向下呢?

    =AGGREGATE(9,3,A4:A$18)*2-AGGREGATE(9,7,A4:A$18)

    excel怎么做表格:大牛函数:AGGREGATE

    除了向下求和的方向外还有隐藏和错误值,这是subtotal+sum(if)都无法实现的统计效果
    (录入方法是选取区域定位空值后编辑栏写完公式ctrl+enter批量填充)

    5、这条开始才是重点-条件极值统计

    这个函数提早五年就实现了2016才有的maxifs和minifs函数的统计效果,而且不需要三键。
    如下图,要计算1车间对应的最小值,公式为:
    =AGGREGATE(15,6,B4:B15/(A4:A15="1车间"),1)

    excel怎么做表格:大牛函数:AGGREGATE

    公式中的第一参数使用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))),"")

    提示:如果您觉得本文不错,请点击分享给您的好友!谢谢

    上一篇:excel教程:数据验证是个宝,数据录入没烦恼 下一篇:excel表格制作教程:折叠表格,不会你就out啦

    郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。