身份证号码和excel不得不说的故事，excel居然可以提取这6类信息
hello，大家好，今天跟大家介绍下身份证号码是如何构成的以及我们能从身份证号码中提取到那些信息，废话不多说，我们先来看下我们能从身份证号码中得到那些信息吧
从上图中可以看到，我们能从身份证号码中提取6种信息，所有的数据提取都是建立在对数据源的理解之上，我们先来看下身份证号码是如何组成的
一、身份证号码的组成
如上图
第一位与第二位是省代码，第三位与第四位是市代码，第五位与第六位是区代码
身份证的前6就构成了我们的籍贯
第七位到第十四位是我们的出生日期
第十五位带第十七位是顺序码，就是在同一区域中对同年同月同日生的人进行按照顺序标号，将顺序码的最后一位，也就是第十七位,奇数分给男性，将偶数分给女性
第十八位校验码，根据前面的十七位数字，根据公式计算出来的，这个计算比较复，跟我们的数据提取无直接关系，就不多做介绍了
了解了身份证号码的组成，下面我们就来学习下如何提取身份证号码中的数据
二、提取身份证号码中的数据1.提取籍贯
公式：=vlookup(left(a3,6),sheet2!a:b,2,0)
在这里我们首先使用left(a3,6)函数来提取身份证号码前6位，因为在sheet2中有籍贯对照表，所有我们可以使用vlookup函数进行匹配查找
2.提取出生日期
公式：=--text(mid(a3,7,8),0-00-00)
在这里我们先使用mid(a3,7,8)函数来提取身份证号码中的出生日期，然后使用text函数将提取出来的出生日期设置为时间格式的显示方式，因为使用text函数设置过格式的数据它都是文本格式，所以我们在函数前面增加两个减号，将文本格式转换为日期格式
3.提取年龄
公式：=datedif(c3,today(),y)
在这里我们使用datedif函数计算年龄，
第一参数：c3就是我们上一步提取的出生年月
第二参数：today（），这个函数会返回当前的日期
第三参数：“y”，代表计算两个日期的年份之差
4.判断性别
公式：=if(mod(mid(a3,17,1),2)=1,男,女)
在这里我们首先使用mid函数来提取身份证号码的第十七位，如果第十七位是奇数性别为男，如果为偶数性别为女
mod函数是一个求余数的函数，我们mod函数的第一参数设置为提取出来的身份证的第十七位，将mod函数的第二参数设置为2，当有余数的时候mod的结果是1，当没有余数的时候mod的结果是0，
最后我们使用if函数判断，在这里我们将条件设置为mod(mid(a3,17,1)=1,这个的意思是判断第十七数字是不是奇数，如果是奇数函数的结果就是男，如果不是奇数函数的结果就是女
5.提取星座
公式：lookup(--mid(a3,11,4),{100;120;219;321;421;521;622;723;823;923;1023;1122;1222},{摩羯座;水瓶座;双鱼座;白羊座;金牛座;双子座;巨蟹座;狮子座;处女座;天秤座;天蝎座;射手座;摩羯座})
这个公式其实非常的简单，我们把它拆开来看就非常容易理解了
首先我们使用--mid(a3,11,4)函数来提取出生日期的月份以及天数，因为mid函数提取出来的结果都是文本格式，我们在mid函数前面加两个减号将其转换为数值格式
然后我们公式中看起来非常长的两列其实就是下图的一个二维数组，只不过我们将其嵌套在公式中了
在这里lookup的第一参数是我们提取的数据，第二参数是上表中的月份数值，第三参数是星座名称，lookup查找数据，我们需要先将数据升序排列，我们使用lookup如果找不到查找值，lookup函数会返回小于或等于查找值的最大值的这一特性来查找星座
6.提取属相
公式：= =choose(mod(mid(a3,7,4)-2008,12)+1,鼠,牛,虎,兔,龙,蛇,马,羊,猴,鸡,狗,猪)
这个公式首先我们使用mid(a3,7,4)，来提取出生的年份，然后我们将年份减去2008，有人会说为什么，其实减去哪一年都可以，在这里2008对应属相是鼠，我们只要将减去的年份对应属相然后依次向后排列即可
然后我们使用mod函数求mid(a3,7,4)-2008这个结果对于12的余数，最后加1是因为当没有余数的时候结果是0，当结果为0函数不能选取到鼠这个值，所以我们要加1，最后使用choose函数选取相应的结果
choose函数第一参数想要选取的值的位置，第二参数：数据区域，函数中属相的区域其实就是一列数据，只不过我们将其嵌套在函数中
以上就是我们从身份证号码中提取6中信息的方法，仅做了比较简单的介绍，不知道大家都能学会多少呢，如果觉得实在是学不会的话，其实是可以直接套用公式，更改下函数单元格位置即可，身份证号码包含这么多信息，大家一定要好好保护自己身份证号码
我是excel从零到一，关注我持续分享更多excel技巧
轴承6000-2rs/Z2/ZZ高精密高速 品质保证量大从优
便捷的通讯光缆_大量供应性价比高的|GYFXY光缆
二手中药提取罐 创源二手设备
功夫鸡排小吃加盟开店如何做好售后服务?
辽宁电线电缆哪家便宜？艾克电缆——便宜放心
身份证号码和excel不得不说的故事，excel居然可以提取这6类信息
找优质1.4577光亮棒1.4577材料公差是多少
零食店加盟 一扫光有品味的选择
产后恢复 月月亲月嫂
万森包装—河南模压托盘设备公司
pvc地板的清洁与保养冠奥通专业PVC运动地板安装
今年的苹果小屏幕手机iPhone SE2还有希望吗？
玉米 农用喷药覆膜机 农作物专用铺膜机
出国留学最受欢迎的八个国家，有没有你想去的
广东节能环保的燃油燃气锅炉现在什么价格
唐山轧制钢球
汉阳新公司注册登记流程
江苏木工板式家具生产线包括什么设备，迈吉克数控行业巅峰
购买幼儿园课桌@最新价？品质服务
太原环氧白陶瓷防腐钢管价格承压能力
从上图中可以看到，我们能从身份证号码中提取6种信息，所有的数据提取都是建立在对数据源的理解之上，我们先来看下身份证号码是如何组成的
一、身份证号码的组成
如上图
第一位与第二位是省代码，第三位与第四位是市代码，第五位与第六位是区代码
身份证的前6就构成了我们的籍贯
第七位到第十四位是我们的出生日期
第十五位带第十七位是顺序码，就是在同一区域中对同年同月同日生的人进行按照顺序标号，将顺序码的最后一位，也就是第十七位,奇数分给男性，将偶数分给女性
第十八位校验码，根据前面的十七位数字，根据公式计算出来的，这个计算比较复，跟我们的数据提取无直接关系，就不多做介绍了
了解了身份证号码的组成，下面我们就来学习下如何提取身份证号码中的数据
二、提取身份证号码中的数据1.提取籍贯
公式：=vlookup(left(a3,6),sheet2!a:b,2,0)
在这里我们首先使用left(a3,6)函数来提取身份证号码前6位，因为在sheet2中有籍贯对照表，所有我们可以使用vlookup函数进行匹配查找
2.提取出生日期
公式：=--text(mid(a3,7,8),0-00-00)
在这里我们先使用mid(a3,7,8)函数来提取身份证号码中的出生日期，然后使用text函数将提取出来的出生日期设置为时间格式的显示方式，因为使用text函数设置过格式的数据它都是文本格式，所以我们在函数前面增加两个减号，将文本格式转换为日期格式
3.提取年龄
公式：=datedif(c3,today(),y)
在这里我们使用datedif函数计算年龄，
第一参数：c3就是我们上一步提取的出生年月
第二参数：today（），这个函数会返回当前的日期
第三参数：“y”，代表计算两个日期的年份之差
4.判断性别
公式：=if(mod(mid(a3,17,1),2)=1,男,女)
在这里我们首先使用mid函数来提取身份证号码的第十七位，如果第十七位是奇数性别为男，如果为偶数性别为女
mod函数是一个求余数的函数，我们mod函数的第一参数设置为提取出来的身份证的第十七位，将mod函数的第二参数设置为2，当有余数的时候mod的结果是1，当没有余数的时候mod的结果是0，
最后我们使用if函数判断，在这里我们将条件设置为mod(mid(a3,17,1)=1,这个的意思是判断第十七数字是不是奇数，如果是奇数函数的结果就是男，如果不是奇数函数的结果就是女
5.提取星座
公式：lookup(--mid(a3,11,4),{100;120;219;321;421;521;622;723;823;923;1023;1122;1222},{摩羯座;水瓶座;双鱼座;白羊座;金牛座;双子座;巨蟹座;狮子座;处女座;天秤座;天蝎座;射手座;摩羯座})
这个公式其实非常的简单，我们把它拆开来看就非常容易理解了
首先我们使用--mid(a3,11,4)函数来提取出生日期的月份以及天数，因为mid函数提取出来的结果都是文本格式，我们在mid函数前面加两个减号将其转换为数值格式
然后我们公式中看起来非常长的两列其实就是下图的一个二维数组，只不过我们将其嵌套在公式中了
在这里lookup的第一参数是我们提取的数据，第二参数是上表中的月份数值，第三参数是星座名称，lookup查找数据，我们需要先将数据升序排列，我们使用lookup如果找不到查找值，lookup函数会返回小于或等于查找值的最大值的这一特性来查找星座
6.提取属相
公式：= =choose(mod(mid(a3,7,4)-2008,12)+1,鼠,牛,虎,兔,龙,蛇,马,羊,猴,鸡,狗,猪)
这个公式首先我们使用mid(a3,7,4)，来提取出生的年份，然后我们将年份减去2008，有人会说为什么，其实减去哪一年都可以，在这里2008对应属相是鼠，我们只要将减去的年份对应属相然后依次向后排列即可
然后我们使用mod函数求mid(a3,7,4)-2008这个结果对于12的余数，最后加1是因为当没有余数的时候结果是0，当结果为0函数不能选取到鼠这个值，所以我们要加1，最后使用choose函数选取相应的结果
choose函数第一参数想要选取的值的位置，第二参数：数据区域，函数中属相的区域其实就是一列数据，只不过我们将其嵌套在函数中
以上就是我们从身份证号码中提取6中信息的方法，仅做了比较简单的介绍，不知道大家都能学会多少呢，如果觉得实在是学不会的话，其实是可以直接套用公式，更改下函数单元格位置即可，身份证号码包含这么多信息，大家一定要好好保护自己身份证号码
我是excel从零到一，关注我持续分享更多excel技巧
轴承6000-2rs/Z2/ZZ高精密高速 品质保证量大从优
便捷的通讯光缆_大量供应性价比高的|GYFXY光缆
二手中药提取罐 创源二手设备
功夫鸡排小吃加盟开店如何做好售后服务?
辽宁电线电缆哪家便宜？艾克电缆——便宜放心
身份证号码和excel不得不说的故事，excel居然可以提取这6类信息
找优质1.4577光亮棒1.4577材料公差是多少
零食店加盟 一扫光有品味的选择
产后恢复 月月亲月嫂
万森包装—河南模压托盘设备公司
pvc地板的清洁与保养冠奥通专业PVC运动地板安装
今年的苹果小屏幕手机iPhone SE2还有希望吗？
玉米 农用喷药覆膜机 农作物专用铺膜机
出国留学最受欢迎的八个国家，有没有你想去的
广东节能环保的燃油燃气锅炉现在什么价格
唐山轧制钢球
汉阳新公司注册登记流程
江苏木工板式家具生产线包括什么设备，迈吉克数控行业巅峰
购买幼儿园课桌@最新价？品质服务
太原环氧白陶瓷防腐钢管价格承压能力