利用VBA 實現職工保險數據的集成與轉換

韋 偉

（黃岡職業技術學院 商學院，湖北 黃岡 438002）

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

1 功能需求分析

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

1.1 數據交叉混亂，關聯度低

目前的保險數據來源于多個部門，數據間關聯度低，數據字段和格式不統一，數據規范不一致。并且社保數據當中又會有失業保險、工傷保險等三種繳費記錄混在一起，整個數據交叉現象嚴重。

1.2 數據分散，無法分析

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

1.3 與財務要求數據不匹配

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

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

2 功能設計

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

2.1 數據導入

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

2.2 數據匯總集成

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

2.3 財務報表生成

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

2.4 數據匯總與查詢

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

2.5 數據清理

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

3 功能實現

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

3.1 數據導入功能的實現

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

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

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

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

表1 機關事業單位養老保險數據格式

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

3.2 數據集成

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

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

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

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

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

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

3.3 財務報表生成

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

（1）每人每月繳費報表

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

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

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

'計算匯總數據

（2）繳費總金額報表

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

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

3.4 數據查詢匯總

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

表4 年度保險費用匯總表

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

3.5 數據清理

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

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