第二代 身份证校验工具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表格中的身份证

给你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公共函数计算二代公民身份证号码末位校验码/验证码(附公式可直接使用)闲来无事,用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数据中身份证号码如何发现重复?条件统计函数帮你忙
今天有一个朋友问我,“我在工作中会遇到大批量的身份证号码,想对重复的身份证号码进行查找出来有没有什么办法?好像用重复值突显没办法办到,一个个找也找不出来啊!怎么办?”
今天我就跟各位朋友分享下在大数据时代,碰到Excel数据中大批量的身份证号码如何才能发现有无重复数据的操作小技巧。

希望今天的条件统计函数的分享对各位朋友工作中有所帮助。

我们要查找C列的身份证号码,在D列显示“重复”输入函数
在D2单元格箭头处双击填充,马上可以看到标记“重复”字样出现“重复”说明身份证号码有重复
这个条件统计函数可以帮你发现是否亦身份证号码重复IF(COUNTIF(C:C,c2&"*")>1,"重复","")
希望今天的身份证号码如何发现有无重复分享,对各位朋友在工作张有所帮助。

更多关于Office-Excel操作小技巧可加入我们群一起交流分享。

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函数判断身份证号是否重复

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))完结,撒花✿✿ヽ(°▽°)ノ✿。

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

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

验证公式一=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提取身份证出生日期、性别、检验身份证号码正确性
中国居民身份证号码是一组特征组合码,原为15位,现升级为18位,其编码规则为:
15位:6位数字常住户口所在县市的行政区划代码,6位数字出生日期代码,3位数字顺序码。
18位:6位数字常住户口所在县市的行政区划代码,8位数字出生日期代码,3位数字顺序码和1位检验码。
假定身份证号码在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的身份证效验程序

excel的身份证效验程序

打开EXCEL,把宏的安全级别设置为“低”,以后再改回来。

然后打开VB编辑器,新建一模块,加入以下代码Function 身份证(n As Object)wi = 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")d = Array("29", "31", "28", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31", "30") s = EmptyIf Len(n) = 15 Then身份证 = "请填18位身份证号"Exit FunctionElseIf Len(n) = Empty Then身份证 = EemptyExit FunctionEnd If年份 = Val(Mid(n, 7, 4))月份 = Val(Mid(n, 11, 2))月份2 = 月份If 月份2 = 2 Thentt = Val(Mid(年份, 3, 2))If tt <> 0 Thentt = 年份 Mod 4If tt = 0 Then 月份2 = 0Elsett = 年份 Mod 400If tt = 0 Then 月份2 = 0End IfEnd If日期 = Val(Mid(n, 13, 2))If 日期 > Val(d(月份2)) Or 日期 = 0 Then身份证 = "日历无此日"Exit FunctionEnd IfIf 月份 > 12 Or 月份 = 0 Then身份证 = "日历无此月份"Exit FunctionEnd IfIf 年份 > Year(Date) Then身份证 = "还没生出来"Exit FunctionElseIf 年份 < Year(Date) - 100 Then身份证 = "百岁老人"Exit FunctionEnd IfIf Len(n) = 17 ThenFor h = 0 To 16r = Mid(n, h + 1, 1)s = s + r * wi(h)Next ht = s Mod 11身份证 = "校验码未填 " & y(t)ElseIf Len(n) = 18 ThenFor h = 0 To 16r = Mid(n, h + 1, 1)s = s + r * wi(h)Next ht = s Mod 11t = y(t)r = Mid(n, 18, 1)If t <> r Then身份证 = "False"Elsett = Val(Mid(n, 15, 3)) Mod 2If tt = 0 Then身份证 = "女"Else身份证 = "男"End IfEnd IfElseMsgBox "身份证位数不正确"End IfEnd Function然后返回EXCLE,把身份证号填入单格,再填入公式如:=身份证(A3。

Excel中常见与身份证有关的公式

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),"正确","校验码错误"),"此身份证号码位数不对")。

用Excel计算身份证第18位(校验码)

用Excel计算身份证第18位(校验码)

