为什么00后同事仅用一个求和公式就得到了老板的赏识？这就是答案！
每天一点小技能
职场打怪不得怂
编按：当数字不是单一出现在一个单元格中，甚至一个单元格中有多个不同量词的数字时，使用SUM函数求和就不再简单。这时，如果表格数据简单，我们可以用分列等方法重新做表；可是当表格数据足够复杂庞大，再重新做表就费时费力。最佳的办法让SUM函数与其它函数的进行嵌套，才能让统计求和顺利高效的完成……
觉得自己写得出公式已经很牛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箱时出现的错误？
扫一扫，在线咨询Excel课程
Excel教程相关推荐
想要全面系统学习Excel，不妨关注部落窝教育《一周Excel直通车》视频课或者《Excel极速贯通班》。
主讲老师：滴答
Excel技术大神，资深培训师；
课程粉丝100万+；
开发有《Excel小白脱白系列课》
《Excel极速贯通班》。
原价299元
限时特价 99 元
少喝两杯咖啡，少吃两袋零食
就能习得受用一生的Excel职场技能！