100個hr用得上的excel技巧（人員信息整理簡直是個大坑）

2023-04-20 18:25:57 1

你知道嗎？我們的身份證就是一個信息寶庫，包含了很多的私密信息籍貫、出生日期、性別統統都在裡面了！

今天，就告訴你 HR 是如何利用身份證號，自動提取各種各樣的信息。這個 5 個函數公式不需要搞懂全部原理，但一定要會套用。

公司經常搞搞生日會還是很不錯的，那本周有哪幾位員工過生日？這事肯定得問 HR。怎麼登記每個人的生日呢？以前我真幹過一個一個手工輸入的蠢事兒。

現在？我都用身份證號碼直接生成：身份證第 7 起 8 位數就表示出生日期呀！

=--TEXT(MID(身份證號碼所在單元格,7,8),"0000-00-00")

02 自動算年齡

HR 要統計公司平均人員平均年齡，按年齡段統計分析人員結構等等，年齡從哪來？靠身份證就能算出來。

=YEAR(NOW)-MID(身份證號碼所在單元格,7,4)

03 自動算性別

性別看似不重要，其實很有用，例如：

部門 Outing 要定多少個房間？三八婦女節禮物預算是多少？……

等等問題，都需要用到性別信息。要手工輸入嗎？NONO，同樣可以從身份證號碼直接獲取。

=IF(MOD(MID(身份證號碼所在單元格,15,3),2),"男","女")

Tips：我們可以根據身份證號碼的第17位判斷性別，如果是奇數為男性，偶數為女性。

04 自動算星座

好吧，我其實不迷信。但是，統計員工星座，其實是件很有趣的事情。例如，員工關懷可以設置為同個星座的員工一起過生日。

還是只用身份證就夠了，你只需要把公式複製粘貼進去，然後把公式所在單元格一改，就搞定。

=CHOOSE(MATCH(TRUE,MID(身份證號碼所在單元格,11,2)&"月"&MID(身份證號碼所在單元格,13,2)&"日">={"12月22日","11月22日","10月23日","09月23日","08月23日","07月23日","06月22日","05月21日","04月21日","03月21日","02月19日","01月20日","01月01日"},),"摩羯座","射手座","天蠍座","天秤座","處女座","獅子座","巨蟹座","雙子座","金牛座","白羊座","雙魚座","水瓶座","摩羯座")

05 自動算出生地（籍貫）

要想從身份證中獲取出生地，還需要另外一張工作表，用來存放地區對應的代碼信息。

從身份證中提取出行政區劃代碼，和對照表查詢匹配，就能立馬得到結果。

（搜索「行政區劃 代碼」便可從國家統計局的官網中獲得相關信息，最近數據更新至 2016年7月31日，文末提供了下載地址~）

=VLOOKUP(VALUE(LEFT(身份證號碼所在單元格,6)),出生地查詢表!B:C,2,1)

06 小心得

在做表之前，先搞清楚，哪些數據可以通過函數公式自動計算得來，就可以省下不少功夫。數據量越大，偷懶效果就越明顯喲。

做好信息表，我們還可以利用條件格式讓表格自己說話。例如，為生日加上條件格式，就能讓本月生日的員工突出顯示。

方法很簡單，選中生日數據後，依次單擊【開始】-【條件格式】-【突出顯示單元格規則】-【發生日期】-【選擇日期為本月】，【設置為自定義格式】-【字體加粗】，【填充淺橙色】-【確定】。

怎麼樣？是不是特別簡單？

私信回復關鍵詞「HR」，獲取一份人員信息表模板吧。

想學習更多Excel小技巧嗎?點擊下方專欄學習更多Excel技能，讓你在職場中如有神助。

,