VBA助力統計自動化

文/徐君超

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

1.規范用戶的操作，控制用戶的操作行為；

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

3.多個步驟的手工操作通過執行VBA代碼可以迅速的實現。

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

下面通過幾個實例來介紹VBA在人力資源實際統計工作中發揮的作用，迅速和自動完成一系列數據的統計和表格制作。

實例1.運用VBA編輯程序加快考勤簽收表及匯總表的制作

每月需要根據職工上月加班情況發放職工輪休單，在人力資源集成系統中，把加班信息錄入后可以導出一張當月加班情況表。表格說明：調休時間表示加班小時數，如下圖“李一”的加班天數累計為2.5天（20小時），輪休單張數為5張（因為該職工加班均是半天）

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

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

例表2：

例表3：

例表4：

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

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

對于此類日常工作，使用VBA編程可以極大的加快日常工作效率和減輕工作負擔，因為它有以下特點：一勞永逸，編寫完程序保存后，每月運行即可；節省了不少時間和精力，可以投入其他工作；只需點擊運行，避免長時用眼；我們知道做數據統計工作和報表時，最令人煩躁的是工作已經走了一大半或者完成了，這時發生原始數據要改動或者之前的計算發生錯誤，以至于要重做或修改，重復勞動。有了VBA就不必為需要重新費時費力再做一遍而煩躁，因為只需要再點一下運行程序，一點也不費力。

實例2.運用VBA編輯程序加快測評數據的統計匯總工作

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

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

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

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

實例3.VBA和Excel函數相結合加快薪酬臺賬、勞資月報制作。

設計此若干VBA程序的背景：

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

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

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

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

2.年金（企業）部門匯總。

3.個人社保基數計算。

三個程序分別作用：

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

2.與程序1近似。

3.可以按選擇的月份區間匯總職工當期薪資情況。

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

接著講與Excel函數結合完成勞資月報的制作。

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

實例例4：VABA完成薪資發放生成加密包前的格式轉換

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

此代碼的功能是把Excel表的形式從左圖變為右圖，其中對發放筆數，總金額會有自動計算顯示，然后把右表復制到txt文檔中，用替換將中間表金額中的點去除，做一些簡單的調整即可使用。

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

通過以上幾個實際案例，應該可以基本了解VBA如何在人力資源數據處理，統計，表單制作上縮短工作時間，簡化工作步驟和減少精力消耗，同時提高準確性。

在設計VBA程序時的注意點：

第一，建立規范的原始數據處理方式，固定處理步驟，將報表制作步驟盡可能固定，并且盡量簡便。

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

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

第四，規范各類數據的存放，命名，是實現計算機自動識別表單和數據的前提。

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