第二代 身份证校验工具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。
最新 第二代身份证号码提取出生年月日的公式 (很简单)
1、第二代身份证号码提取出生年月日的公式：=MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)注：A是列，1是行，&在数字键7的上方。
2、第二代身份证号码提取性别男、女的公式：=IF(MOD(MID(A1,17,1),2),"男","女")注：A是列，1是行。
3、excel用身份证号算年龄的工式比如身份证号保存在a1单元格，那么可以使用=mid(a1,7,4)来取出出生年，再用当前年份减去这个就是年龄了。
4、提取身份证出生年月="19"&MID(B1,9,2)&"年"&MID(B1,11,2)&"月"&MID(B1,13,2)&"日"5、提取身份证年龄=YEAR(NOW())-YEAR(IF(LEN(B1)=18,DATE(MID(B1,7,4),MID(B1,11,2),M ID(B1,13,2)),DATE(MID(B1,7,2),MID(B1,9,2),MID(B1,11,2))))6、提取身份证性别=IF(LEN(B1)=15,IF(MOD(MID(B1,15,1),2)=1,"男","女"),IF(MOD(MID(B1,17,1),2)=1,"男","女"))7、公式内的“B1”代表的是输入身份证号码的单元格。
Excel自动提取身份证中生日和性别以及年龄（注意：以B2单元格中是18位身份证号为例）一、提取出生日期的输入公式=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"二、判断性别“男女”的输入公式=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")三、利用身份证号码求年龄=IF(B2="","",DATEDIF(TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN (B2)=18)*2),"#-00-00"),TODAY(),"y"))八、计算年龄的公式？datedif(开始日期，结束日期，"M")九、用公式计算最大年龄？max(D3:D21)十、用公式计算25--35岁之间的人数？=COUNTIFS(D3:D21,">"&25,D3:D21,"<"&35)十一、计算25岁以下的人数?=COUNTIF(D3:D21,">"&25)十二、计算25岁以下人数的比例?=COUNTIF(D3:D21,">"&25)/count(D3:D21)十三、用excel做表输入年月日，可以变成例如1986-01-01的格式吗？能的话怎么变？先选中单元格，鼠标右击，“设置单元格格式”，“数字”，“自定义”，在类型处输入e-mm-dd 确定十四、1.自动录入性别:“=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))”if假如(len(C2)得到C2的字符长度等于15，[15时mod除以2的余数(取mid(C2单元格,第15个的，1个字符），除以2）]，[不等于15条件不成立时if(mod后面除以2的余数(取C2，第17个，的1个字符），除以2）]所涉及函数：len() 取单元格字符长度。
给你5个实用Excel函数公式搞定盘踞在Excel表格中的身份证
给你5个实用Excel函数公式搞定盘踞在Excel表格中的身份证在工作中，将身份证输入表格的大多是财务或是HR，本以为只要输入数字就可以了，可是，当大家做这事儿的时候，有多少人发现，这个身份证真的很抽风……如果你正做着有关工作，那么就一起来看一下以下内容吧，让你的Excel操作轻松碾压各种有关身份证的信息。
以下是一组和身份证有关的函数公式，如果你是财务人员或是一名HR，那就一起来看一下啦：1、查找重复身份证号码在Excel表格上方输入【=IF(COUNTIF(A:A,A1&"*")>1,"重复","")】，其中“A1”可以自定义修改。
说明：COUNTIF函数在计算文本型数字时，会默认按数值型进行处理，但Excel中的数字精度只有15位，但身份证号码是18位，因此会把前15位相同的身份证全部识别为相同的内容。
在第二参数后加上一个星号 &"*"，就是告诉Excel，要查找包含A1单元格内容的文本，通过这样变通的手段，COUTNIF函数就会乖乖听话啦。
2、出生年月来可计算年龄在Excel表格上方输入【=DATEDIF(A4,TODAY(),"y")】，然后按回车键，即可在其中“A4”可以自定义修改。
说明：DATEDIF函数用于计算两个日期之间的间隔。
=DATEDIF（开始日期,结束日期,指定要返回的类型)；第三参数Y表示年，M则表示月。
TODAY函数返回系统当前的日期。
DATEDIF函数是隐藏函数，输入时没有屏幕提示，需要手工录入。
3、根据身份证号计算出生年月在Excel表格上方输入【=--TEXT(MID(A2,7,8),"0!/00!/00")】，然后按回车键，即可在其中“A2”可以自定义修改。
说明：身份证号码中的第8位到15位是出生年月信息。
先用MID函数从A2单元格的第7位开始，提取出8个字符，即可得到身份证号中的出生年月日信息。
通过excel公共函数计算二代公民身份证号码末位校验码
通过excel公共函数计算二代公民身份证号码末位校验码/验证码（附公式可直接使用）闲来无事，用EXCEL写了个公式（本文最后），可以根据身份证号码前17位计算第18位校验码的，经过验证，非常有效。
使用这个公式可以自己随意编造可以通过验证的身份证号码（感觉在为办证的做贡献。
）18位身份证号码的结构：现在的二代身份证由18位数字组成1、前6位是行政区规划代码，第一位是区位号（如西北、西南），第二位为省份编号，第三、四位是城市编号，第五、六位是县区编号，01-19代表市辖区，20以后代表郊县、县级市等辖区。
具体的行政区域规划码可以可以在国家统计局网站查询：/tjbz/xzqhdm/t20021125_46781.htm2、第7-14位是出生年月日，格式是yyyymmdd；3、第15-17位是顺序码，即同一地区相同生日的人的顺序编码，随便写；4、第18位是根据GB 11643-1999和IOS 7064计算出的校验码，计算方式比较复杂，文章中的公式就是用来计算这个校验码的！下图是完整的身份证效验码计算方式：但是因为上图中的公式太多，不方便直接使用，所以我把上图中所有的公式整合到了一个单元格中，就是下面的超级公式~！现在，打开excel，先将A1单元格属性设置为文本，再把你准备好的1-17位身份证号码填入A1单元格，然后把公式拷到随便哪个单元格中，完整的身份证号码就计算出来了！还可以用下拉的方式同时计算多个号码，很省事吧！公式如下：=IF((LEN(A1))=17,(A1&IF(VALUE(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,(MI D(A1,11,1))*7,(MID(A1,12,1))*9,(MID(A1,13,1))*10,(MID(A1,14,1))*5,(M ID(A1,15,1))*8,(MID(A1,16,1))*4,(MID(A1,17,1))*2)),11))=0,1,(IF(VALU E(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,0,(IF(VALUE(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))= 2,"X",12-VALUE(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)))))))),"输入长度不等于17位！")。
Excel数据中身份证号码如何发现重复？条件统计函数帮你忙
Excel数据中身份证号码如何发现重复？条件统计函数帮你忙
今天有一个朋友问我，“我在工作中会遇到大批量的身份证号码，想对重复的身份证号码进行查找出来有没有什么办法？好像用重复值突显没办法办到，一个个找也找不出来啊!怎么办？”
今天我就跟各位朋友分享下在大数据时代，碰到Excel数据中大批量的身份证号码如何才能发现有无重复数据的操作小技巧。
希望今天的条件统计函数的分享对各位朋友工作中有所帮助。
我们要查找C列的身份证号码，在D列显示“重复”输入函数
在D2单元格箭头处双击填充，马上可以看到标记“重复”字样出现“重复”说明身份证号码有重复
这个条件统计函数可以帮你发现是否亦身份证号码重复IF(COUNTIF(C:C,c2&"*")>1,"重复"，"")
希望今天的身份证号码如何发现有无重复分享，对各位朋友在工作张有所帮助。
更多关于Office-Excel操作小技巧可加入我们群一起交流分享。
Excel校验身份证号码的自定义函数
校验居民身份证号码函数 在日常工作中，我们经常用电子表格输入身份证号码，往往出现输入错误，校对麻烦。
由于身份证中第十八位数字的计算方法较为繁琐，下面提供了一个自定义函数，很方便。
=jysfz(身份证号码 字符型)示例：340524************←号码#NAME?=jysfz(A7)#NAME?=jysfz("340524************") 340524************#NAME? 340524************#NAME?340524************#NAME?340524************#NAME? 340524************#NAME? 340524************#NAME? 3405241952060122188#NAME?34052419710702233#NAME? 3405241964100622#NAME? 340524************#NAME? 34052419641006225想#NAME?#NAME?这样也有错！#NAME? 身份证的号码是按照国家的标准编制的，由18位组成：前六位为行政区划代码，第七至第十四位为出生日期码，第15至17位为顺序码，第15、16位为乡镇代码（一个乡镇可能有多个代码），第17位代表性别（奇数为男，偶数为女），第18位为校验码。
作为尾号的校验码，是由号码编制单位按统一的公式计算出来的，如果某人的尾号是0-9，都不会出现X，但如果尾号是10，那么就得用X来代替，因为如果用10做尾号，那么此人的身份证就变成了19位，而19位的号码违反了国家标准，并且我国的计算机应用系统也不承认19位的身份证号码。
Ⅹ是罗马数字的10，用Ⅹ来代替10，可以保证公民的身份证符合国家标准。
但是我国的居民身份证在升位后，一些人的尾号变成了X，这部分人在工作生活中，例如去银行存取钱、去汽车公司租赁汽车或者报名参加考试等等过程中，往往不被检验者理解，认为是假身份证，这样的误会给很多人的生活带来不便。
身份证正确检验及验证公式
身份证正确检验及验证公式验证公式一=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函数判断。
Excel函数判断身份证号是否重复
Excel函数判断身份证号是否重复有许多方法可以过滤重复值，包括函数和条件格式。
今天主要讲几种通过函数获取重复值的方法来表达不同需求的重复值，如下:1、判断是否重复2、判断一个值在一列中是第几次出现3、汇总每个值出现的序号题目进入正文：1、判断是否重复=COUNTIF($C$2:$C$13,C2)使用条件计数，您可以很容易地获得一个值在该列中出现的次数。
需要注意的是，计数范围要绝对参考，否则公式下拉结果会出错。
2、判断一个值在一列中是第几次出现=COUNTIF($C$2:C2,C2)第几次出现的问题，其实是利用引用区域来计算次数，原理如图范围的初始位置固定，结束位置随着公式下拉而变化，当范围为$N$2:N2时，范围中只有1个A，结果也就返回1；当范围为$N$2:N6时，范围中有2个A，结果也就返回2；也就得到了我们看到的当前值是第几次出现这种样式。
原理3、汇总每个值出现的序号=TEXTJOIN("、",TRUE,FILTER($A$2:$A$13,$C$2:$C$13=C2))汇总出现的序号，这必然要涉及数组函数，万能的filter来啦，条件同上，得到符合条件的结果，再用TEXTJOIN将结果合并为一个放在一个单元格内，就得到了我们想要的。
=TEXTJOIN("、",TRUE,FILTER($A$2:$A$13,$C$2:$C$13=C2))当然我们也可以给他嵌套一个if让没有重复值的单元格为空，方便数据的理解使用。
总结一下：1、判断是否重复=COUNTIF($C$2:$C$13,C2)2、判断一个值在一列中是第几次出现=COUNTIF($C$2:C2,C2)3、汇总每个值出现的序号=TEXTJOIN("、",TRUE,FILTER($A$2:$A$13,$C$2:$C$13=C2))完结，撒花✿✿ヽ(°▽°)ノ✿。