返回目录:excel表格制作
通过身份证号提取年龄必先提取出生日期,之前我们提取身份证号出生日期都是用函数进行提取,今天给大家介绍一键提取出生日期技巧,很简Ctrl+E一键搞定,以下图为例提取身份证号中的出生日期。
在E2单元格中输入19961011,然后选中E2:E7单元格,按Ctrl+E,提取完毕。
是不是很简单快捷。
再给大家介绍一个提取性别的方法:
我们都知道身份证号的第17位数字代表性别,奇数代表男性,偶数代表女性,只要把第17位数字提取出来,用MOD()函数进行奇偶性判断返回0或1,然后用IF函数将0值显示为“女”,将“1”值显示为“男”。还没明白的伙伴往下看图。
步骤1、在D2单元格写入函数:=IF(MOD(RIGHT(LEFT(C2,17)),2),男,女)
步骤2、填充D3:D7区域
LEFT(C2,17)作用取身份证号的左边17位数,RIGHT()函数是指取右边第一位数,
所以RIGHT(LEFT(C2,17))可以取第17位数字。
MOD(RIGHT(LEFT(C2,17)),2)判断第17位数字是奇数还是偶数,返回1或0
IF(MOD(RIGHT(LEFT(C2,17)),2),男,女) 等同于IF(0,“男”,“女”)或IF(1,“男”,“女”),根据1或0返回“女”或“男”。
最后,求年龄,我们可以使用Datedif()函数,配合出生日期列求出年龄
注意:在使用“出生日期”列数据之前先要变成日期格式。
利用“分列”变成日期格式,步骤1、在“数据”选项卡下点击“分列”按钮,弹出“文本分列向导”,点“下一步”
将“分隔符号”区域所有项目取消勾选,点“下一步”
选择“日期”,YMD,点击“完成”
E列转换成日期格式了。
现在,我们用Datedif()函数求年龄,如下图
在F2单元格 输入=DATEDIF(E2,TODAY(),“Y”),填充F2:F7单元格,年龄出现了。
有关身份证号码提取部分个人信息 就讲到这里。 有问题可以私信我!
更多Excel精美模板资源和最新版Excel2016请私信,回复“分享”获取。
您的关注就是最大支持!
干货预警,全文2886字,配图20张。带你重新认识一下身份证号码的各种含义,以及提取文本函数,和时间日期函数的综合应用。
平常我们拿到一个身份证,里面自然有许多中文写着各种信息,一目了然。
知道上面的信息,这就好办了,我们可以逐个提取数字。下面介绍一个提取信息的快借鉴【Ctrl+E】能帮助我们快速准确提取、整合信息。
它可以提取生日、信息加密、文字更替、中英文分离,它全能得把众多文本提取函数逼得没活干了。
一、【快速填充】功能
现在你想,从身份证号码列提取生日,你可以先写出第一行的结果,接着双击填充,选择【快速填充】选项,就能得到正确的结果。
实现这样的功能,有一个快捷键,【Ctrl+E】,它再我们的键盘位置如下。
接下来,通过6个具体的案例带大家认识【Ctrl+E】的强大!
二、【Ctrl+E】快捷键使用规范
①第一行自行手动输入结果值
②把鼠标定位到第二行,按【Ctrl+E】
③如果存在数据类型比较复杂,多输入几行,再在后面使用【Ctrl+E】快捷键
④存在部分识别不了的,自行手动补充
1.从身份证号码中提取生日
了解过我们身份证基本构造的同学都知道,在身份证7-14位数是代表我们的生日。
2.手机打码
在做活动抽奖是,我们需要把中奖用户的手机号码加密。先输入第一行加密的手机,接着按【Ctrl+E】
3.替换字符
日常我们需要替换要用Ctrl+H,但这个方法有些小白不同,这个Ctrl+E的用法就容易理解,只需要写好第一行,按一下【Ctrl+E】,电脑系统就会马上识别出来。
4.手机号分段
日常填写纸表格,手机号码写成:14238219685,人们就很容易输错号码,若果统一改成142-3821-9685,就不容易输错手机号码。
3.添加书名号《》
日常中书籍、歌曲、电影等作品类都是用《》,统一添加符号也很简单。
4.拆分中英文
如果用MID、LEFT和RIGHT函数嵌套提取中英文,估计要写上5分钟,但用了【Ctrl+E】只需要1秒就能轻松完成!
5.内容合并
许多朋友的内容合并要一个个粘贴复制、或者好一点利用&符号链接,但有了这个快速填充,只需要写出实现效果,系统就能自动填充。
6.去除符号
一名一线的报表处理员,经常要面对各种格式错误的表格,但有了这个额【Ctrl+E】,就能快速实现规范数据源。
1.提取省份的代码
因为前2位代码代表省份,故此用LEFT函数即可提取省份代码。
公式:=LEFT(D2,2)
结合省份代码表,就可以把对应的省求导。
公式:=VLOOKUP(E2,省份代码!$A$2:$B$32,2,0)
如果直接用VLOOKUP(省份代码,代码表,2,0)无法匹配到正确的省份,因为提取出来的代码为文本,需要先转化为数值。此处用×1转化,
公式1::=LEFT(D2,2)*1
公式2:=VLOOKUP(E2,省份代码!$A$2:$B$32,2,0)
2.提取生日
省份证中7至14位为生日,在函数输入公式:=MID(D2,7,8)
由于提取出来的日期不是标准日期,需要转化为标准日期。格式:0000!/00!/00
写成这样,格式能变化为:
3.计算准确年龄
在上面提取生日的基础上,可以计算出该人的年龄。
公式:=DATEDIF(G2,TODAY(),"Y")
=datedif(开始日期,结束日期,Y表示相差多少年)
datedif是隐藏函数,需要自己写完整参数
4.判断男女性
身份证第17位数是区分男女
公式1:=MID(D2,17,1),利用MID提取第17位数
公式2:=MOD(MID(D2,17,1),2)
利用MOD去识别奇数和偶数,结果1的是奇数,0为偶数
公式3:=IF(G2=1,"男","女")
利用身份证的特点可知,奇数为男性,偶数为女性
5.身份证15位数后变0
许多人输入身份证在15位后自动变0,相信许多人遇到这种烦恼。
提前设置单元格格式为本文,数据就不会变成0.
如果许多人经常输入少身份证数目,可以在【数据】【有效性】设置文本长度为18
6.设置生日倒计时
每个客户、员工生日都很重要,我们可以设置一个倒计时,提醒生日快到,提前准备礼物。
输入公式:=DATEDIF(G2,TODAY(),"YD")
=datedif(开始日期,结束日期,YD忽略年,表示相差多少天)
datedif是隐藏函数,需要自己写完整参数
本期有关身份证的内容就介绍到这里,想看更多干货知识,记得给我点赞、三连哦❤