Vlookup的兄弟Lookup大显身手,秒秒钟搞定数据提取
送你200篇独家Excel精华教程
全套Excel视频,限时特价,扫码观看!
编按:说到LOOKUP函数,其实是很多人在工作中经常用到的,甚至有些时候比VLOOKUP更有用。今天,我们就来讲讲用LOOKUP如何快速的处理难度系数较高的数据提取……
正文:
金庸先生有“射雕”三部曲,我也来一个“汇总文本中的数据”三部曲!
前几天,我向大家介绍了“事项和金额录入在一起的流水账,如何汇总报销?”和“怎样快速提取产品成分表中的百分比并求和?”。
今天,这里再向大家分享一篇如何提取两段文本间数字的技巧。
题目很简单,用公式提取出整箱的包装数量,并计算装满物资的整箱数和最后装不完整箱时所剩下的尾数。
图一
分析一下题目的要求:
1.前两行的“装箱要求”字符串中都只包含了一个数字,处理起来比较简单,用前面介绍过的“MIDB+SEARCHB”或“LEFT+LEN”均可以处理。
2.第三、四行的“装箱要求”字符串中包含了至少2个数字,这样上面提到的方法就不可行了。
3.这四行文本字符串中我们需要的数字前后没有明显的共同特征,因此不方便在数字后面用公式来插入空格。
困难比较大,但仔细想想,我们还是可以稍微借鉴一下上期文章中介绍过的思路。
1
步骤一
先从左向右提取字符串,提取后的字符串最右侧不应再包含文本字符;再从右向左提取数值。
图二
在单元格G2中我们输入公式“=-LOOKUP(1,-RIGHT(LEFT(C2,LOOKUP(1,-MID(C2,ROW($1:$19),1),ROW($1:$19))),ROW($1:$19)))”并向下拖曳即可。
函数解析:
MID(C2,ROW($1:$19),1)部分,用MID公式依次从单元格C2中字符串的第一位、第二位…,提取长度为1的字符。结果为{"3";"6";"0";"只";"/";"箱";"";"";"";"";"";"";"";"";"";"";"";"";""}。这里ROW($1:$19)表示从第一位到第十九位,实际上我们输入时数字只要大于字符串的长度就可以了。
-MID(C2,ROW($1:$19),1)部分将非数值的字符串转换为错误值,结果为{-3;-6;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}。
LOOKUP(1,-MID(C2,ROW($1:$19),1),ROW($1:$19))部分是本例的一个精华之处。利用LOOKUP函数的特点,在数组{-3;-6;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}中查找“1”。因为1大于字符串中所有数字,所以LOOKUP函数会返回查找区域中最后一个数字所对应的返回值。这里最后一个数字是0,位于第3位,它所对应的值为ROW(3),因此LOOKUP函数的返回值是“3”。注意观察一下,第一行的字符串中,数值型的字符串长度就是3。
LEFT(C2,LOOKUP(1,-MID(C2,ROW($1:$19),1),ROW($1:$19)))部分提取的结果是"360"。
2
步骤二
下面单独对第三和和第四行的函数再详细讲一讲。
图三
LEFT(C4,LOOKUP(1,-MID(C4,ROW($1:$19),1),ROW($1:$19)))部分和之前的思路都是一样的。
RIGHT(LEFT(),ROW($1:$19))部分,将LEFT函数提取到的字符串从右向左依次提取长度为1,2,…的字符串。结果为{"0";"00";"600";"盘600";"0盘600";"20盘600";"20盘600";"20盘600";"20盘600";"20盘600";"20盘600";"20盘600";"20盘600";"20盘600";"20盘600";"20盘600";"20盘600";"20盘600";"20盘600"}。到这里,距离我们想要的结果就不远了。
-RIGHT()将RIGHT提取的文本转为数值。其结果为{0;0;-600;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}。
再次利用LOOKUP函数就可以求得“-600”了,再做一次负运算,得到最终结果600。
3
步骤三
整箱数量提取完成后,分别用INT函数和MOD函数就可以求得整箱数和尾数了。
图四
请大家注意,如果字符串中有多个数字,本例中介绍的公式只能提取文本字符串中最右侧的数字哦!
给有兴趣的小伙伴们提个问题:
在数字、文本混合的字符串中,怎样提取各个数字部分,并将这些数字求和?
温馨提醒:
请点到名的粉丝们,抽时间来免费领取全套Excel课程学习。
详情请点击链接:宠粉送课第7期:阅读、分享、留言最多的粉丝,送课给你们!
Excel教程相关推荐