Excel做《员工花名册》，这一篇就够了！

如何运用Excel树立最规范的员工花名册，如何更有效率的维护更新员工花名册，成为HR效率达人，这一篇不看不行！

《员工花名册》是每个企业中人力资源档案最的一项，管理者能够经过它了解和控制企业的现有人力资源状况，以便能抵达知人善任的管理目的，当然这是是劳动合同法的基本请求：

劳动合同法第七条 劳动关系的树立 用人单位自用工之日起即与劳动者树立劳动关系。用人单位应当树立职工名册备查。

大多数的HR从业者在进入工作单位中，接触的第一个人力资源电子档案就是《员工花名册》了，记得第一天上岗时我的主管/办公室主任（那时分都叫主任）就给我一个电子表格花名册，让我担任员工档案的录入及更新工作，就是依据纸质的《员工档案表》来提取出关键字段来然后录入到Excel中，那时的我Excel操作还不是很熟练，看到主管发我的表格中那么的“神奇”：

输入身份证号就性别就能自动判别男/女，提早提示员工华诞；

自动生成出华诞期；

自动计算年龄；

自动计算工龄；

输入入职时间后就能自动计算员工的入司时间（工龄）等等，

这种“神奇”成为我日后深化学习Excel的动力，直到往常还对Excel情有独钟。好吧，言归正传。

一.我们来讨论下《员工花名册》表格需求哪些字段（列）及部分字段类型（单元格格式）是什么：

（1）序号：便于统计人员

（2）姓名：要留意同名同姓的员工

（3）部门：员工所属部门

（4）岗位：现有工作岗位

（5）手机号码：便于联络也便于我们做通讯录，文本型

（6）出华诞期：便于中止华诞福利（鼓舞），日期型（自定义yyyy/mm/dd）

（7）最高学历：

（8）性别：

（9）年龄：

（10）身份证号：很重要，文本型

（11）参与工作时间：便于了解从业阅历和年休假计算，日期型（自定义yyyy/mm/dd）

（12） 进公司时间：便于统计工龄及员工稳定性考评，日期型（自定义yyyy/mm/dd）

（13） 合同签署时间：劳动合同法规则要件，日期型（自定义yyyy/mm/dd）

（14） 合同终止时间：劳动合同法规则要件，日期型（自定义yyyy/mm/dd）

（15） 合同期限：劳动合同法规则要件，留意试用期规则

（16） 现住址：现有能联络的住所地址

（17） 身份证住址：

（18） 婚姻状况：便于考量员工家庭状况及工作希冀

（19） 紧急联络人姓名及联络方式：很重要，突发事情及时联络

（20） 司龄：便于员工稳定性考量及工龄鼓舞

（21） 备注：

（22）......

这里的“单元格”格式就是数据在单元格中显现的格式，叫做数据类型更好分辨，有一定数据库如MySQL基础的同窗应该都很好了解，在Excel中单元格格式有下列几种，如下图：

员工花名册单元格格式类型

我们看到office中常用的格式以上几种，自定义给了我们更多定义单元格格式的空间，我们在这里需求对上述（6）（11）（12）（13）这些日期型（yyy/mm/dd）单元格格式中止操作演示：

单元格格式设置为日期型

其中的我们运用了自定义格式中的（yyyy/mm/dd）这个代表的是让他显现日期型格式并且年四位，月份两位，天两位的日期就是（1987/08/19）。其他几个日期型字段运用同样的措施中止设置。

二、基础数据的录入

这请求我们的力资源基础工作要做好，这些基础数据来源于我们纸质或电子的人力资源档案，如员工的招聘录用阶段的《求职注销表》、《员工履历注销表》、《员工异动审批表》、员工的身份证复印件、证书复印件、技艺、职称证书等资质类文件，我们一定要留意核实这些文件的真实性，在这些档案文件中提取我们要填写录用的数据。由于第一步我们曾经将字段称号（列）肯定，每一字段（列）的数据类型（单元格格式）也肯定，那么直接录入就能够了。

三、编写自动计算字段（列）的函数公式

（一）出华诞期自动生成

中心函数及解析：MID() ;Date();

MID取文本字符串中从指定位置开端的特定数目的字符

语法：MID(text, start_num, num_chars)

text 要取的目的字符串

start_num 从第几个字符开端取

num_chars 取几个

DATE 函数返回表示特定日期的连续序列号。日期函数

语法：DATE(year,month,day)

简单说就是将三个单独的值并兼并为一个日期

公式解析：

=DATE(MID(J3,7,4),MID(J3,11,2),MID(J3,13,2))

出华诞期自动获取

