时间:2022-01-12 22:01作者:admin来源:未知人气:347我要评论(0)
之前写过用自定义函数提取员工身份证个人信息,很多小伙伴表示VBA较难理解,是否能用函数获取?
今天我们就来总结下通过身份证我们可以获取哪些信息,分别利用哪些公式提取?
在说公式之前我们先了解下身份证号码的构造:公民身份号码是特征组合码,由十七位数字本体码和一位校验码组成;
排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码,其中顺序码最后一位也就是整个身份证号码倒数第二位为性别判断码,奇数为男性、偶数为女性,通过这个我们可以轻易判断性别。
分析完身份证号码的组成之后,我们可以轻易获取员工的出生年月(生日)、性别、籍贯以及其它信息。
公式:=TEXT(MID(B2,7,8),"0000-00-00")
通过上面对身份证号码的分析得到7至14位为员工的出生年月日,截取出来即可;
两个函数的嵌套,其中MID函数为截取函数,截取出来的内容为【19900809】,在利用text函数格式化输入,格式化为日期格式,获得最终结果。
公式:=IF(MOD(MID(B2,17,1),2),"男","女")
通过第一步我们得知身份证号码倒数第二位为性别标识码,奇数男性、偶数女性,提取出来判断奇偶数即可;
第一步同样是MID函数取出倒数第二位数字,接着用MOD取余函数,判断取出来的数字除以2是否能整除,能整除 余数为0,代表女性,不能整除 余数为1,代表男性;
最后利用IF函数返回男女,不能整除判断条件为1(true),返回IF函数第二个参数男,能整除判断条件为0(false),返回IF函数第三个参数女。
一共嵌套了三个函数,小伙伴们拆开看要容易理解一点。
公式:=DATEDIF(TEXT(MID(B2,7,8),"0000-00-00"),TODAY(),"Y")
计算年龄只要计算员工从出生至今一共经历多少个年份即可;
第一步提取员工出生年月日并格式化为日期格式,第二步将员工出生日期与当前日期做差,返回整年数;
先利用mid函数取出员工出生年月日,在利用text转换成日期格式;DATEDIF 返回两个日期之间的年月日间隔数,参数"Y"代表返回两日期之间的整年数。
开头对身份证号码分析过,前两位代表省份,具体数字对应哪个省份?有一张对应关系表,如下图A/B两列所示,对应表在网页上都有,小伙伴们可自行搜索;
第一步提取开头两位数字,这里还是用MID函数:=MID(E2,1,2),
接着用匹配函数匹配上省份即可:=VLOOKUP( F2*1,A:B,2,0);
这里的F2函数之所以要乘以数字1,主要是因为身份证号码是文本格式,利用MID函数取的前两位也是文本格式,而省份匹配表A列却是数值格式,直接匹配的话无法返回正确结果,F2*1直接将文本变成了数值格式,在进行匹配,返回正确内容!
出生年月提取:=TEXT(MID(B2,7,8),"0000-00-00")
30天生日提醒:=TEXT(30-DATEDIF(C2-30,TODAY(),"YD"),"还有0天生日;生日已过;今天生日")
这里的30天主要是提前30提醒,改成15天、10天、3天即可;
其中DATEDIF函数的参数"YD" 表示起始日期与结束日期的同年间隔天数,忽略日期中的年份;
TEXT函数的第二个参数"还有0天生日;生日已过;今天生日"表示如果第一个参数结果X大于0,则返回"还有X天生日",如果参数结果小于0,则返回"生日已过",如果参数结果等于0,则表示今天生日,返回"今天生日"。
以上就是关于身份证账号相关的公式,以及可以获取的那些信息,主要运用的到函数主要包括三个:MID取值函数、TEXT格式化输出函数、DATEDIF返回日期差函数;
公式皆不复杂,繁琐在于多层嵌套,小伙伴们在书写的时候从里向外一步步嵌套即可。如果逻辑理解不了也无所谓,毕竟我们不一定要学会如何造车子,会开就好!
相关阅读 Excel有哪些常用的数学函数?Excel取消表格中虚线的两种方法Excel最常见的「错误值」,这些含义你都知道吗?实现快速找出Excel表格中两列数据不同内容的3种方法!如何利用Excel一键提取身份证的这些重要信息,公式直接套用!Excel如何制作动态红绿灯,工作可不要亮红灯哦Excel身份证号大探索excel如何根据日期按月汇总计算公式Excel浪漫表白公式,发给心仪的她/他Excel表格如何自动求和
热门文章 excel 两表数据快速对比,高手都是这样做,四种方法随你选.xlsm是什么文件格式,以及xlsm文件怎么打开的方法excel if函数如何多个条件并列excel中计算加权平均数的公式:用SUMPRODUCT和SUM函数计算加权平均
最新文章
Excel有哪些常用的数学函数?Excel取消表格中虚线的两种方法
Excel最常见的「错误值」,这些含义你都知道吗?实现快速找出Excel表格中两列数据不同内容的3种方法!如何利用Excel一键提取身份证的这些重要信息,公式直接套用!Excel如何制作动态红绿灯,工作可不要亮红灯哦
人气排行 excel 两表数据快速对比,高手都是这样做,四种方法随你选.xlsm是什么文件格式,以及xlsm文件怎么打开的方法excel if函数如何多个条件并列excel中计算加权平均数的公式:用SUMPRODUCT和SUM函数计算加权平均excel中IF条件函数10大用法完整版,全会是高手,配合SUMIF,VLOOKUPexcel中COUNTIFS函数9种高级用法详解,条件统计重复值,告别加班涨工如何解除Excel VBA工程密码excel 如何根据身份证号码提取户籍所在省份地区函数公式
盖楼回复X
(您的评论需要经过审核才能显示)
查看所有0条评论>>