位置:牛账网 > 资讯中心 > 行业实操 > 用Excel提取号码应该怎么做?Excel数据处理怎么做?

用Excel提取号码应该怎么做?Excel数据处理怎么做?

2019-10-1413:10:01 来源: 牛账网

阅读人数:19336人 分享

导语:用Excel提取号码应该怎么做?Excel数据处理怎么做?无论我们需要用Excel处理何种数据,首先都应先对该数据有一个了解,在不了解它的情况下就直接处理数据,有可能会把方向做错,那样的数据处理结果就令人堪忧了。那我们怎么用Excel处理数据呢?用Excel提取号码应该怎么做呢?下面大家就和小编一起来详细了解一下吧。

 

用Excel提取号码应该怎么做?Excel数据处理怎么做?

我们都知道身份证号码一共有18位(以前的身份证号码是15位的,但是普及二代身份证这么多年,可以说一代的15位身份证已经极少使用了,所以今天主要讲解二代身份证的内容),身份证号码按照“号码段”区分大体可以分为6个信息:

● 1~2位省、自治区、直辖市代码;

● 3~4位地级市、盟、自治州代码;

● 5~6位县、县级市、区代码;

● 7~14位出生年月日;其中

7~10位,代表出生年份

11~12位,代表出生月份

13~14位,代表出生日期

● 15~17位为顺序号,其中17位(倒数第二位)是奇数则为男性,偶数为女性;

● 18位为校验码,0-9和X。


图片.png


我们辅以图示更加直观的记忆一下:


图片.png


应该说我们的身份证号中能有这么多的信息量,当初研究出这套编码规则的人也真是相当的不容易了。

【正文】

有了上面对于身份证的一个了解,我们就可以通过这个号码提取很多的内容了。

01、省、市、县自动提取

示例:


图片.png


函数解析:

对于这个函数来说,本身并不复杂,关键是这几张信息表。


图片.png


不是每一个同学都有的,现在你也可以拥有这么一套代码对照表了。

公式中所使用的MID函数是典型的字符串分段取值的函数。

 

MID函数语法:

=MID(text,start_num,num_chars)

翻译成白话就是在一个字符串(text)中,从某个序号位置(start_num)开始,提取之后连续的几个(num_chars)字符。

那我们再来看一下上面的函数:

B5单元格=VLOOKUP(MID($B$2,1,2),省级码对照表!A:B,2,0)

MID函数在B2单元格的身份证号中,提取出从第1位开始的连续两位字符,就是“51”;再把“51”带入到VLOOKUP函数中,并在《省级码对照表》中的A、B两列处进行索引即可。B7、B9单元格的市、县索引方式也是一样的,但是一定要记住,索引省,用到身份证的前两位;索引市,要用到身份证的前4位。索引出XX省XX市的格式,不能直接使用第3~4位,因为会和其他的省市码重复;县级代码同理,需要索引身份证号的前6位,不可单独索引第5~6位。

当然有些同学会问,用其他函数可以吗?针对第一个问题,用LEFT函数也可以,=VLOOKUP(LEFT($B$2,2),省级码对照表!A:B,2,0)


图片.png


但是建议同学们在身份证处理问题上,还是只使用MID函数来提取号码段,便于对身份证信息这一类问题的统一解决,而LEFT函数或者RIGHT函数,只适合左右两端的取值,对于中间部分的取值相对于MID函数就显得有些麻烦了。

 

对于市、县提取的更高要求

刚才我们说过了,如果直接用第3~4位,或者第5~6位的代码去索引,由于号码有重复,所以索引出的内容有可能就是错误的。但是在人事工作中,有时我们需要精准的提取出XX市、XX区县,那我们就可以使用下面的方式来处理。


图片.png


B14单元格函数:

=SUBSTITUTE(VLOOKUP(MID($B$2,1,4),省市码对照表!A:B,2,0),VLOOKUP(MID($B$2,1,2),省级码对照表!A:B,2,0),"")

聪明的你,是不是一眼就可以看出来,红色的VLOOKUP函数,提取的是XX省XX市;蓝色的VLOOKUP函数,提取的是XX省,我们再用SUBSTITUTE函数,将红色索引值中的XX省部分替换为空文本即可。县的提取也是同理,很简单的思路,但是在工作中十分实用,赶紧学起来吧!

 

以上就是牛账网会计培训班小编有关用Excel提取号码应该怎么做?Excel数据处理怎么做?给出的解答,希望对大家有所帮助。如果大家还想要了解更多有关会计类的相关知识的,敬请关注牛账网吧。


标签:

点击查看>>EXCEL内部资料包
上一篇文章:公司发生股权转让时财务人员应该要做些什么? 下一篇文章:公司发生股权转让时财务人员应该要做些什么?

相关资讯

相关问答

意见反馈

提交