表格里如何检查身份证号是否正确（检查身份证号码是否正确的函数）

身份证号码的验证在网络上有许多公式，往往比较复杂，今天给大家介绍我经常用到的一个验证公式，非常简单，希望对大家有所帮助，也希望有网友有更为简洁的方法，欢迎留言探讨。

在日常excel使用中，最常用，也是最头痛的就是身份证号码的操作，我国身份证采用18位的数字来标识身份信息，容易被excel当做长数字处理，直接用科学记数法显示出来，造成数据丢失。另外，身份证号码中隐藏了许多有用的信息，有户籍信息，出生日期信息，年龄信息，性别信息，校验信息等，我们可以通过公示轻松提取出来。

另外，由于身份证号码比较长，在录入过程中容易出现错误，对号码进行正确性验证就非常有必要。下面我讲一个我经常用的一个校验公式。

一、有必要先简单说一下校验码的计算步骤。

(1)十七位数字本体码加权求和公式

S = Sum(Ai * Wi), i = 0, ... , 16 ，先对前17位数字的权求和
   　　Ai:表示第i位置上的身份证号码数字值(0~9)
   　　Wi:7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 （表示第i位置上的加权因子）

(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

公式如下：

=IF(MID("10X98765432",MOD(SUMPRODUCT((--MID(A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)),{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11) 1,1)=MID(A1,18,18),"正确","错误")

注：公式中红色“A1”表示身份证号码所在的单元格。

解释：这个公式用到if、 mid、mod、sumproduct四个函数，--操作符。

（1）--MID(A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1))这个函数表示把A1单元格中的身份证号码前17位信息转换成内存数组，例如身份证号码为：412901197708151011，转换后为{4；1；2；9；0；1；1；9；7；7；0；8；1；5；1；0；1}，便于下一步计算。操作符“--”表示把字符转换成数值。

（2）SUMPRODUCT函数表示把两个数组进行乘积和。

（3）mod函数表示取模，就是求余数。将计算出来的数字对11求余数，出来就是校验码，也就是身份证号码的最后一位。

赞 (0)

相关推荐

  • EXCEL中怎么输入身份证号 变成E+17怎么办

    在Excel表格中输入身份证号,但是输入完成后却不显示为身份证身份证号,而是现实为一个科学计数--"E+17" 第一种方法 01 在单元格中输入身份证号,这时单元格中并没有显示身份 ...

  • 电脑WPS软件中如何解决身份证号变成乱码数字的问题

    电脑WPS软件中如何解决身份证号变成乱码数字的问题

    有的小伙伴在电脑WPS软件的Excel表格中,输入18位的身份证号,但是发现身份证号码变成了E+17并且后面三位变成000的格式,想要解决这一问题,但是却不知道如何解决,那么小编就来为大家介绍一下吧. ...

  • Excel表格怎样用公式提取身份证号里的出生年月日?

    Excel表格怎样用公式提取身份证号里的出生年月日?下面和小编一起来看看吧. 具体方法: MID函数的组成,如下图所示: MID函数各个参数说明,如下图所示: 如下图所示,要求出下列身份证的出生年月日 ...

  • excel表格怎么快速的提取身份证号里的出生日期

    在行政部门的工作的人,经常会碰到输入工作人员的身份证号.出生日期等基本个人信息,但是一个一个的输入太麻烦,下面我就教大家怎么从身份证号中提取出生日期. 操作方法 01 打开excel表格,如下表是要进 ...

  • Excel表格里怎么输入18位的身份证号和超长数字

    本例教大家如何在Excel表格中输入18位的身份证号和超长数字,不会出现科学计数法和后面几位变成0的情况. 方法说明: 01 举例来说,要在表格中输入:130612198902014220的身份证号, ...

  • 如何在Excel表格中正确输入身份证号码

    在很多时候我们在Excel中制表的过程中会发现，往往有一些数据输入到单元格里面的时候就变了，比如：Excel输入01，在单元格中只会显示一个“1”了，前面的“0”莫名其妙的不见了，这个问题Word联盟 ...

  • 怎么解决Excel表格里输入身份证后几位变成0的问题

    怎么解决Excel表格里输入身份证后几位变成0的问题

    在使用Excel表格输入身份证号码时,我们可能遇到这样的情况:当输入后几位的身份证号时,全部显示成了0,怎么办呢?小编为大家解答.具体如下:1. 首先,请大家将excel表格打开,然后在其中输入一串身 ...

  • 怎么解决身份证输入excel表格里显示不完？

    我是成都路凡教育的吴老师,在教学中,很多学员在没有参加培训之前都不会正确的将身份证号完整的显示出来,相必很多人也会有这方面的疑惑吧,所以呢今天就给大家分享一下咯. 操作方法 01 首先大家要明白的是我 ...

  • Excel单元格中正确显示身份证号的技巧

    对于超过12位的数字(包括12位)，Excel会自动以科学记数法显示，看起来不舒服。当我们要把带有身份证号的Word表格复制到Excel中去，就会遇到这种情况。那么，应该怎样保持身份证号的原貌呢? 一 ...

  • 在Excel表格中如何显示完整身份证号呢?

    在默认情况下Excel输入身份证号是显示不完整的,会显示一堆乱码字符串,这到底是怎么回事呢,其实可以在输入数值前,在前面加一个撇号,或者把单元格设置为文本格式.那么在Excel表格中如何显示完整身份证 ...