编按：

求和就用SUM函数，是我们日常办公中最简单最基础的办法。可是当一个单元格中出现几个数据且带有不同的单位，面对这样的不规范数据源，又该如何批量求和呢？下面，小E就和大家讲讲如何用最快捷的方法写出这样的求和公式……

觉得自己写得出公式已经很牛B了？可是你知道吗，还有人比你写的公式更简短简洁！

最近笔者看到这样一个题目，觉得很有意思，也很有帮助，于是分享给大家。

生活中常见的香烟，其包装规格是这样的：

-          每箱50条

-          每条10包

在下面这个题目中，大家一起帮助小卖部的李老板算一算到底有多少包香烟。

方法1

利用FREQUENCY函数，大家可以解决这个问题。

在单元格C2中输入公式“=SUM(IFERROR(--SUBSTITUTE(RIGHT(LEFT(A2,ROW($1:$10)),FREQUENCY(ROW($1:$10),ISERROR(--MID(A2,ROW($1:$10),1))*ROW($1:$10))),{"箱","条","包"},""),0)*{500,10,1})”，按CTRL+SHIFT+ENTER三键，并用鼠标向下拖曳即可。

函数解析：

①MID(A2,ROW($1:$10),1)部分，依次从第1个、第2个…提取单元格A2中字符串的字符，提取长度为1。其中，ROW($1:$10)的第二个参数的数值，只要是比字符串的位数大就可以。

②ISERROR(--MID(A2,ROW($1:$10),1))部分，--将出现的空值等转换为错误值，ISERROR函数将错误值转换为逻辑值。

③ISERROR(--MID(A2,ROW($1:$10),1))*ROW($1:$10)部分，屏蔽了单元格A2中字符串中的数字部分所对应的自然数序列，结果为{{0;0;3;0;5;0;7;8;9;10}。

④利用FREQUENCY在上述的区间内对ROW($1:$10)计频，结果为{0;0;3;0;2;0;2;1;1;1;0}。

⑤LEFT(A2,ROW($1:$10))部分，利用LEFT函数对A2依次提取1个、连续2个、连续3个…字符，其结果为{"1";"10";"10箱";"10箱2";"10箱2条";"10箱2条3";"10箱2条3包";"10箱2条3包";"10箱2条3包";"10箱2条3包"}。

⑥将LEFT函数和FREQUENCY函数的结果作为RIGHT函数的参数。然后，RIGHT函数依据它开始从右提取。请注意上边三组颜色所对应的值。RIGHT函数提取的结果是{"";"";"10箱";"";"2条";"";"3包";"包";"包";"包";#N/A}。

⑦利用SUBSTITUTE函数将“箱”、“条”和“包”用空值替换掉。再利用“--”和IFERROR函数将错误值转换为0，结果为{0,0,0;0,0,0;10,0,0;0,0,0;0,2,0;0,0,0;0,0,3;0,0,0;0,0,0;0,0,0;0,0,0}。

⑧最后，将上述结果乘以{500,10,1}，再用SUM函数求和得到最终结果。

小结：

这个函数长度为154个字符，对于很多小伙伴来说，不管是看起来还是写起来，都不太简洁。其实，还有更简单的公式可以用在这里，如下面的方法2！

方法2

这个公式就简单多了，更是笔者常用的最简短的方法之一。

在单元格C2中输入公式“=SUM(IFERROR(FIND(ROW($1:$999)&{"箱","条","包"},A2)^0*LEFT(ROW($1:$999))*10^LEN(ROW($1:$999)),)*{50,1,0.1})”，按CTRL+SHIFT+ENTER三键，并用鼠标向下拖曳即可。

这个构思比较奇妙！下面，大家一起来看看！

函数解析：

①ROW($1:$999)&{"箱","条","包"}部分，用行号和{"箱","条","包"}分别组合构成FIND函数的查找值。其结果是一个999行3列的矩阵。为什么是999？因为数据中最大数值是613，所取数值一定要大过这个数字。

②FIND(ROW($1:$999)&{"箱","条","包"},A2)^0部分，FIND函数查找到结果以后，返回一个数字信息后，并将其转换为“1”。

③LEFT(ROW($1:$999))部分，提取行号信息中最左边的数字

④LEFT(ROW($1:$999))*10^LEN(ROW($1:$999))部分，将上面提取的结果中的1-9扩大10倍；10-99扩大100倍；100-999扩大1000倍。

⑤把以上FIND函数部分和LEFT函数部分，两者相乘，得到一个999行3列的矩阵。在这个矩阵中，所有能被FIND函数查找到的数据，其位置上都对应地显示出LEFT函数的结果扩大倍数后的数值，其余都是错误值。

⑥利用IFERROR函数将错误值转为“0”

⑦最后乘上{50,1,0.1}，再套用SUM函数就可以求出最后的结果了。因为在LEFT函数的步骤中，是以10做为底数的，等于为最终的结果都同时扩大了10倍；所以在这个步骤中要缩小10倍，得出结果是{50,1,0.1}而不是{500,10,1}，其分别对应{"箱","条","包"}。

补充：

这个公式还可以改写成：

“=SUM(COUNTIF(A2,"*"&ROW($1:$999)&{"箱*","条*","包*"})*(LEFT(ROW($1:$999))*10^LEN(ROW($1:$999)))*{50,1,0.1})”

有兴趣的小伙伴可以试试分析一下其中的函数，剖析一下它们分别起了什么重用！

方法3

最后再向大家介绍一个公式。这个公式是有缺陷的，但是它的逻辑思路非常值得我们学习和借鉴，因此也分享给大家。

在单元格C2中输入公式“=SUM(IFERROR(SUBSTITUTE(RIGHTB(LEFT(A2,FIND({"箱";"包";"条"},A2)),4),{"箱";"包";"条"},)*{500;1;10},))”，按CTRL+SHIFT+ENTER三键，并用鼠标向下拖曳即可。

大家可以看到，这个公式的结果几乎都是正确的，但在计算613箱是出了错！

现在，一起来分析一下这个公式的思路。

函数解析：

①FIND({"箱";"包";"条"},A2))部分。这部分的理解比较简单，即在单元格A2中分别查找{"箱";"包";"条"}，并返回具体的位置信息。

②LEFT(A2,FIND({"箱";"包";"条"},A2)),4)部分，依次从左向右提取字符串，其结果是{"10箱";"10箱2条3包";"10箱2条"}。

③RIGHTB函数部分，从右提取4个字节。注意，是字节，不是字符。由于"10箱2条3包"和"10箱2条"提取不到第4个字节，因此实际只提取了3个字节。这部分的结果是{"10箱";" 3包";" 2条"}，注意“3”和“2”前面是有空格的。

④利用SUBSTITUTE函数将"箱"、"包"和"条"用空值替换。

剩下的部分就比较简单了，这里就不再花更多的笔墨介绍了。

从本期的帖子中，大家可以看到，不同的思路，写出来的公式有长有短，但没有优劣之分，只有逻辑思路不同。多思考，多练习，才是掌握函数技巧的秘籍！

最后留给小伙伴们一道思考题目：

最后一个公式中，怎样解决计算613箱时出现的错误？

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

如何对带单位的数字进行求和

小白最爱的自动求和，恰是连专家也坑的老虎

SUM函数的进阶用法：快速对交叉区域、应收款项、小计行求和！

灵活组合或嵌套函数编写公式解决问题的技巧2：参数替换法

版权申明：

本文作者EXCEL应用之家；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。