身份证号换算公式

合集下载

身份证号自动生成年龄、出生年月、性别公式

身份证号自动生成年龄、出生年月、性别公式=MID(E4,8,4)&"/"&MID(E4,12,2)&"/"&MID(E4,14,2)(2011-07-28 17:54:56)转载▼身份证号自动生成年龄、出生年月、性别公式:=IF(LEN(C2)=15,"19"&MID(C2,7,2)&"-"&MID(C2,9,2)&"-"&MID(C2,11,2),MID (C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2))=DATE(MID(I4,IF(LEN(I4)=18,7,7),IF(LEN(I4)=18,4,2)),MID(I4,IF(LEN(I4 )=18,11,9),IF(LEN(I4)=18,2,2)),MID(I4,IF(LEN(I4)=18,13,11),IF(LEN(I4 )=18,2,2)))1、生成出生年月日公式一（18位号和15位号都可自动生成 19**-*-*）：=DATE(MID(I4,IF(LEN(I4)=18,7,7),IF(LEN(I4)=18,4,2)),MID(I4,IF(LEN(I4)=18, 11,9),IF(LEN(I4)=18,2,2)),MID(I4,IF(LEN(I4)=18,13,11),IF(LEN(I4)=18,2,2)))生成出生年月日公式二（18位或15位号生成19**-**-**）：=IF(LEN(C2)=15,"19"&MID(C2,7,2)&"-"&MID(C2,9,2)&"-"&MID(C2,11,2),MID (C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2))说明：自动生成出生年月就把以上公式复制粘贴到需自动生成“出生年月日”的那一栏第一格，然后把公式一中的所有“I4”或公式二中的所有“C2”改成身份证号码单元格的栏代码如A、B、C、D……和行数如1、2、3、4……。

18位身份证号码转换成出生日期的函数公式

18位身份证号码转换成出生日期的函数公式：如果E2中是身份证,在F2中求出出生日期，F2=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2)) 自动录入男女：=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))) ,2)= 0,"女","男") 根据身份证号快速录入男女性别“记住”15/18位都可以的公式：转换出生日期：=IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#-00-00"),"19"&TE XT(MID(e2,7,6),"#-00-00"))自动录入男女：=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男"))计算年龄（新旧身份证号都可以）：=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",107-MIDB(E2 ,9,2 ),107-MIDB(E2,7,2)))WPS表格提取身份证详细信息前些天领导要求统计所有员工的性别、出生日期、年龄等信息，并且要得很急。

而我们单位员工人数众多。

短时间内统计相关信息并且输入计算机几乎是不太可能的。

幸好在以前的一份金山表格中我们曾经统计有所有员工的身份证号码，而身份证中正有我们所需要的性别、出生日期、年龄等信息的。

所以。

干脆，还是直接在金山表格中从身份证号码提取相关的信息吧。

身份证号放在A2单元格以下的青岛DNA检测单位区域。

我们需要从身份证号码中提取性别、出生日期、年龄等相关信息。

由于现在使用的身份证有15位和18位两种。

身份证生成性别、年龄等公式

