一个身份证号，用Excel能提取多少信息？（含批量校对错误）

每个人都有身份证号码。作为疾控人的你，知道从身份证号码上可以提取多少信息吗？那么打开你的excel，手把手的交给你如何批量提取有效信息。相关理论基础详见《如何10秒校对1万个身份证号有无错误？》

第一部分批量提取出生省份/地市/区县

民政部官网上有2020年12月中华人民共和国县以上行政区划代码，上面的六位代码就是我们身份证号的前六位，可以通过mid函数+vlookup函数+数组实现自动提取

出生省份查询，在C2中输入

=VLOOKUP(MID(B2,1,2)*1,{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,'澳门特别行政区'},2,0)

其中MID(B2,1,2)*1是因为MID(B2,1,2)提取的是文本值，乘1之后可以转为数值。

同样的方法，出生地址查询，在D2中输入

=vlookup(MID(B2,1,4)*1,{1301,'石家庄市';1302,'唐山市';1303,'秦皇岛市';1304,'邯郸市';1305,'邢台市';1306,'保定市';1307,'张家口市';1308,'承德市';1309,'沧州市';1310,'廊坊市';1311,'衡水市';1401,'太原市';1402,'大同市';1403,'阳泉市';1404,'长治市';1405,'晋城市';1406,'朔州市';1407,'晋中市';1408,'运城市';1409,'忻州市';1410,'临汾市';1411,'吕梁市';1501,'呼和浩特市';1502,'包头市';1503,'乌海市';1504,'赤峰市';1505,'通辽市';1506,'鄂尔多斯市';1507,'呼伦贝尔市';1508,'巴彦淖尔市';1509,'乌兰察布市';1522,'兴安盟';1525,'锡林郭勒盟';1529,'阿拉善盟';2101,'沈阳市';2102,'大连市';2103,'鞍山市';2104,'抚顺市';2105,'本溪市';2106,'丹东市';2107,'锦州市';2108,'营口市';2109,'阜新市';2110,'辽阳市';2111,'盘锦市';2112,'铁岭市';2113,'朝阳市';2114,'葫芦岛市';2201,'长春市';2202,'吉林市';2203,'四平市';2204,'辽源市';2205,'通化市';2206,'白山市';2207,'松原市';2208,'白城市';2224,'延边朝鲜族自治州';2301,'哈尔滨市';2302,'齐齐哈尔市';2303,'鸡西市';2304,'鹤岗市';2305,'双鸭山市';2306,'大庆市';2307,'伊春市';2308,'佳木斯市';2309,'七台河市';2310,'牡丹江市';2311,'黑河市';2312,'绥化市';2327,'大兴安岭地区';3201,'南京市';3202,'无锡市';3203,'徐州市';3204,'常州市';3205,'苏州市';3206,'南通市';3207,'连云港市';3208,'淮安市';3209,'盐城市';3210,'扬州市';3211,'镇江市';3212,'泰州市';3213,'宿迁市';3301,'杭州市';3302,'宁波市';3303,'温州市';3304,'嘉兴市';3305,'湖州市';3306,'绍兴市';3307,'金华市';3308,'衢州市';3309,'舟山市';3310,'台州市';3311,'丽水市';3401,'合肥市';3402,'芜湖市';3403,'蚌埠市';3404,'淮南市';3405,'马鞍山市';3406,'淮北市';3407,'铜陵市';3408,'安庆市';3410,'黄山市';3411,'滁州市';3412,'阜阳市';3413,'宿州市';3415,'六安市';3416,'亳州市';3417,'池州市';3418,'宣城市';3501,'福州市';3502,'厦门市';3503,'莆田市';3504,'三明市';3505,'泉州市';3506,'漳州市';3507,'南平市';3508,'龙岩市';3509,'宁德市';3601,'南昌市';3602,'景德镇市';3603,'萍乡市';3604,'九江市';3605,'新余市';3606,'鹰潭市';3607,'赣州市';3608,'吉安市';3609,'宜春市';3610,'抚州市';3611,'上饶市';3701,'济南市';3702,'青岛市';3703,'淄博市';3704,'枣庄市';3705,'东营市';3706,'烟台市';3707,'潍坊市';3708,'济宁市';3709,'泰安市';3710,'威海市';3711,'日照市';3713,'临沂市';3714,'德州市';3715,'聊城市';3716,'滨州市';3717,'菏泽市';4101,'郑州市';4102,'开封市';4103,'洛阳市';4104,'平顶山市';4105,'安阳市';4106,'鹤壁市';4107,'新乡市';4108,'焦作市';4109,'濮阳市';4110,'许昌市';4111,'漯河市';4112,'三门峡市';4113,'南阳市';4114,'商丘市';4115,'信阳市';4116,'周口市';4117,'驻马店市';4201,'武汉市';4202,'黄石市';4203,'十堰市';4205,'宜昌市';4206,'襄阳市';4207,'鄂州市';4208,'荆门市';4209,'孝感市';4210,'荆州市';4211,'黄冈市';4212,'咸宁市';4213,'随州市';4228,'恩施土家族苗族自治州';4301,'长沙市';4302,'株洲市';4303,'湘潭市';4304,'衡阳市';4305,'邵阳市';4306,'岳阳市';4307,'常德市';4308,'张家界市';4309,'益阳市';4310,'郴州市';4311,'永州市';4312,'怀化市';4313,'娄底市';4331,'湘西土家族苗族自治州';4401,'广州市';4402,'韶关市';4403,'深圳市';4404,'珠海市';4405,'汕头市';4406,'佛山市';4407,'江门市';4408,'湛江市';4409,'茂名市';4412,'肇庆市';4413,'惠州市';4414,'梅州市';4415,'汕尾市';4416,'河源市';4417,'阳江市';4418,'清远市';4419,'东莞市';4420,'中山市';4451,'潮州市';4452,'揭阳市';4453,'云浮市';4501,'南宁市';4502,'柳州市';4503,'桂林市';4504,'梧州市';4505,'北海市';4506,'防城港市';4507,'钦州市';4508,'贵港市';4509,'玉林市';4510,'百色市';4511,'贺州市';4512,'河池市';4513,'来宾市';4514,'崇左市';4601,'海口市';4602,'三亚市';4603,'三沙市';4604,'儋州市';5101,'成都市';5103,'自贡市';5104,'攀枝花市';5105,'泸州市';5106,'德阳市';5107,'绵阳市';5108,'广元市';5109,'遂宁市';5110,'内江市';5111,'乐山市';5113,'南充市';5114,'眉山市';5115,'宜宾市';5116,'广安市';5117,'达州市';5118,'雅安市';5119,'巴中市';5120,'资阳市';5132,'阿坝藏族羌族自治州';5133,'甘孜藏族自治州';5134,'凉山彝族自治州';5201,'贵阳市';5202,'六盘水市';5203,'遵义市';5204,'安顺市';5205,'毕节市';5206,'铜仁市';5223,'黔西南布依族苗族自治州';5226,'黔东南苗族侗族自治州';5227,'黔南布依族苗族自治州';5301,'昆明市';5303,'曲靖市';5304,'玉溪市';5305,'保山市';5306,'昭通市';5307,'丽江市';5308,'普洱市';5309,'临沧市';5323,'楚雄彝族自治州';5325,'红河哈尼族彝族自治州';5326,'文山壮族苗族自治州';5328,'西双版纳傣族自治州';5329,'大理白族自治州';5331,'德宏傣族景颇族自治州';5333,'怒江傈僳族自治州';5334,'迪庆藏族自治州';5401,'拉萨市';5402,'日喀则市';5403,'昌都市';5404,'林芝市';5405,'山南市';5406,'那曲市';5425,'阿里地区';6101,'西安市';6102,'铜川市';6103,'宝鸡市';6104,'咸阳市';6105,'渭南市';6106,'延安市';6107,'汉中市';6108,'榆林市';6109,'安康市';6110,'商洛市';6201,'兰州市';6202,'嘉峪关市';6203,'金昌市';6204,'白银市';6205,'天水市';6206,'武威市';6207,'张掖市';6208,'平凉市';6209,'酒泉市';6210,'庆阳市';6211,'定西市';6212,'陇南市';6229,'临夏回族自治州';6230,'甘南藏族自治州';6301,'西宁市';6302,'海东市';6322,'海北藏族自治州';6323,'黄南藏族自治州';6325,'海南藏族自治州';6326,'果洛藏族自治州';6327,'玉树藏族自治州';6328,'海西蒙古族藏族自治州';6401,'银川市';6402,'石嘴山市';6403,'吴忠市';6404,'固原市';6405,'中卫市';6501,'乌鲁木齐市';6502,'克拉玛依市';6504,'吐鲁番市';6505,'哈密市';6523,'昌吉回族自治州';6527,'博尔塔拉蒙古自治州';6528,'巴音郭楞蒙古自治州';6529,'阿克苏地区';6530,'克孜勒苏柯尔克孜自治州';6531,'喀什地区';6532,'和田地区';6540,'伊犁哈萨克自治州';6542,'塔城地区';6543,'阿勒泰地区'},2,0)

