返回目录:excel表格制作
Hello,大家好,我们又见面了,今天来教大家制作一个输入身份证信息自动获取信息的Excel表格。
想要从身份证号码中了解更多的个人信息,我们先来了解一下身份证号码的各个部分数字的含义。身份证号由18位数字构成, 由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。
身份证信息提取
1、 提取出生日期
在身份证号码的18位中,出生日期是从第七位到第十四位的,提取日期的时候我建议使用DATE函数,这个函数使用起来较为方便,当然也可以使用其他函数,还可以使用分列或者快速填充功能进行信息的提取。
提取出生日期
插入函数:
DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,14,2))
函数解析:
DATE函数表示提取日期,其函数语法为:DATE(年,月,日);MID函数表示从指定的位置获取指定的字符长度,MID(B2,7,4)则表示,在B2单元格内从第七个字符起获取其后四位,而这个区间正好为年份的日期,后面以此类推。
当然,从身份证中获取出生日期不只是这一种,也可以使用=TEXT(MID(B2,7,8),"0-00-00")。关于此示例的方法远不止一个,欢迎大家在下方评论区进行讨论。
2、 提取年龄
方法一:已经计算出出生日期
上文已经求得出生日期,年龄就很好计算了,使用当前日期减去出生日期即可。
提取年龄
插入函数:
DATEDIF(C2,TODAY(),"y")
函数解析:
DATEDIF函数为Excel中隐藏函数,在Excel的函数和帮助中查询不到,需要键入,关于这个函数,大家只需要记住这个特殊的用法即可。
方法二:未计算出出生日期
当我们未计算出出生日期,或者不需要计算出生日期,只需要计算年龄时,方法一就显得不是那么的便捷,此时我们使此种方法:
提取年龄
插入公式:
YEAR(TODAY())-MID(B2,7,4)
公式解析:
YEAR(TODAY())表示当前电脑系统日期的年份的时间;MID(B2,7,4)则表示身份证号中从第七位起获取后四位字符,这四位字符为我们的出生年份,而整个函数则翻译为:"电脑中的年份"-"身份证中的出生年份"。这样我们就得到了年龄。
3、 获取人员性别
在身份证号码上,倒数第二位表示性别,奇数代表男性,偶数代表女性,而我们的核心思想是使用函数判断奇偶(使用MOD函数判断是否能被2整除),再使用IF函数进行判断。
获取人员性别
插入函数:
IF(MOD(MID(B2,17,1),2),"男","女")
函数解析:
我们先使用MID函数对身份证号倒数第二位进行提取;然后使用MOD函数判断所提取的函数是否能对2进行整除(此处也可使用函数:ISEVEN、ISODD等)判断奇偶,最后再使用IF函数。
4、 提取人员籍贯:
籍贯则是由身份证号的前六位确定的,现行的标准为GB/T2260,想要判断籍贯,则需要将GB/T2260中的行政区划代码导入Sheet 2 中。
提取人员籍贯
插入函数:
VLOOKUP(MID(B2,1,6),Sheet2!B:C,2,0)
函数解析:
我们先使用MID函数提取提取身份证号的前6位,然后在我们事先导入的行政区划代码的Sheet 2 中查找所对应的籍贯即可。
文章中所说的技巧和函数并不唯一,你还有哪些方法,欢迎在下方的评论区进行讨论!!
往期精彩干货文章:
私信回复"身份证"即可获取自动提取身份证号码中信息的表格以及GB/T2260中的行政区划代码。
关注我们,持续了解更多知识。