如何从Excel表格中用身份证号码提取出生日期.docVIP

如何从电子表格中用身份证号码提取出生日期每个人从出生报户口起就拥有了自己的身份证号码，每个人的身份证号码的中包含了一些固定信息，如：出生年月日，性别等。根据这些又可推算出其年龄。利用Excel的函数功能，从身份证号码中提取相关信息，这在学校的学籍管理中，可以起到很好的作用。下面我将我用到的几种方法加以归纳，以便有需要是引用。Excel中从身份证号码中提取出表示出生年、月、日的数字，用文本函数MID()可以达到目的。其含义为：从文本字符串中指定的起始位置起返回指定长度的字符。具体格式：MID（文本，开始字符，所取字符）。如今的身份证号码有二种，分别为15位和18位的。15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。18位身份证号从第7位到第14位是出生年月日，年份用的是4位数。首先要对一个身份证号码是15位或是18位进行判断，用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。综合上述分析，可以通过下述操作，完成形如1978-12-24样式的出生年月日自动提取：（假如身份证号数据在A1单元格，在B1单元格中编辑公式）=IF(LEN(A1)=15,MID(A1,7,2)&-&MID(A1,9,2)&-&MID(A1,11,2),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,7,4)&-&MID(A1,11,2)身份证号码中还隐藏着每个人的性别信息，15位身份证号码的最后一位，18位身份证号码的最后第二位的数字表示的是性别，奇数表示男，偶数表示女。根据这个规律，得出根据身份证号码（15位和18位通用）自动提取性别的公式为：（说明：公式中的B2是身份证号）=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,男,女),IF(LEN(B2)=18,IF(MOD(MID(B2,17,1),2)=1,男,女),身份证错)网上看到的公式中用到RIGHT()函数，感觉有错：=IF(LEN(B2)=15,IF(MOD(RIGHT(B2,3),2)=0,女,男),IF(LEN(B2)=18,IF(MOD(MID(B2,15,1),2)=0,女,男),身份证错) 根据身份证号码求年龄：=IF(LEN(B2)=15,year(now()-1900-(MID(B2,7,2),if(LEN(B2)=18, year(now()-(MID(B2,7,4),身份证错)Excel表中用YearMonthDay函数取相应的年月日数据。总结：1、根据身份证号码求出生年月：=IF(LEN(A1)=15,MID(A1,7,2)&-&MID(A1,9,2)&-&MID(A1,11,2),MID(A1,7,4)&-&MID(A1,11,2)&-&MID(A1,13,2)或：=IF(LEN(A1)=15,CONCATENATE(19,MID(A1,7,2),.,MID(A1,9,2),IF(LEN(A1)=18,CONCATENATE(MID(B2,7,4),.,MID(B2,11,2),身份证错)2、根据身份证号码求性别：=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,男,女),IF(LEN(B2)=18,IF(MOD(MID(B2,17,1),2)=1,男,女),身份证错)3、根据身份证号码求年龄：=IF(LEN(B2