編按：同一單元格的文字里包含了多個(gè)百分?jǐn)?shù)。如何直接求百分?jǐn)?shù)的和而不借助分列？
直接匯總文本中的數(shù)據(jù)很困難。在前面我們分享過報(bào)銷事項(xiàng)和金額記在一起的流水賬匯總。當(dāng)時(shí)每條文本中只有一個(gè)數(shù)字。如果每條文本中有多個(gè)百分?jǐn)?shù)又怎么直接相加求和呢？
今天我們就來說說直接匯總文本中的多個(gè)百分?jǐn)?shù)。學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。
下圖是產(chǎn)品的成分表，我們需要匯總成分總和。
這類匯總，為了效率和準(zhǔn)確性，肯定不能口算、筆算、按計(jì)算器算。那要怎么算才又快又準(zhǔn)呢？
——用函數(shù)公式。這道題目的公式如下。
在單元格C2中輸入公式
=SUM(IFERROR(--MID(TEXT(RIGHT(TRIM(MID(SUBSTITUTE("ss"&B2,"%","%"&REPT("",99)),99*COLUMN(A:D)-98,99)),ROW($2:$6)),),2,99),0))
輸完后，按三鍵（CTRL+SHIFT+ENTER），并向下拖曳即可。如下圖：
公式很長(zhǎng)，很上頭？——看解析！函數(shù)解析：
? TRIM(MID(SUBSTITUTE("ss"&B2,"%","%"&REPT("",99)),99*COLUMN(A:D)-98,99))：在B2單元格加上兩個(gè)ss，并在每個(gè)百分號(hào)%后面插入長(zhǎng)度為99的空格；然后依次從第1、100、199、298處各提取長(zhǎng)度為99的字符串，最后去掉提取的字符串中的空格，結(jié)果是{"ss95%","人造棉，5%","滌綸",""}。這段如果看不懂，可以看《Excel腦洞大開：用99個(gè)空格來提取單元格數(shù)據(jù)，你會(huì)嗎？》
? RIGHT(TRIM()):在TRIM返回值中從右向左依次提取長(zhǎng)度分別是2、3、4、5、6的字符，得到的結(jié)果是{"5%","5%","滌綸","";"95%","，5%","滌綸","";"s95%","棉，5%","滌綸","";"ss95%","造棉，5%","滌綸","";"ss95%","人造棉，5%","滌綸",""}。
? TEXT(RIGHT())：將上述的結(jié)果轉(zhuǎn)換為{"","","滌綸","";"","，5%","滌綸","";"s95%","棉，5%","滌綸","";"ss95%","造棉，5%","滌綸","";"ss95%","人造棉，5%","滌綸",""}，所有的數(shù)值型數(shù)據(jù)已經(jīng)變?yōu)榭罩怠?/span>
? MID(TEXT()):從TEXT返回值的第2位開始提取長(zhǎng)度為99的字符串，結(jié)果為{"","","綸","";"","5%","綸","";"95%","，5%","綸","";"s95%","棉，5%","綸","";"s95%","造棉，5%","綸",""}。
? 利用減負(fù)將文本型數(shù)據(jù)轉(zhuǎn)換為錯(cuò)誤值，在用IFERROR函數(shù)將錯(cuò)誤值轉(zhuǎn)換為0，其結(jié)果為{0,0,0,0;0,0.05,0,0;0.95,0,0,0;0,0,0,0;0,0,0,0}。
? SUM函數(shù)求和得到1（100%）。
如果看了函數(shù)解析，還有問題的，請(qǐng)直接跳轉(zhuǎn)到文末看疑問解答。
——錯(cuò)誤處理。咦？為什么最后一行結(jié)果是300%？
原來這行數(shù)據(jù)有個(gè)特殊的地方，字符串的最后有個(gè)數(shù)字2，而EXCEL在最后計(jì)算是將它也加入到計(jì)算中了，因此需要對(duì)公式進(jìn)行調(diào)整，在單元格尾巴上也添加一個(gè)非數(shù)值字符“s”。如下圖示：
原公式中的"ss"&B2調(diào)整為"ss"&B2&"s"后就完美地解決了問題。增加的“s”是在內(nèi)存數(shù)組中將數(shù)字2變成了文本字符串“2s”，從而避開了運(yùn)算。
好了，今天就和大家分享這么多吧！學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。
如果你堅(jiān)持看到這里，那就再送幾個(gè)疑難解答給你，有利完整理解公式。
——疑問解答。
1.為何用RIGHT提取字符串時(shí)要依次提取2~6個(gè)字符？
這與百分比數(shù)字字符長(zhǎng)度和TEXT函數(shù)有關(guān)。
我們當(dāng)前百分比數(shù)據(jù)數(shù)位最長(zhǎng)的是90.5%，有5位。按道理用RIGHT函數(shù)從右往左依次提取2~5個(gè)數(shù)字肯定就能把位數(shù)最長(zhǎng)的“90.5%”提取到；但是后面省略第二參數(shù)的TEXE函數(shù)會(huì)把提取到的90.5%當(dāng)做空值處理。所以我們必須多提取一位，得到“s90.5%”，這樣才能在后續(xù)的提取中得到90.5%。
也就是說提取字符數(shù)必須是百分?jǐn)?shù)最長(zhǎng)字符數(shù)+1。又因百分?jǐn)?shù)最小也有2個(gè)字符長(zhǎng)度，所以是提取2~6個(gè)字符。
2.為何要添加“s”字符？
很簡(jiǎn)單，因?yàn)槭褂昧耸÷缘?span style="margin-top: 0px;margin-bottom: 0px;padding: 0px;">2參數(shù)的TEXT函數(shù)。這個(gè)函數(shù)確定了百分?jǐn)?shù)前面必須有至少一個(gè)非數(shù)值的字符，否則提取到的數(shù)字會(huì)被TEXT當(dāng)做空值處理掉。當(dāng)前數(shù)據(jù)中， B2、B3、B5單元格中的第一個(gè)百分?jǐn)?shù)前缺少非數(shù)值字符，所以我們得添加非數(shù)值字符。你添加“S”或者“人”“，”等非數(shù)值字符都可以。
3.為何要添加兩個(gè)“s”字符？
前一個(gè)問題已經(jīng)明確了百分?jǐn)?shù)前必須至少有一個(gè)非數(shù)值的字符。那為何是加兩個(gè)“s”呢？為了公式能用于整個(gè)數(shù)據(jù)，所以式中是按照百分?jǐn)?shù)最長(zhǎng)字符數(shù)+1進(jìn)行多次提取的。譬如B2單元格的95%，字長(zhǎng)3位，如果只提取它，只需要RIGHT依次提取2、3、4個(gè)字符即可；現(xiàn)在為了“照顧”5位值的百分?jǐn)?shù)，則要依次提取2、3、4、5、6個(gè)字符，多提取了兩次，因此，就得多一個(gè)s，讓多提取的部分都是“ss95%”，如此，最后一次用MID從第2位開始提取的時(shí)候，多提取部分得到的都是s95%,避免了多返回兩個(gè)95%，造成計(jì)算錯(cuò)誤。
結(jié)論：只要百分比的位數(shù)不等，為了避免多次返回同一數(shù)值，必須保證每個(gè)百分比前至少有兩個(gè)非數(shù)值字符。
4. 公式中TEXT函數(shù)起什么作用？
對(duì)比這兩組公式我們可以看到，當(dāng)省略TEXT函數(shù)的第二個(gè)參數(shù)時(shí)，TEXT函數(shù)只返回文本數(shù)據(jù)，所有數(shù)值型的數(shù)據(jù)都當(dāng)做空值處理。上面函數(shù)公式就利用了TEXT函數(shù)的這個(gè)特點(diǎn)，將RIGHT返回值中的所有數(shù)值變成了空值！
童鞋們，今天的公式有點(diǎn)長(zhǎng)，不過都有解析，可以設(shè)計(jì)一個(gè)案例自己動(dòng)手試試！學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。
＊＊＊＊部落窩教育-excel文本中多個(gè)百分?jǐn)?shù)求和＊＊＊＊
原創(chuàng)：Excel應(yīng)用之家/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）
更多教程：部落窩教育
相關(guān)推薦：
流水賬中文本數(shù)據(jù)求和：賬目中文字和數(shù)據(jù)記錄在一起怎么求和？
從訂單中提取手機(jī)號(hào)碼：如何從多人拼單的訂單中提取各顧客的手機(jī)號(hào)？
從混合文本中提取數(shù)字：Excel數(shù)字提取技巧:用簡(jiǎn)單公式從混合文本中提取數(shù)字的3種情景
文本數(shù)據(jù)提取的經(jīng)典案例：Excel腦洞大開：用99個(gè)空格來提取單元格數(shù)據(jù)，你會(huì)嗎？
聯(lián)系客服
