人事必备的4类10组Excel(WPS表格)函数公式，初学者必读!

在大多数的认识行政工作中，Excel（WPS表格）的使用率还是非常的高，不仅能提高工作效率，方便快捷易学易掌握也是一个重要原因……但面对身份信息、考勤、合同管理等复杂场景时，许多同学往往束手无策，仍手动进行，严重影响了工作效率。

一、基础类（身份证相关）

1.提取出生年月

函数：Text+Mid

原理：利用Mid函数提取身份证号码中的第7-15位（出生日期），然后用Text函数将其设置为指定的日期形式。

操作方法：

在目标单元格中输入公式：=TEXT(MID(D4,7,8),"00-00-00")。

注意事项：

身份证号码中从第7位开始长度为8的字符串为出生日期。

2.判断性别

函数：If+MOD+MId

原理：利用MId函数提取指定的值用MOD函数求余后，用If函数判断，如果为奇数，则返回“男”，否则返回“女”。

操作方法：

在目标单元格中输入公式：=IF(MOD(MID(D4,17,1),2),"男","女")。

注意事项：

身份证号码中第17位代表性别。

3.计算年龄

函数：Datedif+Text+MId+Today

原理：首先利用Text+Mid函数提取出生日期，并转换为日期格式；然后用Datedif函数和当前（Today()）日期进行比较，得出年龄。

操作方法：

在目标单元格中输入公式：=DATEDIF(TEXT(MID(D4,7,8),"00-00-00"),TODAY(),"y")。

注意事项：

如果数据源中已经包含出生日期，则可以省略Text+Mid部分，直接用Datedif函数计算。

二、时间计算类

1.计算工龄

函数：Datedif+Today

原理：利用Datedif函数对入职日期和离职日期进行比较，并返回指定的值。

操作方法：

在目标单元格中输入公式：=DATEDIF(E4,TODAY(),"y")。

注意事项：

1.如果工龄按照月份或日期计算，只需将公式中的参数“y”更改为“m”或“d”即可。

2.如果要精准到X年X月，则可以使用：=DATEDIF(E4,TODAY(),"y")&"年"&DATEDIF(E4,TODAY(),"ym")&"个月"。

2.时间管理

函数：员工生日提醒。

函数：Datedif+Text+Mid+Today

原理：利用一年的时间（365天）减去出生日期和今天（Today）相差的天数，就是距过生日的天数。

操作方法：

在目标单元格中输入公式：="还有"&365-DATEDIF(TEXT(MID(D4,7,8),"00-00-00"),TODAY(),"yd")&"天过生日"。

注意事项：

当Datedif函数的第三个参数为“yd”时，统计的是开始日期之后到结束日期的天数，所以用一年（365天）减去顺延的天数后，剩下的就是忽略年月后的天数。

三、考勤统计类

1.根据工龄计算年假（如果工龄≥10年，15天；≥5年，10天，＜5年，5天）。

函数：If+Datedif+Today

原理：利用If函数判断Datedif函数的计算结果，根据计算结果的大小返回相应的值。

操作方法:

在目标单元格中输入公式：=IF(DATEDIF(E4,TODAY(),"y")>10,15,IF(DATEDIF(E4,TODAY(),"y")>5,10,5))。

注意事项：

公式的核心就是Datedif函数的返回值，其次就是If函数的嵌套应用，逻辑关系一定要搞清楚。

2.考勤

函数：If+Time

原理：将考勤的时间和指定的时间（签到：9：00，签退：18:00）进行对比，返回指定的值。

操作方法：

在目标单元格中输入公式：=IF(K4>TIME(9,0,0),"迟到","")；=IF(L4>TIME(18,0,0),"","早退")。

注意事项：

迟到和早退都用的是“＞”计算，签到时如果＞9:00，则为“迟到”；但签退时，如果＞18:00，则为正常，否则为“早退”。两者的逻辑关系不一样。

3.计算加班时长

函数：Text+Max+Time

原理：（签退时间-签到时间-应在时长）＞0，则按要求返回时长，否则返回0。

操作方法：

在目标单元格中输入公式：=TEXT(MAX(0,L4-K4-TIME(9,0,0)),"h小时mm分")。

注意事项：

1，9:00签到，18:00签到，应在时长就为9小时，所以公式中L4-K4-TIME(9,0,0)，而不是其他的时间。

2.加班时间从签到就开始可计算，而并不是从9:00开始计算，在统计中根据实际进行调整。

四、绩效统计类

1.销售额增长率

函数：Text。

原理：增长额÷基数，然后用Text函数转换为百分数即可。

操作方法：

在目标单元格中输入公式：=TEXT((K4-J4)/J4,"0.00%")。

2.等级考评

函数：Vlookup

原理：利用Vlookup函数的模糊匹配功能，匹配考评等级。

操作方法：

在目标单元格中输入公式：=VLOOKUP(L4,$O$4:$P$6,2)。

注意事项：

等级考评时一定是模糊查询，而不是精准查询。

本章小结：

文中从人事工作出发，总结了四大类函数公式，如能熟练掌握，将大幅度提高工作效率。