在excel中巧妙运用身份证号码输入身份证号如何做到自动显示姓别_年龄及出生年月日

第一篇：在excel中巧妙运用身份证号码输入身份证号如何做到自动显示姓别_年龄及出生年月日

1、在excel中输入身份证号如何做到自动显示姓别,年龄及出生年月日

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),MID(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(LEN(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,ABS(RIGHT(A2))=7,ABS(RIGHT(A2))=9),“男”,“女”)),(IF(OR(ABS(MID(A2,17,1))=1,ABS(MID(A2,17,1))=3,ABS(MID(A2,17,1))=5,ABS(MID(A2,17,1))=7,ABS(MID(A2,17,1))=9),“男”,“女”)))

算出生年月的：=DATE(IF(LEN(A2)=15,MID(A2,7,2),MID(A2,9,2)),IF(LEN(A2)=15,MID(A2,9,2),MID(A2,11,2)),IF(LEN(A2)=15,MID(A2,11,2),MID(A2,13,2)))

【身份证信息提取】

从身份证号码中提取出生年月日

=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)),”0000年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&MID(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(B1,7,4))按身份证号号码计算至今天年龄

=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*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周岁为男性50周岁（含50周岁）以上至60周岁为男性60周岁（含60周岁）以上为女性16周岁以下为女性16周岁（含16周岁）以上至45周岁为女性45周岁（含45周岁）以上至55周岁为女性55周岁（含55周岁）以上为=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,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)))

在网站上查到关于如何使用该函数的详解，感觉蛮复杂的。关于VLOOKUP函数的用法

“Lookup”的汉语意思是“查找”，在Excel中与“Lookup”相关的函数有三个：VLOOKUP、HLOOKUO和LOOKUP。下面介绍VLOOKUP函数的用法。

一、功能

在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的数据。

二、语法 标准格式：

VLOOKUP(lookup_value,table_array,col_index_num , range_lookup)

三、语法解释

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)可以写为：

VLOOKUP(需在第一列中查找的数据,需要在其中查找数据的数据表,需返回某列值的列号,逻辑值True或False)1.Lookup_value为“需在数据表第一列中查找的数据”，可以是数值、文本字符串或引用。2.Table_array 为“需要在其中查找数据的数据表”，可以使用单元格区域或区域名称等。

⑴如果 range_lookup 为 TRUE或省略，则 table_array 的第一列中的数值必须按升序排列，否则，函数 VLOOKUP 不能返回正确的数值。

如果 range_lookup 为 FALSE，table_array 不必进行排序。

⑵Table_array 的第一列中的数值可以为文本、数字或逻辑值。若为文本时，不区分文本的大小写。3.Col_index_num 为table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时，返回 table_array 第一列中的数值；

Col_index_num 为 2 时，返回 table_array 第二列中的数值，以此类推。如果Col_index_num 小于 1，函数 VLOOKUP 返回错误值 #VALUE!；

如果Col_index_num 大于 table_array 的列数，函数 VLOOKUP 返回错误值 #REF!。4.Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。

四、应用例子

A B C D 1 编号

姓名

工资

科室 2 2005001 周杰伦 2870 办公室 3 2005002 萧亚轩 2750 人事科 4 2005006 郑智化 2680 供应科 5 2005010 屠洪刚 2980 销售科 6 2005019 孙楠 2530 财务科 7 2005036 孟庭苇 2200 工 会

A列已排序（第四个参数缺省或用TRUE）VLOOKUP(2005001,A1:D7,2,TRUE)等于“周杰伦” VLOOKUP(2005001,A1:D7,3,TRUE)等于“2870” VLOOKUP(2005001,A1:D7,4,TRUE)等于“办公室” VLOOKUP(2005019,A1:D7,2,TRUE)等于“孙楠” VLOOKUP(2005036,A1:D7,3,TRUE)等于“2200” VLOOKUP(2005036,A1:D7,4,TRUE)等于“工 会” VLOOKUP(2005036,A1:D7,4)

等于“工 会”

若A列没有排序，要得出正确的结果，第四个参数必须用FALAE VLOOKUP(2005001,A1:D7,2,FALSE)等于“周杰伦” VLOOKUP(2005001,A1:D7,3,FALSE)等于“2870” VLOOKUP(2005001,A1:D7,4,FALSE)等于“办公室” VLOOKUP(2005019,A1:D7,2,FALSE)等于“孙楠” VLOOKUP(2005036,A1:D7,3,FALSE)等于“2200” VLOOKUP(2005036,A1:D7,4,FALSE)等于“工 会”

五、关于TRUE和FALSE的应用

先举个例子，假如让你在数万条记录的表格中查找给定编号的某个人，假如编号已按由小到大的顺序排序，你会很轻松地找到这个人；假如编号没有排序，你只好从上到下一条一条地查找，很费事。

用VLOOKUP查找数据也是这样，当第一列已排序，第四个参数用TRUE（或确省），Excel会很轻松地找到数据，效率较高。当第一列没有排序，第四个参数用FALSE，Excel会从上到下一条一条地查找，效率较低。

笔者觉得，若要精确查找数据，由于计算机运算速度很快，可省略排序操作，直接用第四个参数用FALSE即可。

提取相同人名字的身份证号码

在A列中有所有人的姓名（有同名不同地址的人），B列中有身份证号码，C列中有这些人的住址，D列中是部分人的姓名，现在要在E列表格内填上D列中对应人的身份证号码（有七八千人）（A列中包含D列中的全部人名）

如无一同名同姓的话 E1输入公式

=VLOOKUP(D1,A:C,2,)下拉即可

这个是如果有同姓名的，会出现首次出现人姓名的身份证号码

如果没有同名同姓，就可以在E2单元格中输入=VLOOKUP(D2,A2:C3000,2,0)注意哦是放在E2哦，也是会出现首次姓名

excel中如何将表格中的身份证号码后几位以*号代替

虽然现在基本上都是新的身份证了（18位），但不排除可能出现的老身份证（15位）。因此，应用下面这个方法。

设身份证数据在c列，d列为隐藏后4的值。在d2输入：

=left(c2,len(c2)-4)&”****" 你需要有几个*，就将公式中的4改成几，然后在后面补几个*就好了。