[{"id":"b66f8b1e-cc14-4e89-9679-abd5687d283d","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"569bb90a-ea68-46c6-96f1-ab151c120714","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5eb52f08-2d1a-4362-9ffc-4871bdc10f3f","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"341dd607-b97d-4d70-bde2-53acda6b6c95","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"05349273-414f-4208-9ea2-c4fc8f4ea2cb","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"86089f76-b778-4d52-821e-6f27de3df613","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b81e4fd6-1fc5-43a0-a258-b6e16a5cbec6","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8aa8ce31-43e4-438e-951f-241608435260","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"37343f41-6ec2-4c7e-b21d-2cc18d5ce1e0","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5915e52f-64f8-4146-b8bd-81bead6324a3","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"836ba889-af9e-460d-a4cc-c24d922795f2","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4ae14b06-bb68-4394-a210-a46b8f028346","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d8f42066-e9dc-4411-bdcf-43b1a203370c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"7ef86f16-b1a0-49f7-9592-612b9be02b25","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b29c7775-a9a4-451e-a1b5-01d19ed5ca5e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"e56f3989-8f81-46af-90fa-a4813eeb976f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"2526c963-f170-45a8-923e-91b0712a9810","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"24575cf0-501a-44f9-8426-c40f8f4b5552","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1a6f8d7f-acd6-42be-8c4f-f464c6218381","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"82b176fd-5cab-498c-909e-8fa7d29c38d8","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1708b3a9-4f37-44a8-8f0e-f9a2d2e5d940","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"c9ac246e-29fb-4bc4-8231-8439795bb590","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"94f2a5c3-2539-436a-af75-23fbbd1a3957","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d99594f4-2d40-4df4-9419-ba2ca6aa3f7f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"fd66e72d-0f10-4f57-9807-6db26290ab2e","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8f344863-503d-4bc3-a594-3815e7d55f5c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"9be82601-de9d-4c18-948a-23ab6f4dd431","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"6495f3c0-b463-47e0-b08a-ca949672211e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b4bede08-3f08-4839-ba4a-abc7ac195bde","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4afcdfc0-3ff5-4f2b-a223-f8fc042a5bbe","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"422062c4-fa40-4771-a86f-008efe6d86e5","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d87e8013-86a7-4840-8d25-6f62e14eb4ac","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a6acedfd-4043-4c64-a5d1-aec3326df9e7","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"dbdc0b24-06c4-48b9-8d6c-7455119dc773","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a3856849-954a-4cfc-96a6-382e530d3638","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4a39306b-ffa5-433d-80a3-28e41f929b72","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]}]
该函数返回列表或数据库中的合计。 AGGREGATE 函数可将不同的聚合函数应用于列表或数据库,并提供忽略隐藏行和错误值的选项。
Aggregate 函数在下列情况下十分有用:
在使用AGGREGATE函数时,用户可以使用以下语法:
语法-1: AGGREGATE(function_num, options, ref1, [ref2], …) - 仅当“ Function_num”参数包含的值介于1到13之间时,此语法才适用。有关详细信息,请参阅下面的表。
语法-2: AGGREGATE(function_num, options, array, k) - 仅当“ Function_num”参数包含的值介于14到19之间时,此语法才适用。有关详细信息,请参阅下面的表。
此函数有下列参数:
参数 | 描述 | ||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Function_num |
[必需], 一个介于 1 到 19 之间的数字,指定要使用的函数。 > 注意: 当“ function_num”值介于1到13时,适用的语法是:AGGREGATE(function_num,options,ref1,[ref2],…),当“ function_num”的值介于14到19之间时,适用的语法为AGGREGATE(function_num,options,array,k)。 THIS_IS_A_WRONG_FORMAT_FOR_MARKDOWN |
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 |
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 | [必需], 一个数值,决定在函数的计算区域内要忽略哪些值。 THIS_IS_A_WRONG_FORMAT_FOR_MARKDOWN |
选项 | 行为 | 0 或省略 | 忽略嵌套 SUBTOTAL 和 AGGREGATE 函数 | 1 | 忽略隐藏行、嵌套 SUBTOTAL 和 AGGREGATE 函数 | 2 | 忽略错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数 | 3 | 忽略隐藏行、错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数 | 4 | 忽略空值 | 5 | 忽略隐藏行 | 6 | 忽略错误值 | 7 | 忽略隐藏行和错误值 | ||||||||||||||||||||||
选项 | 行为 | ||||||||||||||||||||||||||||||||||||||||
0 或省略 | 忽略嵌套 SUBTOTAL 和 AGGREGATE 函数 | ||||||||||||||||||||||||||||||||||||||||
1 | 忽略隐藏行、嵌套 SUBTOTAL 和 AGGREGATE 函数 | ||||||||||||||||||||||||||||||||||||||||
2 | 忽略错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数 | ||||||||||||||||||||||||||||||||||||||||
3 | 忽略隐藏行、错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数 | ||||||||||||||||||||||||||||||||||||||||
4 | 忽略空值 | ||||||||||||||||||||||||||||||||||||||||
5 | 忽略隐藏行 | ||||||||||||||||||||||||||||||||||||||||
6 | 忽略错误值 | ||||||||||||||||||||||||||||||||||||||||
7 | 忽略隐藏行和错误值 | ||||||||||||||||||||||||||||||||||||||||
Ref1 | [必需], 函数的第一个数值参数,这些函数具有要计算聚合值的多个数值参数。 | ||||||||||||||||||||||||||||||||||||||||
Ref2 | [可选], 要计算聚合值的 2 至 253 个数值参数。 | ||||||||||||||||||||||||||||||||||||||||
array | [仅当“ function_num”值介于14到19时必需], 指定数组,数组公式或对一系列单元格的引用。 | ||||||||||||||||||||||||||||||||||||||||
k | [必需], 当“ function_num”值介于14到19时,必须使用此参数来指定功能。 |
注释: 使用AGGREGATE函数时,请牢记以下几点:
接受数值列表或数据库。 返回聚合计算,例如AVERAGE,COUNT,MIN,MAX等,同时忽略隐藏的行和错误。
下图描述了AGGREGATE函数的四个不同示例:
假设您有一个表格,该表格的A列中包含一些值,并且汇总功能已应用于单元格B1,B2,B3和B4,如下面的B列所示。
公式输出结果显示在C列中,使用的公式的描述在D列中提及。
显然,单元格B1和B2中的AGGREGATE公式使用了多个单元格引用[上面说明的语法1],但是单元格B3和B4中的公式使用了函数的数组形式[上面说明的语法2]。