EXCEL提取身份证信息以及鉴别身份证真伪的技术文件.doc

EXCEL 中如何提取身份证出生日期、性别、检验身份证号码的正确性中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为：１５位：６位数字常住户口所在县市的行政区划代码，６位数字出生日期代码，３位数字顺序码１８位：６位数字常住户口所在县市的行政区划代码，８位数字出生日期代码，３位数字顺序码和１位检验码其中３位数字顺序码，是为同一地址码的同年同月同日出生人员编制的顺序号，偶数的为女性，奇数的为男性１、提取籍贯地区的行政区划代码（A2 为身份证号，下同）１５与１８位通用：=LEFT(A2,6)如果有一个编码和省份地区的对照表，可以用 VLOOKUP 函数来提取地区信息２、提取出生日期信息　１５位：=--TEXT(19&MID(A2,7,6),"#-00-00")１８位：=--TEXT(MID(A2,7,8),"#-00-00")１５与１８位通用：=--TEXT(IF(LEN(A2)=15,19,"")&MID(A2,7,6+IF(LEN(A2)=18,2,0)),"#-00-00")简化公式：=--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")(请将输入公式的单元格格式设置为日期格式）３、提取性别信息１５位：=IF(MOD(RIGHT(A2),2)=1,"男","女")１８位：=IF(MOD(MID(A2),17,1)=1,"男","女")１５与１８位通用：=IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1,"男","女")简化公式：=IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女")４、检验身份证号码的正确性１８位身份证号码的最后一位是检验码，它是根据身份证前 17 位数字依照规则计算出来的，其值０～９或 X。

一般情况只要有一位数字输入错误，依照规则计算后就会与第 18 位数不符当然不排除按错误号码计算后恰好与检验码相符的情况，但这种情况出现的可能性较低因此，对１８位号码的验证采用如下公式：=MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2,1)对于１５位身份证，由于没有检验码，我们只能简单地去判断出生日期代码是否是一个有效的日期，避免输入一些像“731302”或“980230“等这样不存在的日期ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00"))综合１５位和１８位后的通用公式为：=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00"))))由于目前１５位身份证号码已经很少了，如果对１５位的号码不需要作进一步的判断，则公式可以简化成：=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),LEN(A2)=15)将上面的公式放到 B2 单元格，如果结果为 TRUE，则身份证号是正确的，结果为 FALSE 则是错误的。

你也可以将上述公式放在数据有效性中，防止录入错误的身份证号操作方法:选择需要输入身份证的全部单元格区域,比如 A2:A10,点菜单"数据"-"有效性",在"允许"的下拉框中选择"自定义",在"公式"输入上面的 15 位和 18 位通用公式,确定以后即可注意:公式里的"A2"是你刚才选定要输入身份证的单元格区域的第一个单元格,如果你是要在 C3:C20 输入身份证号,则将公式里的"A2"改为"C3"另外,你也可以先设置好某单个单元格的数据有效性(这时公式的 A2 改为选定的单元格),再用格式刷将其格式刷到其他需要相同设置的单元格５、１５位升为１８位=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)６、１８位转换为１５位=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)7、示例表中公式:B2　=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00"))))C2　=IF(A2"",IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女"),)E2　=IF(A2"",VLOOKUP(LEFT(A2,2),地区表!A:D,2,),)H2　=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)I2　=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)2010-03-02 16:05眼下是大学生求职应聘的黄金季节，人事主管小刘忙得不亦乐乎，她负责把应聘者的个人信息录入 Excel，确保信息真实可信是必须解决的问题。

为此，小刘特地向信息部主管小张求教，学会了从身份证“挖掘”个人信息的方法，又快又好地完成了招聘的前期准备工作可谓：“整理工作无穷尽，信息问题难小刘，Excel 函数应用，从此更上一层楼一．数据录入快又准小刘负责录入的个人信息内容如图 1 所示，除了“序号”、“姓名”和“身份证号码”以外，其余信息由小张设计公式从“身份证号码”中“挖掘”1．别让数据变“乱”刚开始工作小刘就碰到了难题，她输入的身份证号码变成了“1.10155E+17”之类请教小张之后才知道“身份证号码”要用“文本”格式实现这一点的第一种方法是选中D 列右击鼠标，选择快捷菜单中的“设置单元格格式”，打开对话框的“数字”选项卡选中，选中“分类”下的“文本”然后“确定”即可第二种方法是在输入的身份证号码前加一个单引号，Excel 就可以把输入的数字变为“文本”了第三种方法是选中 D列，单击“格式”菜单下的“单元格”命令打开对话框，按如图 1 所示选中“分类”下的“自定义”然后在“类型”框中输入一个“@”再“确定”即可小刘按小张教的方法继续操作，录入的身份证号码就一切正常了2．录入校验 错误靠边由于前来应聘的大学生高达几百人，一旦身份证号码录入出错可是要扣“银子”的，于是小刘“命令”小张拿出解决办法。

