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

?

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

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

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

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

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

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

1　問題

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

2系統(tǒng)設(shè)計(jì)

2.1程序設(shè)計(jì)思路

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

根據(jù)如上要求，我們可以使用VBA字典以及Find方法來(lái)設(shè)計(jì)檢索模塊。VBA字典(Dictionary)對(duì)象是微軟Windows腳本語(yǔ)言中的一個(gè)很有用的對(duì)象。它相當(dāng)于一種聯(lián)合數(shù)組，雖然在數(shù)據(jù)存取方面，稍遜于數(shù)組；但是在隨機(jī)數(shù)據(jù)的檢索方面，數(shù)組需要對(duì)全部數(shù)據(jù)重新遍歷，而字典對(duì)象只需要通過Exists方法，指定key查找即可，數(shù)據(jù)檢索的速度與效率要大大優(yōu)于數(shù)組，特別有海量數(shù)據(jù)的檢索的需求，兩者速度差可能相差上萬(wàn)倍。Find方法將在指定的單元格區(qū)域中查找包含參數(shù)指定數(shù)據(jù)的單元格，若找到符合條件的數(shù)據(jù)，則返回包含該數(shù)據(jù)的單元格；若未發(fā)現(xiàn)相匹配的數(shù)據(jù)，則返回Nothing。使用Find方法的優(yōu)點(diǎn)在于，該方法提供了對(duì)于單元格數(shù)據(jù)的部分匹配功能(lookat:=xlPart)，可方便實(shí)現(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展示的是檢索模塊對(duì)于不同單元格(“技術(shù)開發(fā)費(fèi)”“承接單位”)的情況，在查詢欄中既可以輸入“40”（以“技術(shù)開發(fā)費(fèi)”作為檢索關(guān)鍵字），也可以輸入“揚(yáng)子”（以“承接單位”作為檢索關(guān)鍵字）。

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

圖1　查詢費(fèi)用輸入“40”檢索的情況

圖2　查詢費(fèi)用輸入“400”檢索的情況

圖3　查詢單位輸入“揚(yáng)”檢索的情況

圖4　查詢單位輸入“揚(yáng)子”檢索的情況

3　結(jié)語(yǔ)

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

參考文獻(xiàn)：

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

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

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

[4]楊群.ExcelVBA應(yīng)用實(shí)戰(zhàn)技巧[M].北京：清華大學(xué)出版社，2013.

（責(zé)任編輯楊荔晴）

(下轉(zhuǎn)第60頁(yè))

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

作者簡(jiǎn)介：楊立功(1974- )，男，江蘇泰州人，高級(jí)工程師.

文章編號(hào)：1671-0142（2015）03-0056-02

文獻(xiàn)標(biāo)志碼：A

中圖分類號(hào)：TP317.3