EXCEL快速录入出生日期的方法.doc

EXCEL中从身份证号码提取出生日期的方法 由于工作需要，我需要单位人员的出生年月，单独一个一个输入？NO太麻烦了。我的工资程序里有人员信息，而且有每个人的身份证号码，所以我就想出了利用excel来完成这个工作。PS，我是用office2003的excel完成这个操作的，office2000可能有些操作有些不同，不妨试一下。首先先要说几个公式，明白了这几个公式后，就能简单完成了。函数【left】作用：提取数据左边n个数字的内容默认公式：=left(text, num_chars)翻译公式：=left(数据,n数字)举例：=left(Hello,2) 输出结果为最开始的2个字母“He”函数【right】作用：提取数据右边n个数字的内容默认公式：=right(text, num_chars)翻译公式：=right(数据,n数字)举例：=right(Hello,2) 输出结果为最末尾的2个字母“lo”好了，利用这两个公式，就可以做到简单的提取一个身份证号码中的日期了。举例，某人身份证18位），输入到A1单元格，建议用把单元格改成文本，不然会显示为科学计数法。【单元格改成文本的方法：鼠标右击A1单元格，选择“设置单元格格式(F).”，在“单元格格式”对话框中，选择“数字”标签，“分类(C)”里选择“文本”，点“确定”】那用公式套用的话，先用left吧，最后4位是不要的，所以提取左边14位。这个身份证号码已经输入到A1单元格，那么在B1单元格可以这样输入：=left(A1,14)，那出现的结果就是“31012319010203”，然后你可以用right命令提取B2单元格里后8位数字，就可以提取出生日代码了。比如我们在C1单元格里输入=right(B1,8)，出现的结果就是“19010203”，基本工程完成了。接下来是进阶教程，其实，完全可以把2个命令合并使用，比如，我们在D1单元格里这样输入：=right(left(A1,14),8)，看看结果如何？是不是直接出现了正确结果？其实，如果left和right两个命令同时用到，可以用一个更好的命令【mid】来代替，接下来我来说一下mid的用法：函数【mid】作用：提取数据，从左边起第n个数字开始，长度为m的内容默认公式：=mid(text, start_num, num_chars)翻译公式：=mid(数据,n开始第n位,m长度)举例：=mid(Hello,2,3) 输出结果为从第二位开始，长度为3的字母“ell”那样，刚刚复杂的left和right嵌套，可以在E1单元格输入公式=mid(A1,7,8)试试，就算出了正确的结果“19010203”但是，又出现问题了，这样的日期，一个8位的数字，其实不是excel正式的日期格式，正式的日期格式应该为“1901-02-03”，那如何操作呢？我的思路是这样的，分别提取出“年”、“月”、“日”，然后利用公式把年月日连接起来，就成为了正式的日期格式，接下来要引入第四个公式【date】函数【date】作用：提取“年”、“月”、“日”，使其转化成日期格式默认公式：=date(year, month, day)翻译公式：=date(x年,y月,z日)举例：=date(1901,02,03) 输出结果为日期格式的“1901-2-3”接下来，按照如下操作提取出年月日：提取年，在F1单元格输入：=mid(A1,7,4)，输出结果为“1901”。提取月，在G1单元格输入：=mid(A1,11,2)，输出结果为“02”。提取日，在H1单元格输入：=mid(A1,13,2)，输出结果为“03”。最后合并，在H1单元格输入：=date(F1,G1,H1)，看看输出结果如何？其实，这里也可以用date和mid的嵌套公式，=date(mid(A1,7,4),mid(A1,11,2),mid(A1,13,2)直接提取出日期。不过又出现一个问题，如果有些人的身份证号码是15位的呢？那么如何处理呢？其实一样的，15位身份证号码生日只有从第7位开始，6个数字，如果一口气写成嵌套公式，就是=date(mid(单元格,7,2),mid(单元格,9,2),mid(单元格,11,2)，比如我再举例一个身份证号码：310123010203952，把这个字符串输入A2单元格（记得先把A2单元格转换成文本格式），然后在B2单元格输入=date(mid(A2,7,2),mid(A2,9,2),mid(A2,11,2)。其实，date里面的数据是date(01,02,03)，公式会自动转换成1901-2-3，其实这里涉及到一个千年虫问题，其实现在已经是2010年，如果你把2001年2月3日简写成01年2月3日，由date命令就会转化成1901-2-3，以后在输入中一定要注意。不过有点可以放心，老的15位身份证不可能出现在2000年以后，也就是它2位数的年份正好直接转换成19XX，也不用多考虑，直接套用date公式。如果为了再严谨一些，确保日期为19XX年份的，可以在把公式改成=date(19&mid(A2,7,2),mid(A2,9,2),mid(A2,11,2)，这样就强制是19XX年了。其实这里大可不必这样做。现在问题又来了，如果我电脑里的数据，既有18位的，又有15位的，有什么办法只用一个公式搞定它？答案是有的。这里又要引入一个判别函数【if】函数【if】作用：判别，如果成立，输出公式/结果1；如果不成立，输出公式/结果2默认公式：=if(logical_test, value_if_ture, value_if_false)翻译公式：=if(判别式, 公式/结果1判别式为真, 公式/结果2判别式结果为假)举例1：=if(1+2=3,答案正确,答案错误) 输出结果“答案正确”【1+2=3，结果为真，所以输出结果1】举例2：=if(false,正确吗？,错误吗？) 输出结果“错误吗？”【false直接判别为假，所以输出结果2】举例3：=if(0,1是正确,0是错误) 输出结果“0是错误”【0直接判别为假（其他数字例如1、2、3的结果都为真），所以输出结果2】说个题外话if语句比较经典，我多举了几个例子，我经常用它来核对，比如有2列数字或者姓名，我要确保它们的位置一一对应，我就可以用if来判断，比如这两列分别是A列和B列，我在C1单元格输入=if(A1=B1,X)然后选中C1单元格，鼠标按住这个单元格边框右下角的小黑方块往下拉（或者双击）可以直接判断出A列和B列的数据是否一样。这里if怎么用呢？对了，就是先在判别式里判断出身份证的长度，对了，还要说判断长度的公式【len】函数【len】作用：输出结果为字符长度默认公式：=len(text)翻译公式：=len(数据)举例：=len(Hello) 输出结果为“5”那开始用if语句来完成这个工作，比如我新建了一个表格，随便输入了如下4个身份证号码 可以用LEN(A2)=18作为if语句的判别式，如果为真，输出结果1，即18位身份证的提取公式date(mid(A2,7,4),mid(A2,11,2),mid(A2,13,2)，那在结果2中输入15位的身份证判别式date(mid(A2,7,2),mid(A2,9,2),mid(A2,11,2)。写在B2单元格中，显示出的结果为“9748”。这是为什么勒？因为B2单元格的“单元格格式”不对，鼠标右击B2单元格，选择“数字”标签，“分类 (C)”中的“日期”，确定即可，结果就对了。【当然，如果你喜欢，你可以选择比如“XXXX年XX月XX日”的格式，也可以只显示年月等】 接下来就按住B2单元格右下角的小黑方块往下拉（双击小方块效果更好），结果就都出来了。 这里再展开一下，为什么一开始B2单元格输出结果为9748呢？怎么说呢？我只能用一种自己理解的非官方回答来说明，其实你们看到单元格的日期是个假象，其实真正背后的内容是一个数字。不妨可以做个实验，你随便找个单元格，输入数字“1”，然后修改“单元格格式”，改成“日期”格式，看到结果是什么？“1900-1-1”，就是说数字“1”对应的日期是“1900-1-1”。然后，再输入一个日期“9999-12-31”，这个日期是现有电脑能判断出最“将来”的日期，然后修改“单元格格式”，改成“常规”，结果就是“2958465”，这就是日期格式的最大值和最小值。而且经过我的测试，存在小数点的数字也是可以转换成日期的，小数点后的内容就是时间。例如“123.456”，转化成日期格式【注：这里的日期格式要带时间的】后输出结果为“1900-5-2 10:56:38 AM”，所以说，日期是可以计算的，例如：A1单元格输入你的出生日期，A2单元格输入今天的日期，然后在A3单元格输入：=A2-A1，然后看到的结果为日期格式，别着急，把A3单元格的格式改成“常规”，对了，这就是你出生以来活了多少天。当然，你可以在A4单元格输入：=A3/365，就可以算出自己几岁了（实足年龄）。另外，如果你输入“0”，然后转化成日期格式，是不成立的，“1900-1-0”这个数字自动左对齐，也就是说这个数据已经成为了文本格式，默认日期格式是右对齐的。当然，你输入类似“1856-5-9”之类的1900之前的日期格式，是死活不会转变成日期格式的，只会默认成为文本格式，自动左对齐。如果输入“-1”再转换成日期格式就更离谱了，输出结果直接是“#”。当然，超过最大值的“29584