返回目录:excel表格制作
众所周知,我国18位的身份证号码蕴含着很多个人信息,最为人们熟知的就是出生日期和年龄。其实不光这些,通过Excel软件我们还可以批量的提取出性别、籍贯、生肖属相、星座等多项有效信息。接下来我们一一展示给大家。
①出生日期
出生日期是最简单的,我们看下结果。
然后我们进入操作,出生日期处于身份证号的第7—14位,很明显我们需要用文本提取函数——MID函数。
公式:MID(A2,7,8)
如果就这样结束,未免太小儿科了,格式我们需要改善一下。于是想到了TEXT函数。
公式:=TEXT(MID(A2,7,8),"0000年00月00日")
TEXT函数就是可以把参数更改为我们需要的格式,第二个参数就是更改的格式类型。
②年龄
其实年龄比出生日期要简单,我们看下效果。
我们只需要用今年(2019)减去身份证号中的年份即可,年份依然可以用MID函数提取出来。
公式:=2019-MID(A2,7,4)
③性别
性别是有身份证号的第17位决定的,我们先看下效果。
第17位数字假如是奇数,则为男性;如果是偶数,则为女性。很明显这是一个IF逻辑函数,我们唯一需要想的是条件如何去判定,也就是如何判定奇偶数。
其实方法很多,这里我推荐用除以2计算余数的方式,假如余数为1,则为奇数;余数为0,则为偶数。自然而然我们想到MOD函数,计算余数。
公式:MOD(MID(A2,17,1),2)
有了判定条件,我们直接把它剪切进IF嵌套函数即可。
公式:=IF(MOD(MID(A2,17,1),2)=1,"男","女")
④籍贯
籍贯主要看身份证号的前6位,前两位是省级,三四位是市级,五六位是区级。我们看下效果。
无论如何我们第一步都需要先把前6位提取出来,用一个很简单的LEFT文本提取函数。
公式:LEFT(A2,6)
接下来我们去借助地区编码(网上可以下载到)去查找引用,VLOOKUP函数上场。
公式:=VLOOKUP(LEFT(A2,6),地区编码!A:B,2,0)
⑤生肖属相
生肖属相属于我们这一期最复杂的一组函数了,我们需要用半理解半记忆的方式去学习,因为很多也是实践出来的,我们先看下结果。
先给大家介绍一款函数,叫做CHOOSE函数,属于查找引用函数。我们先操作,再解释。
公式:CHOOSE(3,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")
其实CHOOSE函数很容易理解,第一个参数为数值(1—254区间),数值为多少,那么最后返回后面的第几位参数内容。那我们只需要去考虑如何计算出CHOOSE函数的第一个参数即可。
生肖属相12年为一个轮回,并且和年份有关,这里我们使用一个比较早的鼠年年份,比如1888年,并且进行以下操作,这里需要大家半理解半记忆了,因为也是实践出的结果。
公式:MOD(MID(A2,7,4)-1888,12)+1
这个公式计算出来就是CHOOSE函数的第一个参数,我们嵌套进去。
这样我们就分别把出生日期、年龄、性别、籍贯、属相这五种信息,通过Excel函数的方式提取出来了。
星座的话我们下期再讲解,因为有点小复杂,需要单独做一篇文章来说明。谢谢大家的观看,谢谢。
如果喜欢Excel,可以看下我的全套原创视频教程。
↓↓↓基础知识+数据透视+函数公式+动态图表↓↓↓
专栏购买价格偏高一些,因为平台分成问题。
我们更建议您在 淘宝店铺:云端网校(搜店铺,搜店铺,不是搜商品) 进行购买。价格便宜一些,具体淘宝观看流程,咨询客服即可。