如何用Excel验证身份证号码是否符合规则

合集下载

验证身份证号是否正确的公式、通过身份证号提取出生年月、性别、年龄等的公式汇总

身份证号验证：=IF(LEN(身份证号所在单元格)=0,"空",IF(LEN(身份证号所在单元格)=15,"老号",IF(LEN(身份证号所在单元格)<>18,"位数不对",IF(CHOOSE(MOD(SUM(MID(身份证号所在单元格,1,1)*7+MID(身份证号所在单元格,2,1)*9+MID(身份证号所在单元格,3,1)*10+MID(身份证号所在单元格,4,1)*5+MID(身份证号所在单元格,5,1)*8+MID(身份证号所在单元格,6,1)*4+MID(身份证号所在单元格,7,1)*2+MID(身份证号所在单元格,8,1)*1+MID(身份证号所在单元格,9,1)*6+MID(身份证号所在单元格,10,1)*3+MID(身份证号所在单元格,11,1)*7+MID(身份证号所在单元格,12,1)*9+MID(身份证号所在单元格,13,1)*10+MID(身份证号所在单元格,14,1)*5+MID(身份证号所在单元格,15,1)*8+MID(身份证号所在单元格,16,1)*4+MID(身份证号所在单元格,17,1)*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=IF(ISNUMBER(RIGHT(身份证号所在单元格,1)*1),RIGHT(身份证号所在单元格,1)*1,"X"),"正确","错误"))))性别：=IFERROR(IF(MOD(VALUE(MID(身份证号所在单元格,17,1)),2)=1,"男","女"),"")年龄：=IFERROR(YEAR(NOW())-VALUE(MID(身份证号所在单元格,7,4)),"")出生年月日：=CONCATENATE(MID(身份证号所在单元格,7,4),"-",MID(身份证号所在单元格,11,2),"-",MID(身份证号所在单元格,13,2))是否农村青壮年（女16至45、男16至50）：=IF(户口性质<>"农业户口","否",IF(AND(性别="女",G4>=16,G4<=45),"是",IF(AND(性别="男",G4>=16,G4<=50),"是","否")))照片插入：第一步：新建一寸照文件夹为“目标文件夹”，照片命名例：99人以内（01+姓名），999人以内（001+姓名）第二步：在基础信息表内输入公式：=”照片源地址”&\”&序号栏&姓名栏&”.jpg”输入公式后，把公式内所有的“\”都换为“\\”；序号要求：99人以内从01开始，999人以内从001开始；第三步：到WORD文档，建立表格，并使光标在照片格“插入”→“文档部件”→“域”→“includepicture”→域属性输入“123”→FN+ALT+F9→把先前输入的“123”删除，在邮件合并内找输入合并域，选择“照片栏”→FN+F9。

Excel判断身份证号校验码

Excel判断身份证号校验码假设在一个工作表中：A列是姓名，B列是身份证号码，C列是备注。

在B列中输入18位身份证号码后，在C2单元格中输入计算数组公式：=RIGHT(B2,1)=MID("10X98765432",MOD(SUM(MID(B2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1) 然后再将该公式复制给C列其他单元格。

公式的计算结果是一个逻辑值，True表示校验正确，即计算出来的校验码与输入的相同，而False则表示校验错误。

值得注意的是，使用校验码进行校验判断为正确的身份证号码未必是正确的，但判断为错误的则肯定是错误的，即通过校验的只是身份证号码正确的必要条件而非充分条件。

当然，在实际输入中，身份证号码输入错误而校验正确的情况很少见。

在大多数情况下，使用校验码进行校验可以大大提高身份证号码输入的正确性。

RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字符。

MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。

MOD 返回两数相除的余数。

SUM 返回某一单元格区域中所有数字之和。

ROW 返回引用的行号。

INDIRECT 返回由文本字符串指定的引用。

或者输入数组公式：=IF(LEN(B2)<>18,"错误",IF(MID("10X98765432",MOD(SUM(MID(B2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=MID(B2,18,1), "正确","错误"))。

excel表格输入身份证正确性实时验证

Excel 表格身份证输入实时验证
一、示例使用的是 wps2019版二、主要利用数据有效性进行设计三、身份证验证：
1、长度 2、X大写 3、逻辑验证
一、需要设置数据位置如下图，主要位置在D2单元格
二、选中D2单元格，从菜单上方选择--数据--有效性--下拉-数据有效性
三、弹出对话框
数据有效-选择设置项一、允许下拉框中选择为自定义二、公式输入加入下面数据验证公式注意：本公式只针对D2单元格，如有变动请将下面的所有D2进行查找替换。
• 首先公式计算获得验证结果，判断正确返回1，错误返回0 • 返回值与 1 进行比较结果=1 返回 True或 False • 数据有效性根据返回的False 进行验证，False则弹出警告
三、确定即可，保留忽略空值。可以考虑在出错警告里添加提示
四、设置完成确定后D2单元格就可以自动验证；然后将光标放到D2单元格右下角，变为实心十字后，按住下拉覆盖要输入的单元格即可。
此处会自动累加D2 变为D3。
公式解释：
=IF(LEN(D2)=0,1,IF(LEN(D2)=15,1,IF(LEN(D2)<>18,0,IF(CHOOSE(MOD(SUM(MID(D 2,1,1)*7+MID(D2,2,1)*9+MID(D2,3,1)*10+MID(D2,4,1)*5+MID(D2,5,1)*8+MID(D 2,6,1)*4+MID(D2,7,1)*2+MID(D2,8,1)*1+MID(D2,9,1)*6+MID(D2,10,1)*3+MID(D 2,11,1)*7+MID(D2,12,1)*9+MID(D2,13,1)*10+MID(D2,14,1)*5+MID(D2,15,1)*8+ MID(D2,16,1)*4+MID(D2,17,1)*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=IF(ISNUMBER(RI GHT(D2,1)*1),RIGHT(D2,1)*1,"X"),1,0))))=1

Excel中检测输入的身份证号码是否合法函数校验方法大揭秘

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

⽤得到的余数与⾝份证第18位号码进⾏对照,余数与⾝份证号码第18位对应关系如下表第2步例如例如：某男⼠的⾝份证号码是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校验身份证号码的方法

用Excel校验身份证号码的方法威远县东联镇小学校·罗斌身份证号码中的校验码是身份证号码的最后一位，是根据〖中华人民共和国国家标准GB 11643-1999〗中有关公民身份号码的规定，根据相应的规定计算出来的。

公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。

排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码，最后一位是数字校验码。

最后一位的数字校验码是由前17位唯一确定的，随便乱填的身份证号就不能通过校验。

下面分步详细介绍一下用Excel校验身份证号码的一种方法：1.在D1到T1这17个单元格中依次输入1到17这17个数。

2.在D2中输入公式：=2^(18-D1)。

单击D2拖动鼠标向右填充到T2。

3.在D3中输入公式：=MOD(D2,11)以得到身份证第一位的校验系数。

单击D3拖动鼠标向右填充至T3可以得到前17位中每一位的校验系数。

4.在B5至B14中输入要校验的身份证号码（这里选择的是10个身份证号码），实际选择的身份证号码数可以自定。

5.在D5中输入公式：=MID($B5,D$1,1)*1以提取身份证的第一个数字，乘1的目的是将第一个数字由文本格式转为数字格式，便于后续的计算。

单击D5拖动鼠标向右填充至T5可以得到身份证号的前17位的每个数字。

6.在U5中输入公式：=SUMPRODUCT($D$3:$T$3,D5:T5)计算出前十七位数字和相应的校验系数的乘积之和。

7.在V5中输入公式：=MOD(1-U5,11)以得到该身份证的校验数。

8.在W5中输入公式：=IF(V5=10,"X",V5)以得到该身份证的校验码。

9.在X5中输入公式：=MID(B5,1,17)&W5得到由前17位计算出的正确的身份证号码。

10.在C5 中输入公式：=IF(B5=X5,"校验正确","号码错误！")以得到第一个身份证的校验结果。

Excel判断身份证号码数据的方法介绍

Excel判断身份证号码数据的方法介绍
下面是为大家提供的关于Excel判断身份证号码数据的方法的文章，希望对大家有帮助，
解决方案
判断18位身份证号的第17位或15位身份证号的末位数字奇偶性。

操作方法
在C3单元格输入以下公式并向下复制：
=IF(MOD(RIGHT(LEFT(B3,17),3),2),”男”,”女”)
原理分析
取得身份证号中性别数字
中国公民身份证号码原使用15位数字表示，其中第15位数字为奇数表示男性，升级后的身份证号码以第17位数字的奇偶表示性别。

RIGHT(LEFT(B3,17),3)截取身份证前17位数字的后3位，同时可以满足15、18位身份证号的取数要求。

公式中使用到了MOD函数，下面是MOD函数功能的简要介绍：
mod 就是返回除法中余数，如10处以3余1为：
公式为
=mod(10,3)结果为1
被除数除数也可为小数，返回剩余小数，但由于2进制的转化，数据有所差值，建议最好不要公式中有小数，误差会较大。

MOD函数的限制
在D3单元格输入以下公式将返回#NUM!错误：。

判断员工的身份证号码是否正确

将光标定在该单元格的右下角，这时光标变成“十”字，按住鼠标左键不放往下拉直到员工洪森林，这时在H列中出“TRUE”或“FALSE”英文单词，显示“TRUE”是指身份证号码是正确的，如果是“FALSE”，则是错误的。姓名性别学历大专大专本科本科大专中专中专大专大专本科本科高中高中本科高中高中参加工作时间所属部门联系电话 2011-2-3 2005-1-12 2010-10-3 2003-5-6 2005-10-3 2008-2-4 2001-3-10 2005-1-12 2001-10-4 2011-5-10 2006-3-11 2010-2-12 2007-5-8 2003-11-2 2004-12-3 2011-10-2 身份证号码
判断员工身份证号码位数是否正确（教材P6例题）
要判断员工的身份证号码是否正确，首先判断的是其位数是否正确。目前国内使用的身份证号码的位数只有1 和18位，如果员工的身份证号码位数不是这两种，那么其身份证号码必然是错误的。在下列表格中选择H5单元格，然后在编辑栏中输入如下公式： =OR(LEN(G5)=18,LEN(G5)=15) 或=OR(LEN(G5)={18,15}（两个公式任选一个）然后按Ctrl+Enter组合键，程序自动在当前单元格中判断员工张程的身份证号码的位数是否正确，之后选择单格H5，
张程袁宏伟欧光超张胜忠王绍华雷小兰李余降王玉良李观条李明奎李老东王亚不杨昌贤方云根吴重奶洪森林
男男男男女女男女男男男女男男女男
物检物检控制控制分析分析生料生料生料生料生料成球立窑立窑立窑立窑
522623196502020019 522630197005090014 52263019870409027X 510222196402225015 522630195605200134 522630197504230296 522630196304260154 522630165412080148 522630510612001 522630195305020131 522630195710100143 522630198210120280 522630198006200304 522630195803070131 522630620308017 52263019690315031X

excel表格在同一张表格里如何检查身份证号码和名字对照是否一致

竭诚为您提供优质文档/双击可除excel表格在同一张表格里如何检查身份证号码和名字对照是否一致篇一：如何用excel来判断身份证号码是否正确如何用excel来判断身份证号码是否正确？没有参照系，只要格式、位数没错，就难以辨其是非了；而给出参照系，即可判别：★按补充题意，用性别判别对与错：1、如果以c列输入性别为准，在b列输入公式“=iF(iF(mod(mid(a1,17,1),2)=0,"女","男")=c1,"第17位正确","第17位错")”，向下复制到需要处如b3，即可判别a列号码第17位数码之对错，如黄色区域所示。

2、如果以a列输入号码为准，在d列输入公式“=iF(iF(mod(mid(a1,17,1),2)=0,"女","男")=c1,"c列正确","c列填错")”，向下复制到需要处如d3，即可判别c列填写之对与错，如绿色区域所示。

3、若以c列为准，还可以用红色标示a列数码以示其17位错误，以与b列相呼应。

请选中a1:a3在“格式/条件格式”中查看公式和格式具体设置。

附件：辨别.xls篇二：excel中判断身份证号的几个方法excel中判断身份证号是否正确的方法探讨身份证有15位和18位两种，身份证位数是否正确，我们可以用len函数判断。

但身份证上的日期是否合法：月份是否在1-12之间，日期是否在1-31之间，并且2月份只有28或29天，其他月份30或31天，都不能超过范围。

另外一般规定6岁以上才可以办理身份证，也就是年份也有一个超范围的可能性。

综合起来看，有三类错误：“身份证位数不对”、“月日错误”、“年份错误”。

假定身份证号码在b1单元格，下面的公式可以综合判断以上三种错误：=iF(oR(len(b1)=18,len(b1)=15),iF(len(b1)=18,iF(oR(m onth(date(1*(mid(b1,7,4)),1*(mid(b1,11,2)),1*(mid(b 1,13,2))))1*(mid(b1,11,2)),day(date(1*(mid(b1,7,4)) ,1*(mid(b1,11,2)),1*(mid(b1,13,2))))1*(mid(b1,13,2) )),"月日错误",""),iF(len(b1)=15,iF(oR(month(date(1*(mid(b1,7,2))+1900,1*(mid(b1,9,2)),1*(mid(b1,11,2))))1*(mid(b1, 9,2)),day(date(1*(mid(b1,7,2))+1900,1*(mid(b1,9,2)) ,1*(mid(b1,11,2))))1*(mid(b1,11,2))),"月日错误",""))),"身份证位数不对")&iF(and(len(b1)=18,1*mid(b1,7,4)>yeaR(today())-6) ,"年份错误","")公式首先对身份证长度进行判断，是否等于15位或18位，然后对18位和15位身份证分别判断月日是否合法，最后判断18位身份证的年份是否合法。

利用excel校验身份证的原理和方法

利用excel校验身份证的原理和方法题记：校验学生录入身份证号信息一直是个很头疼的问题，现在学生的身份证号基本都是18位，利用excel数据有效性只能校验位数是否正确。

而对于合法性却望尘莫及，今天查了很多资料，终于找到了一种方法，现介绍如下：一、身份证编码原理：关于身份证第18是怎么计算的，原理如下：根据〖中华人民共和国国家标准 GB 11643-1999〗中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。

排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。

地址码（身份证前六位）表示编码对象常住户口所在县(市、旗、区)的行政区划代码。

生日期码（身份证第七位到第十四位）表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。

例如：1981年05月11日就用19810511表示。

顺序码（身份证第十五位到十七位）为同一地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号。

其中第十七位奇数分给男性，偶数分给女性。

校验码（身份证最后一位）是根据前面十七位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。

二、校验方法既然第十八位是校验位，那么我们将从这里入手方能搞定。

第十八位数字的计算方法为：1、将前面的身份证号码17位数分别乘以不同的系数。

从第一位到第十七位的系数分别为：7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 22、将这17位数字和系数相乘的结果相加。

3、用加出来和除以11，看余数是多少？4、余数只可能有0 1 2 3 4 5 6 7 8 9 10这11个数字。

其分别对应的最后一位身份证的号码为1 0 X 9 8 7 6 5 4 3 2。

5、通过上面得知如果余数是2，就会在身份证的第18位数字上出现罗马数字的Ⅹ。

如果余数是10，身份证的最后一位号码就是2。

excel表格中怎么判断身份证号是否合法？

excel表格中怎么判断⾝份证号是否合法？今天要和⼤家分享的是：excel判断⾝份证号的合法性！详见下图动态演⽰和步骤分解。

Excel2007 绿⾊版精简免费[58MB]
类型：办公软件
⼤⼩：58MB
语⾔：简体中⽂
时间：2016-06-27
查看详情
⽅⽅格⼦(Excel com加载项免费软件) V3.0.2 免费安装版
类型：应⽤其它
⼤⼩：85.4MB
语⾔：简体中⽂
时间：2016-06-14
查看详情
1、打开数据表格，如下图所⽰：
2、选中数据区域，如下图所⽰：
3、选择diy⼯具箱，如下图所⽰：
4、选择⾝份证按钮中的判断合法按钮，如下图所⽰：
5、弹出对话框，单击确定按钮，如下图所⽰：
6、选择存放位置，并单击确定，如下图所⽰：
7、确定后即可看到输出结果，如下图所⽰：。

怎么判断输入的身份证号码是否正确

怎么判断输入的身份证号码是否正确
1、开excel表格，在表格中可看到身份证号码常出现的问题为“身份证的位数不足”。

2、以及“身份证上的时间不符合实际”，“身份证重复”，点击“D3单元格”。

3、函数的第二部分：检验身份证号是否唯一，函数第三部分：确定身份证日期是否合法。

主要判断月份是否在1-12之间，日期是否超出当月的天数。

4、利用自动填充功能验证下方身份证的正确与否，可看到有问题的身份证均显示为false，此外还可利用条件格式找出重复值，选中要验证的单元格。

5、依次点击“条件格式”、“新建规则”，点击“仅对唯一值或重复值设置格式”，选择“重复”，点击“格式”，挑一个显眼的颜色，设置完成点击“确定”即可。

第 1 页共1 页。

Excel中的数据验证功能详解

Excel中的数据验证功能详解数据验证是Excel提供的一种功能，能够帮助用户验证输入数据的合法性，并在出现错误时提供提示或警告。

本文将详细介绍Excel中的数据验证功能，包括如何设置验证规则、应用范围、常见的验证类型以及一些实用技巧。

一、数据验证的设置数据验证的设置非常简便，只需按照以下步骤进行即可：1. 打开Excel，并选择需要设置数据验证的单元格或单元格范围。

2. 在菜单栏中选择“数据”选项卡，点击“数据工具”组中的“数据验证”。

3. 在弹出的数据验证对话框中，选择需要的验证规则和设置相应的条件。

4. 确认设置后，点击“确定”按钮即可完成数据验证的设置。

二、数据验证的应用范围数据验证可以应用于单个单元格、单个列或多个单元格范围。

用户可以根据实际需求选择合适的应用范围。

1. 单个单元格的数据验证：对于单个单元格的数据验证，只需选择目标单元格，按照上述设置过程进行设置即可。

该验证规则仅适用于选定的单元格。

2. 单个列的数据验证：对于单个列的数据验证，可以通过选定整个列的方式进行设置。

具体操作如下：（1）选定需要设置验证的列，如选定A列。

（2）点击“数据工具”组中的“数据验证”。

（3）将数据验证设置为适用于“整列”，点击“确定”按钮，完成设置。

3. 多个单元格范围的数据验证：对于多个单元格范围的数据验证，可以通过选定所需范围的方式进行设置。

具体操作如下：（1）选定需要设置验证的范围，如A1:A10。

（2）点击“数据工具”组中的“数据验证”。

（3）在数据验证对话框中，将“适用于”设置为“自定义”，输入范围的具体值，点击“确定”按钮，完成设置。

三、常见的验证类型Excel中的数据验证功能提供了多种验证类型，用户可根据实际需求选择合适的验证类型。

1. 数字类型验证：数字类型验证可以限制输入数据的范围或格式，如整数、小数、百分比等。

用户可以设置允许的最小值、最大值、小数位数等条件。

文本类型验证可以限制输入数据的长度、格式或内容。

excel身份证验证公式

excel身份证验证公式
Excel身份证验证公式
1.验证格式：数据校验公式可以验证身份证号码是否正确和合法。

Excel中可以使用“LEN()”函数来确定身份证号码字符串的长度，再与18位数对比来明确证书是否存在。

2.验证位数：只有18位数身份证号码才能使用来进行校验，15位号码已不再使用，可以通过旧省份前两位是否是“35”、“36”或“37”来判断。

3.验证省级代码：通过省份前两位码来验证省级代码，与身份证号码前两位相对应。

4.验证出生日期：可以通过对身份证号码出生日期的部分（第7到14位）与Excel 中“LEFT()”“MID()”等函数结合，来验证身份证号码的出生日期，验证准确。

5.验证顺序号：通过身份证号码的第16位和第17位数字代表性别，第15位和第18位数字代表性别，第15位数字用于最后一位校验码的算法，如果正确，身份证号码可以正确匹配！
6.验证位：计算机通过一组算法，通过身份证号码前17位数字，计算
出一个0-10之间的数字，如果运算结果与最后一位数字相等则表示验证通过！
此外，Excel中对于身份证号码的验证，还可以利用VBA编程语言进行。

其中可以使用“If（）Then（）Else（）”结构计算出一组结果，以决定身份证号码是否要验证成功。

excel：身份证号有效性检查

A：位数检查
1、选中A1:A10
2、“数据/有效性/设置/允许/自定义/公式/=OR(LEN(A1)=15,LEN(A1)=18)/出错警告/样式/停止/标题/错误/错误信息/号码位数错误，请重新输入！/确定”。

3、在选区输入非15、18位数值时即出现错误提示对话框并发出警告声响，只有点“取消”，再输入正确数值方可。

B：用性别判别对与错：
1、如果以C列输入性别为准，在B列输入公式“=IF(IF(MOD(MID(A1,17,1),2)=0,"女","男")=C1,"第17位正确","第17位错")”，向下复制到需要处如B3，即可判别A列号码第17位数码之对错，如黄色区域所示。

（提取性别=IF(MOD(MID(A2,17,1),2)=0,"女","男")）
2、如果以A列输入号码为准，在D列输入公式“=IF(IF(MOD(MID(A1,17,1),2)=0,"女","男")=C1,"C列正确","C 列填错")”，向下复制到需要处如D3，即可判别C列填写之对与错，如绿色区域所示。

C：检查有无重复
H1（或者其他空列)公式=COUNTIF(G:G,G1&"*")
向下填充到底出现大于1的数就是有重复的。

校验身份证号码及性别错误操作办法

校验身份证号码及性别错误操作办法
第一步：在个人所得税申报系统的个人信息登记模块中导出个人信息。

第二步：打开刚才导出的登记信息的EXCEL文件，选取全部内容（Ctrl+A或者用鼠标拉选）并复制（Ctrl+C或者菜单
“编辑”→“复制”）。

第三步：打开附件校验身份证号码及性别的EXCEL文件，选
择A2格，右击，选择粘贴。

（如果没有粘贴成功，请重复第二第三步。

）
结果：系统自动检测身份证号码及性别是否冲突，如果有冲突，将在该行A列显示“错误”字样。

此时，已经可以知道个人所得税申报系统中到底有多少人身份证明号码与性别冲突，根据核实情况到系统中修改相关信息。

注意：此检测方法只适用于国内人员中使用身份证作为身份证明类别的人员，并不使用国外人员以及使用其他身份证明类别（如军官证）的人员。

本表只检测至第500行，如果贵公司国内个人登记信息超过此数目，请自行复制A列检查内容至所需复制的行数。

利用Excel进行数据验证与校验

利用Excel进行数据验证与校验Excel是一款功能强大的电子表格软件，不仅可以进行数据的录入、计算和分析，还能通过数据验证和校验功能提高数据的准确性和可靠性。

本文将介绍如何利用Excel进行数据验证与校验，以确保数据的完整性和一致性。

一、数据验证的基本概念和作用数据验证是指对输入的数据进行逻辑上的检查，并确保数据符合既定的规则和条件。

通过数据验证，可以提前发现和纠正数据中的错误，避免在后续的计算和分析中出现问题。

Excel提供了丰富的数据验证工具和选项，可以根据需要设置不同类型的数据验证规则。

二、设置数据验证规则1. 打开Excel，选择需要进行数据验证的单元格或单元格范围；2. 在“数据”选项卡中，点击“数据工具”下的“数据验证”按钮；3. 在“设置”选项卡中，选择合适的验证规则，如整数、日期、长度等；4. 根据规则要求填写相应的数值范围、日期格式、字符个数等；5. 在“输入提示”选项卡中，填写验证失败时显示的提示信息；6. 在“错误警告”选项卡中，选择验证失败时的警告类型和显示的错误信息；7. 点击“确定”完成数据验证规则的设置。

三、常用的数据验证规则1. 整数验证：用于检查所输入的数据是否为整数，并限定上下限范围；2. 小数验证：用于检查所输入的数据是否为小数，并设定小数位数；3. 文本长度验证：用于检查所输入的文本是否符合指定的字符个数范围；4. 日期验证：用于检查所输入的日期是否符合指定的日期格式和范围；5. 列表验证：用于选择列出的值之一，限定输入必须在给定的列表中；6. 自定义公式验证：根据自定义的公式进行数据的验证。

四、数据校验的方法和步骤数据校验是确保数据在录入过程中的准确性和一致性，通过比较、匹配和合法性判断等方式进行数据校验，可以有效地减少错误和重复工作。

以下是一些常用的数据校验方法和步骤。

1. 数据比较校验：将要校验的数据与已有的参考数据进行比较，查找相同或相似的数据，检测重复、缺失或错误的数据；2. 数据匹配校验：将要校验的数据与已有的参考数据进行匹配，检查数据的一致性和正确性，发现不匹配或有问题的数据；3. 合法性判断校验：根据业务规则和逻辑条件对数据进行合法性判断，确保数据满足一定的要求和约束；4. 数据格式校验：检查数据的格式和结构，例如电话号码、邮政编码、身份证号等，确保数据的正确性和完整性。

Excel中进行验证身份证号数据有效性的操作技巧

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

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

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

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

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

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

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

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

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

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

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

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

：EXCEL身份证核对公式

EXCEL 身份证查对公式EXCEL 中你输入的公民身份号码正确吗?目录 :1、输入错误自动红色显示提示2、15 位身份证号码升位为18 位公民身份号码3、自动生成出诞辰期和性别4、中华人民共和国国家标准GB 11643-1999公民身份号码一、输入错误自动红色显示提示在公司报送的 EXCEL 电子表格中 ,常常碰到公民身份号码输入错误而给工作带来不用要的麻烦 ,降低了工作效率。

有没有方法在公民身份号码录入错误时进行自动提示呢?其实【中华人民共和国国家标准码第 18 位校验码已作了详尽的规定而已。

GB 11643-1999 】对公民身份号,不过我们在电算工作中极少用到可用以下方法在EXCEL 中录入公民身份号码错误时自动红字显示:选择需要录入公民身份号码的单元格 (如 A1), 选择菜单 :格式 /条件格式。

①点击条件 1 左侧的下拉箭头 ,选择公式 ,在右边的公式栏中输入以下公式 :=AND(LEN(D4)<>0,LEN(D4)<>15,LEN(D4)<>18)接着单击格式 ,点击字体 ,颜色选择红色 ,确立。

②在条件格式中点增添,点击条件 2 左侧的下拉箭头 ,选择公式 ,在右边的公式栏中输入以下公式:=MID("10X98765432",MOD(SUMPRODUCT(MID(D4,ROW($1:$ 17),1)*MOD(2^(18-ROW($1:$17)),11) ),11)+1,1)<>MID(D4,18,1)接着单击条件 2 中的格式 ,点击字体 ,颜色选择红色 ,确立 ,确立。

右键按住 A1 单元格右下角的小黑点 ,向下拖至所需要录入公民身份号码的全部单元格 ,松开右键 ,选择仅填补格式。

在接着的录入中 ,凡是输入不是 15 位或 18 位 ,或许输入 18 位错误的公民身份号码都会红色显示。

身份证重复显蓝色的公式:COUNTIF($D$4:$D$65536,D4)>1二、15 位身份证号码升位为18 位公民身份号码要使单元格中 A1 中的 15 位身份证号码升位为 18 位公民身份号码 , 只需在单元格 A2 中输入以下公式即可 :=REPLACE(A1,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A1,7,,19),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11)+1,1)或=REPLACE(A1,7,,19)&LOOKUP(MOD(SUMPRODUCT(MID(RE PLACE(A1,7,,19),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),{0,1,2,3,4,5,6,7,8,9,10},{1,0,"X",9,8,7,6,5,4,3,2})三、自动生成出诞辰期和性别假如单元格 A1 为身份证号码或公民身份号码 ,单元格 A2 为性别 , 单元格 A3 为出诞辰期 ,则操作以下 :在单元格 A2 中输入以下公式 ,则会自动生成性别:=IF(LEN(A1)=18,IF(INT(MID(A1,17,1)/2)=MID(A1,17,1)/2,"女 "," 男"),IF(LEN(A1)=15,IF(INT(MID(A1,15,1)/2)=MID(A1,15,1)/2,"女","男"),""))在单元格 A3 中输入以下公式 ,则会自动生成出诞辰期:=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2) ),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),""))[顶] EXCEL 中多条件乞降、计数的 4 种方法EXCEL 中多条件乞降、计数的方法大概可概括为 4 种:⒈自动挑选法⒉归并条件法⒊数组公式法⒋调用函数法先翻开上边的工作表,分别用这 4 种方法对同时知足“A2:A15地区为A,B2:B15 地区为 10,C2:C15 地区为Ⅰ”条件的 E2:E15 地区进行乞降、计数。

用于验证身份证号码的有效性,包括长度检查、字符检查和校验位检查的Excel函数公式

该函数用于验证身份证号码的有效性，包括长度检查、字符检查和校验位检查。

Function Peng(IDCard As String) As String' 定义变量Dim Weights As VariantDim CheckDigits As StringDim Sum As IntegerDim Remainder As IntegerDim CalculatedCheckDigit As StringDim i As Integer' 校验身份证号码的长度是否为18位If Len(IDCard) <> 18 ThenPeng = "错误：长度不正确" ' 返回错误信息Exit FunctionEnd If' 校验前17位是否全为数字For i = 1 To 17If Not IsNumeric(Mid(IDCard, i, 1)) ThenPeng = "错误：包含非数字字符" ' 如果有非数字字符，返回错误信息Exit FunctionEnd IfNext i' 校验最后一位字符是否为数字或字母XIf Not (Mid(IDCard, 18, 1) Like "[0-9X]") ThenPeng = "错误：最后一位字符无效" ' 返回错误信息Exit FunctionEnd If' 定义权重系数数组Weights = Array(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1)' 计算前17位数字的加权和Sum = 0For i = 1 To 17Sum = Sum + CInt(Mid(IDCard, i, 1)) * Weights(i - 1) ' 将每位数字乘以对应的权重Next i' 计算校验码Remainder = Sum Mod 11 ' 计算加权和的模11CheckDigits = "10X98765432" ' 定义校验码对应的字符串CalculatedCheckDigit = Mid(CheckDigits, Remainder + 1, 1) ' 获取计算出的校验位' 校验最后一位字符If UCase(Mid(IDCard, 18, 1)) = CalculatedCheckDigit ThenPeng = "正确" ' 如果校验位正确，返回“正确”ElsePeng = "错误" ' 如果校验位不正确，返回“错误”End IfEnd Function使用方法1.打开Excel:启动Microsoft Excel并打开你要使用的工作簿。