EXCEL中如从身份证号码提取出生年月日及性别.doc

EXCEL中如何从身份证号码提取出生年月日及性别 (2009-09-24 11:49:19) 转载▼ 标签： 电子表格 函数 it 分类： ??? 近段时间，各校都忙坏了，学生城镇居民医疗保险材料要保，今年必须提供每位学生的身份证号码，还有性别、出生日期等信息；新生学籍要报，学籍信息包含50多项，其中也有身份证号码、性别、出生日期等信息……。为了简化工作，提高效率，特整理出从身份证号码里提取出生年月日及性别等信息的方法，希望对大家有所帮助。 ??? 一、分析身份证号码 ??? 其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。 ??? 15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。 ??? 18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。 ??? 例如，某学生的身份证号码(15位)那么表示1989年8月7日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。 ??? 二、转换身份证号码格式 ??? 我们先将学生的身份证号完整地输入到EXCEL表格中，这时默认为“数字”格式（单元格内显示的是科学记数法的格式），需要更改一下数字格式。选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格式”。在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。 ??? 三、提取个人信息 ??? 这里，我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息。假设学生的身份证号码信息已输入完毕(C列)，出生年月信息填写在D列，性别信息填写在B列。 ??? ??? 如果上交报表时需要填写出生年月日，我们需要关心身份证号码的相应部位即可，即显示为这样的信息。在D2单元格中输入公式 ??? =IF(LEN(C2)=15,19MID(C2,7,6),MID(C2,7,8))，其中：??? 文本函数 ??? Text?? 是包含要提取字符的文本字符串。 ??? Start_num?? 是文本中要提取的第一个字符的位置。文本中第一个字符的start_num 为 1，以此类推。 ??? Num_chars?? 指定希望 MID 从文本中返回字符的个数。 ??? 字符个数计算函数LEN返回文本字符串中的字符数。语法：LEN(text) ??? Text?? 是要查找其长度的文本。空格将作为字符进行计数。 ??? LEN(C2)=15：检查C2单元格中字符串的字符数目，本例的含义是检查身份证号码的长度是否是15位。 ??? MID(C2,7,4)：从C2单元格中字符串的第7位开始提取6位数字，本例中表示提取15位身份证号码的第7、8、9、10、11、12位数字。 ??? 19:表示如果身份证号码是15位，在年份前加“19”。 ??? MID(C2,7,8)：从C2单元格中字符串的第7位开始提取8位数字，本例中表示提取18位身份证号码的第7、8、9、10、11、12、13、14位数字。 ??? IF(LEN(C2)=15,MID(C2,7,6),MID(C2,7,8))：IF是一个逻辑判断函数，表示如果C2单元格是15位，则提取第7位开始的6位数字并在年份前加“19”，如果不是15位则提取自第7位开始的8位数字。 ??? 如果需要显示为“1989-08-07”这样的格式，可以把上面的公式稍加修改即可： ??? =IF(LEN(C2)=15,19MID(C2,7,2)-MID(C2,9,2)-MID(C2,11,2),MID(C2,7,4)-MID(C2,11,2)-MID(C2,13,2))。 ??? ??? 也可以使用DATE格式，并在“单元格格式→日期”中进行设置。 ??? 如果学生的身份证号全部都是18位的新一代身份证，可以以把上面的公式进行简化： ??? =MID(C2,7,4)-MID(C2,11,2)-MID(C2,13,2) ??? ??? 由于报表中学生不是按照男、女固定的顺序进行编排，如果一个一个手工输入的话，既麻烦又容易出错。 ??? 例如性别信息统一在B列填写，可以在B2单元格中输入公式： ??? =IF(MOD(IF (LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,男,女) ??? 其中： ??? LEN