Excel和身份证不得不说的那些事儿

经常有从事HR方面工作的朋友会来询问一些与身份证信息提取相关的问题，在写作《Excel 2007实战技巧精粹》的时候，当中其实有专门一章的内容是与这个话题相关的。现在再通过这篇文章来简单的总结一下与之相关的一些函数公式用法。

目前身份证的类型简单来讲有一代和二代之分，一代是15位号码，二代是18位号码，与一代的不同之处在于补足了四位年份日期并且在末尾添加了一个校验字符。二代身份证自2005年开始换发，一代可以同时通用。但根据规定，在2013年1月1日起将全面停止使用一代身份证。

身份证的号段含义如下：

15位身份证：前2位表示所属省份代码，3~6位表示所属城市和区县代码，7～12表示出生日期，格式为YYMMDD，13～15位是个人顺序码，其中第15位可以标识性别，为奇数表示男性，为偶数表示女性。

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

 

【正确输入】

在Excel当中使用身份证号码时，首先需要注意的就是正确输入方法。由于Excel单元格只支持15位有效数字，当输入的整数数值超过15位时后面输入的内容在回车确认以后都会自动转化成0，这样会造成18位身份证号码的输入错误。这是一个经常容易踏入的错误陷阱，如果不注意，会导致大量的错误数据产生。

要避免这样的错误，可以使用文本方式来输入号码，因为对于文本类型的数据不存在上述15位的限制问题。可以在输入号码之前，先添加一个半角的单引号再输入其他数字，这样完成输入的结果就是一个文本型数据。或者也可以在输入之前事先将单元格格式设置为【文本】再行输入。但是如果在输入完成以后再更改单元格格式就不会有效果。

 

【自动验证输入】

如果你的表格是要下发给其他人由他们来填写信息，为了限定单元格内必须输入身份证并且验证其输入的正确性，可以通过【数据有效性】功能来加以设定。

要限定单元格中输入的内容只能是身份证号码，可以根据下面这几个条件来判断：

条件1：输入长度为15位或18位

由此可以得到公式：

=OR(LEN(A1)=15,LEN(A1)=18)

条件2：前17位必须都是数字

=ISNUMBER(-LEFT(A1,17))

其中对于15位的号码来说，LEFT(A1,17)仍然可以准确的获取其15位字符，不会有额外的占位。

这个公式从严格来讲并未完全满足条件，对于包含小数等数学形式它也会包容。如果需要更严谨的话可以使用下面这个公式来判断：

=ISNUMBER(SUMPRODUCT(1*MID(A1&10,ROW($1:$17),1)))

条件3：如果不全都是数字，那么它只能是18位，并且末尾字符是字母“X”

=OR(ISNUMBER(-A1),AND(LEN(A1)=18,RIGHT(A1)="X"))

上述三个条件全部满足（条件1 And 条件2 And 条件3）的情况下，可以保证A1当中输入的是身份证号码类型的数据（在这里没有验证它的具体内容是否正确，也没有验证它是否是文本型输入，如有必要，可以在条件中继续增加），得到下面的数据有效性判断公式：

=AND(OR(LEN(A1)=15,LEN(A1)=18), ISNUMBER(-LEFT(A1,17)),OR(ISNUMBER(-A1),AND(LEN(A1)=18,RIGHT(A1)="X")))

假定要在A1单元格内进行设定，可以选中A1单元格，然后在菜单栏上点击【数据】——【有效性】（2007以上版本是在【数据】选项卡中点击【数据有效性】），打开【数据有效性】对话框，在【设置】选项卡的【允许】下拉框中选择【自定义】，然后在下方【公式】文本框中输入上面的公式。

 

【提取生日】

可以通过公式将15位号码中第7位开始的6位出生日期数字转换成真实日期值（可以在Excel当中进行日期运算的数值），由于这个6位的格式是YYMMDD，因此在公式处理中要在前面添加“19”补全四位年份代码，否则当YY小于30的时候（1930年之前出生），Excel自动识别的功能会将其转换为20YY年。

对于18位身份证，需要提取的是其中第7位开始的8位数字，这8位数字的格式是YYYYMMDD，可以很方便转化成真实日期数据。

这个公式可以分以下几个步骤来设计：

步骤1：提取6位或8位生日数字

=MID(A1,7,IF(LEN(A1)=15,6,8))

如果愿意，还可以简化成这样：

=MID(A1,7,LEN(A1)/2.2)

