使用Excel VBA實現(xiàn)數(shù)據(jù)模糊檢索

楊立功，花　春（.泰州職業(yè)技術學院；.泰州市科技局，江蘇泰州5300）

?

使用Excel VBA實現(xiàn)數(shù)據(jù)模糊檢索

楊立功1，花春2
（1.泰州職業(yè)技術學院；2.泰州市科技局，江蘇泰州225300）

摘要：文章闡述了一種使用VBA擴充Excel數(shù)據(jù)檢索能力的方法，該方法可以實現(xiàn)對Excel數(shù)據(jù)表中任意單元格數(shù)據(jù)的模糊檢索。其優(yōu)點在于系統(tǒng)會根據(jù)輸入檢索關鍵字的變化，實時篩選出符合要求的數(shù)據(jù)；對于日常辦公事務中海量Excel數(shù)據(jù)的檢索需求有著重要的意義。

關鍵詞：Excel；VBA；數(shù)據(jù)檢索

Excel是微軟公司的辦公軟件Microsoft office的組件之一，它集成了非常優(yōu)秀的數(shù)據(jù)計算與分析功能，是個人電腦普及以來用途最廣泛的辦公軟件；事實上，在很多公司，Excel已經(jīng)完全成為了一種生產(chǎn)工具，在各個部門的核心工作中發(fā)揮著重要的作用[1]。

VBA (Visual Basic for Applications)是Visual Basic的一種宏語言，主要用來擴展Windows的應用程式功能，特別是Microsoft Office軟件功能的擴展。VBA在Excel中的應用，使得用戶在Excel數(shù)據(jù)處理的可操作性、靈活性以及高效性等方面又有了很大的提高。

1　問題

在數(shù)據(jù)檢索特別是海量的數(shù)據(jù)檢索方面[2]，雖然Excel提供了方便的函數(shù)，如：vlookup、lookup函數(shù)，但是存在著如下問題：（1）查詢手段僵化，只能以單元格的數(shù)據(jù)為單位進行檢索，對于需要針對單元格的部分數(shù)值進行檢索的需求，參數(shù)設置非常復雜；（2）檢索效率低下，海量數(shù)據(jù)檢索時，系統(tǒng)運行緩慢；（3）操作繁瑣，每次檢索數(shù)據(jù)都需要輸入復雜的公式，易出錯；（4）數(shù)據(jù)檢索時，檢索結果不能根據(jù)輸入關鍵詞而實時變化，實用性差。

2系統(tǒng)設計

2.1程序設計思路

針對如上問題，我們可以在Excel中編制VBA腳本程序，建立一個通用的數(shù)據(jù)檢索模塊[3]。該模塊要實現(xiàn)如下目的：（1）檢索的關鍵字可以是數(shù)據(jù)表的任意單元格的任意數(shù)據(jù)(可以是單元格的整體數(shù)值，也可以是部分數(shù)值)；（2）檢索結果隨著輸入關鍵字的變化而實時變化；（3）對于海量數(shù)據(jù)而言，檢索要保持高效率。

根據(jù)如上要求，我們可以使用VBA字典以及Find方法來設計檢索模塊。VBA字典(Dictionary)對象是微軟Windows腳本語言中的一個很有用的對象。它相當于一種聯(lián)合數(shù)組，雖然在數(shù)據(jù)存取方面，稍遜于數(shù)組；但是在隨機數(shù)據(jù)的檢索方面，數(shù)組需要對全部數(shù)據(jù)重新遍歷，而字典對象只需要通過Exists方法，指定key查找即可，數(shù)據(jù)檢索的速度與效率要大大優(yōu)于數(shù)組，特別有海量數(shù)據(jù)的檢索的需求，兩者速度差可能相差上萬倍。Find方法將在指定的單元格區(qū)域中查找包含參數(shù)指定數(shù)據(jù)的單元格，若找到符合條件的數(shù)據(jù)，則返回包含該數(shù)據(jù)的單元格；若未發(fā)現(xiàn)相匹配的數(shù)據(jù)，則返回Nothing。使用Find方法的優(yōu)點在于，該方法提供了對于單元格數(shù)據(jù)的部分匹配功能(lookat:=xlPart)，可方便實現(xiàn)數(shù)據(jù)的模糊檢索[4]。

2.2主要系統(tǒng)流程(見圖1)

2.3主要程序代碼

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

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

圖1　主要系統(tǒng)流程圖

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系統(tǒng)截圖

圖1、2與圖3、4展示的是檢索模塊對于不同單元格(“技術開發(fā)費”“承接單位”)的情況，在查詢欄中既可以輸入“40”（以“技術開發(fā)費”作為檢索關鍵字），也可以輸入“揚子”（以“承接單位”作為檢索關鍵字）。

圖1、2與圖3、4分別展示的是當輸入關鍵字變化時匹配的數(shù)據(jù)實時變化的情況，當輸入檢索的內(nèi)容發(fā)生變化時（如“技術開發(fā)費”的內(nèi)容由40變?yōu)?00時）檢索的結果實時發(fā)生變化。

圖1　查詢費用輸入“40”檢索的情況

圖2　查詢費用輸入“400”檢索的情況

圖3　查詢單位輸入“揚”檢索的情況

圖4　查詢單位輸入“揚子”檢索的情況

3　結語

該模糊數(shù)據(jù)檢索方法，在日常辦公事務實踐中取得了很好的效果，對于萬條以上的數(shù)據(jù)檢索，檢索速度小于1s；且真正實現(xiàn)了對數(shù)據(jù)的模糊檢索。可見，只要我們靈活地運用VBA提供的方法與對象，編制簡單的腳本程序就可以解決貌似復雜的問題，極大地提高工作效率。

參考文獻：

[1]伍云輝.精通Excel VBA[M].北京：電子工業(yè)出版社，2013.

[2]姚文濤.ExcelVBA應用開發(fā)經(jīng)典案例[M].北京：清華大學出版社，2009.

[3]陳軍民.Excel VBA在Office中的應用案例[J].電腦知識與技術，2013，9(12)：2819- 2821.

[4]楊群.ExcelVBA應用實戰(zhàn)技巧[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