如何用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表格输入身份证正确性实时验证
一、示例使用的是 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位身份证的年份是否合法。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
EXCEL中如何提取身份证出生日期、性别、检验身份证号
码的正确性
中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为：１５位：６位数字常住户口所在县市的行政区划代码，６位数字出生日期代码，３位数字顺序码。
１８位：６位数字常住户口所在县市的行政区划代码，８位数字出生日期代码，３位数字顺序码和１位检验码。
其中３位数字顺序码，是为同一地址码的同年同月同日出生人员编制的顺序号，偶数的为女性，奇数的为男性。
１、提取籍贯地区的行政区划代码（A2为身份证号，下同）
１５与１８位通用：=LEFT(A2,6)
如果有一个编码和省份地区的对照表，可以用VLOOKUP函数来提取地区信息。
２、提取出生日期信息
１５位：=--TEXT(19&MID(A2,7,6),"#-00-00")
１８位：=--TEXT(MID(A2,7,8),"#-00-00")
１５与１８位通用：=--TEXT(IF(LEN(A2)=15,19,"")&MID(A2,7,6+IF(LEN(A2)=18,2,0)),"#-00-00") 简化公式：=--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")
(请将输入公式的单元格格式设置为日期格式）
３、提取性别信息
１５位：=IF(MOD(RIGHT(A2),2)=1,"男","女")
１８位：=IF(MOD(MID(A2),17,1)=1,"男","女")
１５与１８位通用：=IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1,"男","女")
简化公式：=IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女")
４、检验身份证号码的正确性
１８位身份证号码的最后一位是检验码，它是根据身份证前17位数字依照规则计算出来的，其值０～９或X。
一般情况只要有一位数字输入错误，依照规则计算后就会与第18位数不符。
当然不排除按错误号码计算后恰好与检验码相符的情况，但这种情况出现的可能性较低。
因此，对１８位号码的验证采用如下公式：
=MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-R OW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2,1)
对于１５位身份证，由于没有检验码，我们只能简单地去判断出生日期代码是否是一个有效的日期，避免输入一些像“731302”或“980230“等这样不存在的日期。
=ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00"))
综合１５位和１８位后的通用公式为：
=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:1 7")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00"))))
由于目前１５位身份证号码已经很少了，如果对１５位的号码不需要作进一步的判断，则公式可以简化成：
=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:1 7")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),LEN(A2)=15) 将上面的公式放到B2单元格，如果结果为TRUE，则身份证号是正确的，结果为FALSE 则是错误的。
你也可以将上述公式放在数据有效性中，防止录入错误的身份证号。
操作方法:选择需要输入身份证的全部单元格区域,比如A2:A10,点菜单"数据"-"有效性",在"允许"的下拉框中选择"自定义",在"公式"输入上面的15位和18位通用公式,确定以后即可。
注意:公式里的"A2"是你刚才选定要输入身份证的单元格区域的第一个单元格,如果你是要在C3:C20输入身份证号,则将公式里的"A2"改为"C3"。
另外,你也可以先设置好某单个单元格的数据有效性(这时公式的A2改为选定的单元格),再用格式刷将其格式刷到其他需要相同设置的单元格。
５、１５位升为１８位
=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(RE PLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2 )
６、１８位转换为１５位
=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)
7、示例
表中公式:
B2 =IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:1 7")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00"))))
C2=IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,) D2=IF(A2<>"",IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女"),)
E2 =IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00"),T ODAY(),"y"),)
F2=IF(A2<>"",VLOOKUP(LEFT(A2,2),地区表!A:D,2,),)
H2 =IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(RE PLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2) I2=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)。