一个身份证号码包含了你的出生日期、性别、籍贯等众多隐私信息,那这些信息用Excel是如何获取呢?
如下图所示,我们的性别、籍贯、出生日期、年龄、属性都是可以从身份证号码中提取出来的。
1、性别提取
公式:=IF(MOD(MID(E2,17,1),2)=1,\”男\”,\”女\”)
说明:
根据身份证上的第17位数来判断男女,奇数为男,偶数为女(这里身份证都是18位数的)
MID(E2,17,1):从身份证号码中第17位开始提取1个字符
MID函数语法=MID(被提取值,从第几位开始,提取几位数)
MOD(MID(E2,17,1),2):对第17位身份证号码数字进行取余数,余数有两种情况0或1
MOD函数语法=MOD(被除数,除数)
具体操作如下:
2、籍贯提取
身份证中前六位数是全国行政区代码,通过这几位数就可以提取相应的籍贯。
首先我们需要准备一份全国的行政区域代码,之后利用VLOOKUP函数获取相应的地名即可。
公式:=VLOOKUP(LEFT(E2,6),行政区域代码!A:B,2,0)
说明:
LEFT:从左侧提取相应的字符
VLOOKUP函数语法=VLOOKUP(查找值,查找区域,返回列,查找类型)
具体操作如下:
3、出生日期提取
公式:=–TEXT(MID(E2,7,8),\”0000-00-00\”)
说明:
MID(E2,7,8):从身份证号码第7位开始,向右侧提取8位(也就是获取出生日期)
TEXT(MID(E2,7,8),\”0000-00-00\”):将8位数转换为日期格式
具体操作如下:
4、年龄提取
公式:=YEAR(TODAY())-MID(E22,7,4)
说明:
TODAY():获取今天日期
YEAR(TODAY()):获取日期年份
MID(E22,7,4):获取身份证号码上的年份
具体操作如下:
5、属相提取
公式:
=CHOOSE(MOD(MID(E2,7,4)-1900,12)+1,\”鼠\”,\”牛\”,\”虎\”,\”兔\”,\”龙\”,\”蛇\”,\”马\”,\”羊\”,\”猴\”,\”鸡\”,\”狗\”,\”猪\”)
说明:
MOD(MID(E2,7,4)-1900,12):将获取的年份减去1990年,然后再除以12,所得余数
CHOOSE语法=CHOOSE(参数,(数字或单元格区域))
注意:之所以减去1900,是因为Excel的日期最早是1900年,而且这一年是鼠年,与后面的文字区域相对应。
具体操作如下:
6、身份证的录入
如何快速在Excel中输入身份证号码?
选中区域,点击开始——数字——格式——文本格式,然后再输入身份证号码。
PS:也可以在输入号码之前先输入’(记得必须在英文状态下输入单引号)
7、禁止身份证重复输入
选中区域,点击数据——数据验证——自定义,输入公式=COUNTIF($A$1:$A$11,A1&\”*\”)=1,之后再输入身份证号码。