微信|身份证号在Excel表中如何巧妙运用？五个公式让你统计员工信息变得易如反掌。
文章图片
文章图片
【微信|身份证号在Excel表中如何巧妙运用？五个公式让你统计员工信息变得易如反掌。】
文章图片
文章图片
Excel表是我们工作中经常用到的办公室软件之一 。 尤其在这个大数据时代 ， 对我们采集和整理各种数据至关重要的作用 。
Excel表中的各种函数公式让我们在对复杂数据的提取和使用的过程中 ， 常常会有事半功倍的效果 ， 尤其我们结合常识后 ， 让我们对信息的统计和使用会更有效率 。
众所周知 ， 我们的身份证号给我们提供了很多重要信息 ， 比如性别、出生年月、身份证登记地等 ， 那么我们如何在Excel表中合理运用这些常识来进行更好的数据统计？今天给给大家讲解一下身份证号在Excel表中的巧妙运用 。
一、判断身份证号主性别
我们的身份证倒数第二个数字 ， 是判断性别的重要依据 ， 奇数（13579）为男生 ， 偶数（02468）为女生 ， 我们在Excel表中使用到的公式为IF条件判断函数和MOD函数 。
公式为：IF(MOD(MID(B2171)2)=1\"男\"\"女\")
函数说明：IF函数为条件判断函数 ， 我们通过判断身份证后第二个数字的奇偶数来决定是男生还是女生 。 MOD函数是一个求余函数 ， 这里用来看能否被2整除来判断奇偶数 ， 从而通过IF函数来判断男女性别 。
二、出生年月
我们的身份证第七个数字开始时我们的出生年月 ， 我们需要用到文本函数TEXT函数 ， 将我们的数值转换成文本格式 。
公式为：TEXT(MID(B278)\"0000年00月00日\")
函数说明：用MID从B2单元列表从第7个数字开始截取八个数字 ， 即年月日的信息 ， 然后使用TEXT文本把数字转换成出生年月 。
三、年龄
我们已经学会了出生年月的数据采集 ， 只需要用现在的年份减去出生年份既可得出年龄 。
公式为：YEAR(TODAY())-MID(B274)
函数说明：TODAY()可以获得当前电脑的时间 ， 我们用当前年份减去出生年份即可得到年龄 。
四、属相
我们的出生年月对应着固定的一个属相 ， 很多时候我们也要根据一个人的出生年份算出属相 ， 这就需要我们用到choose函数 。
公式为：=CHOOSE(MOD(MID(B274)-190012)+1\"鼠\"\"牛\"\"虎\"\"兔\"\"龙\"\"蛇\"\"马\"\"羊\"\"猴\"\"鸡\"\"狗\"\"猪\")
函数说明：先用MID函数提取身份证的出生年份再减去1900年 ， 看能不能被12整除 。 由于1900年是鼠年 ， 所以我们以12年为一轮使用choose函数依次从属相中选择并返回 ， 则可以通过身份证获得属相 。
五、户籍所在地
身份证号码的前6位数是户籍所在地的行政代码 ， 前12位数字是省份 ， 34为市区 ， 56为乡镇代码 。 我们通过身份证前6为寻找户籍所在地需要通过网络下载全国户籍行政代码表 ， 通过纵向查找vlookup函数来实现这个功能 。
公式为：=VLOOKUP(VALUE(LEFT(B24))Sheet2!A:B20)
函数说明：通过LEFT函数截取B2单元里的身份证号前四位（正常应该截取前6位 ， 为了快速寻找到结果 ， 测试寻找前4位 。 ）返回我存在户籍行政编码的第二张表即Sheet2!查找到A列表下的相同代码 ， 并导入对应的地区 。
很多人说身份证号码太长 ， Excel表没有办法正常显示 ， 这是因为EXCEL有效数字只有15位 ， 过长会使用默认的科学计数来表示 ， 这时只需要我们把表格单元格格式设置成文本即可正常显示18位数的身份证哦！
推荐阅读
- 各地地方风味特色菜！
- dna|DNA揭秘6200年前大屠杀的死者身份
- 吸烟|微信表情都戒烟了，你什么时候开始戒？
- 戒烟|微信表情都戒烟了！你还在吸三手烟吗？
- 吸烟|连微信新表情都“戒烟”了，你还在犹豫什么？
- 眼底病|眼底病检查与病例分析
- 北斗卫星导航|“北斗微信”纯属扯淡！千万别被骗了！
- 戒烟|连微信表情都“戒烟”了……
- 微信|微信10年烟龄如今成功“戒烟”，你还有什么理由继续抽烟呢？
- 戒烟|连微信表情都“戒烟”了，你为什么还舍不得掐掉手中的烟？