表格提取出生日期公式后格式设置表格提取出生日期公式后格式设置不了

来源：最后更新：24-05-19 06:05:42

导读：身份证号，各位都不陌生，但是对于其包含的消息可能并不是特别了解，除了总是应该看见的诞生年月，性别外，还包含哪些消息？如果要在Excel中进行计算，大概怎么去操作呢？一、正确输入身份证消息号码。目的：在

身份证号，各位都不陌生，但是对于其包含的消息可能并不是特别了解，除了总是应该看见的诞生年月，性别外，还包含哪些消息？如果要在Excel中进行计算，大概怎么去操作呢？

一、正确输入身份证消息号码。

目的：在单元格中正确的输入身份证消息号码。

表格提取出生日期公式后格式设置（身份证转换出生年月日通用公式）

方法：

首先输入“’”（单撇号）,紧接着输入身份证消息号码。

解读：

1、Excel最长能够保存的数字为15位，而身份证消息号码为18位，所以默认状态下输入身份证消息号码后后三位数被代替为0。

2、除了在号码前添加“’”（单撇号）外，还应该提早将单元格的格式设置为“文本”。

二、从身份证消息号码中提取诞生年月。

1、公式法。

表格提取出生日期公式后格式设置（身份证转换出生年月日通用公式）

方法：

在目标单元格中输入公式：=TEXT(MID(C3,7,8),”00-00-00″)。

解读：

1、分析身份证消息号码，发现从第7位开始长度为8的字符串为“诞生年月”。所以需要将其从身份证消息号码中提取出去。

2、Mid函数的作用为：返回文本字符串中从指定地点开始长度为指定值的字符。语法结构为：=Mid(字符串,开始地点,字符长度)。如=MID(C3,7,8)返回的结果为“19650821”。

3、如果仅用Mid函数提取身份证消息号码中的8为数字，其并不是“诞生年月”，因为诞生年月是“年月日”的形式，所以需要对其进行格式设置，而Text函数的作用为根据指定的代码对指定的值进行格式设置，语法结构为=Text(字符串,格式代码)，之中“00-00-00”为日期格式代码。

2、方便键法。

表格提取出生日期公式后格式设置（身份证转换出生年月日通用公式）

方法：

1、在第一个目标单元格中输入第一个体诞生年月的8位数字。

2、选取目标单元格，包括第一个输入内容的单元格，方便键Ctrl+E。

3、【资料】-【分列】-【下一步】-【下一步】，选择【列资料格式】中的【日期(YMD)】并【完成】。

解读：

如果不对AI智能填充的值【分列】，则仅仅为8为数字，并不是“诞生年月”。

三、根据身份证消息号码计算性别。

表格提取出生日期公式后格式设置（身份证转换出生年月日通用公式）

方法：

在目标单元格中输入公式：=IF(MOD(MID(C3,17,1),2),”男”,”女”)。

解读：

1、分析身份证消息号码不难发现，性别掩藏在第17位，如果为“奇数”，则为“男”，否则为“女”。

2、Mod函数的作用为计算两个数字相除的余数，语法结构为：=Mod(被除数,除数)。如=Mod(5,2)的返回结果为1。

3、公式=IF(MOD(MID(C3,17,1),2),”男”,”女”)中首先用Mid函数提取身份证消息号码的第17位，之后将返回的值作为Mod函数的被除数，除数为2，并将Mod函数的计算结果作为IF函数的判断条件，如果条件成立，则返回“男”,否则返回“女”。

四、根据身份证消息号码计算年年。

表格提取出生日期公式后格式设置（身份证转换出生年月日通用公式）

方法：

在目标单元格中输入公式：=DATEDIF(D3,TODAY(),”y”)。

解读：

1、Datedif函数为系统掩藏函数，其功能为根据指定的计算方法计算两个日期之间的差值。语法结构为：=Datedif(开始日期,结束日期,统计方法)。常用的统计方法有“Y”、“M”、“D”，分别为“年”、“月”、“日”。

2、日常生活中，问“多少岁了”指的是从诞生日期到今天经历过了多少年。所以结束日期用Today()函数获取，只要你打开表格，其年年时全自动更新的，而不是不定不变的。如果你的年年要按“月”为单位统计，则只需将公式中的“y”代替为“m”就可。

3、如果给定的表单中没有“诞生年月”列，此时此刻就先要从身份证消息号码中提取诞生年月，之后再计算年年，此时此刻的公式为：=DATEDIF(TEXT(MID(C3,7,8),”00-00-00″),TODAY(),”y”)，一步到位。

