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))。
范文：EXCEL身份证核对公式
EXCEL身份证核对公式EXCEL中你输入的公民身份号码正确吗?目录:1、输入错误自动红色显示提示2、15位身份证号码升位为18位公民身份号码3、自动生成出生日期和性别4、中华人民共和国国家标准GB 11643-1999 公民身份号码一、输入错误自动红色显示提示在企业报送的EXCEL电子表格中,经常遇到公民身份号码输入错误而给工作带来不必要的麻烦,降低了工作效率。
有没有办法在公民身份号码录入错误时进行自动提示呢?其实【中华人民共和国国家标准GB 11643-1999】对公民身份号码第18位校验码已作了详细的规定,只是我们在电算工作中很少用到而已。
可用以下方法在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(SUMPRODU CT(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函数身份证公式验证
excel函数身份证公式验证示例：居民身份证校验码计算方法居民身份证前17位加权因子为C1，C2，……,C17模为：7，9，10，5，8，4，2，1，6，3，7，9，10，5，8，4，2，设某一17位数字是12345678901234567，计算其校验码步骤如下：第一步：计算17位数字各位数字与对应的加权因子的乘积的和S S=1×7+2×9+3×10+4×5+5×8+6×4+7×2+8×1+9×6+0×3+ 1×7+2×9+3×10+4×5+5×8+6×4+7×2=368；第二步：计算S÷11的余数T=S MOD 11=368 mod 11=5；第三步：计算（12—T）÷11的余数R，如果R=10，校验码为字母“X”；如果R≠10，校验码为数字“R”R=（12—5）mod 11=7。
该17位数字的校验码是7，聚合在一起为123456789012345677的身份证编码。
=IF(LEN(J5)=0,"空",IF(LEN(J5)=15,"老号",IF(LEN(J5)<>18,"位数不对",IF(CHOOSE(MOD(SUM(MID(J5,1,1)*7+MID(J5,2,1)*9+MID(J5,3, 1)*10+MID(J5,4,1)*5+MID(J5,5,1)*8+MID(J5,6,1)*4+MID(J5,7,1)*2+MID(J5,8,1)*1+MID(J5 ,9,1)*6+MID(J5,10,1)*3+MID(J5,11,1)*7+MID(J5,12,1)*9+MID(J5,13,1)*10+MID(J5,14,1)*5+ MID(J5,15,1)*8+MID(J5,16 ,1)*4+MID(J5,17,1)*2),11)+1,1,0,"X",9, 8,7,6,5,4,3,2)=IF(ISNUMBER(RIGHT(J5,1)*1),RIGHT( J5,1)*1,"X"),"有效","无效"))))。
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函数公式
Excel函数公式计算技巧
一、提取性别：
以D2列【身份证号码】栏为坐标，
则C2列【性别】栏输入公式：=IF(MOD(MID(D2,17,1),2)=1,"男","女")
二、提取出生日期：
以D2列【身份证号码】为坐标，
则E2列【出生日期】栏公式为：=MID(D2,7,8)
三、提取年龄：
以D2列【身份证号码】为坐标，
则F2列【年龄】栏公式为：=YEAR(TODAY())-MID(D2,7,4)
四、提取生肖：
以D2列【身份证号码】为坐标，
则G2列【生肖】栏公式为：=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(D2,7,4),12)+1,1)
五、计算工龄：
以H2列【入职时间】栏为坐标，
则I2列【工龄】栏输入公式：=DATEDIF(H2,TODAY(),"Y")
六、计算工龄工资：
以I2 列【工龄】为坐标，若工龄工资每年加50元。
则L2列【工龄工资】栏公式为：则=I2*50
七、计算合同到期日：
以H2列【入职时间】和J2【合同期限】为坐标
则F2列【合同到期日】栏公式为：=DATE(YEAR(H2)+J2,MONTH(H2),DAY(H2)-1)。
利用Excel进行数据验证与校验
利用Excel进行数据验证与校验Excel是一款功能强大的电子表格软件，不仅可以进行数据的录入、计算和分析，还能通过数据验证和校验功能提高数据的准确性和可靠性。
本文将介绍如何利用Excel进行数据验证与校验，以确保数据的完整性和一致性。
一、数据验证的基本概念和作用数据验证是指对输入的数据进行逻辑上的检查，并确保数据符合既定的规则和条件。
通过数据验证，可以提前发现和纠正数据中的错误，避免在后续的计算和分析中出现问题。
Excel提供了丰富的数据验证工具和选项，可以根据需要设置不同类型的数据验证规则。
二、设置数据验证规则1. 打开Excel，选择需要进行数据验证的单元格或单元格范围；2. 在“数据”选项卡中，点击“数据工具”下的“数据验证”按钮；3. 在“设置”选项卡中，选择合适的验证规则，如整数、日期、长度等；4. 根据规则要求填写相应的数值范围、日期格式、字符个数等；5. 在“输入提示”选项卡中，填写验证失败时显示的提示信息；6. 在“错误警告”选项卡中，选择验证失败时的警告类型和显示的错误信息；7. 点击“确定”完成数据验证规则的设置。
三、常用的数据验证规则1. 整数验证：用于检查所输入的数据是否为整数，并限定上下限范围；2. 小数验证：用于检查所输入的数据是否为小数，并设定小数位数；3. 文本长度验证：用于检查所输入的文本是否符合指定的字符个数范围；4. 日期验证：用于检查所输入的日期是否符合指定的日期格式和范围；5. 列表验证：用于选择列出的值之一，限定输入必须在给定的列表中；6. 自定义公式验证：根据自定义的公式进行数据的验证。
四、数据校验的方法和步骤数据校验是确保数据在录入过程中的准确性和一致性，通过比较、匹配和合法性判断等方式进行数据校验，可以有效地减少错误和重复工作。
以下是一些常用的数据校验方法和步骤。
1. 数据比较校验：将要校验的数据与已有的参考数据进行比较，查找相同或相似的数据，检测重复、缺失或错误的数据；2. 数据匹配校验：将要校验的数据与已有的参考数据进行匹配，检查数据的一致性和正确性，发现不匹配或有问题的数据；3. 合法性判断校验：根据业务规则和逻辑条件对数据进行合法性判断，确保数据满足一定的要求和约束；4. 数据格式校验：检查数据的格式和结构，例如电话号码、邮政编码、身份证号等，确保数据的正确性和完整性。
Excel教程：countif函数查身份证号，明明不同但显示重复的原因
Excel教程：countif函数查身份证号，明明不同但显示重复的原因雅雯虚拟了一些身份证号，观察刘志如和蔡晓的身份证号码不一样，D3单元格的公式：=IF(COUNTIF(C:C,C3)=1,"不重复","重复")，却显示重复。
countif函数查身份证号，明明不同但显示重复，这是为什么？因为，Excel默认数据只有15位，超过15位的部分在数据比对时全部视作0。
由于身份证号码位数已经超过15位，那么第16位开始统一都会认为是 0，因此就出现了假的重复值。
这就是我们常说的EXCEL中的假重复。
直接用COUNTIF公式常规用法COUNTIF(C:C,C3)，查重肯定失手。
我们可以把公式稍作优化，就可以得到正确结果。
优化后的公式为：=IF(COUNTIF(C:C,C3&"*")=1,"不重复","重复")COUNTIF（指定区域，条件），对指定区域中符合条件的单元格进行计数。
指定区域是对单元格进行计数的区域。
条件的形式可以是数字、表达式或文本等。
"*"是通配符，代表任意多个字符的文本。
这里使用 &"*"将单元格数字后面统一添加*符号强行当做文本进行识别统计，就可以准确的通过计数值是否等于1识别出数字是否重复。
↑点击观看Excel视频↑最后，欢迎大家加入我们的《Excel起步训练营》，和雅雯一起学习。
微信群互动答疑、视频课程永久观看、配套练习课件、课程笔记讲义，能帮助伙伴们很快提升自己的Excel技能。
扫下面二维码加入，等你！。
利用Excel涵数提取18位身份证号码信息
证号会以科学记数法显示，需要更改一下数字格式。选 中该列中的所有身份证号后，右击鼠标，选择“设置单 元格格式”。在弹出对话框中“数字”标签内的“
分类”设为“文本”，然后点击确定。 首先，提取出出生日期将光标指针放到“出生日期”列 的单元格内，这里以C5单元格为例。然后输入“=MID
(B5，7，4)&"年"&MID(B5，11，2)&"月"&MID(B5，13， 2)&"日"”注意：外侧的双引号不用输入，函数式中的引号 和逗
号等符号应在英文状态下输入。 回车后，你会发现在C5单元格内已经出现了出生日期。 然后，选中该单元格后拖动填充柄，其它单元格内就会 出现相应的
出生日期。MID函数是EXCEL提供的一个“从字符串中提 取部分字符”的函数命令，具体使用格式在EXCEL中输入 MID后会出现提示。 提下
来，我们再来提取性别信息选中“性别”列的单元格， 如D5。输入“=IF(MID(B5，17，1)/2=TRUNC(MID(B5，17， 1)/2
全文完！谢谢欣赏！感谢您的支持！
云ERP /
)，"女"，"男")”后回车，就会自动判断出性别了。拖动填 充柄让其他的性别也自动输入。
最后，我们来计算年龄其中C5是出生日期所在列，双
击年龄所在列，然后输入下面公式=YEAR(NOW())-YEAR(C5) 回车后，即可得到年证信息提取什么信息都可以用这个方法来实现。
最后我们来计算年龄其中c5是出生日期所击年龄所在列然后输入下面公式yearnowyearc5回车后即可得到年龄复制到其他单元格即可完成
18位的新一代身份证了，里面的数字都是有规律的。前6 位数字是户籍所在地的代码，7-14位就是出生日期。第17 位“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。