今天我們來看一個求和的問題，數(shù)據(jù)源和模擬結果如下：
這個小伙伴觀察數(shù)據(jù)，發(fā)現(xiàn)消費券都是在最后的，故把數(shù)據(jù)源中的消費券提取出來了。他想對消費券的金額求和，模擬結果如C列所示。
這個小伙伴用的是WPS，故我們可以考慮直接使用EVALUATE。
EVALUATE：對以文字表示的一個公式或表達式求值，并返回結果。
語法：EVALUATE(值)
如果在office里敲=EVALUATE，不會出現(xiàn)，需要定義名稱。
定義名稱方法：公式——定義名稱，在引用位置寫上公式，名稱框寫入名字即可。
用EVALUATE對消費券的金額求和，那么肯定需要類似于300+30的算式。觀察以后發(fā)現(xiàn)每個數(shù)字的前面都有消費券:，故我們用SUBSTITUTE(B2,'消費券:','')將消費券:替換掉，然后這個單元格里面就會有逗號分隔的數(shù)字，我們可以再用一次SUBSTITUTE把逗號替換為+，這樣我們用EVALUATE就可以求和了。因為原本的B2有錯誤，我們可以用iferror排錯。公式如下：
=IFERROR(EVALUATE(SUBSTITUTE(SUBSTITUTE(B2,'消費券:',''),',','+')),0)
<左右滑動查看更多>
效果如下圖：
當然，我們可以把B列的公式套進上面，這樣就不需要輔助列了。
=IFERROR(EVALUATE(SUBSTITUTE(SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND('消費券:',A2,1)+1),'消費券:',''),',','+')),0)
<左右滑動查看更多>
效果如下：
觀察要求和的數(shù)據(jù)，我們發(fā)現(xiàn)都是按照,消費券:來分開的，故我們可以考慮用函數(shù)分列的套路。分列的套路在另一篇文章中會提到，這里簡單提一下。
此函數(shù)的意思是在分隔符號加上單元格字符長度的單元格，然后再根據(jù)列號逐個取出字符，然后再去掉空格。
舉個例子，此處C12有8個字符，故我們把空格重復8次。
從第1位開始取8位，這樣就會取出67 （67后面跟6個空格），然后把空格去了就是第一個數(shù)67了。
從第9位開始取8位，這樣就會取出 45 （45后面跟4個空格），然后把空格去了就是第二個數(shù)45了。
從第17位開始取8位，這樣就會取出 45 （45后面跟4個空格），然后把空格去了就是第二個數(shù)45了。
介紹完分列的套路，我們可以用分列的核心（即MID(SUBSTITUTE($C2,'*',REPT(“ “,LEN($C2))),(COLUMN(A1)-1)*LEN($C2)+1,LEN($C2))）來解決這個問題。
為了方便，我們把A列中的,消費券:替換為99個空格。由于我們不知道需要分多少次，大概看一下發(fā)現(xiàn)沒有超過10組的，故我們可以構建一個1到9的數(shù)組（即最多根據(jù),消費券:分9次）和99相乘。分了之后因為MID取出來的是文本，故變?yōu)閿?shù)值之后相加即可
故完整公式如下：
=SUMPRODUCT(IFERROR(--MID(SUBSTITUTE(A2,',消費券:',REPT(“ “,99)),ROW($1:$9)*99-98,99),0))
<左右滑動查看更多>
效果如下圖：
在解法2的基礎上，我們發(fā)現(xiàn)text函數(shù)可以屏蔽錯誤，故考慮下面的公式。
=SUMPRODUCT(--TEXT(MID(SUBSTITUTE(A2,',消費券:',REPT(CHAR(32),99)),ROW($1:$9)*99-98,99),'[>];;0;!0'))
<左右滑動查看更多>
（提示：[>];;0;!0可以屏蔽錯誤，在此不解釋。Text取出的是個文本，故需要加--變?yōu)閿?shù)值。CHAR(32)就是空格。）
0.31
效果如下圖：
注：上述解法均適用于消費券都在后面的情況，否則會如下圖所示計算錯誤：
這個數(shù)據(jù)源沒有這種情況。這種情況解法1不成立，解法2和解法3我們需要把消費券:和,分開替換，即
=SUMPRODUCT(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(A2,'消費券:',REPT(' ',99)),',',REPT(' ',99)),ROW($1:$9)*99-98,99),0))
<左右滑動查看更多>
=SUMPRODUCT(--TEXT(MID(SUBSTITUTE(SUBSTITUTE(A2,'消費券:',REPT(' ',99)),',',REPT(' ',99)),ROW($1:$9)*99-98,99),'[>];;0;!0'))
<左右滑動查看更多>
如果你使用Excel 2016、2019和365版，恭喜你，你能在數(shù)據(jù)-新建查詢里找到power query；如果你使用2010和2013版，那么你需要下載power query后加載到Excel里；如果你使用2003和2007版，那么請你升級Excel吧；如果你使用WPS任意版本，那么請換Excel來使用power query吧。
以下為oldman大佬的PQ解法分享~
let 源 = Excel.CurrentWorkbook(){[Name='表1']}[Content], Process = Table.AddColumn(源,'n',(x)=> [a = List.Select(Text.Split(x[收款方式],','),each Text.Contains(_,'消費券')), b = List.Transform(a,each Number.From(Text.Remove(_,Text.ToList('消費券:')))), c = List.Sum(b) ][c] ), RemovCol = Table.RemoveColumns(Process,{'收款方式'})in RemovCol<左右滑動查看更多>
代碼使用方法：
Step 01: 單擊【數(shù)據(jù)】選項卡 - 選擇【自表格/區(qū)域】命令；
好啦今兒分享到這里，祝大家好運！
喜歡的同鞋請轉發(fā)、點贊！
掃描下方二維碼，加入微信交流群~
