如何高效制作花名册？这款员工档案管理软件送给你 - 薪人薪事

如何快速制作花名册？是否可以通过员工档案管理软件来高效进行员工管理？如果你也在寻找答案，这篇文章推荐给你。

花名册是每个公司的HR都会经常接触到的一个Excel表格，往往每一个新同事入职，就需要HR在花名册里面输入一大堆繁冗的数据。

每每月初、月尾，我们还需要筛选出各种各样的数据，来做好试用期转正、劳动合同续签等等各种各样的事情。

那么在这个时候，强大的Excel可以帮助我们什么呢？当然就是一连串可以自动填充各种各样Vlookup/Date/If/MID函数了。

身份证号

在正式开始“授之以渔”之前，我们首先来了解一下中国公民的十八位居民身份证号码是怎样组成的吧。

号码的结构

公民身份号码是特征组合码，由17位数字本体码和1位校验码组成。排列顺序从左至右依次为：6位数字地址码，8位数字出生日期码，3位数字顺序码和1位数字校验码。

地址码（从左至右的第1-6位数字）

表示编码对象初始户口所在县（市、旗、区）的行政区划代码。

出生日期码（从左至右的第7-14位数字）

表示编码对象出生的年、月、日，代码之间不使用分隔符。

顺序码（从左至右的第15-17位数字）

表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性。

并且由于顺序码是县、区级政府所辖派出所的分配码，每个派出所分配码为10个连续号码，例如“000-009”，其中单数为男性分配码，双数为女性分配码，如遇同年同月同日有两人以上时顺延第2、第3、第4、第5个分配码。

举个例子

005的就是个男生，而且和他同年月日生的男生至少有两个，他们的后四位是001和003。

分配顺序码中“999、998、997、996”四个顺序号分别为男女性百岁以上老人专用的特定编号。

校验码（最后1位数字）

根据前面17位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。由于篇幅的关系，如果想要知道校验码如何计算的亲可以自行寻找度娘看看唷。

一、根据身份证号码判断出生日期、性别、年龄

（1）出生日期：

假定E2单元格为18位身份证号码所在的单元格，复制以下公式：

“=CONCATENATE(MID(E2,7,4),"-",MID(E2,11,2),"-",MID(E2,13,2))”

以上公式的原理在于CONCATENATE（字符串连接或合并）函数和MID（从一个文本字符串的指定位置开始，截取指定数目的字符）函数的运用。

具体语法

CONCATENATE(Text1,"-",Text2,"-", Text3)

MID(Text,Startnum,Numchars)

①MID(E2,7,4)意在身份证号码中获取表示年份的数字的字符串，即左起第7位开始，提取4个字符串；

②MID(E2,11,2)意在身份证号码中获取表示月份的数字的字符串，即左起第11位开始，提取2个字符串；

③MID(E2,13,2)意在身份证号码中获取表示日期的数字的字符串，即左起第13位开始，提取2个字符串；

④=CONCATENATE(MID(E2,7,4),"-",MID(E2,11,2),"-",MID(E2,13,2))

目的就是将①、②、③中提取的字符串合并在同一个单元格内显示。

（2）性别：

假定E2单元格为身份证号码所在的单元格，复制以下公式至单元格

“=IF(MOD(MID(E2,17,1),2),"男","女")”

以上公式的原理在于IF（判断真假，本段中可理解为判断男女）函数和MOD（求余）函数、MID函数的嵌套运用。

具体语法

IF(Logicaltest,Valueiftrue,Valueif_false)

MOD(Number,Divisor)

MID(Text,Startnum,Numchars)

①MID(A1,17,1)意在身份证号码中获取表示性别的字符串，即左起第17位开始，提取1个字符串。

②根据身份证号码的编号规则，我们知道，奇数为男性，偶数为女性。为了方便在公式中体现，我们可将奇数（即男性）视为“Valueiftrue”，将偶数（即女性）视为“Valueiffalse”。

利用MOD函数，取①除以2的余数，公式可写成MOD(MID(A1,17,1),2)。

③由上可得，若余数=1，第17位就是奇数（即男性）；若余数=0，第17位就是偶数（即女性）。于是总体公式就是

=IF(MOD(MID(A1,17,1),2),"男","女")。

（3）年龄

假定E2单元格为身份证号码所在的单元格，复制以下公式至单元格“=YEAR(NOW())-MID(E2,7,4)”

PS：此公式不另设单元格格式，且不对单元格进行四舍五入。小仙女可放心使用。

以上公式的原理主要运用到YEAR函数（将系列数转换为年）和MID函数的运用。

具体语法

YEAR(Serial_number)

MID(Text,Startnum,Numchars)

①使用YEAR函数计算年龄，第一步首先要知道当前的年份。

在所求单元格内输入=YEAR(NOW())，就可以直接求出今年的年份为2017年。

②在得出当前年份后，我们还需要在身份证号码中提取出的代表年份的字符串，于是可照搬公式MID(E2,7,4)。

