史上最全丨HR六大模塊必備的50個公式&27個Excel函數匯總（內含案例解析）

在HR的日常工作中，經常要計算很多數據，諸如如入職率、離職率、出勤率等。每個數據的計算方式都不複雜，但如果類似的數據有幾十個呢？想必很容易產生混淆，不過沒有關係，今日推文為大家整理了約50個HR常用的計算公式&27個Excel人力資源管理函數，涵蓋了HR日常工作中的主要的兩大板塊，歡迎大家收藏以備不時之需。

HR六大模塊必備的50個公式&27個管理函數大匯總

1

招聘分析常用計算公式

情境：假設公司月初員工為100人，本月招聘，前來應聘30人，成功錄取5名，本月離職員工3名。

1、【招聘入職率】：

應聘成功入職的人數÷應聘的所有人數×100％

例如：應聘30人，錄取5人，則5÷30×100%=入職率：16.6%

2、【月平均人數】：

（月初人數＋月底人數）÷2

例如：月初員工100人，月底員工102人，則（100+102）÷2，本月平均人數101人

3、【月員工離職率】：

整月員工離職總人數÷月平均人數×100％

例如：本月離職3人，月平均人數101人，則3÷101×100%，本月員工離職率為：2.9%

4、【月員工新進率】：

整月員工新進總人數÷月平均人數×100％

例如：本月新進員工5人，月平均人數101人，則5÷101×100%，本月員工新進率為：4.9%

5、【月員工留存率】：

月底留存的員工人數÷月初員工人數×100％

例如：月底員工102人，月初員工100人，則102÷100×100%，本月員工留存率為：102%

6、【月員工損失率】：

整月員工離職總人數÷月初員工人數×100％

例如：本月離職員工3名，月初員工100名，則3÷100×100% ，本月員工損失率為：3%

7、【月員工進出比率】：

整月入職員工總人數÷整月離職員工總人數×100％

例如：本月入職員工5名，本月離職員工3名，則5÷3×100% ，本月員工進出比率為：166%

2

考勤常用的統計分析公式

1、【個人出勤率】：

出勤天數÷規定的月工作日×100％

例如：出勤20天，規定的月工作日為23天，則20÷23×100%，個人出勤率為：86.9%

例如：當月加班6個小時，當月總工作時數為184小時（23天*8小時），則8÷184×100% ，加班強度比率：3.2%

例如：當天出勤員工98人，當天企業總人數102人，則98÷102×100%，人員出勤率為：96%

例如：當天缺勤人數4人，當天企業總人數102人，則4÷102×100%，人員缺勤率為：3.9%

3

常用工資計算、人力成本分析公式

1、【月薪工資】：

月工資額÷21.75天×當月考勤天數

2、【月計件工資】：

計件單價×當月所做件數

3、【平時加班費】：

月工資額÷21.75天÷8小時×1.5倍×平時加班時數

4、【假日加班費】：

月工資額÷21.75天÷8小時×2倍×假日加班時數

5、【法定假日加班費】：

月工資額÷21.75天÷8小時×3倍×法定假日加班時數

6、【直接生產人員工資比率】：

直接生產人員工資總額÷企業工資總額×100％

7、【非生產人員工資比率】：

非生產人員工資總額÷企業工資總額×100％

8、【人力資源費用率】：

一定時期內人工成本總額÷同期銷售收入總額×100％

9、【人力成本占企業總成本的比重】：

一定時期內人工成本總額÷同期成本費用總額×100％

10、【人均人工成本】：

一定時期內人工成本總額÷同期同口徑職工人數

4

培訓統計分析公式

5

HR常用基礎公式匯總

1、【新晉員工比率】：

已轉正員工數÷在職總人數

2、【補充員工比率】：

為離職缺口補充的人數÷在職總人數

3、【離職率】：

（主動離職率÷淘汰率）＝離職人數÷在職總人數

4、異動率：

異動人數÷在職總人數

5、人事費用率：

