Excel数学和三角函数:AGGREGATE
AGGREGATE 函数
AGGREGATE 函数消除了条件格式的限制。如果区域中存在错误,则数据条、图标集和色阶将无法显示条件格式。这是因为当计算区域中存在错误时,MIN、MAX 和 PERCENTILE 函数不进行计算。出于相同的原因,LARGE、SMALL 和 STDEVP 函数也会影响某些条件格式规则的相应功能。通过使用 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 获取最大值,可以用k控制获取第几大的值。 15 SMALL 获取最小值,可以用k控制获取第几小的值。 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,... 可选。要为其计算聚合值的 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!。
嵌套聚合:
如果 ref1, ref2,… 中有其他 AGGREGATE (或嵌套 AGGREGATE),将忽略这些嵌套 AGGREGATE,避免重复计算。
如果 AGGREGATE 函数的引用中包含 SUBTOTAL,将忽略这些 SUBTOTAL。
如果 SUBTOTAL 函数中包含 AGGREGATE,将忽略这些 AGGREGATE。
区域类型:
AGGREGATE 函数适用于数据列或垂直区域,不适用于数据行或水平区域。例如:当使用选项 1 对某个水平区域进行分类汇总时,例如 AGGREGATE(1, 1, ref1),则隐藏某一列并不会影响聚合总值。但是,隐藏垂直区域中的某一行就会对聚合总值产生影响。
示例
示例 1
如果将示例复制到一个空白工作表中,可能会更容易理解该示例。
选择本文中的示例。
要点 不要选择行或列标题。
从“帮助”中选择示例
按 Ctrl+C。
在 Excel 中,创建一个空白工作簿或工作表。
在工作表中,选择单元格 A1,然后按 Ctrl+V。
要点 若要使该示例能够正常工作,必须将其粘贴到工作表的单元格 A1 中。
要在查看结果和查看返回结果的公式之间进行切换,请按 Ctrl+`(重音符),或在“公式”选项卡上的“公式审核”组中,单击“显示公式”按钮。
将示例复制到一个空白工作表中后,可以按照您的需要改编示例。
示例 2
A | B |
---|---|
#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) | 将返回 #VALUE! 错误。因为函数 (SMALL) 要求具有一个第二引用参数,因而 AGGREGATE 需要第二引用参数。 |
=AGGREGATE(12, 6, A1:A11, B1:B11) | 计算中值,同时忽略区域中的错误值 (68) |
=MAX(A1:A2) | 将返回错误值,因为计算区域中存在错误值 (#DIV/0!) |
如果将示例复制到一个空白工作表中,可能会更容易理解该示例。
选择本文中的示例。
要点 不要选择行或列标题。
按 Ctrl+C。
在 Excel 中,创建一个空白工作簿或工作表。
在工作表中,选择单元格 A1,然后按 Ctrl+V。
要点 若要使该示例能够正常工作,必须将其粘贴到工作表的单元格 A1 中。
要在查看结果和查看返回结果的公式之间进行切换,请按 Ctrl+`(重音符),或在“公式”选项卡上的“公式审核”组中,单击“显示公式”按钮。
将示例复制到一个空白工作表中后,可以按照您的需要改编示例。
A | B |
---|---|
x | y |
96 | 82 |
72 | 65 |
30 | 95 |
56 | 63 |
31 | 53 |
98 | 71 |
32 | 55 |
81 | 83 |
33 | 100 |
53 | 91 |
34 | 89 |
公式 | 说明(结果) |
=AGGREGATE(4, 5, A1:A11) | 计算最大值,同时忽略区域中的错误值 (98) |
=AGGREGATE(14, 5, A1:A11, 3) | 计算第 3 个最大值,同时忽略区域中的错误值 (81) |
=AGGREGATE(18, 5, A1:A11) | 将返回 #VALUE! 错误。因为函数 (PERCENTILE) 要求具有一个第二引用参数,因而 AGGREGATE 需要第二引用参数。 |
=AGGREGATE(12, 5, A1:A11, B1:B11) | 计算中值,同时忽略区域中的错误值 (68) |