VBA技術(shù)在數(shù)據(jù)整治中的使用

摘 要：對(duì)于所有的信息系統(tǒng)來(lái)說(shuō)，界面華麗的圖表展示，都是由底層的數(shù)據(jù)來(lái)支撐的，如果底層的數(shù)據(jù)雜亂無(wú)章，界面展示的再絢麗，對(duì)使用者來(lái)說(shuō)沒(méi)有任何的價(jià)值。所以數(shù)據(jù)的整治工作在信息系統(tǒng)中變得尤為重要。但數(shù)據(jù)整治工作工作量都比較大，如何減少數(shù)據(jù)整治工作的工作量，提升工作效率有多種方法，本文結(jié)合實(shí)際工作經(jīng)驗(yàn)，介紹使用VBA技術(shù)提升數(shù)據(jù)整治效率的方法。

關(guān)鍵字：VBA 數(shù)據(jù)整治

中圖分類號(hào)：G642 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1672-8882（2012）12-025-02

VBA（Visual Basic for Applications）是Visual Basic的一種宏語(yǔ)言，主要能用來(lái)擴(kuò)展windows的應(yīng)用程式功能，特別是Microsoft Office軟件。也就是說(shuō)是一種應(yīng)用程式視覺(jué)化的Basic Script。

筆者所在的公司曾為多家公司開展過(guò)數(shù)據(jù)整治工作。數(shù)據(jù)量從萬(wàn)級(jí)到百萬(wàn)級(jí)不等。筆者也負(fù)責(zé)過(guò)幾次數(shù)據(jù)整治，深知數(shù)據(jù)整治工作的重要性。本文將結(jié)合筆者的現(xiàn)場(chǎng)工作經(jīng)驗(yàn)，針對(duì)VBA技術(shù)在數(shù)據(jù)整治中的使用進(jìn)行詳細(xì)介紹。

數(shù)據(jù)整治過(guò)程中，最常遇到的如數(shù)據(jù)匯總、數(shù)據(jù)拆分、數(shù)據(jù)格式自動(dòng)檢查等，本文就針對(duì)這三種情況下VBA技術(shù)使用進(jìn)行闡述。

一、數(shù)據(jù)匯總

情景：下發(fā)數(shù)據(jù)收集模板進(jìn)行數(shù)據(jù)收集，用戶提交了很多的數(shù)據(jù)上來(lái)，數(shù)據(jù)分布在不同的表格內(nèi)，現(xiàn)需對(duì)所有數(shù)據(jù)進(jìn)行審核。每個(gè)表格檢查步驟都一樣，如果逐個(gè)表格檢查，工作量很大，而如果數(shù)據(jù)匯總起來(lái)統(tǒng)一檢查，這樣會(huì)大大減少檢查時(shí)間，那么下面進(jìn)行匯總數(shù)據(jù)。

問(wèn)題：手工匯總數(shù)據(jù)就是頻繁的打開文件，復(fù)制數(shù)據(jù)，粘貼數(shù)據(jù)，關(guān)閉文件。如果數(shù)據(jù)有多個(gè)表格，多個(gè)Sheet頁(yè)，有些表格提交時(shí)忘了去除篩選狀態(tài)，很容易導(dǎo)致疏漏，或重復(fù)匯總問(wèn)題。

VBA解決辦法：

在VBA中使用Dir（pathname“*.xls”），獲取指定目錄下所有的EXCEL文件清單，使用Do while遍歷每個(gè)Excel文件，遍歷文件時(shí)使用for循環(huán)遍歷所有Sheet頁(yè)。使用AutoFilterMode = False，解除表格文件的篩選?？梢允褂肬sedRange來(lái)選擇數(shù)據(jù)，也可以一行一行選擇數(shù)據(jù)，本文使用UsedRange為例，代碼示例如下：

如果匯總數(shù)據(jù)可能會(huì)超過(guò)EXCEL表格最大數(shù)據(jù)量65536（Excel2003的最大行數(shù)）的話，可以在程序中添加代碼進(jìn)行判斷，使用Worksheets.Add，新增sheet也，將后面的數(shù)據(jù)放入新的sheet中。

