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

引言：用Excel处理身份证号，在我们日常工作中是相当普遍的，尤其是对于做人事行政工作、财务工作的同学来说，更显得十分重要。那么一个身份证号，能给予我们多少信息量呢？这也算是一个老生常谈的问题，但是最近在群里又总见到有同学问及，那么今天我们就再来做一个总结，看一看Excel函数对于身份证号码对应的各个信息的处理方式吧。

＊＊＊附加知识：

无论我们需要用Excel处理何种数据，首先都应先对该数据有一个了解，在不了解它的情况下就直接处理数据，有可能会把方向做错，那样的数据处理结果就令人堪忧了。 

我们都知道身份证号码一共有18位（以前的身份证号码是15位的，但是普及二代身份证这么多年，可以说一代的15位身份证已经极少使用了，所以今天主要讲解二代身份证的内容），身份证号码按照“号码段”区分大体可以分为6个信息： 

● 1~2位省、自治区、直辖市代码；

● 3~4位地级市、盟、自治州代码；

● 5~6位县、县级市、区代码；

● 7~14位出生年月日；其中

7~10位，代表出生年份

11~12位，代表出生月份

13~14位，代表出生日期

● 15~17位为顺序号，其中17位（倒数第二位）是奇数则为男性，偶数为女性；

● 18位为校验码，0-9和X。 

我们辅以图示更加直观的记忆一下：

应该说我们的身份证号中能有这么多的信息量，当初研究出这套编码规则的人也真是相当的不容易了。

＊＊＊＊＊＊＊＊＊

有了上面对于身份证的一个了解，我们就可以通过这个号码提取很多的内容了。

一、省、市、县自动提取

函数解析：

对于这个函数来说，本身并不复杂，关键是这几张信息表。

不是每一个同学都有的，现在你也可以拥有这么一套代码对照表了，有需要的同学，可以在看完此文篇文章之后，加入下方提供的部落窝QQ群，下载即可。

公式中所使用的MID函数是典型的字符串分段取值的函数。

MID函数语法：

=MID（text，start_num，num_chars）

翻译成白话就是在一个字符串（text）中，从某个序号位置（start_num）开始，提取之后连续的几个（num_chars）字符。

那我们再来看一下上面的函数：

B5单元格=VLOOKUP(MID($B$2,1,2),省级码对照表!A:B,2,0)

MID函数在B2单元格的身份证号中，提取出从第1位开始的连续两位字符，就是“51”；再把“51”带入到VLOOKUP函数中，并在《省级码对照表》中的A、B两列处进行索引即可。B7、B9单元格的市、县索引方式也是一样的，但是一定要记住，索引省，用到身份证的前两位；索引市，要用到身份证的前4位。索引出XX省XX市的格式，不能直接使用第3~4位，因为会和其他的省市码重复；县级代码同理，需要索引身份证号的前6位，不可单独索引第5~6位。

当然有些同学会问，用其他函数可以吗？针对第一个问题，用LEFT函数也可以，=VLOOKUP(LEFT($B$2,2),省级码对照表!A:B,2,0)

但是建议同学们在身份证处理问题上，还是只使用MID函数来提取号码段，便于对身份证信息这一类问题的统一解决，而LEFT函数或者RIGHT函数，只适合左右两端的取值，对于中间部分的取值相对于MID函数就显得有些麻烦了。

＊＊＊对于市、县提取的更高要求：

刚才我们说过了，如果直接用第3~4位，或者第5~6位的代码去索引，由于号码有重复，所以索引出的内容有可能就是错误的。但是在人事工作中，有时我们需要精准的提取出XX市、XX区县，那我们就可以使用下面的方式来处理。

B14单元格函数：

=SUBSTITUTE(VLOOKUP(MID($B$2,1,4),省市码对照表!A:B,2,0),VLOOKUP(MID($B$2,1,2),省级码对照表!A:B,2,0),"")

聪明的你，是不是一眼就可以看出来，红色的VLOOKUP函数，提取的是XX省XX市；蓝色的VLOOKUP函数，提取的是XX省，我们再用SUBSTITUTE函数，将红色索引值中的XX省部分替换为空文本即可。县的提取也是同理，很简单的思路，但是在工作中十分实用，赶紧学起来吧！

二、生日提取及应用拓展

函数解析：

同样使用MID函数提取代表出生日期的7~14位，再用TEXT函数改变格式为“0000-00-00”，最后以减负运算将文本格式的日期改成数值形态，再调整单元格格式，就是我们需要的“出生日期”。说着很简单，如果需要掌握这个函数的话，还是需要多运用来帮助记忆才行的。关于TEXT详细的使用方法，小伙伴们可以参考之前的教程《如果函数有职业，TEXT绝对是变装女皇！》，在此就不细说了。

当然，我们也可以用下面的方式提取：

 图中“生日2”的公式分别利用MID函数在身份证号中提取出代表“出生年月日”的各个代码段，再分别代入到DATE函数的year、month、day参数中即可。

虽说两种方法的结果一样，但是这是两种思路，一个是整体提取，一个是分开提取，这在我们的实际工作中，有着很重要的作用。

＊＊＊对出生日期的应用拓展：

1、通过身份证得出年龄

“年龄1”的算法依然使用了MID函数，今年的年份【YEAR(TODAY())】减去出生的年份【MID(B2,7,4)】就是年龄了；

“年龄2”的算法利用了DATEDIF函数，计算两个日期的年差，而开始日期的表示方法【--TEXT(MID(B2,7,8),"0000-00-00")】就是我们刚才用的提取出生日期的函数。关于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)】，再使用减负运算，将其变更为数值形态。例如我们这个身份证号的11~14位是0322，数值形态就是322。值得注意的是，此时我们利用的并不是日期的序列值，而是一个常规形态的数值，通过对应码区域，利用VLOOKUP的模糊查询得到对应的星座。

＊＊＊＊＊＊＊＊＊

编后语：介于篇幅问题，以上即为上篇，关于生日提取的拓展思路还没有讲完，下篇内容我们继续。

＊＊＊＊部落窝教育－excel数位函数技巧＊＊＊＊

原创：E图表述/部落窝教育（未经同意，请勿转载）