个人微信号 | (ID:LiRuiExcel520) 微信服务号 | 跟李锐学Excel(ID:LiRuiExcel) 微信公众号 | Excel函数与公式(ID:ExcelLiRui) vlookup丨countif丨lookup丨sumif丨sumproduct 人资HR工作必备的9个Excel函数公式 HR人资在工作中需要用到Excel的地方太多了,我根据平时的反馈,把提问最集中的9类问题总结如下,希望能帮大家排忧解难。 1 从身份证号码提取生日 2 根据身份证号码计算退休日期 3 检查身份证号码是否重复 4 根据身份证号码判断性别 5 根据身份证号码实现生日提醒 6 根据分项得分和所占权重计算KPI绩效最后得分 7 去掉一个最高分、去掉一个最低分后再计算平均分 8 统计两个日期之间的工作日天数 9 根据工龄计算年假天数的4种方法 这些技术在手,可以10倍+提升工作效率,推荐收藏。 一、从身份证号码提取生日 案例场景截图如下(黄色区域输入公式) =TEXT(MID(A2,7,8),"0-00-00") 二、根据身份证号码计算退休日期 案例场景截图如下(黄色区域输入公式) =DATE(MID(A2,7,4)+55+MOD(MID(A2,17,1),2)*5,MID(A2,11,2),MID(A2,13,2)) 三、检查身份证号码是否重复 案例场景截图如下(黄色区域输入公式) =IF(COUNTIF($B$2:$B$11,B2&"*")>1,"重复","") 四、根据身份证号码判断性别 案例场景截图如下(黄色区域输入公式) =IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女") 五、根据身份证号码实现生日提醒 案例场景截图如下(黄色区域输入公式) =TEXT(7-DATEDIF(--TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2.2),8),"0-00-00"),NOW()+7,"yd"),"0天后生日;;今天生日") 六、根据分项得分和所占权重计算KPI绩效得分 案例场景截图如下(黄色区域输入公式) =SUMPRODUCT(B$2:E$2*B3:E3) 七、去掉一个最高分、一个最低分后再计算平均分 案例场景截图如下(黄色区域输入公式) =TRIMMEAN(B2:H2,2/COUNT(B2:H2)) 更多Excel函数实战技术,请进知识店铺查看二期、八期、九期、十二期特训营。 八、统计两个日期之间的工作日天数 一提到工作日计算,很多HR都皱紧了眉头,因为各种岗位员工的排班不同,休息日不同,再加上员工的请假和各种节假日的调休......99%的HR都是在手动计算工作日中苦苦煎熬。 看完下面这个示例的解决方案,以上所有问题一扫而光! 上图所示为按照周六周日双休的企业中,加上自定义节假日的工作日算法 首先定义名称,按<Ctrl+F3>打开名称管理器创建自定义名称holidays =OFFSET(Sheet1!$E$2,,,COUNTA(Sheet1!$E:$E)-1) 然后在C2输入公式: =NETWORKDAYS.INTL(A2,B2,1,holidays) 这样就轻松计算出起始日期和终止日期之间的工作日天数了 为了大家能够举一反三,我再多啰嗦几句,解析一下原理。 NETWORKDAYS.INTL函数的语法结构如下: =NETWORKDAYS.INTL(起始日期,终止日期,周末日weekend,自定义节假日holidays) 关键点在于第三参数和第四参数。 关于第3参数weekend第二种表达方式的几点说明: 1、weekend 的表达方式2是一个长度为7的字符串 2、该字符串中的每个字符代表一周中的一天,从星期一开始 3、1 代表休息日,0 代表工作日 4、该字符串中只允许使用字符 1 和 0 关于第4参数holidays的几点说明: 1、是一个包含一个或多个日期的可选集合,这些日期将作为节假日不参与工作日个数统计 2、可以是包含日期的单元格区域,也可以是区域的引用,还可以是代表日期序列值的数组常量 3、其中的日期或序列值顺序可以任意打乱,不影响统计。 希望能够帮到大家轻松应对各种情况的工作日计算! 九、根据工龄计算年假天数的4种方法 【职工带薪年休假条例】 根据职工累计工作年限而定: 1、已满1年不满10年的,年休假5天; 2、已满10年不满20年的,年休假10天; 3、已满20年的,年休假15天。 案例场景截图如下(黄色区域输入公式) 方法1=IF(B2<1,0,IF(B2<10,5,IF(B2<20,10,15))) 方法2=LOOKUP(B2,{0,1,10,20},{0,5,10,15}) 方法3=5*MIN(3,ROUNDUP(B2/9.9,0)) 方法4=5*MIN(3,CEILING(B2/9.9,1)) 希望这篇文章能帮到你! >>推荐阅读 << (点击蓝字可直接跳转) |
|