HR常用的Excel函數(shù)公式大全

1、在HR同事電腦中，經(jīng)常看到海量的Excel表格，員工基本信息、提成計(jì)算、考勤統(tǒng)計(jì)、合同管理.看來(lái)再完備的HR系統(tǒng)也取代不了Excel表格的作用。一、員工信息表公式DEFCHI姓者身俗證號(hào)的出生年月年齡退休日期籍貫丁狀1969-03-22482029/03/22星期臼吉林省吉林下1980-05-25372040/05/25星期五可南營(yíng)駐馬店才龔麗麗1930-03,11372040/03/11星期日祖南告杵洲二陳美華1985-11-1631

2、2040/11/16湖南省株洲T汪志剛1980-08-12362口35/08/12星同日湖南譽(yù)株慚李麗君1570-01-01472025/01/01星期三江蘇雷仕林除美麗1973-02-17442033/02/17星期四湖北含赧F'132801194了05058361女1947-05-05702002/05/OS星期日河北省J6坊堆段月񟫺-01-15372040/01/150湖南省株沛劉美F320325198001

3、010124女1930-01-01372035/01/01星期一江蘇省徐州1、計(jì)算性別(F列)=IF(MOD(MID(E3,17,1),2),"男","女")2、出生年月(G列)=TEXT(MID(E3,7,8),"0-00-00")3、年齡公式（H歹IJ）=DATEDIF(G3,TODAY(),"y")4、退休日期？(I歹ij)=TEXT(EDATE(G3,12*(5*(F3="男")+55),"yyyy/mm/ddaaaa")5、籍貫(M列)=VLOOKUP(LEFT(E

4、3,6)*1,地址庫(kù)！E:F,2,)注：附帶示例中有地址庫(kù)代碼表TU7XYL姓名工幗捆社會(huì)工齡潸迎光兄_I人司時(shí)間公司工齡修同期合同續(xù)簽3_Ttt_J2012-9-2242012-8-7gi1月20天1。年2022-8-6i陵2015-4-222視2013-9-1&3年1。引2天2年2。15%14j聾麗麗1592-9-824未婚2OO5-6-3T1年11月24天m年200B-8-2iE京美軍1995-12-27212012-8-50詢0月天W18-8-29?江志剛1993-8-9232009-4-198年3月8天5年2014-4-18¥李麗君1992-3-1725201061

5、1了蛔月16天2年2U12610j靛美麗2005-10-211119%-5-S21年2月21天1年1997-5-52015B822002-11-201段8月天2年2004-1M91殷月2005-7-2121993-2-2"年5月25天2年1995-2-2劉勇1994-4-11231991-10-20”年哨7天舜1994-10-193456、社會(huì)工齡（T歹IJ）=DATEDIF(S3,NOW(),"y")7、公司工齡(W列)=DATEDIF(V3,NOW(),"y")&"年"&DATEDIF(V3,NOW(),

6、"ym")&"月"&DATEDIF(V3,NOW(),"md")&"天"8 、合同續(xù)簽日期(Y列)=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3)-19 、合同到期日期(Z列)=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"10 、工齡工資(AA列)=MIN(700,DATEDIF($V3,NOW(),"y")*50)11 、生肖(AB列)=MID("猴雞狗豬鼠?；⑼谬埳唏R羊&qu

