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

很多使用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个最小值。其他的和上一个例子类似。不做过多解释。

以上呢,就是aggregate函数的常用方法,涉及的内容比较多,大家可以多看多练习,只有练习会了,并不断复习,才能整整的掌握。

(0)

相关推荐