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”

  好了，利用这两个公式，就可以做到简单的提取一个身份证号码中的日期了。

  举例，某人身份证为310123************（18位），输入到"A1"单元格，建议用把单元格改成"文本mid函数提取年月日"，不然会显示为科学计数法。

  【单元格改成文本的方法：鼠标右击"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)，比如我再举例一个身份证号码：310123*********，把这个字符串输入"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”，这就是日期格式的最大值和最小值。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。