身份证号相关的6个函数公式.pdf

身份证号相关的 6 个函数公式 身份证号码不单单是一串简单的数字，其蕴藏着很多重要的信息，例如，出生年月、性别 等……那么，如何在 Excel 中提取身份证号码中的出生年月、性别等信息呢？不妨从学习下 文开始吧…… 一、Text+Mid：提取出生年月。 方法： 在目标单元格中输入公式：=TEXT(MID(C3,7,8),"00-00-00")。 解读： 1、身份证号码中从第 7 位开始，一直到第 15 位结束为出生年月。 2、Mid 函数的作用是：从指定的字符串中提取指定长度的字符。其语法结构为：=Mid(字符 串或引用，提取开始位置，字符长度)。 3、Text 函数的作用是：对指定的字符串按照指定的格式进行设置。 二、If+Mod+Mid：提取性别。 方法： 在目标单元格中输入公式：=IF(MOD(MID(C3,17,1),2),"男","女")。 解读： 1、身份证号码中的第 17 位代表的为性别，如果÷2 余数为 1，则为“男”，如果÷2 余数为 0， 则为“女”。 2、首先利用 Mid 函数提取第 17 位上的值，然后利用 Mod 函数求余，最后用 If 函数判断求 余的结果，如果余数为 1，则返回“男”，如果余数为“0”，则返回“女”。 三、Datedif 函数：计算年龄。 方法： 在目标单元格中输入公式：=DATEDIF(D3,TODAY(),"y")。 解读： 1、Datedif 函数为系统隐藏函数。其作用为按照指定的统计方式计算两个日期之间的差值。 其中“y”为“年”，暨计算两个日期相差的年份。 2、语法结构：=Datedif(开始日期，结束日期，统计方式)。 3、示例中用 Today()函数替代“结束日期”。 四、Edate 函数：计算退休年龄。 方法： 在目标单元格中输入公式：=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)。 解读： 1、Edate 函数的作用为：返回一串日期，指示起始日期之前或之后的月份。 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 年的日期。 五、If+Countif：判断身份证号码是否重复。 1、一般方法（错误方法）。 方法： 在目标单元格中输入公式：=IF(COUNTIF(C$3:C$9,C3)>1,"重复","")。 解读： 1、从单元格中的身份证号码中可以看出并不重复，而且公式也没有错误，但返回的结果为 “重复”，Why？？？ 2、在 Excel 中，数值的有效位数为 15 位，暨超过 15 位的值被视为 0 处理，而单元格中的 身份证号只有后 3 位不同，被视为 0 处理，所以返回的结果为“重复”。 2、正确做法：添加“*”。 方法： 在目标单元格中输入公式：=IF(COUNTIF(C$3:C$9,C3&"*")>1,"重复","")。 解读： 对两个公式进行比较，发现只是在计数条件的后面添加了“*”，其实在 Excel 中，“*”被称为通 配符，加上通配符的作用就是将当前的数值内容强制转换为文本，然后进行对比得到正确的 结果。 六、If+Countif：防止重复。 方法： 1、选定目标数据源，【数据】-【数据验证】。 2、选择额【允许】中的【自定义】，并在【公式】中输入：=COUNTIF(C$3:C$9,C3&"*")=1。 3、完善警告信息！ 结束语：此文从提取出生年月、性别、计算年龄及退休年龄，防止身份证号重复等方面做了 详细的介绍，是职场办公人员必备的技能之一哦！