当您处理大型数据集时，了解如何在Google 表格(Google Sheets)中进行过滤会很有用。
有两种方法可以做到这一点。您可以使用Google 表格菜单(Google Sheets menu)中的过滤视图，它允许您自定义过滤表格中可以重复使用的数据的特定方法。在Google 表格(Google Sheets)中过滤数据的一种更动态的方法是使用FILTER函数。
在本文中，您将学习如何使用这两种方法。
在Google表格中创建过滤视图(Filter View)
在此方法中，您将学习如何应用过滤器，该过滤器将只显示您想要查看的大型数据集中的数据。此过滤器视图将隐藏所有其他数据。您还可以组合过滤器参数以获得更高级的过滤器视图。
如何创建过滤视图(How to Create a Filter View)
例如，假设您有一组数据，其中包括客户购买的产品。数据包括姓名、地址、电子邮件、电话号码等。
对于此示例，假设您只想查看来自加利福尼亚州Playa Vista的客户以及拥有“.gov”电子邮件地址的客户。
1. 要创建此过滤器，请在菜单中选择创建过滤器(Create a Filter)图标。这个图标看起来像一个漏斗。
2. 您会在每个列标题的右侧看到小的过滤器图标。选择“客户地址(Customer Address)”字段顶部的此漏斗图标以自定义此字段的过滤器。
3. 这将打开一个窗口，您可以在其中自定义过滤器选项。选择Filter by values左侧的箭头。选择清除(Clear)以取消选择该字段中的所有条目。
注意(Note)：这是一个重要的步骤，因为它将视图从显示所有记录重置为不显示。这使Excel准备好应用您将在后续步骤中创建的过滤器。
4. 在下面的字段中键入您要过滤字段的文本。在此示例中，我们将使用“Playa Vista”并选择搜索图标以仅查看包含该文本的那些记录。选择(Select)结果列表中显示的所有记录。这会自定义您的过滤器，以便只有您选择的项目会显示在电子表格中。
4. 选择OK按钮后，您将看到工作表中的数据已过滤，因此仅显示来自Playa Vista的客户。
5. 要过滤第二个字段，请选择该字段顶部的过滤器图标。重复上述过程以创建过滤器。清除所有条目，键入“gov”文本以过滤掉任何不包含“gov”的电子邮件地址，选择这些条目，然后选择OK。
现在您已经自定义了过滤器，以便只显示您关心的数据集中的记录。这样您就不必每次打开电子表格时都重复此过程，是时候保存过滤器了。
保存和查看过滤器视图(Saving and Viewing Filter Views)
设置完过滤器后，您可以将其保存为过滤器视图，您可以随时启用。
要保存过滤器视图，只需选择过滤器图标旁边的下拉箭头，然后选择另存为过滤器视图(Save as filter view)。
您会看到电子表格顶部打开了一个深灰色字段。这将向您显示过滤器适用的选定范围和字段名称。只需选择(Just)名称(Name)旁边的字段，然后输入您要应用于该过滤器的名称。
只需输入名称并按Enter 即可(Enter)。
您会注意到在灰色条的右侧有一个齿轮图标。选择此图标可查看过滤器选项。
可用选项包括：
- 重命名过滤器
- 更新过滤器适用的范围
- 复制过滤器以更新它而不影响原始过滤器
- 删除过滤器
您可以随时关闭已启用的过滤器，只需再次选择过滤器图标即可。
请注意，当启用任何过滤器时，过滤器图标将变为绿色。当您禁用过滤器时，此图标将变回黑色。这是查看整个数据集或任何过滤器已从当前视图中删除数据的快速方法。
稍后，如果您想重新启用您创建的任何过滤器，只需选择过滤器图标旁边的下拉箭头。您会看到已保存的过滤器出现在菜单中。只需(Just)选择该过滤器即可随时启用它。
这将使用您配置的过滤器设置再次过滤视图。
使用过滤器功能
在Google 表格(Google Sheets)中进行过滤的另一个选项是使用FILTER功能。
FILTER函数允许您根据您选择的任意数量的条件过滤数据集。
让我们使用与上一节相同的Customer Purchases(Customer Purchases)示例来看看使用FILTER函数。
FILTER函数的语法如下：
过滤器（范围，条件1，[条件2，...]）(FILTER(range, condition1, [condition2, …]))
只需要过滤的范围和一个条件。您可以根据需要添加任意数量的附加条件，但它们不是必需的。
FILTER函数的参数如下：
- range：要过滤的单元格范围(range of cells)
- condition1：您要用于过滤结果的列或行
- conditionX：您还想用来过滤结果的其他列或行
请记住，您用于条件的范围需要与整个范围具有相同的行数。
例如，如果您想创建与本文第一部分相同的过滤器，您将使用以下FILTER 函数(FILTER function)。
=FILTER(F1:J20,SEARCH(“Playa Vista”,H1:H20),SEARCH(“gov”,I1:I20))
这会从原始数据表 (F1: J20 ) 中获取行和列，然后使用嵌入式SEARCH函数在地址和电子邮件列中搜索我们感兴趣的文本段。
仅当您要查找文本段时才需要SEARCH函数。(SEARCH)如果您对完全匹配更感兴趣，则可以将其用作条件语句：
I1:I20=”[email protected] "
如果要过滤大于或小于固定限制的值，还可以使用其他条件运算符，例如 > 或 <。
按Enter后，您将看到FILTER函数的结果作为结果表。
如您所见，仅返回您在函数的第一个参数中选择的范围内的列。因此，将FILTER函数放置在有空间（足够的列）以显示所有结果的单元格中很重要。
在 Google 表格中使用过滤器
Google 表格(Google Sheets)中的过滤器是一种非常强大的方法，可以在Google表格(Google Sheets)中挖掘大量数据(very large sets of data)。FILTER功能使您可以灵活地保留原始数据集，但将结果输出到其他地方。
Google 表格(Google Sheets)中的内置过滤器功能可让您在任何给定时刻以您感兴趣的任何方式修改活动数据集视图。您可以随意保存、激活、停用或删除过滤器。
对于在Google 表格(Google Sheets)中使用过滤器，您有什么有趣的提示吗？在下面的评论部分分享(Share)这些。
How to Filter in Google Sheets
When you’re working with largе datasets, it’s uѕeful to know how to filter in Google Sheets.
There are two ways to do this. You can use filter views in the Google Sheets menu, which lets you customize specific ways to filter the data in the sheet that you can reuse. A more dynamic method to filter data in Google Sheets is using the FILTER function.
In this article, you’ll learn how to use both methods.
Create a Filter View in Google Sheets
In this method, you’ll learn how to apply a filter that will show you only data from a large dataset that you want to see. This filter view will hide all other data. You can also combine filter parameters for more advanced filter views as well.
How to Create a Filter View
As an example, imagine you have a set of data that includes product purchases made by customers. The data includes names, addresses, emails, phone numbers, and more.
For this example, let’s say you want to see only customers from Playa Vista, CA, and only customers who have a “.gov” email address.
1. To create this filter, select the Create a Filter icon in the menu. This icon looks like a funnel.
2. You’ll see small filter icons appear on the right side of each column header. Select this funnel icon at the top of the Customer Address field to customize the filter for this field.
3. This will open a window where you can customize the filter options. Select the arrow to the left of Filter by values. Select Clear to deselect all entries in that field.
Note: This is an important step because it resets the view from showing all records to showing none. This prepares Excel to apply the filter you’re going to create in the next steps.
4. Type the text in the field below that you want to filter the field by. In this example, we’ll use “Playa Vista” and select the search icon to see only those records that contain that text. Select all of the records that show up in the results list. This customizes your filter so that only the items you select will be displayed in the spreadsheet.
4. Once you select the OK button, you’ll see the data in your sheet filtered so that only customers from Playa Vista are displayed.
5. To filter on a second field, select the filter icon at the top of that field. Repeat the process above to create the filter. Clear all entries, type the “gov” text to filter out any email addresses that don’t contain “gov,” select those entries, and select OK.
Now you’ve customized your filter so that only the records in the dataset you care about are displayed. So that you don’t have to repeat this process every time you open the spreadsheet, it’s time to save the filter.
Saving and Viewing Filter Views
When you’re done setting up your filter, you can save it as a filter view that you can enable at any time.
To save a filter view, just select the dropdown arrow next to the filter icon and select Save as filter view.
You’ll see a dark gray field open at the top of the spreadsheet. This will show you the selected range that the filter applies to and the name of the field. Just select the field next to Name and type the name you’d like to apply to that filter.
Just type the name and press Enter.
You’ll notice on the right side of the gray bar that there’s a gear icon. Select this icon to see filter options.
Available options include:
- Rename the filter
- Update the range that the filter applies to
- Duplicate the filter to update it without affecting the original filter
- Delete the filter
You can turn off the filter you’ve enabled at any point simply by selecting the filter icon again.
Note that when any filter is enabled, the filter icon will turn green. When you disable the filters, this icon will turn back to black. This is a quick way to see the entire dataset or if any filter has removed data from the current view.
Later, if you want to re-enable any of the filters you’ve created, just select the dropdown arrow next to the filter icon. You’ll see the filters you’ve saved appear in the menu. Just select that filter to enable it whenever you like.
This will filter the view again using the filter settings you’ve configured.
Using the FILTER Function
Another option to filter in Google Sheets is using the FILTER function.
The FILTER function lets you filter a dataset based on any number of conditions you choose.
Let’s take a look at using the FILTER function using the same Customer Purchases example as the last section.
The syntax of the FILTER function is as follows:
FILTER(range, condition1, [condition2, …])
Only the range and one condition for filtering are required. You can add as many additional conditions as you like, but they aren’t required.
The parameters of the FILTER function are as follows:
- range: The range of cells you want to filter
- condition1: The column or rows that you want to use to filter results
- conditionX: Other columns or rows you’d also like to use to filter results
Keep in mind that the range you use for your conditions needs to have the same number of rows as the entire range.
For example, if you want to create the same filter as the first part of this article, you’d use the following FILTER function.
=FILTER(F1:J20,SEARCH(“Playa Vista”,H1:H20),SEARCH(“gov”,I1:I20))
This grabs the rows and columns from the original table of data (F1:J20) and then uses an embedded SEARCH function to search the address and email columns for the text segments that we’re interested in.
The SEARCH function is only needed if you want to look for a text segment. If you’re more interested in an exact match, you can just use this as the condition statement instead:
I1:I20=”[email protected]”
You can also use other conditional operators, like > or < if you want to filter values greater than or less than a fixed limit.
Once you press Enter, you’ll see the results of the FILTER function as a results table.
As you can see, only the columns in the range you selected in the first parameter of the function are returned. So it’s important to place the FILTER function in a cell where there’s room (enough columns) for all of the results to appear.
Using Filters in Google Sheets
Filters in Google Sheets are a very powerful way to dig through very large sets of data in Google Sheets. The FILTER function gives you the flexibility of keeping the original dataset in place but outputting the results elsewhere.
The built-in filter feature in Google Sheets lets you modify the active dataset view in whatever way you’re interested at any given moment. You can save, activate, deactivate, or delete filters however you like.
Do you have any interesting tips to offer for using filters in Google Sheets? Share those in the comments section below.
