VBA助力統(tǒng)計(jì)自動化

文/徐君超

Visual Basic for Applications（VBA）是Visual Basic的一種宏語言，主要能用來擴(kuò)展Windows的應(yīng)用程式功能，特別是Microsoft Office軟件。人們常見的辦公軟件Office軟件中的Word、Excel、Access、Powerpoint都可以利用VBA使這些軟件的應(yīng)用更高效率。例如：通過一段VBA代碼，可以實(shí)現(xiàn)畫面的切換；可以實(shí)現(xiàn)復(fù)雜邏輯的統(tǒng)計(jì)（比如從多個表中，自動生成按合同號來跟蹤生產(chǎn)量、入庫量、銷售量、庫存量的統(tǒng)計(jì)清單）等。掌握了VBA，可以發(fā)揮以下作用：

1.規(guī)范用戶的操作，控制用戶的操作行為；

2.操作界面人性化，方便用戶的操作；

3.多個步驟的手工操作通過執(zhí)行VBA代碼可以迅速的實(shí)現(xiàn)。

在人力資源統(tǒng)計(jì)工作的實(shí)際運(yùn)用中，VBA的益處有四：一是它基于Excel平臺，可以和原有Excel功能無縫結(jié)合。二是它與集成系統(tǒng)不同，可以根據(jù)操作者的需要靈活變化，很好解決各類統(tǒng)計(jì)工作口徑或表格樣式不斷變化的實(shí)際情況。三是易學(xué)易操作，不需要依賴專業(yè)的計(jì)算機(jī)編程技術(shù)人員也可以自學(xué)使用。四是其可推廣運(yùn)用潛力廣泛，不只局限于人力資源統(tǒng)計(jì)工作，也適用于財(cái)務(wù)，營銷等其他統(tǒng)計(jì)工作較多職能部門和崗位。

下面通過幾個實(shí)例來介紹VBA在人力資源實(shí)際統(tǒng)計(jì)工作中發(fā)揮的作用，迅速和自動完成一系列數(shù)據(jù)的統(tǒng)計(jì)和表格制作。

實(shí)例1.運(yùn)用VBA編輯程序加快考勤簽收表及匯總表的制作

每月需要根據(jù)職工上月加班情況發(fā)放職工輪休單，在人力資源集成系統(tǒng)中，把加班信息錄入后可以導(dǎo)出一張當(dāng)月加班情況表。表格說明：調(diào)休時間表示加班小時數(shù)，如下圖“李一”的加班天數(shù)累計(jì)為2.5天（20小時），輪休單張數(shù)為5張（因?yàn)樵撀毠ぜ影嗑前胩欤?/p>

每個月都需要根據(jù)此表中的信息制作輪休單發(fā)放的匯總和簽收表：匯總表一張。部門匯總簽收表一張，上面有各部門匯總的加班天數(shù)和輪休單張數(shù)，可以讓各部門考勤員簽收。部門個人簽收表若干張，可交由部門考勤員在發(fā)放輪休單時讓每位員工簽收，全部簽收完后交還人力資源部。如下圖所示（根據(jù)每月加班情況不同一般一共需要制作5-8張表格）

例表1：普陀煙草糖酒有限公司員工考勤月度匯總表（20141）

例表2：

例表3：

例表4：

以下省略人力資源部，營銷部（卷煙批發(fā)），綜合管理部（安全保衛(wèi)科），即3張上述三個部門的個人輪休單簽收表。

通常，可以通過常規(guī)Excel的操作制作這些表格，即使有效運(yùn)用Excel的各類匯總和計(jì)算功能加快統(tǒng)計(jì)和計(jì)算速度，也需要一步步操作，同時表頭表體等格式制作也需要時間。即使熟練操作完成這些表格的制作，至少需要30分鐘。如果運(yùn)用Excel內(nèi)的VBA功能，編輯程序，則可以每月自動完成所有表單的制作，僅需半分鐘就可完成所有表單制作。需要操作的內(nèi)容也僅是根據(jù)程序可識別的名稱重命名一下工作簿和工作表，如將工作簿重命名為stuff20141，工作表重命名為20141，然后打開寫有程序的Excel文件，點(diǎn)擊運(yùn)行程序，原本至少30分鐘的工作現(xiàn)在只需要半分鐘，并且保證準(zhǔn)確，每月重復(fù)的勞動也變得不再費(fèi)力。程序雖然很長，但是不需要完全手動輸入，可以通過錄制宏的方法獲得基礎(chǔ)代碼，只是基礎(chǔ)代碼是不含變量的，根據(jù)實(shí)際情況加入可以完成邏輯判斷和帶有變量的語句。就可以用程序代替人眼識別并完成一些邏輯判斷。這樣無論每月加班人員和加班情況如何變化，均可讓程序自動識別數(shù)據(jù)完成所有表格制作。

