|
楼主 |
发表于 2016-12-26 21:22
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
【如何在EXCEL中使用公式自动取得身份证号码中的相关信息】
根据出生日期码取出生年月日信息:
假设身份证号码填写在A1单元格,我们要在B1单元格中通过公式取出出生年月。
则可以在B1单元格中录入公式 =TEXT(MID(A1,7,8),"#-00-00")+0
即可得到结果,仍旧以上例中的身份证号码为例,结果为1977/9/24。
在公式中,MID(A1,7,8) 表示从A1的身份证号码中,从第7位开始,取出后面的8个数字,即身份证号码中的8位生日日期码。
TEXT函数,将取出的8位数字通过参数"#-00-00",将得到的“19770924”转换成“1977-09-24”。因为TEXT函数处理的结果是文本,所以通过+0将文本转换成数值。
有时候我们也会这样写:=--TEXT(MID(A1,7,8),"#-00-00") 即相当于连续乘了两个负1,也可以将文本格式的数字转换成数字格式。
来扒一扒EXCEL的1900日期系统,让我们对EXCEL的1900日期系统多一些了解。
EXCEL中的日期值就是数值,日期只是数值的特殊显示样式。
在1900日期系统中,EXCEL允许用户输入的日期范围为1900年1月1日至9999年12月31日。
而数值1代表这个日期区间的第1天,即1900年1月1日,2代表1900年1月2日,3代表1900年1月3日。。。。。。以此类推,数值2958465代表这个日期区间的最后一天9999年12月31日。
在1900日期系统中,EXCEL中的日期值实际是从1至2958465的自然数序列,用公式计算和处理日期,就是用公式计算和处理日期值对应的数值。
如果你得到的结果是28392,则选中B列,点击EXCEL中开始菜单,将数字格式中的常规改为日期,然后选择一个你想要的日期格式,即可得到正确的日期。
根据出生日期码计算年龄:
通过身份证号码计算年龄,我们可以取出身身份证号码中的出生年月日,然后使用DATEDIF函数将TODAY()得到的今天的日期和身份证上的日期相减,得到年龄,参数“Y”表示显示相减得到的年数。
公式如下:
=DATEDIF(TEXT(MID(A1,7,8),"#-00-00"),TODAY(),"Y")
计算性别:
因为上面讲到顺序码中的第17位数,如果是单数,则为男性,双数则为女性。
我们可以根据第17位数的奇偶性来判断性别。
=IF(ISODD(--MID(B1,17,1)),"男","女")
ISODD函数是奇数判断函数,如果第17位的数字是奇数,则显示“男”,否则显示“女”。
计算验证码:
前面给出了验证码的计算方式。
1、是将将身份证号码的前面的十七位数字一一对应和下列十七位数字相乘,然后求和。
{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}
假设身份证号码在A1单元格,我们在B1单元格录入公式如下:
=SUMPRODUCT(--MID($A$1,ROW(1:17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2})
这是一个数组公式,同普通公式不一样,录入完毕后,需要按 CTRL+ALT+ENTER键才可以,按完这三个键后,公式即变成了
{=SUMPRODUCT(--MID($A$1,ROW(1:17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2})}
即前后多了大括号。注意,这一对大括号不是手工录入进去的,而是按CTRL+ALT+ENTER键自动生成的。表示这是一个数组公式。
如果A1的身份证号码为320583197709249415。这样就会得到结果425。
2、将结果425除以11,然后取余数。将会得到0~10的是一个余数。
余数从0~10,一一对应下列验证码。
{1;0;X;9;8;7;6;5;4;3;2}
因为425在B1单元格里,我们在C1单元格录入公式:
=CHOOSE(MOD(B1,11)+1,1,0,"X",9,8,7,6,5,4,3,2)
即可得到最终的验证码为 “5” 。
上面的身份证号码最后一位的验证码也是5,表示这个身份证号码是正确的。
当然,刚才是分解公式,分为两步求出结果的,如果想一步求出结果,那么也很简单。只要将C1单元格中的B1,用B1单元格中的公式替代即可。
结果如下:
=CHOOSE(MOD((SUMPRODUCT(--MID($A$1,ROW(1:17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2})),11)+1,1,0,"X",9,8,7,6,5,4,3,2)
根据前六位的地址码取区域地址信息:
1、首先在EXCEL中新建工作表,将新建的工作表命名为【地区代码】。
2、进入国家统计局的网站,http://www.stats.gov.cn/tjsj/tjbz/xzqhdm/
3、点击【最新县及县以上行政区划代码(截止2015年9月30日)】。
图片
4、将相关代码信息复制,粘贴到EXCEL(最好是先新建一个文本文档,将网站上的地址代码信息先复制粘贴在文本文档里,然后再从文本文档中再次复制,再粘贴到EXCEL中,这样可以去掉因为从网站复制数据所带来的特殊字符,便于数据清洗。),然后将数据分列清洗得到如下图的数据表。
图片
5、再新建【身份证号码】工作表,假设在A1单元格中输入身份证号码,例如320583197709249415 。要注意,在录入身份证号码前,应先将要录入身份证的列自定义格式为文本。
也可以选中A列,然后点击EXCEL中的开始菜单,将数字格式中常规改为文本。
图片
6、在B1单元格中,输入公式
=IFERROR(VLOOKUP(--(LEFT(A1,2)&"0000"),地区代码!A:B,2,0),"")&IFERROR(VLOOKUP(--(LEFT(A1,4)&"00"),地区代码!A:B,2,0),"")&IFERROR(VLOOKUP(--(LEFT(A1,6)),地区代码!A:B,2,0),"")
公式是有点长,不过小伙伴们不要蓝瘦香菇,我们拆解开来再看,其实很简单。
注意:IFERROR函数是EXCEL2017及以后版本中才有,EXCEL2007以下版本不能使用这个函数。
图片
为什么公式是这个样子的,原理是什么,让我们来研究一下地址数据信息:
北京市:110000,天津市:120000,河北省:130000,山西省:140000,内蒙古自治区:150000,辽宁省:210000,吉林省:220000,黑龙江省:230000,上海市:310000,江苏省:320000,浙江省:330000,安徽省:340000,福建省:350000,江西省:360000,山东省:370000,河南省:410000,湖北省:420000,湖南省:430000,广东省:440000,广西壮族自治区:450000,海南省:460000,重庆市:500000,四川省:510000,贵州省:520000,云南省:530000,西藏自治区:540000,陕西省:610000,甘肃省:620000,青海省:630000,宁夏回族自治区:640000,新疆维吾尔自治区:650000,台湾省(886)|710000,香港特别行政区(852)|810000,澳门特别行政区(853)|820000 。
看到没,六位的地址码中后四位为0000的代表是省份和直辖市。同样的,我们以江苏省:320000为例来研究一下地级市的代码。
南京市:320100,无锡市:320200,徐州市:320300,常州市:320400,苏州市:320500,南通市:320600,连云港市:320700,淮安市:320800,盐城市:320900,扬州市:321000,镇江市:321100,泰州市:321200,宿迁市:321300,泰州市:321200,宿迁市:321300。
我们发现,六位的地址码中最后两位为00的代表是地级市。那么剩下的那些地址码就是县和区了。
下面的公式是取省份的:
=IFERROR(VLOOKUP(--(LEFT(A1,2)&"0000"),地区代码!A:B,2,0),"")
我们去掉判断是否出错的IFERROR函数,也可以取出省份。
=VLOOKUP(--(LEFT(A1,2)&"0000"),地区代码!A:B,2,0)
如果还是使用上例中的身份证号码:320583197709249415
那么这个公式中的 LEFT(A1,2)&"0000" 部分的意思是从左边取身份证号码的前两位,即 “32”,然后在后面补上“0000”,就得到“320000”。因为【地区代码】工作表中的代码被清洗成数据格式了,所以这地方要使用 --320000,将文本的320000,变成了数字格式的3200。
然后公式就相当于 =VLOOKUP(320000,地区代码!A:B,2,0)
就是在【地区代码】工作表中的A列到B列的第一列,也就是A列中搜寻320000。找到了这个数据之后,就会自动显示A列到B列中的第2列,也就是B列的内容,也就是“江苏省”。
因为行政区域有时候会发生变化,这时候行政部门就会增加或者减少代码所代表的行政区域,而我们的身份证号码是终身的,而老的行政区域已经被合并或者撤销了,所以有时候会找不到行政区域,特别是县和区的行政区域比较容易变化,最新的区域代码中经常会查找不到。所以我们使用IFERROR函数来容错。
加上IFERROR()函数是防止没有找到具体的信息,容错用的。意思是如果找到了结果就显示结果,如果没找到结果就显示为空。
IFERROR(VLOOKUP(--(LEFT(A1,4)&"00"),地区代码!A:B,2,0),"") 这个公式是取地级市名称的,相当于查找“320500” ,结果得到“苏州市”
下面这个公式是取县和区的。
IFERROR(VLOOKUP(--(LEFT(A1,6)),地区代码!A:B,2,0),"") 这部分公式会得到“昆山市” ,昆山市是苏州市的县级市。
然后将三个公式用 & 连接起来,就得到结果“江苏省苏州市昆山市”。
公式的第一部分取通过前两位取省份信息,然后再通过前四位取城市信息,再通过前六位取县区的地址信息。然后拼起来,如图因为昆山市属于苏州市的,所以得到最终结果【江苏省苏州市昆山市】。 |
|