【原】excel函数应用：如何用数位函数分段提取身份证信息*下

编按：哈喽，大家好！在上篇内容中我们说到了用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图表述/部落窝教育（未经同意，请勿转载）