籍贯
411322 19910621 061 6
出生日期 性别
验证码
1、将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的 系数分别为:
ห้องสมุดไป่ตู้
7-9-10-5-8-4-2-1-6-3-7-9-10-5-8-4-2
2、将这17位数字和系数相乘的结果相加。
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。
首先我们得出前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。我们要看看这个身份证 是不是合法的身份证。
身份证校验码计算器身份证校验码计算身份证校验码身份证号校验码身份证校验码错误循环冗余校验码计算crc校验码计算crc校验码计算器

02身份证检测公式:年龄、出生日期、性别、重号、真假识别(只填白色部分,其他自动校验)

02身份证检测公式:年龄、出生日期、性别、重号、真假识别(只填白色部分,其他自动校验)

制表日期:



说明: 1.身故受益人如为“法定”指被保险人的法定第一顺序继承人。 2.本表中职业类别依据《中国人保寿险有限公司》职业分类表确定。 第8页,共8页
制表日期:



-
72 #VALUE! 说明: 1.身故受益人如为“法定”指被保险人的法定第一顺序继承人。 2.本表中职业类别依据《中国人保寿险有限公司》职业分类表确定。 第3页,共8页
制表日期:



73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
36 4 7 6 6 11 11 7 4 56 3 3 3 3 3 3 3
1 1 1 2 2 1 1 1 1 1 2 2 5 5 5 5 5 -
2 2 2 2 1 1 2 2
一致 一致 一致 一致 一致 一致 一致 一致 一致 一致 一致 一致 一致 一致 一致 一致 一致
假身份证 真身份证 真身份证 真身份证 真身份证 真身份证 真身份证 真身份证 真身份证 假身份证 真身份证 真身份证 假身份证 假身份证 假身份证 假身份证 假身份证
出生日期
年龄
证件重复 校验
姓名重复 身份证判断 校验
提示
身份证验证位信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
熊跃伍 高书华 高书华 马金昆 龚晓宏 袁斌 马金昆 熊跃伍
女 男 女 女 女 女 女 女 男 男 男 男 男 男 男 男 男
19820424 20140517 20110108 20120330 20120330 20070219 20070224 20110327 20140326 19620810 20150131 20150131 20150131 20150131 20150131 20150131 20150131

身份证号码校验也可以利用excel函数公式完成

身份证号码校验也可以利用excel函数公式完成

身份证号码校验也可以利用excel函数公式完成身份证号码校验也可以利用excel函数公式完成,无需vba编程。

一、18位身份证号码校验函数公式:=IF(LEN(B2)=18,IF(MID("10X98765432",MOD(SUM(MID(LEF T(B2,17),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(B2,1),"通过","校验未通过"),"")二、15位身份证号码升18位函数公式:=IF(LEN(B2)=15,REPLACE(B2,7,,19)&MID("10X98765432",M OD(SUM(MID(REPLACE(B2,7,,19),ROW(INDIRECT("1:17")),1)*2^( 18-ROW(INDIRECT("1:17")))),11)+1,1),"")三、同时完成15位升位或18位校验的函数公式:=IF(LEN(B2)=18,IF(MID("10X98765432",MOD(SUM(MID(LEF T(B2,17),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(B2,1),"通过","校验未通过"),IF(LEN(B2)=15,REPLACE(B2,7,,19)&MID("10X98765432",MOD( SUM(MID(REPLACE(B2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),""))以上公式中B2为身份证号码所在单元格。

:EXCEL身份证核对公式

: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检查输入的身份证号码对错?

如何利用Excel检查输入的身份证号码对错?
根据身份证校验位的计算规则,对校验位进行计算判断。

身份证的校验规则说起来比较繁琐,这里就不介绍了,按照下述方法做个简单的设定和公式,只需2步就可完成,快来试试吧。

1、打开excel后,先在一个工资表中输入下列参数数据(不可输错哦),并将这17个数据定义为“系数”(定义名称的操作方法:选中这17个数据,在名称栏,即下图红圈处输入“系数”,回车确定)。

.
2、在另一个工作表中进行身份证号码合法性校验,假设在A2单元格输入身份证号码,在B2单元格输入公式:=IF(LEN($A2)=18,IF(MID("10X98765432",MOD(SUMPRODUCT(V ALUE(MID($A2,ROW($1:$17),1)),系数),11)+1,1)=RIGHT($A2,1),"正确","校验位错"),"长度错误")
向下复制公式,既可对A列输入的身份证号码进行长度和校验位的判断验证。

经过测试,妥妥的。

  1. 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
相关文档
最新文档