excel怎么提取身份证信息中的数据

身份证是一组有规律的号码,具体分割应该是:XXX XXX XXXX XX XX XX X X的格式 。这里解释一下,第一组和第二组共六个数字代表的是户籍所在地代码,第三组数据是出生年份,第四组两位数代表出生月份,第五组两位数代表出生日期,倒数第二位则是性别判断数字(偶数为女性,奇数为男性),在统计信息的时候,可能只登记了姓名和身份证号码,如何能够快速的将生日、性别、年龄一一提取计算出来,这是很多人都想知道的 。而这些,都可以通过Excel公式来实现快速自动提取计算 。
首先新建一个Excel表格,右键单击要填写身份证号码的列(或者Ctrl+A整个表格),选择 设置单元格格式。
【excel怎么提取身份证信息中的数据】在弹出对话框内,点击 数字 中的 文本 确定 。注意这个操作一定要在复制身份证数字前操作 。
如表格中所示,在姓名和身份证后的同行单元格内,输入公式:=MID(B2 , 7,4)年MID(B2,11,2)月MID(B2 , 13,2)日 , 注意,公式不包括中文半角双引号 , 手动输入时也要留意 , 所有符号全部为英文半角字符 。另外,公式中B2为身份证所在单元格,具体到自己的表格中要根据情况变换 。这里要解释一下,公式中的MID函数是Excel提供的一个从字符串中提取部分字符的函数命令 , 具体使用格式在EXCEL中输入MID后会出现提示 。这个公式的大概意思是,提取B2单元格第7位后4个数字标记为年,第11位后2个数字标记为月,第13位后2个数字为日,由此组成出生日期 。
判断男女则是根据身份证号的第17位数字决定,所以我们在后面的单元格中单击输入公式:=IF(MID(B2 , 17,1)/2=TRUNC(MID(B2,17 , 1)/2),女,男),大意为根据指定的条件(本例中即B2单元格第17位)来判断其真(TRUE)、假(FALSE)(注:男、女),这里根据奇数、偶数来判断,所谓单数即男性,偶数为女性 。
最后是年龄 , 一般使用公式:=YEAR(NOW())-YEAR(C2)(本文中C2单元格为出生日期),但是这个公式第一要建立在之前我们提取出生日期的单元格数据上 , 而且这个公式也仅仅是以年份相减得出的年龄,说起来有点像我们常说的虚岁,这个在人事工作中是不科学的 。最精确的公式应为:=DATEDIF(TEXT(MID(B2,7,6+2*(LEN(B2)=18)),#-00-00),NOW(),y),DATEDIF函数其实是Excel隐藏函数,在帮助和插入公式里面没有 。公式主要作用是返回两个日期之间的年\月\日间隔数 , 我们也正是借此来精确计算实际年龄 。
如此一来,通过三组不同的函数 , 我们可以依靠着身份证信息快速提取出生日期和性别,并能精确计算出当事人年龄,为统计录入节约大量时间 。

excel怎么提取身份证信息中的数据的详细内容就为您分享到这里,【精彩生活】jing111.com小编为您精选以下内容,希望对您有所帮助: