在日常工作中,各种人员信息的表可谓是层出不穷,而身份证号是其中重要的一项,其中包含了出生年月日、性别等信息,如何在Excel中进行计算,自动输出出生年月日、性别等信息呢?
正确输入身份证号码。
由于Excel最长能够保存的数字为15位,而身份证号码为18位,所以默认情况下输入身份证号码后后三位数被替换为0。
如何解决呢?
有两种办法,一是在号码前添加“’”(单撇号,位置在Enter键左边);二是提前将单元格的格式设置为“文本”(选择区域-右键-设置单元格格式)。
Mod函数语法规则
MID(text, start_num, num_chars)
MID(字符串,开始位置,字符长度)
如下图,输入Mid函数,第一个参数C3就是我们要计算的身份证号,身份证号从第7位开始长度为8位的字符串为“出生年月”,所以第二个参数是从第7位开始,获得一个长度8位的数。
=mid(C3,7,8)
原理:省份证第17位为奇数为男性,偶数为女性。因此,我们需要获得第17为数字,然后判断奇偶性。
获取第17为数字使用的是上面Mid函数,公式如下:
=mid(C3,17,1)
意思是获得C3单元格中的第17位数,取一位数。
判断奇偶性用的是Mod函数,mod函数是一个求余函数,语法规则为:
MOD(number,divisor)
MOD(被除数,除数)
因此,判断第17位数的奇偶性的公式为:
mod(mid(C3,17,1),2)
这一步涉及到函数的嵌套,mid(C3,17,1)就是身份证的第17位数字,2就是除数,余数为1就是奇数,0就是偶数。
上面完成了奇偶性的计算,因此,只需要一个if函数就可以完成奇偶性的判断了。if函数的语法规则
IF(logical_test,value_if_true,value_if_false)
IF(逻辑值,结果为True的值,结果为Flase的值)
所以,最后的公式为:
=if(mod(mid(C3,17,1),2),”男”,”女”)
如果身份证的第17位数字是奇数则为男,偶数则为女。
如下图
源文件:
总结,本篇文章通过从身份证号计算出生年月和性别的方法,分别介绍了MID函数,MOD函数和IF函数的简单应用,希望对大家有所帮助。