Range.AutoFilter 方法 (Excel)Range.AutoFilter method (Excel)

通过 AutoFilter 筛选列表。Filters a list by using the AutoFilter.


expression.AutoFilter (Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown)expression.AutoFilter (Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown)

expression:一个返回 Range 对象的表达式。expression An expression that returns a Range object.


名称Name 必需/可选Required/Optional 数据类型Data type 说明Description
FieldField 可选Optional VariantVariant 相对于作为筛选基准字段(从列表左侧开始,最左侧的字段为第一个字段)的字段的整型偏移量。The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field one).
Criteria1Criteria1 可选Optional VariantVariant 条件(字符串;例如,“101”)。The criteria (a string; for example, "101"). 使用 "=" 查找空白字段,使用 "<>" 查找非空白字段,使用 "><" 选择数据类型中的(否数据)字段。Use "=" to find blank fields, "<>" to find non-blank fields, and "><" to select (No Data) fields in data types.

如果此参数被省略,条件为“全部”。If this argument is omitted, the criteria is All. 如果 OperatorxlTop10Items,则 Criteria1 指定项数(例如“10”)。If Operator is xlTop10Items, Criteria1 specifies the number of items (for example, "10").
OperatorOperator 可选Optional XlAutoFilterOperatorXlAutoFilterOperator 一个指定筛选器类型的 XlAutoFilterOperator 常量。An XlAutoFilterOperator constant specifying the type of filter.
Criteria2Criteria2 可选Optional VariantVariant 第二个条件(字符串)。The second criteria (a string). Criteria1Operator 一起组合成复合筛选条件。Used with Criteria1 and Operator to construct compound criteria. 也用作日期字段的单一条件(按日、月或年筛选)。Also used as single criteria on date fields filtering by date, month or year. 后跟一个数组,该数组用于详述和筛选 Array(Level, Date)Followed by an Array detailing the filtering Array(Level, Date). 其中,Level 为 0-2(年、月、日),Date 为筛选期内的一个有效日期。Where Level is 0-2 (year,month,date) and Date is one valid Date inside the filtering period.
SubFieldSubField 可选Optional VariantVariant 对其应用条件的数据类型中的字段(例如,来自地理位置的“人口”字段或来自股票的“交易量”字段)。The field from a data type on which to apply the criteria (for example, the "Population" field from Geography or "Volume" field from Stocks). 省略此值目标是“(显示值)”。Omitting this value targets the "(Display Value)".
VisibleDropDownVisibleDropDown 可选Optional VariantVariant 如果为 True,则显示已筛选字段的 AutoFilter 下拉箭头。True to display the AutoFilter drop-down arrow for the filtered field. 如果为 False,则隐藏已筛选字段的 AutoFilter 下拉箭头。False to hide the AutoFilter drop-down arrow for the filtered field. 默认情况下为 TrueTrue by default.

返回值Return value



如果忽略全部参数,此方法仅在指定区域切换自动筛选下拉箭头的显示。If you omit all the arguments, this method simply toggles the display of the AutoFilter drop-down arrows in the specified range.

Excel for Mac 不支持此方法。Excel for Mac does not support this method. 支持对 SelectionListObject 使用类似方法。Similar methods on Selection and ListObject are supported.

与在公式中不同,子字段无需使用括号即可包含空格。Unlike in formulas, subfields do not require brackets to include spaces.


此示例会筛选工作表 Sheet1 上从单元格 A1 开始的列表,从而仅显示字段 1 等于字符串 Otis 的条目。This example filters a list starting in cell A1 on Sheet1 to display only the entries in which field one is equal to the string Otis. 将隐藏字段 1 的下拉箭头。The drop-down arrow for field one will be hidden.

Worksheets("Sheet1").Range("A1").AutoFilter _
 Field:=1, _
 Criteria1:="Otis", _

此示例会筛选工作表 Sheet1 上从单元格 A1 开始的列表,从而仅显示字段 1 的值包含 SubField = 行政署 1 (省/市/自治区/其他) 的条目,其中该值为“华盛顿”。This example filters a list starting in cell A1 on Sheet1 to display only the entries in which the values of field one contain a SubField, Admin Division 1 (State/province/other), where the value is Washington.

Worksheets("Sheet1").Range("A1").AutoFilter _
 Field:=1, _
 Criteria1:="Washington", _
 SubField:="Admin Division 1 (State/province/other)"

此示例会筛选工作表 Sheet1 上的表格 Table1,从而仅显示字段 1 的值具有为 1、3、“西雅图”或“雷德蒙德”的“(显示值)”的条目。This example filters a table, Table1, on Sheet1 to display only the entries in which the values of field one have a "(Display Value)" that is either 1, 3, Seattle, or Redmond.

Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
 Field:=1, _
 Criteria1:=Array("1", "3", "Seattle", "Redmond"), _

数据类型可应用多个 SubField 筛选器。Data types can apply multiple SubField filters. 此示例会筛选工作表 Sheet1 上的表格 Table1,从而仅显示字段 1 的值包含 SubField = 时区的条目,其中该值为太平洋时区,名为“找到的日期”的 SubField 为 1851 或“(无数据)”。This example filters a table, Table1, on Sheet1 to display only the entries in which the values of field one contain a SubField, Time Zone(s), where the value is Pacific Time Zone, and where the SubField named Date Founded is either 1851 or there is "(No Data)".

Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
 Field:=1, _
 Criteria1:="Pacific Time Zone", _
 SubField:="Time Zone(s)"
Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
 Field:=1, _
 Criteria1:=Array("1851", "><"), _
 Operator:=xlFilterValues, _
 SubField:="Date founded"

此示例会筛选工作表 Sheet1 上的表格 Table1,从而根据“人口”SubField 显示字段 1 的前 10 个条目。This example filters a table, Table1, on Sheet1 to display the Top 10 entries for field one based off the Population SubField.

Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
 Field:=1, _
 Criteria1:="10", _
 Operator:=xlTop10Items, _

此示例会筛选 Sheet1 上的表格 Table1,以显示字段 1 中 2019 年 1 月和 2019 年 2 月的所有条目。This example filters a table, Table1, on Sheet1 to display the all entries for January 2019 and February 2019 for field one. 不必为包含 1 月 31 日的行。There does not have to be a row containing January the 31.

Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
 Field:=1, _
 Criteria2:=Array(1, "1/31/2019", 1, "2/28/2019") 

支持和反馈Support and feedback

有关于 Office VBA 或本文档的疑问或反馈?Have questions or feedback about Office VBA or this documentation? 请参阅 Office VBA 支持和反馈,获取有关如何接收支持和提供反馈的指南。Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.