Word技巧達(dá)人推薦搜索

Word課程

Word表格

段落

郵件合并

樣式

圖表

今天我們來(lái)看一個(gè)求和的問(wèn)題,，數(shù)據(jù)源和模擬結(jié)果如下：

這個(gè)小伙伴觀察數(shù)據(jù)，發(fā)現(xiàn)消費(fèi)券都是在最后的,，故把數(shù)據(jù)源中的消費(fèi)券提取出來(lái)了,。他想對(duì)消費(fèi)券的金額求和，模擬結(jié)果如C列所示,。

這個(gè)小伙伴用的是WPS,，故我們可以考慮直接使用EVALUATE。

EVALUATE：對(duì)以文字表示的一個(gè)公式或表達(dá)式求值,，并返回結(jié)果,。

語(yǔ)法：EVALUATE(值)

如果在office里敲=EVALUATE，不會(huì)出現(xiàn),，需要定義名稱,。

定義名稱方法：公式——定義名稱，在引用位置寫上公式,，名稱框?qū)懭朊旨纯伞?/span>

用EVALUATE對(duì)消費(fèi)券的金額求和,，那么肯定需要類似于300+30的算式。觀察以后發(fā)現(xiàn)每個(gè)數(shù)字的前面都有消費(fèi)券:,，故我們用SUBSTITUTE(B2,'消費(fèi)券:','')將消費(fèi)券:替換掉,，然后這個(gè)單元格里面就會(huì)有逗號(hào)分隔的數(shù)字，我們可以再用一次SUBSTITUTE把逗號(hào)替換為+,，這樣我們用EVALUATE就可以求和了,。因?yàn)樵镜腂2有錯(cuò)誤，我們可以用iferror排錯(cuò),。公式如下：

<左右滑動(dòng)查看更多>

效果如下圖：

當(dāng)然,，我們可以把B列的公式套進(jìn)上面，這樣就不需要輔助列了,。

=IFERROR(EVALUATE(SUBSTITUTE(SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND('消費(fèi)券:',A2,1)+1),'消費(fèi)券:',''),',','+')),0)

<左右滑動(dòng)查看更多>

效果如下：

觀察要求和的數(shù)據(jù),，我們發(fā)現(xiàn)都是按照,消費(fèi)券:來(lái)分開(kāi)的，故我們可以考慮用函數(shù)分列的套路,。分列的套路在另一篇文章中會(huì)提到,，這里簡(jiǎn)單提一下。

此函數(shù)的意思是在分隔符號(hào)加上單元格字符長(zhǎng)度的單元格,，然后再根據(jù)列號(hào)逐個(gè)取出字符,，然后再去掉空格,。

舉個(gè)例子，此處C12有8個(gè)字符,，故我們把空格重復(fù)8次,。

從第1位開(kāi)始取8位，這樣就會(huì)取出67        （67后面跟6個(gè)空格）,，然后把空格去了就是第一個(gè)數(shù)67了,。

從第9位開(kāi)始取8位，這樣就會(huì)取出  45     （45后面跟4個(gè)空格）,，然后把空格去了就是第二個(gè)數(shù)45了,。

從第17位開(kāi)始取8位，這樣就會(huì)取出  45     （45后面跟4個(gè)空格）,，然后把空格去了就是第二個(gè)數(shù)45了,。

介紹完分列的套路，我們可以用分列的核心（即MID(SUBSTITUTE($C2,'*',REPT(“ “,LEN($C2))),(COLUMN(A1)-1)*LEN($C2)+1,LEN($C2))）來(lái)解決這個(gè)問(wèn)題,。

為了方便,，我們把A列中的,消費(fèi)券:替換為99個(gè)空格。由于我們不知道需要分多少次,，大概看一下發(fā)現(xiàn)沒(méi)有超過(guò)10組的,，故我們可以構(gòu)建一個(gè)1到9的數(shù)組（即最多根據(jù),消費(fèi)券:分9次）和99相乘。分了之后因?yàn)?/span>MID取出來(lái)的是文本,，故變?yōu)閿?shù)值之后相加即可

故完整公式如下：

<左右滑動(dòng)查看更多>

效果如下圖：

在解法2的基礎(chǔ)上,，我們發(fā)現(xiàn)text函數(shù)可以屏蔽錯(cuò)誤，故考慮下面的公式,。

=SUMPRODUCT(--TEXT(MID(SUBSTITUTE(A2,',消費(fèi)券:',REPT(CHAR(32),99)),ROW($1:$9)*99-98,99),'[>];;0;!0'))

<左右滑動(dòng)查看更多>

（提示：[>];;0;!0可以屏蔽錯(cuò)誤，在此不解釋,。Text取出的是個(gè)文本,，故需要加--變?yōu)閿?shù)值。CHAR(32)就是空格,。）

0.31

效果如下圖：

注：上述解法均適用于消費(fèi)券都在后面的情況,，否則會(huì)如下圖所示計(jì)算錯(cuò)誤：

這個(gè)數(shù)據(jù)源沒(méi)有這種情況。這種情況解法1不成立,，解法2和解法3我們需要把消費(fèi)券:和,分開(kāi)替換,，即

<左右滑動(dòng)查看更多>

=SUMPRODUCT(--TEXT(MID(SUBSTITUTE(SUBSTITUTE(A2,'消費(fèi)券:',REPT(' ',99)),',',REPT(' ',99)),ROW($1:$9)*99-98,99),'[>];;0;!0'))

<左右滑動(dòng)查看更多>

如果你使用Excel 2016、2019和365版,，恭喜你,，你能在數(shù)據(jù)-新建查詢里找到power query；如果你使用2010和2013版,，那么你需要下載power query后加載到Excel里,；如果你使用2003和2007版,，那么請(qǐng)你升級(jí)Excel吧；如果你使用WPS任意版本,，那么請(qǐng)換Excel來(lái)使用power query吧,。

以下為oldman大佬的PQ解法分享~

<左右滑動(dòng)查看更多>

代碼使用方法：

Step 01: 單擊【數(shù)據(jù)】選項(xiàng)卡 - 選擇【自表格/區(qū)域】命令；

好啦今兒分享到這里,，祝大家好運(yùn)！

喜歡的同鞋請(qǐng)轉(zhuǎn)發(fā),、點(diǎn)贊,！

掃描下方二維碼，加入微信交流群~