利用VBA 實(shí)現(xiàn)職工保險(xiǎn)數(shù)據(jù)的集成與轉(zhuǎn)換

韋 偉

（黃岡職業(yè)技術(shù)學(xué)院 商學(xué)院，湖北 黃岡 438002）

隨著信息化程度不斷提高，各部門都會(huì)建設(shè)自己專用的業(yè)務(wù)處理系統(tǒng)，以規(guī)范辦事流程、提升辦事效率，同時(shí)，日益積累的龐大業(yè)務(wù)數(shù)據(jù)也能為后期綜合分析提供有力支持。但是，各部門業(yè)務(wù)系統(tǒng)的數(shù)據(jù)在數(shù)據(jù)格式、數(shù)據(jù)規(guī)范、數(shù)據(jù)流轉(zhuǎn)等方面都相對(duì)獨(dú)立，不能很好地與其他系統(tǒng)進(jìn)行對(duì)接，此時(shí)，就需要設(shè)計(jì)數(shù)據(jù)集轉(zhuǎn)換接口，進(jìn)行系統(tǒng)間的數(shù)據(jù)匹配與對(duì)接。本文將從某事業(yè)單位實(shí)際出發(fā)，利用VBA 設(shè)計(jì)一個(gè)職工保險(xiǎn)數(shù)據(jù)的集成與轉(zhuǎn)換程序，以實(shí)現(xiàn)人事部門的保險(xiǎn)數(shù)據(jù)與財(cái)務(wù)部門所要求的順利對(duì)接。

1 功能需求分析

某單位職工保險(xiǎn)數(shù)據(jù)主要包含四個(gè)方面：職工基本醫(yī)療保險(xiǎn)、職工養(yǎng)老保險(xiǎn)、大病醫(yī)療和社會(huì)保險(xiǎn)，這些數(shù)據(jù)都是從各部門業(yè)務(wù)系統(tǒng)中下載而來(lái)，在實(shí)際分析和與財(cái)務(wù)對(duì)接中，主要存在以下幾個(gè)問(wèn)題。

1.1 數(shù)據(jù)交叉混亂，關(guān)聯(lián)度低

目前的保險(xiǎn)數(shù)據(jù)來(lái)源于多個(gè)部門，數(shù)據(jù)間關(guān)聯(lián)度低，數(shù)據(jù)字段和格式不統(tǒng)一，數(shù)據(jù)規(guī)范不一致。并且社保數(shù)據(jù)當(dāng)中又會(huì)有失業(yè)保險(xiǎn)、工傷保險(xiǎn)等三種繳費(fèi)記錄混在一起，整個(gè)數(shù)據(jù)交叉現(xiàn)象嚴(yán)重。

1.2 數(shù)據(jù)分散，無(wú)法分析

現(xiàn)有原始數(shù)據(jù)是根據(jù)業(yè)務(wù)類別分別存放在4 個(gè)Excel 文件中，且數(shù)據(jù)不能簡(jiǎn)單地復(fù)制合成，需要計(jì)算匯總后才能合并為一條記錄，不利于整體的數(shù)據(jù)分析。

1.3 與財(cái)務(wù)要求數(shù)據(jù)不匹配

原始數(shù)據(jù)是以個(gè)人身份證號(hào)為依據(jù)進(jìn)行數(shù)據(jù)記錄，而某單位財(cái)務(wù)數(shù)據(jù)是以職工工號(hào)為關(guān)鍵字進(jìn)行流轉(zhuǎn)，兩者需要進(jìn)行轉(zhuǎn)換。同時(shí)，原始數(shù)據(jù)是業(yè)務(wù)流水，因?yàn)橛醒a(bǔ)交等特殊情況的出現(xiàn)，可能會(huì)出現(xiàn)同一險(xiǎn)種每個(gè)人每個(gè)月有多條繳費(fèi)記錄，與財(cái)務(wù)要求的每人每月僅有一條數(shù)據(jù)的要求不匹配。

同時(shí)，人事部門也根據(jù)業(yè)務(wù)情況，提出了具體的功能需求：一是將所有數(shù)據(jù)匯總到一個(gè)表中，并對(duì)各月份內(nèi)每個(gè)人的相關(guān)數(shù)據(jù)進(jìn)行月內(nèi)合計(jì)；二是數(shù)據(jù)格式對(duì)接財(cái)務(wù)要求，以工號(hào)作為關(guān)鍵字進(jìn)行整體分析，同時(shí)按照財(cái)務(wù)部門要求處理匯總后直接生成財(cái)務(wù)報(bào)表；三是要能夠按照保險(xiǎn)項(xiàng)目、二級(jí)單位等進(jìn)行匯總分析和綜合查詢。

