10个制作花名册必⽤的Excel公式，必须收藏！
60秒get⼀个HR实⽤⼩技巧
花名册是每个公司的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,Start_num,Num_chars)
①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(Logical_test,Value_if_true,Value_if_false)
MOD(Number,Divisor)
MID(Text,Start_num,Num_chars)
①MID(A1,17,1)意在⾝份证号码中获取表⽰性别的字符串，即左起第17位开始，提取1个字符串。
②根据⾝份证号码的编号规则，我们知道，奇数为男性，偶数为⼥性。为了⽅便在公式中体现，我们可将奇数（即男性）视为“Value_if_true”，将偶数（即⼥性）视为“Value_if_false”。
利⽤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)”
为什么vlookup显示的是公式
PS：此公式不另设单元格格式，且不对单元格进⾏四舍五⼊。⼩仙⼥可放⼼使⽤。
以上公式的原理主要运⽤到YEAR函数（将系列数转换为年）和MID函数的运⽤。
具体语法
YEAR(Serial_number)
MID(Text,Start_num,Num_chars)
①使⽤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(Lookup_value,Table_array,Col_index_num,Range_lookup)
MID(Text,Start_num,Num_chars)
①⾸先在同⼀个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))

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。