【5A文】HR常用的Excel函数公式大全.pdf
【5A 文】HR 常用的 Excel 函数公式大全 【5A5A 文】文】 HRHR 常用的常用的 ExcelExcel 函数公式大全函数公式大全 一、员工信息表公式 1、计算性别 (F 列) =IF(MOD(MID(E3,17,1),2),男,女) 2、出生年月 (G 列) =TEXT(MID(E3,7,8),0-00-00) 3、年龄公式 (H 列) 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 =DATEDIF(G3,TODAY(),y) 4、退休日期 (I 列) =TEXT(EDATE(G3,12*(5*(F3=男)+55),yyyy/mm/dd aaaa) 5、籍贯 (M 列) =VLOOKUP(LEFT(E3,6)*1,地址库 !E:F,2,) 注：附带示例中有地址库代码表 6、社会工龄 (T 列) 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 =DATEDIF(S3,NOW(),y) 7、公司工龄 (W 列) =DATEDIF(V3,NOW(),y)旷;病;丧;婚 )*30;50;20;10;10) 三、员工数据分析公式 1、本科学历人数 =COUNTIF(D:D, 本科 ) 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 2、办公室本科学历人数 =COUNTIFS(A:A,办公室 ,D:D, 本科 ) 3、3040 岁总人数 =COUNTIFS(F:F,=30,F:F, 四、其他公式 1、提成比率计算 =VLOOKUP(B3,$C$12:$E$21,3) 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 2、个人所得税计算 假如 A2 中是应税工资，则计算个税公式为： =5*MAX(A2*0.6,2,4,5,6,7,9%-21,91,251,376,761,1346 ,3016,) 3、工资条公式 =CHOOSE(MOD(ROW(A3),3)+1,工资数据 源!A$1,OFFSET( 工资数据源 !A$1,INT(ROW(A3)/3),),) 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 注： A3:标题行的行数 +2,如果标题行在第3 行，则 A3 改为 A5 工资数据源 !A$1: 工资表的标题行的第一列位置 4、Countif 函数统计身份证号码出错的解决方法 由于 Excel 中数字只能识别15 位内的，在 Countif 统计时也 只会统计前15 位，所以很容易出错。不过只需要用 &* 转换 为文本型即可正确统计。 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 =Countif(A:A,A2&*) 五、利用数据透视表完成数据分析 1、各部门人数占比 统计每个部门占总人数的百分比 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 2、各个年龄段人数和占比 公司员工各个年龄段的人数和占比各是多少呢？ 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 3、各个部门各年龄段占比 分部门统计本部门各个年龄段的占比情况 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 4、各部门学历统计 各部门大专、本科、硕士和博士各有多少人呢？ 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 5、按年份统计各部门入职人数 每年各部门入职人数情况 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 附： HR 工作中常用分析公式 1.【新进员工比率】已转正员工数/在职总人数 2.【补充员工比率】为离职缺口补充的人数/在职总人数 3.【离职率】 （主动离职率 /淘汰率离职人数/在职总人数 = 离职人数 /(期初人数 +录用人数 )100 4.【异动率】异动人数/在职总人数 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 5.【人事费用率】（人均人工成本*总人数） /同期销售收 入总数 6.【招聘达成率】 =（报到人数 +待报到人数） /（计划增补 人数 +临时增补人数） 7.【人员编制管控率】=每月编制人数 /在职人数 8.【人员流动率】 =（员工进入率 +离职率） /2 9.【离职率】 =离职人数 /（期初人数 +期末人数） /2） 10.【员工进入率】 =报到人数 /期初人数 11.【关键人才流失率】 =一定周期内流失的关键人才数/公司 关键人才总数 12.【工资增加率】 =（本期员工平均工资上期员工平均工 资） /上期员工平均工资 13.【人力资源培训完成率】 =周期内人力资源培训次数/计划 总次数 14.【部门员工出勤情况】 =部门员工出勤人数/部门员工总数 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 15.【薪酬总量控制的有效性】=一定周期内实际发放的薪酬 总额 /计划预算总额 16.【人才引进完成率】 =一定周期实际引进人才总数/计划引 进人才总数 17.【录用比】 =录用人数 /应聘人数 *100% 18.【员工增加率】=（本期员工数上期员工数）/上期员 工数 本文 Excel 示例下载（百度网盘）： 今天分享的Excel 公式虽然很全，但实际和HR 实际要用到 的 excel 公式相比，还会有很多遗漏。欢迎做HR 的同学们补充 你工作中最常用到的公式。 要点回顾： 1. 员工信息表公式 2. 员工考勤表公式 3. 员工数据分析公式 【5A 文】HR 常用的 Excel 函数公式大全 【5A 文】HR 常用的 Excel 函数公式大全 4. 其他公式 5. 利用数据透视表完成数据分析 【5A 文】HR 常用的 Excel 函数公式大全
