人事劳资工作中常用的Excel函数公式

01

【新进员工比率】＝已转正员工数/在职总人数 

02

【补充员工比率】＝为离职缺口补充的人数/在职总人数 

03

【离职率】（主动离职率/淘汰率＝离职人数/在职总人数=离职人数/(期初人数+录用人数)×100％

04

【异动率】＝异动人数/在职总人数 

05

【人事费用率】＝（人均人工成本*总人数）/同期销售收入总数 

06

【招聘达成率】=（报到人数+待报到人数）/（计划增补人数+临时增补人数） 

07

【人员编制管控率】=每月编制人数/在职人数 

08

【人员流动率】=（员工进入率+离职率）/2 

09

【离职率】=离职人数/（（期初人数+期末人数）/2）

10

【员工进入率】=报到人数/期初人数

11

【关键人才流失率】=一定周期内流失的关键人才数/公司关键人才总数

12

【工资增加率】=（本期员工平均工资—上期员工平均工资）/上期员工平均工资

13

【人力资源培训完成率】=周期内人力资源培训次数/计划总次数

14

【部门员工出勤情况】=部门员工出勤人数/部门员工总数

15

【薪酬总量控制的有效性】=一定周期内实际发放的薪酬总额/计划预算总额

16

【人才引进完成率】=一定周期实际引进人才总数/计划引进人才总数

17

【录用比】=录用人数/应聘人数*100%

18

【员工增加率】 =（本期员工数—上期员工数）/上期员工数

公式中的一些符号说明

◐ =TEXT(-1^MID(A,17,1),”女;男”)

◐  =IF(MOD(MID(A,17,1),2),”男”,”女”)

◐  =IF(-1^LEFT(RIGHT(A,2),1)=-1,”男”,”女”)

◐  =IF(-1^MID(A,9,9)=1,”女”,”男”)

◐=IF(A=””,””,IF(AND(LEN(A)<>15,LEN(A)<>18),”错误”,IF(ISERROR(1*(TEXT(MID(A,7,6+(LEN(A)=18)*2),”#-00-00″))),”错误”,IF(OR((1*(TEXT(MID(A,7,6+(LEN(A)=18)*2),”#-00-00″)))<VALUE(“1905-01-01″),(1*(TEXT(MID(A,7,6+(LEN(A)=18)*2),”#-00-00″)))>TODAY()),”错误”,VALUE(TEXT(MID(A,7,6+(LEN(A)=18)*2),”#年00月00日“))))))

◐=TEXT(MID(A,7,8),”0-00-00″)

利用出生年月计算年龄

◐=DATEDIF(B,TODAY(),”y”)

◐=YEAR(NOW())-YEAR(B)

利用身份证号码计算年龄

◐=YEAR(NOW())-MID(A,7,4)

◐=TEXT(EDATE(B,12*(5*(C=”男”)+55)),”yyyy/mm/dd aaaa”)

◐=DATEDIF(D,NOW(),”y”)

◐=DATEDIF(E,NOW(),”y”)&”年”&DATEDIF(E,NOW(),”ym”)&”月”&DATEDIF(E,NOW(),”md”)&”天”

◐=MID(“猴鸡狗猪鼠牛虎兔龙蛇马羊“,MOD(MID(A,7,4),12)+1,1)

说明：该公式是以月工资5000作为税额起征点，按月进行申报，未叠加专项附加扣除。

◐=MAX((F-5000)*{3;10;20;25;30;35;45}%-{0;210;1410;2660;4410;7160;15160},0)

依据标准：GBT 226-2007中华人民共和国行政区划代码，EXCEL版本的已按公开的最新信息更新

◐ =VLOOKUP(LEFT(A,6)*1,地址库!E:F,2,)

身份证号码地址库下载地址：

链接： https://pan.baidu.com/s/1QHa0HZNaktj_dBt_cx9XqA?pwd=aa2i 

提取码: aa2i

将‌所有空格‌（包括中间）替换为空白，实现‌完全删除‌。（假设数据在A1）

◐=SUBSTITUTE(A1, ” “, “”)：将 A1 中所有空格删除

若文字和数字‌穿插出现‌（如“a1b2c3”），需提取‌所有数字‌或‌所有文字‌，可使用以下数组公式（假设数据在A1）：

◐提取所有数字：=TEXTJOIN(“”, TRUE, IF(ISNUMBER(–MID(A1, ROW(INDIRECT(“1:”&LEN(A1))), 1)), MID(A1, ROW(INDIRECT(“1:”&LEN(A1))), 1), “”))

◐‌提取所有文字‌（非数字字符）：=TEXTJOIN(“”, TRUE, IF(ISNUMBER(–MID(A1, ROW(INDIRECT(“1:”&LEN(A1))), 1)), “”, MID(A1, ROW(INDIRECT(“1:”&LEN(A1))), 1)))

（假设人员信息数据在B列）

◐=IF(COUNTIF(B:B,B49)>1,”重复”,”0″)

◐=IF(A=””,””,(IF(MID(“10X98765432”,MOD(SUMPRODUCT(MID(A,ROW(INDIRECT(“1:17”)),1)*2^(18-ROW(INDIRECT(“1:17″)))),11)+1,1)=MID(A,18,18),”0″,”错误”)))

◐=IF(G=””,””,IF(MOD(SUMPRODUCT(–(0&MID((0&MID(G,ROW($1:$20),1))*2^MOD(ROW($1:$20)+MOD(LEN(G),2),2),{1,2},1))),10),”错误”,”0″))

◐=VLOOKUP(A1,跨行行号信息!$A:$C,2,FALSE)

◐=(MID(A1,1,6))

◐=COUNTIF(D7:AH8,”√”)/2

计算“华东区”且“A产品”且”1月份“的销售总额。

◐=SUMIFS(C:C, A:A,”华东”,B:B,”A产品”)

统计“销售部”且”迟到”的人数。

◐=COUNTIFS(B:B，”销售部”，C:C，”迟到”)