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

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

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

 

输入身份证号就性别就能自动判断男/女，提前提醒员工生日；

自动生成出生日期；

自动计算年龄；

自动计算工龄；

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

这种“神奇”成为我日后深入学习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这个都属于流程控制，这么长的公式来了那我就简单画个流程图：

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

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