身份证提取出生年月及计算周岁年龄等信息的Excel公式

ABCDEFGHI
序号姓名身份证号码性别出生年月周岁退休年龄距离退休天数距离退休时间
1张三1202231935122701741935-12-27841995年12月27日
2李四1202231930052701641930-05-27891980年5月27日
3王五1202231928112601761928-11-26911988年11月26日
4赵六1202231970091101661970-09-11492020年9月11日1620年5个月12天

一、提取性别

如上图,在C列输入身份证号码,在D2单元格输入:从输入的身份证号码内让系统自动提取性别,可以输入以下公式:

=IF(MOD(MID(C2,17,1),2),”男”,”女”)

公式内的“C2”代表的是输入身份证号码的单元格。

解读:

  • 1、首先用Mid函数提取第17位上的数字。
  • 2、用Mod函数求模取余
  • 3、用If函数判断求模取余的结果,如果为奇数,返回“男”,如果为偶数,返回“女”。

二、提取出生年月

如上图,从输入的18位身份证号中提取出生年月的计算公式,在E2单元格输入:

=TEXT(MID(C2,7,8),”00-00-00″)

——C2为输入的18位身份证号的单元格。

解读:

  • 1、Mid函数的主要作用是从指定字段的指定位置提取指定长度的字符串。语法结构为:=Mid(字符串,起始位置,字符串长度)。
  • 2、Text函数的主要作用是将字符串设置为指定的格式。

三、计算年龄

如上图,根据出生日期自动计算周岁,在F2单元格输入:

———假设E列存放学生的出生日期(可以这样输入:比如2010-11-27),F列输入该函数后则产生该生的周岁。

=TRUNC((DAYS360(E2,NOW( )))/360,0)

———假如要计算到2010/11/1的周岁年龄。E列为出生年月所在的列。就用以下的公式:

=TRUNC((DAYS360(E2,”2010/11/1″,FALSE))/360,0)

解读

1.TRUNC 函数:将数字的小数部分截去,返回整数。

TRUNC(n1,n2),n1表示被截断的数字,n2表示要截断到那一位。n2可以是负数,表示截断小数点前。注意,TRUNC截断不是四舍五入。int函数处理负数时,是将负数向下舍入到最接近的整数。

2.DAYS360 函数:按照一年 360 天的算法(每个月以 30 天计,一年共计 12 个月),返回两个日期间相差的天数。

四、计算退休年龄。

方法一:在目标单元格中输入公式:

=EDATE(E2,MOD(MID(C3,17,1),2)*120+600)。

E2为出生年月,C3为身份证号码。

解读:

1、Edate函数的主要功能为:返回一串日期,指示起始日期之前或之后的月数。

2、此处的计算规则为:男工作60年退休,女50年退休。首先用Mod函数判断性别,如果为“男”,则在出生日期的基础上加上1*120+600=720个月,也就是60年。如果为“女”,则在出生日期的及出生加上0*120+600=600个月,也就是50年。

方法二:在目标单元格中输入公式:

=EDATE(E2,IF(D2=”女”,55*12,60*12))

E2为出生年月,D2为性别;女性退休年龄55,男性退休年龄60。

五、距离退休时间倒计时(精确到年月日天)

我的思路是在H列计算出天数,再在I列把天数转为年月日。

1.H列输入公式:

=G5-TODAY()

2.I列输入公式:

公式一:

=INT(H5/365)&”年”&INT((H5-INT(H5/365)365)/30)&”个月”&(H5-INT(H5/365)365-INT(((H5-INT(H5/365)365)/30))30&”天”)

INT函数:将数字向下舍入到最接近的整数。

  • INT函数是取整函数;
  • 不进行四舍五入直接去掉小数部分取整;
  • INT函数处理负数的小数时总是向上进位的。

公式二:

=ROUNDDOWN(H5/365,0)&”年”&ROUNDDOWN(MOD(H5,365)/30,0)&”个月”&MOD(MOD(H5,365),30)&”天”

rounddown函数是向下取整,所以不遵循四舍五入原则,所以得出的结果是省略位数的取整值。

六、如何查询重复的身份证号码

如果是Excel中普通的筛选突出重复值,可以使用 “突出显示单元格规则”——重复值”。但是由于身份证号码的特殊性,使用此操作技巧行不通。

关于Excel查找身份证重复的正确方法是使用函数来解决。

B2单元格公式为:=IF(COUNTIF(A:A,A2&”*”)>1,”重复”,””),下拉即可将重复的标识出来。

可能有些小伙伴会问,这样写公式怎么不对:=IF(COUNTIF(A:A,A2)>1,”重复”,””)

原因在于excel表中只能保留15位有效数字,超过15位后的数字全部显示为0;即使是文本格式,COUNTIF函数在运算时,将文本型数字当作数值来处理;所以我们为了精确无误就需要连接通配符&”*”,让Excel强行识别为文本进行统计。

切记:countif函数在处理时会将文本数值识别为数值进行统计,在excel中超过15位的数值只能保留15位有效数字,后3位全部置为0,因此超过15位的号码后面都识别为相同,使用通配符&”*”的目的是使其强行识别为文本进行统计。所以写excel查找重复身份证号公式的时候要特别注意。

发表评论

电子邮件地址不会被公开。 必填项已用*标注