乔山办公网我们一直在努力
您的位置:乔山办公网 > excel表格制作 > 教大家用Excel函数快速从身份证号码提取有效信息,人事必备技能-excel身份证号码格式

教大家用Excel函数快速从身份证号码提取有效信息,人事必备技能-excel身份证号码格式

作者:乔山办公网日期:

返回目录:excel表格制作

给大家分享一下如何用Excel函数从身份证号码提取各种隐藏的信息,今天是跟身份证干上了,人事必备技能哦。

1、提取出生日期

第一种方法:利用分列法

选中区域,点击数据——分列,在打开的界面中选择固定宽度,之后将出生日期框选出来,随后将其他部分内容选择不导入,点击完成即可。


教大家用Excel函数快速从身份证号码提取有效信息,人事必备技能

第二种方法:快速填充法

先在第一个单元格中输入出生日期,之后选中区域按Ctrl+E即可快速填充,随后再选中区域,按Ctrl+1打开单元格格式设置,在自定义中输入0-00-00代码即可。

教大家用Excel函数快速从身份证号码提取有效信息,人事必备技能

2、性别判断

在18位身份证号码中,第17位是判断性别的,偶数为女,奇数为男。

这里可以输入公式=IF(MOD(MID(B2,17,1),2)=1,"男","女")


教大家用Excel函数快速从身份证号码提取有效信息,人事必备技能

公式说明:

MID函数语法=(被提取值,从第几位开始,提取几位数)

MOD函数语法=(被除数,除数)

3、籍贯判断

输入公式=VLOOKUP(--LEFT(B2,2),行政区域代码表!A:B,2,0)


教大家用Excel函数快速从身份证号码提取有效信息,人事必备技能

公式说明:

VLOOKUP函数语法=VLOOKUP(查找值,查找区域,返回的列,查找的类型)

LEFT函数语法= LEFT(字符串,从左侧提取字符数)

注意:籍贯的判断是需要事先准备好行政区域代码表才可以查找。

4、提取年龄

输入公式=YEAR(TODAY())-YEAR(C2)

教大家用Excel函数快速从身份证号码提取有效信息,人事必备技能

5、属相提取

这里输入公式=CHOOSE(MOD(MID(B2,7,4)-1900,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")

教大家用Excel函数快速从身份证号码提取有效信息,人事必备技能

注意:之所以减去1900,是因为Excel的日期最早是1900年,而且这一年是鼠年,与后面的文字区域相对应。

6、身份证的录入

如何快速在Excel中输入身份证号码?

选中区域,点击开始——数字——格式——文本格式,然后再输入身份证号码。


教大家用Excel函数快速从身份证号码提取有效信息,人事必备技能

PS:也可以在输入号码之前先输入’(记得必须在英文状态下输入单引号)

7、禁止身份证重复输入

选中区域,点击数据——数据验证——自定义,输入公式=COUNTIF($A$1:$A$11,A1&"*")=1,之后再输入身份证号码。


教大家用Excel函数快速从身份证号码提取有效信息,人事必备技能

以上就是今天要与大家分享的Excel函数技巧,你学会了吗?


相关阅读

关键词不能为空
极力推荐

ppt怎么做_excel表格制作_office365_word文档_365办公网