学习啦>学习电脑>工具软件>办公软件学习>Excel教程>Excel函数>

如何将Excel函数运用到身份证的查询上

锦晖分享

  对于身份证大家肯定不会陌生,在Excel中经常需要根据身份证号码来提取一些信息,或者进行判断。光靠眼力去看,显然不够效率。以下是学习啦小编为您带来的关于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"))

  提取生日

  ①提取6位或8位生日数字,=MID(A1,7,IF(LEN(A1)=15,6,8))

  ②对于15位号码,需要补足前面两位“19”数字,=RIGHT(19&MID(A1,7,IF(LEN(A1)=15,6,8)),8)

  ③将上面得到的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")

  判断性别

  ①提取数字:=MID(A1,15,3),对于15位身份证号码,上述公式提取到是其末位数字,不包含其他字符占位。而对于18位的身份证号码,上述公式提取到的是其15~17位数字。

  ②判断奇偶性:=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,"女","男")

猜你喜欢:

1.excel 公式获取行号的方法

2.excel怎么从身份证号码中提取生日

3.excel身份证号码格式提取生日

4.Excel2007中进行从身份证号中自动获取出生日期的方法

5.Excel表格如何显示完整身份证号

    3731121