身份证号码中藏有很多信息,在前几篇,小编总结了身份证号码暗藏的四个信息:性别,出身年月,周岁,生肖。今天小编再讲身份证号码相关的三个信息:地区,退休日期,星座
上图的身份证号码都是虚构的,如有雷同,纯属巧合一、根据身份证号码判断所在省份
B2=LOOKUP(--LEFT(A2,2),{11,12,13,14,15,21,22,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,54,61,62,63,64,65},{"北京","天津","河北","山西","内蒙古","辽宁","吉林","黑龙江","上海","江苏","浙江","福建","安徽","江西","山东","河南","湖北","湖南","广东","广西","海南","重庆","四川","贵州","云南","西藏","陕西","甘肃","青海","宁夏","新疆"})
身份证前两位数字是判断省份的标识,我们通过left函数调取前两位数字,最后通过lookup函数多层级查找,构造一个参数表,来最终查询所在的省份。二、根据身份证号码判断退休日期
C2=EDATE(TEXT(MID(A2,7,8),"0-00-00"),720)
假设男女都是60岁退休。身份证号码从第七位开始的8个数字是出生年月。我们通过TEXT(MID(A2,7,8),"0-00-00")就能获得出生年月,edate函数是可以判断时间的函数,最后一个参数是月数,60年后也就是720个月后,因此最终公式是EDATE(TEXT(MID(A2,7,8),"0-00-00"),720)。三、根据身份证号码判断星座
D2=LOOKUP(--TEXT(MID(A2,LEN(A2)*62%,4),"1900-00-00"),{1,20,50,81,112,142,174,205,236,267,297,327,357},{"魔蝎","水平","双鱼","白羊","金牛","双子","巨蟹","狮子","处女","天秤","天蝎","射手"})
公式解读:十二星座和出生日期息息相关,再通过lookup函数函数多层级查找,构造一个参数表,来最终查询所在的星座。
以上公式都是可以拿来即用的。实用第一。