自动过滤多个通配符 [英] Auto filter with multiple wildcards

问题描述

是否有一种使用Excel自动筛选器的方法，您可以设置一个预定义的值列表进行筛选，并且它将返回包含该短语的列中的所有单元格?例如，想象下面的数据(全部在单个列中).

Is there a way to use an Excel auto filter, whereby you set a predefined list of values to filter by, and it will return all the cells in a column that contain that phrase? For example imagine the data below (all in a single column).

       A
1    NAMES
2  Brian
3  Brian, John
4  Brian, Mark
5  Mark, John

该过滤器将具有三个预定义值Brian，John和Mark，当您选择1个或多个选项时，它是否满足所有选定的过滤器?因此，在此示例中，Brian将返回前三个单元格，而选择Mark和John则仅返回最后一个单元格.

The filter would have three predefined values, Brian, John and Mark, when you select either 1 or more options, it satisfies all selected filters? So in this example Brian would return the first three cells, where as selecting Mark and John would return only the last.

我知道可以使用Advanced Filter在数据列表中执行一次操作，但是我正在寻找一种实时的方法来执行多次操作.

I know this is possible to do once in a list of data using the Advanced Filter, but I'm looking for a real-time way of doing this many times.

推荐答案

您不能使用通配符过滤两个以上的条件.样本数据的性质是，您将使用="Brian *"，="Mark *"或="* John"将通配符作为开头为... 或以... 结尾.您只能在任何一个过滤器操作中使用其中两个.您不能通过创建通配值数组来添加第三个值.

You cannot filter more than two criteria with wildcards. The nature of your sample data is such that you would be using ="Brian*", ="Mark*" or ="*John" to wildcard the first names as either Begins with... or Ends with... criteria. You can only use two of these in any one filter operation. You cannot add a third by creating an array of wildcarded values.

这有效:

with activesheet.cells(1, 1).currentregion
    .AutoFilter Field:=1, Criteria1:="=Brian*", Operator:=xlOr, Criteria2:="=*John"
end with

这不起作用:

with activesheet.cells(1, 1).currentregion
    .AutoFilter Field:=1, Criteria1:=Array("Brian*", "Mark*", "*John"), Operator:=xlFilterValues
end with

这篇关于自动过滤多个通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！