高级自动过滤器以排除某些值 [英] Advance AutoFilter to exclude certain values

问题描述

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

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:K5000 and my criteria is in H2:H3 (The criteria will grow but I kept the list small for testing). Any help would be greatly appreciated!

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

推荐答案

从K2:K5000中排除H2:H3中的值使用高级过滤器，您可以使用以下方法:

To exclude the values in H2:H3 from K2:K5000 using advanced filter you can use following approach:

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

• 在I2

• Make sure cell K1 is 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$2:$H$3,0))

使用以下代码排除行

Use the following code to exclude rows

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

这篇关于高级自动过滤器以排除某些值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！