使用Excel VBA实现数据模糊检索

杨立功，花　春（.泰州职业技术学院；.泰州市科技局，江苏泰州5300）



使用Excel VBA实现数据模糊检索

杨立功1，花春2
（1.泰州职业技术学院；2.泰州市科技局，江苏泰州225300）

摘要：文章阐述了一种使用VBA扩充Excel数据检索能力的方法，该方法可以实现对Excel数据表中任意单元格数据的模糊检索。其优点在于系统会根据输入检索关键字的变化，实时筛选出符合要求的数据；对于日常办公事务中海量Excel数据的检索需求有着重要的意义。

关键词：Excel；VBA；数据检索

Excel是微软公司的办公软件Microsoft office的组件之一，它集成了非常优秀的数据计算与分析功能，是个人电脑普及以来用途最广泛的办公软件；事实上，在很多公司，Excel已经完全成为了一种生产工具，在各个部门的核心工作中发挥着重要的作用[1]。

VBA (Visual Basic for Applications)是Visual Basic的一种宏语言，主要用来扩展Windows的应用程式功能，特别是Microsoft Office软件功能的扩展。VBA在Excel中的应用，使得用户在Excel数据处理的可操作性、灵活性以及高效性等方面又有了很大的提高。

1　问题

在数据检索特别是海量的数据检索方面[2]，虽然Excel提供了方便的函数，如：vlookup、lookup函数，但是存在着如下问题：（1）查询手段僵化，只能以单元格的数据为单位进行检索，对于需要针对单元格的部分数值进行检索的需求，参数设置非常复杂；（2）检索效率低下，海量数据检索时，系统运行缓慢；（3）操作繁琐，每次检索数据都需要输入复杂的公式，易出错；（4）数据检索时，检索结果不能根据输入关键词而实时变化，实用性差。

2系统设计

2.1程序设计思路

针对如上问题，我们可以在Excel中编制VBA脚本程序，建立一个通用的数据检索模块[3]。该模块要实现如下目的：（1）检索的关键字可以是数据表的任意单元格的任意数据(可以是单元格的整体数值，也可以是部分数值)；（2）检索结果随着输入关键字的变化而实时变化；（3）对于海量数据而言，检索要保持高效率。

根据如上要求，我们可以使用VBA字典以及Find方法来设计检索模块。VBA字典(Dictionary)对象是微软Windows脚本语言中的一个很有用的对象。它相当于一种联合数组，虽然在数据存取方面，稍逊于数组；但是在随机数据的检索方面，数组需要对全部数据重新遍历，而字典对象只需要通过Exists方法，指定key查找即可，数据检索的速度与效率要大大优于数组，特别有海量数据的检索的需求，两者速度差可能相差上万倍。Find方法将在指定的单元格区域中查找包含参数指定数据的单元格，若找到符合条件的数据，则返回包含该数据的单元格；若未发现相匹配的数据，则返回Nothing。使用Find方法的优点在于，该方法提供了对于单元格数据的部分匹配功能(lookat:=xlPart)，可方便实现数据的模糊检索[4]。

2.2主要系统流程(见图1)

2.3主要程序代码

Dim i As Integer, ii As Integer, tmp As Range

Dim rng As Range:rng = Sht1.Range(“A2:K”& rw)

图1　主要系统流程图

Dim dic As Object:dic = CreateObject (“scripting. dictionary”) Dim what As String:what = TextBox1.Value Dim rw As String:rw = Sht1.Range(“A65536”).End (xlUp).Row For i = 2 To rw For ii = 1 To 11 Set tmp = Sht1.Cells (i，ii).Find (what, lookat:=xlPart, MatchCase:=False) If Not tmp Is Nothing Then If Not dic.Exists(tmp.Row) Then dic.Add tmp.Row, "" Call tb End If End If Next ii Next i Set dic = Nothing

2.4系统截图

图1、2与图3、4展示的是检索模块对于不同单元格(“技术开发费”“承接单位”)的情况，在查询栏中既可以输入“40”（以“技术开发费”作为检索关键字），也可以输入“扬子”（以“承接单位”作为检索关键字）。

图1、2与图3、4分别展示的是当输入关键字变化时匹配的数据实时变化的情况，当输入检索的内容发生变化时（如“技术开发费”的内容由40变为400时）检索的结果实时发生变化。

图1　查询费用输入“40”检索的情况

图2　查询费用输入“400”检索的情况

图3　查询单位输入“扬”检索的情况

图4　查询单位输入“扬子”检索的情况

3　结语

该模糊数据检索方法，在日常办公事务实践中取得了很好的效果，对于万条以上的数据检索，检索速度小于1s；且真正实现了对数据的模糊检索。可见，只要我们灵活地运用VBA提供的方法与对象，编制简单的脚本程序就可以解决貌似复杂的问题，极大地提高工作效率。

参考文献：

[1]伍云辉.精通Excel VBA[M].北京：电子工业出版社，2013.

[2]姚文涛.ExcelVBA应用开发经典案例[M].北京：清华大学出版社，2009.

[3]陈军民.Excel VBA在Office中的应用案例[J].电脑知识与技术，2013，9(12)：2819- 2821.

[4]杨群.ExcelVBA应用实战技巧[M].北京：清华大学出版社，2013.

（责任编辑杨荔晴）

(下转第60页)

1

, HUA Chun

2

(1.Taizhou Polytechnic College；2.Taizhou Technology Bureau,Taizhou Jiangsu 225300,China)

Abstract：This article describes a method of using VBA to expand Excel data query ability, the method can realize the fuzzy query of data in any cell of Excel table. The advantage of this method is that system changes in real time according to the input keywords; this method has been widely used in daily office affairs.

Key words：Excel; VBA; data query

作者简介：杨立功(1974- )，男，江苏泰州人，高级工程师.

文章编号：1671-0142（2015）03-0056-02

文献标志码：A

中图分类号：TP317.3