Excel如何运用Aggregate函数忽略出错值空值求与求平均值及其数组形式的用法
Aggregate函数是 Excel 2010 新增的函数之一，它用于忽略错误值、空值、隐藏行列、SubTotal函数分类汇总求和、求平均值、统计单元格个数、查找最大值、最小值等。Aggregate函数分为引用形式和数组形式，它们支持 19 个函数；引用区域至少有一个，最多只能有 253 个；数组形式只支持 6 个函数，并且不能少 k 参数。
Aggregate函数的主要作用就是忽略错误值、空值、隐藏行列，如果用相应的函数求和、求平均值等不能达到此目的，可以使用Aggregate函数，这样不但便于写公式并且使问题变得简单。
一、Excel Aggregate函数语法
（一）引用形式
表达式：AGGREGATE(Function_Num, Options, Ref1, [Ref2], …)
中文表达式：Aggregate(函数序号, 忽略选项, 引用区域1, [引用区域2], …)
（二）数组形式
表达式：AGGREGATE(Function_Num, Options, Array, [k])
中文表达式：Aggregate(函数序号, 忽略选项, 数组, [第几个])
（三）说明：
1、Function_Num 为函数序号，它的取值范围为 1-19，每个数字代表一个函数，具体如下：
Function_num 函数
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
2、Options 为忽略选项，共有 0 - 7 八个取值，具体如下：
0 或省略：忽略嵌套的 Subtotal 函数和 Aggregate 函数；
1：忽略隐藏行、嵌套的 Subtotal 函数和 Aggregate 函数；
2：忽略错误值、嵌套的 Subtotal 函数和 Aggregate 函数；
3：忽略隐藏行、错误值、嵌套的 Subtotal 函数和 Aggregate 函数；
4：忽略空值；
5：忽略隐藏行；
6：忽略错误值；
7：忽略隐藏行和错误值。
3、Ref 至少有一个，最多只能有 253 个；如果引用了三维单元格，将返回值错误 #VALUE!。
4、如果使用Aggregate函数的数组形式，一些函数不能省略参数 K，这些函数有 Large(Array, k)、Small(Array, k)、Percentile.inc(array, k)、Quartile.inc(Array, Quart)、Percentile.exc(Array, k)、Quartile.exc(Array, Quart)，如果省略了参数 K，将返回值错误#VALUE!。
5、用Aggregate函数进行分类汇总时，隐藏的列仍然会被计入汇总，而隐藏的行不被计汇总。
二、Excel Aggregate函数的使用方法及实例
（一）忽略嵌套的 Subtotal 函数实例
1、双击 E9 单元格，把公式 =SUBTOTAL(9,E2:E8) 复制到 E9，按回车，返回求和结果 5028；再次双击 E9 单元格，把公式 =AGGREGATE(9,0,E2:E9) 复制到 E10，按回车，返回求和结果 5028；操作过程步骤，如图1所示：
图1
2、公式 =SUBTOTAL(9,E2:E8) 是对 E2:E8 求和，公式 =AGGREGATE(9,0,E2:E9) 是对 E2:E9 求和，两公式返回结果一样，说明当Aggregate函数把 Options 参数设置为 0 时忽略 Subtotal 函数的求和结果。
（二）忽略空值的实例
1、双击 E9 单元格，把公式 =AGGREGATE(9,4,E2:E8) 复制到 E9，按回车，返回求和结果 5028；选中 E6，按 Delete 键，把数值删除，E9 中的值变为 4266；操作过程步骤，如图2所示：
图2
2、当把 E6 中的数值删除后，求和结果变小，说明把参数 Options 设置为 4 时，Aggregate函数会忽略空值。
（三）忽略隐藏行但包含隐藏列实例
1、双击 E9 单元格，把公式 =AGGREGATE(2,5,D2:E8) 复制到 E9，按回车，返回统计结果 14；右键第二行行号 2，在弹出的菜单中选择“隐藏”，则第二行被隐藏，E9 中的统计结果随之变为 12；右键 D 列列号 D，在弹出的菜单选择“隐藏”，把 D 列隐藏，E9 中的值仍是12；操作过程步骤，如图3所示：
图3
2、公式 =AGGREGATE(2,5,D2:E8) 中 2 代表统计数值单元格数目函数 Count，5 表示忽略空值；当隐藏第二行后，统计结果减少两个，说明把参数 Options 设置为 5，Aggrerate函数会忽略隐藏行；当隐藏列后，统计结果不变，说明Aggrerate函数包含隐藏列。
（四）忽略错误值实例
1、假如要对营业额求和。双击 G11 单元格，把公式 =AGGREGATE(9,6,G2:G10) 复制到 G11，按回车，返回求和结果 2682664.1；双击G12，输入公式 =SUMD(G2:G10)，按回车，返回值错误 #VALUE!，再次双击 G12，把 G2 改为 G3，按回车，同样返回求和结果 2682664.1；操作过程步骤，如图4所示：
图4
2、公式 =AGGREGATE(9,6,G2:G10) 中 6 表示忽略错误值，求和区域为 G2:G10，其中 G2 为错误，但被忽略；改用 Sum函数，则返回值错误 #VALUE!，只有不包含错误值才能返回正确结果。
提示：其它函数（如求平均值函数 Average、统计函数 Count、CountA、乘除法函数 Product、最大值函数 Max、最小值函数Min 等）也支持忽略错误值计算。
（五）需要参数 K 的函数省略后返回错误实例
1、假如要返回第三个销量最小值。双击 E9 单元格，把公式 =AGGREGATE(15,3,E2:E8,3) 复制到 E9，按回车，返回 638，它正好是第三个销量最小值；再次双击 E9 单元格，把“,3”去掉，按回车，返回值错误 #VALUE!；操作过程步骤，如图5所示：
图5
2、公式 =AGGREGATE(15,3,E2:E8,3) 中 15 表示 Small函数，参数 Options（3）表示“忽略隐藏行、错误值、嵌套的 Subtotal 函数和Aggregate 函数”，参数 k（3）表示返回第三个最小值；当把 k（3）删除后，返回 #VALUE!，说明 Small函数不能省略 k。
三、Excel 用Aggregate函数数组形式求平均值
1、假如有营业额和价格，要求根据它们求平均销量。双击 E9 单元格，把公式 =AGGREGATE(1,6,E2:E8/D2:D8) 复制到 E9，按回车，返回值错误 #VALUE!；再次双击 E9 单元格，把公式改为 =AGGREGATE(14,6,E2:E8/D2:D8,2)，按回车，返回服装销量第二个最大值 897；双击 E10，把公式 =AVERAGE(IFERROR(E2:E8/D2:D8,FALSE)) 复制到 E10，按 Ctrl + Shift + 回车，返回服装平均销量 721.60；再次双击 E10，把公式中 FALSE 改为 0，按 Ctrl + Shift + 回车，返回服装平均销量 515.43；操作过程步骤，如图6所示：
图6
2、公式说明：
A、公式 =AGGREGATE(1,6,E2:E8/D2:D8) 中 1 表示求平均值，6 表示忽略错误值，E2:E8/D2:D8 表示用 E2:E8 中每个元素除以 D2:D8 中对应的元素并以数组形式返回结果；公式的本意为：把 E2:E8/D2:D8 返回的数组求平均值，但Aggregate函数不支持Average函数的数组形式，因此返回值错误 #VALUE!。
B、公式 =AGGREGATE(14,6,E2:E8/D2:D8,2) 中 14 表示求最大值函数 Large，最后一个参数 2 表示返回第二个最大值；由于Aggregate函数支持Large函数的数组形式，因此能返回正确值。由此可知，要用数组形式，必须用支持数组形式的函数，即函数序号为 14-19 的函数。
C、公式 =AVERAGE(IFERROR(E2:E8/D2:D8,FALSE)) 中 IFERROR(E2:E8/D2:D8,FALSE) 用于判断 E2:E8 中每个元素除以 D2:D8 对应的元素，如果出错，返回 FALSE，否则返回相除的结果；例如第一次用 E2/D2，返回结果 329，第二次 E3/D3 发生错误，返回 FALSE，其它的以此类推，最后返回数组 {329;FALSE;638;FALSE;762;982;897}；则公式变为 =AVERAGE({329;FALSE;638;FALSE;762;982;897})，最后求平均值，返回 721.60，FALSE 不参与求平均值，即不包含 E2:E8 和 D2:D8 中为文本的单元格。
D、公式 =AVERAGE(IFERROR(E2:E8/D2:D8,0)) 把 FALSE 改为 0 后，区别在于 IFERROR(E2:E8/D2:D8,0) 返回的数组中 FALSE 变为 0，即 {329;0;638;0;762;982;897}，则公式变为 =AVERAGE({329;0;638;0;762;982;897})，返回求平均值结果 515.43，0 参与求平均值，即包含E2:E8 和 D2:D8 中为文本的单元格。
……