2 功能設(shè)計(jì)

根據(jù)原始設(shè)備數(shù)據(jù)特點(diǎn)和人事部門使用需求，社保數(shù)據(jù)的集成與轉(zhuǎn)換程序至少應(yīng)包含以下功能。

2.1 數(shù)據(jù)導(dǎo)入

按照保險(xiǎn)類型，建立4 個(gè)工作表用以存放不同類型的保險(xiǎn)繳費(fèi)記錄，用戶可以根據(jù)需要，選擇原始數(shù)據(jù)文件后，直接將所選原始數(shù)據(jù)以追加的方式導(dǎo)入到既往數(shù)據(jù)后邊，同時(shí)，還需要去除重復(fù)記錄、空白記錄等冗余數(shù)據(jù)，并對(duì)數(shù)據(jù)格式進(jìn)行強(qiáng)制轉(zhuǎn)化，將文本形式存儲(chǔ)的數(shù)字進(jìn)行強(qiáng)制類型轉(zhuǎn)換，以方便后期計(jì)算。

2.2 數(shù)據(jù)匯總集成

當(dāng)用戶執(zhí)行數(shù)據(jù)匯總功能后，程序會(huì)以工號(hào)和月份為主鍵，對(duì)四類保險(xiǎn)數(shù)據(jù)進(jìn)行匯總，并按照每人每月一條記錄的形式進(jìn)行呈現(xiàn)，同時(shí)，增加二級(jí)單位、編制等基本信息。通過(guò)數(shù)據(jù)匯總后，可以將所有數(shù)據(jù)轉(zhuǎn)換集成到一個(gè)表中，作為數(shù)據(jù)分析、報(bào)表生成、綜合查詢的基礎(chǔ)。

2.3 財(cái)務(wù)報(bào)表生成

財(cái)務(wù)報(bào)表生成為本程序的核心功能，就是按照財(cái)務(wù)部門的數(shù)據(jù)要求，以職工工號(hào)為關(guān)鍵字，生成機(jī)關(guān)事業(yè)單位養(yǎng)老保險(xiǎn)、醫(yī)療保險(xiǎn)、社會(huì)養(yǎng)老保險(xiǎn)和大病醫(yī)療保險(xiǎn)等保險(xiǎn)類別的單位繳費(fèi)和個(gè)人繳費(fèi)情況，并進(jìn)行匯總，生成財(cái)務(wù)報(bào)表。同時(shí)，程序還提供了按照月份查詢生成報(bào)表功能。

2.4 數(shù)據(jù)匯總與查詢

數(shù)據(jù)匯總主要用于人事部門年末對(duì)賬，在年度結(jié)束時(shí)，可以按照二級(jí)單位去匯總各部門不同編制的人數(shù)及對(duì)應(yīng)的各類保險(xiǎn)繳費(fèi)小計(jì)。當(dāng)然，也可以按照條件去查詢某二級(jí)部門的全年繳費(fèi)情況。

2.5 數(shù)據(jù)清理

數(shù)據(jù)清理主要是在新年度開始時(shí)操作，執(zhí)行該功能后，可以刪除上一年度的所有保險(xiǎn)數(shù)據(jù)，清空數(shù)據(jù)表，以便開始新一周期的導(dǎo)入和計(jì)算。

3 功能實(shí)現(xiàn)

VBA 是一種利用Visual Basic 編寫的宏語(yǔ)言，通常用于擴(kuò)展Office 等Windows 應(yīng)用程序的功能，以實(shí)現(xiàn)功能擴(kuò)充和工作自動(dòng)化[1]。本次數(shù)據(jù)的集成與轉(zhuǎn)換即利用VBA 來(lái)進(jìn)行數(shù)據(jù)規(guī)范化和操作自動(dòng)化，并使用函數(shù)和公式進(jìn)行數(shù)據(jù)的計(jì)算、匯總和引用等。

3.1 數(shù)據(jù)導(dǎo)入功能的實(shí)現(xiàn)

按照原始數(shù)據(jù)內(nèi)容新建4 個(gè)工作表，制作和原始表格一樣的列標(biāo)題，用以分別放置不同的保險(xiǎn)數(shù)據(jù)。然后，利用Application 對(duì)象的FileDialog 屬性實(shí)現(xiàn)用戶自主選取原始數(shù)據(jù)文件，并讀取所有行追加到導(dǎo)入后的工作表中，主要代碼如下：

