除B列的身份证号码由手动录入外，C-K列的所有信息都可以通过身份证号码计算出来，你相信吗？

知道凭身份证号查名字(专业调查个人隐私)

在知道Excel函数之前，我也是不信的，肯定是有什么机关……

但是，不得不信啊。

我们先以其中一个身份证为例，来讲讲身份证号码中的秘密：

知道凭身份证号查名字(专业调查个人隐私)

身份证号码由18位组成，其中：

前1、2位数字表示：所在省（直辖市、自治区）的代码；

第3、4位数字表示：所在地级市（自治州）的代码；

第5、6位数字表示：所在区（县、自治县、县级市）的代码；

第7—14位数字表示：出生年、月、日；

第15、16位数字表示：所在地的派出所的代码；

第17位数字表示性别：奇数表示男性，偶数表示女性；

第18位数字是校检码：也有的说是个人信息码，不是随计算机的随机产生，它是 用来检验身份证的正确性。校检码可以是0—9的数字，有时也用X表示。

在录入省、市、区县的的公式之前，我们需要在国家统计局获取到行政区划代码表，并处理为如下样式：

知道凭身份证号查名字(专业调查个人隐私)

接下来就可以开始大干一场了~

省份：=VLOOKUP(LEFT(B2%2c2)%2c行政区划数据!H:I%2c2%2cFALSE)

城市：=VLOOKUP(LEFT(B2%2c4)%2c行政区划数据!K:L%2c2%2cFALSE)

区县：=VLOOKUP(LEFT(B2%2c6)%2c行政区划数据!B:E%2c4%2cFALSE)

F-K列的信息都能根据身份证号码中的规律来获取

生日：=–TEXT(MID(B2%2c7%2c8)%2c”0-00-00″)

使用MID从身份证号码中的第7位数值开始截取8位连续数值，并利用TEXT转化为日期样式，利用减负运算，将文本型日期转化为数值日期

最后需要通过自定义格式设置为日期格式，确保生日是规范日期，可计算

年龄：=DATEDIF(F2%2cTODAY%2c”y”)

利用DATEDIF从生日计算年龄

性别：=IF(MOD(MID(B2%2c17%2c1)%2c2)%2c”男”%2c”女”)

提取身份证号码中的第17位数值，并利用MOD求余判断数值奇偶，来判定性别

知道凭身份证号查名字(专业调查个人隐私)

以及一些非常用的信息提取：

生肖：=MID(“鼠牛虎兔龙蛇马羊猴鸡狗猪”%2cMOD(TEXT(F2%2c”[$-130000]e”)-4%2c12)+1%2c1)

利用MOD对修正顺序后的年份求余，获取到生肖位置，使用MID提取对应位置的字符

星座：=LOOKUP(–TEXT(F2%2c”mdd”)%2c{0%2c”摩羯座”;120%2c”水瓶座”;219%2c”双鱼座”;321%2c”白羊座”;420%2c”金牛座”;521%2c”双子座”;622%2c”巨蟹座”;723%2c”狮子座”;823%2c”处女座”;923%2c”天秤座”;1024%2c”天蝎座”;1123%2c”射手座”;1222%2c”摩羯座”})

先排列出每个星座的开始日期，利用text格式化月日为mdd样式，使用LOOKUP获取对应的星座（注意摩羯座因为跨年的原因，在首尾都有出现）

幸运数字：=MOD(TEXT(F2%2c”emmdd”)%2c9)

幸运数字通常将年月日各位数值逐位累加，至个位为止。利用9余数特性，使用MOD取巧算出幸运数字

快学好Excel函数，应用到工作中吧……