未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
由身份证号码分析出生日期及性别 EXCEL表格自动生成

2013.06.05

关注
EXCEL表格自动生成

身份证包含了大量信息,如发证地址、出生日期、性别等,可以通过设置公式自动将出生日期和性别提取出来。设置方法如下:

(1)提取出生日期

身份证号码通常是15位或18位,

1)若身份证号码为15位,如某人的身份证号码为“310112570416314”,说明他是1957年4月16日出生,如果出生的年份用M表示,月份用N表示,日期用P表示,用文本连接符将其连接起来就是“="19"&M&"年"&N&"月"&P&"日"”。
为了从身份证号码中提取有关的信息,需要用一个文本函数"MID”,其格式为:MID(text,start_num,num_chars),表示的意思是:MID(原来的字串,开始位置,字符的个数)。设原来字串即身份证号码为R。则:
年份M=MID(R,7,2),表示从第7个字符开始算起取2个,即取“57”。
月份N=MID(R,9,2),表示从第9个字符开始算起取2个,即取“04”。
日期P=MID(R,11,2),表示从第11个字符开始算起取2个,即取“16”。

这时上述函数就变为:
="19"&MID(B2,7,2)&"年"&MID(B2,9,2)&"月"&MID(B2,11,2)&"日"

上式表示的意思是:“19”加上从B2单元格中的第7位开始取出2位的数值加上“年”,再加上B2单元格中从第9位开始取出2位的数值加上“月”,再加上B2单元格中从第11位开始取出2位的数值加上“日”。在C2中输入公式后,打回车,然后拉动C2单元格的右下角,得出如图2-72所示结果。

图2-72

2)身份证号码为18位,因为不需要再加年了,只需将公式改为“=MID(B9,7,4)&"年"&MID(B9,11,2)&"月"&MID(B9,13,2)&"日"”即可,如图2-73所示。

图2-73

3)若15位和18位的混合在一起。

方法一:可以将其排序,将15位的和18位的分开成两类,分别用上述函数即可。
方法二:不进行新的排序,再引入判别函数IF,对字符串进行判别,若是15位,则用前述函数,否则用后面的函数。
公式为:=IF(LEN(B2)=15,”19”&MID(B2,7,2)&”年"&MID(B2,9,2)&”月"&MID(B2,11,2)&”日",MID(B2,7,4)&”年"&MID(B2,11,2)&”月"&MID(B2,13,2)&”日")。
上式的含义是:若B2单元格中是15个字符时,用含有“19”的函数运算,否则用后面的函数运算。如图2-74所示。“LEN”是确定单元格中字符串的个数的函数,既可以确定数值的个数,也可以确定文字的个数。

图2-74

(2)确定男女性别

18位身份证号码中的倒数第二位是用来确定性别的,单数为男,双数则为女。要确定性别,用函数MID,该函数表示从字符串指定位置开始取出的字符位数,如“MID(B2,17,1),2)”表示从B2单元格中的字符中,从左边数第17位开始,取出一位数值。再用函数MOD判别是奇数还是偶数。在D2中输入的函数为:“=IF(MOD(MID(B2,17,1),2)=1,"男","女")”。公式的含义是:在B2单元格中取出倒数第二位,然后除以2,当余数是1时,则为男,否则为女。然后下拉进行公式的填充。如图2?75所示。也可以在D2单元格中输入公式:“=(IF(B2="","",IF(MOD(MID(B2,17,1),2)=1,"男","女")))”,其含义是,当B2单元格为空时,显示为空,否则再进行性别的判断。如果15位和18位混合在一起,可以用公式:“=(IF(B2="","",IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,"男","女")))”。当B2单元格为空时,显示为空,否则,当B2单元格为15位时,取15位的最后一位(身份证号为15位的最后一位表示性别),否则,从18位身份证号第17开始取一位,进行性别的判断。

图2-75

(3)确定年龄

由身份证分析了出生年月后,利用判断两个时间间隔的函数“DATEDIF”可以确定年龄。在E2单元格中输入函数:“=DATEDIF(C2,TODAY(),"Y")”,含义是,当前时间与C2单元格中的时间差,以“年”显示在E2单元格中。然后向下拉动,将该公式一直填充到E11单元格中。如图2-76所示。

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP阅读全文并永久保存 更多类似文章
猜你喜欢
类似文章
从身份证号码提取出生日期、性别、年龄等信息
转Excel表格中根据身份证号码自动填出生日期、计算年龄
如何在EXCEL中提取身份证号码里的出生年月日、性别
如何从在excel中身份证号码里提取需要的信息
Excel快速从身份证上提取出生日期、年龄、性别
Excel表格身份证号码提取出生日期的公式
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!