WPS表格提取身份证详细信息(2)
最新整理WPS表格提取身份证信息的方法详解

W P S表格提取身份证信息的方法详解身份证相关信息包括籍贯、性别、出生年月日、农历年、属相、生日、星座、出生至今年数、出生至今天数和判断证件有效性,那么怎样把这个信息都提取出来呢?想知道的朋友可以看看以下W P S操作技巧教程,或许对你有帮助!W P S表格提取身份证信息的方法一、原籍贯:(公式代码)=I F E R R O R(I F(L E N B(A2)=15,I F E R R O R(v l o o k u p(V A L U E(M I D(A2,1,6)),S h e e t4!$A:$B,2,0),V L O O K U P(V A L U E(M I D (A2,1,4)00),S h e e t4!$A:$B,2,0)),I F E R R O R(I F E R R O R( V L O O K U P(V A L U E(M I D(A2,1,6)),S h e e t3!$A:$E,5,0),V L O O K U P(V A L U E(M I D(A2,1,4)00),S h e e t3!$A:$E,5,0)),V L O O K U P(V A L U E(M I D(A2,1,2)0000),S h e e t3!$A:$E,5,0) )),证件无效)运算说明:此运算运用了i f e r r o r逻辑函数,主要判断后面的函数计算结果,如果经过后面所有函数的判断都无法得到结果的,就在此单元格显示证件无效。
(此函数在后面的单元格(除判断身份证有效单元格外)都用到此函数,同理,后面就不一一解释此函数的用法。
i f函数主要是判断a2单元格的字符数(l e n函数)是否满足15位,如果是15位的话,则从a2单元格里提取(m i d函数)前6位数字(从第一位提取,6个字符)在数据库中查找(v l o o k u p函数)满足此条件的原籍贯所在地,此单元中后面的v l o o k u p函数也是此用途。
如何在Excel表格中利用身份证号码提取个人信息

如何在Excel表格中利用身份证号码提取个人信息如何在Excel表格中利用身份证号码提取个人信息一、分析身份证号码其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是15位还是18位的身份证号码,其中都保存了相关的个人信息。
15位身份证号码:第7、8位为出生年份(两位数),第9、10位为出生月份,第11、12位代表出生日期,第15位代表性别,奇数为男,偶数为女。
18位身份证号码:第7、8、9、10位为出生年份(四位数),第11、第12位为出生月份,第13、14位代表出生日期,第17位代表性别,奇数为男,偶数为女。
中国教育网盘论坛例如,某员工的身份证号码(15位)是511025*********,那么表示1998年7月6日出生,性别为男。
如果能想办法从这些身份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错,核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率。
"二、提取个人信息这里,我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。
假定我们的表格A列输入姓名;B列输入性别;C列输入身份证号码,并且员工的身份证号码信息已输入完毕,出生年月信息填写在D列。
1. 提取出生年月信息由于上交报表时只需要填写出生年月,不需要填写出生日期,因此这里我们只需要关心身份证号码的相应部位即可,即显示为“9807”这样的信息。
在D2单元格中输入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”,其中: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位数字。
如何从在excel中身份证号码里提取需要的信息

如何从在excel中身份证号码里提取需要的信息一、在excel中如何从身份证号码里提取出出生年月日及性别等假如,A1是姓名,B1是身份证号码,C1是出生年月可以用以下公式求出=IF(LEN(B2)=15,"19"&MID(B2,7,2)&"年"&MID(B2,9,2)&"月"&MID(B2,11,2)&"日",MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日")在C2设置单元格格式为“日期”即可在C2中正确提取出其出生年月2EXCEL从身份证号码求出生年月日、性别及年龄公式2007-11-26 16:40EXCEL中用身份证号码求出生年月日及年龄公式2007年11月21日星期三 17:17一、分析身份证号码其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是15位还是18位的身份证号码,其中都保存了相关的个人信息。
15位身份证号码:第7、8位为出生年份(两位数),第9、10位为出生月份,第11、12位代表出生日期,第15位代表性别,奇数为男,偶数为女。
18位身份证号码:第7、8、9、10位为出生年份(四位数),第11、第12位为出生月份,第13、14位代表出生日期,第17位代表性别,奇数为男,偶数为女。
例如,某员工的身份证号码(15位)是320521*********,那么表示1972年8月7日出生,性别为女。
如果能想办法从这些身份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错,核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率。
二、如何从在excel中身份证号码里提取个人信息这里,我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。
Excel中利用身份证号码提取个人信息

Excel中利用身份证号码提取个人信息在日常工作中,有时候需要制作员工的档案,需要添加每位员工的姓名、性别、身份证号码、出生年月、籍贯、毕业学校、职称等相关信息,需要对身份证号码、性别、出生年月、性别等资料进行核对,实际在Excel中只要正确输入身份证号码,再利用相关的技术即可从身份证号码中提取正确的性别、出生年月、年龄等信息,并且不需要核对!一、分析身份证号码其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是15位还是18位的身份证号码,其中都保存了相关的个人信息。
15位身份证号码:第7、8位为出生年份(两位数),第9、10位为出生月份,第11、12位代表出生日期,第15位代表性别,奇数为男,偶数为女。
18位身份证号码:第7、8、9、10位为出生年份(四位数),第11、第12位为出生月份,第13、14位代表出生日期,第17位代表性别,奇数为男,偶数为女。
例如,某员工的身份证号码(15位)是320521*********,那么表示1972年8月7日出生,性别为女。
如果能想办法从这些身份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错,核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率。
二、提取个人信息这里,我们需要使用IF、LEN、MOD。
MID、DATE等函数从身份证号码中提取个人信息。
如图1所示,其中员工的身份证号码信息已输入完毕(C列),出生年月信息填写在D列,性别信息填写在B 列,年龄填写在E列。
图1由于报表中各位员工的序号编排是按照上级核定的编制进行的,因此不可能按照男、女固定的顺序进行编排,如果一个一个手工输入的话,既麻烦又容易出错。
例如性别信息统一在B列填写,可以在B2单元格中输入公式"=IF(MOD((I F(LEN(C2)=15,RIGHT(C2),MID(C2,17,1))),2)=0,"女","男")",如图2所示。
WPS表格从身份证号提取生日及性别信息

WPS表格从身份证号提取生日及性别信息目前,我国的身份证号分为两种,一种为15位,一种为18位。
在15位的老版身份证中,第7到12位为出生日期数,最后一位为性别代码,偶数为女,奇数为男。
18位的新身份证中第7到14位为出生日期数,倒数第2位为性别代码,同样偶数为女,奇数为男。
基于这个特点,我们可以用函数加以判断。
如图1是我们已输入完身份证号的XX表,在输入身份证号时需注意,要把单元格格式设为文本型,最好不要在所输身份证号前加一个单引号“'”或其它的符号。
一、提取出生日期在E2中输入公式:方法一,公式“=DATE(MID(D2,7,4),MID(D2,11,2),MID(D2,13,2))”方法二,公式“=IF(LEN(D2)=15,TEXT(MID(D2,7,6),"1900年00月00日"),TEXT(MID(D2,7,8),"00年00月00日"))”,拖动填充柄向下复制公式,这样就完成了出生日期的提取(如图2):二、提取性别信息在C2中输入公式:“=IF(MOD(IF(LEN(D2)=15,MID(D2,15,1),MID(D2,17,1)),2)=1,"男","女")”,拖动填充柄向下复制公式,这样就完成了性别信息的提取(如图3):三、年龄计算在F2单元格输入公式:方法一,从身份证直接计算公式“=DATEDIF(DATE(MID(D2,7,4),MID(D2,11,2),MID(D2,13,2)),TO DAY(),"y")”并回车。
15位身份证“=DATEDIF(DATE(MID(F880,7,4),MID(F880,11,2),MID(F880,13, 2)),TODAY(),"y")”(如图4):方法二,从出生日期计算公式“=DATEDIF(E2,TODAY(),"Y")”,并回车。
WPS身份证号码提取出生年月日的公式

WPSEXCEL公式1、18位身份证号码提取出生年月日的公式:=MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2)注:A是列,1是行,&在数字键7的上方。
2、18位身份证号码提取性别男、女的公式:=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"))。
EXCEL中身份证信息提取的方法(五篇)

EXCEL中身份证信息提取的方法(五篇)第一篇:EXCEL中身份证信息提取的方法【身份证信息提取】从身份证号码中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”)+0=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”)*1=IF(A2<>“",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)显示格式均为yyyy-m-d。
(最简单的公式,把单元格设置为日期格式)=IF(LEN(A2)=15,”19“&MID(A2,7,2)&”-“&MID(A2,9,2)&”-“&MID(A2,11,2),MID(A2,7,4)&”-“&MID(A2,11,2)&”-“&MID(A2,13,2))显示格式为yyyy-mm-dd。
(如果要求为“1995/03/29”格式的话,将”-” 换成”/”即可)=IF(D4=”“,”“,IF(LEN(D4)=15,TEXT((”19“&MID(D4,7,6)),”0 000年00月00日“),IF(LEN(D4)=18,TEXT(MID(D4,7,8),”0000年00月00日“))))显示格式为yyyy年mm月dd日。
(如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd)=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),”19“&MID(A1:A2,7,6))显示格式为yyyymmdd。
=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)+0=IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2))=MID(A1,7,4)&”年“&MID(A1,11,2)&”月“&MID(A1,13,2)&”日“=IF(A1<>”“,TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1) =18)*2),”#-00-00“))从身份证号码中提取出性别=IF(MOD(MID(A1,15,3),2),”男“,”女“)(最简单公式)=IF(MOD(RIGHT(LEFT(A1,17)),2),”男“,”女“)=IF(A2<>” ”,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),)=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,”男“,”女“)从身份证号码中进行年龄判断=IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MI D(A3,7,6+ (LEN(A3)=18*2),”#-00-00”),TODAY(),”Y”),)=DATEDIF(A1,TODAY(),“Y”)(以上公式会判断是否已过生日而自动增减一岁)=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900=YEAR(TODAY())-IF(LEN(A1)=15,”19“&MID(A1,7,2),MID(A1,7,4))=YEAR(TODAY() )-VALUE(MID(B1,7,4))&”岁“=YEAR(TODAY())-IF(MID(B1,18,1)=”“,CONCATENATE(”19“,MID(B1,7,2)),MID(B 1,7,4))按身份证号号码计算至今天年龄=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=1 8)*2),”#-00-00“),TODAY(),”y“)以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)*1,”2006-10-31“,”y“)按身份证号分男女年龄段按身份证号分男女年龄段,身份证号在K列,年龄段在J列(身份证号为18位)男性16周岁以下为男性16周岁(含16周岁)以上至50周岁为2 男性50周岁(含50周岁)以上至60周岁为3 男性60周岁(含60周岁)以上为女性16周岁以下为女性16周岁(含16周岁)以上至45周岁为2 女性45周岁(含45周岁)以上至55周岁为 3 女性55周岁(含55周岁)以上为=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,1 3,2)),TODAY(),”y“),{0,16,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1)))=SUM(--(DATEDIF(MID(K1,7,4)&”/“&MID(K1,11,2)&”/“&MID(K1,13,2 ),TODAY(),”y“)>={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),2)))㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【年龄和工龄计算】根据出生年月计算年龄=DATEDIF(A1,TODAY(),”y“)=DATEDIF(A1,TODAY(),”y“)&”周岁“ =DATEDIF(A1,NOW(),”y“)根据出生年月推算生肖中国人有12生肖,属什么可以推算出来。
利用WPS表格提取身份证个人信息

