Excel中如何利用身份证号自动识别男女性别?(Excel问答系列之五) |
您所在的位置:网站首页 › 用mid函数从身份证号码提取性别 › Excel中如何利用身份证号自动识别男女性别?(Excel问答系列之五) |
针对这个问题,我们首先得了解我国身份证号的组成结构,知道了身份证号不同位数所代表的的含义后,就可以针对性的设计出相对完善处理方案。 (1)目前我们现行使用的是18位的二代身份证号,其不同位数所代表意思如下图所示18位身份号代表信息 左数第1、2位表示省份代码 左数第3、4位表示城市代码 左数第5、6位表示区县代码 左数第7-14位表示出生年月代码 左数第15-17位表示同一地区范围内,对同年同月同日出生的人员编定的顺序码,其中第17位的奇数代表男性,偶数代表女性 左数第18位表示校验码(0-9,X) 虽然目前一代的15位身份证号码已不再使用,但在一些情况下,部分表格和数据库中还会留存着15位身份证号的信息。所以我们也需要了解下这类身份证号的组成结构 (2)15位身份证号不同位数所代表意思如下图所示15位身份号代表信息 左数第1、2位表示省份代码 左数第3、4位表示城市代码 左数第5、6位表示区县代码 左数第7-12位表示出生年月代码 左数第13-15位表示同一地区范围内,对同年同月同日出生的人员编定的顺序码,其中第15位的奇数代表男性,偶数代表女性 (3)18位身份证号和15位身份证号的区别一:18位身份证号的出身年月代码有8位,其中代表年份的有4位。15位身份证号的出身年月代码有6位,其中代表年份有2位。 二:18位身份证号最后一位是校验码,15位身份证号没有校验码 了解了身份证号的组成结构后,对于18位的身份证号,我们可以判断第17位数的奇偶来识别是男性还是女性。对于15位的身份证号,可以判断第15位数的奇偶来识别男性还是女性。(4)我们先从18位身份证号开始设计函数,操作步骤如下图所示上图函数公式: =IF(MOD(MID(C2,17,1),2)=1,"男","女") 函数解析:MID(C2,17,1) MID函数的作用是从原文本数据中截取指定位置,指定长度的字符串。 C2表示要截取数据的原文本 17表示从第17位开始截取 1表示截取长度为1的字符串 整个函数的作用是把18位身份证号中的第17位数提取出来 函数解析: MOD(MID(C2,17,1),2) MOD函数的作用是求余 这里MID(C2,17,1)是被除数 2表示除数 整个函数相当于(MID(C2,17,1))%2,数字奇偶数的判断我们通常都是通过(被除数%2)这样的思路来操作的。余数为0视为偶,余数为1视为奇。 函数解析: =IF(MOD(MID(C2,17,1),2)=1,"男","女") 整个函数的思路就是 先用MID函数提取身份证号中能区分性别位数上的数字。 再利用MOD函数求余判断是奇数还是偶数。 最后利用IF条件函数判断如果结果是奇数就是“男”,否则就是“女”。 (5)对于15位身份证号获取性别的函数公式设计,如下图所示上图函数公式:=IF(MOD(MID(C3,15,1),2)=1,"男","女") 15位身份证号的性别获取公式原理同18位身份证号,唯一区别是提取的位置一个是第17位,一个是第15位。这里就不再做解析了。 (6)对于表格中既含有15位身份证号,又含有18位身份证号的情况。函数公式设计思路如下图所示 上图函数公式: =IF(LEN(C2)=18,IF(MOD(MID(C2,17,1),2)=1,"男","女"),IF(MOD(MID(C2,15,1),2)=1,"男","女")) 函数解析:LEN(C2)=18 LEN函数的作用是返回文本串的字符串数 这里的作用是获取C2单元格中的身份证号的位数并判断是否等于18,其返回值为TRUE或是FALSE 函数解析: IF(MOD(MID(C2,17,1),2)=1,"男","女") 18位身份证号的性别获取公式 函数解析: IF(MOD(MID(C2,15,1),2)=1,"男","女") 15位身份证号的性别获取公式 函数解析: =IF(LEN(C2)=18,IF(MOD(MID(C2,17,1),2)=1,"男","女"),IF(MOD(MID(C2,15,1),2)=1,"男","女")) 整个函数的设计思路是 利用IF函数先判断LEN函数获取的身份证号的位数是否是18位,如果是,则调用18位身份证号的性别获取公式IF(MOD(MID(C2,17,1),2)=1,"男","女"),否则,调用15位身份证号的性别获取公式。 (7)扩展:18位15位身份证号的性别获取公式还有一个更简洁的=IF(MOD(MID(C2,(LEN(C2)=18)*2+15,1),2),"男","女") 这个公式对于Excel老鸟来说理解起来没什么难度,但对于刚接触Excel函数的小伙伴来说还是有点挑战的。这个函数整体的解析我就不做了,把其中的难点跟各位小伙伴分析一下。 函数解析:(LEN(C2)=18)*2+15 “(LEN(C2)=18)*2+15”这个公式,仔细观察的小伙伴也不难发现其代表的是MID函数中第二个参数,用于指定截取数据的开始位置。 根据Excel中TRUE和FALSE乘以数字会对应变为1和0的特性, 如果LEN(C2)=18为TRUE,则(LEN(C2)=18)*2+15就变为“1*2+15”得出17的结果,代表18位身份证号中的性别获取位置。 如果LEN(C2)=18为FALSE,则(LEN(C2)=18)*2+15就变为“0*2+15”得出15的结果,代表15位身份证号中的性别获取位置。 知道了这个公式的含义,结合之前解释的MID函数MOD函数以及IF函数,整个函数就很好理解了。 如您在Excel的操作中遇到不能解决的问题,欢迎关注微信公众号并留言自己在Excel中所遇到的情况,小编会不定时发文,阐述解决方案。 往期Excel专题回顾 EXCEL的神操作,让你效率提升数十倍!(EXCEL神操作系列之一) 学会这几招,Excel菜鸟也能秀出大神范!(EXCEL神操作系列之二) 一看就懂,不容错过的Excel神操作!(EXCEL神操作系列之三) 一学就会的Excel小操作,简单又高效!(EXCEL神操作系列之四) 几个Excel小技巧,数十倍提升你的效率!(EXCEL神操作系列之五) 几个神奇的Excel快捷操作,你值得收藏!(EXCEL神操作系列之六) 21种常用Excel快捷操作,你想要的都在这里! Excel工作表排序时想区分大小写该怎么办?(Excel问答系列之一) EXCEL中手机号码如何进行分段显示?(Excel问答系列之二) Excel怎样快速输入星期?(Excel问答系列之三) Excel输入身份证号后面自动变成000怎么办?(Excel问答系列之四) |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |