打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
人员信息整理?HR教你这5个必会的Excel技巧!


Hi, 我是秋小叶~


如果你做过班级/办公室人员信息整理你就会知道,人员的信息整理简直是个大坑。


今天,HR 傅琳就要来分享她在工作中总结出来的几条经验,赶紧学一遍,万一以后遇上不抓瞎~



你知道吗?我们的身份证就是一个信息宝库,包含了很多的私密信息籍贯、出生日期、性别统统都在里面了!


今天,就告诉你 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)



小心得


在做表之前,先搞清楚,哪些数据可以通过函数公式自动计算得来,就可以省下不少功夫。数据量越大,偷懒效果就越明显哟。


做好信息表,我们还可以利用条件格式让表格自己说话。例如,为生日加上条件格式,就能让本月生日的员工突出显示。


方法很简单,选中生日数据后,依次单击【开始】-【条件格式】-【突出显示单元格规则】-【发生日期】-【选择日期为本月】,【设置为自定义格式】-【字体加粗】,【填充浅橙色】-【确定】。



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


回复关键词「HR」,获取一份人员信息表模板吧。


小问题


你的工作中经常用到哪些公式或 Excel 技巧呢?

快来留言区分享一下! 

作者:傅琳,「和秋叶一起学Excel」学员

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
excel身份证号码如何快速计算性别
身份证号提取男女性别公式
EXCEL 在身份证号码中提取性别
HR工作必备的10个Excel函数公式!
在Excel中根据身份证号码自动生成出生生日期和性别
如何通过身份证号取出生日?
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服