編按：

求和就用SUM函數(shù)，是我們?nèi)粘＾k公中最簡單最基礎(chǔ)的辦法。可是當一個單元格中出現(xiàn)幾個數(shù)據(jù)且?guī)в胁煌膯挝?，面對這樣的不規(guī)范數(shù)據(jù)源，又該如何批量求和呢？下面，小E就和大家講講如何用最快捷的方法寫出這樣的求和公式……

覺得自己寫得出公式已經(jīng)很牛B了？可是你知道嗎，還有人比你寫的公式更簡短簡潔！

最近筆者看到這樣一個題目，覺得很有意思，也很有幫助，于是分享給大家。

生活中常見的香煙，其包裝規(guī)格是這樣的：

-          每箱50條

-          每條10包

在下面這個題目中，大家一起幫助小賣部的李老板算一算到底有多少包香煙。

方法1

利用FREQUENCY函數(shù)，大家可以解決這個問題。

在單元格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三鍵，并用鼠標向下拖曳即可。

函數(shù)解析：

①MID(A2,ROW($1:$10),1)部分，依次從第1個、第2個…提取單元格A2中字符串的字符，提取長度為1。其中，ROW($1:$10)的第二個參數(shù)的數(shù)值，只要是比字符串的位數(shù)大就可以。

②ISERROR(--MID(A2,ROW($1:$10),1))部分，--將出現(xiàn)的空值等轉(zhuǎn)換為錯誤值，ISERROR函數(shù)將錯誤值轉(zhuǎn)換為邏輯值。

③ISERROR(--MID(A2,ROW($1:$10),1))*ROW($1:$10)部分，屏蔽了單元格A2中字符串中的數(shù)字部分所對應(yīng)的自然數(shù)序列，結(jié)果為{{0;0;3;0;5;0;7;8;9;10}。

④利用FREQUENCY在上述的區(qū)間內(nèi)對ROW($1:$10)計頻，結(jié)果為{0;0;3;0;2;0;2;1;1;1;0}。

⑤LEFT(A2,ROW($1:$10))部分，利用LEFT函數(shù)對A2依次提取1個、連續(xù)2個、連續(xù)3個…字符，其結(jié)果為{"1";"10";"10箱";"10箱2";"10箱2條";"10箱2條3";"10箱2條3包";"10箱2條3包";"10箱2條3包";"10箱2條3包"}。

⑥將LEFT函數(shù)和FREQUENCY函數(shù)的結(jié)果作為RIGHT函數(shù)的參數(shù)。然后，RIGHT函數(shù)依據(jù)它開始從右提取。請注意上邊三組顏色所對應(yīng)的值。RIGHT函數(shù)提取的結(jié)果是{"";"";"10箱";"";"2條";"";"3包";"包";"包";"包";#N/A}。

⑦利用SUBSTITUTE函數(shù)將“箱”、“條”和“包”用空值替換掉。再利用“--”和IFERROR函數(shù)將錯誤值轉(zhuǎn)換為0，結(jié)果為{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}。

⑧最后，將上述結(jié)果乘以{500,10,1}，再用SUM函數(shù)求和得到最終結(jié)果。

小結(jié)：

這個函數(shù)長度為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三鍵，并用鼠標向下拖曳即可。

這個構(gòu)思比較奇妙！下面，大家一起來看看！

函數(shù)解析：

①ROW($1:$999)&{"箱","條","包"}部分，用行號和{"箱","條","包"}分別組合構(gòu)成FIND函數(shù)的查找值。其結(jié)果是一個999行3列的矩陣。為什么是999？因為數(shù)據(jù)中最大數(shù)值是613，所取數(shù)值一定要大過這個數(shù)字。

②FIND(ROW($1:$999)&{"箱","條","包"},A2)^0部分，FIND函數(shù)查找到結(jié)果以后，返回一個數(shù)字信息后，并將其轉(zhuǎn)換為“1”。

③LEFT(ROW($1:$999))部分，提取行號信息中最左邊的數(shù)字

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

⑤把以上FIND函數(shù)部分和LEFT函數(shù)部分，兩者相乘，得到一個999行3列的矩陣。在這個矩陣中，所有能被FIND函數(shù)查找到的數(shù)據(jù)，其位置上都對應(yīng)地顯示出LEFT函數(shù)的結(jié)果擴大倍數(shù)后的數(shù)值，其余都是錯誤值。

⑥利用IFERROR函數(shù)將錯誤值轉(zhuǎn)為“0”

⑦最后乘上{50,1,0.1}，再套用SUM函數(shù)就可以求出最后的結(jié)果了。因為在LEFT函數(shù)的步驟中，是以10做為底數(shù)的，等于為最終的結(jié)果都同時擴大了10倍；所以在這個步驟中要縮小10倍，得出結(jié)果是{50,1,0.1}而不是{500,10,1}，其分別對應(yīng){"箱","條","包"}。

補充：

這個公式還可以改寫成：

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

有興趣的小伙伴可以試試分析一下其中的函數(shù)，剖析一下它們分別起了什么重用！

方法3

最后再向大家介紹一個公式。這個公式是有缺陷的，但是它的邏輯思路非常值得我們學習和借鑒，因此也分享給大家。

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

大家可以看到，這個公式的結(jié)果幾乎都是正確的，但在計算613箱是出了錯！

現(xiàn)在，一起來分析一下這個公式的思路。

函數(shù)解析：

①FIND({"箱";"包";"條"},A2))部分。這部分的理解比較簡單，即在單元格A2中分別查找{"箱";"包";"條"}，并返回具體的位置信息。

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

③RIGHTB函數(shù)部分，從右提取4個字節(jié)。注意，是字節(jié)，不是字符。由于"10箱2條3包"和"10箱2條"提取不到第4個字節(jié)，因此實際只提取了3個字節(jié)。這部分的結(jié)果是{"10箱";" 3包";" 2條"}，注意“3”和“2”前面是有空格的。

④利用SUBSTITUTE函數(shù)將"箱"、"包"和"條"用空值替換。

剩下的部分就比較簡單了，這里就不再花更多的筆墨介紹了。

從本期的帖子中，大家可以看到，不同的思路，寫出來的公式有長有短，但沒有優(yōu)劣之分，只有邏輯思路不同。多思考，多練習，才是掌握函數(shù)技巧的秘籍！

最后留給小伙伴們一道思考題目：

最后一個公式中，怎樣解決計算613箱時出現(xiàn)的錯誤？

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學習Excel：

相關(guān)推薦：

如何對帶單位的數(shù)字進行求和

小白最愛的自動求和，恰是連專家也坑的老虎

SUM函數(shù)的進階用法：快速對交叉區(qū)域、應(yīng)收款項、小計行求和！

靈活組合或嵌套函數(shù)編寫公式解決問題的技巧2：參數(shù)替換法

版權(quán)申明：

本文作者EXCEL應(yīng)用之家；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。