|
每个人都有一张身份证，身份证号码中不仅包含了出生年月，还有性别等信息……如果给你几千条或几万条人员信息，你能快速的从身份证号码中提取出生年月，性别等信息吗？ 一、从身份证号码中提取出生年月。 方法： 在目标单元格中输入公式：=TEXT(MID(C3,7,8),'00-00-00')。 解读： 1、MId函数的作用为：从文本字符传中指定的起始位置返回指定长度的字符串。语法结构为：=Mid(字符串，起始位置，长度)。 2、Text函数的作用为：根据制定的数值格式将数值转换为文本。 3、公式：=TEXT(MID(C3,7,8),'00-00-00')中，首先用Mid函数提取C3单元格中从第7位开始，长度为8的字符，然后用Text函数设置成格式为“00-00-00”（暨年月日）的文本。 二、从身份证号码中提取性别。 方法： 在目标单元格中输入公式：=IF(MOD(MID(C3,17,1),2),'男','女')。 解读： 1、Mod函数的作用为：返回两数相除的余数。语法结构为：=Mod(被除数，除数)，例如=Mod(6,2)，返回值为0；=Mod(3,2)，返回值为1。 2、If函数的作用为：判断条件是否成立，如果成立返回一个值，否则返回另外一个值。 3、公式：=IF(MOD(MID(C3,17,1),2),'男','女')中，首先利用Mid函数提取C3单元格中字符串的第17位，然后除以2，返回余数（暨Mod函数的作用，其返回的结构只有0和1两种），最后用If函数判断余数，如果为“1”，返回“男”，如果为“0”，返回“女”。 三、从身份证号码中提取年龄。 方法： 在目标单元格中输入公式：=DATEDIF(D3,TODAY(),'y')。 解读： 1、Datedif函数是系统隐藏函数，其作用为按照指定的类型计算两个日期之间的差值。语法结构为：=Datedif(开始日期，结束日期，统计方式)，常见的“统计方式”有：“Y”、“M”、“D”；分别为“年”、“月”、“日”。 2、Today()函数的作用为获取当天的日期。 四、用身份证号码计算退休年龄。 方法： 在目标单元格中输入公式：=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)。 解读： 1、Edate函数的作用为返回指定日期之前或之后X月的日期。语法结构为：=Edate(指定日期，月份)。 2、公式：=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)中，首先用判断性别，如果为“男”性，则公式为：=Edate(D3，1*120+600)，暨从出生日期算起之后的720个月（暨60年）的日期；如果为“女性”，则公式为：=Edate(D3，0*120+600)，暨从出生日期算起之后的600个月（暨50年）的日期。 五、判断身份证号是否重复。 1、常规操作（错误办法）。 方法： 在目标单元格中输入公式：=IF(COUNTIF($C$3:$C$9,C3)>1,'重复','')。 解读： 1、Countif函数是单条件计数函数，其语法结构为：=Countif(条件范围，条件)。 2、利用Countif函数统计出当前值在C3:C9范围内数量，紧接着和1进行比较，并用If函数来判断，如果比较的结果成立，则返回“重复”，否则返回空值。 3、从单元格值中可以看出，两个身份证号并不重复，但是返回的结果为“重复”，Why？因为在Excel中的数字的有效位数为15位，超过15位的全部按“0”处理。而上述的两个号码中只有最后两位不同，故被Excel作为“0”处理，因此判断的结果为“重复”。 2、正确做法。 方法： 在目标单元格中输入公式：=IF(COUNTIF(C$3:C$9,C3&'*')>1,'重复','')。 解读： 1、此公式和“常规操作”的公式比较，只是在C3后添加了&'*'，但能得到正确的结果，Why？ 2、“*”在Excel中被称为通配符，起作用就是将当前的数字强制转换为文本，然后进行比较，从而得到了正确的结果。 六、防止身份证号重复。 方法： 1、选定目标单元格。 2、【数据】-【数据验证】，选择【允许】中的【自定义】，并在【公式】中输入：=COUNTIF(C$3:C$9,C3)=1。 3、完善提示信息并【确定】。 结束语： |
|
|
来自： hercules028 > 《excel》
