EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

2019-06-2809:37:09办公软件与工具应用Comments20,613 views字数 1514阅读模式
EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

身份证号码不单单是一串简单的数字,其蕴藏着很多重要的信息,例如,出生年月、性别等……那么,如何在Excel中提取身份证号码中的出生年月、性别等信息呢?不妨从学习下文开始吧……文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html


一、Text+Mid:提取出生年月。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

在目标单元格中输入公式:=TEXT(MID(C3,7,8),"00-00-00")。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

解读:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

1、身份证号码中从第7位开始,一直到第15位结束为出生年月。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

2、Mid函数的作用是:从指定的字符串中提取指定长度的字符。其语法结构为:=Mid(字符串或引用,提取开始位置,字符长度)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

3、Text函数的作用是:对指定的字符串按照指定的格式进行设置。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html


二、If+Mod+Mid:提取性别。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

在目标单元格中输入公式:=IF(MOD(MID(C3,17,1),2),"男","女")。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

解读:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

1、身份证号码中的第17位代表的为性别,如果÷2余数为1,则为“男”,如果÷2余数为0,则为“女”。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

2、首先利用Mid函数提取第17位上的值,然后利用Mod函数求余,最后用If函数判断求余的结果,如果余数为1,则返回“男”,如果余数为“0”,则返回“女”。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html


三、Datedif函数:计算年龄。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

在目标单元格中输入公式:=DATEDIF(D3,TODAY(),"y")。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

解读:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

1、Datedif函数为系统隐藏函数。其作用为按照指定的统计方式计算两个日期之间的差值。其中“y”为“年”,暨计算两个日期相差的年份。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

2、语法结构:=Datedif(开始日期,结束日期,统计方式)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

3、示例中用Today()函数替代“结束日期”。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html


四、Edate函数:计算退休年龄。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

在目标单元格中输入公式:=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

解读:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

1、Edate函数的作用为:返回一串日期,指示起始日期之前或之后的月份。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

2、公式:=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)是按照男同志60岁退休,女同志50岁退休来计算的,具体过程为:首先判断性别,如果为“男”,则在生日的基础上加上1*120+600=720(月),暨60年;如果为“女”,则在生日的基础生加上0*120+600=600(月),暨50年;然后用Edate函数返回生日之后60年或50年的日期。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html


五、If+Countif:判断身份证号码是否重复。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

1、一般方法(错误方法)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

在目标单元格中输入公式:=IF(COUNTIF(C$3:C$9,C3)>1,"重复","")。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

解读:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

1、从单元格中的身份证号码中可以看出并不重复,而且公式也没有错误,但返回的结果为“重复”,Why???文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

2、在Excel中,数值的有效位数为15位,暨超过15位的值被视为0处理,而单元格中的身份证号只有后3位不同,被视为0处理,所以返回的结果为“重复”。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html


2、正确做法:添加“*”。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

在目标单元格中输入公式:=IF(COUNTIF(C$3:C$9,C3&"*")>1,"重复","")。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

解读:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

对两个公式进行比较,发现只是在计数条件的后面添加了“*”,其实在Excel中,“*”被称为通配符,加上通配符的作用就是将当前的数值内容强制转换为文本,然后进行对比得到正确的结果。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html


六、If+Countif:防止重复。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

1、选定目标数据源,【数据】-【数据验证】。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

2、选择额【允许】中的【自定义】,并在【公式】中输入:=COUNTIF(C$3:C$9,C3&"*")=1。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

3、完善警告信息!文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html


结束语:
此文从提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复等方面做了详细的介绍,是职场办公人员必备的技能之一哦!文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

作者:Excel函数公式
来源:知乎文章源自菜鸟学院-https://www.cainiaoxueyuan.com/office/13856.html

  • 本站内容整理自互联网,仅提供信息存储空间服务,以方便学习之用。如对文章、图片、字体等版权有疑问,请在下方留言,管理员看到后,将第一时间进行处理。
  • 转载请务必保留本文链接:https://www.cainiaoxueyuan.com/office/13856.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定