这个函数牛到不行了，7.53%的人竟然还不会用

微信扫码观看全套Excel、Word、PPT视频

作者：流浪铁匠
来源：Excel之家ExcelHome（ID：iexcelhome）

小伙伴们好啊，今天介绍一个还有7.53%的人不认识的函数：AGGREGATE

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课程

Excel教程相关推荐

Excel教程：同时冻结首行和尾行！你可会？刚下班老板就安排个苦差事，Excel请来个帮手3秒搞定！90后小姐姐打印excel表格前做了1个步骤，工资直接从4000涨到6000想要跟随滴答老师全面系统学习Excel，不妨关注《一周Excel直通车》视频课或者《Excel极速贯通班》。

《一周Excel直通车》视频课
包含Excel技巧、函数公式、
数据透视表、图表。
一次购买，永久学习。
[iframe]https://v.qq.com/iframe/preview.html?width=500&height=375&auto=0&vid=b05674i0j3h[/iframe]
最实用接地气的Excel视频课
《一周Excel直通车》
风趣易懂，快速高效，带您7天学会Excel
38 节视频大课
（已更新完毕，可永久学习）
理论+实操一应俱全

主讲老师：滴答

Excel技术大神，资深培训师；
课程粉丝100万+；
开发有《Excel小白脱白系列课》
《Excel极速贯通班》。

原价299元
限时特价 99 元，随时涨价
少喝两杯咖啡，少吃两袋零食
就能习得受用一生的Excel职场技能！

长按下面二维码立即购买学习

购课后，加客服微信：blwjymx3领取练习课件

让工作提速百倍的「Excel极速贯通班」
↓ 点击阅读原文，可直接购买。