目前,我国的身份证号分为两种,一种为15位,一种为18位。
在15位的老版身份证中,第7到12位为出生日期数,最后一位为性别代码,偶数为女,奇数为男。
18位的新身份证中第7到14位为出生日期数,倒数第2位为性别代码,同样偶数为女,奇数为男。
基于这个特点,我们可以用函数加以判断。
如图1是我们已输入完身份证号的职工信息表,在输入身份证号时需注意,要把单元格格式设为文本型,或在所输身份证号前加一个单引号“'”。
1、提取出生日期在C2中输入公式“=IF(LEN(C2)=15,TEXT(MID(C2,7,6),"1900年00月00日"),TEXT(MID(C2,7,8),"00年00月00日"))”,拖动填充柄向下复制公式,这样就完成了出生日期的提取(如图2):热门推荐:教你几招判断系统是否被流氓侵犯多窗口浏览器Opera 9.0新版怒放2、提取性别信息在D2中输入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")”,拖动填充柄向下复制公式,这样就完成了性别信息的提取(如图3): 公式详解:LEN是长度函数,MID是提取字符函数,TEXT是转换格式函数,MOD是整除函数,IF是判断函数。
提取出生日期公式的意思是如果身份证号为15位,把从第7位起的6个字符转换为"1900年00月00日"的格式,否则(即为18位身份证),把从第7位起的8个字符转换为"00年00月00日"的格式。
提取性别信息公式的意思是如果身份证号为15位,把第15位和2整除,如果整除,显示为女,不能整除,显示为男;身份证号为18位,把第15位和2整除,如果整除,显示为女,不能整除,显示为男。
另外,如果想把出生日期转换为真正的日期类型,只需在公式中在TEXT前加两个减号,这是减负运算,然后根据需要设置单元格具体的日期类型。
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
假设C2为身份证号、D2为年龄。
公式1:(按自然年份计算年龄)
=YEAR(TODAY())-MID(C2,7,4)
公式2:(按出生月份计算年龄)
=DATEDIF(TEXT(MID(C2,7,6+(LEN(C2)=18)*2),"#-00-00"),TODAY(),"y")
作者:宋志明
前些天领导要求统计所有员工的性别、出生日期、年龄等信息,并且要得很急。
而我们单位员工人数众多,短时间内统计相关信息并且输入计算机几乎是不太可能的。
幸好在以前的一份金山表格中我们曾经统计有所有员工的身份证号码,而身份证中正有我们所需要的性别、出生日期、年龄等信息的。
所以,干脆,还是直接在金山表格中从身份证号码提取相关的信息吧。
身份证号放在A2单元格以下的区域。
我们需要从身份证号码中提取性别、出生日期、年龄等相关信息。
由于现在使用的身份证有15位和18位两种。
所以,在提取相关信息时,首先应该判断身份证号码的数字个数,然后再区别不同情况进行相关处理。
一、身份证号的位数判断
在B2单元格输入如下公式“=LEN($A2)”,回车后即可得到A2单元格身份证号码的数字位数,如图1所示。
LEN($A2)公式的含义是求出A2单元格字符串中字符的个数。
由于当初身份证输入时就是以文本形式输入的,所以用此函数正可以很方便地求到身份证号码的位数。
图1 WPS中键入判断函数
二、判断男女性别
第一步:提取性别识别数字。
如果是18位身份证,那么倒数第二位就是性别判断的识别数字,奇数为男,偶数则为女。
而如果是15位身份证,那么倒数第一位则是性别识别数字,同样奇数为男,偶数为女。
所以首先应该提取这两种身份证的性别识别数字。
在C2单元格输入公式“=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))”,回车后,立刻可以得到相应的识别数字了。
如图2所示。
图2 WPS中键入函数
函数MID(A2,17,1)的作用是从A2单元格字符串的第17个字开始截取1个字符,这正好是倒数第二个字符;函数RIGHT(A2,1)的作用则是从A2单元格字符串的右侧开始截取1个字符,也就是倒数第一个字符。
至于IF函数,则进行相应的判断。
公式
“=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))”的作用是“如果B2单元格等于18,那么从A2单元格字符串的第17位开始截取1个字符,否则从A2单元格字符串的右侧截取1个字符”。
第二步:根据识别数字判断男女性别,在D2单元格输入公式“=IF(ISEVEN($C2)=TRUE,"女","男")”,回车后就可以得到的结果了。
如图3所示。
图3 WPS中键入函数判断男、女性别
此处函数ISEVEN($C2)的作用是判断C2单元格的数字是否为偶数,如果是,则返回结果TRUE,否则返回结果FALSE。
因此,公式“=IF(ISEVEN($C2)=TRUE,"女","男")”的含义就是如果C2单元格数字为偶数,那么在当前单元格显示“女”,否则显示“男”。
三、出生日期提取
第一步:提取数字型出生日期。
在E2单元格输入公式“=IF($B2=18,MID($A2,7,8),"19"&MID($A2,7,6))”并回车,如图4所示。
这种形式的出生日期我们并不常用,此处提取出来只是为了我们下一步计算年龄时要用,也是一个辅助列。
"19"&MID($A2,7,6)的意思是把字符“19”和MID($A2,7,6)截取到的6个字符合并在一起成为一个新的字符串。
其它的,就不用再解释了吧?
图4 WPS中键入函数判断出生日期
第二步:提取带连接符的出生日期。
在F2单元格输入公式
“=IF($B2=18,MID($A2,7,4)&"-"&MID($A2,11,2)&"-"&MID($A2,13,2),"19"&MID($A2,7,2)&" -"&MID($A2,9,2)&"-"&MID($A2,11,2))” 并回车,如图5所示,可以得到我们常用的如“1977-08-11”的格式。
此处用到的函数在前面已经有过解释,所以不再罗嗦了。
图5 WPS中键入函数判断特殊日期
四、公式计算年龄
在G2单元格输入公式“=YEAR(NOW())-LEFT(E2,4)”并回车,年龄计算就这么简单,用当前的年份减去出生的年份就行了。
如图6所示。
函数YEAR(NOW())可以返回当前的年数“2007”,而LEFT(E2,4)则从E2单元格的左侧截取4个字符,也就是出生年份。
图6 WPS中用公式计算年龄
最后,选中B2:G2单元格,向下拖动填充句柄至最后一行,那么我们所需要的所有数据就有了,再选中B列、C列、E列,并点击右键,在弹出的右键菜单中点击“隐藏”命令,将这些辅助列隐藏起来,就可以得到一份完整的表格了。
很简单吧?利用WPS的函数功能,只需要设置一下相应的参数,把一个个隐藏在身份证号码里的信息提取出来,完成上面所有操作用时也不到半小时,将所得到的表格美化一下,就可以向领导交差了,呵呵。