怎么用身份证识别器在excel登记(身份证号还能这么玩)(1)

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

把身份证号码玩出花的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)

提取生日示例

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)

提取性别示例

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登记(身份证号还能这么玩)(4)

计算年龄

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

怎么用身份证识别器在excel登记(身份证号还能这么玩)(5)

这种情况曾经让很多新入手Excel的网友头疼

怎么用身份证识别器在excel登记(身份证号还能这么玩)(6)

其实解决的方法,只要一步就可以!简单吧

,