一、算性别=IF((LEN((IF(LEN(C4)<18,MID(C4,15,1),MID(C4,17,1)))/2)=1),"女","男")二、出生年月日公式一（18位或15位号生成年月日（19**-*-*）=DATE(MID(J3,IF(LEN(J3)=18,7,7),IF(LEN(J3)=18,4,2)),MID(J3, IF(LEN(J3)=18,11,9),IF(LEN(J3)=18,2,2)),MID(J3,IF(LEN(J3)=1 8,13,11),IF(LEN(J3)=18,2,2)))出生年月日公式二（18位或15位号生成19**-**-**）=IF(LEN(C2)=15,"19"&MID(C2,7,2)&"-"&MID(C2,9,2)&"-"&MID(C2, 11,2),MID(C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2))利用身份证号码生成年龄：=IF(LEN(D4)=18,YEAR(TODAY())-MID(D4,7,4),YEAR(TODAY())-MID( D4,7,2)-1900)三、利用出生年月日生成年龄：=YEAR(TODAY())-YEAR(F4)检证身份证错误：=IF(I5="","",IF(LEN(I5)=18,IF(OR(MONTH(DATE(1*(MID(I5,7,4)) ,1*(MID(I5,11,2)),1*(MID(I5,13,2))))<>1*(MID(I5,11,2)),DAY( DATE(1*(MID(I5,7,4)),1*(MID(I5,11,2)),1*(MID(I5,13,2))))<>1*(MID(I5,13,2)))," 月日错误",IF(MID(I5,18,1)=MID("10X98765432",MOD(MID(I5,1,1)*7+MID(I 5,2,1)*9+MID(I5,3,1)*10+MID(I5,4,1)*5+MID(I5,5,1)*8+MID(I5, 6,1)*4+MID(I5,7,1)*2+MID(I5,8,1)+MID(I5,9,1)*6+MID(I5,10,1) *3+MID(I5,11,1)*7+MID(I5,12,1)*9+MID(I5,13,1)*10+MID(I5,14, 1)*5+MID(I5,15,1)*8+MID(I5,16,1)*4+MID(I5,17,1)*2+11,11)+1, 1),""," 校验码错误")),IF(LEN(I5)=15,IF(OR(MONTH(DATE(1*(MID(I5,7,2))+1900,1*( MID(I5,9,2)),1*(MID(I5,11,2))))<>1*(MID(I5,9,2)),DAY(DATE(1 *(MID(I5,7,2))+1900,1*(MID(I5,9,2)),1*(MID(I5,11,2))))<>1*( MID(I5,11,2)))," 月日错误 ","")," 位数不对 ")))取18位号=LEFT(J5,18)如何筛选重复项2007年版开始——条件格式——突出显示单元格规则——重复项为了保护您的眼睛，请对电脑作如下设置吧-- 桌面->右键->属性->外观->高级->项目选择(窗口)、颜色1(L)选择(其它)将色调改为:85。

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

身份证号码换算年龄、性别、出生日期的公式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)))”只要把输入身份证号的单元格格式设为“文本”就是最关键的，准确。

身份证号码提取公式

合并两列文字：=CONCATENATE(h2,i2)知道生日生成身份证=440981&TEXT(f2,"emmdd")&INT(10+RAND()*99)&IF(e2="F",2,1)&INT(RAN D()*9+1)中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为：１５位：６位数字常住户口所在县市的行政区划代码，６位数字出生日期代码，３位数字顺序码。

１８位：６位数字常住户口所在县市的行政区划代码，８位数字出生日期代码，３位数字顺序码和１位检验码。

其中３位数字顺序码，是为同一地址码的同年同月同日出生人员编制的顺序号，偶数的为女性，奇数的为男性。

１、提取籍贯地区的行政区划代码（A2为身份证号，下同）１５与１８位通用：=LEFT(A2,6)如果有一个编码和省份地区的对照表，可以用VLOOKUP函数来提取地区信息。

２、提取出生日期信息=TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"#-##-##")１５位：=--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。

身份证号码等公式

身份证号码导出出生年月1=TEXT(MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2),"YYYY-MM-DD")=MID(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2)提取出生年月或任意数字段 =mid（A5,7,8）年龄2=DATEDIF(TEXT(MID(A1,7,INT(LEN(A1)/2-1)),"#-00-00"),TODAY(),"Y")身份证姓名性别=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),"女","男")excel设置单元格格式后，必须双击才生效的解决办法选中整列，“数据”-“分列”-“下一步”-“下一步”-“常规”-“完成”。

如果该列有合并单元格，则选中需变换格式的列。

一次只能转换一列。

