经典代码解析5：查找并显示部分匹配的数据

L罗乐 2017-08-03

展开全文

引子：本文中的代码来源于www.excelhero.com，文中稍作修改，演示了如何高效地从大量的数据中查找与想要的内容部分匹配的数据。本文提供的方法比《经典代码解析2：查找并获取所查找内容的全部单元格》中的代码更简洁更快速。我觉得可以作为一个模板，在类似的情形中使用。

目的

在大量的数据中，快速查找与查找值部分匹配的所有数据。

示例工作簿与程序代码

如下图所示，工作簿包含两个工作表，一个工作表Data用作数据库，包含大量的数据；另一个工作表Search用来从数据库工作表Data中查找想要的数据并显示。

方法1：使用Range对象的AdvancedFilter方法

在工作表中定义名称

在Search工作表中定义的名称如下：

C3单元格名称：search_string

C8单元格名称：result_target

在Data工作表中将单元格区域z1:z2定义的名称为：filter_criteria

在“定义名称”对话框中直接定义的名称（隐藏的名称）：

Database=Data!$B$1:INDEX(Data!$1:$1048576,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

result=Search!$C$8:INDEX(Search!$C$8:$XFD$1048576,COUNTA(Search!$C$8:$C$1048576),COUNTA(Search!$C$8:$XFD$1048576))

在Search工作表单元格C5中，使用下面的公式统计找到的记录数：

=SUMPRODUCT( --(C9:C1048576>''))

设置工作表Search

设置Search工作表单元格C3的格式，在“单元格格式”的“保护”选项卡中取消“锁定”复选，以便于在“保护工作表”时仍能编辑该单元格。

保护工作表Search。

编写代码

在Search工作表对象模块中的代码为：

说明：

方法2：使用ADO

在工作表中定义名称

除以下变化外，工作表中名称与方法1相同。

C9单元格名称：result_target

在“定义名称”对话框中直接定义的名称（隐藏的名称）：

Database=Data!$A$2:INDEX(Data!$2:$1048576,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

result=Search!$C$9:INDEX(Search!$C$9:$XFD$1048576,COUNTA(Search!$C$9:$C$1048576),COUNTA(Search!$C$8:$XFD$1048576))

在Search工作表单元格C3中，使用下面的公式统计找到的记录数：

=SUMPRODUCT((C9:C65536>'') * 1 )

设置工作表Search

除以下变化外，工作表设置与方法1相同。

与方法1相比，本方法增加了一个复选框，其目的是为了避免内存泄漏而导致计算机崩溃。其工作表界面如下图所示。

编写代码

在Search工作表对象模块中的代码为：

说明：

将Excel工作簿作为数据库，使用ADO结合数据库查询语句获取结果。
根据是否选取复选框，来确定是否创建工作簿副本，即是否在工作簿副本中查询数据。
基于Excel单元格区域进址进行查询时，使用的是“[工作表名 $ 单元格区域地址]”的格式。因此，语句[database].Parent.Name & '$' &Replace([database].Address, '$', vbNullString)将单元格地址转换成正确的查询格式。
在SQL查询语句中，使用[F2]、[F3]、[F4]来表示第2列、第3列、第4列。（第一次见到这种用法，还没有查到相应的详细资料。有熟悉的朋友能够详细介绍一下吗？）
有关ADO的详细介绍参见《经典代码解析4：使用ADO整合Excel与Access》。