Excel从身份证号码中提取各种信息的汇总教程

Vlookup函数（滴答老师主讲） 来自excel教程 00:00 01:16

这是一篇整合身份证号码提取的总结教程！

只需在Excel中输入身份证号码即可得到以下数据：包含校验输入的正确性，提取年龄、性别、出身年月、所在地区、生肖、星座、生日提醒。

18位身份证的号段含义如下：

前2位表示所属省份代码，3~6位表示所属城市和区县代码，7~14位表示出生日期，格式为YYYYMMDD，15~17位是个人顺序码，其中17位可以标识性别，奇数表示男性，偶数表示女性，第18位是校验码，由前17位通过计算求得。

输入18位身份证显示4.45121E+17，WHY？

 

这是科学计数法，E+17是10的17次方。默认情况下，数字超过12位就显示科学技术法，数字超过15位后面都显示0。如图所示。

选择A列，将单元格格式设置为文本，再重新输入身份证即可。

设置好后，又有一个问题。如何才能保证身份证刚好是18位，同时还得符合身份证的所有条件，前17位必须是数字，第18位是X或者是数字。

18位可以用

=LEN(A2)=18

 前17位是数字

=ISNUMBER(-LEFT(A2,17))

 第18位是数字或者字母X

=OR(ISNUMBER(-RIGHT(A2)),RIGHT(A2)='X')

综合3个条件，可以进行数据有效性设置。

选择A2:A5，设置数据有效性（注：Excel2013版叫数据验证），在“允许”下拉列表框中选择“自定义”选项。在“公式”文本框输入下面的公式，点击“确定”按钮。

=AND(LEN(A2)=18,ISNUMBER(-LEFT(A2,17)),OR(ISNUMBER(-RIGHT(A2)),RIGHT(A2)='X'))

数据有效性对于已经输入好的身份证无效，但可以把不满足条件的圈出来。对设置好数据有效性后进行“圈释无效数据”。

把无效的数据重新更改即可，然后输入正确的身份证，以后如果输入不正确的话，会提示错误，直到你输入正确为止。如图所示。

关于身份证的准备工作已经做好，后面是获取各种信息的关键时刻。

 如图所示，是一份详细的地区码。

通过提取码可以查询到省份跟地区。

省份就是提取身份证前2位，然后去地区码查询对应值。

=VLOOKUP(LEFT(A2,2),地区码!A:B,2,0)

地区就是提取身份证前6位，然后去地区码查询对应值。

=VLOOKUP(LEFT(A2,6),地区码!A:B,2,0)

出生日期就是从身份证第7位开始提取8位，将提取到的数字转换成0-00-00这种形式。

=--TEXT(MID(A2,7,8),'0-00-00')

性别就是第15-17位，奇数是男，偶数是女。

=IF(MOD(MID(A2,15,3),2),'男','女')

除了上面介绍的，通过出生日期还可以获取一系列问题：周岁、生肖、生日提醒、星座等。现在就以这4个常见问题再进行说明。为了方便，出生日期就引用D2单元格。

周岁

=DATEDIF(D2,NOW(),'y')

生肖

=MID('鼠牛虎兔龙蛇马羊猴鸡狗猪',MOD(TEXT(D2,'[$-130000]e')-4,12)+1,1)

TEXT(D2,'[$-130000]e')的作用就是将日期转换成阴历年份。

生日提醒

=TEXT(TEXT(D2,'m-d')-TEXT(NOW(),'m-d'),'还有0天生日;;今天生日')

先将日期转变成月日再进行相减，大于0就显示还有多少天生日，小于0就不显示，0就是今天生日。

星座

=LOOKUP(--TEXT(D2,'m.dd'),{0,'摩羯座';1.21,'水瓶座';2.2,'双鱼座';3.21,'白羊座';4.21,'金牛座';5.22,'双子座';6.22,'巨蟹座';7.24,'狮子座';8.24,'处女座';9.24,'天秤座';10.24,'天蝎座';11.23,'射手座';12.23,'摩羯座'})

 这个看着很长，其实很简单，只要获取星座对应表就可以。

经过了N步处理，最终效果如图所示。