韋 偉

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

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

1 功能需求分析

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

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

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

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

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

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

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

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

2 功能設計

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

2.1 數(shù)據(jù)導入

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

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

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

2.3 財務報表生成

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

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

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

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

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

3 功能實現(xiàn)

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

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

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

此過程中，有兩種特殊情況需要進一步處理：

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

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

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

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

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

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

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

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

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

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

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

3.3 財務報表生成

財務報表的生成主要包含兩部分：一是具體的每個人每月各項保險繳費金額匯總報表，二是各類保險年度繳費總金額匯總報表。

（1）每人每月繳費報表

首先我們需要新建工作表，并設計報表樣式，以社會保險為例，制作如表2 所示的工作表。

表2 每人每月設備保險繳費報表

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

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

（2）繳費總金額報表

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

表3 社會保險繳費總金額報表

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

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

表4 年度保險費用匯總表

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

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

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

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