HR必備的15個EXCEL公式

  HR工作中用到的公式多種多樣,今天給大家歸納了15個必備的Excel公式,希望對大家的工作起到幫助作用。

1、身份證提取出生日期

=–TEXT(MID(A2,7,8),”0000-00-00″)

HR必備的15個EXCEL公式

如果出現以下情況,設置為「日期」格式。

HR必備的15個EXCEL公式

2、身份證提取性別

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

HR必備的15個EXCEL公式

3、根據出生日期計算年齡

=DATEDIF(A2,TODAY(),”y”)

HR必備的15個EXCEL公式

4、根據入職日期計算入職月數

=DATEDIF(A2,TODAY(),”M”)

HR必備的15個EXCEL公式

5、生日提醒

15天之內提醒

=TEXT(15-DATEDIF(A2-15,TODAY(),”yd”),”還有0天生日;;今天生日”)

HR必備的15個EXCEL公式

6、提取部分人員信息

=VLOOKUP($F2,$A$1:$D$11,4,0)

HR必備的15個EXCEL公式

7、計算年休假天數

根據《職工帶薪年休假條例》和《企業職工帶薪年休假實施辦法》,年休假是按自然年核算,工齡滿1年、10年和20年的年度,要分段計算年休假。

=IFERROR(INT(SUM((DATE($B$1,MONTH(B3),DAY(B3))-DATE($B$1+{0,1},1,1))*{1,-1}*LOOKUP(DATEDIF(B3,DATE($B$1+1,1,1),”y”)-{1,0},{0,0;1,5;10,10;20,15}))/SUM(DATE($B$1+{1,0},1,1)*{1,-1})),0)

HR必備的15個EXCEL公式

8、統計員工全年培訓情況

=INDEX(B:B,SMALL(IF($A$2:$A$1000=$F$2,ROW($2:$1000),4^8),ROW(1:1)))

G2單元格輸入公式後,按Ctrl+Shift+Enter三鍵,向右和向下拖動填充公式。

HR必備的15個EXCEL公式

9、計算遲到情況

假定8:30上班。

=IF(C2>8.5/24,”遲到”,””)

HR必備的15個EXCEL公式

10、計算個稅

=ROUND(MAX((B2-3500)*{3,10,20,25,30,35,45}%-{0,105,555,1005,2755,5505,13505},0),2)

HR必備的15個EXCEL公式

11、計算年終獎個稅

=LOOKUP(MAX(0.0001,(C2+MIN(B2-3500,0))/12),{0;3;9;18;70;110;160}*500+0.0001,MAX(0,(C2+MIN(B2-3500,0)))*{3;10;20;25;30;35;45}%-5*{0;21;111;201;551;1101;2701})

HR必備的15個EXCEL公式

12、按部門匯總人數

=COUNTIF(C:C,F2)

HR必備的15個EXCEL公式

13、按部門匯總薪水

=SUMIF(C:C,F2,D:D)

HR必備的15個EXCEL公式

14、統計員工考核結果

60分以下為「不及格」,大於等於60分小於80分為「及格」, 大於等於80分小於90分為「良好」, 大於等於90分為「優秀」。

=LOOKUP(B2,{0,”不及格”;60,”及格”;80,”良好”;90,”優秀”})

HR必備的15個EXCEL公式

15、計算部門平均績效得分

=ROUND(AVERAGEIF($C$2:$C$357,F2,$D$2:$D$345),2)

HR必備的15個EXCEL公式