一个身份证号竟然包含这么多信息，你还敢随便泄露你的身份证号吗？
从事人事工作的朋友们少不了与身份证号打交道，数据量大的时候如何批量操作能？
【本文示例文件获取方法见文末】
今天，技巧君来讲讲身份证号！国际惯例，先来看下效果：
身份证号构成
身份证号由四部分组成：
地址码：表示编码对象常住户口所在县（市、旗、区）的行政区划代码
出生日期码 ：表示编码对象出生的年、月、日
顺序码 ：表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性
校验码：根据前面十七位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。
所以从身份证号中提取信息，我们需要对身份证号进行分段处理，要从地址码中对应提取出籍贯，必须有一个地区和编码之间的编码表，下面来看一下具体怎么制作吧！
身份证号提取性别
函数公式：=IF(MOD(MID(A2,17,1),2),”男”,”女”)
说明：
a、MID(A2,17,1)函数提取第17位数
b、MOD函数对2取余数，偶数余数为0，说明为女性，奇数余数为1，说明为男性，再用IF函数来判断性别
c、外层再嵌套IFERROR规避错误值
身份证号提取年龄
函数：DATEDIF(–TEXT(MID(A2,7,8),”0-00-00″),TODAY(),”y”)
说明：
a、MID(A2,7,8)提取出生日期码
b、TEXT函数将文本处理为日期格式，–减负运算将文本转化为日期值
c、DATEDIF函数计算出生日期和今天的差值，即得到年龄
身份证号提取出生日期
函数：TEXT(MID(A2,7,8),”0年00月00日”)
说明：
a、MID(A2,7,8)，提取出生日期码
b、TEXT函数处理为日期格式
身份证号提取生肖
函数：MID(“鼠牛虎兔龙蛇马羊猴鸡狗猪”,MOD(MID(A2,7,4)-4,12)+1,1)
说明：
a、MID(A2,7,4)提取出生年份
b、MOD函数对12取余数，刚好对应12生肖的序列代码，里面日期的-4和外面的 +1是为了和前面的生肖相对应
身份证号提取星座
函数：VLOOKUP(VALUE(“1900-“&TEXT(MID(A2,LEN(A2)/2+2,4),”#-##”)),{1,”摩羯座”;20,”水瓶座”;50,”双鱼座”;81,”白羊座”;111,”金牛座”;142,”双子座”;174,”巨蟹座”;205,”狮子座”;236,”处女座”;267,”天秤座”;298,”天蝎座”;328,”射手座”;357,”摩羯座”},2,TRUE)
说明：这个函数主要是了解星座和日期之间的换算关系，有兴趣可以做进一步了解，因计算规则较为复杂，这里不做详述
身份证号提取籍贯信息
函数：VLOOKUP(LEFT(A2,6),地区代码!A:B,2,1)
说明：籍贯信息主要有地区代码表，将代码换算为地区就可以了
注意事项：
1、本文所用所有身份证号均为地区码+出生日期码+顺序码+校验码随机组合而成，非真实身份证号，如有雷同，纯属巧合！
2、本文涉及的主要函数有：MID、MOD、TEXT、IF、VLOOKUP、DATEDIF、IFERROR、VALUE、内存数组等，这些公式都为常用公式，这里只做用途功能介绍，想要详细学习以上函数的基础用法，请持续关注 EXCEL精选技巧的后续文章！
3、VLOOKUP函数已有文章做了详细阐述，请参考技巧君的另一篇文章《连VLOOKUP这些用法都不会，别说你会EXCEL！》点击技巧君头像，查看历史文章即可
示例文件获取
如下图，要根据A列的身份证号码提取出生日期，判断性别和计算年龄
提取出生日期
现在身份证号码都是18位，代表出生日期的是第7位-第14位，总共8位数字，
所以在B2单元格输入公式：=–TEXT(MID(A2,7,8),”0-00-00″)
公式说明：使用MID函数从第7位开始提取，提取8位数，但是提取出来的是一串数字，并不属于日期格式，所以使用TEXT函数将数字转换为日期格式。
判断性别
身份证号码的第17位数字是代表性别的，奇数为男生，偶数为女生，所以在C2单元格输入公式：=IF(MOD(MID(A2,17,1),2),”男”,”女”)
公式说明：用MID函数提取第17位代表性别的数字，然后用MOD函数将第17位数跟2相除取余数，奇数跟2相除的余数为1，偶数跟2相除的余数为0，再用if函数判断MOD函数的余数是1则返回“男” 是0则返回“女”
计算年龄
在D2单元格输入公式：=DATEDIF(B2,TODAY(),”Y”)
公式说明：用DATEDIF函数计算两个日期之间相差的日期间隔，用出生日期跟今天的日期（用TODAY函数表示当前的日期），计算类型为“Y”(年）
本文来自投稿，不代表天一生活立场，如若转载，请注明出处：http://tiyigo.com/it/6157.html
