excel表格中筛选汇总_在Excel中汇总过滤列表

最新推荐文章于 2022-04-22 10:48:43 发布

culiao2169

最新推荐文章于 2022-04-22 10:48:43 发布

阅读量1.2k

点赞数 1

文章标签：列表 python 大数据 java 机器学习

原文链接：https://contexturesblog.com/archives/2008/11/11/total-a-filtered-list-in-excel/

版权

excel表格中筛选汇总

In Excel, you might have a long list of orders with a grand total at the end. If you filter the Region column, so the list only shows one region's sales, you'd like the total to include only those items. Here's how to total a filtered list in Excel.

在Excel中，您可能有很长的订单列表，最后是总计。如果您对“地区”列进行过滤，那么该列表仅显示一个地区的销售额，您希望总计中仅包含这些项目。这是在Excel中汇总过滤列表的方法。

SUM函数问题 (SUM Function Problem)

If you used the SUM function in the grand total cell, the result won't change if a filter is applied. This list is filtered to show orders from the West Region.

如果在总计单元格中使用了SUM函数，则应用过滤器后结果不会改变。此列表经过过滤以显示来自西部地区的订单。

At a glance, you can see that the Grand Total is much higher than the records listed. There are only 3 orders visible, but the order count is calculated as 49.

一目了然，您可以看到“总计”远高于列出的记录。仅显示3个订单，但订单数计算为49。

Instead of SUM or COUNT, you can use the SUBTOTAL function, and only the filtered rows will be included in the grand total.

可以使用SUBTOTAL函数来代替SUM或COUNT，并且只将筛选出的行包括在总计中。

创建一个小计公式 (Create a SUBTOTAL formula)

A quick way to create a SUBTOTAL formula is to:

创建SUBTOTAL公式的快速方法是：

Apply a filter to the list. In this example, the Region column is filtered for "West".
将过滤器应用于列表。在此示例中，将“地区”列过滤为“西”。
Select the cell where you want the grand total.
选择要总计的单元格。
On Excel's Standard toolbar, click the AutoSum button, or on the keyboard, press the Alt key and tap the equal sign key (Alt + =).
在Excel的“标准”工具栏上，单击“自动求和”按钮，或者在键盘上，按Alt键并点击等号键(Alt + =)。

Because the list is filtered, a SUBTOTAL formula is inserted, instead of a SUM formula.

因为列表被过滤，所以将插入SUBTOTAL公式，而不是SUM公式。

读取小计公式 (Reading a SUBTOTAL formula)

Within the brackets for the SUBTOTAL function there are two arguments, separated by a comma (or a semi-colon, depending on your regional settings).

在SUBTOTAL函数的括号内，有两个参数，以逗号(或分号，取决于您的区域设置)分隔。

The first argument is a number that tells Excel which summary function to use in the subtotal. Most of the time you'll use a 9, which is the SUM function.

第一个参数是一个数字，告诉Excel在小计中使用哪个汇总函数。大多数情况下，您将使用9，即SUM函数。

The second argument is the range of numbers that should be subtotaled. In this example, cells H2:H50 are the cells that we want to sum.

第二个参数是应合计的数字范围。在此示例中，单元格H2：H50是我们要求和的单元格。

更改小计公式 (Changing a SUBTOTAL formula)

In some cases, you might want a different function in the SUBTOTAL function. For example, it wouldn't make sense to sum the Unit Cost column, but it might be useful to know the average unit cost.

在某些情况下，您可能希望在SUBTOTAL函数中使用其他函数。例如，对“单位成本”列求和是没有意义的，但是了解平均单位成本可能会很有用。

Unfortunately, when the list is filtered, only the SUM function on the AutoSum button inserts a SUBTOTAL function. If you click Average, you'll get an AVERAGE formula. (Note: This is improved in Excel 2007, and the other functions on the AutoSum button also insert a SUBTOTAL function.)

不幸的是，当列表被过滤后，只有“自动求和”按钮上的SUM函数才插入SUBTOTAL函数。如果单击“平均”，您将获得一个“平均”公式。 (注意：这在Excel 2007中得到了改进，“自动求和”按钮上的其他功能也插入了SUBTOTAL函数。)

So, click Sum to create a SUBTOTAL function, then change the function number from 9 to 1, which will calculate an AVERAGE. Or, change the function number to 2, and you'll calculate a COUNT of the numbers in a range.

因此，单击求和以创建SUBTOTAL函数，然后将函数号从9更改为1，这将计算出AVERAGE。或者，将函数编号更改为2，您将计算一个范围内的COUNT个数字。

There are 11 functions that you can use as the first argument in the SUBTOTAL function. The list is alphabetical, so that might help you remember some of the numbers, without going to Excel's Help every time.

您可以将11个函数用作SUBTOTAL函数中的第一个参数。该列表是按字母顺序排列的，因此可以帮助您记住一些数字，而不必每次都转到Excel的帮助。