ExcelVBA实现企业人事数据分析自动化.docx
- 文档编号:28006557
- 上传时间:2023-07-07
- 格式:DOCX
- 页数:9
- 大小:19.17KB
ExcelVBA实现企业人事数据分析自动化.docx
《ExcelVBA实现企业人事数据分析自动化.docx》由会员分享,可在线阅读,更多相关《ExcelVBA实现企业人事数据分析自动化.docx(9页珍藏版)》请在冰豆网上搜索。
ExcelVBA实现企业人事数据分析自动化
ExcelVBA实现企业人事数据分析自动化
摘要:
本文介绍了使用ExcelVBA工具进行编程实现企业人事数据分析自动化的过程,首先设计了员工学历、年龄、职称、性别结构分析功能,这四个员工单一属性结构分析可以给出数据透视表、堆积柱形图和饼图;再设计职称与学历和年龄的两个相关性分析功能。
然后给出程序的操作过程详细介绍,从导入数据、清理数据、设计用户界面到编码。
之后给出了程序的部分代码展示和效果图。
最后指出程序可改进之处是制作修改结构分析属性的用户界面,以便没有VBA基础的用户灵活修改程序。
关键词:
人数据分析ExcelVBA统计图表
中图分类号:
TP311.52文献标识码:
A文章编号:
1007-9416(2014)10-0038-03
企业的人事数据包括企业员工的劳资、人事、培训、社保、档案等大量数据信息。
随着时间的增长信息量不断积累,人事管理人员的常规事务性工作就必然包括定期的汇总统计、阶段分析。
作者在常用的Office办公软件Excel上进行VBA编程,实现企业人事数据分析自动化。
1VBA编程简介
VBA(VisualBasicforApplication)是由VisualBasic发展而来的。
对于熟悉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="员工学历结构分析"'定义新建的数据透视表图表的名称 Dimn,chart_number,chart_posAsInteger
n=Worksheets.Count'用于计算工作表的个数
chart_number=1'用于计算当前工作表中图表的个数
ForEachSheetInWorksheets
IfSheet.Name=pivot_sheet_nameThen
Worksheets(pivot_sheet_name).Delete
EndIf
Next
'删除之前的同名工作表,不能是存放数据透视表的工作表
n=Worksheets.Count
Sheets("源数据").Select
Sheets.Addafter:
=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
WithActiveSheet.PivotTables(Pivot_Tables_name).PivotFields("部门")
.Orientation=xlRowField
.Position=1
EndWith
Cells(3,1).Select
ActiveSheet.PivotTables(Pivot_Tables_name).AddDataFieldActiveSheet.
PivotTables(Pivot_Tables_name).PivotFields("学历"),"计数项:
学历",xlCount
WithActiveSheet.PivotTables(Pivot_Tables_name).PivotFields("学历")
.Orientation=xlColumnField
.Position=1
EndWith
'定义数据透视表的行标签、计数项、列标签
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
'定义数据透视表的列标签的排列顺序
ForEachSheetInWorksheets
IfSheet.Name=chart_sheet_nameThen
Worksheets(chart_sheet_name).Delete
EndIf
Next
'删除之前的同名工作表,不能是同一天同一种类型的表
Sheets.Addbefore:
=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.PasteSpecialPaste:
=xlPasteValues,Operation:
=xlNone,SkipBlanks:
=False,Transpose:
=False
'复制数据透视表到新建工作表
Worksheets(pivot_sheet_name).Delete
'删除数据透视表
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType=xlColumnStacked
ActiveChart.SetSourceDataSource:
=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.SetSourceDataSource:
=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
Endsub
5程序效果
程序的第一类自动化功能是针对四个员工属性进行整体分析和子部门分析,其中整体分析是分析对比每个部门的所有数据,使用了堆积柱形图来展示;子部门分析采用的是传统饼图分析。
第二类自动化功能是进行两个属性之间的相关性分析,使用到了Excel中的数据分析功能。
下面展示的是在员工学历结构分析中自动形成的图表,首先形成数据透视表,以部门为行标签、学历为列标签展示了各个部门的学历分布情况,如图2所示;然后选择整个数据透视表为数据区域,插入堆积柱形图,显示了所有部门的所有员工的学历人数,如图3所示;最后一部分是各部门的员工学历结构形成的饼图,期中图4是财务部的图表,其他部门的图表形式与此相同。
6程序的可改进之处
该程序对于人事管理人员的好处是:
以前要定期完成的人事数据分析工作可以一键完成了,只要把“源数据”工作表中的人员信息定期更新,就能得到所有结构分析的结果。
不同企业的人事数据报表的分析内容可能有所不同,例如增加了员工离职时间、工作年限等分析,这就需要在该程序的基础上修改形成数据透视表的行坐标、列坐标的代码,对于有ExcelVBA编程基础知识的人员来说修改起来非常方便。
本人认为该程序的可改进之处是把“源数据”导入、修改分析属性等操作制作成用户界面,这样的好处是对于不会ExcelVBA编程的用户也可以灵活使用了。
本论文受北京建筑大学教材建设项目《VBA程序设计》基金资助。
参考文献
[1]吴智.《基于ExcelVBA高校毕业设计文档快速生成系统的开发》.电脑知识与技,2013年2期.
[2]李萍.《基于ExcelVBA实现自动成绩分析的实践》.中国教育信息化?
基础教育,2014年2期.
[3]廖明梅,舒清录.《ExcelVBA在对口中职招生考试中的应用》.微型电脑应用,2014年7期.
[4]刘铮.《人事信息化加强高校人力资源管理》.计算技术与自动化,2012年3期.
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ExcelVBA 实现 企业 人事 数据 分析 自动化
![提示](https://static.bdocx.com/images/bang_tan.gif)