使用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