通过excel公共函数计算二代公民身份证号码末位校验码
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公共函数计算二代公民身份证号码末位校验码/验证码（附公式可直接使用）闲来无事，用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字母数字校验码的计算方法。
一、什么是校验码？校验码是一种用于验证数据准确性的编码方式。
在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帮你轻松获取身份证号码隐藏的信息，轻松统计人员基本信息在实际工作中，我们可能会对一些人员的基本信息进行统计，往往我们可能会一项项的输入，其实呢，在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)三、如何得到出生年月日信息。
Excel校验身份证号码的自定义函数
校验居民身份证号码函数 在日常工作中，我们经常用电子表格输入身份证号码，往往出现输入错误，校对麻烦。
由于身份证中第十八位数字的计算方法较为繁琐，下面提供了一个自定义函数，很方便。
=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电子表格审计身份证号位数合规性的
使用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中进行验证身份证号数据有效性的操作技巧。
Excel中进行验证身份证号数据有效性的操作步骤
1、先建立下面的表格，要设置蓝色框区域的单元格的数据有效性，使得输入的身份证号码不出错。
2、选中G3-G11的单元格。
3、如下图，1处单击“数据”，2处单击“数据有效性”，出现下列对话框。
4、现在可以设置：允许的地方先“文本长度”;数据选“等于”;长度选“18”，因为身份证号码是18位。
5、点下图1处“出错警告”;就有下列选项，在2处选“停止”;3处输入出错提示的标题”输入错误“;4处输入错误提示信息”亲，身份证数字位数不对。
6=现在我试一下，在蓝色框处填上10位，按回车就有如下提示，成功了。
7、如果是设性别可以参考下图。
8、如果是设出生年月日可以参考下图。
9、如果是设身高可以参翻考下图。
注意事项：
第七步，输入男女中间的逗号是英文状态下。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
通过excel公共函数计算二代公民身份证号码末位校验码/验证码（附公式可直接使用）
闲来无事，用EXCEL写了个公式（本文最后），可以根据身份证号码前17位计算第18位校验码的，经过验证，非常有效。
使用这个公式可以自己随意编造可以通过验证的身份证号码（感觉在为办证的做贡献。
）
18位身份证号码的结构：
现在的二代身份证由18位数字组成
1、前6位是行政区规划代码，第一位是区位号（如西北、西南），第二位为省份编号，第三、四位是城市编号，第五、六位是县区编号，01-19代表市辖区，20以后代表郊县、县级市等辖区。
具体的行政区域规划码可以可以在国家统计局网站查询：
/tjbz/xzqhdm/t20021125_46781.htm
2、第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位！")。