在小刘的“威逼利诱”面前，小张很快想出了“高招”：第一步选中存放身份证号码的数据区域（例如“D2：D800”），单击 Excel“数据”菜单下的“有效性”命令，打开“数据有效性”对话框的“设置”选项卡在“允许”下拉列表中选择“自定义”，接着在如图 2 所示“公式”框中输入“=COUNTIF（D:D，D2）=1”　　第二步打开“出错警告”选项卡，在“标题”框内输入“数据重复”，并按如图 3所示输入更详细的警告信息，单击“确定”按钮将打开的对话框关闭当然，这一步是可选的，使用时可以根据具体情况取舍此后只要在当前单元格中输入了重复数据，Excel 就会弹出“数据重复”对话框告知小刘，并拒绝接受已经输入的重复数据除了防止录入身份证号码出现重复以外，还要防止小张输入的号码长度不足 15 位或 18 位接下来的第三步仍然是选中录入身份证号码的数据区域（例如“D2：D80”），单击“格式”菜单下的“条件格式”命令打开如图 4 所示对话框，在“条件一”下拉列表中选择“公式”，然后在中间的框内输入公式“=IF（LEN（D10）18）”第四步单击如图 4 中的“格式”按钮打开对话框，在“字体”选项卡中选择合适的颜色或删除线等。

之后如果 D 列中输入的数据长度不是 15 位或 18 位，其字体就会显示前面选择的颜色（例如红色）3．录后检查 万无一失看到这里小刘忽然问道：假如上面的操作执行前已经录入了部分数据，那么有没有办法检查录入的身份证号码是否重复？稍微思考了一会，小张设计了一个带有公式的“条件格式”，圆满解决了小刘提出的问题小张操作的第一步是选中如图 1 中的 D2 单元格，单击“格式”菜单中的“条件格式”命令，打开如图 5 所示对话框在“条件 1”下拉列表选择“公式”，然后在右边的输入框中输入公式“=COUNTIF（$D:$D，D2）>1”它的用途是计算 D 列单元格中的数据是否与 D2 相同，再进行比较以确定这个结果是否大于 1（为“真”）如果计算结果大于 1（即存在相同的身份证号码），就应用右边设置的条件格式，否则保持单元格的格式不变第二步是设置比较结果为“真”时应用的条件格式，方法是单击“格式”按钮打开如图6 所示对话框，在“颜色”下拉列表选中条件为“真”时显示的字体颜色（例如红色）也可以根据需要选择其它字形或选中“删除线”，连续两次单击“确定”按钮将打开的对话框关闭第三步是将 D2 单元格中的条件格式应用于 D 列的其它单元格，方法是选中 D2 单元格单击工具栏的“复制”按钮。

再选中 D 列中需要应用条件格式的区域（例如 D3：D80 区域），单击“编辑”菜单中的“选择性粘贴”命令，打开对话框选中“格式”单击“确定”，那么 D 列中存在的重复数据就会显示前面设置的条件格式，例如用红色带删除线的字体身份证号码这时小刘忽然说到：如果我只想看出 D 列中重复录入的身份证号码，那么应当怎样操作？小张说：可以将如图 5 所示对话框中的公式修改为“=COUNTIF（$D$2:$D2，D2）>1”这个公式中对数据区域结束单元格（即“$D2”）的引用会随它向下复制的过程而变化，例如格式复制到 D3 单元格后公式变为“=COUNTIF（$D$2:$D3，D3）>1”因此查找是否存在重复数据时，公式进行比较的范围是从当前单元格向上直到数据区域第一个单元格（即“$D2”），所以比较的结果只对第一个以后的相同名称的数据应用条件格式二．隐藏信息充分“挖掘”当小刘将姓名和身份证号码输入如图 1 所示的工作表以后，小张设计的公式马上从身份证号码中“挖掘”出了信息不过小刘的好学精神上来了，非要小张说清楚“挖掘”信息的基本原理，小张只好一一给她解释：1．性别根据现行居民身份证号码的编码规定，18 位身份证编码的 1~6 位为归属地（公民第一次申领身份证时常住户口所在地的行政区划，下同）代码，7~10 位为 4 位的出生年份，11~12 位为出生月份，13~14 位为出生日期，17 位（也就是倒数第二位）为性别（奇数为男，偶数为女），18 位（也就是最后一位）则是校验位。

而早期使用的是 15位的身份证编码，它的 1~6 位为归属地代码，7~8 位是 2 位的出生年份，9~10 位为出生月份。