通过excel公共函数计算二代公民身份证号码末位校验码

Excel能够验证身份证号码对错的公式来了，还不快点收藏！当我们在Excel中输入大量身份证号码的时候，为了防止输入错误，就要与身份证上面的数字进行反复核实，很耗时不说，难免还是会有看花眼输错了的情况发生。

今天就跟大家分享一个可以判断身份证号码对错的公式，提高一些工作效率，减少失误。

从上图的编辑栏中可以看到公式（默认的全部都是二代18位的身份证号码）=IF(B2='','',IF(MID('10X98765432',MOD(SUMPRODUCT(MID (B2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1,1)= RIGHT(B2),'正确','错误'))那么这个公式表达的是什么意思呢？其实呀，我们身份证号码的编制，是遵循了一定规则的，并不是按顺序随便取的。

从倒数第二位可以判断性别一样，这就是其中一个编制规则。

只是今天我们要说的这个规则，不像判断性别那样普及，不是每个人都知道的。

二代身份证是由18位数字组成的，它的编制有一个规律：将前17位数字进行一种特定的运算，这个运算的结果必定等于第18位数字。

那到底是什么样的特定运算呢？1．身份证号码有一组固定的17位系数，分别对应身份证号码的前17位数字。

这组固定的系数是：7、9、10、5、8、4、2、1、6、3、7、9、10、5、8、4、2。

（别问我是怎么知道的，我网上查的。

也别问为什么偏偏是这17位，而不是其他的，因为就是这样定下的，是固定的，详情网上可以查到。

）2．将该系数分别与身份证号码的前17位数字相乘，再把17个相乘的结果相加。

3．用相加的结果除以11，看余数是多少，再给它加上1，余数+1。

4．身份证号码还有一组固定的11位校验码：1、0、X、9、8、7、6、5、4、3、2。

（与上面所说的系数一样，也是固定的。

通过excel公共函数计算二代公民身份证号码末位校验码/验证码（附公式可直接使用）闲来无事，用EXCEL写了个公式（本文最后），可以根据身份证号码前17位计算第18位校验码的，经过验证，非常有效。

使用这个公式可以自己随意编造可以通过验证的身份证号码（感觉在为办证的做贡献。

）18位身份证号码的结构：现在的二代身份证由18位数字组成1、前6位是行政区规划代码，第一位是区位号（如西北、西南），第二位为省份编号，第三、四位是城市编号，第五、六位是县区编号，01-19代表市辖区，20以后代表郊县、县级市等辖区。

具体的行政区域规划码可以可以在国家统计局网站查询：/tjbz/xzqhdm/t20021125_46781.htm2、第7-14位是出生年月日，格式是yyyymmdd；3、第15-17位是顺序码，即同一地区相同生日的人的顺序编码，随便写；4、第18位是根据GB 11643-1999和IOS 7064计算出的校验码，计算方式比较复杂，文章中的公式就是用来计算这个校验码的！下图是完整的身份证效验码计算方式：但是因为上图中的公式太多，不方便直接使用，所以我把上图中所有的公式整合到了一个单元格中，就是下面的超级公式~！现在，打开excel，先将A1单元格属性设置为文本，再把你准备好的1-17位身份证号码填入A1单元格，然后把公式拷到随便哪个单元格中，完整的身份证号码就计算出来了！还可以用下拉的方式同时计算多个号码，很省事吧！公式如下：=IF((LEN(A1))=17,(A1&IF(VALUE(MOD((SUM((MID(A1,1,1))*7,(MID(A1,2,1)) *9,(MID(A1,3,1))*10,(MID(A1,4,1))*5,(MID(A1,5,1))*8,(MID(A1,6,1))*4, (MID(A1,7,1))*2,(MID(A1,8,1))*1,(MID(A1,9,1))*6,(MID(A1,10,1))*3,(MI D(A1,11,1))*7,(MID(A1,12,1))*9,(MID(A1,13,1))*10,(MID(A1,14,1))*5,(M ID(A1,15,1))*8,(MID(A1,16,1))*4,(MID(A1,17,1))*2)),11))=0,1,(IF(VALU E(MOD((SUM((MID(A1,1,1))*7,(MID(A1,2,1))*9,(MID(A1,3,1))*10,(MID(A1, 4,1))*5,(MID(A1,5,1))*8,(MID(A1,6,1))*4,(MID(A1,7,1))*2,(MID(A1,8,1) )*1,(MID(A1,9,1))*6,(MID(A1,10,1))*3,(MID(A1,11,1))*7,(MID(A1,12,1)) *9,(MID(A1,13,1))*10,(MID(A1,14,1))*5,(MID(A1,15,1))*8,(MID(A1,16,1) )*4,(MID(A1,17,1))*2)),11))=1,0,(IF(VALUE(MOD((SUM((MID(A1,1,1))*7,( MID(A1,2,1))*9,(MID(A1,3,1))*10,(MID(A1,4,1))*5,(MID(A1,5,1))*8,(MID (A1,6,1))*4,(MID(A1,7,1))*2,(MID(A1,8,1))*1,(MID(A1,9,1))*6,(MID(A1, 10,1))*3,(MID(A1,11,1))*7,(MID(A1,12,1))*9,(MID(A1,13,1))*10,(MID(A1 ,14,1))*5,(MID(A1,15,1))*8,(MID(A1,16,1))*4,(MID(A1,17,1))*2)),11))= 2,"X",12-VALUE(MOD((SUM((MID(A1,1,1))*7,(MID(A1,2,1))*9,(MID(A1,3,1) )*10,(MID(A1,4,1))*5,(MID(A1,5,1))*8,(MID(A1,6,1))*4,(MID(A1,7,1))*2 ,(MID(A1,8,1))*1,(MID(A1,9,1))*6,(MID(A1,10,1))*3,(MID(A1,11,1))*7,( MID(A1,12,1))*9,(MID(A1,13,1))*10,(MID(A1,14,1))*5,(MID(A1,15,1))*8, (MID(A1,16,1))*4,(MID(A1,17,1))*2)),11)))))))),"输入长度不等于17位！")。

excel 字母数字的校验码计算方法Excel是一款功能强大的电子表格软件，广泛应用于数据处理、计算、统计和分析等领域。

在Excel中，字母和数字的校验码是一种常见的计算方法，用于验证数据的准确性和完整性。

本文将介绍Excel字母数字校验码的计算方法。

一、什么是校验码？校验码是一种用于验证数据准确性的编码方式。

在Excel中，字母数字的校验码是通过对字母和数字进行特定运算得到的一个校验值。

通过对数据进行校验码的计算，可以判断数据是否被篡改或输入错误。

二、校验码的计算方法在Excel中，字母数字的校验码的计算方法主要有两种：奇偶校验和凯奇校验。

下面分别介绍这两种计算方法。

1. 奇偶校验奇偶校验是一种简单的校验码计算方法，适用于字母和数字的校验。

具体计算步骤如下：（1）将待校验的字母和数字转换为ASCII码。

（2）将ASCII码相加，得到校验和。

（3）判断校验和的奇偶性，如果校验和为奇数，则校验码为奇校验（校验码为1），如果校验和为偶数，则校验码为偶校验（校验码为0）。

例如，对于字母'A'的校验，其ASCII码为65，校验和为65，为奇数，所以校验码为奇校验（校验码为1）。

2. 凯奇校验凯奇校验是一种更加复杂的校验码计算方法，适用于字母和数字的校验。

具体计算步骤如下：（1）将待校验的字母和数字转换为ASCII码。

（2）将ASCII码相加，得到校验和。

（3）将校验和转换为二进制码。

（4）判断校验和的二进制码中1的个数，如果个数为奇数，则校验码为奇校验（校验码为1），如果个数为偶数，则校验码为偶校验（校验码为0）。

例如，对于字母'B'的校验，其ASCII码为66，校验和为66，转换为二进制码为'1000010'，其中1的个数为2，为偶数，所以校验码为偶校验（校验码为0）。

三、校验码的应用场景字母数字的校验码在Excel中有着广泛的应用场景。

下面介绍几个常见的应用场景。

Excel帮你轻松获取身份证号码隐藏的信息，轻松统计人员基本信息在实际工作中，我们可能会对一些人员的基本信息进行统计，往往我们可能会一项项的输入，其实呢，在Excel中如果我们知道了身份证号，可以利用简单的公式统计出一些具体的个人信息，你比如省份，具体所在地区，出生日期，年龄，性别等。

首先呢，我们先了解一下我国身份证号的编号规律：1-2位代码是指省、自治区、直辖市代码3-4位代码是指地级市、盟、自治州代码5-6位代码是指县、县级市、区代码7-14位代码是指出生年月日15-17位为顺序号，其中第17位如果是奇数指男性，偶数指女性最后一位就是校验码知道这些之后，接下来我们看一下怎样用Excel得到相关信息一、如何得到省份信息。

首先我们需要准备好一份地区代码对照表，这个可以从网上下载，或者是私信我，我发给你们电子版。

将对照表复制到Excel表格中，添加上身份证信息。

然后在省份一栏输入公式。

=VLOOKUP(LEFT(C2,2),A:B,2)解释一下公式：首先LEFT函数是用来对单元格里的内容进行截取选择，从左边第一个字符开始截取，截取指定的长度。

这里的LEFT(C2,2) 就是从C2单元格里面的数值，从左往右截取两位。

VLOOKUP函数就是竖直查找，即列查找。

在指定竖直列区域内查找，找到指定的内容，返回指定列的数据。

这里VLOOKUP(LEFT(C2,2),A:B,2) 就是在A、B列（也就是我们提前准备好的地区代码对照表）查找省份证信息的前两位返回的值是第二列对应的值(这里的第二列就是B列了，不过要注意在使用此函数时，返回第n列的数值不一定对应相应的字母列，这需要我们从选定的区域第一列开始数一下）填完了第一个单元格，鼠标移动到单元格右下角，变成加好时双击或者往下拉就行了。

二、如何得到具体所在地区信息。

公式基本和上面一样，就是利用LEFT函数截取位数时，这次截取前六位的长度。

=VLOOKUP(LEFT(C2,6),A:B,2)三、如何得到出生年月日信息。

校验居民身份证号码函数 在日常工作中，我们经常用电子表格输入身份证号码，往往出现输入错误，校对麻烦。

由于身份证中第十八位数字的计算方法较为繁琐，下面提供了一个自定义函数，很方便。

=jysfz(身份证号码 字符型)示例：340524************←号码#NAME?=jysfz(A7)#NAME?=jysfz("340524************") 340524************#NAME? 340524************#NAME?340524************#NAME?340524************#NAME? 340524************#NAME? 340524************#NAME? 3405241952060122188#NAME?34052419710702233#NAME? 3405241964100622#NAME? 340524************#NAME? 34052419641006225想#NAME?#NAME?这样也有错！#NAME?　身份证的号码是按照国家的标准编制的，由18位组成：前六位为行政区划代码，第七至第十四位为出生日期码，第15至17位为顺序码，第15、16位为乡镇代码（一个乡镇可能有多个代码），第17位代表性别（奇数为男，偶数为女），第18位为校验码。

作为尾号的校验码，是由号码编制单位按统一的公式计算出来的，如果某人的尾号是0-9，都不会出现X，但如果尾号是10，那么就得用X来代替，因为如果用10做尾号，那么此人的身份证就变成了19位，而19位的号码违反了国家标准，并且我国的计算机应用系统也不承认19位的身份证号码。

Ⅹ是罗马数字的10，用Ⅹ来代替10，可以保证公民的身份证符合国家标准。

但是我国的居民身份证在升位后，一些人的尾号变成了X，这部分人在工作生活中，例如去银行存取钱、去汽车公司租赁汽车或者报名参加考试等等过程中，往往不被检验者理解，认为是假身份证，这样的误会给很多人的生活带来不便。

使用EXCEL电子表格审计身份证号位数合规性的审计方法首先对电子表格中的数据进行排序或分类（主要目的是让身份证位数为15位的或18位放在一起），然后在所要分析的位数为15位的身份证后一列中插入公式：=IF(Logical,Value_if_true,Value_if_false)，如：=IF(LEN(L2)=15, "符合要求","不符合要求")；在位数为18位的身份证号后插入公式：=IF(LEN(L2)=18,"符合要求", "不符合要求")，在插入公式时，特别注意的是单元格格式。

然后将显示不符合要求的全部列出分析。

2.利用身份证号构造年龄字段与实际年龄核对的审计方法同样在分析前，将身份证号分成15位和18位两类。

（1）身份证号为15位在所要分析的15位身份证号后一列插入公式：=109-MID(身份证所在列坐标,起始位数,所要取出的位数)，如=109-MI D(L2,7,2)，意为用109减去从L列第2行的身份证号码中从第7位开始，取出2位数（身份证所有者出生年份）。

如身份证号码为320***550126580的公民，从第7位开始取出2位为55，用109（审计时为2009年）减55为54，即当时该身份证所有者年龄为54岁。

（2）身份证号为18位在所要分析的18位身份证号后一列插入公式：=109-MID(身份证所在列坐标,起始位数,所要取出的位数)，如=109-MI D(k2,9,2)，意为用109减去从k列第2行的身份证号码中从第9位开始，取出2位数（身份证所有者出生年份）。

如身份证号码为320***194702285 830的公民，从第9位开始取出2位为47，用109（审计时为2009年）减4 7为62，即当时该身份证所有者年龄为62岁。

3.利用身份证号识别性别审计方法（1）身份证号为15位在所要分析的身份证号后一列插入公式：=IF(MOD(MID(列坐标,15,1),2)=0,"女","男")，意为如果15位身份证号的最后一位被2整除的话，该身份证所有者为女性，反之为男性。

Excel中进行验证身份证号数据有效性的操作技巧
设置单元格的数据有效性，使得输入的身份证号码不出错。

具体该怎么去进行操作的呢?今天，店铺就教大家在Excel中进行验证身份证号数据有效性的操作技巧。

Excel中进行验证身份证号数据有效性的操作步骤
1、先建立下面的表格，要设置蓝色框区域的单元格的数据有效性，使得输入的身份证号码不出错。

2、选中G3-G11的单元格。

3、如下图，1处单击“数据”，2处单击“数据有效性”，出现下列对话框。

4、现在可以设置：允许的地方先“文本长度”;数据选“等于”;长度选“18”，因为身份证号码是18位。

5、点下图1处“出错警告”;就有下列选项，在2处选“停止”;3处输入出错提示的标题”输入错误“;4处输入错误提示信息”亲，身份证数字位数不对。

6=现在我试一下，在蓝色框处填上10位，按回车就有如下提示，成功了。

7、如果是设性别可以参考下图。

8、如果是设出生年月日可以参考下图。

9、如果是设身高可以参翻考下图。

注意事项：
第七步，输入男女中间的逗号是英文状态下。

Excel中检测输⼊的⾝份证号码是否合法函数校验⽅法⼤揭秘什么时候需要校验⾝份证号码合法性呢？1.防⽌输⼊⼈员随⼿输⼊⼀个18位数字冒充⾝份证号码2.防⽌输⼊⼿误,有时候并⾮故意输错注意本⽅法⽆法检测⾝份证是否真实存在,只能从格式上检查号码是否合法⾝份证号码校验基本原理第1步：把号码的前17位分别乘以不同的系数,系数表如下第2步：把17位数字和系数相乘的结果相加，加和值再除以11，取其余数。

⽤得到的余数与⾝份证第18位号码进⾏对照,余数与⾝份证号码第18位对应关系如下表例如：某男⼠的⾝份证号码是622924************。

⾸先我们得出前17位与系数的乘积和是320，除以11的余数是1，对应第18位数是0。

所以这是⼀个不合法的⾝份证号码。

判断⾝份证号码是否合法的函数公式计算第18位数=IF(LEN($C2)=18,MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1),"长度错误")判断是否合法=IF(LEN($C2)=18,IF(MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)=RIGHT($C2,1),"合法","不合法"),"长度错误")其中，C2为⾝份证号码，$B$2:$B$18为第1到17个系数。

这个⼀般存到另⼀个sheet⾥或者顶⼀个名称,与实际使⽤的表分开函数分步讲解VALUE(MID($C2,ROW($1:$17),1))ROW函数返回⼀个1到17的数组，配个mid函数依次取出C2号码中前17个数字，并将截取的⽂本⽤value转换为数值SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)⽤SUMPRODUCT函数返回MID($C2,ROW($1:$17),1)数组和$B$2:$B$18(校验表)区域对应相乘的和MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)⽤mod对上⼀步求乘积之取11的余数MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)⽤MID函数截取10X98765432中的1位数，即⾝份证号码的第18位。

如何利用Excel检查输入的身份证号码对错？
根据身份证校验位的计算规则，对校验位进行计算判断。

身份证的校验规则说起来比较繁琐，这里就不介绍了，按照下述方法做个简单的设定和公式，只需2步就可完成，快来试试吧。

1、打开excel后，先在一个工资表中输入下列参数数据（不可输错哦），并将这17个数据定义为“系数”（定义名称的操作方法：选中这17个数据，在名称栏，即下图红圈处输入“系数”，回车确定）。

.
2、在另一个工作表中进行身份证号码合法性校验，假设在A2单元格输入身份证号码，在B2单元格输入公式：=IF(LEN($A2)=18,IF(MID("10X98765432",MOD(SUMPRODUCT(V ALUE(MID($A2,ROW($1:$17),1)),系数),11)+1,1)=RIGHT($A2,1),"正确","校验位错"),"长度错误")
向下复制公式，既可对A列输入的身份证号码进行长度和校验位的判断验证。

经过测试，妥妥的。