核对身份证公式(检验)模板

合集下载

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

身份证号验证：=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。

身份证校验码计算公式

身份证校验码计算公式校验码是识别一个身份证号码是否真实存在的重要的依据。

那我们要如何通过校验码来判断一个身份证号码是否真实的呢？校验码如何判断身份证号码真伪呢？我们来看看具体计划过程。

第一步：将身份证号码的第1位数字与7相乘；将身份证号码的第2位数字与9相乘；将身份证号码的第3位数字与10相乘；将身份证号码的第4位数字与5相乘；将身份证号码的第5位数字与8相乘；将身份证号码的第6位数字与4相乘；将身份证号码的第7位数字与2相乘；将身份证号码的第8位数字与1相乘；将身份证号码的第9位数字与6相乘；将身份证号码的第10位数字与3相乘；将身份证号码的第11位数字与7相乘；将身份证号码的第12位数字与9相乘；将身份证号码的第13位数字与10相乘；将身份证号码的第14位数字与5相乘；将身份证号码的第15位数字与8相乘；将身份证号码的第16位数字与4相乘；将身份证号码的第17位数字与2相乘。

第二步：将第一步身份证号码1~17位相乘的结果求和，全部加起来。

第三步：用第二步计算出来的结果除以11，这样就会出现余数为0，余数为1，余数为2，余数为3，余数为4，余数为5，余数为6，余数为7，余数为8，余数为9，余数为10共11种可能性。

第四步：如果余数为0，那对应的最后一位身份证的号码为1；如果余数为1，那对应的最后一位身份证的号码为0；如果余数为2，那对应的最后一位身份证的号码为X；如果余数为3，那对应的最后一位身份证的号码为9；如果余数为4，那对应的最后一位身份证的号码为8；如果余数为5，那对应的最后一位身份证的号码为7；如果余数为6，那对应的最后一位身份证的号码为6；如果余数为7，那对应的最后一位身份证的号码为5；如果余数为8，那对应的最后一位身份证的号码为4；如果余数为9，那对应的最后一位身份证的号码为3；如果余数为10，那对应的最后一位身份证的号码为2。

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

身份证正确检验及验证公式

