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

文/徐君超

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

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

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

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

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

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

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

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

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

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

例表2：

例表3：

例表4：

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

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

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

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

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

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

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

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

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

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

雖然有人力資源集成系統(tǒng)，但是其存在幾個(gè)問(wèn)題導(dǎo)致使用起來(lái)不是很方便：一是各商業(yè)公司的薪資種類和發(fā)放習(xí)慣，和集團(tuán)并不相同，日常的薪資臺(tái)賬及報(bào)表制作主要還是依靠Excel。二是報(bào)表的結(jié)構(gòu)和計(jì)算口徑時(shí)常會(huì)根據(jù)實(shí)際情況而發(fā)生調(diào)整或變動(dòng)，基于Excel自行設(shè)計(jì)的臺(tái)賬可以靈活變化且符合商業(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來(lái)完成符合自身需求的各類報(bào)表。

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

1.GZ個(gè)稅部門匯總，并錄入臺(tái)賬。

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

3.個(gè)人社?；鶖?shù)計(jì)算。

三個(gè)程序分別作用：

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

2.與程序1近似。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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