Excel之一在Excel中根据身份证号码利用公式自动生成出生年月日

1、【Excel之一】在Excel中根据身份证号码利用公式自动生成出生年月日一、先给实例，不用搞懂原理假设：A1栏内有身份证号码，要在B1栏中自动生成年月日，那么就在B1栏中输入以下公式。（由于A1是假定的，可以任意替换，所以在下面的公式中用红色醒目标出）【情况1】公式如下：=TEXT(LEN(A1)=15)*19&MID(F2,7,6+(LEN(A1)=18)*2),"#-00-00") 显示的效果是： 年年年年-月月-日日，如1984-04-28 【情况2】 公式如下： =IF(LEN(A1)=18,MID(A1,7,8),19&MID(A1,7,6) )

2、显示的效果是： 年年年年月月日日，如19840428 【情况3】 公式如下： =IF(LEN(A1)=18,MID(A1,7,4)&"年"&MID(A1,11,2)&"月"&MID(A1,13,2)&"日",IF(LEN(A1)=15,MID(A1,7,2)&"年"&MID(A1,9,2)&"月"&MID(A1,11,2)&"日","无效身份证号码") 显示的效果如下： XXX

3、X年XX月XX日，如1984年04月28日 【情况4】 公式如下： =IF(LEN(A1)=18,MID(A1,7,4)&"."&MID(A1,11,2),IF(LEN(A1)=15,MID(A1,7,2)&"."&MID(A1,9,2),"无效身份证号码") 显示的效果如下： 年年年年.月月，如1984.04   二、有兴趣就拓展一下根据以上四种情况，在不需要理解公式含义的情况下，就可以派生出其他几种不同的显示格式出来。【情况5】公式如下：=IF(LEN(A1)=18,MID(A1,7,4)

4、&"-"&MID(A1,11,2),IF(LEN(A1)=15,MID(A1,7,2)&"."&MID(A1,9,2),"无效身份证号码") 显示的效果如下： 年年年年-月月，如1984-04依次类推，基本上需要的格式都能改出来。 三、还有兴趣就总结一下以上运用到的函数1. Len函数（1）函数作用：用来对单元格内容的长度进行测量。（2）使用语法：LEN(text)说明：text 是要查找其长度的文本。空格将作为字符进行计数。2. MID函数（1）函数作用：根据用户自行指定的开始位置和字符长度

5、对某单元格进行字符提取。（2）使用语法：MID(text,start_num,num_chars)说明：Text 是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。Num_chars 指定希望 MID 从文本中返回字符的个数。注意： 如果 start_num 大于文本长度，则 MID 返回空文本 ()。 如果 start_num 小于文本长度，但 start_num 加上 num_chars 超过了文本的长度，则 MID 只返回至多直到文本末尾的字符。 如果 sta

6、rt_num 小于 1，则 MID 返回错误值 #VALUE!。 如果 num_chars 是负数，则 MID 返回错误值 #VALUE!。 3. IF函数（1）函数作用：根据指定的条件来计算结果为 TRUE 或 FALSE，返回不同的结果。可以使用 IF 对数值和公式执行条件检测。（2）使用语法：IF(logical_test,value_if_true,value_if_false)说明：Logical_test   表示计算结果为 TRUE 或 FALSE 的任意值或表达式。Value_if_true   是 logical_test 为 TRUE

7、 时返回的值。Value_if_false   是 logical_test 为 FALSE 时返回的值。 =分割线=一、以上大部分内容来源于网络，并非原创，我只是按照自己的爱好做了整理。二、最近写“类型文”写烦了，想写写日志，却囿于内容限制，只好以此充数，骗骗自己。三、有可能写二，也可能永远只有一。反正不重要。说明：公式中的A1是身份证号 身份证号码为15位的,最后一位号码能决定性别,奇数为男性,偶数为女性;身份证号码为18位的,倒数第二位号码能决定性别,奇数为男性,偶数为女性。 1、根据身份证号码求性别： =IF(LEN(A1)=15,IF(MOD(VALUE

8、(RIGHT(A1,1),2)=0,"女","男"),IF(LEN(A1)=18,IF(MOD(VALUE(MID(A1,17,1),2)=0,"女","男"),"身份证错") 身份证号码为15位的,第七、八位为出生年，第九、十位为出生月,第十一、十二位为出生日;身份证号码为18位的,第七、八、九、十位为出生年，第十一、十二位为出生月,第十三、十四位为出生日。 2、根据身份证号码求出生年月： =IF(LEN(A1)=15,CONCATENATE("19",MID(A1,7,2

9、),".",MID(A1,9,2),IF(LEN(A1)=18,CONCATENATE(MID(A1,7,4),".",MID(A1,11,2),"身份证错") 3、根据身份证号码求年龄： =IF(LEN(A1)=15,year(now()-1900-VALUE(MID(A1,7,2),if(LEN(A1)=18,year(now()-VALUE(MID(A1,7,4),"身份证错")叶子  11:04:27=MID(A2,3,6)  叶子  11:04:

10、55考生号中截取其中的生源地代码：=MID(A2,3,6)六然学童  11:05:06谢谢叶子  11:06:37生源地代码与生源地名称查找比对命令：VLOOKUP(B2,'生源地代码表'!A:B,2,FALSE)用syszddm在“生源地代码表”取SYSZDMC。叶子  11:06:47在excel使用哪个函数能在同一个工作簿下的一个工作表中引用另一个工作表中的数据,以及函数ISNA,VLOOKUP的使用方法？直接写=sheet1！A1即可ISNA(value)判断值是否为错误值 #N/A（值不存在）。VLOOKUP

11、(lookup_value,table_array,col_index_num,range_lookup)在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。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!。Range_lookup 为一逻辑值，指明函