快好知 kuaihz

身份证号对不对,八个函数来联袂,一个公式...

玩转电子表格 2020-03-31 09:33:05

身份证号码中存储了一个人的重要信息,学习与工作中我们经常要建立各种各样的信息表,一般要输入人员的身份证号码,由于身份证号码长达18位,很有可能输入错误,而这信息是不能有半点差错的,轻则会给当事人造成麻烦,重则可能会造成重大损失。在Excel中能不能利用函数与公式,还快速判断身份证号码输入是否正确呢?经过探索我写出了这个公式,与大家分享,大家可以将公式复制到Excel中亲自动手操作,公式可能看起来很复杂,但并不难。

一、校验码生成机制

身份号码由17位数字本体码和1位校验码组成,校验码是为了防止身份证号码填写错误而设计的。

身份证号码与加权因子

校验码生成公式

二、检查身份证号码是否正确

1、原理:检查校验码是否正确

2、公式:

=IF(EXACT(MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW($A$1:$A$17),1)*1,MOD(POWER(2,18-ROW($A$1:$A$17)),11)),11)+1,1),RIGHT(A2)),"正确","错误")

注:单元格A2存放身份证号码;

观看视频:https://m.toutiaoimg.com/group/6809949704357413388/?app=news_article×tamp=1585616384&group_id=6809949704357413388&wxshare_count=1&tt_from=weixin_moments&utm_source=weixin_moments&utm_medium=toutiao_android&utm_campaign=client_share

Excel演示图

⑴公式第一层是IF函数,用于条件判断,语法结构为IF(逻辑判断式, 真时返回值, 假时返回值) :"EXACT(MID("10X98765432", MOD(SUMPRODUCT (MID(A2,ROW ($A$1:$A$17),1)*1,MOD(POWER(2,18-ROW($A$1:$A$17)),11)),11)+1,1),RIGHT(A2))"整体作为IF函数的逻辑判断式,如果该判断式返回True,函数返回第二个参数"正确";如果该判断式返回False,函数返回第二个参数"错误"。

⑵公式第二层是EXACT函数,比较两个字符串,若完全相同,则返回 TRUE;否则返回 FALSE。语法结构为EXACT(文本1, 文本2): "MID ("10X98765432", MOD(SUMPRODUCT (MID(A2,ROW($A$1:$A$17),1)*1, MOD (POWER (2,18-ROW ($A$1:$A$17)), 11)),11)+1,1)"是参数文本1,文本2参数为"RIGHT(A2)",若省略RIGHT函数最后一个参数则值为1,即取得存放在A2单元格中的身份证号码最右边的一个字符。

⑶公式第三层是MID函数,返回文本字符串中从指定位置开始的特定数目的字符。语法结构为MID(文本字符串,提取字符的开始位置,字符的个数):第一个参数"文本字符串"为"10X98765432",第二个参数"提取字符的开始位置"为"MOD(SUMPRODUCT(MID(A2, ROW($A$1:$A$17),1)*1,MOD(POWER(2,18-ROW($A$1:$A$17)),11)),11)+1",第三个参数"字符的个数"为1。

⑷公式第四层是MOD函数,返回两数相除的余数。语法结构为MOD(被除数,除数):第一个参数"被除数"为" SUMPRODUCT(MID(A2,ROW($A$1:$A$17),1)*1, MOD(POWER(2,18-ROW($A$1:$A$17)),11))",第二个参数"除数"为"11"。

⑸公式第五层是SUMPRODUCT函数,在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。语法结构为SUMPRODUCT(数组1, [数组2], [数组3], ...):第一个参数"数组1"为" MID(A2,ROW($A$1:$A$17),1)*1",第二个参数"数组2"为"MOD(POWER(2,18-ROW($A$1:$A$17)),11)",此参数就是加权因子,2的i-1次幂除以11的余数。

⑹公式第六层是ROW函数,返回引用的行号。语法结构为ROW([单元格的引用]),如果省略参数,则是对函数 ROW()所在单元格的引用。如果引用一个单元格区域,将以垂直数组的形式返回的行号。ROW($A$1:$A$17)返回从1到17数字,若选中D2:D18单元格区域,输入"=ROW($A$1:$A$17)",按Ctrl+Shift+Enter键,将在D2:D18中得到从1至17的数字。类似的,E2:E18单元格区域,输入"=MID(A2,ROW($A$1:$A$17),1)",按Ctrl+Shift+Enter键,将在E2:E18中得到身份证号码中按从左到右顺序的1至17的文本,后面加上"*1"表示乘以1,可以将文本型数据转换为数字型数据,这样才能进行数学运算。

⑺公式第七层是POWER函数,返回给定数字的乘幂,语法结构为POWER(底数, 指数)。SUMPRODUCT函数的第二个参数"数组2"为"MOD(POWER(2,18-ROW ($A$1:$A$17)),11)"。即"POWER(2,18-ROW ($A$1:$A$17))"是作为MOD函数的第一个参数"被除数",即2的"18-ROW ($A$1:$A$17)"次幂,根据上面分析可知其值分别是从17递减到1。

本站资源来自互联网,仅供学习,如有侵权,请通知删除,敬请谅解!
搜索建议:联袂  联袂词条  公式  公式词条  函数  函数词条  证号  证号词条  不对  不对词条  
办公

 word字稿模板:如何制作书法字...

小时候,父母为了让我们写好一手漂亮的字,不惜花钱买许多字帖临帖练习;为了让我们作文字迹工整规范,会买专用的绿色方格作文稿纸进行写作。甚至,老师们在制作语文试卷的...(展开)

办公

 学会这些常用的Excel公式,你...

在Excel软件中掌握各种公式的用法可以让你的工作效率大大提升。一个公式几秒钟处理的文档可能需要你花十几分钟甚至几个小时的时间去处理,所以说掌握Excel公式的...(展开)