Excel VBA高级筛选技巧

标签：VBA，AdvancedFilter方法

本文探讨如何使用AdvancedFilter基于多个条件进行筛选，而不仅仅是一列数据。示例数据如下图1所示（本文学习整理自wellsr.com，有兴趣的朋友可以查阅原文）。

图1：一个订单列表，包括标题和相关名称、地区、州和金额。

可以显式地定义要筛选的数据区域，包括结束的行。在这个示例中，表跨越了列A至列G，我们定义的筛选的区域如下：

Range(“A:G”).AdvancedFilter

条件区域

AdvancedFilter方法可以接受一组筛选条件。我们无须在VBA代码中硬编码条件，我们可以构建一个新表，其标题与数据区域中的标题相匹配，然后，将筛选需求添加到此表中。第I列和第J列显示了新表，如下图2所示。

图2

不需要包含每个标题，也不必为表中的每个标题指定条件。示例数据区域有7个字段，而条件区域表只有City和Amount，甚至没有为后者输入限制条件。

接着，设置CriteriaRange属性为单元格区域I1:J2，如下代码所示：

Range(“A:G”).AdvancedFilter CriteriaRange:=Range(“I1:J2”)

目前，我们仍在构建AdvancedFilter语句，因此代码还不能工作。如果现在尝试运行它，将得到“运行时错误’1004’：Range类的AdvancedFilter方法失败”错误，因为尚未定义参数Action，该参数告诉AdvancedFilter是在原有区域显示筛选结果还是将筛选结果复制到其他位置。

到目前为止，要求AdvancedFilter筛选Range(“A:G”)中包含“Pittsburgh”的所有条目。这是一个简单的单列对“City”的筛选。

该区域包含标题/字段和条件本身。注意，与数据区域不同，我们没有输入整列。条件区域中的空行将匹配所有数据记录，这不是我们想要的。相反，Excel将空白单元格（此处为J2）解释为任何值。由于J2在此处为空，因此所有金额（Amount）均有效。

基于多条件的筛选

这里将展示AdvancedFilter方法的强大功能。假设要分析所有超过400美元的“Center”地区的订单。我们将设置我们的筛选表（条件区域），使我们能够灵活地按“City”进行深入调查。

可以设置条件区域如下图3所示：

图3

要设置上图所示作为条件区域，代码如下：

Range(“A:G”).AdvancedFilterCriteriaRange:=Range(“I1:K2”)

使用这个表，我们可以选择按Region、City或两者进行筛选。

也可以有多组条件。我们可以调查金额超过400美元的Center地区或金额超过300美元的West地区。

要添加第二组条件，只需将其添加到新行，并确保在宏中扩展条件区域：

图4

代码如下：

Range(“A:G”).AdvancedFilterCriteriaRange:=Range(“I1:K3”)

注意，这里仍然没有定义Action参数，因此这个宏仍然无法工作。

AND vs OR的高级筛选

在计算机中：

1.AND意味着必须满足所有条件

2.OR意味着必须至少满足一个条件

使用AdvancedFilter方法的条件区域，跨行被视为AND，而向下行被视为OR。

因此，前面的示例中：

CenterAND >100

OR

WestAND >300

使用间隔的高级筛选

使用算术运算符（<，>，等），可以选择间隔：

图5

Range(“A:G”).AdvancedFilterCriteriaRange:=Range(“I1:L3”)

这将筛选：

CenterAND >400
OR
WestAND >300 AND <400

在定义条件区域表时，务必记住ANDs在一行（跨列），ORs向下列（跨行）。

输出区域

唯一的强制参数Action有两个可能的值：XlFilterInPlace和XlFilterCopy。必须定义这些XlFilterActions之一，否则AdvancedFilter方法将发生运行时1004错误失败。

使用xlFilterInPlace

XlFilterInPlace操作通过直接在表中隐藏行来筛选整个数据区域，将在输出中获得所有字段。让我们使用上面的条件：

CenterAND >100
OR
WestAND >300

代码如下：

Sub AdvancedFilterDemo()
   Range("A:G").AdvancedFilter Action:=xlFilterInPlace,criteriarange:=Range("I1:K3")
End Sub

结果如下图6所示。

图6

不符合条件的行现在被隐藏，这是一种非常快速的筛选方法，但它的缺点是可能会将重要行隐藏在主表之外。

要取消隐藏所有行，使用代码：

ActiveSheet.ShowAllData

使用xlFilterCopy

在许多情况下，最好将输出复制到新位置。这可以是另一个工作表，也可以是同一工作表上的另一个位置。

此方法还提供了对输出的更多控制，因为可以选择显示哪些字段。

假设想要条件：

CenterAND >400
OR
WestAND >300
OR
Boston

但仅想输出：

1.family name

2.amount

3.order number

将在与条件区域（浅绿色）相同的列下的第6行（浅蓝色）开始输出。需要手动键入所需的标题名称，以便AdvancedFilter宏知道要粘贴哪些标题，这正是我们在下面浅蓝色中所做的。键入这些内容后，VBAAdvancedFilter方法将知道所需的数据列，并自动将符合筛选条件的结果复制到该位置。如果不执行此操作，将出现“运行时错误’1004’：提取范围有一个缺少或无效的字段名”错误。

代码如下：

Sub AdvancedFilterCopyDemo()
    Range("A:G").AdvancedFilterAction:=xlFilterCopy, _
                   CriteriaRange:=Range("I1:K4"), _
                   CopyToRange:=Range("I6:K6")
End Sub

结果如下图7所示。

图7

最后一个参数CopyToRange包含所选标题/字段的区域。

条件区域和输出区域实际上根本不必共享字段。例如，可以筛选North地区超过500美元的金额，并只输出订单号。这没什么错。当然，这两个区域必须与原始表共享字段，否则AdvancedFilter方法将不知道该做什么。

清除CopyToRange

如果使用XlFilterCopy，Excel将猜测在输出区域内要清除的内容。但是，可以通过自己清除它来确保更安全。

注意，从输出数据的第一行清除，而不是从标题行清除：

Range(“I7:K”& Rows.Count).Clear

小结

通过将XlFilterCopy与多个工作表、用户窗体甚至UsedRange（以确定条件区域和输出区域的界限）相结合，可以为客户或自己的数据分析需求创建一个非常好的工具。

下面的步骤提供了复杂的AdvancedFilter工具的概述：

1.将数据表放在工作表中

2.将用户可调整的条件区域放在另一工作表上，使用数据验证将标题限制为表中的标题

3.以编程方式确定条件区域表的最后一行，使用：End(xlUp)或UsedRange

4.允许用户在数据验证的限定下更改输出字段

5.在运行AdvancedFilter宏的条件页面中添加一个按钮

许多企业和组织利用Excel的数据处理功能，AdvancedFilter是获取数据概览或最小化提取重要信息的最快方法之一。

可以使用VBA的AdvancedFilter方法查找匹配字段，捕获数值数据中的间隔，并使用AND/OR条件区域表结构进行调整。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。