关于在Excel中何自动提取出生年月日.docVIP

关于在Excel中如何自动提取出生年月日、性别及年龄的计算方法 假若E2单元格内是身份证号，那么只要在所求单元格处用到如下的函数就可以了： 出生年月日： IF LEN E2 15, 19MID E2,7,2 年MID E2,9,2 月MID E2,11,2 日 , MID E2,7,4 年MID E2,11,2 月MID E2,13,2 日 性别： IF MOD IF LEN E2 15,MID E2,15,1 ,MID E2,17,1 ,2 1,男,女 年龄： YEAR TODAY -YEAR C2 （方案1） DATEDIF SUBSTITUTE A1,.,- -1,TODAY ,Y （方案2） YEAR NOW -LEFT A2,4 （方案3） 详细解释： 一、分析身份证号码 其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。 15位身份证号码：第7、8位为出生年份 两位数 ，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。 18位身份证号码：第7、8、9、10位为出生年份 四位数 ，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。 例如，某员工的身份证号码 15位 是座机电话号码0807024，那么表示1972年8月7日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。 二、提取个人信息 这里，我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。如图1所示，其中员工的身份证号码信息已输入完毕 C列 ，出生年月信息填写在D列，性别信息填写在B列。 图1 输入身份证号码 1．提取出生年月信息 由于上交报表时只需要填写出生年月，不需要填写出生日期，因此这里我们只需要关心身份证号码的相应部位即可，即显示为“7208”这样的信息。在D2单元格中输入公式“ IF LEN C2 15,MID C2,7,4 ,MID C2,9,4 ”，其中： LEN C2 15：检查C2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。 MID C2,7,4 ：从C2单元格中字符串的第7位开始提取四位数字，本例中表示提取15位身份证号码的第7、8、9、10位数字。 MID C2,9,4 ：从C2单元格中字符串的第9位开始提取四位数字，本例中表示提取18位身份证号码的第9、10、11、12位数字。 IF LEN C2 15,MID C2,7,4 ,MID C2,9,4 ：IF是一个逻辑判断函数，表示如果C2单元格是15位，则提取第7位开始的四位数字，如果不是15位则提取自第9位开始的四位数字。 如果需要显示为“70年12月”这样的格式，请使用DATE格式，并在“单元格格式→日期”中进行设置。 2．提取性别信息 由于报表中各位员工的序号编排是按照上级核定的编制进行的，因此不可能按照男、女固定的顺序进行编排，如果一个一个手工输入的话，既麻烦又容易出错。 例如性别信息统一在B列填写，可以在B2单元格中输入公式“ IF MOD IF LEN C2 15,MID C2,15,1 ,MID C2,17,1 ,2 1,男,女 ”，其中： LEN C2 15：检查身份证号码的长度是否是15位。 MID C2,15,1 ：如果身份证号码的长度是15位，那么提取第15位的数字。 MID C2,17,1 ：如果身份证号码的长度不是15位，即18位身份证号码，那么应该提取第17位的数字。 MOD IF LEN C2 15,MID C2,15,1 ,MID C2,17,1 ,2 ：用于得到给出数字除以指定数字后的余数，本例表示对提出来的数值除以2以后所得到的余数。 IF MOD IF LEN C2 15,MID C2,15,1 ,MID C2,17,1 ,2 1,男,女 ：如果除以2以后的余数是1，那么B2单元格显示为“男”，否则显示为“女”。 回车确认后，即可在B2单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。如图2所示，现在这份报表无论是提取信息或是核对，都方便多了！ 图2 提取个人信息 在A2单元格求出出生年份（如1970-5），则在其他单元格中输入下面公式，就能算出年龄。 IF TODAY DATE YEAR NOW ,MONTH A2 ,DAY A2 ,YEAR NOW -YEAR A2 ,YEAR NOW -YEAR A2 -1 这是根据身份