出生区县查询的话公式太长了，超过excel的允许，需要用到辅助列，

在E2中输入

=VLOOKUP(MID(B2,1,6)*1,辅助列!$A$1:$B$2844,2,0)

 

第二部分批量提取性别

身份证号第17位代表性别，奇数为男，偶数为女。利用if函数+mod函数+mid函数构建符合函数

在F2输入公式

=IF(MOD(MID(B2,17,1),2),'男','女')。

第三部分批量提取出生年月/年龄（周岁）/生肖属相

身份证号第17位代表性别，奇数为男，偶数为女。利用if函数+mod函数+mid函数构建符合函数

在G2输入公式

=TEXT(MID(B2,7,8),'0000-00-00')

也可以直接计算年龄（周岁）在H2中输入公式

=DATEDIF(TEXT(MID(B2,7,8),'00-00-00'),TODAY(),'y')

DATEDIF函数用于计算两个日期之间的间隔。=DATEDIF(开始日期,结束日期,指定要返回的类型),第三参数是Y，表示年，是M，就表示月，TODAY函数返回系统当前的日期。

生肖属相，在I2中输入公式

=MID('鼠牛虎兔龙蛇马羊猴鸡狗猪',MOD(TEXT((TEXT(MID(B2,7,8),'0000-00-00')),'[$-130000]e')-4,12)+1,1)

