户口所在地代码查询（单位所在地代码怎么查）原创千年古月杨2021-04-25 23:03:24
巧取身份证六大信息（申明：本文所用身份证号均为随机组合而成，非真实身份证号，如有雷同，纯属巧合）
在实际的办公应用中，大量人员信息处理是件很繁琐的事情，包括有身份证号、出生日期、性别、年龄、籍贯，甚至是个人生肖、星座等，那么如何利用身份证号快速自动的录入相关信息，实现又快又准的完成工作。下面就以我国身份证的信息特点，利用身份证号，在excel表格中巧妙提取身份证六大信息，快速轻松完成人员信息处理。
当前，我国的身份证号码为18位，如图1，其中里面的信息特点为，第1到6位为全国行政户籍区划代码，第7到14位为出生日期数，倒数第2位为性别代码，偶数为女，奇数为男。我们可以利用excel中的函数，根据已输入的身份证号码，快速提取相关信息，准确完成人员信息处理。
图1
一.身份证号提取出生日期
函数公式：=IFERROR(TEXT(MID(B2,7,8),"0-00-00"),"")
说明：
a.MID(B2,7,8)提取出生日期码
b.TEXT函数将文本处理为日期格式
c.外层嵌套IFERROR函数是为了空单元格规避错误值
如图2所示：
图2
二．身份证号提取岁数
函数公式：=IFERROR(DATEDIF(--TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"y"),"")
说明：
a.MID(A2,7,8)提取出生日期码
b.TEXT函数将文本处理为日期格式，--减负运算将文本转化为日期值
c.DATEDIF函数计算出生日期和今天的差值，即得到年龄
d.外层嵌套IFERROR函数是为了空单元格规避错误值
如图3所示：
图3
三．身份证号提取性别
函数公式：=IFERROR(IF(MOD(MID(B2,17,1),2),"男","女"),"")
说明：
a.MID(B2,17,1)函数提取第17位数
b.MOD函数对2取余数，偶数余数为0，说明为女性，奇数余数为1，说明为男性，再用IF函数来判断性别
c.外层嵌套IFERROR函数是为了空单元格规避错误值
如图4所示：
图4
四．身份证号提取生肖
函数公式：=IFERROR(MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(C2)-4,12)+1,1),"")
说明：
a.MID(B2,7,4)提取出生年份
b.MOD函数对12取余数，刚好对应12生肖的序列代码，里面日期的-4和外面的 +1是为了和前面的生肖相对应
c.外层嵌套IFERROR函数是为了空单元格规避错误值
d.身份证提取生肖是按公历计算的，我国生肖多数都是以农历计算为准，所以略有偏差，实际中需做适当调整
e.上面生肖的提取是以，身份证号提取的出生年月来提取计算的，实际中也可用身份证号直接提取阳历生肖，其函数公式为：
=IFERROR(IF(LEN(B9)=15,MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD("19"&MID(B9,7,2)-4,12)+1,1),MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(MID(B9,7,4)-4,12)+1,1)),"")
如图5所示：(利用出生年月提取生肖)
图5
如图6所示：(利用身份证号直接提取生肖)
图6
五．身份证号提取籍贯
函数公式：=IFERROR(VLOOKUP(LEFT(B2,6),户籍行政区划代码表!A:B,2,1),"")
说明：
a.籍贯信息需要有户籍行政区划代码表作为附表，通过函数代码换算为地区就可以
b.户籍行政区划代码表，可以通过网络下载最新版的区划表，根据需求做好调整使用即可。
c.外层嵌套IFERROR函数是为了空单元格规避错误值
如图7、8所示：
图7
图8
六．身份证号提取星座
函数公式：=IFERROR(LOOKUP(--TEXT(C2,"mdd"),{101,"摩羯";120,"水平";219,"双鱼";321,"白羊";420,"金牛";521,"双子";621,"巨蟹";723,"狮子";823,"处女";923,"天秤";1023,"天蝎";1122,"射手";1222,"摩羯"})&"座","")
说明：
a. C2的数字转换为日期，取其中的月和日对应的数字，然后与后面的数字模糊匹配，找到比这个数字小的数字，然后取对应的星座。
b.TEXT(C2,"mdd") 是把C2的数字转换为日期，取其中的月和日对应的数字，在Excel里日期都是以一个数字来记录的，因此数字能转换为日期，这里不要年，只保留月和日，因为计算星座只要月日。
c.TEXT(C2,"mdd") 前面加两格减号--TEXT(C2,"mdd") 是把字符串转换为纯数字，得到的数字作为lookup函数的第一个参数，表示要查找的数字。
d.后面的{}里是查询数组，逗号表示是在同一行，分号表示下一行，这里就是一个数字一个逗号一个星座一个分号为一行，其实就是两列，第一列是数字，第二列是星座，第一列的数字是两个星座分隔的日期。
使用lookup函数查询前面那个数字在后面这个数组中哪个区间，然后就得到对应的星座了。
e.外层嵌套IFERROR函数是为了空单元格规避错误值
f.上面星座的提取是以，身份证号提取的出生年月日来提取计算的，实际中也可用身份证号直接提取星座，其函数公式为：
=IFERROR(LOOKUP(--MID(B2,11,4),{101,"摩羯";120,"水瓶";219,"双鱼";321,"白羊";420,"金牛";521,"双子";621,"巨蟹";723,"狮子";823,"处女";923,"天秤";1023,"天蝎";1122,"射手";1222,"摩羯"})&"座","")如图9所示：（利用出生年月提取星座）
图9
如图10所示：（利用身份证号直接提取星座）
图10
注：
在提取函数中都增加了一个函数IFFRROR，目的是为了避免空单元格的错误值，方便实际的录入操作，实际中可以根据情况可以不使用此函数。
出现的有些函数只做了部分用途功能介绍，对于excel函数的各种用法，需读者在不断学习中理解。
收藏举报0条评论