用vlookup函数，及其他各项功能，通过身份证号得出了出生日期、性别、户籍所在地等多项信息，并加入了身份证号正确性验证机制和15位、18位号码的转换功能。
详细制作步骤
要想了解身份证的秘密，首先让我们了解一下我国现行的公民身份证标准。
1.我国现行使用公民身份证号码有两种标准。
2.15位身份证号码（六位数字地址码，六位数字出生日期码，三位数字顺序码）。
3.18位身份证号码（六位数字地址码，八位数字出生日期码，三位数字顺序码和
一位数字校验码）。
4.
5.地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码。
6.日期码表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、
日之间不用分隔符。
7.顺序码表示同一范围内对同年、月、日出生的人员编定的顺序号。奇数分给男
性，偶数分给女性。
8.校验码是根据前面十七位数字码计算出来的检验码。
复制代码
了解了以上知识后，让我们来制作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)))，如图一。
在“出错警告”标签中，设置出错提示信息，如图二。
第二步 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,,1 9),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"),T ODAY(),"Y"),)
第六步判断籍贯，包括省份、市、区县
这是本期比赛最难的一部分，关键在于如何多重判断省份、市、区县信息。
首先要知道地址码的构成。代码前两位是省或直辖市代码，中间两位是市代码，最后两位是区县代码。
其次是数据存放。“区域信息”表中已经存放了查询所需的数据，将数据区命名为“Code”以便查询。如图三
最后利用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行写入的，大家可以根据需要向下拖拽，具体方法略去。
