利用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]。主要代码如下：

通过以上处理，已经能完全实现该单位多种保险数据的集成转换，并按照人事部门和财务部门的数据格式要求，进行缴费数据的汇总和财务报表的生成，完美实现多部门的数据对接，大幅度提升工作效率。