打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
EXECL身份证信息全提取!很恐怖,还敢泄露自己的身份证号码吗?


本篇文章要解决的几个问题:

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

演示:

注,由于信息量大,所以不想要自己研究,想直接复制公式的,请下载原文档就好了。

如果内容对你有用,请大家记得关注哦!

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
excel如何输入身份证号码及自动显示性别
在Excel中根据身份证号码自动生成出生生日期和性别
在Excel中根据身份证号码自动生成性别、出生日期和年龄
如何从在excel中身份证号码里提取需要的信息
用Excel自制身份证验证器,所有身份证信息都囊括全了(建议收藏)
Excel和身份证不得不说的那些事儿
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服