免费咨询热线：400-699-1716 
微信
APP
7.53%的财务人竟然还不会用！
文章来源：网络
发布时间：2019-06-06 14:38
阅读：1410次
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个*大值了。
当然，这函数有一点是无法替代subtotal的，因为aggregate不支持多维引用，所以目前subtotal还是无法完全替代的
本文来自网络，如涉及作品内容、版权及其它问题，请与本网编辑联系，我们将在*时间删除。Email:editor@zbgedu.com
财苑【Gold】
财会精英的聚集地，扫一扫你就是一员
免费领取 2025年证书课程学习资料
请大家认真填写以下信息，获取2025年证书课程学习资料包，会以网盘链接的形式给到大家，点击免费领取后请尽快保存。
*姓名不能为空
*手机号错误
*验证码错误
考试问答
Brian Peng
讲师认证：雅思、托福讲师
8年教龄，英汉口译专业硕士毕业，英语专业八级，国家人社部二级口笔译证书 前啄木鸟教育深圳分公司钻石金牌导师，教学主管；数百场国际会议同声传译和交替传译经历，曾服务于各大政府机关，沃尔玛，美国中小学校长协会，第45届世界体操锦标赛组委会，中国-东盟博览会组委会，海外高校，多国驻广州大使馆等。
免费下载老师推荐的学习资料
免费直播
