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

【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) ) 显示的效果是： 年年年年月月日日， 【情况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)&"日","无效身份证号码")) 显示的效果如下： XXXX年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)&"-"&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）函数作用：根据用户自行指定的开始位置和字符长度对某单元格进行字符提取。 （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 只返回至多直到文本末尾的字符。 如果 start_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 时返回的值。 Value_if_false?? 是 logical_test 为 FALSE 时返回的值。 ? ======================分割线====================== 一、以上大部分内容来源于网络，并非原创，我只是按照自己的爱好做了整理。 二、最近写“类型文”写烦了，想写写日志，却囿于内容限制，只好以此充数，骗骗自己。 三、有可能写二，也可能永远只有一。反正不重要。 说明：公式中的A1是身份证号 身份证号码为15位的,最后一位号码能决定性别,奇数为男性,偶数为女性;身份证号码为18位的,倒数第二位号码能决定性别,奇数为男性,偶数为女性。 1、根据身份证号码求性别： =IF(LEN(A1)=15,IF(MOD(VALUE(RIGHT(A1,1)),2)=0,"女","男"),IF(LEN(A1)=18,