vba 高级自动筛选以排除某些值

Advance AutoFilter to exclude certain values

提问by Peter Burke

I want to filter a large list of names in a Sheet in excel. In another sheet I have contained a list of names that I want to filter out and exclude from the larger list. How would I use the advanced filter to do this? I have tried this below but it is not seeming to work. My big list is in K2:K5000and my criteria is in H2:H3(The criteria will grow but I kept the list small for testing). Any help would be greatly appreciated!

我想在 Excel 的工作表中过滤大量名称。在另一张工作表中，我包含了一个名称列表，我想过滤掉这些名称并从更大的列表中排除这些名称。我将如何使用高级过滤器来做到这一点？我在下面尝试过这个，但它似乎不起作用。我的大名单在K2:K5000，我的标准也在H2:H3（标准会增加，但我保持小名单进行测试）。任何帮助将不胜感激！

Sub Filter()
    Sheet5.Range("K2:K5000").AdvancedFilter Action:=xlFilterInPlace, _
        CriteriaRange:=Sheets("Sheet3").Range("H2:H3"), Unique:=False
End Sub

回答by BrakNicku

To excludethe values in H2:H3from K2:K5000using advanced filter you can use following approach:

要排除的值H2:H3从K2:K5000采用先进的过滤器，你可以使用以下方法：

• Make sure cell K1is not empty (enter any header)
• Find 2 unused cells (e.g. I1:I2)
• Leave I1blank

• Enter the following formula in I2

   =ISNA(MATCH(K2,$H:$H,0))
  

• Use the following code to exclude rows

   Sheet5.Range("K1:K5000").AdvancedFilter Action:=xlFilterInPlace, _
       CriteriaRange:= Sheets("Sheet3").Range ("I1:I2"), Unique:=False 
  

• 确保单元格K1不为空（输入任何标题）
• 找到 2 个未使用的单元格（例如I1:I2）
• 离开I1空白

• 在中输入以下公式 I2

   =ISNA(MATCH(K2,$H:$H,0))
  

• 使用以下代码排除行

   Sheet5.Range("K1:K5000").AdvancedFilter Action:=xlFilterInPlace, _
       CriteriaRange:= Sheets("Sheet3").Range ("I1:I2"), Unique:=False 
  

回答by dleuschke

I am not sure off the top of my head how you would use advanced filter to exclude, but you can use formulas in your advanced filter(near the bottom). You can, however, just use a dictionary to store values you want to exclude, then exclude (hide rows, or autofilter on the ones not found in your exclusion list)

我不确定您将如何使用高级过滤器进行排除，但您可以在高级过滤器中使用公式（靠近底部）。但是，您可以只使用字典来存储要排除的值，然后排除（隐藏行，或对排除列表中未找到的行进行自动过滤）

Sub Filter()
Dim i as integer
Dim str as string
Dim dict As Object

Set dict = CreateObject("Scripting.Dictionary")

With Worksheets("Sheet3")
    For i = 2 To 3
        str = CStr(.Range("H" & i).Value)
        If Not dict.exists(str) Then
            dict.Add str, vbNullString
        End If
    Next i
End With

With Sheet5
    For i = 2 To 5000
        str = CStr(.Range("K" & i).Value)
        If Len(str) > 0 And dict.exists(str) Then
            .Range("K" & i).EntireRow.Hidden = True
        Elseif
            'alternatively, you can add those that aren't found
            'to an array for autofilter
        End if
    Next i
End With

'If building autofilter array, apply filter here.

End Sub

Using AutoFilter:

使用自动筛选：

Use an array of strings as criteria to filter on with the "Operator:=xlFilterValues" argument of AutoFilter. Build your array however you want, I chose to do it by building a string with a for loop and splitting (quick to write and test, but not ideal for a number of reasons).

使用字符串数组作为条件，通过 AutoFilter 的“Operator:=xlFilterValues”参数进行过滤。随心所欲地构建数组，我选择通过构建一个带有 for 循环和拆分的字符串来实现（快速编写和测试，但由于多种原因并不理想）。

Note: AutoFilter is applied to the headers, not data.

注意：自动筛选应用于标题，而不是数据。

With Sheet5
    .AutoFilterMode = False
    .Range("K1").AutoFilter _
      Field:=1, _
      Criteria1:=arr, _
      Operator:=xlFilterValues
End With

回答by L42

I think you need to understand first how to use the Advance filter.
There is a good tutorial you can find HERE.

我认为您首先需要了解如何使用高级过滤器。
你可以在这里找到一个很好的教程。

Now based on that, let us make an example. Suppose you have below data:

