身份证号函数使用教学剖析.doc

身份证号的秘密 三茅网站给出了一个“用函数从身份证号提取户籍.xls”文件下载。（如果有的同学下载后不能打开，请在文件后面加扩展名“.xls”就可以打开。）有些人不知道怎么用，本文讲解其用法。 要想了解身份证号码的秘密，首先让我们了解一下我国现行的公民身份证标准。 我国现行使用公民身份证号码有两种标准： 15位身份证号码（数字依次排列顺序是六位数字地址码，六位数字出生日期码，三位数字顺序码）。 18位身份证号码（数字依次排列顺序是六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码）。 地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码，又称户籍所在地。 日期码表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。 顺序码表示同一范围内对同年、月、日出生的人员编定的顺序号。奇数分给男性，偶数分给女性。 校验码是根据前面十七位数字码计算出来的检验码。 因为人力资源部门经常用的身份证号码，在EXCEL软件中，提供了一些函数，可以从身份证号码中，直接提取几个内容，如性别，出生日期，当前年龄，户籍所在地等。对于身份证号码的用法，整理如下： 1. 判断号码是否为正确的身份证号 一个正确的身份证号码，要符合以下几个标准： ①应为15位或18位； ②要包含数字（0－9）或字符（X）； ③18位身份证号最后一位是真正的校验码。 如果上面三个条件都满足，则是正确的身份证号。 我们利用Excel单元格的“有效性”限制来完成校验。 具体方法是： 选择“身份证号”所在的列的第一个单元格，选择菜单上的“数据－有效性”菜单命令，在弹出的窗口“设置”标签中，将有效性条件的“允许”设置为“自定义”、“公式”设置为： =OR(LEN(B2)=15,IF(LEN(B2)=18,MID(10MOD(SUM(MID(B2,ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1)=RIGHT(B2)))， 如图1，2，3，4，5，6，7，8，9九个位置的操作。 注意：本文身份证号所在的单元格是B2，所以公式中是B2 身份证号所在的单元格格式为文本，下同。 接下来，设置出错报警，点“出错警告”如下图： 确定以后，就设置完成了。 2、 15位升18位身份证号码 具体方法是选择“身份证号码”的同一行的其它列，如本文初始数据在B2单元格，18位号码放在C2单元格（格式为文本），在此单元格C2的公式中输入： =IF(LEN(B2)=15,CONCATENATE(MID(B2,1,6),19,MID(B2,7,9),MID(10MOD(MID(B2,1,1)*7+MID(B2,2,1)*9+MID(B2,3,1)*10+MID(B2,4,1)*5+MID(B2,5,1)*8+MID(B2,6,1)*4+1*2+9*1+MID(B2,7,1)*6+MID(B2,8,1)*3+MID(B2,9,1)*7+MID(B2,10,1)*9+MID(B2,11,1)*10+MID(B2,12,1)*5+MID(B2,13,1)*8+MID(B2,14,1)*4+MID(B2,15,1)*2,11)+1,1)),B2) 3、 提取性别 如果是15位的，则取最后一位； 如果是18位的号，则取倒数第二位； 最后判断该数值的奇偶性即可。 具体方法是选择“身份证号码”的同一行的其它列，如本文初始数据在B2单元格，性别放在D2单元格（格式为文本），在此单元格D2的公式中输入： =IF(B2=,,IF(AND(LEN(B2)15,LEN(B2)18),错误,IF(ISERROR(1*(TEXT(MID(B2,7,6+(LEN(B2)=18)*2),#-00-00))),错误,IF(OR((1*(TEXT(MID(B2,7,6+(LEN(B2)=18)*2),#-00-00)))VALUE(1905-01-01),(1*(TEXT(MID(B2,7,6+(LEN(B2)=18)*2),#-00-00)))TODAY()),错误,IF(MOD(MID(B2,15+(LEN(B2)=18)*2,1),2)=0,女,男))))) 如图： 4、提取出生日期 如果是15位的，则取第7至12位，在年份数前加上“19”； 如果是18位的，则直接取第7至14位； 具体方法是选择“身份证号码”的同一行的其它列，如本文初始数据在B2单元格，出生日期放在E2单元格（格式为日期），在此单元格E2的公式中输入： =IF(B2=,,IF(AND(LEN(B2)15,LEN(B2)18),错误,IF(ISERROR(1*(TEXT