二、數(shù)據(jù)拆分

情景：項(xiàng)目組從系統(tǒng)中導(dǎo)出所有的數(shù)據(jù)，準(zhǔn)備下發(fā)給用戶或者項(xiàng)目組成員整改，數(shù)據(jù)需要按照一定的邏輯進(jìn)行拆分開來(lái)。那么下面進(jìn)行數(shù)據(jù)拆分。

問(wèn)題：手工拆分?jǐn)?shù)據(jù)就是頻繁的篩選數(shù)據(jù)、復(fù)制數(shù)據(jù)，新增表格，粘貼數(shù)據(jù)。如果需要拆分成很多個(gè)表格，很容易導(dǎo)致內(nèi)容或者標(biāo)題疏漏。

VBA解決辦法：

數(shù)據(jù)可以拆分至多個(gè)excel文件，也可以拆分至多個(gè)sheet頁(yè)，本文以拆分至多個(gè)sheet頁(yè)為例，示例代碼如下：

判斷sheet頁(yè)是否存在的getExsit函數(shù)代碼：

三、數(shù)據(jù)格式自動(dòng)檢查

情景：收集上來(lái)的數(shù)據(jù)，可能存在很多方面不符合要求，如：文本長(zhǎng)度，文本必填，文本唯一性，文本格式，文本有效性等，無(wú)法滿足系統(tǒng)要求，需要對(duì)數(shù)據(jù)進(jìn)行檢查，那么下面進(jìn)行數(shù)據(jù)的檢查。

問(wèn)題：手工檢查數(shù)據(jù)工作量大，需要用大量的公式進(jìn)行按列檢查。公式的操作，可能會(huì)大面積變動(dòng)原有的數(shù)據(jù)表，容易出錯(cuò)和疏漏。且手工檢查只能有少數(shù)人進(jìn)行，檢查效率較低。

VBA解決辦法：

我們可以使用UsedRange確定數(shù)據(jù)范圍，逐單元格進(jìn)行檢查。定義一些檢查的函數(shù)，當(dāng)檢查不同列時(shí)，調(diào)用不同的函數(shù)，來(lái)判斷單元格內(nèi)的值是否滿足要求。部分函數(shù)代碼示例如下：

檢查文本長(zhǎng)度：

檢查必填：

檢查唯一性：

文本格式（以檢查數(shù)值格式為例）：

文本有效性，可借助VBA的圈釋CircleInvalid功能，也可以使用定義好的Range來(lái)進(jìn)行檢查，本文中以Range來(lái)檢查：

當(dāng)確定單元格文本存在錯(cuò)誤后，如何進(jìn)行標(biāo)注錯(cuò)誤呢？VBA中可以用批注功能AddComment，也可以用單元格底色進(jìn)行標(biāo)注，本文使用單元格底色進(jìn)行標(biāo)注，這樣可以使用多種顏色來(lái)標(biāo)注不同的錯(cuò)誤類型，簡(jiǎn)明直觀：

使用VBA檢查數(shù)據(jù)還有一點(diǎn)好處，當(dāng)模板中嵌入檢查代碼后，最終收集數(shù)據(jù)的人員可以自行檢查數(shù)據(jù)問(wèn)題。這樣大大提高匯總前的數(shù)據(jù)質(zhì)量，減少最終數(shù)據(jù)檢查的工作量，可以將核心的人員從繁瑣的數(shù)據(jù)檢查的工作中釋放處理。

當(dāng)然，數(shù)據(jù)治理遇到的問(wèn)題遠(yuǎn)不止以上列舉的三個(gè)問(wèn)題，其他的問(wèn)題也同樣可以考慮使用VBA來(lái)解決。微軟給我們提供了這么實(shí)用的工具，我們要充分利用起來(lái)，讓枯燥數(shù)據(jù)治理工作變得越來(lái)越輕松。