![38850ad53ad12da3986af5ce39f002ba.png](https://img-blog.csdnimg.cn/img_convert/38850ad53ad12da3986af5ce39f002ba.png)
作为HR,日常各类员工信息表格整理、统计工作必不可少,这些信息以后不用再手动录入了,只要输入一个身份证号,套用函数,一键提取!不仅事半功倍,而且大大减少我们的出错率。
身份证号码编码规则如下图:
![a05b0dff860f0886a3f522cbf504c1fb.png](https://img-blog.csdnimg.cn/img_convert/a05b0dff860f0886a3f522cbf504c1fb.png)
籍贯、出生年月、性别统统都隐藏在里边,接下来我们来分享一些实用的函数,轻松提取你需要的信息。
![93abc009c390a6669361467f9a4a32c8.png](https://img-blog.csdnimg.cn/img_convert/93abc009c390a6669361467f9a4a32c8.png)
籍贯(省市)
公式:
=LOOKUP(VALUE(LEFT(A2,2)),{11,"北京市";12,"天津市";13,"河北省";14,"山西省";15,"内蒙古自治区";21,"辽宁省";22,"吉林省";23,"黑龙江省";31,"上海市";32,"江苏省";33,"浙江省";34,"安徽省";35,"福建省";36,"江西省";37,"山东省";41,"河南省";42,"湖北省";43,"湖南省";44,"广东省";45,"广西壮族自治区";46,"海南省";50,"重庆市";51,"四川省";52,"贵州省";53,"云南省";54,"西藏自治区";61,"陕西省";62,"甘肃省";63,"青海省";64,"宁夏回族自治区";65,"新疆维吾尔自治区";71,"台湾省";81,"香港特别行政区";82,"澳门特别行政区";"","0"})
![4004e7f345f6358c5770e0fc8e032f89.png](https://img-blog.csdnimg.cn/img_convert/4004e7f345f6358c5770e0fc8e032f89.png)
解释:身份证前两位仅代表初次申报户口的省份信息
注:地址码与省份对照表
![4dc01c78fcd6fef9e18207a0707b7f33.png](https://img-blog.csdnimg.cn/img_convert/4dc01c78fcd6fef9e18207a0707b7f33.png)
![f472569bcc3c1f41cb0b456a0a6fecb8.png](https://img-blog.csdnimg.cn/img_convert/f472569bcc3c1f41cb0b456a0a6fecb8.png)
出生日期
公式:
=--TEXT(MID(A2,7,8),"0000-00-00")
![a2bac30d911562d9d7c354e85ca02782.png](https://img-blog.csdnimg.cn/img_convert/a2bac30d911562d9d7c354e85ca02782.png)
提醒:记得把单元格格式设置成日期格式
![51d2ab9923efa793f1ec41da2457e80b.png](https://img-blog.csdnimg.cn/img_convert/51d2ab9923efa793f1ec41da2457e80b.png)
属相
公式:=CHOOSE(MOD(MID(A2,7,4)-2008,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")
![31ac6b4f6b0b54562b3d7aa46d1a9cd5.png](https://img-blog.csdnimg.cn/img_convert/31ac6b4f6b0b54562b3d7aa46d1a9cd5.png)
![450d8d5b7b5239496bfae7634eac69ee.png](https://img-blog.csdnimg.cn/img_convert/450d8d5b7b5239496bfae7634eac69ee.png)
星座
公式:
=LOOKUP(--MID(A2,11,4),{100;120;219;321;421;521;622;723;823;923;1023;1122;1222},{"摩羯座";"水瓶座";"双鱼座";"白羊座";"金牛座";"双子座";"巨蟹座";"狮子座";"处女座";"天秤座";"天蝎座";"射手座";"摩羯座"})
![77a59c74ff8eedca651258dba9c47126.png](https://img-blog.csdnimg.cn/img_convert/77a59c74ff8eedca651258dba9c47126.png)
注:星座与阳历的对应表
![edcf0a8a141eed6d08b8c765e5b6b4ff.png](https://img-blog.csdnimg.cn/img_convert/edcf0a8a141eed6d08b8c765e5b6b4ff.png)
![aa71a1477595603335d7c6b646752480.png](https://img-blog.csdnimg.cn/img_convert/aa71a1477595603335d7c6b646752480.png)
性别
公式:
=IF(MOD(MID(A2,17,1),2),"男","女")
![74c1d9b8546f14f2b8e2c34ead741cc6.png](https://img-blog.csdnimg.cn/img_convert/74c1d9b8546f14f2b8e2c34ead741cc6.png)
解释:身份证号倒数第2位,奇数为男,偶数为女
![319ec8e34facf9912d64ea3ca8a5e8f1.png](https://img-blog.csdnimg.cn/img_convert/319ec8e34facf9912d64ea3ca8a5e8f1.png)
年龄
公式:
=DATEDIF(C2,TODAY(),"y")
![887d271707144d787f81d54fed1627f9.png](https://img-blog.csdnimg.cn/img_convert/887d271707144d787f81d54fed1627f9.png)
![a5fc1c141b9b8fb27850e8d4fedfdf2a.png](https://img-blog.csdnimg.cn/img_convert/a5fc1c141b9b8fb27850e8d4fedfdf2a.png)
员工生日福利准备,名单统计的工作也是相当繁琐,设置好函数,一目了然,还减少出错率。另外除了生日提醒,以下的公式还可以用在更多方面,例如,员工合同到期提醒,财务催款时间提醒等等。
1.当月提醒
公式:
=IF((MONTH($C2)=MONTH(TODAY())),"是","")
![2c3ab4ea359d0cea2ca765d70533843d.png](https://img-blog.csdnimg.cn/img_convert/2c3ab4ea359d0cea2ca765d70533843d.png)
2.生日15天内提醒
公式:
=TEXT(15-DATEDIF($C2-15,TODAY(),"YD"),"还有0天生日;;今天生日")
![1af4c384eec90e00f4ff276c80162762.png](https://img-blog.csdnimg.cn/img_convert/1af4c384eec90e00f4ff276c80162762.png)
解释:公式“DATEDIF(日期1,日期2,"YD")”将忽略年份计算两个日期之间的天数。如果你需要的是一周(7天)内的提醒,则把公式中的数字“15”改为“7”即可。
小贴士:公式可以直接复制使用,套用公式时记得根据实际情况修改对应单元格信息。
本文首发微信公众号:萌懂微暖,欢迎关注查阅。