Excel:具有多个条件的过滤器:AutoFilter或AdvancedFilter [英] Excel : filter with Multiple Criteria : AutoFilter or AdvancedFilter

问题描述

我正在尝试创建一个过滤器，该过滤器将执行以下操作

I am trying to create a filter that will do the below

以"4"开头或 包含"2TWH"或 包含"2TER"

Begins with "4" or Contains "2TWH" or Contains "2TER"

我相信结果会像这样

ActiveSheet.Range("$A$5:$H$10").AutoFilter Field:=2, Criteria1:="=4*", _
    Operator:=xlOr, Criteria2:="=*2TWH*" 

但是我需要以某种方式为="= 2TER "

but I need to somehow also have a criteria 3 for ="=2TER"

我做了一些谷歌搜索，发现您可以将值存储到数组中，然后调用数组值，但是我无法以可用的方式将它们添加到数组中.

I did some googling and saw that you can store the values into an array and then call the array values but I was unable to get them into the array in a usable fashion.

请问有人可以提供一些帮助吗?

Is anyone able to offer some assistance with this please?

推荐答案

这应该可以解决问题:

ActiveSheet.Range("$A$5:$H$10").AutoFilter Field:=2, Criteria1:="=4*", _
    Operator:=xlOr, Criteria2:=Array("*2TWH*","*2TER*")

这不会引发任何错误，但是...
由于存在通配符(*)，它会仅采用2条条件.
这是数组(此处为*2TER*)

This doesn't throw any error, but ...
it'll only take 2 criteria because there are wildcards (*).
Here it's the last criteria from the array (here *2TER*)

理想情况下，这会很好，因为它适用于常量，
但由于您使用通配符(*)，因此仅支持2个条件...:/

Ideally, this would have been nice, as it works for constants,
but because you use WildCards (*) it only supports 2 criteria... :/

ActiveSheet.Range("$A$1:$H$10").AutoFilter Field:=2, Criteria1:=Array("*2TWH*", "*2TER*", "4*"), _
    Operator:=xlFilterValues

---

---

因此，您必须使用AdvancedFilter:

---

---

So you'll have to go with AdvancedFilter :

With ActiveSheet
    'Const xlFilterInPlace = 1
    .Range("$A$5:$H$10").AdvancedFilter _
                        Action:=xlFilterInPlace, _
                        CriteriaRange:=WsFilterSettings.Range("A1:A4")
End With 'ActiveSheet

AdvancedFilter将范围作为条件输入，CriteriaRange，因此您必须放在工作表上:

• 您要应用于过滤器的列的标题
• 您各自标题下的条件(如果您有多个列)

• the header of the column you want to apply to the filter on
• your criteria below the respective header (if you have multiples columns)

该范围的每个列由 AND
链接 该范围的每个 ROW 由 OR
链接 因此，请仔细构建您的桌子！

Each COLUMN of that Range, are linked by an AND
Each ROW of that Range, are linked by an OR
So build your table carefully!

在上面的示例代码中，我使用了:
(假设您的列标题是要过滤的列):

In the above example code, I have used :
(let's say that your column's header was Column To Filter On) :

A1 | Column To Filter On
A2 | 4*
A3 | *2TWH*
A4 | *2TER*

这篇关于Excel:具有多个条件的过滤器:AutoFilter或AdvancedFilter的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！