函数应用如何用数位函数分段提取身份

　　引言：用Excel处理身份证号，在我们日常工作中是相当普遍的，尤其是对于做人事行政工作、财务工作的同学来说，更显得十分重要。那么一个身份证号，能给予我们多少信息量呢？这也算是一个老生常谈的问题，但是最近在群里又总见到有同学问及，那么今天我们就再来做一个总结，看一看Excel函数对于身份证号码对应的各个信息的处理方式吧。
　　附加知识：
　　无论我们需要用Excel处理何种数据，首先都应先对该数据有一个了解，在不了解它的情况下就直接处理数据，有可能会把方向做错，那样的数据处理结果就令人堪忧了。
　　我们都知道身份证号码一共有18位（以前的身份证号码是15位的，但是普及二代身份证这么多年，可以说一代的15位身份证已经极少使用了，所以今天主要讲解二代身份证的内容），身份证号码按照号码段区分大体可以分为6个信息：
　　12位省、自治区、直辖市代码；
　　34位地级市、盟、自治州代码；
　　56位县、县级市、区代码；
　　714位出生年月日；其中
　　710位，代表出生年份
　　1112位，代表出生月份
　　1314位，代表出生日期
　　1517位为顺序号，其中17位（倒数第二位）是奇数则为男性，偶数为女性；
　　18位为校验码，09和X。
　　我们辅以图示更加直观的记忆一下：
　　应该说我们的身份证号中能有这么多的信息量，当初研究出这套编码规则的人也真是相当的不容易了。
　　有了上面对于身份证的一个了解，我们就可以通过这个号码提取很多的内容了。
　　一、省、市、县自动提取
　　函数解析：
　　对于这个函数来说，本身并不复杂，关键是这几张信息表。
　　不是每一个同学都有的，现在你也可以拥有这么一套代码对照表了，有需要的同学，可以在看完此文篇文章之后，加入下方提供的部落窝QQ群，下载即可。
　　公式中所使用的MID函数是典型的字符串分段取值的函数。
　　MID函数语法：
　　MID（text，startnum，numchars）
　　翻译成白话就是在一个字符串（text）中，从某个序号位置（startnum）开始，提取之后连续的几个（numchars）字符。
　　那我们再来看一下上面的函数：
　　B5单元格VLOOKUP（MID（B2，1，2），省级码对照表！A：B，2，0）
　　MID函数在B2单元格的身份证号中，提取出从第1位开始的连续两位字符，就是51；再把51带入到VLOOKUP函数中，并在《省级码对照表》中的A、B两列处进行索引即可。B7、B9单元格的市、县索引方式也是一样的，但是一定要记住，索引省，用到身份证的前两位；索引市，要用到身份证的前4位。索引出XX省XX市的格式，不能直接使用第34位，因为会和其他的省市码重复；县级代码同理，需要索引身份证号的前6位，不可单独索引第56位。
　　当然有些同学会问，用其他函数可以吗？针对第一个问题，用LEFT函数也可以，VLOOKUP（LEFT（B2，2），省级码对照表！A：B，2，0）
　　但是建议同学们在身份证处理问题上，还是只使用MID函数来提取号码段，便于对身份证信息这一类问题的统一解决，而LEFT函数或者RIGHT函数，只适合左右两端的取值，对于中间部分的取值相对于MID函数就显得有些麻烦了。
　　对于市、县提取的更高要求：
　　刚才我们说过了，如果直接用第34位，或者第56位的代码去索引，由于号码有重复，所以索引出的内容有可能就是错误的。但是在人事工作中，有时我们需要精准的提取出XX市、XX区县，那我们就可以使用下面的方式来处理。
　　B14单元格函数：
　　SUBSTITUTE（VLOOKUP（MID（B2，1，4），省市码对照表！A：B，2，0），VLOOKUP（MID（B2，1，2），省级码对照表！A：B，2，0），）
　　聪明的你，是不是一眼就可以看出来，红色的VLOOKUP函数，提取的是XX省XX市；蓝色的VLOOKUP函数，提取的是XX省，我们再用SUBSTITUTE函数，将红色索引值中的XX省部分替换为空文本即可。县的提取也是同理，很简单的思路，但是在工作中十分实用，赶紧学起来吧！
　　二、生日提取及应用拓展
　　函数解析：
　　同样使用MID函数提取代表出生日期的714位，再用TEXT函数改变格式为00000000，最后以减负运算将文本格式的日期改成数值形态，再调整单元格格式，就是我们需要的出生日期。说着很简单，如果需要掌握这个函数的话，还是需要多运用来帮助记忆才行的。关于TEXT详细的使用方法，小伙伴们可以参考之前的教程《如果函数有职业，TEXT绝对是变装女皇！》，在此就不细说了。
　　当然，我们也可以用下面的方式提取：
　　图中生日2的公式分别利用MID函数在身份证号中提取出代表出生年月日的各个代码段，再分别代入到DATE函数的year、month、day参数中即可。
　　虽说两种方法的结果一样，但是这是两种思路，一个是整体提取，一个是分开提取，这在我们的实际工作中，有着很重要的作用。
　　对出生日期的应用拓展：
　　1、通过身份证得出年龄
　　年龄1的算法依然使用了MID函数，今年的年份【YEAR（TODAY（））】减去出生的年份【MID（B2，7，4）】就是年龄了；
　　年龄2的算法利用了DATEDIF函数，计算两个日期的年差，而开始日期的表示方法【TEXT（MID（B2，7，8），00000000）】就是我们刚才用的提取出生日期的函数。关于DATEDIF详细的使用方法，小伙伴们可以参考之前的教程《用上DATEDIF，您永不再缺席那些重要的日子！》，在此就不细说了。
　　2、判断生肖（以身份证公历生日判断）
　　生肖每12年一个轮回，大家没有必要背上面的对照码，教给大家一个小技巧，以自己的出生年份除以12的余数作为基准，再顺序排列其他11个生肖的对照码就好了。
　　B13单元格函数：
　　VLOOKUP（MOD（MID（B2，7，4），12），A16：B27，2，0）
　　又是利用MID函数提取年份的四位，再通过MOD函数得到除以12的余数作为索引值，接着利用VLOOKUP函数得到对照码对应的生肖。
　　3、判断星座
　　使用MID函数提取身份证中的月份和日期四位数字的文本格式【MID（B2，11，4）】，再使用减负运算，将其变更为数值形态。例如我们这个身份证号的1114位是0322，数值形态就是322。值得注意的是，此时我们利用的并不是日期的序列值，而是一个常规形态的数值，通过对应码区域，利用VLOOKUP的模糊查询得到对应的星座。
　　编后语：介于篇幅问题，以上即为上篇，关于生日提取的拓展思路还没有讲完，下篇内容我们继续。
　　部落窝教育excel数位函数技巧
　　原创：E图表述部落窝教育（未经同意，请勿转载）