身份证正确检验及验证公式验证公式一=IF(LEN(H20)=18,IF(RIGHT(H20,1)="X",IF(CHOOSE(MOD(SU M(LEFT(RIGHT(H20,18))*7+LEFT(RIGHT(H20,17))*9+LEFT(RIGHT( H20,16))*10+LEFT(RIGHT(H20,15))*5+LEFT(RIGHT(H20,14))*8+L EFT(RIGHT(H20,13))*4+LEFT(RIGHT(H20,12))*2+LEFT(RIGHT(H20 ,11))*1+LEFT(RIGHT(H20,10))*6+LEFT(RIGHT(H20,9))*3+LEFT(RI GHT(H20,8))*7+LEFT(RIGHT(H20,7))*9+LEFT(RIGHT(H20,6))*10+ LEFT(RIGHT(H20,5))*5+LEFT(RIGHT(H20,4))*8+LEFT(RIGHT(H20, 3))*4+LEFT(RIGHT(H20,2))*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT( RIGHT(H20,1)),"正确！","出错啦！"),IF(CHOOSE(MOD(SUM(LEFT(RIGHT(H20,18))*7+LEFT(RIGHT(H 20,17))*9+LEFT(RIGHT(H20,16))*10+LEFT(RIGHT(H20,15))*5+LEF T(RIGHT(H20,14))*8+LEFT(RIGHT(H20,13))*4+LEFT(RIGHT(H20,1 2))*2+LEFT(RIGHT(H20,11))*1+LEFT(RIGHT(H20,10))*6+LEFT(RIG HT(H20,9))*3+LEFT(RIGHT(H20,8))*7+LEFT(RIGHT(H20,7))*9+LEF T(RIGHT(H20,6))*10+LEFT(RIGHT(H20,5))*5+LEFT(RIGHT(H20,4)) *8+LEFT(RIGHT(H20,3))*4+LEFT(RIGHT(H20,2))*2),11)+1,1,0,"X", 9,8,7,6,5,4,3,2)=LEFT(RIGHT(H20,1))*1,"正确！","出错啦！")),IF(LEN(H20)=15,"老号，请注意！",IF(LEN(H20)=0,"缺号码","位数不对！")))验证公式二=MID("10X98765432",MOD(SUMPRODUCT(MID(H20,ROW(I NDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(H20,1)身份证有15位和18位两种，身份证位数是否正确，我们可以用LEN函数判断。

第二代居民身份证号码的校验码计算模型、步骤、公式

注：上述⑧个公式，用任何一个都可以，校验结果是一致的。使用时，需要将公式中的D2全部替换为相应单元格名称。
第二代居民身份证号码的校验码计算公式
①最原始的公式，逐个运算，便于理解
=LEFT(D2,17)&LOOKUP(MOD(SUM(MID(D2,1,1)*MOD(2^17,11),MID(D2,2,1)*MOD(2^16,11),MID(D2,3,1)*MOD(2^15,11),MID(D2 ,4,1)*MOD(2^14,11),MID(D2,5,1)*MOD(2^13,11),MID(D2,6,1)*MOD(2^12,11),MID(D2,7,1)*MOD(2^11,11),MID(D2,8,1)*MOD(2^10, 11),MID(D2,9,1)*MOD(2^9,11),MID(D2,10,1)*MOD(2^8,11),MID(D2,11,1)*MOD(2^7,11),MID(D2,12,1)*MOD(2^6,11),MID(D2,13,1)* MOD(2^5,11),MID(D2,14,1)*MOD(2^4,11),MID(D2,15,1)*MOD(2^3,11),MID(D2,16,1)*MOD(2^2,11),MID(D2,17,1)*MOD(2^1,11)),1 1),{0;1;2;3;4;5;6;7;8;9;10},{1;0;"X";9;8;7;6;5;4;3;2}) =LEFT(D2,17)&LOOKUP(MOD(SUM(MID(D2,1,1)*2^17,MID(D2,2,1)*2^16,MID(D2,3,1)*2^15,MID(D2,4,1)*2^14,MID(D2,5,1)*2^13, MID(D2,6,1)*2^12,MID(D2,7,1)*2^11,MID(D2,8,1)*2^10,MID(D2,9,1)*2^9,MID(D2,10,1)*2^8,MID(D2,11,1)*2^7,MID(D2,12,1)*2^6,M ID(D2,13,1)*2^5,MID(D2,14,1)*2^4,MID(D2,15,1)*2^3,MID(D2,16,1)*2^2,MID(D2,17,1)*2^1),11),{0;1;2;3;4;5;6;7;8;9;10},{1;0;"X";9;8; 7;6;5;4;3;2}) =LEFT(D2,17)&MID("10X98765432",MOD(SUM(MID(D2,1,1)*MOD(2^17,11),MID(D2,2,1)*MOD(2^16,11),MID(D2,3,1)*MOD(2^15, 11),MID(D2,4,1)*MOD(2^14,11),MID(D2,5,1)*MOD(2^13,11),MID(D2,6,1)*MOD(2^12,11),MID(D2,7,1)*MOD(2^11,11),MID(D2,8,1)* MOD(2^10,11),MID(D2,9,1)*MOD(2^9,11),MID(D2,10,1)*MOD(2^8,11),MID(D2,11,1)*MOD(2^7,11),MID(D2,12,1)*MOD(2^6,11),MI D(D2,13,1)*MOD(2^5,11),MID(D2,14,1)*MOD(2^4,11),MID(D2,15,1)*MOD(2^3,11),MID(D2,16,1)*MOD(2^2,11),MID(D2,17,1)*MOD (2^1,11)),11)+1,1) =LEFT(D2,17)&MID("10X98765432",MOD(SUM(MID(D2,1,1)*2^17,MID(D2,2,1)*2^16,MID(D2,3,1)*2^15,MID(D2,4,1)*2^14,MID(D2 ,5,1)*2^13,MID(D2,6,1)*2^12,MID(D2,7,1)*2^11,MID(D2,8,1)*2^10,MID(D2,9,1)*2^9,MID(D2,10,1)*2^8,MID(D2,11,1)*2^7,MID(D2,1 2,1)*2^6,MID(D2,13,1)*2^5,MID(D2,14,1)*2^4,MID(D2,15,1)*2^3,MID(D2,16,1)*2^2,MID(D2,17,1)*2^1),11)+1,1)

身份证校验算法

下面介绍算法：
计算公式令结果为 Y，Y的计算公式为：Y = mod(S, 11)根据下表找出 Y 对应的校验码即为要求身份证号码的校验码C。
例如：假设某女性居民的15位身份证号码是320105820927512，32表示江苏省，01表示南京市，05表示鼓楼区，820927表示1982年9月27日。512表示是该地区同年同月同日的第512人，其中2表示是女性。那么，升级为18位后的号码为32010519820927512C。下面就根据公式来计算C的值。
根据《中华人民共和国国家标准GB 11643-1999》中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。顺序码的奇数分给男性，偶数分给女性。校验码是根据前面十七位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。
String[] w = {"1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2"};
for (int i=0; i<17; i++) {
int ai = Integer.parseInt(arrIdCard.substring(i,i+1));
具体实现代码如下（JAVA）：
public boolean isIdCard(String arrIdCard) {
int sigma = 0;

身份证正确检验及验证公式

验证公式一=IF(LEN(H20)=18,IF(RIGHT(H20,1)="X",IF(CHOOSE(MOD(SUM(LEFT(RIGHT(H20,18) )*7 LEFT(RIGHT(H20,17))*9 LEFT(RIGHT(H20,16))*10 LEFT(RIGHT(H20,15))*5 LEFT(RIGHT(H20,14))*8 LEFT(RIGHT(H20,13))*4 LEFT(RIGHT(H20,12))*2LEFT(RIGHT(H20,11))*1 LEFT(RIGHT(H20,10))*6 LEFT(RIGHT(H20,9))*3LEFT(RIGHT(H20,8))*7 LEFT(RIGHT(H20,7))*9 LEFT(RIGHT(H20,6))*10LEFT(RIGHT(H20,5))*5 LEFT(RIGHT(H20,4))*8 LEFT(RIGHT(H20,3))*4LEFT(RIGHT(H20,2))*2),11)1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(H20,1)),"正确！","出错啦！"),IF(CHOOSE(MOD(SUM(LEFT(RIGHT(H20,18))*7 LEFT(RIGHT(H20,17))*9LEFT(RIGHT(H20,16))*10 LEFT(RIGHT(H20,15))*5 LEFT(RIGHT(H20,14))*8 LEFT(RIGHT(H20,13))*4 LEFT(RIGHT(H20,12))*2 LEFT(RIGHT(H20,11))*1LEFT(RIGHT(H20,10))*6 LEFT(RIGHT(H20,9))*3 LEFT(RIGHT(H20,8))*7LEFT(RIGHT(H20,7))*9 LEFT(RIGHT(H20,6))*10 LEFT(RIGHT(H20,5))*5LEFT(RIGHT(H20,4))*8 LEFT(RIGHT(H20,3))*4 LEFT(RIGHT(H20,2))*2),11)1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(H20,1))*1,"正确！","出错啦！")),IF(LEN(H20)=15,"老号，请注意！",IF(LEN(H20)=0,"缺号码","位数不对！")))验证公式二=MID("10X98765432",MOD(SUMPRODUCT(MID(H20,ROW(INDIRECT("1:17")),1)*2^(18 -ROW(INDIRECT("1:17")))),11) 1,1)=RIGHT(H20,1)身份证有15位和18位两种，身份证位数是否正确，我们可以用LEN函数判断。

身份证正确检验及验证公式

公式二出处
EXCEL提取身份证出生日期、性别、检验身份证号码正确性
中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为：
１５位：６位数字常住户口所在县市的行政区划代码，６位数字出生日期代码，３位数字顺序码。
１８位：６位数字常住户口所在县市的行政区划代码，８位数字出生日期代码，３位数字顺序码和１位检验码。
假定身份证号码在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(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),"年份错误","")

身份证核对公式

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(SUMPRODUCT(MID(REPLACE(A1,7,,19),ROW($1:$1 7),1)*MOD(2^(18-ROW($1:$17)),11)),11)+1,1)或=REPLACE(A1,7,,19)&LOOKUP(MOD(SUMPRODUCT(MID(REPLACE(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区域进行求和、计数。

身份证号码换算年龄、性别、出生日期的公式

身份证号码换算年龄、性别、出生日期的公式15位和18位的身份证号都合适的了。

设内容如下：........A列......B列......C列......D列....1 身份证号性别出生年月年龄2（输入身份证号）算年龄的：“=IF(OR(LEN(A2)=15,LEN(A2)=18),RIGHT(IF(IF(LEN(A2)=15, MID(A2,9,2),MID(A2,11,2) )*100+IF(LEN(A2)=15,MID(A2,11,2),MI D(A2,13,2))>MONTH(TODAY())*100+DAY(TODAY()),YEAR(TODAY())-IF(LEN(A2)=15,MID(A2,7,2),MID(A2,9,2))-1,YEAR(TODAY())-IF(L EN(A2)=15,MID(A2,7,2),MID(A2,9,2))),2),"证号错误")”算性别的：“=IF(LEN(A2)=15,(IF(OR(ABS(RIGHT(A2))=1,ABS(RIGHT(A2) )=3,ABS(RIGHT(A2))=5,A BS(RIGHT(A2))=7,ABS(RIGHT(A2))=9),"男","女")),(IF(OR(ABS(MID(A2,17,1))=1,ABS(MID(A2,17,1))=3,ABS(M ID(A2,17,1))=5,ABS(MID( A2,17,1))=7,ABS(MID(A2,17,1))=9),"男","女")))”算出生年月的：“=DATE(IF(LEN(E3)=15,MID(E3,7,2),MID(E3,9,2)),IF(LEN(E3) =15,MID(E3,9,2),MID(E3,11,2)),IF(LEN(E3)=15,MID(E3,11,2),MID(E3,13,2)))”只要把输入身份证号的单元格格式设为“文本”就是最关键的，准确。

筛重--身份证号码算年龄性别筛查重复

511221198302028210
一：如何提取身份证里面的出生年月为XXXX年XX月XX
日：=MID(E5,7,4)&"-"&MID(E5,11,2)&""&MID(E5,13,2)，其中E5为身份证(E1:$E$2,E1&"*")>1,1,"") 1、2003版word筛重：选择身份证列→数据→自动筛选 →升序→格式→条件格式→公式→输入上面公式[ =IF(COUNTIF(E1:$E$2,E1&"*")>1,1,"") 公式的列字母要注意改成身份证对应列，如公式中为E列]→选择显示颜色→确定 2、WPS筛重：选择身份证列→数据→升序→重复项→高亮显示重复项 3、07版和10版office筛重：选择身份证列→数据→升序→条件格式→突出显示单元格规则→重复项→选择颜色→确定
32 双击上面单元格可见公式
=DATEDIF(TEXT(MID(a1,INT(LEN(A1)/2-1)),"#-00-00"),TODAY(),"Y") 511221198302028823 双击上面单元格可见公式
二：18位身份证号码如何算年龄：如上
假设“A1”是某人身份证号码，要令其 B1为某人的年龄公式如下：在“B1”中输入 =DATEDIF(TEXT(MID(A1,7,INT(LEN(A1)/ 2-1)),"#-00-00"),TODAY(),"Y")，然后回车就可以了。如果在实际表格中不是 “A1”是身份证号码，而是“C1”，那么只需把公式中的“A1”改为“C1”在 D1中输入 =DATEDIF(TEXT(MID(C1,7,INT(LEN(C1)/ 2-1)),"#-00-00"),TODAY(),"Y"),同理可推。

60分钟整理了三种有效方法判断身份证号码是否重复

Excel工作表身份证查重的三种方法详解
身份证查重的第一种方法-通过条件格式查重
1.先向右复制第一行身份证号码并添加字符a
2.在下面按快捷键Ctrl+E可以给所有身份证号码后填充字母a，
3.然后选择条件格式
4.突出显示重复值即可
身份证查重的第二种方法-通过if函数嵌套countif函数完成
1.利用countif函数，查找符合条件重复值数量，
=COUNTIF(A2:A9,A2&"*")
第一参数：A2:A9，这是一个数据区域不是单个数据，所以依旧会保持原有的文本格式
第二参数：B2&"*"，在这里我们将身份证号码连接上一个星号，星号是通配符它代表任意多个字符，也可以代表没有字符，在这里他的作用就是代表没有任何字符，利用连接符号将身份证与星号连接后，身份证号码就会保持文本格式代入公式中计算，这样的话才能找到正确的结果
2.通过if函数，判断结果大于1就代表有重复，结果等于1则表示没有重复，
=IF(COUNTIF(A2:A9,A2&"*")>1,"重复","")
用这个公式的确可以找出重复的身份证号码（公式结果大于1），但是，这个公式只适用于数字个数相同的统计，如果不同就会出错。

如下图中A5和A9前18位相同，但多出了3个数字，用公式计算却是重复的。

所以遇到这种情况，用Sumproduct函数更靠谱些。

身份证查重的第三种方法-通过if函数嵌套Sumproduct函数。

：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来判断身份证号码是否正确

如何用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。