现在基于此，让我们举个例子。假设您有以下数据：

Now, let us say you want to filter out Data1and Data2. According, to the link you can use a formula as criteria but:

现在，假设您要过滤掉Data1和Data2。根据链接，您可以使用公式作为标准，但是：

Note: always place a formula in a new column. Do not use a column label or use a column label that is not in your data set. Create a relative reference to the first cell in the column (B6). The formula must evaluate to TRUE or FALSE.

注意：始终将公式放在新列中。不要使用列标签或使用不在您的数据集中的列标签。创建对列 (B6) 中第一个单元格的相对引用。公式的计算结果必须为 TRUE 或 FALSE。

So in our case, our relative reference is A11(the first cell or item in the field you want filtered).
Now we make a formula in B2since we cannot use A2, it is a Column Label.
Enter the formula: =A11<>"Data1".

所以在我们的例子中，我们的相对引用是A11（您要过滤的字段中的第一个单元格或项目）。
现在我们创建一个公式，B2因为我们不能使用A2，它是一个列标签。
输入公式：=A11<>"Data1"。

Above took care of Data1but we need to filter out Data2as well.
So we make another formula in C2which is: =A11<>"Data2"

以上处理了Data1，但我们也需要过滤掉Data2。
所以我们制作了另一个公式，C2其中：=A11<>"Data2"

Once properly set up, you can now apply Advance Filtermanually or programmatically. A code similar to yours is found below:

正确设置后，您现在可以Advance Filter手动或以编程方式申请。与您的代码类似的代码如下：

With Sheets("Sheet1")
    .Range("A10:A20").AdvancedFilter xlFilterInPlace, .Range("A1:C2")
End With

And Hola! We have successfully filtered out Data1and Data2.

还有你好！我们已经成功过滤掉了Data1和Data2。

Result:

结果：

It took me a while to get a hang of it as well but thanks to that link above, I manage to pull it of.
I have learned something new as well today :-). HTH.

我也花了一段时间才掌握它，但多亏了上面的链接，我才设法把它拉出来。
我今天也学到了一些新东西:-)。哈。

Additional:

额外的：

I see that you have your criteria on another Sheet so you have to just use that in your formula.
So if in our example you have Data1and Data2in H2:H3in Sheet2, your formula in B2and C2is: =A11<>Sheet2!H2and =A11<>Sheet2!H3respectively.

我看到您在另一张工作表上有您的标准，因此您必须在公式中使用它。
所以，如果在我们的例子中，你有数据1和数据2中H2:H3在Sheet2中，您的公式B2和C2是：=A11<>Sheet2!H2和=A11<>Sheet2!H3分别。

回答by HymanHannum

You don't really even need VBA for this... to achieve the same result:

你甚至不需要 VBA 来实现相同的结果：

1. Put the values into a separate spreadsheet, in the first column.
2. Create 2 new columns next to the data you want to filter in your original spreadsheet

3. In the first column next to your data to be filtered, use

   =VLOOKUP(A2, [nameOfOtherSpreadSheet.xlsx/xlsm/xls/etc]sheetName!$A:$A,1, FALSE)

1. 将值放入单独的电子表格中，位于第一列。
2. 在原始电子表格中要过滤的数据旁边创建 2 个新列

3. 在要过滤的数据旁边的第一列中，使用

   =VLOOKUP(A2, [nameOfOtherSpreadSheet.xlsx/xlsm/xls/etc]sheetName!$A:$A,1, FALSE)

Where A2 is the value you're searching for, field 2 is the reference of the range in which you want to search for this value, 1 is the index of the column in which you're searching, and FALSE tells VLOOKUPto only return exact matches.

其中 A2 是您要搜索的值，字段 2 是您要搜索该值的范围的引用，1 是您要搜索的列的索引，FALSE 告诉VLOOKUP只返回精确火柴。

1. In the second column next to the data you want to filter, use

   =IFERROR(G2, FALSE)

4. 在要过滤的数据旁边的第二列中，使用

   =IFERROR(G2, FALSE)

Where G2 is the reference of the function that might return an error, and FALSE is the value you want to return if that function throws an error.

其中 G2 是可能返回错误的函数的引用，而 FALSE 是该函数抛出错误时要返回的值。

1. Filter the second column next to the data you want to filter for FALSEs

5. 筛选要筛选 FALSE 的数据旁边的第二列

This should return the original data set without the values you wanted to exclude.

这应该返回没有您想要排除的值的原始数据集。

Record a macro to do this it's one step instead of 5 for future uses.

记录一个宏来做到这一点，它是一步而不是 5 以备将来使用。