身份证号一键提取个人信息 excel 神技



对于职场人士、学校干部来说,总少不了与表格打交道,并为员工和同学们建立起电子档案。

想想整理几十个或上百个人身份证号等信息,是不是瞬间觉得工作量十分巨大、简直毫无人性?

身份证号一键提取个人信息 excel 神技

未经许可,不得转载,本文原文来自电脑学习网(www.yuucn.com

(员工档案,图/ 网络)

不过别怕!只要学会今天介绍的 6 个快捷技巧,就能仅凭一个身份证号,快速批量提取年龄、出生日期、性别等信息。

身份证号一键提取个人信息 excel 神技

毕竟众所周知,我们身份证上号码并不只是简单的数字,其中就包含了我们每个人的生日、年龄、性别等许多个人信息。

而一旦你掌握基础表格技巧,就能直接批量提取这些信息,无需再手工繁琐输入核对。

01.

出生年月,快捷键就能搞定

以前如果需要提取身份证的出生年月,我们可能会需要用到函数公式=MID(B2,7,8)。但现在,通过快捷键 Ctrl+E 轻松搞定。

因为身份证里面的第 7~14 位数字出生年月,我们只需要第一个单元格中,先手动输入第一个身份证的年月日信息,选中并按住Ctrl+E,WPS就能识别提取规则,即可批量自动填充。

身份证号一键提取个人信息 excel 神技

02.
年龄,也能即刻得出

既然身份证号码包含出生年份,那么直接用现在的年份减去出生年份,就能直接计算出每个人的实际年龄了。

以下图为例,我们在的 C2 单元格输入函数公式后下拉即可:=2019-MID(B2,7,4)

身份证号一键提取个人信息 excel 神技

对于公式,我们可以理解为 =MID(B2,7,4) 即提取 B2 单元格从第 7 位开始的 4 位数字,即身份证号上的出生年份。

=2019-MID(B2,7,4)  则是用现在年份 2019 减去身份证出生的年份,直接得出实际年龄。

03.
快捷提取性别信息

想必大家都不知道,其实仅靠身份证里面的第十七位数字,便能知道性别。即奇数代表男生,偶数就代表女生。

在 WPS 中,你可以选中目标单元格,选择选项卡中的公式-插入-函数,点击常用公式-提取身份证性别,接着选中身份证号码单元格即可。

身份证号一键提取个人信息 excel 神技

此外,你也可以直接通过公式计算。我们以下图为例,在 C2 单元格输入函数公式后下拉即可:=IF(MOD(MID(B2,17,1),2),”男”,”女”)

身份证号一键提取个人信息 excel 神技

稍微分解下,即 MID(B2,17,1) 是从身份证的第17位开始提取1位字符。而 MOD(MID(B2,17,1),2) 则是将提取的字符除以 2 的余数。

若余数为0,则说明身份证的第17位数是偶数,若余数不为0,则说明身份证的第17位数是奇数。算出余数后,再用 IF 函数进行判断,若是偶数,则为女性;若是奇数,则为男性。

04.
户籍信息快速提取

每个人的身份证号共有 18 位数字,其中前面 6 位数字为地址码,其中 1-2 位代表省,3-4 位代表地(市),5-6 位代表县(市)。

如果你需要提取户籍信息,除了公式之外,我们便需要引用表格中的《身份证号户籍对照表》工作表(文末有提供下载链接)。

以下图为例,在C2单元格输入函数公式后下拉即可:=VLOOKUP(VALUE(LEFT(B2,6)),身份证号户籍对照表!A:E,5,0)

身份证号一键提取个人信息 excel 神技

这个公式其实也不难理解,稍微分解下,LEFT(B2,6)是指提取身份证的前六位;VALUE(LEFT(B2,6)) 则是指将前六位字符转为数字格式。

接着再使用 VLOOKUP 函数从《身份证号户籍对照表》工作表中查找A列~E列 5 列中相对应的数据,如此一来便可快速提取详细户籍信息了。

05.

传统的生肖提取

虽然身份证号不会直接告诉你生肖信息,但我们也能通过计算规则,用简单的表格公式轻松实现。

以下图为例,在C2 单元格输入函数公式后下拉即可:=CHOOSE(MOD(MID(B2,7,4)-2008,12)+1,”鼠”,”牛”,”虎”,”兔”,”龙”,”蛇”,”马”,”羊”,”猴”,”鸡”,”狗”,”猪”)

身份证号一键提取个人信息 excel 神技

06.
星座信息提取

如果你想收集星座信息,我们则需要获取身份证月份和具体天数,也就是身份证号的第 11~14 位,然后配合公式便能轻松收集。

以下图为例,在C2单元格输入函数公式后下拉即可:

=LOOKUP(–MID(B2,11,4),{100;120;219;321;421;521;622;723;823;923;1023;1122;1222},{“摩羯座”;”水瓶座”;”双鱼座”;”白羊座”;”金牛座”;”双子座”;”巨蟹座”;”狮子座”;”处女座”;”天秤座”;”天蝎座”;”射手座”;”摩羯座”})

身份证号一键提取个人信息 excel 神技

在这个公式里,MID(B3,11,4) 即是指获取身份证号第 11~14 位出生月日;– 是把文本转为数值;LOOKUP():则是根据出生月日,找到星座的临界值,进而匹配星座。

本文未经电脑学习网书面授权,不得进行转载,请知晓




除了特别申明外,本站资源均来自互联网公开内容,如果侵犯了您的权益,请进行投诉
电脑学习网 » 身份证号一键提取个人信息 excel 神技

发表评论