Excel函数提示: 使用Excel函数公式检查ID号

编辑说: 编辑是第一个了解ID编码规则的人. 原来并不复杂. 根据此编码规则，我们还可以区分的真假，这真是太神奇了！但是，实际上，如果公司员工的ID号错误，并且我们没有找到它，这确实会对公司造成很多麻烦，因此今天我们将与您分享ID号的验证信息. 公式.

通常，ID号有两种错误，长度错误和内容错误.

大多数错误在输入过程中发生. 对于长度错误，当前为18位数字. 如果长度不正确，则更容易找出，但是如果其中一个数字不正确，则很难找到，将来可能会产生一些严重的后果. 因此，今天的重点是检查内容的正确性.

首先，的组成原理

首先，您需要对ID号的编码规则有所了解:

由18位ID组成的ID号: ddddddyyyymmddxxsp总共有18位数字，其中: 区号1-6是，生日是7-14，15-17的三位是性别代码，最后一位基于前17位.

校验位的计算规则比较复杂:

（1）前十七位数字身体代码的加权和公式

S =总和（Ai * Wi），i = 0，...，16，首先将前17位数字的权重相加

Ai: 表示第i个位置的ID号的数字值

Wi: 代表第i个位置的加权因子

Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

（2）计算模式

Y = mod（S，11）

（3）通过模块获取相应的校验码

Y: 0 1 2 3 4 5 6 7 8 9 10

校验码: 1 0 X 9 8 7 6 5 4 3 2

要检查ID卡号是否正确，原则是根据此规则计算验证码，然后将其与最后一位数字进行比较. 如果不一致，则有问题. 很难理解这个规则，让我们来看一个例子:

1. 将前一个ID号的17位数字乘以不同的系数. 从第一名到第十七名的系数为:

7-9-10-5-5-8-4-2-4-1－6－3－7－9－10－5－8－4－2.

2. 将这17位数字与系数相乘的结果相加.

3. 使用加法和除法11. 余数是什么？

4. 其余的最多只能有11个数字－1-2－3-4－5－6－7－8－9－10. 与之对应的最后一个ID卡的编号为1-X-9-9-8-7-6-5-5-3-4-2.

5. 根据验证码的比较，如果余数为3，则第9位将出现在ID卡的第18位上. 如果相应的数字为10excel验证公式，则ID卡的最后一个数字为罗马数字2.

例如: 某人的号为[53010219200508011x]，让我们看看该是否为合法.

首先，我们获得前17位的乘积之和

【（5 * 7）+（3 * 9）+（0 * 10）+（1 * 5）+（0 * 8）+（2 * 4）+（1 * 2）+（9 * 1） ）+（2 * 6）+（0 * 3）+（0 * 7）+（5 * 9）+（0 * 10）+（8 * 5）+（0 * 8）+（1 * 4）+ （1 * 2）]

是189，然后将189除以11得到189/11 = 17 ---- 2，这意味着剩余数为2. 最后，通过相应的规则，您可以知道对应于余数2是X，因此您可以确定这是正确的ID号.

第二，Excel检查真假ID号

原理很明确. 现在的问题是如何用Excel公式表示此计算过程. 在这里excel验证公式，我将与大家分享公式:

= IF（右（A2）= MID（“ 10X98765432”，MOD（SUM（MID（A2，ROW（INDIRECT（“ 1:17”））），1）* 2 ^（18-ROW（INDIRECT（“ 1:17“）））），11​​）+1,1），” true“，” false“）

请注意，这是一个数组公式. 输入或粘贴公式后，需要同时按住Ctrl键和Shift键，然后按Enter键.

该公式看起来非常复杂. 使用时，只需将两个A2修改为表中ID编号所对应的位置.

简单说说公式，核心部分是

MID（“ 10X98765432”，MOD（SUM（MID（A2，ROW（INDIRECT（“ 1:17”）），1）* 2 ^（18-ROW（INDIRECT（“ 1:17”））））） ，11）+1,1）

此字符串用于根据校验码规则计算前17位数字，然后将其与RIGHT（A2）比较. RIGHT（A2）的功能是从A2中提取最右边的数字. ，根据比较结果，使用if函数进行判断.

我们可以使用此公式检查文章开头图片中的ID号是否正确:

结果为假.

让我们使用此公式检查您的表单中是否有错误的ID号. 更正确定为错误的数字，以便在萌芽阶段及时解决问题！

＊＊＊＊部落巢穴教育-Excel功能可检查号码********

原文: 旧菜鸟/部落巢穴教育（未经同意，请勿转载）

更多教程: 部落的巢穴教育

本文来自电脑杂谈，转载请注明本文网址：
https://www.pc-fly.com/a/tongxinshuyu/article-190832-1.html