其中 TEXT(D2,'[$-130000]e')的作用就是将日期转换成阴历年份，然后在十二属相之间轮回。

 

第四部分批量提取星座

星座，在J2中输入公式

=LOOKUP(TEXT(TEXT(MID(B3,7,8),'0000-00-00'),'mdd')*1,{0,'摩羯座';121,'水瓶座';220,'双鱼座';321,'白羊座';421,'金牛座';522,'双子座';622,'巨蟹座';724,'狮子座';824,'处女座';924,'天秤座';1024,'天蝎座';1123,'射手座';1223,'摩羯座'})

主要思路是先提取出生月日，然后用vlookup函数匹配。

 

第五部分批量计算退休年龄

计算退休时间（以男性60岁，女性55岁退休为例），在K2中输入公式

=EDATE(TEXT(MID(B2,7,8),'0!/00!/00'),MOD(MID(B2,15,3),2)*120+660)

EDATE(start_date,months) 其中start_date表示起始日期的日期，months：表示start_date 之前或之后的月份数。男性要经过720个月（60年），女性要经过660个月（55年）就是退休年龄了。

其基本思路是先用TEXT(MID(B2,7,8),'0!/00!/00')，计算出出生年月信息。

再使用MOD(MID(B2,15,3),2)计算性别码和2相除的余数，结果是1或是0。如果性别码和2相除的余数是1，则为奇数，判定为男性，MOD(MID(B2,15,3),2)*120计算结果为1，部分就相当于1*120+660，结果是720，及720月，合60年。

如果是女性，性别码和2相除的余数是0，则是0*120+660，结果是660，合55年。

第六部分最简单的办法

直接拿来用，所有的函数都已经设置好了

END