浅谈如何利用Excel为人力资源统计工作效力

张娟音

摘要：人力资源统计工作大多可以从人力资源管理系统直接导出统计结果，但对于跨模块或跨系统或者系统都不具备统计所需信息时，如果人工来完成，不仅大量耗费工时，且效率、质量、效果都不敢保证。对于这类复杂统计可以利用Excel的强大功能来帮助实现，下面举例说明如何利用Excel来帮助实现人力资源复杂又机械的统计要求。

关键词：Excel VBA语言 统计

对于企业来说，人力资源管理是非常重要的工作，而在人力资源管理中，最通常的应用是基于Excel进行数据统计，通过excel可以方便快捷的处理数据的收集、整合、呈报和传输，也可以保证人力资源管理工作的备案和追溯，从而促进人力资源管理工作的效率提升。

本文从一个较简单的案例入手，浅淡excel如何统计人力资源信息。

例子：将企业人员信息按照表1的格式上报，每人一表;并将人员基本情况汇成表2及生成表3人员统计表，表1电子版以“姓名+身份证”命名上报，表格的格式和内容见附录。

一、观察各表信息情况

表1所需的信息有：姓名、性别、身份证、职工证号、毕业学校、所学专业、职称/技能证、发证机构、所属信息局、工作简历、照片等。

表2的信息包含了表1的信息，但非全部信息。其中身份证、毕业学校、所学专业未包含。

表3是对表2的统计，包含了表2部分信息。

二、分析各表信息的获取途径

（一）途径一：从表1汇总成表2再生成表3 。

表1中除“所属信息局”外的其他信息都可以从人力资源管理系统中获得，而“所属信息局”的信息无法从人力资源管理系统中直接获取，需要从人员的能力装备库中去获得。如果管理系统能够以“姓名+身份证”为文件名生成所需表1，但对于成千上万人的企业，就得打开成千上万的Excel去添加“所需信息局”信息，这方法不可取。

（二）途径二：从表2生成表1和表3

如果将表1、表2、表3放在统一Excel文件，先制作好表2，再生成表1和表3。必须解决的问题：一是表2只包含表1的部分信息而非全部信息，如何获表1全部信息问题;二是表2的“所属信息局”信息是人力资源管理系统不具体的信息，该信息如何获取？三是照片批量插入问题;四是生成以“姓名+身份证”命名的文件问题。

（三）途径二的可行性分析

问题一：表2不含表1的全部信息，但表1的大多信息是人力资源管理系统具备的，可以通过在表2增加辅助列，待生成表1之后再把辅助列删除来解决。

问题二：对于表2的“所属信息局”信息是管理系统没有的，但又可以从人员能力装备库中取获取，也就是解决将信息从一个Excel引入到另一个Excel中的问题，这种引用是可行。

問题三、问题四是关联的。因为Excel有将各个Sheets生成独立文件的功能，那么我们只需将表2的人员生成以“姓名+身份证”命名的sheets就有途径生成独立的Excel文件，而从总表生成各个Sheets且统一命名sheets名是有办法的;而Excel也有批量插入各个sheets照片的功能，至此采用途径二是可行的。

三、实现过程分解

（一）从人力资源管理系统导出信息项

从人力资源管理系统导出表2+辅助列的信息项，如表4，格式和内容见附录。

表4中（13）列至（17）列是辅助列，在生成表1后删除形成表2。因为文件名是以“姓名+身份证”命名，所以表4辅助列（17）是为文件命名用的。

（二）从人员能力装备库中提取信息局信息

人员能力装备库中是姓名+装备信息，而装备信息又与信息局对应，则可以通过装备信息作为引用项，使用Vlookup（）公式将信息局的信息引入到人员能力装备库，形成“姓名+装备信息+信息局”信息，也就实现了人员与信息局的对应。将信息局通过“姓名”这个引用项，引入到表4中，这里要注意的是同姓名的情况，Vlookup（）的引用只引用第一个出现的姓名的信息，若出现同名的情况会名引用源文件第一出现姓名的信息，需人工加以修正。

自此生成表1、表2、表3所需的信息都齐全，接下来四步就可以完成，第一步生成多个Sheets，第二步以“姓名+身份证”统一命名Sheets名，第三步插入照片，第四步生成以“姓名+身份证”命名的独立文件。

（四）生成多个Sheets

将表1的打印格式设置好并设置好打印区域，然后在打印区域外，增加“序号”（G1单元格）和“姓名+身份证”（H1单元格）两项信息，通过“序号”这个引用项将表4的信息引入到表1，包括“姓名+身份证”信息。这样“序号”输入几，表2“序号”所对应的信息就会引入到表1中。然后通过“移动或者复制工作表”的功能把表1复制，将复制生成的表格的“序号”通过公式设置成前一表格的“序号”+1，这样复制的生成的表就是序号首尾相连的表格（如20个），再将这20个首尾相连的表格选中复制，则又是另20个首尾相连的表格，将第21个表1的序号设置成第20个表1的“序号”+1，则生成了40个“序号”首尾相连的表1，这样反复操作，将以2n的速度生成表1，就很快可以把表2的所有人员生成Sheets。