③合并上述两个公式，则得出计算年龄总公式=YEAR(NOW())-MID(E2,7,4)。

由于此公式是直接计算年份，好处是不需要另外去设置单元格格式，并且不会产生后续的小数点，坏处就是基本上都会算少了一岁。如果对此表示不满意的小仙女和小鲜肉们，可直接在公式后手动+1即可。

二、根据身份证号码判断初始户籍所在地

由于身份证号码的前6位代表了初始户口所在县（市、旗、区）的行政区域代码，所以利用Vlookup（纵向查找）函数公式和辅助工作表即可完成此项。

假定E2单元格为身份证号码所在的单元格，Sheet2工作表为辅助工作表，Sheet2中A列为行政区域代码，B列为对应行政区域名称，

复制以下公式至单元格

=VLOOKUP(MID(E2,1,6)*1,Sheet2!$A$1:$B$9999,2,0)

以上公式的原理主要是利用Vlookup函数和MID函数的嵌套运用。

具体语法

VLOOKUP(Lookupvalue,Tablearray,Colindexnum,Range_lookup)

MID(Text,Startnum,Numchars)

①首先在同一个Excel工作簿内建立含有行政区域代码和名称的辅助工作表，根据上面的公式，辅助工作表的名称为Sheet2，辅助表中A列对应为行政区域代码，B列对应为对应行政区域名称。

②MID(E2,1,6)意在身份证号码中获取表示地址码的字符串，即左起第1位开始，提取6个字符串；

③使用Vlookup函数，在提取字符串后，使用字符串与辅助表Sheet2的A、B列数据进行引用，

从而得出总公式

=VLOOKUP(MID(E2,1,6)*1,Sheet2!$A$1:$B$9999,2,0)。

根据入职日期计算转正日期

在我们计算转正日期之前，首先要做的一件事，就是要清楚公司目前规定的转正时间。

以下分别给大家列举两种情况的Excel公式：

假定新员工2017年7月27日入职，D2单元格的内容为2017-07-27。

（1）入职满3月后转正，可直接使用DATE公式进行加减。

=DATE(YEAR(D2),MONTH(D2)+3,DAY(D2))

若是入职满1月、2月转正的，可直接将+3修改为+1、+2，以此类推。

同理可得，若是按天数去计算转正的，可以将+号挪移至DAY(D2)后，填写上转正的天数即可。

（2）当月10日前入职，试用期2个自然月；当月10日之后入职，首月不计算入试用期，试用期3个自然月。以每月的1日作为统一的转正日期。

假定D2单元格为入职日期所在的单元格，复制以下公式至单元格

=IF(DAY(D2)>10,DATE(YEAR(D2),MONTH(D2)+3,1),DATE(YEAR(D2),MONTH(D2)+2,1))

以上公式原理最关键的点，就是使用了IF函数的判断原理。

具体语法

IF(Logicaltest,Valueiftrue,Valueif_false)

DATE(YEAR(Text),MONTH(Text),DAY(Text))

若日期DAY大于10，则返回DATE(YEAR(D2),MONTH(D2)+3,1)的计算值；

若日期DAY不大于10，则返回DATE(YEAR(D2),MONTH(D2)+2,1)的计算值。

此公式的操作可根据实际情况进行变更，只需要修改、变动“>”号、“+”号的位置和具体值即可。

根据入职日期计算劳动合同签订、到期时间

作为每N年1签的劳动合同，我们也是可以利用Excel的DATE函数去计算签订日期、到期日期，甚至可以根据续签的年份，来变动相对应的日期。

那么这里的公式又是怎么样的呢？

假定新员工2017年7月27日入职，D2单元格的内容为2017-07-27。

签订3年的劳动合同，O2单元格为签订时间，P2单元格为到期时间，Q2单元格为登记续签年份。

签订时间公式：

=DATE(YEAR(D2)+ Q2,MONTH(D2),DAY(D2))

到期时间公式：

=DATE(YEAR(O2)+3,MONTH(O2),DAY(O2)-1)

这两个公式的运用的原理如同本文第三项。到期时间公式的颜色“+”号、“-”号同样可以变更为具体所需的数据。

其中，Q2单元格作为登记续签年份的存在，是需要手动的进行更新。其余的签订时间、到期时间单元格均可根据Q2的变动而变动。

有些小仙女可能会问，为什么需要在日期函数DAY后-1呢？

这出于计算一整个年度的考虑，往往劳动合同到期日均比签订日提前一天。在实际操作中视为同一日的，也可直接根据实际对公式进行更改。

当然，除了这几个Excel公式之外，还有更高效的员工档案管理方式，那就是利用薪人薪事的员工档案管理软件，员工便捷录入、导出和管理个人信息，HR可批量导入，轻轻松松做好员工管理。

“本文为薪人薪事（极致好用的人力资源系统）出品，转载请注明”