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

1、使用Excel VBA实现数据模糊检索杨立功;花春【摘要】文章阐述了一种使用VBA扩充Excel数据检索能力的方法，该方法可以实 现对Excel数据表中任意单元格数据的模糊检索其优点在于系统会根据输入检索 关键字的变化，实时筛选出符合要求的数据;对于日常办公事务中海量Excel数据的 检索需求有着重要的意义.期刊名称】泰州职业技术学院学报年(卷),期】2015(015)003【总页数】3页(P56-57,60)【关键词】Excel;VBA;数据检索【作者】杨立功;花春【作者单位】泰州职业技术学院;泰州市科技局,江苏泰州225300【正文语种】中 文【中图分类】TP317.3Excel是微软公司

2、的办公软件Microsoft office的组件之一，它集成了非常优秀的 数据计算与分析功能，是个人电脑普及以来用途最广泛的办公软件；事实上，在很 多公司，Excel已经完全成为了一种生产工具，在各个部门的核心工作中发挥着重 要的作用1。VBA (Visual Basic for Applications)是 Visual Basic 的一种宏语言，主要用来扩 展Windows的应用程式功能，特别是Microsoft Office软件功能的扩展。VBA 在 Excel 中的应用，使得用户在 Excel 数据处理的可操作性、灵活性以及高效性等 方面又有了很大的提高。在数据检索特别是海量的数据检索

3、方面2，虽然 Excel 提供了方便的函数，如： vlookup、lookup 函数，但是存在着如下问题： （1）查询手段僵化，只能以 单元格的数据为单位进行检索，对于需要针对单元格的部分数值进行检索的需求， 参数设置非常复杂；（ 2）检索效率低下，海量数据检索时，系统运行缓慢；（ 3） 操作繁琐，每次检索数据都需要输入复杂的公式，易出错；（4）数据检索时，检 索结果不能根据输入关键词而实时变化，实用性差。2.1 程序设计思路 针对如上问题，我们可以在 Excel 中编制 VBA 脚本程序，建立一个通用的数据检 索模块3。该模块要实现如下目的：（1）检索的关键字可以是数据表的任意单元 格的任意

4、数据（可以是单元格的整体数值，也可以是部分数值）；（2）检索结果随 着输入关键字的变化而实时变化；（3）对于海量数据而言，检索要保持高效率。 根据如上要求，我们可以使用VBA字典以及Find方法来设计检索模块。VBA字 典（Dictionary）对象是微软Windows脚本语言中的一个很有用的对象。它相当于 一种联合数组，虽然在数据存取方面，稍逊于数组；但是在随机数据的检索方面， 数组需要对全部数据重新遍历，而字典对象只需要通过 Exists 方法，指定 key 查 找即可，数据检索的速度与效率要大大优于数组，特别有海量数据的检索的需求， 两者速度差可能相差上万倍。Find方法将在指定的单元格

5、区域中查找包含参数指 定数据的单元格，若找到符合条件的数据，则返回包含该数据的单元格；若未发现 相匹配的数据，则返回Nothing。使用Find方法的优点在于，该方法提供了对于 单元格数据的部分匹配功能（lookat:=xlPart），可方便实现数据的模糊检索4。主要系统流程（见图 1）主要程序代码Dim i As Integer, ii As Integer, tmp As RangeDim rng As Range:rng = Sht1.Range(“A2:K”& rw)Dim dic As Object:dic = CreateObject (“scripting. dictionary

6、”) Dim whatAs 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 E

7、nd If Next ii Next i Set dic = Nothing 2.4系统截图 图1、2与图3、4展示的是检索模块对于不同单元格(“技术开发费”“承接单 位”)的情况，在查询栏中既可以输入“40”(以“技术开发费”作为检索关键 字)，也可以输入 “扬子”(以“承接单位”作为检索关键字)。图 1、2 与图 3、4 分别展示的是当输入关键字变化时匹配的数据实时变化的情况， 当输入检索的内容发生变化时(如“技术开发费”的内容由40变为400时)检索 的结果实时发生变化。该模糊数据检索方法，在日常办公事务实践中取得了很好的效果，对于万条以上的 数据检索，检索速度小于1s ；且真正实现了对数据的模糊检索。可见，只要我们 灵活地运用VBA提供的方法与对象，编制简单的脚本程序就可以解决貌似复杂的 问题，极大地提高工作效率。相关文献】伍云辉精通Excel VBAM.北京：电子工业出版社，2013.姚文涛.ExcelVBA应用开发经典案例M