【数学和三角函数】AGGREGATE函数
AGGREGATE函数
返回列表或数据库中的合计。
AGGREGATE 函数可将不同的聚合函数应用于列表或数据库，并提供忽略隐藏行和错误值的选项。
语法
引用形式
AGGREGATE(function_num, options, ref1, [ref2], …)
数组形式
AGGREGATE(function_num, options, array, [k])
AGGREGATE 函数语法具有以下参数：
▪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 |
▪Options 必需。 一个数值，决定在函数的计算区域内要忽略哪些值。
注意: 如果数组参数中包含计算（例如，=AGGREGATE(14,3,A1:A100*(A1:A100>0),1)），则函数不会忽略隐藏行、嵌套分类汇总或嵌套聚合。
选项 | 行为 |
0 或省略 | 忽略嵌套 SUBTOTAL 和 AGGREGATE 函数 |
1 | 忽略隐藏行、嵌套 SUBTOTAL 和 AGGREGATE 函数 |
2 | 忽略错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数 |
3 | 忽略隐藏行、错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数 |
4 | 忽略空值 |
5 | 忽略隐藏行 |
6 | 忽略错误值 |
7 | 忽略隐藏行和错误值 |
▪Ref1 必需。函数的第一个数值参数，这些函数具有要计算聚合值的多个数值参数。
▪Ref2,... 可选。要计算聚合值的 2 至 253 个数值参数。
对于使用数组的函数，ref1 可以是一个数组或数组公式，也可以是对要为其计算聚合值的单元格区域的引用。 ref2 是某些函数必需的第二个参数。 以下函数需要 ref2 参数：
函数 |
LARGE(array,k) |
SMALL(array,k) |
PERCENTILE.INC(array,k) |
QUARTILE.INC(array,quart) |
PERCENTILE.EXC(array,k) |
QUARTILE.EXC(array,quart) |
备注
Function_num :
▪在将 AGGREGATE 函数输入到工作表上的单元格中时，只要键入 function_num 参数，就会立即看到可以作为参数使用的所有函数的列表。
错误：
▪如果第二个引用参数是必需的但未提供，AGGREGATE 将返回 #VALUE! 错误。
▪如果有一个或多个引用是三维引用，AGGREGATE 将返回 #VALUE! 错误值。
区域类型：
▪AGGREGATE 函数适用于数据列或垂直区域， 不适用于数据行或水平区域。 例如，当使用选项 1 对某个水平区域进行分类汇总时，如 AGGREGATE(1, 1, ref1)，则隐藏某一列并不会影响聚合总值。 但是，隐藏垂直区域中的某一行将对聚合总值产生影响。
示例
#DIV/0! | 82 |
72 | 65 |
30 | 95 |
#NUM! | 63 |
31 | 53 |
96 | 71 |
32 | 55 |
81 | 83 |
33 | 100 |
53 | 91 |
34 | 89 |
公式 | 说明 | 结果 |
=AGGREGATE(4, 6, A1:A11) | 计算最大值，同时忽略区域中的错误值 | 96 |
=AGGREGATE(14, 6, A1:A11, 3) | 计算第 3 个最大值，同时忽略区域中的错误值 | 72 |
=AGGREGATE(15, 6, A1:A11) | 将返回 错误 #NUM!。 因为函数 (SMALL) 要求具有一个第二引用参数，因而 AGGREGATE 需要第二引用参数。 | #VALUE! |
=AGGREGATE(12, 6, A1:A11, B1:B11) | 计算中值，同时忽略区域中的错误值 | 68 |
=MAX(A1:A2) | 将返回错误值，因为计算区域中存在错误值。 | #DIV/0! |
AGGREGATE函数是一个数学与三角函数,它可以返回列表或数据库中的合计。
简单的来讲AGGREGATE函数可将不同的聚合函数，应用于列表或数据库并提供忽略隐藏行和错误值的公式计算。
AGGREGATE函数语法为AGGREGATE(函数序号、选项、引用、引用2 ....)。
“函数序号”是一个介于 1 到 19 之间的指定函数序号。
对应的函数与序号如图所示“选项”是一个数值，决定在函数的计算区域内要忽略哪些值对应的选项与数值。
我们想使用AGGREGATE函数，计算A1:B10区域中的最大值，同时忽略区域中的错误值应该如何操作呢？
▪ 点击上方菜单栏公式-插入函数，输入AGGREGATE函数,此时弹出AGGREGATE函数对话框。
▪ 在函数序号中输入“4” ,意思是使用函数序号表第4个序号——MAX函数。
在选项中输入“6”，意思是选项列表第6个序号——忽略错误值。
在引用中输入区域A1:B10。
整体公式的意思为使用AGGREGATE函数计算A1:B10区域的最大值同时忽略区域中的错误值。
