巧取身份证六大信息

（申明：本文所用身份证号均为随机组合而成，非真实身份证号，如有雷同，纯属巧合）

在实际的办公应用中，大量人员信息处理是件很繁琐的事情，包括有身份证号、出生日期、性别、年龄、籍贯，甚至是个人生肖、星座等，那么如何利用身份证号快速自动的录入相关信息，实现又快又准的完成工作。下面就以我国身份证的信息特点，利用身份证号，在excel表格中巧妙提取身份证六大信息，快速轻松完成人员信息处理。

　　当前，我国的身份证号码为18位，如图1，其中里面的信息特点为，第1到6位为全国行政户籍区划代码，第7到14位为出生日期数，倒数第2位为性别代码，偶数为女，奇数为男。我们可以利用excel中的函数，根据已输入的身份证号码，快速提取相关信息，准确完成人员信息处理。

图1

　　一.身份证号提取出生日期

函数公式：=IFERROR(TEXT(MId(B2,7,8),&34;0-00-00&34;),&34;&34;)

说明：

a.MId(B2,7,8)提取出生日期码

b.TEXT函数将文本处理为日期格式

c.外层嵌套IFERROR函数是为了空单元格规避错误值

如图2所示：

图2

二．身份证号提取岁数

函数公式：=IFERROR(DATEDIF(--TEXT(MId(B2,7,8),&34;0-00-00&34;),TODAY(),&34;y&34;),&34;&34;)

说明：

a.MId(A2,7,8)提取出生日期码

b.TEXT函数将文本处理为日期格式，--减负运算将文本转化为日期值

c.DATEDIF函数计算出生日期和今天的差值，即得到年龄

d.外层嵌套IFERROR函数是为了空单元格规避错误值

如图3所示：

　　图3

三．身份证号提取性别

函数公式：=IFERROR(IF(MOD(MId(B2,17,1),2),&34;男&34;,&34;女&34;),&34;&34;)

说明：

a.MId(B2,17,1)函数提取第17位数

b.MOD函数对2取余数，偶数余数为0，说明为女性，奇数余数为1，说明为男性，再用IF函数来判断性别

c.外层嵌套IFERROR函数是为了空单元格规避错误值

如图4所示：

图4

四．身份证号提取生肖

函数公式：=IFERROR(MId(&34;鼠牛虎兔龙蛇马羊猴鸡狗猪&34;,MOD(YEAR(C2)-4,12)+1,1),&34;&34;)

说明：

a.MId(B2,7,4)提取出生年份

b.MOD函数对12取余数，刚好对应12生肖的序列代码，里面日期的-4和外面的 +1是为了和前面的生肖相对应

c.外层嵌套IFERROR函数是为了空单元格规避错误值

d.身份证提取生肖是按公历计算的，我国生肖多数都是以农历计算为准，所以略有偏差，实际中需做适当调整

e.上面生肖的提取是以，身份证号提取的出生年月来提取计算的，实际中也可用身份证号直接提取阳历生肖，其函数公式为：

=IFERROR(IF(LEN(B9)=15,MId(&34;鼠牛虎兔龙蛇马羊猴鸡狗猪&34;,MOD(&34;19&34;&MId(B9,7,2)-4,12)+1,1),MId(&34;鼠牛虎兔龙蛇马羊猴鸡狗猪&34;,MOD(MId(B9,7,4)-4,12)+1,1)),&34;&34;)

如图5所示：(利用出生年月提取生肖)

图5

如图6所示：(利用身份证号直接提取生肖)

图6

五．身份证号提取籍贯

函数公式：=IFERROR(VLOOKUP(LEFt(B2,6),户籍行政区划代码表!A:B,2,1),&34;&34;)

说明：

a.籍贯信息需要有户籍行政区划代码表作为附表，通过函数代码换算为地区就可以

b.户籍行政区划代码表，可以通过网络下载最新版的区划表，根据需求做好调整使用即可。

c.外层嵌套IFERROR函数是为了空单元格规避错误值

如图7、8所示：

图7

图8

六．身份证号提取星座

函数公式：=IFERROR(LOOKUP(--TEXT(C2,&34;mdd&34;),{101,&34;摩羯&34;;120,&34;水平&34;;219,&34;双鱼&34;;321,&34;白羊&34;;420,&34;金牛&34;;521,&34;双子&34;;621,&34;巨蟹&34;;723,&34;狮子&34;;823,&34;处女&34;;923,&34;天秤&34;;1023,&34;天蝎&34;;1122,&34;射手&34;;1222,&34;摩羯&34;})&&34;座&34;,&34;&34;)

说明：

a. C2的数字转换为日期，取其中的月和日对应的数字，然后与后面的数字模糊匹配，找到比这个数字小的数字，然后取对应的星座。

b.TEXT(C2,&34;mdd&34;) 是把C2的数字转换为日期，取其中的月和日对应的数字，在Excel里日期都是以一个数字来记录的，因此数字能转换为日期，这里不要年，只保留月和日，因为计算星座只要月日。

c.TEXT(C2,&34;mdd&34;) 前面加两格减号--TEXT(C2,&34;mdd&34;) 是把字符串转换为纯数字，得到的数字作为lookup函数的第一个参数，表示要查找的数字。

d.后面的{}里是查询数组，逗号表示是在同一行，分号表示下一行，这里就是一个数字一个逗号一个星座一个分号为一行，其实就是两列，第一列是数字，第二列是星座，第一列的数字是两个星座分隔的日期。

使用lookup函数查询前面那个数字在后面这个数组中哪个区间，然后就得到对应的星座了。

e.外层嵌套IFERROR函数是为了空单元格规避错误值

f.上面星座的提取是以，身份证号提取的出生年月日来提取计算的，实际中也可用身份证号直接提取星座，其函数公式为：

=IFERROR(LOOKUP(--MId(B2,11,4),{101,&34;摩羯&34;;120,&34;水瓶&34;;219,&34;双鱼&34;;321,&34;白羊&34;;420,&34;金牛&34;;521,&34;双子&34;;621,&34;巨蟹&34;;723,&34;狮子&34;;823,&34;处女&34;;923,&34;天秤&34;;1023,&34;天蝎&34;;1122,&34;射手&34;;1222,&34;摩羯&34;})&&34;座&34;,&34;&34;)如图9所示：（利用出生年月提取星座）

图9

如图10所示：（利用身份证号直接提取星座）

图10

注：

1. 在提取函数中都增加了一个函数IFFRROR，目的是为了避免空单元格的错误值，方便实际的录入操作，实际中可以根据情况可以不使用此函数。
2. 出现的有些函数只做了部分用途功能介绍，对于excel函数的各种用法，需读者在不断学习中理解。