excel函数之王aggregate函数用法详解，功能真的好强

Excel函数 2023-01-08

很多使用excel的朋友，竟然不知道aggregate函数，更不知道它能实现什么。实际上aggregate函数和subtotal函数很像，但是比subtotal函数更更直观，功能更强大。我们在介绍完aggregate的语法后，会给大家演示下，aggregate函数的几种常用方法。

语法：AGGREGATE(function_num, options, array, [k])

翻译成中文：AGGREGATE(合计函数, 忽略参数, 数组, [参数])

参数1：合计函数，aggregate函数支持的函数，如下图所示

参数2：忽略参数，可选择的参数如下图所示：

参数3：数值，也就是引用的区域。

参数4：不是必要参数，这个K，可以是个索引，当第一个参数为small或者large函数的时候，可以当索引号。

下面我们来讲下aggregate函数的具体应用。

一、忽略错误值求和。当一列数据中出现#DIV/0! #REF!#NAME?等错误值的时候，求和结果为错误值，解决办法，要么使用辅助列，要么使用sumif函数，都比较的复杂，使用aggregate函数一部就可以完成。具体操作如下

公式解读：=AGGREGATE(9,6,B2:B10)

第一个参数9，代表sum函数，第二个参数6，为忽略错误值，第三个参数是求和的区域。

二、生成连续的序列，就算删除和隐藏行，行号仍然连续

函数解读：=AGGREGATE(3,5,$A$2:A3)

第一个参数 3，代表counta函数，counta函数是对非空值计数

第二个参数5，忽略隐藏行，所以在隐藏后行号仍然连续

第三个参数为数据区域，$A$2:A3向下填充，变为$A$2:A4，$A$2:A5.。。。所以就产生了连续的序号。

由于设置了忽略隐藏行，并且用counta生成了序号，所以就算删除和隐藏行，行号仍然连续。

三、输入内容，自动产生连续序号，就算删除和隐藏行，序号仍然连续。

函数解读：=IF(B2<>"",AGGREGATE(3,5,$B$1:B1),"")

AGGREGATE(3,5,$B$1:B1)的意思和例2基本相似，在这就不多说。

我们用if函数判断B2单元格是否为空，如果不为空，则显示AGGREGATE(3,5,$B$1:B1)，如果为空，就显示为空。所以输入内容就产生了序号。

四，取得一列数据的任一最大值或者最小值，删除或者隐藏行，自动改变。比如求一列中，工资第2高的人，或者第2低的人。操作如下，这次我们要使用到第4个参数

求：工资第2高的人

函数解读：=AGGREGATE(14,5,E2:E8,2)

第一个参数14代表large函数，large函数是取得第N个最大值的意思。

第二个参数5，代表忽略隐藏行。

第三个参数为数据区域。第四个参数2 代表求第二个最大值。

当我们隐藏行后，最大值会进行变化。

求：工资第二低的人。

函数解读：=AGGREGATE(15,5,E2:E8,2)

15代表small函数，取第N个最小值。其他的和上一个例子类似。不做过多解释。