韋 偉
(黃岡職業(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ù)與財務部門所要求的順利對接。
某單位職工保險數(shù)據(jù)主要包含四個方面:職工基本醫(yī)療保險、職工養(yǎng)老保險、大病醫(yī)療和社會保險,這些數(shù)據(jù)都是從各部門業(yè)務系統(tǒng)中下載而來,在實際分析和與財務對接中,主要存在以下幾個問題。
目前的保險數(shù)據(jù)來源于多個部門,數(shù)據(jù)間關聯(lián)度低,數(shù)據(jù)字段和格式不統(tǒng)一,數(shù)據(jù)規(guī)范不一致。并且社保數(shù)據(jù)當中又會有失業(yè)保險、工傷保險等三種繳費記錄混在一起,整個數(shù)據(jù)交叉現(xiàn)象嚴重。
現(xiàn)有原始數(shù)據(jù)是根據(jù)業(yè)務類別分別存放在4 個Excel 文件中,且數(shù)據(jù)不能簡單地復制合成,需要計算匯總后才能合并為一條記錄,不利于整體的數(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ù)格式對接財務要求,以工號作為關鍵字進行整體分析,同時按照財務部門要求處理匯總后直接生成財務報表;三是要能夠按照保險項目、二級單位等進行匯總分析和綜合查詢。
根據(jù)原始設備數(shù)據(jù)特點和人事部門使用需求,社保數(shù)據(jù)的集成與轉(zhuǎn)換程序至少應包含以下功能。
按照保險類型,建立4 個工作表用以存放不同類型的保險繳費記錄,用戶可以根據(jù)需要,選擇原始數(shù)據(jù)文件后,直接將所選原始數(shù)據(jù)以追加的方式導入到既往數(shù)據(jù)后邊,同時,還需要去除重復記錄、空白記錄等冗余數(shù)據(jù),并對數(shù)據(jù)格式進行強制轉(zhuǎn)化,將文本形式存儲的數(shù)字進行強制類型轉(zhuǎn)換,以方便后期計算。
當用戶執(zhí)行數(shù)據(jù)匯總功能后,程序會以工號和月份為主鍵,對四類保險數(shù)據(jù)進行匯總,并按照每人每月一條記錄的形式進行呈現(xiàn),同時,增加二級單位、編制等基本信息。通過數(shù)據(jù)匯總后,可以將所有數(shù)據(jù)轉(zhuǎn)換集成到一個表中,作為數(shù)據(jù)分析、報表生成、綜合查詢的基礎。
財務報表生成為本程序的核心功能,就是按照財務部門的數(shù)據(jù)要求,以職工工號為關鍵字,生成機關事業(yè)單位養(yǎng)老保險、醫(yī)療保險、社會養(yǎng)老保險和大病醫(yī)療保險等保險類別的單位繳費和個人繳費情況,并進行匯總,生成財務報表。同時,程序還提供了按照月份查詢生成報表功能。
數(shù)據(jù)匯總主要用于人事部門年末對賬,在年度結(jié)束時,可以按照二級單位去匯總各部門不同編制的人數(shù)及對應的各類保險繳費小計。當然,也可以按照條件去查詢某二級部門的全年繳費情況。
數(shù)據(jù)清理主要是在新年度開始時操作,執(zhí)行該功能后,可以刪除上一年度的所有保險數(shù)據(jù),清空數(shù)據(jù)表,以便開始新一周期的導入和計算。
VBA 是一種利用Visual Basic 編寫的宏語言,通常用于擴展Office 等Windows 應用程序的功能,以實現(xiàn)功能擴充和工作自動化[1]。本次數(shù)據(jù)的集成與轉(zhuǎn)換即利用VBA 來進行數(shù)據(jù)規(guī)范化和操作自動化,并使用函數(shù)和公式進行數(shù)據(jù)的計算、匯總和引用等。
按照原始數(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 行為當頁小計和下頁的標題部分。主要代碼如下:
數(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ù),大幅縮短了計算時間。
財務報表的生成主要包含兩部分:一是具體的每個人每月各項保險繳費金額匯總報表,二是各類保險年度繳費總金額匯總報表。
(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 社會保險繳費總金額報表
為了更好地幫助人事部門分析各個二級單位的保險費用,設計了數(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)分月匯總。
數(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ù)對接,大幅度提升工作效率。