人資HR工作必備的10個Excel函數公式
HR人資在工作中需要用到Excel的地方太多了，我根據平時的回饋，
1 從身份證號碼提取生日
2 根據身份證號碼計算退休日期
3 檢查身份證號碼是否重複
4 根據身份證號碼判斷性別
5 根據身份證號碼實現生日提醒
6 根據分項得分和所占權重計算KPI績效最後得分
7 去掉一個最高分、去掉一個最低分後再計算平均分
8 個人所得稅計算
9 統計兩個日期之間的工作日天數
10 根據工齡計算年假天數的4種方法
這些技術在手，可以10倍+提升工作效率，推薦收藏哦~
1 從身份證號碼提取生日
案例場景截圖如下（黃色區域輸入公式）
=TEXT(MID(A2,7,8),"0-00-00")
2 根據身份證號碼計算退休日期
案例場景截圖如下（黃色區域輸入公式）
=DATE(MID(A2,7,4)+55+MOD(MID(A2,17,1),2)*5,MID(A2,11,2),MID(A2,13,2))
3 檢查身份證號碼是否重複
案例場景截圖如下（黃色區域輸入公式）
=IF(COUNTIF($B$2:$B$11,B2&"*")>1,"重複","")
4 根據身份證號碼判斷性別
案例場景截圖如下（黃色區域輸入公式）
=IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女")
5 根據身份證號碼實現生日提醒
案例場景截圖如下（黃色區域輸入公式）
=TEXT(7-DATEDIF(--TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2.2),8),"0-00-00"),NOW()+7,"yd"),"0天后生日;;今天生日")
6 根據分項得分和所占權重計算KPI績效最後得分
案例場景截圖如下（黃色區域輸入公式）
=SUMPRODUCT(B$2:E$2*B3:E3)
7 去掉一個最高分、去掉一個最低分後再計算平均分
案例場景截圖如下（黃色區域輸入公式）
=TRIMMEAN(B2:H2,2/COUNT(B2:H2))
8 個人所得稅計算
個人所得稅計算由於涉及超額累進，所以困擾很多人，今天咱們來介紹1個公式輕鬆搞定。
我再上個圖，展示一下在工資表中如何計算個人所得稅
=ROUND(MAX((D2-3500)*{3;10;20;25;30;35;45}%-5*{0;21;111;201;551;1101;2701},0),2)
9 統計兩個日期之間的工作日天數
一提到工作日計算，很多HR都皺緊了眉頭，因為各種崗位員工的排班不同，休息日不同，再加上員工的請假和各種節假日的調休......99%的HR都是在手動計算工作日中苦苦煎熬。
看完下面這個示例的解決方案，以上所有問題一掃而光！
上圖所示為按照週六周日雙休的企業中，加上自訂節假日的工作日演算法
首先定義名稱，按打開名稱管理器創建自訂名稱holidays=OFFSET(Sheet1!$E$2,,,COUNTA(Sheet1!$E:$E)-1)
然後在C2輸入公式：=NETWORKDAYS.INTL(A2,B2,1,holidays)
這樣就輕鬆計算出起始日期和終止日期之間的工作日天數了
為了大家能夠舉一反三，我再多囉嗦幾句，解析一下原理。
NETWORKDAYS.INTL函數的語法結構如下：
=NETWORKDAYS.INTL(起始日期,終止日期,週末日weekend,自訂節假日holidays)
關鍵點在於第三參數和第四參數。
關於第3參數weekend第二種表達方式的幾點說明：
1、weekend 的表達方式2是一個長度為7的字串
2、該字串中的每個字元代表一周中的一天，從星期一開始
3、1 代表休息日，0 代表工作日
4、該字串中只允許使用字元 1 和 0
關於第4參數holidays的幾點說明：
1、是一個包含一個或多個日期的可選集合，這些日期將作為節假日不參與工作日個數統計
2、可以是包含日期的儲存格區域，也可以是區域的引用，還可以是代表日期序列值的陣列常量
3、其中的日期或序列值順序可以任意打亂，不影響統計。
希望能夠幫到大家輕鬆應對各種情況的工作日計算！
10 根據工齡計算年假天數的4種方法
【職工帶薪年休假條例】
根據職工累計工作年限而定：
1、已滿1年不滿10年的，年休假5天;
2、已滿10年不滿20年的，年休假10天;
3、已滿20年的，年休假15天。
案例場景截圖如下（黃色區域輸入公式）
方法1=IF(B2
方法2=LOOKUP(B2,{0,1,10,20},{0,5,10,15})
方法3=5*MIN(3,ROUNDUP(B2/9.9,0))
方法4=5*MIN(3,CEILING(B2/9.9,1))
End.
作者：李銳（中國統計網特邀認證作者）
中國統計網，是國內最早的大資料學習網站，歡迎關注！=ROUND(MAX((D2-3500)*{3;10;20;25;30;35;45}%-5*{0;21;111;201;551;1101;2701},0),2)
9 統計兩個日期之間的工作日天數
一提到工作日計算，很多HR都皺緊了眉頭，因為各種崗位員工的排班不同，休息日不同，再加上員工的請假和各種節假日的調休......99%的HR都是在手動計算工作日中苦苦煎熬。
看完下面這個示例的解決方案，以上所有問題一掃而光！
上圖所示為按照週六周日雙休的企業中，加上自訂節假日的工作日演算法
首先定義名稱，按打開名稱管理器創建自訂名稱holidays=OFFSET(Sheet1!$E$2,,,COUNTA(Sheet1!$E:$E)-1)
然後在C2輸入公式：=NETWORKDAYS.INTL(A2,B2,1,holidays)
這樣就輕鬆計算出起始日期和終止日期之間的工作日天數了
為了大家能夠舉一反三，我再多囉嗦幾句，解析一下原理。
NETWORKDAYS.INTL函數的語法結構如下：
=NETWORKDAYS.INTL(起始日期,終止日期,週末日weekend,自訂節假日holidays)
關鍵點在於第三參數和第四參數。
關於第3參數weekend第二種表達方式的幾點說明：
1、weekend 的表達方式2是一個長度為7的字串
2、該字串中的每個字元代表一周中的一天，從星期一開始
3、1 代表休息日，0 代表工作日
4、該字串中只允許使用字元 1 和 0
關於第4參數holidays的幾點說明：
1、是一個包含一個或多個日期的可選集合，這些日期將作為節假日不參與工作日個數統計
2、可以是包含日期的儲存格區域，也可以是區域的引用，還可以是代表日期序列值的陣列常量
3、其中的日期或序列值順序可以任意打亂，不影響統計。
希望能夠幫到大家輕鬆應對各種情況的工作日計算！
10 根據工齡計算年假天數的4種方法
【職工帶薪年休假條例】
根據職工累計工作年限而定：
1、已滿1年不滿10年的，年休假5天;
2、已滿10年不滿20年的，年休假10天;
3、已滿20年的，年休假15天。
案例場景截圖如下（黃色區域輸入公式）
方法1=IF(B2
方法2=LOOKUP(B2,{0,1,10,20},{0,5,10,15})
方法3=5*MIN(3,ROUNDUP(B2/9.9,0))
方法4=5*MIN(3,CEILING(B2/9.9,1))
End.
作者：李銳（中國統計網特邀認證作者）
中國統計網，是國內最早的大資料學習網站，歡迎關注！