Excel干坏事了:一个身份证号就能扒出您7条隐私(下篇)
编按:哈喽,大家好!在上篇内容中我们说到了用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-17,YEAR(TODAY())就是2019,YEAR(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图表述的文章,力求让大家能够深入浅出,开拓思维,真正地在部落窝学到你想要的知识。
****部落窝教育-MID函数提取身份证信息****
原创:E图表述/部落窝教育(未经同意,请勿转载)