Excel函数学习41:AGGREGATE函数

 

讲解AGGREGATE函数及应用示例...



AGGREGATE函数返回列表或数据库中的合计。AGGREGATE函数消除了条件格式的限制,如果区域中存在错误,则数据条、图标集和色阶将无法显示条件格式。这是因为当计算区域存在错误时,MIN、MAX和PERCENTILE函数不进行计算。同样,LARGE、SMALL和STDEVP函数也会影响某些条件格式规则的相应功能。通过使用AGGREGATE函数,将忽略这些错误。AGGREGATE函数可以将不同的聚合函数应用于列表或数据库,并提供忽略隐藏行和错误值的选项。



图1

什么情况下使用AGGREGATE函数?

AGGREGATE函数是在Excel 2010中引入的一个非常强大的函数,可以对列表或数据库应用不同的聚合函数并忽略隐藏行和错误值。它能够:

  • 计算最大值、最小值、中值等
  • (不用数组公式)计算满足多条件的数据最大值
  • (不用数组公式)提取满足多个条件的数据


AGGREGATE函数语法

AGGREGATE函数有两种形式,一种是引用形式,一种是数组形式。其引用形式语法如下:

  • AGGREGATE(function_num,options,ref1,ref2,[ref3],…)
其数组形式语法如下:

  • AGGREGATE(function_num,options,array,[k])
  • [list]
  • function_num: 必需。一个在1至19之间的数字,指定要使用的函数,其中:1为AVERAGE函数,2为COUNT函数,3为COUNTA函数,4为MAX函数,5为MIN函数,6为PRODUCT函数,7为STDEV.S函数,8为STDEV.P函数,9为SUM函数,10为VAR.S函数,11为VAR.P函数,12为MEDIAN函数,13为MODE.SNGL函数,14为LARGE函数,15为SMALL函数,16为PERCENTILE.INC函数,17为QUARTILE.INC函数,18为PERCENTILE.EXC函数,19为QUARTILE.EXC函数。
  • options: 必需。一个数值,确定在函数的计算区域内要忽略的值。其中:0或者省略,忽略嵌套SUBTOTAL和AGGREGATE函数;1指定忽略隐藏行、嵌套SUBTOTAL和AGGREGATE函数;2指定忽略错误值、嵌套SUBTOTAL和AGGREGATE函数;3指定忽略隐藏行、错误值、嵌套SUBTOTAL和AGGREGATE函数;4指定忽略空值;5指定忽略隐藏行;6指定忽略错误值;7指定忽略隐藏行和错误值。
  • ref1:必需。函数的第一个数值参数。对于使用数组的函数,可以是一个数组或者数组公式,也可以是对要为其计算聚合值的单元格区域的引用。
  • ref2: 对于某些函数必需,例如LARGE(array,k)、SMALL(array,k)、PERCENTILE.INC(array,k)、QUARTILE.INC(array,quart)、PERCENTILE.EXC(array,k)、QUARTILE.EXC(array,quart)。


AGGREGATE函数陷阱

如果第二个引用参数是必需的但未提供,那么AGGREGATE将返回错误值#VALUE!。如果有一个或多个引用是三维引用,那么AGGREGATE将返回错误值#VALUE!。如果在Excel 2007或之前的版本打开AGGREGATE工作簿将返回#NAME?。

如果ref1,ref2,…中有其他AGGREGATE(或嵌套AGGREGATE),将忽略这些嵌套AGGREGATE,避免重复计算。如果AGGREGATE函数的引用中包含SUBTOTAL,那么将忽略这些SUBTOTAL。如果SUBTOTAL函数中包含AGGREGATE,那么将忽略这些AGGREGATE。

AGGREGATE函数适用于数据列或垂直区域,不适用于数据行或水平区域。

AGGREGATE函数仅适用于2010及其后的版本。参数function_num指定的函数代号中,1至13不能处理数组操作,14至19可以处理数组操作。例如,5代表MIN函数,但不能用于获取最小值的数组运算,可以使用15代表的SMALL函数来获取最小值。

示例1: 计算最大值、最小值、中值等

在下图2所示的工作表,数据单元格区域为A1:B11,其中单元格A1和A4中含有错误值。使用AGGREGATE函数来计算最大值、最小值、中值等。



图2

示例2: 计算满足多条件的数据最大值

如下图3所示的工作表,在单元格区域A1:C12中是不同超市的水果销售数据。现在想要知道,除中心超市外,榴莲和苹果在其他超市的最大销量。在单元格A16和A17中列出了水果名,在B16中的公式为:

=AGGREGATE(14,6,$C$2:$C$12/(($A$2:$A$12$E$2)*($B$2:$B$12=A16)),1)

向下复制到单元格B17。



图3

示例3: 提取满足多个条件的数据

如图4所示,在单元格区域A4:D14中是数据,在单元格区域B1:D2中设置了条件,要从A4:D14中提取满足B1:D2条件的数据并放置到单元格F5开始的区域中。本例中,要提取张三在2017年5月1日至2017年12月1日之间的销售数据。



图4

在单元格F5中的公式为:

[b]=IFERROR(INDEX(A$5:A$14,AGGREGATE(15,6,(ROW($A$5:$A$14)-ROW($A$5)+1)/(($A$5:$A$14>=$B$2)*($A$5:$A$14


    关注 完美Excel


微信扫一扫关注公众号

0 个评论

要回复文章请先登录注册