身份证号码函数

用vlookup函数，及其他各项功能，通过身份证号得出了出生日期、性别、户籍所在地等多项信息，并加入了身份证号正确性验证机制和15位、18位号码的转换功能。 详细制作步骤 要想了解身份证的秘密，首先让我们了解一下我国现行的公民身份证标准。 1. 我国现行使用公民身份证号码有两种标准。 2. 15位身份证号码（六位数字地址码，六位数字出生日期码，三位数字顺序码）。 3. 18位身份证号码（六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码）。 4. 地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码。 5. 日期码表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。 6. 顺序码表示同一范围内对同年、月、日出生的人员编定的顺序号。奇数分给男性，偶数分给女性。 7. 校验码是根据前面十七位数字码计算出来的检验码。 复制代码 了解了以上知识后，让我们来制作Excel表。 1. 新建一个Excel文档， 2. 将Sheet1重命名为“身份证信息”，在第1行各列中依次输入“身份证号码、十五位身份证号码、十八位身份证号码、性别、出生日期、年龄、省份、市、区县”等文字，并将“出生日期”列设置为日期（yyyy.mm.dd）格式，其余列设置为文本格式。 3. 将Sheet2重命名为“区域信息”，从国家统计局下载到最新县及县以上行政区划代码，经整理后分别导入到“区域信息”的A、B两列中，以便查询。 复制代码 到此，Excel表框架搭建完成，我们来写入函数完成查询。 第一步 判断号码是否为正确的身份证号 一个正确的身份证号码，要符合以下几个标准： ①应为15位或18位； ②要包含数字（0－9）或字符（X）； ③18位身份证号最后一位是真正的校验码。 如果上面三个条件都满足，则是正确的身份证号。 我们利用Excel单元格的“有效性”限制来完成校验。 具体方法是选择“身份证信息”表，全选“身份证号码”列，选择“数据－有效性”菜单命令，在弹出的窗口“设置”标签中，将有效性条件的“允许”设置为“自定义”、“公式”设置为 ＝OR(LEN(A2)=15,IF(LEN(A2)=18,MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2)))，如图一。 2007-10-28 20:33 上传 下载附件 (32.42 KB) 图一 在“出错警告”标签中，设置出错提示信息，如图二。 2007-10-28 20:33 上传 下载附件 (36.54 KB) 图二 第二步 15位和18位身份证号码的互换 具体方法是选择“身份证信息”表，在B2单元格中输入=IF(LEN(A2)=15,A2,LEFT(REPLACE(A2,7,2,),15))； 在C2单元格中输入{=IF(LEN(A3)=15,REPLACE(A3,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A3,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A3)} 这里要注意是数组！ 第三步 判断性别 如果是15位的，则取最后一位； 如果是18位的号，则取倒数第二位； 最后判断该数值的奇偶性即可。 具体方法是选择“身份证信息”表，在D2单元格中输入=IF(A2<>"",IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女"),) 第四步 判断出生日期 如果是15位的，则取第7至12位，在年份数前加上“19”； 如果是18位的，则直接取第7至14位； 具体方法是选择“身份证信息”表，在E2单元格中输入=IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,) 第五步 判断年龄 这里利用一个Excel的隐藏函数 DATEDIF（） 基本语法： =DATEDIF(开始日期，结束日期，单位代码），用“Y”来表示年份。 具体方法是选择“身份证信息”表，在F2单元格中输入=IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00"),TODAY(),"Y"),) 第六步 判断籍贯，包括省份、市、区县 这是本期比赛最难的一部分，关键在于如何多重判断省份、市、区县信息。 首先要知道地址码的构成。代码前两位是省或直辖市代码，中间两位是市代码，最后两位是区县代码。 其次是数据存放。“区域信息”表中已经存放了查询所需的数据，将数据区命名为“Code”以便查询。如图三 2007-10-28 21:44 上传 下载附件 (71.36 KB) 图三 最后利用VLOOKUP函数来查询数据。 基本语法：＝VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 具体方法是选择“身份证信息”表， 在G2单元格中输入=IF(A2<>"",VLOOKUP(LEFT(A2,2)&"0000",code,2,),) 在G2单元格中输入=IF(A2<>"",VLOOKUP(LEFT(A2,4)&"00",code,2,),) 在G2单元格中输入=IF(A2<>"",VLOOKUP(LEFT(A2,6),code,2,),) 最后一步 根据需要拖拽 刚才所写的公式只是在第2行写入的，大家可以根据需要向下拖拽，具体方法略去。