步骤2：对于15位号码，需要补足前面两位“19”数字

=RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8)

步骤3：将上面得到的8位数字转换成真实日期数值

=TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00")+0

步骤3的公式就是最终可以从A1单元格中的身份证号码取得其出生日期的公式。如果通过这个公式得到的结果是一个5位数的数值，不要惊讶，这就是Excel当中日期值的本来面目，把单元格格式设置为【日期】就可以看到实际的日期显示。

 

【计算年龄】

年龄的计算实际上就是通过前面得到的出生日期来用DATEDIF函数计算到当前所相差的年份数（周岁）：

=DATEDIF(TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00"),NOW(),"Y")

如果你愿意，也可以通过生日来计算星座、生肖、法定退休年龄、晚婚晚育年龄等等。

 

【判别性别】

15位号码的最后一位，18位号码的倒数第二位都可以用来判断性别，奇数表示男性，偶数表示女性。

可以提取这位数字、或是提取以这个数字结尾的多位数字（一个数的奇偶性可以由它的末尾数字来判定）进行奇偶性判断来得到性别结果。这里需要注意的是，由于Excel的运算限制，不能提取一个位数太多的数字来判断奇偶性。

也可以分成几个步骤来设计完整的公式：

步骤1：提取数字

=MID(A1,15,3)

对于15位身份证号码，上述公式提取到是其末位数字，不包含其他字符占位。而对于18位的身份证号码，上述公式提取到的是其15～17位数字。

步骤2：判断奇偶性

=IF(MOD(MID(A1,15,3),2), "男","女")

通过MOD函数除以2取余数来进行奇偶判断，如果余数为1，表示奇数，得到男性判断，如果余数为0，得到女性判断。在IF函数中，第一参数不为0时都可以得到TRUE的判断结果。

或者也可以使用下面的公式：

=IF(-1^MID(A1,15,3)=1,"女","男")

通过负数的幂次方来进行判断，如果得到正数，说明其幂数是偶数，否则为奇数。

 

【获取所属地】

身份证号码的前两位是省份行政区的标识代码，3～6位数字是城市和区县代码，前6位的完整数字可以通过查询对应关系得到相应的所属地信息。

信息表可以在这里下载：微盘链接（http://vdisk.weibo.com/s/gnJoe）

 

【验证有效性】

二代身份证的最后一位校验位可以验证号码有效性。校验位的字符可以通过前17位号码计算取得，如果计算得到的字符（0123456789X）与实际最末位字符相符，可以大致认为这个号码真实有效。

校验位算法如下：

参数1：取前17位数字，以i表示其所在位数，从左向右依次为17、16、15...2、1

参数2：每一位上的身份证号码数字记为Ai

参数3：计算MOD(2^i,11)，记为Wi

参数4：将每一个Ai与对应位置上的Wi相乘

将参数4得到的17个数值求和，然后除以11求余数，得到一个数值n

通过n在下面的对应表中进行查表，得到最终的校验位字符：

计算值n	0	1	2	3	4	5	6	7	8	9	10
校验码	1	0	X	9	8	7	6	5	4	3	2

 

 

因此，可以通过下面这个公式来计算A1单元格中18位身份证号码的最后一位校验码：

=MID("10X98765432",1+MOD(SUMPRODUCT(MID(LEFT(A1,17),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),1)

通过这个公式计算得到的字符与A1当中实际的末位字符相对照，就可以判断这个身份证号码的有效性

通过这个算法，还可以计算得到一代身份证升级到二代身份证的号码结果，假定一代15位号码位于A1单元格内：

=REPLACE(A1,7,,19)&MID("10X98765432",1+MOD(SUMPRODUCT(MID(REPLACE(A1,7,,19),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),1)

 

在2013年1月1日以后，政策上不再允许使用一代身份证，15位号码将逐渐淡出视线，前面的许多公式都是为了自适应15位和18位两种号码而存在的，如果仅仅需要针对18位号码，这些公式将变得更简单。

验证输入：

=AND(LEN(A1)=18,ISNUMBER(-LEFT(A1,17)),OR(ISNUMBER(-A1),RIGHT(A1)="X"))

提取生日：

=TEXT(MID(A1,7,8),"0-00-00")+0

计算年龄：

=DATEDIF(TEXT(MID(A1,7,8),"0-00-00"),NOW(),"Y")

判别性别：

=IF(-1^MID(A1,15,3)=1,"女","男")

 

附件表格下载：身份证公式 (2015)