【原】总结篇-身份证号相关的4组函数公式

点击上方

蓝色

文字  关注我们吧！

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

在6月出的时候我写了一篇帖子--如何根据【身份证号码】判断【性别】，介绍了如何从身份证号中获取性别信息。

其实，身份证号提供给我们的信息还远远不止性别，我们还可以从中获取生日信息、年龄信息、退休年龄信息等等。

下面我们就来详细介绍一下如何提取以上这些信息吧！

01

性别信息

这个请点击上面的链接来查看，这里就不在详细介绍了。

02

生日信息

我们在单元格C2中输入

“=--TEXT((LEN(B2)=15)*19&MID(B2,7,6+(LEN(B2)=18)*2),"0000-00-00")”并向下拖曳即可。

思路：

• 这里用到了TEXT+MID函数，TEXT函数输出日期的格式为“0000-00-00”

• MID函数从身份证第7位开始提取字符

• 若身份证是15位的，MID函数提取的字符长度为6位，且需要利用LEN函数返回判断值True（即为1）再乘以19，补足18位

• 若身份证是18位的，MID函数提取的字符长度为6+2位，即8位；LEN函数返回判断值False（即为0），最后通过TEXT函数返回标准的日期格式

• --运算将文本格式转换为日期格式
  

03

年龄信息

在单元格D2中输入“=DATEDIF(C2,TODAY(),"y")”并下拉即可。

思路：

• 利用DATEDIF函数我们可以直接求出年龄，其作用是计算两个日期之间的差值。“y”表示年份

04

计算退休年龄

在单元格E2中输入

“=EDATE(C2,MOD(MID(B2,17,1),2)*120+600)”并下拉即可。

注意：若是15位身份证号码，则公式总“17”要被替换成“15”。

思路：

• 利用MID函数判断性别

• 利用MOD函数分别判断返回男性为60岁退休，女性为50岁退休

• 利用EDATE返回50年或60年后的日期
  

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

推荐阅读：

我能熟练运用的8组逆天的函数组合，你会几个？

你会将一维表格转换为二维表格吗？其实很简单！

学会POWER QUERY数据处理技巧，数据清洗只要一分钟！

根据等级加权计算总分--SUMPRODUCT和LOOKUP函数的组合

戳原文，更有料！免费模板文档！