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 程序实现过程 下面说明一下该自动化程序的实现过程，本人是在Excel 2007的环境下完成程序的开发过程的，其中用到的关于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 Integer n=Worksheets.Count 用于计算工作表的个数 chart_number = 1 用于计算当前工作表中图表的个数 For Each Sheet In Worksheets If Sheet.Name = pivot_sheet_name Then Worksheets（pivot_sheet_name）.Delete End If Next 删除之前的同名工作表，不能是存放数据透视表的工作表 n = Worksheets.Count Sheets（源数据）.Select Sheets.Add after：=Worksheets（n） n = Worksheets.Count Sheets（n）.Name = pivot_sheet_name 新建工作表，用于存放根据源数据形成的数据透视表 ActiveWorkbook.PivotCaches.Create（SourceType：=xlDatabase， SourceData：= 源数据！R1C1：R42C12， Version：=xlPivotTableVersion12）.CreatePivotTable TableDestination：=pivotsheet！R3C1，TableName：=Pivot_Tables_name， DefaultVersion：= xlPivotTableVersion12 数据透视表定义数据来源、目标表的位置、表的名称 Sheets（pivot_sheet_name）.Select Cells（3， 1）.Select ActiveWorkbook.ShowPivotTableFieldList = True With ActiveSheet.PivotTables（Pivot_Tables_name）.PivotFields（部门） .Orientation = xlRowField .Position = 1 End With Cells（3， 1）.Select ActiveSheet.PivotTables（Pivot_Tables_name）.AddDataFieldActiveSheet. PivotTables（Pivot_Tables_name）.PivotFields（学历）， 计数项：学历， xlCount With ActiveSheet.PivotTables（Pivot_Tables_name）.PivotFields（学历） .Orientation = xlColumnField .Position = 1 End With 定义数据透视表的行标签、计数项、列标签 ActiveSheet.PivotTables（Pivot_Tables_name）.PivotFields（学历）.PivotItems（博士）. Position = 1 ActiveSheet.PivotTables（Pivot_Tables_name）.PivotFields（学历）.PivotItems（硕士）. Position = 2 ActiveSheet.PivotTables（Pivot_Tables_name）.PivotFields（学历）.PivotItems（本科）. Position = 3 ActiveSheet.PivotTables（Pivot_Tables_name）.PivotFields（学历）.PivotItems（大专）. Position = 4 ActiveSheet.PivotTables（Pivot_Tables_name）.PivotFields（学历）.PivotItems（中专）. Position = 5 ActiveSheet.PivotTables（Pivot_Tables_name）.PivotFields（学历）.PivotItems（高中）.Position = 6 定义数据透视表的列标签的排列顺序 For Each Sheet In Worksheets If Sheet.Name = chart_sheet_name Then Worksheets（chart_sheet_name）.Delete End If Next 删除之前的同名工作表，不能是同一天同一种类型的表 Sheets.Add before：=Worksheets（源数据） ActiveSheet.Name = chart_sheet_name 新建工作表，以当日日期和分析类型代号命名 Worksheets（pivot_sheet_name）.Select ActiveSheet.PivotTables（Pivot_Tables_name）.PivotSelect ， xlDataAndLabel， True Selection.Copy Sheets（chart_sheet_name）.Select Selection.PasteSpecial Paste：=xlPasteValues， Operation：=xlNone， SkipBlanks ：=False， Transpose：=False 复制数据透视表到新建工作表 Worksheets（pivot_sheet_name）.Delete 删除数据透视表 ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlColumnStacked ActiveChart.SetSourceData Source：=Range（A2：G12） ActiveChart.PlotArea.Select ActiveChart.ApplyLayout （2） ActiveChart.ChartTitle.Select ActiveChart.ChartTitle.Text = 员工学历结构分析（按部门） 根据统计表形成员工学历结构分析（按部门）统计图，并移动统计图到合适位置 ActiveSheet.ChartObjects（chart_number）.Activate Selection.Copy chart_pos = 20 * chart_number Cells（chart_pos， 1）.Select ActiveSheet.Paste ActiveSheet.ChartObjects（chart_number）.Activate ActiveChart.Parent.Delete chart_number = chart_number + 1 根据统计表形成财务部学历结构分析统计图 ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlPie ActiveChart.SetSourceData Source：=Range（A2：G3） Selection.Copy chart_pos = 20 * chart_number Cells（chart_pos， 1）.Select ActiveSheet.Paste ActiveSheet.ChartObjects（chart_number）.Activate ActiveChart.Parent.Delete chart_number = chart_number + 1 End sub 5 程序效果 程序的第一类自动化功能是针对四个员工属性进行整体分析和子部门分析，其中整体分析是分析对比每个部门的所有数据，使用了堆积柱形图来展示；子部门分析采用的是传统饼图分析。第二类自动化功能是进行两个属性之间的相关性分析，使用到了Excel中的数据分析功能。 下面展示的是在员工学历结构分析中自动形成的图表，首先形成数据透视表，