在Excel中提取身份证出生年份及判断性别.doc

在Excel中提取身份证出生年份及判断性别摘#8195;要：本文在介绍身份证号码组成的基础上，从身份证号码的输入、出生年份的提取、性别的判断这三个步骤阐述了如何在Excel中运用公式函数对身份证号码中的出生年份进行提取，并判断性别。 关键词：身份证号码；函数；出生年份；性别 在工作中，我们常要对身份证号码中的出生年份进行提取，并对性别进行判断，在Excel中可以运用函数进行处理。首先我们来了解身份证号码的组成：旧的身份证号码有15位，新的身份证号码有18位。新增第7、第8、第18三位。排列顺序从左至右依次为：6位数字地址码，8位数字出生日期码，3位数字顺序码和1位数字校验码。其中前两位表示省、自治区或直辖市，第3、第4两位表示所在的市，第5、第6两位表示所在的县区；第7~14位表示出生年月日；第15位至第17位为顺序码，第17位表示性别，一般男的用奇数表示，女的用偶数表示；第18位表示校验码。 一、身份证号码的输入 要对身份证号码进行处理，首先要输入身份证号码。当输入的数字超过11位时，Excel会自动默认为科学记数法。如果我们直接输入身份证号码，Excel会自动转换为科学记数法，所以我们要以文本的方式输入数字。可以在输入数字之前把所要输入数字的单元格设置为文本格式，操作如下：选取要输入数字的单元格，选择“格式”菜单，选择“单元格”命令，然后选择“数字”选项卡，选择“文本”一项，确定。或者在输入的数字前加“”，Excel会自动把后面的数字以文本输入。 二、出生年份的提取 以身份证号码是18位为例子，数据如上图录入，在A2输入出生年月公式为=MID(C2,7,4)，返回值为1980。函数MID是从文字中某一指定起点位值开始，返回指定字符长度的文字串。上面的公式是指C2中从第7位字符开始，取4位数字，所以返回值为1980。 如果身份证号码是15位，则身份证号码变为340524800101001，那出生年月公式为=19MID(C2,7,2)，其中是文本运算符，起到把文本连接起来的作用，MID(C2,7,2)指从C2中第7位开始取2位数字，返回值为80，1980，为“1980”。 当一个表格中既含有15位又含有18位数字的身份证号码时，我们可以先对身份证号码的数字长度作一个判断，所用公式为： =LEN(C2): 结果返回的是C2单元格的数字的位数。我们可以把以上公式综合，无论身份证号码是15位还是18位数字，都可以用以下公式来提取出生年份： =IF(LEN(C2)=18, MID(C2,7,4), 19MID(C2,7,2))) 以此类推，提取出生年月的公式为： =IF(LEN(C2)=18, MID(C2,7,6), 19MID(C2,7,4))) …… 三、性别的判断 性别的提取相对复杂点，以18位身份证作为例子，其第17位数字表示性别，奇数为男性，偶数为女性。首先我们要通过公式提取第17位数，再通过函数判断是奇数还是偶数，最后通过奇偶数的结果判断性别。仍以上述例子为例，我们一步步来操作： （1）先提取第17位数字： =MID(C2,17,1) 这个例子返回值为1。 （2）判断第17位数是奇数还是偶数： =MOD（MID(C2,17,1),2） MOD函数返回两数相除的余数，如=MOD（5，2）所指的是返回5除以2的余数，返回值为1。我们可以通过除以2的余数来判断奇偶数，余数为1，为奇数；余数为0，则为偶数。 这个例子17位数为“1”，1除以2余数为1，所以判断的结果为“奇数”。 （3）根据奇偶结果来判断性别。 用IF函数，如果余数为0，返回结果为“女”，余数不是0，返回结果为“男”。 这个例子所判断的结果为“奇数”，所以返回结果为“男”，本例子所用的身份证号码是一个男性的号码。 综合以上，我们可以直接在B2输入公式： =IF(MOD(MID(C2,17,1),2)=0,”女”,”男”) 如果身份证号码是15位数字，由于第15位是表示性别的，所以公式为： =IF(MOD(MID(C2,15,1),2)=0,”女”,”男”) 我们把公式综合，无论身份证号码是15位还是18位数字，都可以用以下公式来判断性别： =IF(LEN(C2)=18,IF(MOD(MID(C2,17,1),2)=0,”女”,”男”),IF(MOD( MID(C2,15,1),2)=0,”女”,”男”) 1