此過(guò)程中，有兩種特殊情況需要進(jìn)一步處理：

（1）原始數(shù)據(jù)中有部分?jǐn)?shù)據(jù)應(yīng)該是數(shù)值型，但是存放單元格卻是文本型，不能進(jìn)行后期的計(jì)算，因此，需要強(qiáng)制將其轉(zhuǎn)換為數(shù)值型，具體代碼是：

（2）機(jī)關(guān)事業(yè)養(yǎng)老保數(shù)據(jù)在導(dǎo)出時(shí)，是分頁(yè)存放的，每15 條數(shù)據(jù)為一頁(yè)并做小計(jì)，且每頁(yè)都有數(shù)據(jù)標(biāo)題（如表1 所示），如果單純的復(fù)制，數(shù)據(jù)凌亂且不能計(jì)算。

表1 機(jī)關(guān)事業(yè)單位養(yǎng)老保險(xiǎn)數(shù)據(jù)格式

因此，該表導(dǎo)入時(shí)不能簡(jiǎn)單復(fù)制，需要循環(huán)處理，每次只追加15 條數(shù)據(jù)，且在追加結(jié)束后需跳過(guò)5 行數(shù)據(jù)，此5 行為當(dāng)頁(yè)小計(jì)和下頁(yè)的標(biāo)題部分。主要代碼如下：

3.2 數(shù)據(jù)集成

數(shù)據(jù)集成就需要將導(dǎo)入的4 個(gè)工作表數(shù)據(jù)通過(guò)處理后，放入到一個(gè)匯總表中，每一行存放一個(gè)職工某月的四類保險(xiǎn)數(shù)據(jù)。

按要求做好匯總表表頭后，首先需要確定的就是人員名單，為了減少數(shù)據(jù)冗余，提高數(shù)據(jù)的準(zhǔn)確性，本次不以學(xué)校的職工信息表為基準(zhǔn)進(jìn)行，而是建立一個(gè)中間頁(yè)面--繳費(fèi)名單，并讀取導(dǎo)入的4 個(gè)保險(xiǎn)數(shù)據(jù)中將身份證號(hào)和姓名放在一起，然后進(jìn)行去重操作，即可得到最準(zhǔn)確的繳費(fèi)名單。主要代碼如下：

具體數(shù)據(jù)集成功能的實(shí)現(xiàn)主要利用函數(shù)和公式即可，使用引用函數(shù)可以直接從中間表“繳費(fèi)名單”中讀取所有繳費(fèi)人員的姓名和身份證號(hào)，然后利用Vlookup 查找函數(shù)，根據(jù)身份證號(hào)去職工信息表中查詢對(duì)應(yīng)的編制、所屬部門、工號(hào)等信息填入。各類保險(xiǎn)數(shù)據(jù)的計(jì)算需要使用sumifs 函數(shù)，按照身份證號(hào)進(jìn)行匯總后填入到對(duì)應(yīng)單元格，具體函數(shù)示例如下：

其中的核定單號(hào)和人員編號(hào)等可能在身份證列的前邊，不能使用vlookup 直接查找，我們就利用index 和match 函數(shù)結(jié)合進(jìn)行查找填入[2]，具體函數(shù)示例如下：

通過(guò)以上處理后，已經(jīng)可以實(shí)現(xiàn)數(shù)據(jù)的匯總集成，但是，隨著繳費(fèi)月份的增多，數(shù)據(jù)量也越來(lái)越大，過(guò)多的查找操作會(huì)讓運(yùn)行時(shí)間大幅增加。為了解決這一問(wèn)題，可以將該Excel 文件的自動(dòng)計(jì)算功能關(guān)閉，采用手動(dòng)控制計(jì)算。我們可以先讀取匯總表中現(xiàn)有已處理好的數(shù)據(jù)記錄量，放入變量old_len；讀取最新追加數(shù)據(jù)的記錄個(gè)數(shù)，存放到變量new_len 中[3]。從而能確定匯總表中新增區(qū)域，并進(jìn)行手動(dòng)重算，主要代碼如下：

通過(guò)以上處理后，每次匯總只需計(jì)算新追加的數(shù)據(jù)，大幅縮短了計(jì)算時(shí)間。

3.3 財(cái)務(wù)報(bào)表生成

財(cái)務(wù)報(bào)表的生成主要包含兩部分：一是具體的每個(gè)人每月各項(xiàng)保險(xiǎn)繳費(fèi)金額匯總報(bào)表，二是各類保險(xiǎn)年度繳費(fèi)總金額匯總報(bào)表。

