第二代身份证校验工具excel公式
excel身份证有效期计算公式
excel身份证有效期计算公式首先要知道，我国对身份证有效期的相关规定：十六周岁至二十五周岁的，发给有效期十年的居民身份证;二十六周岁至四十五周岁的，发给有效期二十年的居民身份证;四十六周岁以上的，发给长期有效的居民身份证；未满十六周岁的公民，自愿申请领取居民身份证的，发给有效期五年的居民身份证。
根据以上规定，用Excel公式实现身份证有效期日的计算在D2单元格输入公式：“=IFERROR(EDATE(C2,LOOKUP(DATEDIF(DATE(MID( B2,7,4),MID(B2,11,2),MID(B2,13,2)),C2,"y"),{0,16,26,46}, {5,10,20,"长期"})*12)-1,"长期")”，按Enter键完成计算，即可计算第一位的身份证到期日，公式向下填充，即可计算其他人的身份证到期日，如下图：完整公式：=IFERROR(EDATE(C2,LOOKUP(DATEDIF(DATE(MI D(B2,7,4),MID(B2,11,2),MID(B2,13,2)),C2,"y"),{0,16,26,4 6},{5,10,20,"长期"})*12)-1,"长期")公式解析DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2)：从身份证号码中提取出生日期。
DATEDIF(DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2) ),C2,"y")：计算办理身份证日期时，已满多少周岁。
LOOKUP(DATEDIF(DATE(MID(B2,7,4),MID(B2,11,2),MI D(B2,13,2)),C2,"y"),{0,16,26,46},{5,10,20,"长期"})：根据办理身份证日期时已满多少周岁，返回身份证的有效有效期为多少年。
验证身份证号是否正确的公式、通过身份证号提取出生年月、性别、年龄等的公式汇总
身份证号验证：=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() 取单元格字符长度。
18位身份证号码验证公式及标准
18位身份证号码验证工具
计算依据及说明
根据《中华人民共和国国家标准》（GB 11643-1999）规定：
公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。
排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。
地址码（1-6位）：表示编码对象常住户口所在县(市、旗、区)的行政区划代码
生日码（7-14位）：表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。
例如：1981年05月11日就用19810511表示。
顺序码（15-17位）：为同一地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号。
其中奇数分给男性，偶数分给女性。
校验码（18位）：是根据前面十七位数字码，按照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。
以上算法来源于网络，具体规范见国家相关标准。
身份证校验码计算公式
身份证校验码计算公式校验码是识别一个身份证号码是否真实存在的重要的依据。
那我们要如何通过校验码来判断一个身份证号码是否真实的呢？校验码如何判断身份证号码真伪呢？我们来看看具体计划过程。
第一步：将身份证号码的第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能够验证身份证号码对错的公式来了，还不快点收藏！当我们在Excel中输入大量身份证号码的时候，为了防止输入错误，就要与身份证上面的数字进行反复核实，很耗时不说，难免还是会有看花眼输错了的情况发生。
今天就跟大家分享一个可以判断身份证号码对错的公式，提高一些工作效率，减少失误。
从上图的编辑栏中可以看到公式（默认的全部都是二代18位的身份证号码）=IF(B2='','',IF(MID('10X98765432',MOD(SUMPRODUCT(MID (B2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1,1)= RIGHT(B2),'正确','错误'))那么这个公式表达的是什么意思呢？其实呀，我们身份证号码的编制，是遵循了一定规则的，并不是按顺序随便取的。
从倒数第二位可以判断性别一样，这就是其中一个编制规则。
只是今天我们要说的这个规则，不像判断性别那样普及，不是每个人都知道的。
二代身份证是由18位数字组成的，它的编制有一个规律：将前17位数字进行一种特定的运算，这个运算的结果必定等于第18位数字。
那到底是什么样的特定运算呢？1．身份证号码有一组固定的17位系数，分别对应身份证号码的前17位数字。
这组固定的系数是：7、9、10、5、8、4、2、1、6、3、7、9、10、5、8、4、2。
（别问我是怎么知道的，我网上查的。
也别问为什么偏偏是这17位，而不是其他的，因为就是这样定下的，是固定的，详情网上可以查到。
）2．将该系数分别与身份证号码的前17位数字相乘，再把17个相乘的结果相加。
3．用相加的结果除以11，看余数是多少，再给它加上1，余数+1。
4．身份证号码还有一组固定的11位校验码：1、0、X、9、8、7、6、5、4、3、2。
（与上面所说的系数一样，也是固定的。
通过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计算身份证第18位(校验码)
十年后，我连log是什么都不知道，但还是可以活得好好的！
是这样吗？
JUNE.作 . 品
身份证中包含着我们诸多的信息：比如家庭住址、出生年月、性别等。 可是大家见没见过有同学的身份证数字里有“X”的吗？它是什么含义呢？
籍贯
411322 19910621 061 6
出生日期 性别
首先我们得出前17位的乘积和是259，然后用259除以11得出的结果是13+6， 也就是说其余数是6。最后通过对应规则就可以知道余数6对应的数字是6。所 以，可以判定这是一个合格的身份证号码
我们来现场演示一下吧！
李刚， 拿你的身份证号来。。。
MID("10X98765432",MOD(SUM(MOD(2^(18-ROW($1:$17)),11)*MID(A1,ROW($1:$17),1)),11)+1,1)
你们这样的人是不是想找一个像EXCEL一样的基友——想隐藏就隐藏,想筛选就筛选,想 删除就删除,一个不高兴,嘿,我还就不保存了。
5、通过上面得知如果余数是2，就会在身份证的第18位数字上出现罗马数字的 Ⅹ。如果余数是10，身份证的最后一位号码就是2。 例如：某下图的身份证号码是411322199106210616。我们要看看这个身份证 是不是合法的身份证。
验证码
1、将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的 系数分别为：
7－9－10－5－8－4－2－1－6－3－7－9－的结果相加。
Heavy metal
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。
身份证正确检验及验证公式
验证公式一=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：身份证号有效性检查
A：位数检查
1、选中A1:A10
2、“数据/有效性/设置/允许/自定义/公式/=OR(LEN(A1)=15,LEN(A1)=18)/出错警告/样式/停止/标题/错误/错误信息/号码位数错误，请重新输入！/确定”。
3、在选区输入非15、18位数值时即出现错误提示对话框并发出警告声响，只有点“取消”，再输入正确数值方可。
B：用性别判别对与错：
1、如果以C列输入性别为准，在B列输入公式“=IF(IF(MOD(MID(A1,17,1),2)=0,"女","男")=C1,"第17位正确","第17位错")”，向下复制到需要处如B3，即可判别A列号码第17位数码之对错，如黄色区域所示。
（提取性别=IF(MOD(MID(A2,17,1),2)=0,"女","男")）
2、如果以A列输入号码为准，在D列输入公式“=IF(IF(MOD(MID(A1,17,1),2)=0,"女","男")=C1,"C列正确","C 列填错")”，向下复制到需要处如D3，即可判别C列填写之对与错，如绿色区域所示。
C：检查有无重复
H1（或者其他空列)公式=COUNTIF(G:G,G1&"*")
向下填充到底出现大于1的数就是有重复的。
Excel中常见与身份证有关的公式
Excel中常见与身份证有关的公式1.根据身份证号计算年龄=IF(D2<>"",DATEDIF(TEXT((LEN(D2)=15)*19&MID(D2,7,6+( LEN(D2)=18)*2),"#-00-00"),TODAY(),"y"),)注意：D2单元格为身份证号，且为文本格式。
2.根据身份证号计算出生年月日①计算出来的格式为××年××月××日=IF(LEN(D2)=15,"19"&MID(D2,7,2)&"年"&MID(D2,9,2)&"月"&MID(D2,11,2)&"日",MID(D2,7,4)&"年"&MID(D2,11,2)&"月"&MID(D2,13,2)&"日") 或者=IF(LEN(D2)=15,19,"")&TEXT(MID D2,7,8-(LEN(D2)=15)*2),"#年00月00日")②计算出来的格式为××年××月=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"年"&MID(A2,9,2)&"月",MID(A2,7,4)&"年"&MID(A2,11,2)&"月")③计算出来的格式为2011/1/1 =MID(B11,7,4)&"/"&MID(B11,11,2)&"/"&MID(B11,13,2) =DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2))3.根据身份证号计算性别①自动录入男女：=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))) ,2)=0,"女","男")②自动录入男女：=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男"))③=IF(MOD(IF(LEN(D2)=15,MID(D2,15,1),MID(D2,17,1)),2)=1 ,"男","女")4.身份证号全部改为18位（输出正确18位），输入数组公式(ctrl+shift+enter结尾）：=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",M OD(SUM(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRE CT("1:17")))),11)+1,1),A2)5.以18位身份证为准输出15位身份证号：=IF(LEN(G2)=15,G2,LEFT(REPLACE(G2,7,2,),15))6.验证身份证号（G2为输入18位身份证号，H2为输出正确18位身份证号）：=IF(LEN(A2)=18,IF(RIGHT(A2,1)=MID("10X98765432",MOD( SUMPRODUCT(MID(A2,ROW($A$1:$A$17),1)*{7;9;10;5;8;4;2;1;6; 3;7;9;10;5;8;4;2}),1 1)+1,1),"正确","校验码错误"),"此身份证号码位数不对")。
最新-第二代身份证号码提取出生年月日的公式-(很简单)
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),MID(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() 取单元格字符长度。
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，这部分人在工作生活中，例如去银行存取钱、去汽车公司租赁汽车或者报名参加考试等等过程中，往往不被检验者理解，认为是假身份证，这样的误会给很多人的生活带来不便。
身份证提取男女公式
在EXCEL中使用公式提取身份证号码男女性别可以使用
=IF(MOD(MID(A1;17;1);2);"男";"女")公式来实现。
其中IF表示判定条件，A1表示身份证号码所在的单元格。
身份证号码倒数第二位奇数表示南，偶数表示女。
具体的操作方法如下：
1、在电脑上打开EXCEL文件进入。
2、在进入到EXCEL文件以后输入需要的身份证号码。
3、此时在需要的单元格中输入=IF(MOD(MID(A1;17;1);2);"男";"女")。
4、函数输入完成以后按下回车键，可以看到起已经自动判定了男女性别。
5、如需要处理多个数据，可以点击该单元格公式，下拉套用计算公式。
6、则可以看到套用公式以后自动计算提取男女性别。
：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身份证计算性别年龄出生日期姓名重复身份证重复生肖等
LEN(C2)=15：检查C2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。
MID(C2,7,4)：从C2单元格中字符串的第7位开始提取四位数字，本例中表示提取15位身份证号码的第7、8、9、10位数字。
MID(C2,9,4)：从C2单元格中字符串的第9位开始提取四位数字，本例中表示提取18位身份证号码的第9、10、11、12位数字。
=DATEDIF(TEXT(RIGHT(19&MID(A1,7,LEN(A1)/2-1),8),"#-00-00"),NOW(),"Y")
已有身份证的情况下,公式应加合法性,有效性判断,所以公式改为:
出生日期:
=IF(A1="","",IF(AND(LEN(A1)<>15,LEN(A1)<>18),"错误",IF(ISERR(1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")),"错误",IF(OR(1*TEXT
身份证第18位（校验码）的计算方法
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。
替换哪两个数字？可以用公式：
=replace(A1,n,2,"s")