Excel以其強大的數據分析、引用、計算、統計等功能在單位人事工資管理中得到了廣泛應用，特别是在工作人員衆多的企事業可以說是人事工資管理工作不二的選擇。由于長期從事人事工資管理工作，在實踐中邊學習邊應用，許多公式、函數用過後就很容易忘記了，為便于保存并有利于指導今後工作，現将一些基本的操作記錄如下。如對同仁有所助益，也不妄此舉！

一、不可不重視的身份證

身份證是一個人的唯一标識（不排除重複問題），在人事管理具有不可替代的作用，特别是在人員衆多、姓名可能出現重複的情況下尤其重要（這是多年的經驗），所以在建立人事工資管理基礎表格的時候要作為重要信息反複核對清楚。同時，一個正确的身份證号在人事過程中可提取很多重要信息，避免手工輸入引起的錯誤。

（一）身份證号提取性别信息

1.在18位身份證号碼中，倒數第二位是性别代碼，偶數為女性，奇數為男性，根據這個特性，我們可以在“性别”列設置如下函數：

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

函數釋義：如果（if）身份證号（C2）的第十七位是奇數（MOD），性别為“男”，否則為“女”。

如果你需要套用這個函數，隻需要将A2更改為你數據中對應的身份證列與行即可。

圖片1

2.如果在衆多身份證号中有15位的，也有18位的則要考慮15位的身份證号是最後一位代表性别信息的，應輸入以下函數進行判斷：

=IF(LEN(C2)=15,IF(MOD(RIGHT(C2,1),2)=1,"男","女"),IF(LEN(C2)=18,IF(MOD(MID(C2,17,1),2)=1,"男","女"),"号碼有誤"))

函數釋義：如果身份證為15位（LEN(C2)=15），則取身份證倒數第一位（RIGHT(C2,1)）并判斷是否為奇數（MOD(RIGHT(C2,1),2)=1），結果為真，則輸出結果為“男”，否則為“女”；如果身份證号為18位，解釋同第1條。這裡同時加了對身份證号位數是否正确的判斷，如果身份證号既不為15，也不為18位，則認為“号碼有誤”。

圖片2

（二）身份證号提取籍貫信息

如果你收藏了全國籍貫查詢表，你還可以通過在“籍貫”列輸入以下公式，實現個人籍貫信息的自動查找填充：

=VLOOKUP(LEFT(C2,6),全國籍貫查詢表!A2:E3506,5,TRUE)

函數釋義：提取身份證的前6位（left(C2,6)）,從“全國籍貫查詢表”的A2列到E3506行這一數據域中查找（VLOOKUP）與身份證前6位相對應的第五列（5）籍貫信息。

注：1.查詢數據域必須将查詢關鍵詞（如：身份證前6位）設為第一列，但不一定是查數據表的第一列（如在身份證對照信息在全國籍貫查詢表的第B列，則選擇數據域時就變成B2：E3506），而後才能确定查找引用的是第幾列的數據（如：第5列，第幾列隻能從數據域的第1列起算）。

2.參數：“TRUE”是一個參數設置，分為大緻匹配（FALSE）精确匹配（TRUE）,如果忽略不填則默認為精确匹配。

圖片3

（三）身份證号提取出生日期

出生日期是人事管理中的重要信息也是身份證号内含的重要信息，這一信息從身份證号第7位起到第14位按照“YYYYMMDD”排列，這為我們提取出生日期提供了幫助，隻要在出生日期中輸入以下公式即可從身份證号中獲得：

1.簡單公式

=MID(C2,7,8)

函數釋義：從身份證号（C2）的第7位起提取後8位數據，出現的數據就是“YYYYMMDD”的形式，且不能通過“數字”格式設置改變其顯示結果。

圖片4

2.顯示“年”“月”“日”

=DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))

函數釋義：從身份證号（C2）第7位起取4位為出生年（MID(C2,7,4)），從第11位起取2位為出生月（MID(C2,11,2)），從第13位起取2位為出生日（MID(C2,13,2)），然後把這三組數字組成日期格式（DATE）。

注：信息數據提取後如果顯示的不是你想要的數值，主要是出生日期的格式設置不正确，還需要打開出生日期“設置單元格格式”裡的“數字”将其設為你想要的格式，一般有“YYYY年MM月DD日”“YYYY-MM-DD”或者隻顯示“年”和“月”等格式，如果你還不滿意還可以能過“自定義”将其定義為你想要的格式。（如我們把這個顯示格式選擇為“YYYY-MM-DD”格式顯示。）

圖片5

3.考慮身份證号位數

如果在從多工作人員中有個别人員還是舊的15位編号的身份證則需要輸入以下函數進行判斷：

=IF(LEN(C2)=15,DATE(MID(C2,7,2),MID(C2,9,2),MID(C2,11,2)),IF(LEN(C2)=18,DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2)),"号碼有錯"))

函數釋義：如果身份證号為15位（LEN(C2)=15），則從身份證号（C2）的第7位起取2位為出生年（MID(C2,7,2)），從第9位起取2位為出生月（MID(C2,9,2)），從第11位起取2位為出生日（MID(C2,11,2))，然後組合為出生日期；如果身份證号為18位，則從第7位起取4位為出生年，其餘相同；如果不符合15位、18位的規定，則顯示“号碼有錯”。（如我們把這一個出生日期組合方式自定義為“YYYY.MM”

圖片6

在實踐中如果想運用農曆出生日期（生辰八字），還可以依仗EXCEL表格的萬年曆去查找，或者運用一定的公式、函數進行計算，但在人事工資管理中實際意義不大，在這裡不再一一贅述。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!