不到五分钟时间!EXCEL变成一个身份证信息查询工具
在hr部门工作同学经常会需要记录员工的身份证号码,然后需要手动计算或者手动查询年龄,生肖户籍地等信息,非常繁琐。这次给大家分享只输入身份证号码就可以显示你想要的信息。
性别判断方法
获取身份证号码第17位数,然后除2的余数,奇数是男,偶数是女。
=IF(MOD(MID(A2,17,1),2),男,女)
=IF(MOD(MID(A2,17,1),2),男,女)
使用到的函数:
MID函数:从文本字符串中的指定位置起返回特定个数的字符
MOD函数:返回除法的余数
年龄获取方式
=DATEDIF(--TEXT((LEN(A2)=15)*19MID(A2,7,6+(LEN(A2)=18)*2),0-00-00),TODAY(),y)周岁DATEDIF(--TEXT((LEN(A2)=15)*19MID(A2,7,6+(LEN(A2)=18)*2),0-00-00),TODAY(),ym)月DATEDIF(--TEXT((LEN(A2)=15)*19MID(A2,7,6+(LEN(A2)=18)*2),0-00-00),TODAY(),md)天
=DATEDIF(--TEXT((LEN(A2)=15)*19MID(A2,7,6+(LEN(A2)=18)*2),0-00-00),TODAY(),y)周岁DATEDIF(--TEXT((LEN(A2)=15)*19MID(A2,7,6+(LEN(A2)=18)*2),0-00-00),TODAY(),ym)月DATEDIF(--TEXT((LEN(A2)=15)*19MID(A2,7,6+(LEN(A2)=18)*2),0-00-00),TODAY(),md)天
使用到的函数:
DATEDIF函数:计算两个日期之间的天数、月数或年数。此函数在用于计算年龄的公式中很有用。
LEN函数:返回文本字符串中的字符个数
MID函数:从文本字符串中的指定位置起返回特定个数的字符
TEXT函数:设置数字格式并将其转换为文本
出生日期
获取身份证第7位数开始提取后面的8位数,指定数字格式yyyy年mm月dd日。
=TEXT(MID(A2,7,8),0年00月00日)
=TEXT(MID(A2,7,8),0年00月00日)
使用到的函数:
MID函数:从文本字符串中的指定位置起返回特定个数的字符
TEXT函数:设置数字格式并将其转换为文本
生肖获取方法
=IF(LEN(A2)=15,MID(鼠牛虎兔龙蛇马羊猴鸡狗猪,MOD(19MID(A2,7,2)-4,12)+1,1)
=IF(LEN(A2)=15,MID(鼠牛虎兔龙蛇马羊猴鸡狗猪,MOD(19MID(A2,7,2)-4,12)+1,1)
使用到的函数:
LEN函数:返回文本字符串中的字符个数
MID函数:从文本字符串中的指定位置起返回特定个数的字符
MOD函数:返回除法的余数
星座获取方法
=VLOOKUP(VALUE(1900-TEXT(MID(A2,LEN(A2)/2+2,4),#-##)),{1,摩羯座;20,水瓶座;50,双鱼座;81,白羊座;111,金牛座;142,双子座;174,巨蟹座;205,狮子座;236,处女座;267,天秤座;298,天蝎座;328,射手座;357,摩羯座},2,TRUE)
=VLOOKUP(VALUE(1900-TEXT(MID(A2,LEN(A2)/2+2,4),#-##)),{1,摩羯座;20,水瓶座;50,双鱼座;81,白羊座;111,金牛座;142,双子座;174,巨蟹座;205,狮子座;236,处女座;267,天秤座;298,天蝎座;328,射手座;357,摩羯座},2,TRUE)
使用到的函数:
TEXT函数:设置数字格式并将其转换为文本
LEN函数:返回文本字符串中的字符个数
MID函数:从文本字符串中的指定位置起返回特定个数的字符
VALUE函数:将文本参数转换为数字
VLOOKUP函数:在数组第一列中查找,然后在行之间移动以返回单元格的值
出生地获取方法
在sheet2工作簿添加下面的数据库
然后在身份证号码查询器输入以下公式
=VLOOKUP(LEFT(A2,6),户籍地!1:1048576,2,1)
=VLOOKUP(LEFT(A2,6),户籍地!1:1048576,2,1)
使用到的函数:
LEFT函数:返回文本值中最左边的字符
VLOOKUP函数:在数组第一列中查找,然后在行之间移动以返回单元格的值
这样就完成了身份证查询器,是不是特别棒,如果想获取excel模板 请在评论中回复 返回搜狐,查看更多
责任编辑: