在excel中巧妙运用身份证号码输入身份证号如何做到自动显示姓别.docx

在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),\男\女\S(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),\=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),\=IF(A2\显示格式均为yyyy-m-d。

最简单的公式，把单元格设置为日期格式）=IF(LEN(A2)=15,\4)&\显示格式为yyyy-mm-dd〔如果要求为“1995/03/29〞格式的话，将〞-〞 换成〞/〞即可〕 =IF(D4=\年00月00日\年00月00日\显示格式为yyyy年mm月dd日〔如果将公式中“0000年00月00日〞改成“0000-00-00〞,那么显示格式为yyyy-mm-dd〕=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),\显示格式为yyyymmddTEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),\=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)&\年\月\日\=IF(A1\从身份证号码中提取出性别=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,\=YEAR(TODAY())-VALUE(MID(B1,7,4))&\岁\=YEAR(TODAY())-IF(MID(B1,18,1)=\7,4))按身份证号号码计算至今天年龄=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),\AY(),\以2023年10月31日为基准日,按按身份证计算年龄(周岁)的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),\按身份证号分男女年龄段按身份证号分男女年龄段，身份证号在K列，年龄段在J列〔身份证号为18位〕 男性16周岁下列为1男性16周岁〔含16周岁〕以上至50周岁为2 男性50周岁〔含50周岁〕以上至60周岁为3 男性60周岁〔含60周岁〕以上为4 女性16周岁下列为1女性16周岁〔含16周岁〕以上至45周岁为2 女性45周岁〔含45周岁〕以上至55周岁为3 女性55周岁〔含55周岁〕以上为4=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),\{0,16,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1)))=SUM(--(DATEDIF(MID(K1,7,4)&\)>={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。

四、应用例子ABCD1编号 姓名 工资科室 22023001周杰伦2870办公室 32023002萧亚轩2750人事科 42023022郑智化2680供给科 52023022屠洪刚2980销售科 62023022孙楠 2530财务科 72023036孟庭苇2200工会A列已排序〔第四个参数缺省或用TRUE〕 VLOOKUP(2023001,A1:D7,2,TRUE) 等于“周杰伦〞VLOOKUP(2023001,A1:D7,3,TRUE) 等于“2870〞VLOOKUP(2023001,A1:D7,4,TRUE) 等于“办公室〞VLOOKUP(2023022,A1:D7,2,TRUE) 等于“孙楠〞VLOOKUP(2023036,A1:D7,3,TRUE) 等于“2200〞VLOOKUP(2023036,A1:D7,4,TRUE) 等于“工会〞VLOOKUP(2023036,A1:D7,4)等于“工会〞假设A列没有排序，要得出正确的结果，第四个参数必须用FALAE VLOOKUP(2023001,A1:D7,2,FALSE) 等于“周杰伦〞VLOOKUP(2023001,A1:D7,3,FALSE) 等于“2870〞VLOOKUP(2023001,A1:D7,4,FALSE) 等于“办公室〞VLOOKUP(2023022,A1:D7,2,FALSE) 等于“孙楠〞VLOOKUP(2023036,A1:D7,3,FALSE) 等于“2200〞VLOOKUP(2023036,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改成几，然后在后面补几个*就好了。