Excel2010新增函数中的多面手AGGREGATE

AGGREGATE是Excel 2010中新增的函数之一，它可以返回列表或数据库中的合计。 AGGREGATE不仅可以实现诸如SUM、AVERAGE、COUNT、LARGE等19个函数的功能，而且还可以忽略隐藏行、错误值、空值等。如果区域中包含错误值，SUM等函数将返回错误，这时用 AGGREGATE函数就非常方便了。其引用形式的语法为：
AGGREGATE(function_num, options, ref1, [ref2], …)
其中第一个参数“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
返回数据集中第k个最大值
15
SMALL
返回数据集中的第k个最小值
16
PERCENTILE.INC
返回区域中数值的第K(0≤k≤1)个百分点的值
17
QUARTILE.INC
返回数据集的四分位数（包含0和1）
18
PERCENTILE.EXC
返回区域中数值的第K（0
19
QUARTILE.EXC www.2361.net
返回数据集的四分位数(不包括0和1）
第二个参数“options”为为一个0至7之间的数字，指定要忽略的项目。在单元格中输入该函数的第二个参数时，Excel会自动给出该参数的选择列表，如图。



第三个参数“ref1”为区域引用或一个数组，第四个可选参数“[ref2]”为某些函数必需的第二个参数。
AGGREGATE函数与SUBTOTAL函数有点类似，当SUBTOTAL函数的第一个参数为“101-111”之间的数字时，就会忽略隐藏值，但SUBTOTAL函数不能忽略错误值。
例如在下图的A2:F12区域是由公式返回的动态数据区域，其中某些单元格可能会返回错误值，要对这些包含错误值的区域求和，在Excel 2010中使用AGGREGATE函数更为简洁。



如果在G10单元格中输入公式：
=SUM(B10:F10)
公式返回错误“#N/A”。而公式：
=AGGREGATE(9,6,B10:F10)
会忽略错误值而返回B10:F10区域的合计值。
值得注意的是，AGGREGATE函数只能忽略隐藏行，而不能忽略隐藏列。