AGGREGATE等5个万能函数技巧解读，快来学习吧！

作者：老菜鸟来源：部落窝教育发布时间：2021-11-03 15:42:21点击：2598

分享到:

收藏收藏人气：0人

版权说明：原创作品，禁止转载。

编按：

Excel里有400多个函数，每个函数都是针对某一种问题而设计的，但是在这些函数中，有的函数身兼数能，一个函数就能具备很多种功能，因此被广大的函数爱好者冠以“万能函数”的称号，今天就来盘点一下Excel中都有哪些“万能函数”。

万能函数之一：SUBTOTAL

功能1.动态求和

公式为=SUBTOTAL(9,D2:D21)，具体效果看动图演示。

注意对比公式求和的结果与下方状态栏的求和结果始终保持一致。

功能2.动态求平均值

公式为=SUBTOTAL(1,D2:D21)，具体效果看动图演示

注意对比公式求平均值的结果与下方状态栏的求平均值结果始终保持一致。

功能3.动态求最大值

公式为=SUBTOTAL(4,D2:D21)，有兴趣的同学可以自己测试。

功能4.动态求最小值

公式为=SUBTOTAL(5,D2:D21)，有兴趣的同学可以自己测试。

这时候可能有些细心的伙伴已经发现了，通过修改SUBTOTAL的第一参数就能实现不同的功能，也就是说第一参数有多少选项这个函数就有多少个功能。

的确是这样的，SUBTOTAL函数的第一参数就能实现11种函数的功能，具体对照关系如图所示。

每种功能还有两种情况，1-11是对包含隐藏的数据进行求和，101-111是忽略隐藏的数据进行求和，隐藏的意思就是通过右键隐藏行。虽然对于使用者来说隐藏和筛选都可以让某些行看不见，但是对于这个函数来说是有区别的，详见动图。

可以看到使用9和109在有隐藏行的情况下求和结果不同，但对于筛选求和则无影响。

这是第一个万能函数，具备了22种功能的SUBTOTAL。

万能函数之二：AGGREGATE

这是一个比SUBTOTAL更为强大的万能函数，它具备19种函数的功能，8种应用场景（可以选择忽略哪些类型的值）。如果要把功能和场景组合起来的话，能实现一百多种统计效果。可以说SUBTOTAL会的AGGREGATE都会，但是AGGREGATE会的SUBTOTAL很多都不会。

AGGREGATE的基本格式为：= AGGREGATE（统计功能，忽略哪些值，数据区域，k值）

下图列举了AGGREGATE的19种功能代码：

可以看出AGGREGATE函数的统计功能确实要比SUBTOTAL函数更加丰富。

还有8种应用场景：

在这8个应用场景中，前四个比较少用到，个人觉得最有用的是6（忽略错误值），举个例子来说。

在数据源中有个错误值#N/A，这时候用公式=SUBTOTAL(9,D2:D21)就无法得到合计金额，而用公式=AGGREGATE(9,6,D2:D21)则不受影响，因为第二参数我们使用了6。

关于AGGREGATE的更多用法可以参阅往期教程：

https://mp.weixin.qq.com/s/lxE7OPuMiY6wgz6LrcxCjQ

这里要给大家强调的是：AGGREGATE虽说有四个参数，但是只有第一、三两个参数是必须要填的，这时候第二参数就默认为0，例如=AGGREGATE(9, ,D2:D21)。另外一点就是当第一参数填14-19的时候，第四参数就必须要填了，例如=AGGREGATE(14,7,D2:D21,2)，就表示10月1日的第二高金额。