身份证号函数使用教学

1、身份证号的秘密三茅网站给出了一个“用函数从身份证号提取户籍.xls”文件下载。（如果有的同学下载后不能打开，请在文件后面加扩展名“.xls”就可以打开。）有些人不知道怎么用，本文讲解其用法。要想了解身份证号码的秘密，首先让我们了解一下我国现行的公民身份证标准。 我国现行使用公民身份证号码有两种标准：1. 15位身份证号码（数字依次排列顺序是六位数字地址码，六位数字出生日期码，三位数字顺序码）。2. 18位身份证号码（数字依次排列顺序是六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码）。地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码，又称户籍所在地。日期码表示编码

2、对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。顺序码表示同一范围内对同年、月、日出生的人员编定的顺序号。奇数分给男性，偶数分给女性。校验码是根据前面十七位数字码计算出来的检验码。因为人力资源部门经常用的身份证号码，在EXCEL软件中，提供了一些函数，可以从身份证号码中，直接提取几个内容，如性别，出生日期，当前年龄，户籍所在地等。对于身份证号码的用法，整理如下：1. 判断号码是否为正确的身份证号一个正确的身份证号码，要符合以下几个标准：应为15位或18位；要包含数字（09）或字符（X）；18位身份证号最后一位是真正的校验码。如果上面三个条件都满足，则是正确的身份证号。我

3、们利用Excel单元格的“有效性”限制来完成校验。具体方法是：选择“身份证号”所在的列的第一个单元格，选择菜单上的“数据有效性”菜单命令，在弹出的窗口“设置”标签中，将有效性条件的“允许”设置为“自定义”、“公式”设置为：=OR(LEN(B2)=15,IF(LEN(B2)=18,MID("10X98765432",MOD(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九个位置的操作。注

4、意：本文身份证号所在的单元格是B2，所以公式中是B2身份证号所在的单元格格式为文本，下同。B212345接下来，设置出错报警，点“出错警告”如下图：6978确定以后，就设置完成了。2、 15位升18位身份证号码具体方法是选择“身份证号码”的同一行的其它列，如本文初始数据在B2单元格，18位号码放在C2单元格（格式为文本），在此单元格C2的公式中输入：=IF(LEN(B2)=15,CONCATENATE(MID(B2,1,6),"19",MID(B2,7,9),MID("10X98765432",MOD(MID(B2,1,1)*7+MID(B2,2,1)*

5、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单元

6、格，性别放在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"

7、),(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=""

8、;,"",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

9、")>TODAY(),"错误",1*(TEXT(MID(B2,7,6+(LEN(B2)=18)*2),"#年00月00日")如图：5、计算年龄这里利用一个Excel的隐藏函数 DATEDIF（）基本语法： =DATEDIF(开始日期，结束日期，单位代码），用“Y”来表示年份。具体方法是选择“身份证号码”的同一行的其它列，如本文初始数据在B2单元格，年龄放在F2单元格（格式为常规），在此单元格F2的公式中输入：=IF(ISERROR(1*(TEXT(MID(B2,7,6+(LEN(B2)=18)*2),"#-00-00"

10、),"错误",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(),"错误",DATEDIF(TEXT(MID(B2,7,6+(LEN(B2)=18)*2),"#-00-00"),TODAY(),"y")如图：6、提取籍贯，包括省份、市、区县代码前两位是省或直辖市代码，中间两位是市代码，最后两位是区县代码。其次是数据存放。在这里，我们需要制作一个“区域信息”表，表中存放最新的“县及县以上行政区划代码”，有这个代码，才可以查询所需的数据。“县及县以上行政区划代码”可以从国家统计局下载到（下载地址是：具体操作是：在当前Excel文档中，将下载的数据整理到一个子表中，（或从其它表中复制过来，为了数据最新，自己下载整理最好），本例中子表重命名为：“地址码”。在子表中，选中两列数据，将原来显示A1的位置的内容，修改成“区域”，回车后，则显示