【菁培班PK】二代身份证号验证

2组发难，1组接招~ 关于二代身份证号的验证，是一个普遍存在的问题，在办公场景中非常常见。需求：请用三种不同的算法，利用身份证号验证位验证其合法性。二代身份证号的编码规则：第1-6位：出生地编码，其中1-2代表省级行政区，3-4为地级市，5-6为县级市；第7-14位：出生年月日；第15-16位：户籍地代码；第17位：性别流水号，13579位男性，24680为女性；第18位：验证码；计算方式为每一位按对应权重的乘积和，除以11取余数后，匹配对应位的值。权重及对位匹配如图所示。本题要求，利用验证位来验证身份证号的合法性，此处忽略地区编码的非法情况，仅按照验证位的编码规则判断。要求使用三种不同的思路和方法。参考文件：【金山文档】二代身份证号验证 https://kdocs.cn/l/cgW3KyxUMLND

菁培班

2023-08-16 10:40:31 北京

7 +1

1 +1

全部评论 14

苏禹嘉

【金山文档】二代身份证号验证-答案 https://kdocs.cn/l/cqxkbzVRbiQt

2023-08-17 07:13:59 · 北京

苏禹嘉

【答案推荐】法5和法6，两种最简洁的公式。引用部分我换成了黑括号，这样粘贴到表格里就能立马看到是哪里需要改了。 =RIGHT(【引用】)=MID("10X98765432",MOD(SUM(MID(【引用】,SEQUENCE(17),1)*2^(18-SEQUENCE(17))),11)+1,1) =IFERROR(--RIGHT(【引用】),10)=MOD(12-SUM(MID(【引用】,SEQUENCE(17),1)*MOD(2^SEQUENCE(17,,17,-1),11)),11)

2023-08-17 06:14:41 · 北京

苏禹嘉

【答案揭晓2-4】解题思路：【法6】利用了【答案揭晓1-1】的所有特征，公式： =IFERROR(--RIGHT($A$1),10)=MOD(12-SUM(MID($A$1,SEQUENCE(17),1)*MOD(2^SEQUENCE(17,,17,-1),11)),11)

2023-08-17 06:10:46 · 北京

苏禹嘉

【答案揭晓2-3】解题思路：【法4】依照法3的思路，将余数转化为数组的下标，这样它也可以通过切片的方式获得内容。公式： =RIGHT($A$1)=INDEX(MID("10X98765432",SEQUENCE(11),1),1+MOD(SUM(MID($A$1,SEQUENCE(17),1)*IFERROR(--MID("79X584216379X5842",SEQUENCE(17),1),10)),11)) 【法5】利用了【答案揭晓1-1】的思路。直接上公式： =RIGHT($A$1)=MID("10X98765432",MOD(SUMPRODUCT(MID($A$1,ROW($1:$17),1)*2^(18-ROW($1:$17))),11)+1,1) 法5提示两点： 1.不建议使用ROW()函数，因为它和行数有连锁关系，删改行列时容易报错，不利于日常的应用，可以用Sequence代替row。 2.有的小伙伴想用SUM代替SUMPRODUCT，但一定要注意CSE三键结尾，因为SUMPRODUCT本身是数组函数，不需要CSE强调，但SUM必须要CSE。

2023-08-17 06:09:29 · 北京

苏禹嘉

如果是Sequence搭配sum函数的话，不需要CSE，因为Sequence本身也是数组函数。

2023-08-17 07:07:33 · 北京

苏禹嘉

【答案揭晓2-2】解题思路：【法2】使用文本切片功能进行运算。可以将辅助单元格的内容使用F9进行转化，转化为内存数组后正常引用。公式： =RIGHT($A$1)=VLOOKUP(MOD(SUM(MID($A$1,SEQUENCE(17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),{0,"1";1,"0";2,"X";3,"9";4,"8";5,"7";6,"6";7,"5";8,"4";9,"3";10,"2"},2,FALSE) 【法3】将权重放在一个文本里，通过切片的方式取出对应位的权重，可以有效简化公式，出错时也容易修改。公式仍然有点长： =RIGHT($A$1)=VLOOKUP(MOD(SUM(MID($A$1,SEQUENCE(17),1)*IFERROR(--MID("79X584216379X5842",SEQUENCE(17),1),10)),11),{0,"1";1,"0";2,"X";3,"9";4,"8";5,"7";6,"6";7,"5";8,"4";9,"3";10,"2"},2,FALSE)

2023-08-17 06:00:52 · 北京

苏禹嘉

【答案揭晓2-1】解题思路：篇幅有限，分开发。以下给出6种解法，也可以根据使用习惯交叉使用。【法1】首先最容易让大家想到的就是“匹配”操作，没有学过文本切片的同学，加权和写个又臭又长的公式也不是不行。公式： =RIGHT($A$1)=VLOOKUP(MOD(MID($A$1,1,1)*7+MID($A$1,2,1)*9+MID($A$1,3,1)*10+MID($A$1,4,1)*5+MID($A$1,5,1)*8+MID($A$1,6,1)*4+MID($A$1,7,1)*2+MID($A$1,8,1)*1+MID($A$1,9,1)*6+MID($A$1,10,1)*3+MID($A$1,11,1)*7+MID($A$1,12,1)*9+MID($A$1,13,1)*10+MID($A$1,14,1)*5+MID($A$1,15,1)*8+MID($A$1,16,1)*4+MID($A$1,17,1)*2,11),{0,"1";1,"0";2,"X";3,"9";4,"8";5,"7";6,"6";7,"5";8,"4";9,"3";10,"2"},2,FALSE)

2023-08-17 05:55:19 · 北京

苏禹嘉

【答案揭晓1】特征解读：这里牵扯到的两个参考列表，一个是加权和的权重，一个是余数的对应位。特征解读1：注意到权重均为1-10的正整数，并且以2、4、8收尾，猜测此序列与2^n有关，经验证其逆序序列为2^n除以11的余数。如下图1所示。特征解读2：将X视为10，可见余数+对应位=1或12，仍可从余数角度获取对应位，经验证此处公式合理。如下图2所示。因此，最简洁的公式一定是从这两个特征上面进行简化。