對于此類日常工作，使用VBA編程可以極大的加快日常工作效率和減輕工作負(fù)擔(dān)，因?yàn)樗幸韵绿攸c(diǎn)：一勞永逸，編寫完程序保存后，每月運(yùn)行即可；節(jié)省了不少時間和精力，可以投入其他工作；只需點(diǎn)擊運(yùn)行，避免長時用眼；我們知道做數(shù)據(jù)統(tǒng)計(jì)工作和報(bào)表時，最令人煩躁的是工作已經(jīng)走了一大半或者完成了，這時發(fā)生原始數(shù)據(jù)要改動或者之前的計(jì)算發(fā)生錯誤，以至于要重做或修改，重復(fù)勞動。有了VBA就不必為需要重新費(fèi)時費(fèi)力再做一遍而煩躁，因?yàn)橹恍枰冱c(diǎn)一下運(yùn)行程序，一點(diǎn)也不費(fèi)力。

實(shí)例2.運(yùn)用VBA編輯程序加快測評數(shù)據(jù)的統(tǒng)計(jì)匯總工作

每年進(jìn)行的民主測評工作，需要將大量的測評數(shù)據(jù)錄入電腦，然后再用各種數(shù)據(jù)處理功能匯總計(jì)算。但是在實(shí)際工作中遇到一個問題，一份測評卷數(shù)據(jù)放在一個工作表中，100份測評卷數(shù)據(jù)即有100張工作表，數(shù)據(jù)錄入后要將所有測評表匯總在一張表中才能統(tǒng)計(jì)計(jì)算。

運(yùn)用VBA的知識，迅速解決了將n份測評卷數(shù)據(jù)復(fù)制入一張表的問題，只需要一個很簡單的代碼就可以完成。

解決了這個問題就可以按照測評卷本身的視覺樣式錄入數(shù)據(jù)，大大節(jié)省了錄入時間。最終統(tǒng)計(jì)工作同去年相比加快了一倍多。

使用體會：對于復(fù)制粘貼，新建文件夾，等非數(shù)據(jù)統(tǒng)計(jì)工作，無法運(yùn)用函數(shù)加快效率，通常只能手動用眼判斷，用鼠標(biāo)操作。但是運(yùn)用VBA卻可以。幾十秒就可以將105張表中的數(shù)據(jù)依次貼入一張新表中，大大加快效率，同時可以避免手動操作可能產(chǎn)生的失誤。

實(shí)例3.VBA和Excel函數(shù)相結(jié)合加快薪酬臺賬、勞資月報(bào)制作。

設(shè)計(jì)此若干VBA程序的背景：

雖然有人力資源集成系統(tǒng)，但是其存在幾個問題導(dǎo)致使用起來不是很方便：一是各商業(yè)公司的薪資種類和發(fā)放習(xí)慣，和集團(tuán)并不相同，日常的薪資臺賬及報(bào)表制作主要還是依靠Excel。二是報(bào)表的結(jié)構(gòu)和計(jì)算口徑時常會根據(jù)實(shí)際情況而發(fā)生調(diào)整或變動，基于Excel自行設(shè)計(jì)的臺賬可以靈活變化且符合商業(yè)公司的現(xiàn)狀。而VBA和Excel本身具備的強(qiáng)大函數(shù)功能相輔相成，綜合運(yùn)用，可以更好地加快統(tǒng)計(jì)和報(bào)表制作效率。三是集成系統(tǒng)的信息機(jī)構(gòu)固定，結(jié)構(gòu)和格式多不可變化，所以其產(chǎn)生的數(shù)據(jù)和報(bào)表多是半成品，需要進(jìn)一步加工才能轉(zhuǎn)化為需要的報(bào)表形式。

以上各種原因造成即使有了集成系統(tǒng)，還是需要更靈活的Excel來完成符合自身需求的各類報(bào)表。

于是根據(jù)需要設(shè)計(jì)了3類程序，這三個程序前兩個是自動向臺賬錄入數(shù)據(jù)，并生成可以打印的部門匯總表。第三個是公司全員個人年度社保繳費(fèi)基數(shù)的自動計(jì)算。其中程序有些原理十分接近，只不過稍加改動就可以變成運(yùn)用在不同數(shù)據(jù)源上，完成不同的操作。

1.GZ個稅部門匯總，并錄入臺賬。

2.年金（企業(yè)）部門匯總。

3.個人社保基數(shù)計(jì)算。

三個程序分別作用：

1.可以按月對收到的個稅數(shù)據(jù)進(jìn)行處理，添加所屬部門，重新排序，按部門匯總得出部門匯總表，同時添加表頭表體，完成格式操作，同時，可以將原始數(shù)據(jù)錄入月度薪酬臺賬。

2.與程序1近似。

3.可以按選擇的月份區(qū)間匯總職工當(dāng)期薪資情況。