五、使用身份证消息号码计算退休年年。

（以男60岁、女50岁退休为例）

表格提取出生日期公式后格式设置（身份证转换出生年月日通用公式）

方法：

在目标单元格中输入公式：=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)。

解读：

1、Edate函数的作用为：返回指定日期之后数月的日期，语法结构为：=Edate(开始日期,月份)。如=Edate(“2021/4/19”,12)的返回结果为：2021/4/19。

2、公式=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)中，首先判断其性别，如果为男性，则在D3的基础上+1*120+600个月，如果为女性，则在D3的基础上+0*120+600个月。

3、如果给定的表单中没有“诞生年月”列，此时此刻就先要从身份证消息号码中提取诞生年月，之后再计算退休年年，此时此刻的公式为：=EDATE(TEXT(MID(C3,7,8),”00-00-00″),MOD(MID(C3,17,1),2)*120+600)。

4、如果根据“男60岁，女55岁”计算，则公式为：=EDATE(D3,MOD(MID(C3,17,1),2)*60+660)。

六、判断身份证消息号码是否重复。

1、常规操作（错误方法）。

表格提取出生日期公式后格式设置（身份证转换出生年月日通用公式）

方法：

在目标单元格中输入公式：=IF(COUNTIF(C$3:C$9,C3)>1,”重复”,””)。

解读：

公式中用Countif函数统计目前值在指定周围内的个数，如果＞1，返回“重复”……其实思路没有所有问题，但重要时机在于Excel中的数值精度，目前只支持15位，15位未来的值全部根据0来对待，所以统计出的结果为“重复”。

2、正确做法。

表格提取出生日期公式后格式设置（身份证转换出生年月日通用公式）

在目标单元格中输入公式：=IF(COUNTIF(C$3:C$9,C3&”*”)>1,”重复”,””)。

解读：

和上述的公式相有那么一点，只是在C3后添加了“&”*””，但结果截然不一样，Why？其实很无脑，只是加上通配符“*”之后，原来的数值变为了文本。

七、禁止录入重复的身份证消息号码。

表格提取出生日期公式后格式设置（身份证转换出生年月日通用公式）

方法：

1、选定目标单元格。

2、【资料】-【资料验证】，选择【允许】中的【自己定义设置】，在【公式】中输入：=COUNTIF(C:C,C3&“*”)=1。

3、单击【出错警告】标签，录入警告消息并【确认】。

八、根据身份证消息号码查询引用。

表格提取出生日期公式后格式设置（身份证转换出生年月日通用公式）

方法：

在目标单元格中输入公式：=VLOOKUP(G3,C3:D9,2,0)。

解读：

1、Vlookup函数为常见的查询引用函数之一，其语法结构为：=Vlookup(查询值,资料周围,返回列数,匹配模式)。当匹配模式为“0”时为准确匹配，为“1”时为模糊匹配。

2、由于每一个体的身份证消息号码是唯一的，所在身份证消息号码只是“索引值”，代表唯一的数值，混合Vlookup或Lookup等获取很多的消息。

结束语：

从文中应该看出，身份证消息号码中掩藏着很多消息，除了车身年月、性别外，还应该计算年年、退休年年、作为“索引值”等……对于使用技术，你Get到了吗？如果亲有再多的关于身份证消息号码的应用，不如在留言区留言研究哦！

标签: [db:关键词]

免责声明：本文系转载，版权归原作者所有；旨在传递信息，其原创性以及文中陈述文字和内容未经本站证实。

本文地址:http://www.bbbaike.com/news/jiaodian/443075.html

proe工程图比例设置（proe工程图图框比例）

网站首页 返回栏目

色彩搭配图衣服色彩搭配图

表格提取出生日期公式后格式设置表格提取出生日期公式后格式设置不了

一、正确输入身份证消息号码。

方法：

解读：

方法：

解读：

方法：

解读：

方法：

解读：

方法：

解读：

方法：

解读：

方法：

解读：

解读：

方法：

方法：

解读：

结束语：

栏目导航

怀孕周期

热门标签

热门文章

表格提取出生日期公式后格式设置 表格提取出生日期公式后格式设置不了

一、正确输入身份证消息号码。

方法：

解读：

方法：

解读：

方法：

解读：

方法：

解读：

方法：

解读：

方法：

解读：

方法：

解读：

解读：

方法：

方法：

解读：

结束语：

栏目导航

怀孕周期

热门标签

热门文章

表格提取出生日期公式后格式设置表格提取出生日期公式后格式设置不了