成绩排名：=RANK(A1,A:A)=COUNTIF($E$7:$E$28,">"&E7)+1比较异同=if(a2=b2,"","不同")＝COUNTIF(A:A,b1)>=1两列数据比较异同=VLOOKUP(B:B,A:A,1,) 无序校验1=VLOOKUP(A:A,B:B,1,0) 无序校验2参加工作时间（出生年月入201005）计算工龄（年龄）：1、=REPLACE(F1,5,0,"-") 加入,"-") 2、复制粘贴为日期格式 3、=YEAR(NOW())-YEAR(A4)计算工龄一列数据是否重复数据→删除重复项1、排序2、然后把旁边一列当辅助列，第一空格打进1，第二空格打入公式：=if($a3=$a2，2，1)，然后按下回车键3、拖动2中的公式应用到下面剩余的单元格中，重复出现的词旁边单元格的值变为2，否则就是1：检验身份证号码=IF(IF(LEN(E5)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(E5,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(E5),IF(LEN(E5)=15,ISNUMBER(--TEXT(19&MID(E5,7,6),"#-00-00")))),"正确","身份证号错误")=IF(MID(E5,11,2)="12",MID(E5,7,4)+61&"年01"&"月",MID(E5,7,4)+60&"年"&MID(E5,11,2)+1&"月")=IF(LEN(E5)=15,IF(MOD(MID(E5,15,1),2)=1,"男","女"),IF(MOD(MID(E5,17,1),2)=1,"男","女"))姓名加空格=IF(LEN(B2)=2,LEFT(B2,1)&" "&RIGHT(B2,1),B2)数值比对=IF(COUNTIF($e:$e,$A2)=0,"",INDEX($e:$f,MATCH($A2,$e:$e,),COLUMN(A2)) )进一法使用的函数是roundup去一法使用的函数是ROUNDDOWN四舍五入使用的函数是round保留两位小数，你可以在A2中输入 =roundup(a1,2）=vlookup(b2,Sheet1!b:c,2,)自动填充=VLOOKUP(A3,Sheet1!A:C,3,0) 3表示第三列一组成绩前N名的平均数ROW(1:5)前5名=AVERAGE(LARGE(F4:F19,ROW(1:5))) 数组公式输入完成shift+ctrl+enter。

身份证计算公式

地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码。

生日期码表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。

顺序码表示同一地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号。

顺序码的奇数分给男性，偶数分给女性。

校验码是根据前面十七位数字码，按照ISO7064:1983.MOD11-2校验码计算出来的检验码。

下面举例说明该计算方法。

某男性公民身份号码本体码为34052419800101001，首先按照公式⑴计算：∑(ai×Wi)(mod 11) (1)公式(1)中：i----表示号码字符从由至左包括校验码在内的位置序号；ai----表示第i位置上的号码字符值；Wi----示第i位置上的加权因子，其数值依据公式Wi=2（n-1）(mod11)计算得出。

(2的n-1次方;n初始值是1)ia iw iai×Wi 21 36 0 25 16 16 2 9 48 0 0 9 0 5 0 0 2 a1根据公式(1)进行计算：∑(ai×Wi) =（21+36+0+25+16+16+2+9+48++0+0+9+0+5+0+0+2) = 189189 ÷ 11 = 17 + 2/11∑(ai×Wi)(mod 11) = 2然后根据计算的结果，从下面的表中查出相应的校验码，其中X表示计算结果为10：校验位余数∑(ai×WI)(mod 11) 0 1 2 3 4 5 6 7 8 9 10校验码字符值ai 1 0 X 9 8 7 6 5 4 3 2根据上表，查出计算结果为2的校验码为所以该人员的公民身份号码应该为 340524************。

18171615141312111098765432134052419800101001a7910584216379105842101234567891010X9876543200101001X。

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

身份证号换算出生年月公式（A1表示所在单元格坐标）
公式：
=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-" &MID(A1,11,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-" &MID(A1,13,2))
出生年月换算年龄公式（B1表示所在单元格坐标）公式：
=datedif(B1,today(),"y")
身份证号换算年龄公式（D2表示身份证所在单元格坐标）
公式：
=IF(LEN(D2)=15,YEAR(NOW())-1900-VALUE(MID(D2,7 ,2)),IF(LEN(D2)=18,YEAR(NOW())-VALUE(MID(D2,7, 4))
,"身份证错"))
在所在的单元格中复制这个公式，把“D2”修改成身份证号所在的单元格就可以。

去除身份证前非打印字符（在身份证前有非打印字符下实用）：
=CLEAN(SUBSTITUTE(D16,CHAR(127),CHAR(7)))
3、根据身份证号计算性别
=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,1 7,1)),2)=1,"男","女")
第四，根据身份证号码自动提取年龄的方法
=2012-MID(a1,7,4) 2014.10.1日
性别计祘2015年
=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,"男","女"),IF(MOD(MID(B2,17,1),2)=1,"男","女"))。