（人均人工成本*總人數）÷同期銷售收入總數

6、招聘達成率：

7、人員編製管控率：

每月編製人數÷在職人數

8、人員流動率：

（員工進入率+離職率）÷2

10、員工進入率：

報到人數÷期初人數

11、 離職率：

離職人數÷（期初人數+錄用人數）×100％

12、每天工資：

月固定工資÷21.75天

13、當月應得工資：

每天工資x當月有效工作天x當月實際工作天數調整比例

● 當月應工作天數：當月自然日天數–當月休息日天數

● 當月有效工作日：當月應工作天數–全無薪假期

● 當月實際工作天數調整比列：21.75天÷當月應工作天數

● 作為生產型企業，還會要算到勞動生產率：勞動生產率=銷售收入÷總人數

6

成本效用評估

1、總成本效用：錄用人數÷招聘總成本

2、招募成本效用：應聘人數÷招募期間的費用

3、選拔成本效用：被選中人數÷選拔期間的費用

4、人員錄用效用：正式錄用人數÷錄用期間的費用

5、招聘收益成本比：所有新員工為組織創造的價值÷招聘總成本

7

數量評估

1、錄用比：錄用人數÷應聘人數x100%

2、招聘完成比：錄用人數÷計劃招聘人數x100%

3、應聘比：應聘人數÷計劃招聘人數x100%

4、工資計算：月工資÷21.75x實際工作天數（不計算周六日）

5、加班率：總加班時間÷總出勤時間

6、直接間接人員比例：直接人員÷間接人員

8

制度工作時間的計算

1、年工作日：365天-104天（休息日）-11天（法定節假日）＝250天

2、季工作日：250天÷4季＝62.5天÷季

3、月工作日：250天÷12月＝20.83天÷月

4、工作小時數的計算：以月、季、年的工作日乘以每日的8小時

9

日工資、小時工資的折算

按照《勞動法》第五十一條的規定，法定節假日用人單位應當依法支付工資，即折算日工資、小時工資時不剔除國家規定的11天法定節假日。

據此，日工資、小時工資的折算為：

1、日工資： 月工資收入÷月計薪天數

2、小時工資： 月工資收入÷（月計薪天數×8小時）

3、月計薪天數：（365天-104天）÷12月＝21.75天

PS：2000年3月17日勞動保障部發布的《關於職工全年月平均工作時間和工資折算問題的通知》（勞社部發〔2000〕8號）同時廢止。

10

社保計算方式

根據最新的養老金計算辦法,職工退休時的養老金有兩部分組成：養老金=基礎養老金+個人帳戶養老金

1、個人賬戶養老金：個人賬戶儲存額÷計發月數（50歲為195、55歲為170、60歲為139，不再統一是120了）

2、基礎養老金：（全省上年度在崗職工月平均工資+本人指數化月平均繳費工資）÷2×繳費年限×1% =全省上年度在崗職工月平均工資（1+本人平均繳費指數）÷2×繳費年限×1%

3、本人指數化月平均繳費工資：全省上年度在崗職工月平均工資×本人平均繳費指數

4、個人養老金：基礎養老金+個人賬戶養老金=基礎養老金+個人賬戶儲存額÷139

5、在上述公式中可以看到，在繳費年限相同的情況下，基礎養老金的高低取決於個人的平均繳費指數，個人的平均繳費指數就是自己實際的繳費基數與社會平均工資之比的歷年平均值。低限為0.6，高限為3。

因此，在養老金的兩項計算中，無論何種情況，繳費基數越高，繳費的年限越長，養老金就會越高。

6、平均繳費指數：你的繳費基數/當地社平工資，假設你選擇社平工資的100%那個檔次為你的基數，那該指數就是1，你選擇60%，就是0.6

1

IF函數

函數用途

此函數又稱條件函數，改函數用於對目標區域進行判斷，返回真假邏輯值，據此輸出相應的結果。

函數語法

IF（logical test，value if true，value if false）

