ExcelVBA实现企业人事数据分析自动化

ExcelVBA 实现企业人事数据分析自动化摘要：本文介绍了使用 ExcelVBA 工具进行编程实现 企业人事数据分析自动化的过程，首先设计了员工学历、年 龄、职称、性别结构分析功能，这四个员工单一属性结构分 析可以给出数据透视表、堆积柱形图和饼图；再设计职称与 学历和年龄的两个相关性分析功能。然后给出程序的操作过 程详细介绍， 从导入数据、 清理数据、 设计用户界面到编码。 之后给出了程序的部分代码展示和效果图。最后指出程序可 改进之处是制作修改结构分析属性的用户界面，以便没有 VBA 基础的用户灵活修改程序。关键词：人数据分析 ExcelVBA 统计图表 中图分类号： TP311.52 文献标识码： A 文章编号： 1007-9416(2014)10-0038-03企业的人事数据包括企业员工的劳资、人事、培训、社 保、档案等大量数据信息。 随着时间的增长信息量不断积累， 人事管理人员的常规事务性工作就必然包括定期的汇总统 计、阶段分析。作者在常用的 Office 办公软件 Excel 上进行 VBA 编程，实现企业人事数据分析自动化。1 VBA 编程简介VBA ( Visual Basic for Application )是由 Visual Basic 发 展而来的。对于熟悉 VB 的用户可以很快适应 VBA 面向对 象的程序设计方法。 对于没有编程基础的用户， VBA 最大的 优势在于允许用户通过宏记录器记录用户的各种操作，将其 转换为 VBA 代码完成编程工作。 VBA 可以直接应用 Office 软件的各项强大功能，程序的设计和开发更加简便、快捷。Office 软件都带有 VBA 编程的功能， 不需要单独安装编 译环境。用户编好的 VBA 程序代码在相应的编程软件中使 用。ExcelVBA就是在Excel软件中进行编程，编好的自动化 功能可以在相应的 Excel 模板中使用。2 程序功能结构图整个软件结构有六项自动化功能，分别如表1 所示。所有功能总共可以分为两类，其中前四项属于第一类自动化功 能，是针对四个单一的员工属性进行整体分析和子部门分 析；后两项属于员工属性之间的相关性分析。3 程序实现过程下面说明一下该自动化程序的实现过程， 本人是在 Excel2007 的环境下完成程序的开发过程的，其中用到的关于Excel 数据透视表、 Excel 图表的对象代码，同样适用与 Excel2003 及以后的其他版本。3.1 导入数据首先要在 Excel 模版中导入企业人事基本信息的数据， 包含企业员工的姓名、性别、出生日期、年龄、入职日期、 工作年限、学历、职称、部门、职务、工资级别等基本信息。各个企业所使用的人事信息管理软件和后台数据库都不一 定相同，但都是在这些基本信息上增加属性，而且大部分都 能支持从数据库中导出为 Excel 数据的功能。这些数据导入 到 Excel 模板的中，并把当前工作表名称修改为“源数据” 。3.2 整理数据格式在 Excel 模板的“源数据”工作表中，至少要有企业员 工的姓名、性别、出生日期、年龄、学历、职称、部门信息， 其他的员工属性可以任意增加。因为该模板中实现的自动化 程序使用到了以上的几个属性，如果用户需要再增加其他的 使用需求，就必须导入相应的属性信息。例如如果用户需要 实现员工工资级别结构分析，就必须在“源数据”工作表中 增加工资级别的属性信息。3.3 设计用户界面在 Excel 模板的第一个工作表中设计用户界面，在用户 界面中插入六个选项按钮，再分别为它们指定相应的宏代 码，如图 1 所示。用户界面的设计也可以使用按钮等其他控 件，只要指定到相应的宏代码，实现的功能都是一样的。3.4 编写人事数据分析自动化代码本 Excel 模板要编写六个宏代码，也就是六个 sub 事件 过程，都是由单击选项按钮触发事件过程，实现的功能都是 根据“源数据”工作表自动产生数据透视表和不同类型的图 标，所以六个过程的程序流程是相同的。程序流程如下：1）根据用户在用户界面选择的不同的分析类别触发不同的事件过程。（2）根据用户选择的分析类别从“源数据”中自动生 成数据透视表。 例如员工性别结构分析就需要选取 “源数据” 的性别、部门属性形成图表。（3）新建数据表用来保存自动形成的各类图表。（4）从“源数据”工作表中插入数据透视表，并把数 据透视表选择性粘贴“值”到新建的数据表中，以免“源数 据”工作表变动数据后引起数据透视表的重新计算。（5）删除原数据透视表，比便保持“源数据”工作表 的唯一性。（6）根据新建数据表的数据透视表的内容，插入各种 用户统计报表和用户统计图。4 软件程序部分代码展示 本软件通过六个选项按钮分别实现系统结构图中的六 项自动化功能，以下给出“员工学历结构分析”选项按钮所 指定的部分 VBA 代码。以下代码实现了工具“源数据”工 作表自动形成员工学历结构数据透视表、所有部门的总体结 构堆积柱形图、财务部的员工结构饼图。另外，该过程的 VBA 代码还包括其他部门的员工结构饼图， 因为代码结构与 形成财务部的员工结构饼图的代码结构相似，限于篇幅没有 展示。Sub员工学历结构分析按钮 _Click ()chart_sheet_name = Date 定义新建的展示图表的工作表 名称为当天的日期chart_sheet_name = 1.1at&chart_sheet_name 1.1at 表示 是用户界面中的员工学历分析pivot_sheet_name = pivotsheet 定义新建的数据透视表 工作表的名称Pivot_Tables_name = 员工学历结构分析 定义新建的 数据透视表图表的名称Dim n ， chart_number，chart_pos As Integern=Worksheets.Count 用于计算工作表的个数 chart_number = 1 用于计算当前工作表中图表的个数 For Each Sheet In WorksheetsIf Sheet.Name = pivot_sheet_name ThenWorksheets( pivot_sheet_name) .DeleteEnd IfNext删除之前的同名工作表， 不能是存放数据透视表的工作 表n = Worksheets.CountSheets( 源数据 ) .SelectSheets.Add after： =Worksheets( n)n = Worksheets.CountSheets（ n） .Name = pivot_sheet_name 新建工作表，用于存放根据源数据形成的数据透视表 ActiveWorkbook.PivotCaches.Create （ SourceType：=xlDatabase， SourceData： =源数据！ R1C1：R42C12 ， Version：=xlPivotTableVersion12 ） .CreatePivotTableTableDestination ： =pivotsheet ！ R3C1 ， TableName：=Pivot_Tables_name， DefaultVersion ：= xlPivotTableVersion12数据透视表定义数据来源、目标表的位置、表的名称Sheets（ pivot_sheet_name） .SelectCells（ 3， 1）.SelectActiveWorkbook.ShowPivotTableFieldList = TrueWith ActiveSheet.PivotTables（ Pivot_Tables_name） .PivotFields （ 部门 ）.Orientation = xlRowField.Position = 1End WithCells（ 3， 1）.SelectActiveSheet.PivotTables（ Pivot_Tables_name） .AddDataFieldActiveSheet.PivotTables（ Pivot_Tables_name）.PivotFields （ 学历 ），计数项：学历 xlCountWith ActiveSheet.PivotTablesPivot_Tables_name） .PivotFields （ 学历 ）.Orientation = xlColumnField.Position = 1End With定义数据透视表的行标签、计数项、列标签ActiveSheet.PivotTables（ Pivot_Tables_name）.PivotFields 学历 ） .PivotItems （ 博士 ） . Position = 1ActiveSheet.PivotTables（ Pivot_Tables_name）.PivotFields （学历）Pivotltems （硕士 ） . Position = 2ActiveSheet.PivotTables（ Pivot_Tables_name）.PivotFields （学历）Pivotltems （本科）.Position = 3ActiveSheet.PivotTables（ Pivot_Tables_name）.PivotFields （”学历 ”）Pivotltems （大专）.Position = 4ActiveSheet.PivotTables（ Pivot_Tables_name）.PivotFields （”学历 ”）Pivotltems （中专）.Position = 5ActiveSheet.PivotTables（ Pivot_Tables_name）.PivotFields （学历）Pivotltems （高中）.Position = 6定义数据透视表的列标签的排列顺序For Each Sheet ln Worksheetslf Sheet.Name = chart_sheet_name ThenWorksheets( chart_sheet_name) .DeleteEnd IfNext删除之前的同名工作表， 不能是同一天同一种类型的表Sheets.Add before: =Worksheets (源数据”)ActiveSheet.Name = chart_sheet_name新建工作表，以当日日期和分析类型代号命名Worksheets( pivot_sheet_name) .SelectActiveSheet.PivotTables( Pivot_Tables_name) .PivotSelect ， xlDataAndLabel ， TrueSelection.CopySheets( chart_sheet_name) .SelectSelection.PasteSpecial Paste: =xlPasteValues， Operation:=xlNone ， SkipBlanks : =False， Transpose: =False 复制数据透视表到新建工作表Worksheets( pivot_sheet_name) .Delete删除数据透视表ActiveSheet.Shapes.AddChart.SelectActiveChart.ChartType = xlColumnStackedActiveChart.SetSourceData Source: =Range(A2: G12)ActiveChart.PlotArea.SelectActiveChart.ApplyLayout( 2)ActiveChart.ChartTitle.Select员工学历结构分析（按部ActiveChart.ChartTitle.Text = 门） 根据统计表形成员工学历结构分析（按部门）统计图， 并移动统计图到合适位置ActiveSheet.ChartObjects （ chart_number） .ActivateSelection.Copychart_pos = 20 * chart_numberCells（ chart_pos， 1） .SelectActiveSheet.PasteActiveSheet.ChartObjects （ chart_number） .ActivateActiveChart.Parent.Deletechart_number = chart_number + 1 根据统计表形成财务部学历结构分析统计图 ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlPieActiveChart.SetSourceData Source ： =Range（ A2 ：G3）Selection.Copychart_pos = 20 * chart_numberCells（ chart_pos， 1） .SelectActiveSheet.PasteActiveSheet.ChartObjects （ chart_number） .ActivateActiveChart.Parent.Delete chart_number = chart_number + 1End sub5 程序效果 程序的第一类自动化功能是针对四个员工属性进行整 体分析和子部门分析，其中整体分析是分析对比每个部门的 所有数据，使用了堆积柱形图来展示；子部门分析采用的是 传统饼图分析。第二类自动化功能是进行两个属性之间的相 关性分析，使用到了 Excel 中的数据分析功能。下面展示的是在员工学历结构分析中自动形成的图表， 首先形成数据透视表，以部门为行标签、学历为列标签展示 了各个部门的学历分布情况，如图 2 所示；然后选择整个数 据透视表为数据区域，插入堆积柱形图，显示了所有部门的 所有员工的学历人数，如图 3 所示；最后一部分是各部门的 员工学历结构形成的饼图，期中图 4 是财务部的图表，其他 部门的图表形式与此相同。6 程序的可改进之处 该程序对于人事管理人员的好处是：以前要定期完成的 人事数据分析工作可以一键完成了，只要把“源数据”工作 表中的人员信息定期更新，就能得到所有结构分析的结果。 不同企业的人事数据报表的分析内容可能有所不同，例如增 加了员工离职时间、工作年限等分析，这就需要在该程序的 基础上修改形成数据透视表的行坐标、列坐标的代码，对于 有 ExcelVBA 编程基础知识的人员来说修改起来非常方便。 本人认为该程序的可改进之处是把“源数据”导入、修 改分析属性等操作制作成用户界面，这样的好处是对于不会 ExcelVBA 编程的用户也可以灵活使用了。本论文受北京建筑大学教材建设项目 VBA 程序设计 基金资助。参考文献1 吴智.基于 Excel VBA 高校毕业设计文档快速生成 系统的开发 .电脑知识与技， 2013 年 2 期.2 李萍 .基于 ExcelVBA 实现自动成绩分析的实践 中国教育信息化 ?基础教育， 2014 年 2 期.3 廖明梅， 舒清录 .Excel VBA 在对口中职招生考试中 的应用 .微型电脑应用， 2014 年 7 期.4 刘铮 .人事信息化加强高校人力资源管理 .计算技 术与自动化， 2012 年 3 期.