（五）统一命名Sheets名

使用VBA语言，对Sheets进行统一命名，VBA语言（代号A）如下：

Sub 统一命名（）

For Each Sheet In Sheets

If Trim（Sheet.Cells（1， 8）.Text ）<> ""

Then

Sheet.Name = Trim（Sheet.Cells（1， 8）.Text ）

End If

Next

End Sub

注意：因Sheets名不能为空，所以必须保证各表H1内容非空。

因为H1单元格的值是“姓名+身份证”，通过上述VBA语言的运行，各Sheets的名就变成了“姓名+身份证”的形式。

（六）各表插入照片

插入照片的VBA语言（代号B）如图1 。

代号B的VBA语言是指以C3的左边和顶端为标准插入照片，照片以B3单元格命名的jpg照片。B3单元格内容为姓名，所以照片是姓名命名的jpg照片。照片的存储路径与Excel文件所在位置一致。因表3、表4的B3单元格不一定就是姓名，所以必须有以表3、表4的B3单元格内容命名的jpg照片，不然会显示图2的错误。

所有照片插入后，删除表3、表4的照片。

（七）生成以“姓名+身份证”命名的独立文件

从上一步可以看出，各表的信息已完整齐全，具备生成以“姓名+身份证”命名的独立文件的条件，但各表内容是有公式引用的，需变成无公式无链接文本。首先，全选所有表格，按住Ctrl反选表3和表4 ;然后选择其中一个表1，选择整表内容，复制，选择性粘贴成无公式文本;第三，统一删除各个表1的G1和H1的辅助列，使用VBA生成以“姓名+身份证”命名的独立文件。

VBA语言（代号C）如下：

Private Sub 分拆工作表（）

Dim sht As Worksheet

Dim MyBook As Workbook

Set MyBook = ActiveWorkbook

For Each sht In MyBook.Sheets

sht.Copy

ActiveWorkbook.SaveAs Filename：=MyBook.Path & "＼" & sht.Name， FileFormat：=xlNormal '将工作簿另存为EXCEL默认格式

ActiveWorkbook.Close

Next

MsgBox "文件已经被分拆完毕！"

End Sub

最后删除表4（13）-（17）辅助列形成表2。

（八）统计生成表3

用counta（）公式从表2统计形成表3的总人数，用countif（）公式从表2统计形成表3信息局数量。

至此，统计任务完成。

四、本次任务的关键点

本此任务有四个关键点。一是把所需的信息在表4收集齐;二是把表1模板设置好，因它是N多个表的模板，事先设置好避免重复的操作动作;三是插入照片之前做好备份工作，因插入照片之前几乎是单个表操作，而插入照片后就是多个表操作，为保证万無一失，插入照片前做好备份，若后面的操作中有不合心意的情况，有补救办法;四是确保照片齐全。可以通过工具来确认照片是否齐全。首先，在照片所在的文件夹，新建文本文档;其次，打开创建的txt文件，输入：dir *.jpg>jpg.txt之后保存;再次，进入文件夹选项选择隐藏已知文件类型的扩展名，去掉勾选;第四，将创建的txt文件后缀名修改为bat，双击运行bat文件，就自动生成文件夹及子目录下的所有文件清单了。第五，从清单中提取姓名，用Vlookup（）公式与表4的人员姓名作匹配，就可以查出哪些人员照片缺失，补齐照片即可。

当遇到复杂的统计任务，一是先观察、再分析、寻找最简便可行的解决办法，尽量把一些细节想周全，避免尾部工作的繁琐。二是当任务完成，对此次工作过程进行复盘，将所用的工具方法和关键点进行归纳总结，汇总进入Excel工具集，以备它用或者再次启动此类工作可以轻松应对。三是实施的过程中遇到困难时，要不耻下问，百度也好，咨询Excel专家也好，寻求解决办法，下次就会有提高。这样经过几次这种大型的统计，经验将会大大增加，遇到更大的问题都容易解决。

结束语：Excel具备强大的功能，对一些复杂繁琐机械的任务有妙招，可以适当考虑。

参考文献

【1】作者：揣素荣，《浅议统计在人力资源管理工作中的应用》，2017

【2】作者：陈长伟，《Excel在人力资源管理中的应用》，2013

【3】作者：林金灿，《Excel在统计中的应用技巧》，2003

作者：樊娟玲，《信息系统在人力资源社会保障管理中的应用》，2015

广州市长岛光电机械厂