IF（條件表達式（看也是一個比較式或邏輯式），當條件表達式的邏輯值為TRUE時的返回值 ，當條件表達式的邏輯值為FALSE是的返回值）

函數說明

利用IF函數可以構造IF函數嵌套來完成複雜的功能，但是IF函數最多只能嵌套7層。

IF函數還可以喝數組相結合，藉此能夠進行工作所需的計算。

函數簡單示例

現假設在A1單元格里輸入數字15，在B1單元格里進行如下的操作，觀察出現的結果。

RANK函數

函數用途

次函數的功能是在某個區域對目標數字進行排位計算。

函數語法

RANK(number，ref，order）

RANK(目標數字，要進行排行的區域，何種排位的方式（order為0或者省略，系統會按降序排列目標數字排位；相反，當order不是0，系統會按升序排列對目標數字排位。）

函數說明

在應用RANK函數的過程中，當存在重複時數會影響後面的排位。

函數簡單示例

先在假設A1到A7單元格里分別輸入如下數字：3、1、2、3、5、7、6.在B1單元格里進行如下的操作，觀察出現的結果。

員工銷售獎金計算表

3

VLOOKUP函數

函數用途

此函數的功能是在給定區域的首列里查找目標數值，然後返回目標數值所在行里某一列的相關數值。

函數語法

VLOOKUP（lookup＿value，table＿array，col＿index＿num，range＿lookup）

VLOOKUP（目標數值，給定區域，制定返回給定區域中某一列的序號，用來規定VLOOKUP函數查找類型）

函數說明

range＿lookup為TRUE或省略，VLOOKUP函數將進行近似匹配查找；range＿lookup為FALSE或0，VLOOKUP函數將進行精確匹配查找。

range＿lookup為TRUE或省略時，給定區域內首列值要以生序排列。

注意要查找的目標數值的大小應該是比查找區域內的最小值大，否則應用VLOOKUP函數時會顯示錯誤值。

函數簡單示例

在A1至A5單元格里分別輸入了「1」、「4.3」、「2.9」、「5」和」2「，在B1至B5單元格里分別輸入了「第一名」、「第四名」、「第三名」、「第五名」和「第二名」，在C1隻C5單元格里分別輸入了「張」、「李」、「陳」、「王」和「滕」。接著在D5單元格里輸入如下公式，觀察出現結果。

4

LOOKUP函數

函數用途

此函數的功能是事先確定兩個單行或單列區域，將其中的一個作為查找區域，然後進行目標數值查找，最後返回目標數值所在列或行與另一個區域香蕉的單元格內容。

LOOKUP函數具有兩種語法形式，即向量形式和數組形式。

函數語法

向量形式：LOOKUP（lookup＿value，lookup＿vector，result＿vector）

LOOKUP（目標數值，查找區域，輸出結果的區域（其構成和查找區域相同，即同為單行或單列）

數組形式：LOOKUP（lookup＿value，array）

LOOKUP（查找的目標數值，查找區域）

函數說明

不同於VLOOKUP函數實現需要規定近似或精確匹配查找，LOOKUP函數默認為精確匹配查找，當查找不到目標數值是LOOKUP函數開始近似匹配查找。

函數簡單示例

向量形式。現假設在A1至A5單元格里分別輸入了「1」、「4.3」、「2.9」、「5」和」2「，在B1至B5單元格里分別輸入了「第一名」、「第四名」、「第三名」、「第五名」和「第二名」，在C1隻C5單元格里分別輸入了「張」、「李」、「陳」、「王」和「滕」。接著在D5單元格里輸入如下公式，觀察出現結果。

數組形式。數組中的值必須以升序順便放置；…，-2,-1,0,1,2，…；A-Z;FALSE,TRUE.否則LOOKUP函數無法提供正確的值。現在在D5單元格里輸入如下公式，觀察出現結果。

5

HLOOKUP函數

函數用途

此函數的功能是在給定區域的首行里查找目標數值，然後返回目標數值所在列某一行的相關值。顯然該函數與VLOOKUP函數相似，無非一個是在首行里查找，一個是在首列里查找。

類似於LOOKUP函數，HLOOKUP函數也具有兩種語法形式，即向量形式和數組形式。下面簡答介紹一下向量形式的函數語法，至於數組形式的函數語法，可以參閱LOOKUP函數的數組形式函數語法。

函數語法

HLOOKUP（lookup＿value，table＿array，row＿index＿num,range＿lookup）

HLOOKUP（要查找的目標數值，給定區域{系統將在給定區域的首行中查找目標數值}，返回給定區域中某一行的序號）

函數說明

range＿lookup為TURE或省略，HLOOKUP函數將進行近似匹配查找，range＿lookup為FALSE或0，HLOOKUP函數將進行精確匹配查找。

注意要查找的目標數值的大小應該是在查找區域數值的範圍內，否則應用HLOOKUP函數是會顯示錯誤值。

函數簡單示例

現假設在A1至E1單元格里分別輸入了「1」、「4.3」、「2.9」、「5」和」2「，在A2至E2單元格里分別輸入了「張」、「李」、「陳」、「王」和「滕」。接著在A3單元格里輸入如下公式，觀察出現結果。

6

DATEDIF函數

函數用途

此函數的功能計算起始日期和截止日期之間的天數、月數或年數。

函數語法

DATEDIF（start＿date，end＿date，unit）

DATEDIF（起始日期，截止日期，返回類型）

函數簡單示例

在B3單元格里輸入下列公式，，觀察出現結果。

加班統計表

7

TEXT函數

函數用途

此函數的功能是一句需要目標數字轉換為指定的文本格式。

函數語法

TEXT（value，format＿text）

TEXT（目標數值（可以為數字可以為計算結果），需要設定的文本格式）

函數簡單示例

在A2和A3單元格里分別輸入「工業」和「服務業」，在B2和B3單元格里分別輸入「45%」和「1000億」。然後在C4和C5單元格里分別輸入下面表格左側的公式，按鍵確認，觀察出現結果。

8

ROUND函數

函數用途

此函數的功能是設置指定位數，按此位數對數字進行四捨五入去整，然後返回相應的結果。

函數語法

ROUND（number，num＿digits）

TEXT（目標數字，指定的位數（系統按此位數進行四捨五入））

函數說明

如果num＿digits小於、等於或者大於0，都會有不同的結果。

函數簡單示例

在Excel任意一個單元格輸入以下公式，觀察出現的結果。

9

YEAR函數

函數用途

此函數的功能是返回給定日期所屬的年份。

函數語法

函數簡單示例

在A1單元格里輸入「2007-8-2」，在D5單元格里輸入以下公式，觀察出現的結果。

10

MONTH函數

函數用途

此函數的功能是返回給定日期所屬的月份。

函數語法

MONTH（serial＿number）

MONTH（給定日期）

函數說明

應用MONTH函數時，應注意給定日期的輸入格式。

函數簡單示例

在A1單元格里輸入「=DATE(2007,8,2)」，在D4單元格里輸入以下公式，觀察出現的結果。

11

DAY函數

函數用途

此函數的功能是返回給定日期的具體天數。

函數語法

函數簡單示例

在A1單元格里輸入「2007-9-2」，在D5單元格里輸入以下公式，觀察出現的結果。

12

NOW函數

函數用途

此函數的功能是顯示系統當前的時間。

函數語法

NOW

函數簡單示例

在Excel任意一個單元格里輸入以下公式，觀察出現的結果。

13

TODAY函數

函數用途

此函數的功能是顯示系統當前日期。

函數語法

TODAY

函數簡單示例

14

NETWORKDAYS函數

函數用途

此函數的功能是計算兩個給定參數間的工作日數值。

函數語法

NETWORKDAYS（start＿date，end＿date，hoildays）

NETWORKDAYS（起始時間，終止時間，假日）

函數說明

應用NETWORKDAYS函數時應注意給定日期的輸入格式。

操作NETWORKDAYS函數時，若該函數不可用，則顯示#NAME？錯誤值，此時，需分要載入相應的宏，具體方法如下：依次單擊菜單「工具」→「載入宏」淡出一個對話框，勾選「分析工具庫」複選框，然後單擊「確定」按鈕。

函數簡單示例

在A1單元格里輸入「=DATE(2007,8,2)作為開始日期，在A2單元格里輸入「=DATE(2008,3,2)」作為結束日期，在A3單元格里輸入「=DATE(2007,10,13)」作為假日在D4單元格里輸入以下公式，觀察出現的結果。

15

EOMONTH函數

函數用途

此函數的功能是給定一個起始日期，再輔以一個指定數字，將一個指定數字，將起始日期的月份向前或向後撥動該數字大小距離，最後返回所得月份的最後一天日期。

函數語法

EOMONTH（start＿date，months）

EOMONTH（起始日期，制定數字（正數表示向未來移動的月數，負數表示向過去移動的月數））

函數說明

應用EOMONTH函數時應注意給定日期的輸入格式。

操作EOMONTH函數一樣，若EOMONTH函數第一次不能使用，也要進行載入宏的操作。

函數簡單示例

在A1單元格里輸入「=DATE(2007,8,2)，在D4單元格里輸入以下公式，觀察出現的結果。

註：若顯示的結果為「39386」，此時則可通過調整單元格格式來獲得日期格式。

16

WORKDAY函數

函數用途

此函數的功能是返回某一個日期前後相隔制定工作日的日期。

函數語法

WORKDAY（start＿date，days，holiday）

WORKDAY（起始日期，自起始日期起推算的天數（當該值為正值時意味著將來的日期，當該值為負值時意味著過去的日期，並且推算中是不包含共假日的），要剔除的日期（可以自定義的日期，也可以定義公休假日）

函數說明

應用WORKDAY函數時應注意給定日期的輸入格式。

操作WORKDAY函數一樣，若WORKDAY函數第一次不能使用，也要進行載入宏的操作。

函數簡單示例

在B1單元格里輸入「=DATE(2007,8,2)，在B2單元格里輸入"179"作為工作天數，在B3單元格里輸入「=DATE(2007,10,17)」作為假日；在D4單元格里輸入以下公式，觀察出現的結果。

註：若顯示的結果為「39547」，此時則可通過調整單元格格式來獲得日期格式。

17

TIME函數

函數用途

此函數的功能是返回某一個特定時間的小數值。

函數語法

TIME（hour，minute，second）

TIME（小時，分鐘，秒）【三個數值都在在區間（0,32767）內取值】

函數簡單示例

在E1單元格里輸入「8」作為小時，在F1單元格里輸入「12」作為分鐘，在G1單元格里輸入「0」作為秒；在G4單元格里輸入以下公式，觀察出現的結果。

單擊任意一個單元格，然後輸入表格里的任意一個公式，按鍵確認，系統將自動顯示對應日期

人事信息數據表

18

TEXT函數

函數用途

函數語法

函數簡單示例

鍵確認，觀察出現結果。

19

MID函數

函數用途

此函數的功能是在目標數字元串中指定一個開始位置，按設定的數值返回該字元串中的相應數目字元內容。

函數語法

MID（text，start＿num，num＿chars）

MID（目標字元串，字元串開始的位置{通常start＿num是從字元串的首個字元編號，依次為1、2、3...start＿num有取值範圍，不能大於字元串的長度，不能小於1}，設定的數目{MID函數將按次數目返回相應的字元個數，顯然該數值不為負，否則函數將輸出錯誤值。}）

函數簡單示例

在A2輸入「wonderful」然後在B3單元格里輸入下列公式，觀察出現結果。

20

LEN函數

函數用途

此函數的功能是計算目標字元中的字元數。

函數語法

LEN（text）

LEN（目標字元串）

函數說明

空格也視為字元，加到字元串的字元數中。

函數簡單示例

在A3單元格里輸入「wonderful」，在A4單元格里輸入「Excel word」然後在B4單元格里輸入下列的公式，觀察出現結果。

21

LEFT函數

函數用途

此函數的功能是從字元串的左側開始，按照指定的數值返回相應的字元內容。

函數語法

LEFT（text，num＿chars）

LEFT（目標字元串，指定的數值{該數值必須大於等於0}）

函數簡單示例

在A3單元格里輸入「wonderful」，然後在B4單元格里輸入下列的公式，觀察出現結果。

22

RIGHT函數

函數用途

此函數的功能是從字元串的右側開始，按照指定的數值返回相應的字元內容。該函數恰好與LEFT函數的功能相反。

函數語法

RIGHT（text，num＿chars）

RIGHT（目標字元串，指定的數值{該數值必須大於等於0}）

函數簡單示例

在A3單元格里輸入「wonderful」，然後在B5單元格里輸入下列的公式，觀察出現結果。

23

MOD函數

函數用途

此很熟的功能是求解對目標數值除以指定數后的餘數，餘數的符號和除數相同。

函數語法

MOD（number，divisor）

MOD（目標數值{作為被除數}，指定數{作為除數}）

函數說明

當指定數為0時，系統將顯示錯誤值#DIV/0!

函數簡單示例

在A1、B1和C1單元格里分別輸入數字「3」、「2」和「-2」，再在C5單元格里輸入下列任意公式，觀察出現的結果。

24

TODAY函數

函數用途

函數語法

TODAY

函數簡單示例

25

DATEDIF函數

函數用途

函數語法

函數簡單示例

ExcelD 字元查找和替換技術

在實際工作中，有時可能需要從姓名字元串中提取姓或者名（如外籍員工的姓名等），此時需要用到Excel的字元查找和替換技術。

姓名拆分

在A2單元格里輸入「Michael jordan」（Michael和jordan之間有一個空格），此時需要將「姓」和「名」拆分。

單擊B4單元格，然後輸入如下公式，觀察出現的結果。

替換字元

在C2單元格里輸入「Excelhome中文論壇「，現在需要將「論壇」替換為「社區」，可以借組SUBSTITUTE函數來實現。單擊D4單元格，然後輸入如下公式，觀察出現的結果。

26

FIND函數

函數用途

此函數的功能是查找字元串中首字元編號為1，接著對餘下的字元按順序依次進行編號，然後在查找字元串中查找目標字元，並且返回目標字元在查找字元串中的編號。

函數語法

FIND（find＿text，within＿text，start＿num）

FIND（目標字元，查找字元串，查找字元串中開始查找的起始位置{取值範圍要大於0，港市不能大於查找字元串的長度}）

函數說明

FIND 函數區分大小寫

函數簡單示例

在A5單元格里輸入「substitute」，然後在B6單元格里輸入下列公式，，觀察出現結果。

27

SUBSTITUTE函數

函數用途

此函數的功能是將目標字元或字元串替換到需要被替換的字元串中。

函數語法

SUBSTITUTE（text，old＿text，new＿text，instance＿num）

SUBSTITUTE（存放需要被替換的字元串的單元格位置，需要被替換的元字元或字元串，用於替換的元字元或字元串，替換字元串中第幾個字元或字元串{若不指明，將會替換所有符合條件的原字元或字元串}）

函數簡單示例

在A8單元格里輸入「杭州西湖」，然後在B9單元格里輸入下列公式，，觀察出現結果。

註：以上公式可能會隨著政策調整，或每個公司企業的實際情況也有所差異，故上述內容僅供大家參考之用，如覺得實用也歡迎你戳戳手指分享給你的Ta。

- END -

文末推薦給需要惡補HR知識的讀者朋友一本新書

關於如何做最好效的HR小編也只推薦這一本

希望輕戳書影或者閱讀原文關注Ta