需在某一单元格中录入身份证号码,如何才能判断所录入的身份证号码是正确的?
具体的录入方式如下:
当A1为0时,A2则必须录入18位的身份证号码;此时需校验A2的有效性
当A1非0时,A2则为非身份证号码,此时不需校验A2的有效性。
1、这里有一个Excel文件,里面录入了一组身份证号码(非真实人的身份证号)其中一个为错误号码,在没有认真核对以前很难发现。
2、新建一列校验结果列,双击单元格,粘贴以下公式代码
=IF(A3="","",(IF(MID("10X98765432",MOD(SUMPRODUCT(MID(A3,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=MID(A3,18,18),"正确","错误")))
将公式中三处 A3 修改为你要校验的身份证号码所在单元格名,然后按回车键,公式自动完成校验。
3、鼠标放单元格右下角变十字时,按住下拉,完成所有行身份证号的校验,或双击本单元格右下角完成所有行校验。
4、认真核对显示校验错误的身份证号码,更正后校验列会变为“正确”。
5、身份证最后一位为校验位,但不能完全保证身份证号码的正确性,如果同时出现两位或多位号码错误,校验结果也可能显示“正确”,如图,
试了一下,不行喔。我录入的单元格位置是N7,已把公式上的D2全改为了N7。
是在有效性那是设,是吗?
不是,直接作为公式输入你想要显示结果的单元格。此单元格格式必须是常规格式。
追问这样不行啊!我是需在N7上录入身份证号码,录入完毕后必须马上提示出错,不能在另外的单元格显示出错。有办法实现吗?
再次感谢!
就在录入的单元格内显示出错,如果正确就不需提示,有办法实现吗?谢谢!
追答在任意单元格显示"有效","无效!!"
公式
=IF(A1=0,IF(AND(LEN(A2)=18,CHOOSE(MOD(SUMPRODUCT(MID(A2,ROW($1:$17),1)*2^(18-ROW($1:$17))),11)+1,1,0,"X",9,8,7,6,5,4,3,2)&""=RIGHT(A2)),"有效","无效!!"),"")
-----------------------------------------------------------------
"就在录入的单元格内显示出错,如果正确就不需提示"
用 =OR(A10,AND(LEN(A2)=18,CHOOSE(MOD(SUMPRODUCT((0&MID(A2,ROW($1:$17),1))*2^(18-ROW($1:$17))),11)+1,1,0,"X",9,8,7,6,5,4,3,2)&""=RIGHT(A2)))
设置数据有效性
公式中的A1 , A2 可以根据实际需要调整
试了一下第二种的,只能是当A1为空时,才对A2作校验;当如A1有值时,则不作校验。请问该如何,请再指教,谢谢!
追答"当A1为0时,A2则必须录入18位的身份证号码;此时需校验A2的有效性
当A1非0时,A2则为非身份证号码,此时不需校验A2的有效性。"
按你上面的这个要求,
当A1为0 时,对A2作校验;
当A1非0 时,不作校验