7、ot;,MOD(MID(E3,7,4),12)+1,1)二、員工考勤表公式UFITXJY.ZkkfeBAC.ADAEAP.AG數(shù)及所扣工錢,嫁扣1。塊，病假扣20元,事假扣30元,礦工扣50元1、本月工作日天數(shù)（AG歹IJ）=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)2、調(diào)休天數(shù)公式(AI歹IJ)=COUNTIF(B9:AE9,"調(diào)")3、扣錢公式(AO歹IJ)婚喪扣10塊，病假扣20元，事假扣30元，礦工扣50元=SUM(B9:AE9="事"曠"病""喪"&qu

8、ot;婚")*30;50;20;10;10)三、員工數(shù)據(jù)分析公式AECDEGI1部門姓名悵！1學(xué)歷出生年月日入職時(shí)間-七.2公司領(lǐng)導(dǎo)尹萼幢S1583-4-15342004-4-29133公司領(lǐng)導(dǎo)劉毅男1965-7-26522003-4-884公司領(lǐng)導(dǎo)王的事碩士1974-10-14422000-726175公司領(lǐng)導(dǎo)陳星女1都1580-7-24372015-6-526辦公室洪曷庫(kù)男大專1962-10-11541999-6-1187辦公室雌女神19832)342004-5-13138辦公室韋琳女制1590-5-24272015-6-2929.辦公室贓女相1591-8-26252014-10

9、-15210辦公宣普美究女椿5978-10-27382010-9-21611辦公室里1976-7-20412015-7-29112辦公室專障云女大專1994-12-1622201S-7-27213辦公室女神1585-7-13322015-10-20114汽車美容中心姜保林男商中4921515的美容中心as初中1994-9/9222014-11-21216汽車美容中心尊立威更初中1992-5-1252011-6-20617汽車美容中心趙勝峰在力高中1979-9-15372014-9-26218j作美容中心由沖咒初中1595-8-14212014-10-1121、本科學(xué)歷人數(shù)=COUNTIF（D:

10、D,"本科"）2、辦公室本科學(xué)歷人數(shù)=COUNTIFS(A:A,"辦公室",D:D,"本科")3、3040歲總?cè)藬?shù)=COUNTIFS(F:F,">=30",F:F,”四、其他公式1、提成比率計(jì)算=VLOOKUP(B3,$C$12:$E$21,3)C3A=VLOOKUP(B3XS12ES21,3)ft_DE1計(jì)尊員工業(yè)凄提頑比例2|'t比例獎(jiǎng)金顫3：S296541%|296.54432917425%182293.5523459015%3518&5631002525%77506257897502%

11、17958q1CL=VLOOKUF(B3,$L$3:$N$12,3)11業(yè)績(jī)總?cè)谳o助列等強(qiáng)說(shuō)明獎(jiǎng)金比例1121低于10000元00%132:含I00U0元100001%143含50口0口元)500002%154含100000元1oaooQ«±25%105含150。0。元15000010%176含2。口000元20000015%IS7含250000元250000蕩注320%cinnnnni氣rftf2、個(gè)人所得稅計(jì)算假如A2中是應(yīng)稅工資，則計(jì)算個(gè)稅公式為：=5*MAX(A2*,2,4,5,6,7,9%-21,91,251,376,761,1346,3016,)3、工資條公式=

12、CHOOSE(MOD(ROW(A3),3)+1,工資數(shù)據(jù)源！A$1,OFFSET(工資數(shù)據(jù)源!A$1,INT(ROW(A3)/3),),"")注：A3:標(biāo)題行的行數(shù)+2,如果標(biāo)題行在第3行，則A3改為A5工資數(shù)據(jù)源！A$1:工資表的標(biāo)題行的第一列位置4、Countif函數(shù)統(tǒng)計(jì)身份證號(hào)碼出錯(cuò)的解決方法由于Excel中數(shù)字只能識(shí)別15位內(nèi)的，在Countif統(tǒng)計(jì)時(shí)也只會(huì)統(tǒng)計(jì)前15位，所以很容易出錯(cuò)。不過(guò)只需要用？&"*"?轉(zhuǎn)換為文本型即可正確統(tǒng)計(jì)。=Countif(A:A,A2&"*")A=COUNHF(A;A,Abjti

13、r身份證出現(xiàn)次數(shù)/2一1111111111111111121JrLlllllllllllllLllS21111111111111111141iiiiiiiniiiiiiui21111111111111111151illlLlllllllllinS21111111111111111161_一五、利用數(shù)據(jù)透視表完成數(shù)據(jù)分析ABCD1部門入職日期年齡學(xué)歷2財(cái)務(wù)部201561832博士3財(cái)務(wù)部2014-1-732本科4財(cái)務(wù)部2015-2-2637大專5人事部2015-1-2549本科6人事部2013-81621大專?人事部2012-12-636大專8人事部2015-2-1343本科9人事部2014-1

14、2-2324本科10銷售部2014-8-1734大專11,銷售都2014-6-225碩士12銀總部2cH522641碩士13銷售部201321123大專1、各部門人數(shù)占比統(tǒng)計(jì)每個(gè)部門占總?cè)藬?shù)的百分比2、各個(gè)年齡段人數(shù)和占比公司員工各個(gè)年齡段的人數(shù)和占比各是多少呢ABC文件開蛉播入支面布局公式敵據(jù)苗闌祝圜升發(fā)工具選直設(shè)計(jì)審合口百J£123456781011121.314151617數(shù)據(jù)ig視表E若要生成報(bào)表，請(qǐng)從“嫩盛表，中選擇字段口卜國(guó)Sheetl/She"taj'ShET9闡回凹II日3、各個(gè)部門各年齡段占比分部門統(tǒng)計(jì)本部門各個(gè)年齡段的占比情況4、各部門學(xué)歷統(tǒng)計(jì)各

