本篇文章要解决的几个问题:
00、从号码提取详细信息,位数转化;
01、输入身份证号问题;
02、检测是否合法;
03、身份证号是否有重复;
04、提取出生年月;
05、提取性别;
06、提取年龄;
07、提取籍贯;
08、提取星座;
09、生肖提取;
10、行列高亮显示;
从根本上一次性解决身份证相关的所有问题,因为涉及比较全面所以篇幅有点长,所以请认真看完,小编主要也是简略的讲一下方法,文末有原文档下载方法,可以下载后自己研究,或者复制到自己的表格中。
首先来看下成品:
过程有点复杂,下面上简单说明:
0、提取详细信息利用在线查询,查询身份证号的信息,大家搜索一下在线查询身份证信息,很多地网址,小编查了一下自己的,当然15位转18位,18位转15位也有的。
而且还能搜出很多其它身份证信息。
1、输入问题方法1:先行设置单元格文本格式,然后填入就是文本了;
方法2:在身份证前输入英文状态下单引号【 ’ 】;
方法3:单元格自定义格式:@
【自定义格式0表示数字,@表示文本】自定义格式不会的朋友,直接头条查一下,要多少有多少,或者点我的头像看其它的教程。
方法4:输入=“511923199501056855”,然后确定回车;不推荐这个方法,但是可以用的。
位数验证方法1:数据有效性-有效性条件-选取文本长度-根据窗体提示填入:18。
当然这个方法只能用在15或者18其中一个。
方法2:数据有效性-公式:=OR(LEN(B1)=15,LEN(B1)=18)
这样之后只要输入不是15位或者18位的就要报警,当然方法也适用于其它数据的验证,大家可以发散一下。
身份证是否重复
利用countif()公式,返回区域中满足条件的个数。
=IF(COUNTIF(B:B,B2)<>1,''重复'',''不重复'')
解释:在B区域中找到B2,如果结果大于1,说明有重复,反之也成立。
出生年月利用下面的公式提取18位的:
=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))
意思分别提取了身份证信息中的年月日,然后转化为日期。这个方法只适应为18位的。
建议用下面的公式:
=TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),''0-00-00'')
这个公式15位的也适用。
详细的用法,请大家自行百度,由于篇幅的问题,小编就提供思路和查询,因为网上很详细。
提取年龄利用公式=DATEDIF(D2,TODAY(),''Y'')
利用出生年月与今天的日期的差值,然后取多少年。
这个公式很简单,datedif()主要用于计算两个日期之间的,年、月、日。
性别公式:=IF(MOD(MID(B2,17,1),2),''男'',''女'')
15位身份证号码:
第15位代表性别,奇数为男,偶数为女。
第17位代表性别,奇数为男,偶数为女。
上面的公式也是提取18位的号码,如果要适用于15位,直接使用if与LEN()相结合来执行。
=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=0,''女'',''男'')推荐使用最后这一个公式,能完美解决。
合法性验证这个有点复杂,小编也就不详细讲解了,只提供一下思路,(这部分验证内容,有引用网络上的图片)
第一步:将身份证号码前17位分别乘以不同的系数。
第二步:将17位数字和系数相乘的结果相加,加和值再除以11,得到余数。余数与身份证号码第18位对应
下面是余数对应表
如果能对应上,就是对的,对应不上,就是错的,当然也只适应18位号码的验证,而且不能识别姓名等的正确性。能验证一些乱填的号码。
小编原文档打包了一个验证工具,需要 的直接私信回复1007就行了,都在包里:
籍贯查询
利用公式:
=LOOKUP(VALUE(LEFT(B2,2)),{11,''北京市'';12,''天津市'';13,''河北省'';14,''山西省'';15,''内蒙古自治区'';21,''辽宁省'';22,''吉林省'';23,''黑龙江省'';31,''上海市'';32,''江苏省'';33,''浙江省'';34,''安徽省'';35,''福建省'';36,''江西省'';37,''山东省'';41,''河南省'';42,''湖北省'';43,''湖南省'';44,''广东省'';45,''广西壮族自治区'';46,''海南省'';50,''重庆市'';51,''四川省'';52,''贵州省'';53,''云南省'';54,''西藏自治区'';61,''陕西省'';62,''甘肃省'';63,''青海省'';64,''宁夏回族自治区'';65,''新疆维吾尔自治区'';71,''台湾省'';81,''香港特别行政区'';82,''澳门特别行政区'';'''',''0''})
当然小编这里只查询了省,如果你需要更加详细的,可以再列一个信息表,利用VLOOKUP来做查询也方便。
星座利用公式:
=LOOKUP(--MID(B2,11,4),{100;120;219;321;421;521;622;723;823;923;1023;1122;1222},{''摩羯座'';''水瓶座'';''双鱼座'';''白羊座'';''金牛座'';''双子座'';''巨蟹座'';''狮子座'';''处女座'';''天秤座'';''天蝎座'';''射手座'';''摩羯座''})
lookup数据型查找,对应关系。
就是把身份证的月和日挑出来,比如0405就变成405,就在321-421之间,就是白羊座。
公式前面加两个小横杠的原理是,把文本型做为数字型进行计算。
生肖利用公式:
=CHOOSE(MOD(MID(B2,7,4)-2008,12)+1,''鼠'',''牛'',''虎'',''兔'',''龙'',''蛇'',''马'',''羊'',''猴'',''鸡'',''狗'',''猪'')
2008年是鼠年,依次类推。每12年为一个循环。
求出日期差,然后除12求余,余再加1就是自己要选择的值。
这个公式,大家可以多研究下,当然想透了还是很简单的。
高亮显示选择单元格及行列这个东西很好,要用到VBA,记得先把宏安全性打开,不然不好用。VBA代码自己保存起来,什么时候想用直接搬出来 就行了。
代码:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
With Target
If .Count > 1 Then
Exit Sub
Else
Rows(.Row).Interior.ColorIndex = 6
Columns(.Column).Interior.ColorIndex = 6
End If
End With
End Sub
演示:
注,由于信息量大,所以不想要自己研究,想直接复制公式的,请下载原文档就好了。
如果内容对你有用,请大家记得关注哦!
联系客服