欢迎光临
我们一直在努力

excel vlookup的高阶用法——数据提取 不是很简单 但是很实用

今天跟大家分享下我们如何使用vlookup函数提取固定长度的数字,如下图,我们想要在这些毫无规律的字符串中将工号提取出来,对于这种毫无规律的数据,我们使用快速填充是提取不到准确的工号的,但是我们可以利用vlookup+mid函数快速提取工号

 

一、mid函数

在这里我们需要用到mid函数,先跟大家简单的介绍下这个函数

Mid函数:从一个字符串中截取出指定数量的字符

第一参数:想要提取的字符串
第二参数:想要从哪一位开始提取
第三参数:提取多少位

如下图,红色区域是我们要提取的字符串,当mid函数的第三参数每增加1,就会多提取一个字符

 

二、提取数字

在这里我们想要提取工号,而工号都是由6位数字组成的,所以我们在旁边输入公式

=VLOOKUP(0,{0,1}*MID(A2,ROW($1:$30),6),2,0)因为这个一个数组公式所以我们要按Ctrl+shift+回车三键填充公式,然后向下拖动即可,这样的话我们就提取到了工号,但是如果工号的第一位或者第二位是0的话,这个0我们是提取不到的,我们点击这一列,然后按Ctrl+1调出格式窗口,点击自定义,在类型中输入6个0,点击确定,这样的话就完成了

 

我们以1个字符串为例,跟大家简单的讲解着这个公式是如何计算的,这个公式是一个数组公式,

=VLOOKUP(0,{0,1}*MID(A2,ROW($1:$30),6),2,0)

第一参数:0,在这里查找值变成了0
第二参数:{0,1}*MID(A2,ROW($1:$30),6),我们使用这个公式来构建查找区域
第三参数:2,在使用构建的区域中查找结果在第二列
第四参数:0,精确匹匹配

在这个公式中mid函数它是一个数组公式,mid函数的第二参数变成了ROW($1:$30),它是一个1到30的序列,所以他的第二参数会从1开始计算30次,而mid函数的第三参数为6,所以我们会得到30个固定长度位6的字符串,如下图,在这里有的字符串的长度不是6是因为字符串长度不够

 

然后我们有将这个结果乘以数组{0,1},就代表的提取的字符串分别要与0和1计算一次,如下图,这个橙色的区域就是我们构建查找区域,虽然查找值有重复值存在,但是我们想要的结果他在第一个位置,所以我们可以查找到正确的结果

 

以上就是我们使用vlookup+mid函数提取固定长度数字的过程,它使用的数组公式,理解起来还是比较困难的,如果大家没有理解的话,直接套用即可

我是excel从零到一,关注我持续分享更多excel技巧

 收藏 (0) 打赏

您可以选择一种方式赞助本站

支付宝扫一扫赞助

微信钱包扫描赞助

未经允许不得转载:英协网 » excel vlookup的高阶用法——数据提取 不是很简单 但是很实用

分享到: 生成海报
avatar

热门文章

  • 评论 抢沙发

    • QQ号
    • 昵称 (必填)
    • 邮箱 (必填)
    • 网址

    登录

    忘记密码 ?

    切换登录

    注册

    我们将发送一封验证邮件至你的邮箱, 请正确填写以完成账号注册和激活