重生之九龙夺爱tx书包:EXCEL中从身份证号码提取出生日期的方法

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”

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

　　举例，某人身份证为310123190102039527（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”再转换成日期格式就更离谱了，输出结果直接是“##########”。当然，超过最大值的“2958465”后一个数字“2958466”转化成日期格式，也是输出“##########”，不存在。

　　所以说，以前1999年面临最大的问题是“千年虫”问题，可能到了2000年，日期显示为1900年。不过现在都已经解决了这个问题。但是，在比较远的未来，将会遇到“万年虫”问题，当然，这离我们很遥远，那时候地球在不在还是个问题，不过我感觉，这个“万年虫”问题可能比之前的“千年虫”问题更严重，也许，那时候我们的身份证号码要成为了19位也说不定。