你知道嗎?我們的身份證就是一個信息寶庫,包含了很多的私密信息籍貫、出生日期、性别統統都在裡面了!
今天,就告訴你 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技能,讓你在職場中如有神助。
,更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!