excel自动筛选_具有范围内条件的Excel自动筛选-CSDN博客

本文介绍了Excel自动筛选的功能演变，从Excel 2003及更早版本的仅支持两个条件，到Excel 2007及以后版本允许在每一列中设置多个筛选条件。通过实例展示了如何在Excel中创建和使用多条件自动筛选，包括记录宏以自动生成筛选代码，并使用VBA创建动态条件数组。同时提供了下载示例文件以供实践操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

excel自动筛选

In Excel 2003, and earlier versions, an AutoFilter allows only two criteria for each column. In Excel 2007 and later, you can select multiple criteria from each column in the table. See how to apply an Excel AutoFilter with multiple criteria in a range on the worksheet

在Excel 2003和早期版本中，自动筛选器仅对每个列使用两个条件。在Excel 2007和更高版本中，您可以从表的每一列中选择多个条件。在工作表上查看如何应用具有多个条件的Excel自动筛选

自动筛选条件 (AutoFilter Criteria)

In Excel 2003, and earlier versions, if you wanted to filter for multiple criteria, you had to use an Advanced Filter.

在Excel 2003和早期版本中，如果要过滤多个条件，则必须使用Advanced Filter 。

To prepare for an Advanced Filter, list all the criteria on a worksheet, and then use that list (and its heading cell) as the criteria range.

要准备高级筛选器，请在工作表上列出所有条件，然后使用该列表(及其标题单元格)作为条件范围。

In Excel 2007 and Excel 2010, the AutoFilter feature has been improved, and you can select multiple criteria in each column.

在Excel 2007和Excel 2010中，“自动筛选”功能已得到改进，您可以在每列中选择多个条件。

记录自动筛选宏 (Record an AutoFilter Macro)

If you record a macro while selecting criteria in Excel 2007, it will look something like this:

如果您在Excel 2007中选择条件时记录宏，它将看起来像这样：

The criteria are entered as an array, showing all three items that were selected in the drop down list.

标准以数组形式输入，显示在下拉列表中选择的所有三个项目。

创建自己的阵列 (Create Your Own Array)

In the Contextures mail bag this week, someone asked if it's possible to create this type of AutoFilter criteria array from a list on the worksheet. And the answer is yes, you can!

在本周的Contextures邮袋中，有人问是否可以从工作表上的列表中创建这种类型的AutoFilter条件数组。答案是肯定的，可以！

For this example, there's a dynamic named range -- CritList -- on the Lists worksheet. The items in the CritList range will be used as the AutoFilter criteria array.

在此示例中，“列表”工作表上有一个动态的命名范围 -CritList。 CritList范围内的项目将用作“自动筛选”条件数组。

On the Orders sheet, the fourth column -- Products -- will be filtered using this criteria list.

在“订单”表上，第四列-产品-将使用此条件列表进行过滤。

带数组的自动过滤代码 (AutoFilter Code With Array)

In an Excel VBA procedure, you can create a variable to store the values from the CritList named range. Define this variable as a Variant, and it will store the values as an array.

在Excel VBA过程中，您可以创建一个变量来存储CritList命名范围中的值。将该变量定义为Variant，然后将值存储为数组。

vCrit = rngCrit.Value

Then, to use this variable as the AutoFilter criteria list, transpose the array, so it's read as a row, instead of a column. If you don't transpose the array, only the first item would be used in the criteria array. (Or, create your worksheet list in a row, instead of a column, and you won't have to transpose it.)

然后，要将此变量用作“自动筛选”条件列表，请转置数组，以便将其读取为行而不是列。如果不转置数组，则条件数组中仅会使用第一项。 (或者，在一行中而不是在列中创建工作表列表，而不必进行转置。)

Criteria1:=Application.Transpose(vCrit)

Here's the complete code for the AutoFilter:

这是自动筛选的完整代码：

Sub FilterRangeCriteria()
Dim vCrit As Variant
Dim wsO As Worksheet
Dim wsL As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range
Set wsO = Worksheets("Orders")
Set wsL = Worksheets("Lists")
Set rngOrders = wsO.Range("$A$1").CurrentRegion
Set rngCrit = wsL.Range("CritList")
vCrit = rngCrit.Value
rngOrders.AutoFilter _
    Field:=4, _
    Criteria1:=Application.Transpose(vCrit), _
    Operator:=xlFilterValues
End Sub