身份证号在Excel表中如何巧妙运用？五个公式让你统计员工信息变得易如反掌。

身份证号在Excel表中如何巧妙运用？五个公式让你统计员工
信息变得易如反掌。

Excel表是我们工作中经常用到的办公室软件之一。

尤其在这个大数据时代，对我们采集和整理各种数据至关重要的作用。

Excel表中的各种函数公式让我们在对复杂数据的提取和使用的过程中，常常会有事半功倍的效果，尤其我们结合常识后，让我们对信息的统计和使用会更有效率。

众所周知，我们的身份证号给我们提供了很多重要信息，比如性别、出生年月、身份证登记地等，那么我们如何在Excel表中合理运用这些常识来进行更好的数据统计？今天给给大家讲解一下身份证号在Excel表中的巧妙运用。

一、判断身份证号主性别
我们的身份证倒数第二个数字，是判断性别的重要依据，奇数（13579）为男生，偶数（02468）为女生，我们在Excel表中使用到的公式为IF条件判断函数和MOD函数。

公式为：IF(MOD(MID(B2,17,1),2)=1,'男','女')
函数说明：IF函数为条件判断函数，我们通过判断身份证后第二个数字的奇偶数来决定是男生还是女生。

MOD函数是一个求余函数，这里用来看能否被2整除来判断奇偶数，从而通过IF函数来判断男女性别。

二、出生年月
我们的身份证第七个数字开始时我们的出生年月，我们需要用到文本函数TEXT函数，将我们的数值转换成文本格式。

公式为：TEXT(MID(B2,7,8),'0000年00月00日')
函数说明：用MID从B2单元列表从第7个数字开始截取八个数字，即年月日的信息，然后使用TEXT文本把数字转换成出生年月。

三、年龄
我们已经学会了出生年月的数据采集，只需要用现在的年份减去出生年份既可得出年龄。

公式为：YEAR(TODAY())-MID(B2,7,4)
函数说明：TODAY()可以获得当前电脑的时间，我们用当前年份减去出生年份即可得到年龄。

四、属相
我们的出生年月对应着固定的一个属相，很多时候我们也要根据一个人的出生年份算出属相，这就需要我们用到choose函数。

公式为：=CHOOSE(MOD(MID(B2,7,4)-1900,12)+1,'鼠','牛','虎','兔','龙','蛇','马','羊','猴','鸡','狗','猪')
函数说明：先用MID函数提取身份证的出生年份再减去1900年，看能不能被12整除。

由于1900年是鼠年，所以我们以12年为一轮
使用choose函数依次从属相中选择并返回，则可以通过身份证获得属相。

五、户籍所在地
身份证号码的前6位数是户籍所在地的行政代码，前12位数字是省份，34为市区，56为乡镇代码。

我们通过身份证前6为寻找户籍所在地需要通过网络下载全国户籍行政代码表，通过纵向查找vlookup 函数来实现这个功能。

公式为：=VLOOKUP(VALUE(LEFT(B2,4)),Sheet2!A:B,2,0)
函数说明：通过LEFT函数截取B2单元里的身份证号前四位（正常应该截取前6位，为了快速寻找到结果，测试寻找前4位。

）返回我存在户籍行政编码的第二张表即Sheet2!查找到A列表下的相同代码，并导入对应的地区。

很多人说身份证号码太长，Excel表没有办法正常显示，这是因为
EXCEL有效数字只有15位，过长会使用默认的科学计数来表示，这时只需要我们把表格单元格格式设置成文本即可正常显示18位数的身份证哦！。