Excel验证身份证号码
EXCEL中如何提取身份证出生日期和性别信息以及检验身份证号码的正确性
1、检验身份证号码的正确性（1）录入前可以通过下面操作避免录入错误通用公式为：=IF(LEN(D6)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(D6,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT ("1:17")))),11)+1,1)=RIGHT(D6),IF(LEN(D6)=15,ISNUMBER(--TEXT(19&MID(D6,7,6),"#-00-00"))))你也可以将上述公式放在数据有效性中，防止录入错误的身份证号。
操作方法:选择需要输入身份证的全部单元格区域,比如D6:A10,点菜单"数据"-"有效性",在"允许"的下拉框中选择"自定义",在"公式"输入上面的15位和18位通用公式,确定以后即可。
注意:公式里的"D6"是你刚才选定要输入身份证的单元格区域的第一个单元格,如果你是要在C3:C20输入身份证号,则将公式里的"D6"改为"C3"。
另外,你也可以先设置好某单个单元格的数据有效性(这时公式的D6改为选定的单元格),再用格式刷将其格式刷到其他需要相同设置的单元格。
（2）录入后可以通过下面操作检测录入错误通用公式为：=IF(LEN(D6)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(D6,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(D6),IF(LEN(D6)=15,ISNUMBER(--TEXT(19&MID(D6,7,6),"#-00-00"))))将上面的公式放到B2单元格，如果结果为TRUE，则身份证号是正确的，结果为FALSE则是错误的。
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
ExcelVBA学习之身份证号校检自定义函数
ExcelVBA学习之身份证号校检自定义函数Excel VBA学习之身份证号校检自定义函数【问题提出】上次学习了一个用Excel函数的方法来解决这个问题，但是函数太长了，有时会忘记：=IF(LEN($B2)=18,IF(MID('10X98765432',MOD(SUMPRODUC T(VALUE(MID($B2,ROW($1:$17),1)),说明!$B$2:$B$18),11) 1,1)=RIGHT($B2,1),'合法','不合法'),'长度错误')能不能简单一点，工作时不用那么辛苦的输入这个函数【知识学习】自定义函数的基本语法：我们在Excel中使用的内置函数，有的没参数，比如Rand函数，有的有参数，比如Vlookup，参数还好几个，有些参数是区域，有些是数字。
我们今天就从最基本的开始，先来学习下定义一个没有参数和一个只有一个参数的函数，通过最基本的例子来掌握自定义函数的基本语法结构。
自定义函数的语法结构：Function 函数名(参数1，参数2，……，参数n)代码函数名 = 代码执行的结果End Function语法很简单，看来难在了怎么得到结果的这段代码上。
好了，下面我们就开始自定义一个没有参数的函数吧。
【写一个自定义函数用于检测身份证号是否正确】''====用于校检身份证号是否正确的自定义函数=========Function ID(n)Dim h, s, t, z As Integerwi = Array('7', '9', '10', '5', '8', '4', '2', '1', '6', '3', '7', '9', '10', '5', '8', '4', '2')y = Array('1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2') If Len(n) = 18 ThenFor h = 0 To 16r = Mid(n, h 1, 1)If IsNumeric(r) = False ThenID = '第 ' & h 1 & ' 位为非法字符'Exit FunctionEnd Ifs = s r * wi(h)Next ht = s Mod 11If UCase(Mid(n, 18)) = y(t) ThenID = '身份证号码正确'ElseID = '身份证号码不正确'End IfElseID = '位数少于18位'End IfEnd Function【使用方法】【测试成功】=====The End=====。
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(MONTH(DATE(1*( MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*(MID(B1,11,2)),DAY(D ATE(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))+190 0,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位身份证的年份是否合法。
身份证EXECL校验模板 含地址校验码
510727198409138609 500225198409138607 430501198502213779
0
0
0
0
3
9
1
510727 500225500200500000 430501430500430000
1 1265/3016/3021 2773/1031阳市
0009000000000510727500225198409138607500225500200500000126530163021重庆市大足县430501198502213779430501430500430000277310311032湖南省邵阳市00号码错误110000北京市110100110101东城区110102西城区110103崇文区110104宣武区110105朝阳区110106丰台区110107石景山区110108海淀区110109门头沟区110111房山区110112通州区110113顺义区110114昌平区110115大兴区110200110226平谷县110227怀柔县110228密云县110229延庆县120000天津市120100120101和平区120102河东区120103河西区120104南开区120105河北区120106红桥区120107塘沽区120108汉沽区120109大港区120110东丽区120111西青区120112津南区120113北辰区120114武清区120115宝坻区120200120221宁河县120223静海县120225蓟县130000河北省130100石家庄市130101130102长安区130103桥东区130104桥西区130105新华区130421邯郸县130423临漳县130424成安县130425大名县130426涉县130427磁县130428肥乡县130429永年县130430邱县130431鸡泽县130432广平县130433馆陶县130434魏县130435曲周县130481武安市130500邢台市130501130502桥东区130503桥西区130521邢台县130522临城县130523内丘县130524柏乡县130525隆尧县130526任县130527南和县130528宁晋县130529巨鹿县130530新河县130531广宗县130532平乡县130533威县130534清河县130535临西县130581南宫市130582沙河市130600保定市130601130602新市区130603北市区130604南市区130621满城县130622清苑县130623涞水县130624阜平县130625徐水县130626定兴县130627唐县130628高阳县130629容城县130630涞源县130924海兴县130925盐山
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中如何提取身份证出生日期、性别、检验身份证号码的正确性中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为：１５位：６位数字常住户口所在县市的行政区划代码，６位数字出生日期代码，３位数字顺序码。
１８位：６位数字常住户口所在县市的行政区划代码，８位数字出生日期代码，３位数字顺序码和１位检验码。
其中３位数字顺序码，是为同一地址码的同年同月同日出生人员编制的顺序号，偶数的为女性，奇数的为男性。
１、提取籍贯地区的行政区划代码（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。
excel表格的基本操作身份证号
excel表格的基本操作身份证号
大家知道excel表格的基本操作吗?下面我们就给大家详细介绍一下吧!我们积累了一些经验，在此拿出来与大家分享下，请大家互相指正。
方法一：
15位身份证号：410881********* 输出出生日期1979/06/05 =CONCATENATE(“19”,MID(E2,7,2),”/”,MID(E2,9,2),”/”,MID(E2,11,2))
公式解释：
a.MID(E2,7,2)为在身份证号码中获取表示年份的数字的字符串
b. MID(E2,9,2) 为在身份证号码中获取表示月份的数字的字符串
c. MID(E2,11,2) 为在身份证号码中获取表示日期的数字的字符串
d.CONCATENATE(“19”,MID(E2,7,2),”/”,MID(E2,9,2),”/”,MID(E2,11,2))目的就是将多个字符串合并在一起显示。
18位身份证号：410881************ 输出出生日期1979/06/05 =CONCATENATE(MID(E2,7,4),”/”,MID(E2,11,2),”/”,MID(E2,13,2))。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
使用excel校验身份证号码
校验学生录入身份证号信息一直是个很头疼的问题，现在学生的身份证号基本都是18位，利用excel 数据有效性只能校验位数是否正确。
而对于合法性却望尘莫及，今天查了很多资料，终于找到了一种方法，现介绍如下：
一、身份证编码原理：
关于身份证第18是怎么计算的，原理如下：根据〖中华人民共和国国家标准GB 11643-1999〗中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。
排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。
地址码（身份证前六位）表示编码对象常住户口所在县(市、旗、区)的行政区划代码。
（所有区域的编码可以到这个网站/tjbz/index.htm 查询到最新的县及县以上的行政编码资料。
）生日期码（身份证第七位到第十四位）表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。
例如：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 2
2、将这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。
例如：某男性的身份证号码是340524************。
我们要看看这个身份证是不是合法的身份证。
首先：我们得出，前17位的乘积和是189
然后：用189除以11得出的结果是17 2/11，也就是说余数是2。
最后：通过对应规则就可以知道余数2对应的数字是x。
所以，这是一个合格的身份证号码。
到此已经柳暗花明了，接下来就是写公式了，假设excel表的A1单元格记录的是身份证号码，我们在B1单元格输入如下公式：
=IF(LEN(A1)=0,"空",IF(LEN(A1)=15,"老号",IF(LEN(A1)<>18,"位数不对",IF(CHOOSE(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,1 ,0,"X",9,8,7,6,5,4,3,2)=IF(ISNUMBER(RIGHT(A1,1)*1),RIGHT(A1,1)*1,"X"),"正确","错误")))) 好了，搞定！！！
备注说明：关于大陆身份证有的人会发现前几位为什么变化了。
这主要出现在中国的重庆。
原有的重庆人的身份证多数以51开头。
以前隶属于四川的原因。
但新办的身份证可能是50开头，原因是行政区划改变所致。
中国各地的行政区划代码请参考国家统计局网站/tjbz/index.htm。