二维码 购物车
部落窝在线教育欢迎您!

2019年全网最全—excel提取身份证信息合集!(建议收藏)-下篇

 

作者:E图表述来源:部落窝教育发布时间:2019-09-26 14:59:17点击:2423

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好!在上篇内容中我们说到了用excel提取身份证号码中的户籍地址、出生日期、年龄、生肖、星座等内容,今天我们书接上回,继续带大家学习excel提取身份证信息的其他操作!

 

引言:

 

我们继续上篇生日提取的拓展思路,开始我们今天的内容,保证开篇就有惊喜!

 

【正文】


4、生日提醒


生日提醒的问题,作者觉得要分两部分来说。

 

1)有的公司的管理比较人性化,可能每个月都会给当月过生日的员工发一些小礼品,但如果这些小礼品都是生日当天发送,那这一年下来,就是一个很费精力的事情,所以就在每个月中选一天,给本月过生日的员工一起庆生。这个时候,我们可以使用EXCEL进行如下操作:

 

 

B43单元格函数:

=IF(--MID(B2,11,2)=MONTH(TODAY()),"本月生日","非本月生日")

 

讲到现在,想必同学们都已经对“--MID(B2,11,2)”函数用法和意义了如指掌了吧?!它用于返回生日的月份,然后与今天的月份作对比,如果相等就返回“本月生日”,否则就返回“非本月生日”,一目了然。

 

2)对于重要人物的生日提醒。关于类似的提醒,作者建议大家一定要做到精确到日,类似“倒计时”的提醒,例如下面的示范:

 

 

B45单元格函数:

=IF(--TEXT(MID(B2,11,4),YEAR(TODAY())&"-00-00")<=TODAY(),--TEXT(MID(B2,11,4),YEAR(TODAY())+1&"-00-00")-TODAY(),--TEXT(MID(B2,11,4),YEAR(TODAY())&"-00-00")-TODAY())&"天后庆生"

 

这个函数看着比较复杂(好吧,我承认里面有一段确实复杂),但是思路其实很简单,天数=IF(本年生日日期<=今天日期,次年生日日期-今天日期,本年生日日期-今天日期)

 

本年生日日期=--TEXT(MID(B2,11,4),YEAR(TODAY())&"-00-00")

次年生日日期=--TEXT(MID(B2,11,4),YEAR(TODAY())+1&"-00-00")

 

MID(B2,11,4)提取月份日期就不多说了,TEXT函数的第二参数是代表需要转换的格式表达式,这个参数是可以有函数参与的,例如本例,TODAY()2019-9-17YEAR(TODAY())就是2019YEAR(TODAY())&"-00-00"这样的表达的格式就是"2019-00-00"(今年的生日日期),同理YEAR(TODAY())+1&"-00-00"代表"2020-00-00"的格式(次年的生日日期),用这个方式就确定了还有多少天庆生。

 

当然同学们也可以使用函数:=DATE(YEAR(TODAY()),MID(B2,11,2),MID(B2,13,2))来得到身份证中的生日日期,上例就是为了给大家一个其他的函数应用思路,实际工作中,会哪个就用哪个吧。


 

三、性别判断及延伸思考


 

相对于出生日期的话题,性别判断的话题就显得有点简单了。身份证的编码规则,第15~16位,是各个地区户籍公安局的编码,这个我们就不多说了。第17位代表的是性别的代码,奇数代表男性,偶数代表女性。

 

 

B5单元格函数:

=IF(MOD(MID(B2,17,1),2)=1,"","")

 

MID(B2,17,1)提取身份证第17位的代码,用MOD函数得到除以2的余数,再用IF函数判断余数是否为1,为1则为奇数返回“男”,否则为“女”(一个整数除以2的余数,除了1就是0)。说到这里,我们可以额外多说一句,在EXCEL函数中其实是有专门判断奇偶性的函数——ISODD函数、ISEVEN函数。

 

 

利用这两个函数也是可以判断性别的,如下图(注意两个函数的逻辑返回值所对应的“男”、“女”):

 

 

当然EXCEL从来都是一题多解的,也可以用VLOOKUP函数的方式来做,同学们会哪个,擅长哪个,就随大家方便吧,解决问题就好。

 

 

写了这么多的内容,有的同学可能会说了,我们这样一直曝光别人的身份证,应该不太好吧?!

 


四、判断身份证号的真伪

 


在很多情况下,我们都有可能只看到身份证号,而看不到身份证原件,即便看到了身份证原件,没有专业的身份证读卡机我们也不好判断是真是假。那么本系列最后一部分内容我们就来学习如何使用EXCEL判断身份证号的真伪。

 

1)首先身份证的位数是我们第一步判断身份证号录入是否正确的标准。

 

 

这是最基本的,如果位数都不对,那就别谈其他的了。

 

2)第18位的效验码

 

身份证号的第18位码是一个计算结果值,是用前17位代码通过复杂的一个计算得到的,了解了这个计算我们就可以验证身份证号码的真伪了。

 

计算步骤:

 

★前17位号码,每一位乘对应位数的系数,再相加求和,对应码如下:

 

 

★将得数除以11求余,余数和下面的对比表进行对比,对应的上则为“真”,否则为“假”。

 

 

就是这么简单的两步而已,但是难点就在于我们要分别取出各个位上的值,如果用辅助列做,这个问题很好解决,今天我们来学习不使用辅助列的做法,如下:

 

 

问题揭晓,此身份证号码是假的,大家可以用真实的身份证号码验证一下函数。

 

B5单元格函数:

{=VLOOKUP(MOD(SUM(MID(B2,ROW(1:17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0)=RIGHT(B2)}

 

输入完成后,按数组函数的结束键CTRL+SHIFT+ENTER三键结束。

 

函数解析:

 

MID(B2,ROW(1:17),1)利用数组的方式,分别得到身份证前17位的数字,形成一个常量数组,{"5";"1";"1";"5";"0";"2";"1";"9";"9";"1";"0";"3";"2";"2";"3";"1";"8"}。


然后和{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}相乘再用SUM求和,通过MOD(值,11)得到余数,再用VLOOKUP函数在{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2}数列中索引出对照码;最后和RIGHT(B2)如果LEFT函数和RIGHT函数的第2参数是1,可以省略)比较,返回逻辑值TRUE就是真身份证号,FALSE则是假身份证号。

 

编后语:

 

我知道网络上有很多关于身份证号的文章,但是每次写都会有不同的感受和新的内容出来,比如第一部分我们使用SUBSTITUTE函数精准提取市、区县,又如生日提醒里面的TEXT函数的使用方式,更比如最后的效验码问题,这些都不是不好理解的东西。E图表述的文章,力求让大家能够深入浅出,开拓思维,真正地在部落窝学到你想要的知识。

 

本文配套的练习课件请加入QQ群:109723835下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

Excel提取身份证信息合集(上2019年全网最全—excel提取身份证信息合集!(建议收藏)-上篇

检验身份证号码真伪《Excel检验身份证号码真假的函数公式

DATEDIF函数的应用用上DATEDIF,您永不再缺席那些重要的日子!