WPS Excel:从身份证中提取生日、年龄、性别、籍贯、生肖、星座

2019年4月8日20:58:21WPS Excel:从身份证中提取生日、年龄、性别、籍贯、生肖、星座已关闭评论 32 views
微信公众号 【EXCEL880】 QQ群【165159540】 Excel课程咨询 请加郑广学老师微信EXCEL880B

从一串身份证号码中,可以1秒提取出这个号码对应的生日、年龄、性别、籍贯、生肖、星座。

WPS Excel:从身份证中提取生日、年龄、性别、籍贯、生肖、星座

作为一名HR,需要登记员工的许多信息,如果学会了怎么从身份证号码中提取这些信息,可以节省许多时间。

提取生日

公式:“=TEXT(MID(A2,7,8),"0-00-00")”

函数“MID(A2,7,8)”可以从A2单元格的第7位开始提取8位数字,再用TEXT函数将提取到的数字显示为“年-月-日”格式。

提取年龄

公式:“=DATEDIF(B2,TODAY(),"Y")”

B2单元格是我们提取到的生日,DATEDIF函数中前两个表示日期,最后一个参数表示计算方式,“Y”表示计算这两个日期之间相差多少年。

提取性别

公式:“=IF(MOD(MID(A2,17,1),2)=1,"男","女")”

身份证号码的第17位如果是偶数表示性别是“女”,奇数表示性别是“男”。“MOD(数字,2)”表示用指定数字除以“2”,余数是1就表示这个数字就奇数,也就是代表“男”。

#钉钉新工作方式#

提取籍贯

公式:“=VLOOKUP(LEFT(A2,2),身份证代码表!$A$2:$B$35,2,0)”

WPS Excel:从身份证中提取生日、年龄、性别、籍贯、生肖、星座

这个公式用文字表示就是“VLOOKUP(身份证号码前2位,身份证代码表,第2列,精确匹配)”。提取身份证号码的前2位数字后,去“身份证代码表”查询,找到这2位数字后,获取代码表对应的第二列数据,这样就获得了籍贯。

提取生肖

公式:“=MID("猴鸡狗猪鼠牛虎免龙蛇马羊",MOD(YEAR(B2),12)+1,1)”或“=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(B2)-4,12)+1,1)”

我们有12个生肖,因此将年龄除以“12”(函数“MOD(年龄,12)”)得到的数字相同的就是同一个生肖。

提取星座

WPS Excel:从身份证中提取生日、年龄、性别、籍贯、生肖、星座

公式:“=LOOKUP(--TEXT(B2,"mdd"),{101,"摩羯座";120,"水瓶座";219,"双鱼座";321,"白羊座";420,"金牛座";521,"双子座";621,"巨蟹座";723,"狮子座";823,"处女座";923,"天秤座";1023,"天蝎座";1122,"射手座";1222,"摩羯座"})”

不要别这个公式吓着了!

其实它就是公式“=LOOKUP(--TEXT(B2,"mdd"),{星座其实日期,"星座"})”,由于有12个星座,我们没有用辅助表格,就在公式中用“{}”就这些星座和对应的日期都罗列出来了,这样就显的公式很复杂了。

注意

WPS Excel:从身份证中提取生日、年龄、性别、籍贯、生肖、星座

从上面这些公式,我们知道一个身份证号码包含的信息实在太多了。因此就不要轻易泄露自己的身份证号码和姓名。本例中的身份证号码都只是为了介绍公式用法瞎编的。


谢谢阅读,每天学一点,省下时间充实自己。欢迎点赞、评论、关注和点击头像。

表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页