15、部門大專、本科、碩士和博士各有多少人呢文件開始插入天面布局公式數(shù)據(jù)宙閱視圜產(chǎn)發(fā)工具選直中?？诩?#163;ABC1T蠹據(jù)透程表宇佳列表選擇要靠邠到報(bào)表的字段:34567810數(shù)據(jù)透視表3*，Shcetl/Sh虺虺若要生成報(bào)表，請(qǐng)從“前據(jù)透視表字段列表”中場(chǎng)擇字段。明日門職靜歷部人年學(xué)在以下區(qū)域間拖通字段:闡回凹100%匕U吐.5、按年份統(tǒng)計(jì)各部門入職人數(shù)每年各部門入職人數(shù)情況BC文件開始插入支面布局公式故據(jù)由閱相圜升發(fā)工具選直設(shè)計(jì)審碎口國(guó)£123456781011121.314151617數(shù)據(jù)透視表3若要生成報(bào)表，請(qǐng)從"數(shù)據(jù)透視表字段列表”中選擇字段。/Sh”t2jEhe色

16、ShJWmi蠹據(jù)透程表宇佳列表選擇要第加到報(bào)表的字段：B邰門匚心疆目翱學(xué)歷在以下區(qū)域間拖通字段：7輾表篩選口列標(biāo)簽另行粽笠£數(shù)值哩二.推遲布局更新畫I3凹too%Q更行0©附：HR工作中常用分析公式1 .【新進(jìn)員工比率】=已轉(zhuǎn)正員工數(shù)/在職總?cè)藬?shù)2 .【補(bǔ)充員工比率】=為離職缺口補(bǔ)充的人數(shù)/在職總?cè)藬?shù)3 .【離職率】（主動(dòng)離職率/淘汰率=離職人數(shù)/在職總?cè)藬?shù)=離職人數(shù)/（期初人數(shù)+錄用人數(shù)）X100%4 .【異動(dòng)率】=異動(dòng)人數(shù)/在職總?cè)藬?shù)5.【人事費(fèi)用率】（人均人工成本* 總?cè)藬?shù)） / 同期銷售收入總數(shù)6. 【招聘達(dá)成率】=（報(bào)到人數(shù)+待報(bào)到人數(shù)）/（計(jì)劃增補(bǔ)人數(shù)+臨時(shí)增補(bǔ)人數(shù)）7. 【人員編制管控率】=每月編制人數(shù)/在職人數(shù)8. 【人員流動(dòng)率】=（員工進(jìn)入率+離職率）/29. 【離職率】=離職人數(shù)/（期初人數(shù)+期末人數(shù)）/2）10. 【員工進(jìn)入率】=報(bào)到人數(shù)/期初人數(shù)11. 【關(guān)鍵人才流失率】=一定周期內(nèi)流失的關(guān)鍵人才數(shù)/公司關(guān)鍵人才總數(shù)12. 【工資增加率】=（本期員工平均工資上期員工平均工資）/上期員工平均工資13. 【人力資源培訓(xùn)完成率】=周期內(nèi)人力資源培訓(xùn)次數(shù)/計(jì)劃總次數(shù)14. 【部門員工出勤情況】=部門員工出勤人數(shù)/部門員工總數(shù)15. 【薪酬