本篇文章要解决的几个问题: 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 演示: 注,由于信息量大,所以不想要自己研究,想直接复制公式的,请下载原文档就好了。 如果内容对你有用,请大家记得关注哦! |
|