這里再次可以體會到VBA給數(shù)據(jù)處理統(tǒng)計(jì)工作帶來的方便之處：一是由于基于Excel平臺，生成后的數(shù)據(jù)或報(bào)表如需變動或繼續(xù)加工，也可以直接在Excel上常規(guī)操作。靈活高效。所以你設(shè)計(jì)的程序可以一次性完成所有操作，也可以僅設(shè)計(jì)部分操作。無論如何，都能加快原有工作效率。由于基于Excel平臺，可以直接與Excel函數(shù)相結(jié)合，綜合運(yùn)用。二是程序的修改也很簡單，數(shù)據(jù)源如果結(jié)構(gòu)或其他發(fā)生變化需要修改程序時，也不會很麻煩。稍作修改，又可以繼續(xù)使用。四是設(shè)計(jì)程序時結(jié)合實(shí)際考慮人員變化，部門調(diào)動等因素，在人員、部門變動后仍可使用程序完成數(shù)據(jù)處理，確保可以一勞永逸。五是數(shù)據(jù)處理的表格可以有規(guī)律的增行，添列，滿足日后統(tǒng)計(jì)任務(wù)不斷變化的需要。

接著講與Excel函數(shù)結(jié)合完成勞資月報(bào)的制作。

要完成勞資月報(bào)的自動完成，各類臺賬的科學(xué)設(shè)計(jì)是第一步。臺賬可分為個人臺賬與部門臺賬，其中部門臺賬也是從個人臺賬中取數(shù)自動完成，部門臺賬和勞資月報(bào)等制作不需要使用VBA，在科學(xué)設(shè)計(jì)臺賬的基礎(chǔ)上，靈活用Excel自帶的函數(shù)和公式替換功能就可每月輕松完成月報(bào)。

實(shí)例例4：VABA完成薪資發(fā)放生成加密包前的格式轉(zhuǎn)換

工資加密需要txt文件，且txt文件的格式有要求。而薪資制作等均用Excel文件。要將帶有薪資發(fā)放信息的Excel表格轉(zhuǎn)換成符合要求的格式。如下圖所示：

此代碼的功能是把Excel表的形式從左圖變?yōu)橛覉D，其中對發(fā)放筆數(shù)，總金額會有自動計(jì)算顯示，然后把右表復(fù)制到txt文檔中，用替換將中間表金額中的點(diǎn)去除，做一些簡單的調(diào)整即可使用。

其中VBA并沒有完成所有的格式替換工作，這也是其在實(shí)際使用中的一個可以斟酌的地方，并不是所有的操作都需要用VBA來完成，有時由于不是專業(yè)技術(shù)人員，不能運(yùn)用VBA所有的功能一步登天地完成所有操作，但是沒有關(guān)系，因?yàn)檫@些用VBA加工完成的半成品也是可以繼續(xù)手工修改的。即使需要繼續(xù)加工，至少也能簡化我們的一些工作步驟，節(jié)省時間和精力，讓工作變得稍微輕松一點(diǎn)。

通過以上幾個實(shí)際案例，應(yīng)該可以基本了解VBA如何在人力資源數(shù)據(jù)處理，統(tǒng)計(jì)，表單制作上縮短工作時間，簡化工作步驟和減少精力消耗，同時提高準(zhǔn)確性。

在設(shè)計(jì)VBA程序時的注意點(diǎn)：

第一，建立規(guī)范的原始數(shù)據(jù)處理方式，固定處理步驟，將報(bào)表制作步驟盡可能固定，并且盡量簡便。

第二，建立基礎(chǔ)數(shù)據(jù)模板，即將所有需要及發(fā)生的數(shù)據(jù)均以一定的規(guī)范格式記錄在模板中，并利用VBA和函數(shù)的組合將第一步建立的原始數(shù)據(jù)添加到模板中。

第三，用VBA建立各類表樣，并輸入公式，使到模板中取出各類數(shù)據(jù)并進(jìn)行處理。

第四，規(guī)范各類數(shù)據(jù)的存放，命名，是實(shí)現(xiàn)計(jì)算機(jī)自動識別表單和數(shù)據(jù)的前提。

本文列舉了一些操作例子，均是利用其完成與人力資源有關(guān)的一些報(bào)表制作，因?yàn)椴皇怯?jì)算機(jī)專業(yè)出身，對VBA的編程方面還是有很多不足，特別是在設(shè)計(jì)程序的簡潔上做的不夠，建議非專業(yè)人士也可以像作者一樣在VBA的程序設(shè)計(jì)上完全本著“不管黑貓白貓，抓到老鼠就是好貓”的原則，將要求放低到只要運(yùn)行程序能達(dá)到需要的統(tǒng)計(jì)和報(bào)表制作目的就算成功了。撰寫此文的目的是為了拋磚引玉，將這一思路和工作方法分享給一同奮斗在各種統(tǒng)計(jì)崗位上，淹沒在各種統(tǒng)計(jì)報(bào)表中的同仁，可以運(yùn)動此種方法從令人眼花繚亂的數(shù)據(jù)處理中解放出來。VBA和Excel結(jié)合的技術(shù)，可以根據(jù)不同使用者實(shí)際需求，憑借各人的聰明才智，應(yīng)用于更多部門的有關(guān)工作，比如財(cái)務(wù)部門的報(bào)表制作，財(cái)務(wù)部有NC集成系統(tǒng)，通過集成系統(tǒng)，可以獲得許多原始數(shù)據(jù)或半成品表格，都可通過VBA編輯程序?qū)崿F(xiàn)自動轉(zhuǎn)化為所需報(bào)表樣式，可以大幅加快工作效率。