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

◐=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，"迟到")