湖南龙网

  1. 首页 > 生活百科 > >

怎么将Excel函数利用到身份证信息查询中

小知识生活百科

对于身份证大家肯定不会陌生，老式的15位号码，新一代18位 。在Excel中经常需要根据身份证号码来提取一些信息，或者进行判断 。光靠眼力去看，显然不够效率 。下面我就将Excel函数运用到这个上面，大家学着做 。
类型格式
15位身份证：前2位表示所属省份代码，3~6位表示所属城市和区县代码，7～12表示出生日期，格式为YYMMDD，13～15位是个人顺序码，其中第15位可以标识性别，为奇数表示男性，为偶数表示女性 。
18位身份证：前2位表示所属省份代码，3~6位表示所属城市和区县代码，7～14表示出生日期，格式为YYYYMMDD，15～17位是个人顺序码，其中第17位可以标识性别，为奇数表示男性，为偶数表示女性 。第18位是校验位，由前17位通过计算求得 。
正确输入
因为Excel单元格只支持15位有效数字，输入多了就不能完全显示 。这样就导致新一代身份证号码输入时不便，为了解决这个问题，我们可以采取文本输入的方法，可以在输入号码之前，先添加一个半角的单引号再输入其他数字，这样完成输入的结果就是一个文本型数据 。或者也可以在输入之前事先将单元格格式设置为文本再行输入 。但是如果在输入完成以后再更改单元格格式就不会有效果 。
自动验证输入
条件1：输入长度为15位或18位，函数公式可以这样写：=OR(LEN(A1)=15,LEN(A1)=18)
条件2：前17位必须都是数字，公式：=ISNUMBER(-LEFT(A1,17))
条件3：如果不全都是数字，那么它只能是18位，并且末尾字符是字母“X”，公式：=OR(ISNUMBER(-A1),AND(LEN(A1)=18,RIGHT(A1)="X"))
【怎么将Excel函数利用到身份证信息查询中】提取生日
1、提取6位或8位生日数字，=MID(A1,7,IF(LEN(A1)=15,6,8))
2、对于15位号码，需要补足前面两位“19”数字，=RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8)
3、将上面得到的8位数字转换成真实日期数值，=TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00")+0
计算年龄
年龄的计算实际上就是通过前面得到的出生日期来用DATEDIF函数计算到当前所相差的年份数(周岁)：
=DATEDIF(TEXT(RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8),"0-00-00"),NOW(),"Y")
判断性别
1、提取数字：=MID(A1,15,3)，对于15位身份证号码，上述公式提取到是其末位数字，不包含其他字符占位 。而对于18位的身份证号码，上述公式提取到的是其15～17位数字 。
2、判断奇偶性：=IF(MOD(MID(A1,15,3),2), "男","女") 。通过MOD函数除以2取余数来进行奇偶判断，如果余数为1，表示奇数，得到男性判断，如果余数为0，得到女性判断 。
上面大篇幅的介绍了15位和18位身份证号码的函数公式运用，自从2013年1月1日开始，第一代身份证已经不允许属于，这样我们函数公式也能够简化：
验证输入：=AND(LEN(A1)=18,ISNUMBER(-LEFT(A1,17)),OR(ISNUMBER(-A1),RIGHT(A1)="X"))
提取生日：=TEXT(MID(A1,7,8),"0-00-00")+0
计算年龄：=DATEDIF(TEXT(MID(A1,7,8),"0-00-00"),NOW(),"Y")
判别性别：=IF(-1^MID(A1,15,3)=1,"女","男")

    推荐阅读


    上一篇：11月什么水果是当季吃的

    下一篇：粉煤灰是什么材料