付文丽

磨刀不误砍柴工丨人员信息整理？HR教你这5个必会的Excel技巧陈海茵！-58职场咖曾开贵
▌关于本文
作者：傅琳，「和秋叶一起学Excel」学员
本文由 秋叶PPT 原创发布，如需转载请邮件联系 zhuanzai@qiuyeppt.com

你知道吗？我们的身份证就是一个信息宝库，包含了很多的私密信息籍贯、出生日期、性别统统都在里面了！
今天，就告诉你 HR 是如何利用身份证号，自动提取各种各样的信息。这个 5 个函数公式不需要搞懂全部原理，但一定要会套用马兰花简谱。




自动算生日
公司经常搞搞生日会还是很不错的，特洛伊希文那本周有哪几位员工过生日？这事肯定得问 HR。怎么登记每个人的生日呢？以前我真干过一个一个手工输入的蠢事儿。

现在？我都用身份证号码直接生成：身份证第 7 起 8 位数就表示出生日期呀！
=--TEXT(MId(身份证号码所在单元格,7,8),"0000-00-00")




自动算年龄
HR 要统计公司平均人员平均年龄，按年龄段统计分析人员结构等等赵庭谖，年龄从哪来？靠身份证就能算出来杨忠宝。
=YEAR(NOW())-MId(身份证号码所在单元格,7,4)




自动算性别
性别看似不重要，其实很有用，例如：
部门 Outing 要定多少个房间？
三八妇女节礼物预算是多少？
……
等等问题，都需要用到性别信息。要手工输入吗？NONO，同样可以从身份证号码直接获取。
=IF(MOD(MId(身份证号码所在单元格关谷健次郎,15,3),2),"男","女")

Tips：我们可以根据身份证号码的第17位判断性别，如果是奇数为男性，偶数为女性。



自动算星座
好吧，我其实不迷信。但是，统计员工星座，其实是件很有趣的事情。例如，员工关怀可以设置为同个星座的员工一起过生日。

还是只用身份证就够了，你只需要把公式复制粘贴进去，然后把公式所在单元格一改，就搞定。

=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日"},),"摩羯座","射手座"刘大铭,"天蝎座","天秤座","处女座","狮子座","巨蟹座","双子座",长春动植物公园门票"金牛座","白羊座","双鱼座","水瓶座","摩羯座")




自动算出生地（籍贯）
要想从身份证中获取出生地，还需要另外一张工作表，用来存放地区对应的代码信息。

从身份证中提取出行政区划代码，和对照表查询匹配井川ゆい ，就能立马得到结果。
（搜索「行政区划 代码」便可从国家统计局的官网中获得相关信息，最近数据更新至 2016年7月31日，文末提供了下载地址~）
=VLOOKUP(VALUE(LEFt(身份证号码所在单元格,6)),出生地查询表!B:C,2,1)




小心得
在做表之前，先搞清楚，哪些数据可以通过函数公式自动计算得来，就可以省下不少功夫。数据量越大，偷懒效果就越明显哟。
做好信息表，我们还可以利用条件格式让表格自己说话。例如，为生日加上条件格式，就能让本月生日的员工突出显示。
方法很简单，选中生日数据后，依次单击【开始】-【条件格式】-【突出显示单元格规则】-【发生日期】-【选择日期为本月】，【设置为自定义格式】-【字体加粗】，【填充浅橙色】-【确定】。

怎么样？是不是特别简单？