身份证号还能这么玩?利用Excel把身份证号玩出花

2017-01-18 15:59  出处:PConline原创  作者:唐山居人   责任编辑:censi 

  【PConline 技巧】身份证号中蕴含着很多信息,比方说大家都知道的生日,便是其中一个最经典的案例。其实除此之外,还有一些数据也是日常经常会用到的。将这些信息快速提取出来,不但能让工作更加高效,还能大大减少输入出错的机率。

身份证号码信息 Excel技巧
把身份证号码玩出花的Excel小技巧

  1. 提取生日

  函数:MID(从指定单元格中提取指定数量的字符)

  身份证号中第7位~14位数字就是每个人的出生日期,而MID函数的最终原理,就是将这8位数字提取出来,再通过对应的连字符连接起来。

  用法:MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)

  * MID(B2,7,4)提取的是出生年份(从B2单元格中的第7位数字开始,向后提取4位)

  * MID(B2,11,2)提取的是出生月份(从B2单元格的第11位数字开始,向后提取2位)

  * MID(B2,13,2)提取的是出生日期(从B2单元格的第13位数字开始,向后提取2位)

  * &”-”&是将提取到的数字用“-”连接起来,而非参与计算,最终可以得到“出生年-出生月-出生日”这样一种我们需要的格式

身份证号码信息 Excel技巧
提取生日示例

  2. 提取性别

  函数:MOD(判断两个单元格作除法运算后的余数)

  除了出生日期外,身份证号中的第17位数字是性别标识符。其中奇数为男性、偶数为女性。和生日提取不同,想要实现性别提取,仅靠单一函数是无法实现的,需要与IF函数搭配使用。

  用法:IF(MOD(MID(B2,15,3),2),"男","女")

  * MID(B2,15,3)提取的是身份证号中第15-17三位数字,即性别标识位

  * MOD(MID(B2,15,3),2)是将提取出来的数字与2取余,这样可计算出MID函数提取出来的数字到底是奇数还是偶数

  * IF(MOD(MID(B2,15,3),2),”男”,”女”))根据MOD函数得出的结果,奇数显示“男”,偶数显示“女”

身份证号码信息 Excel技巧
提取性别示例

  3. 计算年龄

  函数:YEAR(提取日期数据中年份部分)

  年龄计算其实应该算身份证计算的衍生公式了,同样无法借助单一函数实现。一般操作前,先利用MID函数将生日提取出来,然后借助YEAR函数得到当前年份与生日年份之间的差值即可。

  用法:YEAR(NOW())-YEAR(MID(B2,7,4))

  * NOW()提取当前日期

  * YEAR(NOW())将当前日期中年份信息提取出来,比如”1970/01/01”提取出来后就是”1970”

  * YEAR(MID(B2,7,4)):首先借助MID函数提取身份证号中的出生年份,然后再用YEAR函数提取当前年份,最后将两个年份相减,结果即为该员工当前年龄

身份证号码信息 Excel技巧
计算年龄

  注:最后再来个小贴士吧,有些网友可能会遇到这种情况,输入18位身份证号时出现“x.xxxxxE+17”这样的提示。这主要是因为单元格格式设置有问题,解决的方法是提前将单元格格式改为“文本”,然后再次输入数值即可。

身份证号码信息 Excel技巧
这种情况曾经让很多新入手Excel的网友头疼

身份证号码信息 Excel技巧
其实解决的方法,只要一步就可以!简单吧

相关阅读:

Excel表格怎么求差?Excel求差函公式使用方法

http://pcedu.pconline.com.cn/875/8755800.html

Excel教程:Excel文件如何快速转换为Word文档?

http://pcedu.pconline.com.cn/875/8755520.html

如何将excel文本转换为数字?这些技巧请收下

http://pcedu.pconline.com.cn/873/8736900.html

超简单一招!用Excel十秒快速制作工资条

http://pcedu.pconline.com.cn/817/8170320.html

 

excel如何筛选
BOSS刮目相看!让你效率增10倍的“神键”
BOSS刮目相看!让你效率增10倍的“神键”

很多朋友工作上都会用到Excel,不过Excel的一些使用技巧,并不人人都知道。掌握一些小技巧,甚至可以让效率提升10倍!今天,一起来看看Excel那些能让效率暴增的小技巧吧。...

唐山居人 2016-05-25 评论: 5 标签: excel合并单元格   excel怎么合并单元格  

你所不知道的!Excel 2013办公小技巧三则
你所不知道的!Excel 2013办公小技巧三则

相信不少人经常使用Office excel 2013软件办公,通畅在处理excel数据的时候都是非常麻烦的。但是如果你懂得一些excel技巧的话,会很让你的办公效率提高很多。...

唐山居人 2013-08-30 评论: 4 标签: office2003破解版   office兼容包  

Office免费?MAC用户不必费劲去找破解版
Office免费?MAC用户不必费劲去找破解版

微软校园先锋在近期搞一个免费赠送价值1999元的Office Mac 2011的促销活动。只要以微软校园先锋注册用户的身份登录Apple在线教育商店购买Apple商品,最高可省2300元,同时还有机会免...

xiaohu 2013-03-29 评论: 33 标签: Mac   excel合并单元格  

微软新规:Office 2013安装授权可转移
微软新规:Office 2013安装授权可转移

自微软正式发布Office 2013后,就有不少用户对微软限制Office 2013的安装、转移授权问题表示不满。近日,微软在其官方博客表示,称根据用户的反馈,微软决定更改Office 2013的零售许...

盛夏Veya 2013-03-07 评论: 19 标签: excel合并单元格   excel如何筛选  

更新网站架构 微软或将发布iOS版Office
更新网站架构 微软或将发布iOS版Office

据外媒近日报道,微软支持网站上包含了Excel for iPad、PowerPoint for iPad和Office Mobile for iPhone等各种非常明显的标签。这意味着微软正在研发iOS平台的Office软件。...

盛夏Veya 2012-12-11 评论: 23 标签: excel合并单元格   excel如何筛选  

卡巴斯基:警惕Excel宏病毒感染计算机
卡巴斯基:警惕Excel宏病毒感染计算机

Excel文档可以说是今天人们不可缺少的一种办公应用,由于其丰富的功能和方便的数据处理能力,受到大家的青睐。而也正是因为这些出色的能力和广泛的使用度,使其也成为了各种病毒、恶...

PConline 2012-10-26 评论: 0 标签: excel合并单元格   xls文件怎么打开  

加入HTML5支持 Excel网页版详细内容曝光
加入HTML5支持 Excel网页版详细内容曝光

微软在官方博客中展露了大量关于网页版Excel的消息和截图,网页版Excel依然充满着Metro元素,添加了HTML5支持。微软称用户可以在不同的设备上体验一样的Excel。...

Aimo 2012-07-24 评论: 6 标签: excel合并单元格   xls文件怎么打开