我最近写了一篇关于如何在Excel中使用汇总函数轻松汇总大量数据的文章，但那篇文章考虑到了工作表上的所有数据。如果您只想查看数据子集并汇总数据子集怎么办？
在Excel 中(Excel)，您可以在列上创建过滤器，以隐藏与您的过滤器不匹配的行。此外，您还可以使用Excel中的特殊功能，仅使用过滤后的数据汇总数据。
在本文中，我将引导您完成在Excel中创建过滤器的步骤，以及使用内置函数来汇总过滤后的数据。
在 Excel 中创建简单的过滤器
在Excel 中(Excel)，您可以创建简单过滤器和复杂过滤器。让我们从简单的过滤器开始。使用过滤器时，您应该始终在顶部有一行用于标签。拥有这一行不是必需的，但它使使用过滤器更容易一些。
上面，我有一些假数据，我想在City列上创建一个过滤器。在Excel 中(Excel)，这真的很容易做到。继续并单击功能区中的“数据(Data)”选项卡，然后单击“过滤器(Filter)”按钮。您也不必选择工作表上的数据或单击(sheet or click)第一行。
当您单击Filter时，第一行中的每一列将自动在最右侧添加一个小的下拉按钮。
现在继续并单击City 列(City column)中的下拉箭头。你会看到几个不同的选项，我将在下面解释。
在顶部，您可以按City 列(City column)中的值快速对所有行进行排序。请注意，当您对数据进行排序时，它将移动整行，而不仅仅是City 列(City column)中的值。这将确保您的数据保持原样。
此外，最好在最前面添加一个名为ID 的列，并将(ID and number)其从 1 编号到工作表中的行数。这样，您始终可以按ID 列(ID column)排序，并以与原来相同的顺序恢复数据，如果这对您很重要的话。
如您所见，电子表格中的所有数据现在都根据City 列(City column)中的值进行排序。到目前为止，没有隐藏任何行。现在让我们看一下过滤器对话框(filter dialog)底部的复选框。在我的示例中，我在City 列(City column)中只有三个唯一值，而这三个显示在列表中。
我继续并取消选中两个城市并留下一个选中。现在我只显示了 8 行数据，其余的都被隐藏了。如果您检查最左侧的行号，您可以很容易地知道您正在查看过滤的数据。根据隐藏的行数，您会看到一些额外的水平线，并且数字的颜色将为蓝色。
现在假设我想过滤第二列以进一步减少结果数量。在 C 列中，我有每个家庭的成员总数，我只想查看有两个以上成员的家庭的结果。
继续并单击C 列(Column C)中的下拉箭头，您将看到该列中每个唯一值的相同复选框。但是，在这种情况下，我们要单击数字过滤器(Number Filters)，然后单击大于( Greater Than)。如您所见，还有许多其他选项。
将弹出一个新对话框，您可以在此处输入过滤器的值。您还可以使用AND 或 OR 函数(AND or OR function)添加多个条件。例如，您可以说您想要值大于 2 且不等于 5 的行。
现在我只剩下 5 行数据：仅来自新奥尔良(New Orleans)且有 3 个或更多成员的家庭。够容易吗？(Easy)请注意，您可以通过单击下拉列表，然后单击“从“列名称”中清除过滤器(Clear Filter From “Column Name”)链接轻松清除列上的过滤器。
以上就是Excel中的简单过滤器。它们非常易于使用，结果非常简单。现在让我们看看使用“高级(Advanced)过滤器”对话框的复杂过滤器。
在 Excel 中创建高级筛选器
如果要创建更高级的过滤器，则必须使用“高级(Advanced) 过滤器”对话框(filter dialog)。例如，假设我想查看居住在新奥尔良(New Orleans)且家庭成员超过 2 人的所有家庭，或(OR)克拉克斯维尔(Clarksville)的所有家庭成员超过 3 人的家庭，并且(AND)只查看以.EDU结尾的电子邮件地址的家庭。现在你不能用一个简单的过滤器来做到这一点。
为此，我们需要稍微不同地设置Excel 工作表。(Excel sheet)继续在您的数据集上方插入几行，并将标题标签准确复制到第一行，如下所示。
现在这里是高级过滤器的工作原理。您必须首先在顶部的列中输入您的条件，然后单击“数据(Data)”选项卡上“排序和过滤”下的“( Sort & Filter)高级(Advanced)”按钮。
那么我们究竟可以在这些单元格中输入什么？好的，让我们从我们的示例开始。我们只想查看来自New Orleans 或 Clarksville(New Orleans or Clarksville)的数据，所以让我们将它们输入到单元格E2 和 E3(E2 and E3)中。
当您在不同的行上键入值时，这意味着 OR。现在我们希望拥有超过 2 名成员的新奥尔良(New Orleans)家庭和拥有超过 3 名成员的克拉克斯维尔家庭。(Clarksville)为此，请在 C2中输入 >2 >3。
由于 >2 和New Orleans在同一行，它将是一个AND 运算符(AND operator)。上面的第 3 行(row 3)也是如此。最后，我们只需要以 .EDU 结尾的电子邮件地址的家庭。为此，只需在 D2 和 D3 中输入* .edu(D2 and D3)*.edu。* 符号表示任意数量的字符。
完成此操作后，单击数据集中的任意位置，然后单击“高级(Advanced)”按钮。列表(List Rang)范围字段将自动找出您的数据集，因为您在单击高级按钮(Advanced button)之前单击它。现在单击Criteria range(Criteria range)按钮右侧的小按钮。
选择(Select everything)从 A1 到 E3 的所有内容，然后再次单击相同的按钮返回“高级过滤器”对话框(Advanced Filter dialog)。单击确定(Click OK)，您的数据现在应该被过滤了！
如您所见，现在我只有 3 个符合所有这些条件的结果。请注意，标准范围的标签必须与数据集的标签完全匹配才能正常工作。
您显然可以使用此方法创建更复杂的查询，因此请尝试使用它以获得您想要的结果。最后，让我们谈谈将求和函数应用于过滤后的数据。
汇总过滤数据
现在假设我想总结过滤数据中的家庭成员数量，我该怎么做呢？好吧，让我们通过单击功能区中的清除(Clear)按钮来清除我们的过滤器。不用担心，只需单击“高级”按钮(Advanced button)并再次单击“确定”即可再次应用高级过滤器非常容易。
在我们数据集的底部，让我们添加一个名为Total的单元格，然后添加一个 sum 函数来总结家庭成员的总数。在我的示例中，我只是输入了=SUM(C7:C31)。
因此，如果我查看所有家庭，我总共有 78 个成员。现在让我们继续并重新应用我们的高级过滤器(Advanced filter)，看看会发生什么。
哎呀！而不是显示正确的数字，11，我仍然看到总数是 78！这是为什么？好吧，SUM 函数(SUM function)不会忽略隐藏行，所以它仍然使用所有行进行计算。幸运的是，您可以使用几个函数来忽略隐藏的行。
第一个是小计(SUBTOTAL)。在我们使用这些特殊功能之前，您需要清除过滤器，然后输入该功能。
清除过滤器后，继续输入=SUBTOTAL(，您应该会看到一个带有一堆选项的下拉框。使用此功能，您首先使用数字选择要使用的求和函数的类型。(summation function)
在我们的示例中，我想使用SUM，因此我将输入数字 9(number 9)或从下拉列表中单击它。然后键入逗号并选择单元格范围。
当您按下回车键时，您应该会看到 78 的值与之前相同。但是，如果您现在再次应用过滤器，我们将看到 11！
优秀！这正是我们想要的。现在您可以调整过滤器，该值将始终仅反映当前显示的行。
与SUBTOTAL 函数(SUBTOTAL function)几乎完全相同的第二个函数是AGGREGATE。唯一的区别是AGGREGATE 函数(AGGREGATE function)中有另一个参数，您必须在其中指定要忽略隐藏行。
第一个参数是您要使用的求和函数，与(summation function)SUBTOTAL一样，9 代表求和函数(SUM function)。第二个选项是您必须输入 5 以忽略隐藏行。最后一个参数是相同的，是单元格的范围。
您还可以阅读我关于摘要函数的文章，以更详细地了解如何使用AGGREGATE 函数(AGGREGATE function) 和其他函数，如MODE、MEDIAN、AVERAGE等。
希望本文为您在Excel中创建和使用过滤器提供了一个良好的起点(starting point)。如果您有任何问题，请随时发表评论。享受！
How to Filter Data in Excel
I recently wrote an article on how to use summary functions in Excel to easily summarize large amounts of data, but that article took into account all data on the worksheet. What if you only want to look at a subset of data and summarize the subset of data?
In Excel, you can create filters on columns that will hide rows that do not match your filter. In addition, you can also use special functions in Excel to summarize data using only the filtered data.
In this article, I’ll walk you through the steps for creating filters in Excel and also using built-in functions to summarize that filtered data.
Create Simple Filters in Excel
In Excel, you can create simple filters and complex filters. Let’s start off with simple filters. When working with filters, you should always have one row at the top that is used for labels. It’s not a requirement to have this row, but it makes working with filters a bit easier.
Above, I have some fake data and I want to create a filter on the City column. In Excel, this is really easy to do. Go ahead and click on the Data tab in the ribbon and then click on the Filter button. You don’t have to select the data on the sheet or click in the first row either.
When you click on Filter, each column in the first row will automatically have a small dropdown button added at the very right.
Now go ahead and click on the dropdown arrow in the City column. You’ll see a couple of different options, which I will explain below.
At the top, you can quickly sort all the rows by the values in the City column. Note that when you sort the data, it will move the entire row, not just the values in the City column. This will ensure that your data remains intact just as it was before.
Also, it’s a good idea to add a column at the very front called ID and number it from one to however many rows you have in your worksheet. This way, you can always sort by the ID column and get your data back in the same order it was originally, if that is important to you.
As you can see, all the data in the spreadsheet is now sorted based on the values in the City column. So far, no rows are hidden. Now let’s take a look at the checkboxes at the bottom of filter dialog. In my example, I have only three unique values in the City column and those three show up in the list.
I went ahead and unchecked two cities and left one checked. Now I only have 8 rows of data showing and the rest are hidden. You can easily tell you are looking at filtered data if you check the row numbers at the far left. Depending on how many rows are hidden, you’ll see a few extra horizontal lines and the color of the numbers will be blue.
Now let’s say I want to filter on a second column to further reduce the number of results. In column C, I have the total number of members in each family and I want to only see the results for families with more than two members.
Go ahead and click on the dropdown arrow in Column C and you’ll see the same checkboxes for each unique value in the column. However, in this case, we want to click on Number Filters and then click on Greater Than. As you can see, there are a bunch of other options too.
A new dialog will pop up and here you can type in the value for the filter. You can also add more than one criteria with an AND or OR function. You could say you want rows where the value is greater than 2 and not equal to 5, for example.
Now I’m down to just 5 rows of data: families only from New Orleans and with 3 or more members. Easy enough? Note that you can easily clear a filter on a column by clicking on the dropdown and then clicking the Clear Filter From “Column Name” link.
So that’s about it for simple filters in Excel. They are very easy to use and the results are pretty straight-forward. Now let’s take a look at complex filters using the Advanced filters dialog.
Create Advanced Filters in Excel
If you want to create more advanced filters, you have to use the Advanced filter dialog. For example, let’s say I wanted to see all families that live in New Orleans with more than 2 members in their family OR all families in Clarksville with more than 3 members in their family AND only the ones with a .EDU ending email address. Now you can’t do that with a simple filter.
To do this, we need to setup the Excel sheet a little differently. Go ahead and insert a couple of rows above your set of data and copy the heading labels exactly into the first row like shown below.
Now here is how advanced filters work. You have to first type your criteria into the columns at the top and then click the Advanced button under Sort & Filter on the Data tab.
So what exactly can we type into those cells? OK, so let’s start with our example. We only want to see data from New Orleans or Clarksville, so let’s type those into cells E2 and E3.
When you type values on different rows, it means OR. Now we want New Orleans families with more than two members and Clarksville families with more than 3 members. To do this, type in >2 in C2 and >3 in C3.
Since >2 and New Orleans are on the same row, it will be an AND operator. The same is true for row 3 above. Finally, we want only the families with .EDU ending email address. To do this, just type in *.edu into both D2 and D3. The * symbol means any number of characters.
Once you do that, click anywhere in your dataset and then click on the Advanced button. The List Range field will automatically figure out your dataset since you clicked into it before clicking the Advanced button. Now click on the small little button at the right of the Criteria range button.
Select everything from A1 to E3 and then click on the same button again to get back to the Advanced Filter dialog. Click OK and your data should now be filtered!
As you can see, now I have only 3 results that match all of those criteria. Note that the labels for the criteria range have to match exactly with the labels for the dataset in order for this to work.
You can obviously create a lot more complicated queries using this method, so play around with it to get your desired results. Finally, let’s talk about applying summation functions to filtered data.
Summarizing Filtered Data
Now let’s say I want to sum up the number of family members on my filtered data, how would I got about doing that? Well, let’s clear our filter by clicking on the Clear button in the ribbon. Don’t worry, it’s very easy to apply the advanced filter again by simply clicking on the Advanced button and clicking OK again.
At the bottom of our dataset, let’s add a cell called Total and then add a sum function to sum up the total family members. In my example, I just typed =SUM(C7:C31).
So if I look at all families, I have 78 members total. Now let’s go ahead and reapply our Advanced filter and see what happens.
Whoops! Instead of showing the correct number, 11, I still see the total is 78! Why is that? Well, the SUM function does not ignore hidden rows, so it is still doing the calculation using all the rows. Luckily, there are a couple of functions you can use to ignore hidden rows.
The first is SUBTOTAL. Before we use any of these special functions, you will want to clear your filter and then type in the function.
Once the filter is cleared, go ahead and type in =SUBTOTAL( and you should see a dropdown box appear with a bunch of options. Using this function, you first pick the type of summation function you want to use using a number.
In our example, I want to use SUM, so I would type in the number 9 or just click on it from the dropdown. Then type a comma and select the range of cells.
When you press enter, you should see the value of 78 is the same as previously. However, if you now apply the filter again, we will see 11!
Excellent! That’s exactly what we want. Now you can adjust your filters and the value will always reflect only the rows that are currently showing.
The second function that works pretty much exactly the same as the SUBTOTAL function is AGGREGATE. The only difference is that there is another parameter in the AGGREGATE function where you have to specify that you want to ignore hidden rows.
The first parameter is the summation function you want to use and as with SUBTOTAL, 9 represents the SUM function. The second option is where you have to type in 5 to ignore hidden rows. The last parameter is the same and is the range of cells.
You can also read my article on summary functions to learn how to use the AGGREGATE function and other functions like MODE, MEDIAN, AVERAGE, etc. in more detail.
Hopefully, this article gives you a good starting point for creating and using filters in Excel. If you have any questions, feel free to post a comment. Enjoy!
