Excel怎么汇总货品清单中的文本数据?

作者：EXCEL应用之家来源：部落窝教育发布时间：2020-12-14 15:52:27点击：3038

分享到:

收藏收藏人气：0人

版权说明：原创作品，禁止转载。

编按：说到文本数据，深受其害的数据人不计其数！只要有他在，简单的数据汇总处理起来也会很麻烦，但我们还常常无法避免。那么，如何才能有效的处理此类问题呢？下面，我们就用“汇总货品清单”的案例来好好讲解一下……

正文：

金庸先生有“射雕”三部曲，我也来一个“汇总文本中的数据”三部曲！

前几天，我向大家介绍了“事项和金额录入在一起的流水账，如何汇总报销？”和“怎样快速提取产品成分表中的百分比并求和？”。

今天，这里再向大家分享一篇如何提取两段文本间数字的技巧。

题目很简单，用公式提取出整箱的包装数量，并计算装满物资的整箱数和最后装不完整箱时所剩下的尾数。

图一

分析一下题目的要求：

1. 前两行的“装箱要求”字符串中都只包含了一个数字，处理起来比较简单，用前面介绍过的“MIDB+SEARCHB”或“LEFT+LEN”均可以处理。

2. 第三、四行的“装箱要求”字符串中包含了至少2个数字，这样上面提到的方法就不可行了。

3. 这四行文本字符串中我们需要的数字前后没有明显的共同特征，因此不方便在数字后面用公式来插入空格。

困难比较大，但仔细想想，我们还是可以稍微借鉴一下上期文章中介绍过的思路。先从左向右提取字符串，提取后的字符串最右侧不应再包含文本字符；再从右向左提取数值。

图二

在单元格G2中我们输入公式“=-LOOKUP(1,-RIGHT(LEFT(C2,LOOKUP(1,-MID(C2,ROW($1:$19),1),ROW($1:$19))),ROW($1:$19)))”并向下拖曳即可。

函数解析：

l MID(C2,ROW($1:$19),1)部分，用MID公式依次从单元格C2中字符串的第一位、第二位…,提取长度为1的字符。结果为{"3";"6";"0";"只";"/";"箱";"";"";"";"";"";"";"";"";"";"";"";"";""}。这里ROW($1:$19)表示从第一位到第十九位，实际上我们输入时数字只要大于字符串的长度就可以了。

l -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!}。

l 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。

l LEFT(C2,LOOKUP(1,-MID(C2,ROW($1:$19),1),ROW($1:$19)))部分提取的结果是"360"。

下面单独对第三和和第四行的函数在详细讲一讲。

图三

l LEFT(C4,LOOKUP(1,-MID(C4,ROW($1:$19),1),ROW($1:$19)))部分和之前的思路都是一样的。

l 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"}。到这里，距离我们想要的结果就不远了。

l -RIGHT()将RIGHT提取的文本转为数值。其结果为{0;0;-600;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}。

l 再次利用LOOKUP函数就可以求得“-600”了,再做一次负运算，得到最终结果600。

整箱数量提取完成后，分别用INT函数和MOD函数就可以求得整箱数和尾数了。

图四