玩转电子表格 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。
