Advance AutoFilter可以排除某些值 [英] Advance AutoFilter to exclude certain values
问题描述
我想在Excel中的Sheet中过滤大量名称。在另一张表中,我已经包含一个我想要从较大列表中过滤掉并排除的名称列表。如何使用高级过滤器来做到这一点?我在下面尝试过,但似乎没有工作。我的大名单在 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
推荐答案
要排除 中的值H2:H3
从 K2:K5000
使用高级过滤器可以使用以下方法:
To exclude the values in H2:H3
from K2:K5000
using advanced filter you can use following approach:
- 确保单元格
K1
不为空(输入任何标题) - 查找2个未使用的单元格(例如
I1:I2
) - 离开
I1
blank -
在
I2中输入以下公式
- Make sure cell
K1
is not empty (enter any header) - Find 2 unused cells (e.g.
I1:I2
) - Leave
I1
blank 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
这篇关于Advance AutoFilter可以排除某些值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!