（1）每人每月繳費(fèi)報(bào)表

首先我們需要新建工作表，并設(shè)計(jì)報(bào)表樣式，以社會(huì)保險(xiǎn)為例，制作如表2 所示的工作表。

表2 每人每月設(shè)備保險(xiǎn)繳費(fèi)報(bào)表

數(shù)據(jù)填寫最直接的辦法，就是利用查找函數(shù)填寫基本信息，使用sumifs 函數(shù)匯總各類繳費(fèi)金額，但是運(yùn)行效率低下，資源的耗費(fèi)較大，為了解決這一問(wèn)題，需要最大限度地減少查找量和計(jì)算量。建立一個(gè)中間頁(yè)，利用高級(jí)篩選在匯總表中篩選出對(duì)應(yīng)月份的所有數(shù)據(jù)，然后將各項(xiàng)數(shù)據(jù)引用至此。特別需要注意的是，報(bào)表要求在人員繳費(fèi)明細(xì)羅列之后，對(duì)各項(xiàng)繳費(fèi)金額進(jìn)行合計(jì)，考慮到繳費(fèi)人員可能會(huì)變動(dòng)，需采用合計(jì)位置動(dòng)態(tài)放置的方式處理。利用公式計(jì)算出第一行數(shù)據(jù)后，使用VBA 將所有人員數(shù)據(jù)向下填充，并將合計(jì)行后移，在填充結(jié)束后，進(jìn)行合計(jì)計(jì)算并設(shè)置格式。主要代碼如下：

'計(jì)算匯總數(shù)據(jù)

（2）繳費(fèi)總金額報(bào)表

繳費(fèi)總金額報(bào)表是要上交給財(cái)務(wù)的報(bào)表之一，主要是匯總不同類別人員的職工數(shù)、單位繳費(fèi)金額和個(gè)人繳費(fèi)金額，可以先按照保險(xiǎn)類別制作如表3 所示的繳費(fèi)情況表，然后使用countifs函數(shù)和sumifs函數(shù)，按照類別和繳費(fèi)月份進(jìn)行匯總即可。

表3 社會(huì)保險(xiǎn)繳費(fèi)總金額報(bào)表

3.4 數(shù)據(jù)查詢匯總

為了更好地幫助人事部門分析各個(gè)二級(jí)單位的保險(xiǎn)費(fèi)用，設(shè)計(jì)了數(shù)據(jù)查詢匯總功能，首先制作如表4 所示的年度保險(xiǎn)費(fèi)用匯總表，并利用數(shù)據(jù)驗(yàn)證功能制作所有單位的下拉列表，然后再使用countifs 函數(shù)統(tǒng)計(jì)兩類人員的人數(shù)，利用sumifs 函數(shù)按照二級(jí)單位名稱和人員性質(zhì)進(jìn)行數(shù)據(jù)匯總。

表4 年度保險(xiǎn)費(fèi)用匯總表

本次查詢匯總是對(duì)全年度數(shù)據(jù)進(jìn)行，因此沒(méi)有進(jìn)行繳費(fèi)月份的判定。后期可以進(jìn)一步改進(jìn)，在查詢條件中增加月份選項(xiàng)，sumifs 函數(shù)按照二級(jí)單位名稱、人員性質(zhì)和繳費(fèi)月份進(jìn)行數(shù)據(jù)匯總，即可實(shí)現(xiàn)分月匯總。

3.5 數(shù)據(jù)清理

數(shù)據(jù)清理主要是在新年度開始時(shí)，將所有數(shù)據(jù)清空，以便開始新一年的數(shù)據(jù)導(dǎo)入與分析。對(duì)此可以利用 Range 屬性去選擇區(qū)域，然后使用選區(qū)的ClearContents 屬性清空單元格內(nèi)容。由于匯總表中存放了大量的公式，不能直接清空內(nèi)容，因此，在清空基礎(chǔ)信息時(shí)，需要先利用變量new_len 記錄現(xiàn)有數(shù)據(jù)量，使用重新計(jì)算匯總表的方式清空匯總表[4]。主要代碼如下：

通過(guò)以上處理，已經(jīng)能完全實(shí)現(xiàn)該單位多種保險(xiǎn)數(shù)據(jù)的集成轉(zhuǎn)換，并按照人事部門和財(cái)務(wù)部門的數(shù)據(jù)格式要求，進(jìn)行繳費(fèi)數(shù)據(jù)的匯總和財(cái)務(wù)報(bào)表的生成，完美實(shí)現(xiàn)多部門的數(shù)據(jù)對(duì)接，大幅度提升工作效率。