我们知道身份证号码中的7—14位数字代表着出华诞期，这个函数的功用首先是将身份证号中代表出华诞期的文本字符提取出来，然后在将字符转换成我们要的日期型格式。

这里给大家分享一个小技巧来调试自己公式函数，翻开数据选项卡/公式求值，能够散布调试直观又好用：

出华诞期自动生成

（二）性别自动生成

中心函数及解析：VALUE();MOD();IF();

VALUE 取将代表数字的文本格式转变成数值格式，返回值是一个数值。

MOD返回两数相除的余数 ，也叫“取模运算”“取余运算”。

MOD(number, divisor)

number：被除数

divisor：除数

IF函数允许经过测试某个条件并返回 True 或 False 的结果，从而对某个值和预期值中止逻辑比较。

=IF（内容为 True，则执行此操作，否则就执行其他操作）

因而 IF 语句有两个结果。第一个比较结果为 True，第二个比较结果为 False

公式解析：

=IF(MOD(VALUE(MID(J3,17,1)),2)=1,"男","女")

员工花名册性别自动判别

身份证的第17位代表性别，奇数为"男",偶数为"女"，我们将取得的数与2相除得到的余数，来判别性别，当然这里还能够不运用取余运算来判定数字的奇、偶性，在Excel中特地有一个函数来判别数字的就是ISODD()那么上述函数也能够这样写：

=IF(ISODD(MID(J15,17,1)),"男","女")

也能够这么写

=IF(ISEVEN(MID(J14,17,1)),"女","男")

这三种公式的结果都是一样的，大家能够自己试试。

（三）年龄自动生成

中心函数及解析：TODAY();YEAR();MONTH();IF()

TODAY:该函数没有参数，返回当前系统（当前运用的电脑）日期的序列号， 序列号是 Excel 用于日期和时间计算的日期-时间代码。

图中的1986/12/9日期格式与在常规格式中序列号为31755，可见在Excel中，日期和时间函数的运算是经过序列号运算的；

YEAR：返回对应于某个日期的年份。

MONTH:返回对应某个日期中的月份。

公式解析：

=(YEAR(TODAY())-YEAR(F3))+IF((MONTH(TODAY())-MONTH(F3))<0,-1,0)

年龄自动生成

这部分主要是日期函数的应用，上述公式中的计算精度到月份，若是依据天数来精确计算年龄，需求将公式重写，大家能够试试。

运用公式求值工具看看计算的步骤吧

（四）司龄的自动计算

中心函数及解析：DATEDIF();TODAY();

DATEDIF计算两个日期之间相隔的天数、月数或年数。

DATEDIF(start_date,end_date,unit)

start_date:要计算的起始日期；

end_date:要计算的终了日期；

unit:结果返回的类型

"Y"一段时期内的整年数。

"M"一段时期内的整月数。

"D"一段时期内的天数。

"MD"start_date 与 end_date 之间天数之差。 疏忽日期中的月份和年份。

“YM”start_date 与 end_date 之间月份之差。 疏忽日期中的天和年份

"YD"start_date 与 end_date 的日期部分之差。 疏忽日期中的年份。

便于记忆和了解，我们能够把这个函数看成datedifferent的简写，就是来计算两个日期的差，我们需求什么样的结果就要填入对应unit的值。

公式解析：

=DATEDIF(L3,TODAY(),"m")

（五）合同期限的自动计算

中心函数及解析：DATEDIF();MOD();INT();IF();

INT：将数字向下舍入到最接近的整数，简称向下取整。

其他函数都是我们引见过的了，这里就不加赘述了；我们先看看公式，为便于了解我将公式分段展示：

=IF(DATEDIF(起始日期,终了日期,"m")>=12, //第一次判别

IF(MOD(DATEDIF(起始日期,终了日期,"m"),12)=0, //'第二次判别'

INT(DATEDIF(起始日期,终了日期,"m")/12)&"年",

INT(DATEDIF(起始日期,终了日期,"m")/12)&"年"&MOD(DATEDIF(起始日期,终了日期,"m"),12)&"个月"),

DATEDIF(起始日期,终了日期,"m")&"个月")

看起来很长的样子，这个主要是IF函数的用法，在程序言语中IF....else，D0...while这个都属于流程控制，这么长的公式来了那我就简单画个流程图：

引见到这里我们基本上把花名册中主要的函数要点都分享终了了，其他字段就只是编辑填充就好了！

员工花名册建好后我们还能依据员工花名册做更多人力资源剖析工作，如人力资源结构剖析等等。好吧今天就分享到这里！

作者：HRlliang，一个会写代码的人力资源从业者，本文为作者原创，转载请注明来源，谢谢