在大多数的认识行政工作中，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)。
注意事项：
等级考评时一定是模糊查询，而不是精准查询。
本章小结：
文中从人事工作出发，总结了四大类函数公式，如能熟练掌握，将大幅度提高工作效率。
热门跟贴