aggregate 求和
The SUBTOTAL function is great for calculating totals on a filtered list in Excel. Unlike the SUM function, SUBTOTAL ignores the values in rows hidden by the filter, and can even ignore manually hidden rows, so the total includes only the visible cells.
SUBTOTAL函数非常适合在Excel中的过滤列表上计算总计。 与SUM函数不同,SUBTOTAL会忽略过滤器隐藏的行中的值,甚至可以忽略手动隐藏的行,因此总数仅包括可见的单元格。
A new function, AGGREGATE, introduced in Excel 2010, is similar to SUBTOTAL, and has a couple of advantages.
Excel 2010中引入的新功能AGGREGATE与SUBTOTAL相似,并具有许多优点。
AGGREGATE中的更多功能 (More Functions in AGGREGATE)
The first advantage is that AGGREGATE has 19 functions, compared to SUBTOTAL's 11 functions.
第一个优点是,与SUBTOTAL的11个功能相比,AGGREGATE具有19个功能。
使用AGGREGATE忽略错误 (Ignore Errors with AGGREGATE)
Another advantage is that AGGREGATE can ignore errors, as well as hidden rows. You can choose from a list of options, for what to ignore.
另一个优点是,AGGREGATE可以忽略错误以及隐藏的行。 您可以从选项列表中选择要忽略的内容。
观看视频 (Watch the Video)
Watch this short video to see the steps for setting up an AGGREGATE total, and see how it differs from SUM and SUBTOTAL
观看此简短视频,以了解设置总计总额的步骤,并了解其与SUM和SUBTOTAL的区别
下载样本文件 (Download the Sample File)
To download the sample file, please visit the Excel Sum Functions Examples page on my Contextures website. ___________
要下载示例文件,请访问我的Contextures网站上的“ Excel Sum函数示例”页面 。 ___________
翻译自: https://contexturesblog.com/archives/2013/05/21/sum-a-filtered-list-with-aggregate-function/
aggregate 求和