Excel教程下载和软件下载中心,Microsoft技术社区联盟成员,全球极具影响力的Excel门户,Office技术培训社区

看看身份证号能判断出多少信息吧

2019-12-21  作者:ExcelHome  阅读:
身份证号共18位,利用函数可以从中判断出很多信息。本文主要讲解利用函数判断性别,提取生日,判断周岁,虚岁,以及如何对身份证号中的出生日期部分隐藏为"********"

1、判断性别
身份证号的第17位数字,奇数为男性,偶数为女性,所以我们利用这一特点进行性别判断,公式如图1所示,下面我们进行公式分解讲解;


=MID(字符串,开始位置,字符个数),所以=MID(B2,17,1)就是从身份证号的第17位开始,提取1个数。
然后利用MOD函数,利用第17位数除以2,余数为0,则为偶数,余数为1,则为奇数。
最后利用IF函数,当余数结果为0时,输出"女",余数结果为1数,输出"男"。

2、生日提取
某公司人员信息表如下图2所示,人事主管要求你统计出员工生日,方便公司每月举办生日会。


这里如果我们直接用=TEXT(MID(F3,7,8),"0-00-00")的话,能统计出出生日期,但是它还是数值的格式,不方便我们进行筛选统计。所以这里我们用DATE函数进行统计,=DATE(年,月,日),利用MID函数提前身份证号第7~10位为年,第11~12位为月,第13~14位为日。最后将生日列的日期格式根据需要设置为"mm/dd"即可。


3、判断虚岁与周岁

本小节的主要思想是:计算虚岁只需当下的年减去出生的年即可;计算周岁时,需要判断身份证中的生日日期是否超过了当前的日期,没超过的话就是
=当前的年-出生的年
如果超过了,那么 就是:
=当前的年-出生的年-1


在同年初年的李红和张明来说,张明生日为12月24日,周岁为17,而李红生日为11月21日,周岁则为18.
对以上公式进行分解,=TODAY,返回当前的日期,即2019/12/4;=YEAR,返回日期的年份, =TODAY( YEAR)=2019; =MID(B2,7,4)就是张明的出生日期2001,最后进行相减便是张明的虚岁年龄18岁。
在核算周岁时,有几个需要注意点,一、我们需要使用一个DATEDIF函数,来判断出生日期与当前日期的相差日期,然后转化为以年为单位的表示方式;二、对于身份证中提取出的出生年月日,我们运用TEXT函数,转化为"yyyy-mm-dd"的形式,在TEXT函数中的表示方式为"0-00-00";

4、 隐藏 出生信息
对于身份证号中的8位出生年月日,我们在火车票上,网站信息上,都会看到其中八位数都是被"********"替换,那么这个功能是怎么实现的呢?


这里使用的是REPLACE函数,用法简单言之就是将指定字符串,从第几位开始的多少位数,用""号内的字符进行替换,=REPLACE(B2,7,8,"********")
就是将身份证号中,从第七位开始,连续八位数,用"********"进行替换,这样可以很好的保护大家的信息哦。

转载请注明来源"ExcelHome"并保留原文链接。
固定链接:http://www.excelhome.net/lesson/article/excel/1952.html
分享到:
Copyright 1999 - 2020 Excel Home.All Rights Reserved.
本站特聘法律顾问:徐怀玉律师 李志群律师   沪ICP备11019229号

沪公网安备 31011702000001号

征信