HR常用的Excel函數公式大全(共21個),幫你整理齊了!

　　

　　在HR同事電腦中，蘭色經常看到海量的Excel表格，員工基本信息、提成計算、考勤統計、合同管理....看來再完備的HR系統也取代不了Excel表格的作用。一周前，蘭色交給小助理木炭一個任務，儘可能多的收集HR工作中的Excel公式。蘭色進行了整理編排，於是有了這篇本平臺史上最全HR的Excel公式+數據分析技巧集。

　　一、員工信息表公式

　　

　　1、計算性別(F列)

　　 =IF(MOD(MID(E3,17,1),2),"男","女")

　　2、出生年月(G列)

　　 =TEXT(MID(E3,7,8),"0-00-00")

　　3、年齡公式(H列)

　　=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列)

　　=DATEDIF(S3,NOW(),"y")

　　7、公司工齡(W列)

　　=DATEDIF(V3,NOW(),"y")&"年"&DATEDIF(V3,NOW(),"ym")&"月"&DATEDIF(V3,NOW(),"md")&"天"

　　8、合同續籤日期(Y列)

　　=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1

　　9、合同到期日期(Z列)

　　=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[<0]過期0天;[<30]即將到期0天;還早")

　　10、工齡工資(AA列)

　　=MIN(700,DATEDIF(\$V3,NOW(),"y")*50)

　　11、生肖(AB列)

　　=MID("猴雞狗豬鼠牛虎兔龍蛇馬羊",MOD(MID(E3,7,4),12)+1,1)

　　二、員工考勤表公式

　　

　　1、本月工作日天數(AG列)

　　=NETWORKDAYS(B\$5,DATE(YEAR(N\$4),MONTH(N\$4)+1,),)

　　2、調休天數公式(AI列)

　　=COUNTIF(B9:AE9,"調")

　　3、扣錢公式(AO列)

　　婚喪扣10塊，病假扣20元，事假扣30元，礦工扣50元

　　=SUM((B9:AE9={"事";"曠";"病";"喪";"婚"})*{30;50;20;10;10})

　　四、員工數據分析公式

　　

　　1、本科學歷人數

　　=COUNTIF(D:D,"本科")

　　2、辦公室本科學歷人數

　　=COUNTIFS(A:A,"辦公室",D:D,"本科")

　　3、30~40歲總人數

　　=COUNTIFS(F:F,">=30",F:F,"<40")

　　五、其他公式

　　1、提成比率計算

　　=VLOOKUP(B3,\$C\$12:\$E\$21,3)

　　

　　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),,),"")

　　註：

　　

　　

　　4、Countif函數統計身份證號碼出錯的解決方法

　　由於Excel中數字只能識別15位內的，在Countif統計時也只會統計前15位，所以很容易出錯。不過只需要用 &"*" 轉換為文本型即可正確統計。

　　=Countif(A:A,A2&"*")

　　

　　六、利用數據透視表完成數據分析

　　

　　1、各部門人數佔比

　　統計每個部門佔總人數的百分比

　　

　　2、各個年齡段人數和佔比

　　公司員工各個年齡段的人數和佔比各是多少呢？

　　

　　3、各個部門各年齡段佔比

　　分部門統計本部門各個年齡段的佔比情況

　　

　　4、各部門學歷統計

　　各部門大專、本科、碩士和博士各有多少人呢？

　　

　　5、按年份統計各部門入職人數

　　每年各部門入職人數情況

　　

　　附：HR工作中常用分析公式

　　1.【新進員工比率】＝已轉正員工數/在職總人數

　　2.【補充員工比率】＝為離職缺口補充的人數/在職總人數

　　3.【離職率】（主動離職率/淘汰率＝離職人數/在職總人數=離職人數/(期初人數+錄用人數)×100％

　　4.【異動率】＝異動人數/在職總人數

　　5.【人事費用率】＝（人均人工成本*總人數）/同期銷售收入總數

　　6.【招聘達成率】=（報到人數+待報到人數）/（計劃增補人數+臨時增補人數）

　　7.【人員編制管控率】=每月編制人數/在職人數

　　8.【人員流動率】=（員工進入率+離職率）/2

　　9.【離職率】=離職人數/（（期初人數+期末人數）/2）

　　10.【員工進入率】=報到人數/期初人數

　　11.【關鍵人才流失率】=一定周期內流失的關鍵人才數/公司關鍵人才總數

　　12.【工資增加率】=（本期員工平均工資—上期員工平均工資）/上期員工平均工資

　　13.【人力資源培訓完成率】=周期內人力資源培訓次數/計劃總次數

　　14.【部門員工出勤情況】=部門員工出勤人數/部門員工總數

　　15.【薪酬總量控制的有效性】=一定周期內實際發放的薪酬總額/計劃預算總額

　　16.【人才引進完成率】=一定周期實際引進人才總數/計劃引進人才總數

　　17.【錄用比】=錄用人數/應聘人數*100%

　　18.【員工增加率】 =（本期員工數—上期員工數）/上期員工數

　　本文Excel示例下載（百度網盤）：https://pan.baidu.com/s/1kVLvWwR

　　蘭色說：今天分享的Excel公式雖然很全，但實際和HR實際要用到的excel公式相比，還會有很多遺漏。歡迎做HR的同學們補充你工作中最常用到的公式。

　　長按下面二維碼圖片，點上面」識別圖中二維碼「然後再點關注，每天可以收到一篇蘭色最新寫的excel教程。

　　

特別聲明：以上內容(如有圖片或視頻亦包括在內)為自媒體平臺「網易號」用戶上傳並發布，本平臺僅提供信息存儲服務。

Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.