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教程相关推荐

烧脑的小学题:“5.4%我的,她65.96%”,不用口算笔算,怎么求和?【Excel教程】
VLOOKUP和LOOKUP查询双雄战(一):VLOOKUP的漂亮开局
再见VLOOKUP!一对多查找,这个新函数好用到爆哭!【Excel教程】
再因为Excel核对数据而加班,买块豆腐吧!难道12种方法不够你用?!
(0)

相关推荐