身份证相关处理方法,是每一位数据处理者都会遇到的问题。韩老师总结了EXCEL中,关于身份证必须要会的十条知识,你都会吗?如下:

1、身份证输入

18位身份证号码的正确输入方式:一种方式是把单元格格式设置成文本,另一种方式是在输入身份证号码之前加一个英文状态的单引号。其实这两种方法操作过程不一样,实质都是一样的,就是把单元格变成文本格式:

excel显示不出来身份证数字(Excel中关于身份证的十条黄金用法)(1)

2、限定输入身份证号码为18位且不能重复

【数据】——【数据验证】(EXCEL2010及以前版本称为“数据有效性”):验证条件选择自定义,输入公式:“=AND(LEN(H1)=18,COUNTIF(H:H,H1&"*")=1)”,如下图所示:

excel显示不出来身份证数字(Excel中关于身份证的十条黄金用法)(2)

限定输入身份证号码

其中:

LEN(H1)=18:含义是H1单元格内容的长度为18位;

COUNTIF(H:H,H1&"*")=1:在H列中H1单元格内容只出现1次,也就是不能重复出现;

AND(LEN(H1)=18,COUNTIF(H:H,H1&"*")=1):同时满足内容长度为18位、不能重复出现两个条件。

3、身份证位数输入错误,自动变色提示

用“条件格式”的设计方法,只要位数错误,单元格会自动填充颜色,以提示出现了错误。

【开始】——【条件格式】——【新建规则】——【使用公式确定要设置格式的单元格】,输入公式:=AND(LEN(A1)<>15,LEN(A1)<>18,A1<>""),如下图所示:

excel显示不出来身份证数字(Excel中关于身份证的十条黄金用法)(3)

条件格式中输入公式公式

设置单元格格式填充为你想要的颜色,当单元格长度不是15和18,并且单元格不是空时,填充某种颜色。

4、身份证号码计算性别

公式:=IF(MOD(MID(A2,17,1),2)=1,"男","女")

excel显示不出来身份证数字(Excel中关于身份证的十条黄金用法)(4)

5、身份证计算出生日期

公式:=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))

excel显示不出来身份证数字(Excel中关于身份证的十条黄金用法)(5)

6、身份证计算周岁年龄

公式:=DATEDIF(TEXT(MID(A2,7,8),"0-00-00"),TODAY(),"Y")

excel显示不出来身份证数字(Excel中关于身份证的十条黄金用法)(6)

7、隐藏身份证部分字符

在D3单元格,输入公式:=REPLACE(C3,7,8,"********"),再往下填充,即可隐藏所有身份证号码部分数字:

excel显示不出来身份证数字(Excel中关于身份证的十条黄金用法)(7)

8、身份证统计个数

身份证号码统计个数时,也会出现错误:

比如,在D2单元格输入公式:"=COUNTIF($C$2:$C$12,C2)",向下填充,会出现统计错误,如下图:

excel显示不出来身份证数字(Excel中关于身份证的十条黄金用法)(8)

还是由于Excel对同一串数字的有效辨识,仅限于15位,会把前15位相同的身份证号码等同成完全相同。

解决方法:

把公式改为“=COUNTIF($C$2:$C$12,C2&"*")”,在统计条件C2后面加上&"*",让EXCEL“明白”现在统计的是文本,结果如下图:

excel显示不出来身份证数字(Excel中关于身份证的十条黄金用法)(9)

9、身份证高级筛选

根据身份证号码筛选员工信息的时候出现错误:同一个身份证号码,高级筛选出好几位员工,如下图 2‑259所示:

excel显示不出来身份证数字(Excel中关于身份证的十条黄金用法)(10)

分析结果,我们可以看出:筛选出的三位员工,他们的身份证号码的前15位是一致的。

解决方法是在筛选条件的身份证后面加上“*”,让EXCEL认为筛选条件是文本,就不会出现这种错误了。如下动图:

excel显示不出来身份证数字(Excel中关于身份证的十条黄金用法)(11)

10、根据身份证计算退休时间

在B2单元格输入公式:=EDATE(DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)),55*12 MOD(MID(A2,17,1),2)*5*12),确定,公式向下填充,即可得所有身份证号码对应的退休日期。如所示:

excel显示不出来身份证数字(Excel中关于身份证的十条黄金用法)(12)

身份证号码对应的退休日期

关于身份证,你还有什么常用的方法?欢迎留言!


,