excel 筛选索引列表_Excel筛选器以匹配项目列表

最新推荐文章于 2022-09-30 17:48:23 发布

culiao2169

最新推荐文章于 2022-09-30 17:48:23 发布

阅读量695

点赞数

文章标签： python java excel mysql 数据分析

原文链接：https://contexturesblog.com/archives/2017/05/18/excel-filter-to-match-list-of-items/

版权

excel 筛选索引列表

To make it easy to filter for several different items, you create a list of those items on a worksheet. Then, filter your data based on that list, so you don't have to check all the items manually each time.

为了使筛选多个不同项目变得容易，您可以在工作表上创建这些项目的列表。然后，根据该列表过滤数据，因此您不必每次都手动检查所有项目。

两种过滤方式 (Two Options for Filtering)

Here's the sample data, and the list of items that we want to filter. The table in column F is named tblFind, and cells F2:F3 are named FindList.

这是示例数据，以及我们要过滤的项目列表。 F列中的表名为tblFind ，单元格F2：F3名为FindList 。

We'll look at two options for filtering the list:

我们将看两个过滤列表的选项：

Filter rows that have an exact Product match for items in the list
筛选出与列表中的商品完全匹配的产品行
Filter rows that contain an item in the list, anywhere in the Product field
在产品字段中的任何位置过滤列表中包含项目的行

进阶筛选 (Advanced Filter)

Both options will use an Advanced Filter, so a Criteria range is added to the worksheet.

这两个选项都将使用Advanced Filter ，因此Criteria范围将添加到工作表中。

We'll be using a formula in the criteria cell, so type a heading that is different from any heading in the data table, or leave the criteria heading blank.

我们将在条件单元格中使用公式，因此键入与数据表中任何标题均不同的标题，或将条件标题留为空白。

列表中项目的完全匹配 (Exact Match For Items in List)

For the first filter, we want to find rows with a product that is an exact match for one of the list items.

对于第一个过滤器，我们要查找具有与列表项之一完全匹配的产品的行。

In cell C2, enter the following formula. It refers to the named range in the list of items, and the first Product cell in the data table.
在单元格C2中，输入以下公式。它引用了项目列表中的命名范围以及数据表中的第一个“产品”单元格。

=COUNTIF(FindList,C5)
= COUNTIF(FindList，C5)

The formula uses the COUNTIF function to check each record, and test for the list items. Rows with an exact match will be returned in the filter.

该公式使用COUNTIF函数检查每个记录，并测试列表项。完全匹配的行将在过滤器中返回。

运行高级过滤器 (Run the Advanced Filter)

To run the Advanced Filter:

运行高级过滤器：

Select a cell in the data table
在数据表中选择一个单元格
On the Data tab of the Ribbon, in the Sort & Filter group, click Advanced
在功能区的“数据”选项卡上的“排序和筛选”组中，单击“高级”。
For Action, select Filter the list, in-place
对于“操作”，选择“就地过滤列表”
For List range, select the data table
对于列表范围，选择数据表
For Criteria range, select C1:C1 – the criteria heading and formula cells
对于“条件范围”，请选择C1：C1 –条件标题和公式单元格
Click OK, to see the results
单击确定，以查看结果

The 4 rows that have a product that is exactly "Milk" or "Cookies" (case does not matter), are visible, and all other rows are hidden.

产品完全为“牛奶”或“饼干”(大小写无关)的4行是可见的，所有其他行均被隐藏。

在列表中包含一个项目 (Contain an Item in the List)

For the second filter, we want to find rows with a product that contains one of the list items, anywhere in the Product cell.

对于第二个过滤器，我们要在“产品”单元格中的任何位置查找包含产品的行，其中包含列表项之一。

In cell C2, enter the following formula. It refers to the named range in the list of items, and the first Product cell in the data table.
在单元格C2中，输入以下公式。它引用项目列表中的命名范围，并引用数据表中的第一个“产品”单元格。

=SUMPRODUCT(COUNTIF(C5,"*"& FindList &"*"))>0
= SUMPRODUCT(COUNTIF(C5，“ *”＆FindList＆“ *”))> 0

The COUNTIF function checks each Product cell, and tests for the list items. The * wildcards are used before and after the list item, so the text can be found anywhere in the Product cell. The SUMPRODUCT function sums the number, and if it's greater than zero, the result is TRUE.

COUNTIF函数检查每个产品单元格，并测试列表项。 *通配符用于列表项的前后，因此可以在“产品”单元格中的任何位置找到文本。 SUMPRODUCT函数对数字求和，如果它大于零，则结果为TRUE。

Run the Advanced Filter with the same settings as in the first example, and the 6 rows that contain "Milk" or "Cookies" (case does not matter) in the product cell are visible.

使用与第一个示例相同的设置运行“高级筛选器”，并且在产品单元格中包含“牛奶”或“饼干”(大小写无关)的6行可见。