同一單元格的文字里包含了多個百分數(shù)。如何直接求百分數(shù)的和而不借助分列？

直接匯總文本中的數(shù)據(jù)很困難。在前面我們分享過報銷事項和金額記在一起的流水賬匯總。當時每條文本中只有一個數(shù)字。如果每條文本中有多個百分數(shù)又怎么直接相加求和呢？

今天我們就來說說直接匯總文本中的多個百分數(shù)。學習更多技巧，請收藏關注部落窩教育excel圖文教程。

下圖是產(chǎ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），并向下拖曳即可。如下圖：

公式很長，很上頭？——看解析！函數(shù)解析：

???TRIM(MID(SUBSTITUTE("ss"&B2,"%","%"&REPT("",99)),99*COLUMN(A:D)-98,99))：在B2單元格加上兩個ss，并在每個百分號%后面插入長度為99的空格；然后依次從第1、100、199、298處各提取長度為99的字符串，最后去掉提取的字符串中的空格，結果是{"ss95%","人造棉，5%","滌綸",""}。這段如果看不懂，可以看《Excel腦洞大開：用99個空格來提取單元格數(shù)據(jù)，你會嗎？》

???RIGHT(TRIM()):在TRIM返回值中從右向左依次提取長度分別是2、3、4、5、6的字符，得到的結果是{"5%","5%","滌綸","";"95%","，5%","滌綸","";"s95%","棉，5%","滌綸","";"ss95%","造棉，5%","滌綸","";"ss95%","人造棉，5%","滌綸",""}。

???TEXT(RIGHT())：將上述的結果轉換為{"","","滌綸","";"","，5%","滌綸","";"s95%","棉，5%","滌綸","";"ss95%","造棉，5%","滌綸","";"ss95%","人造棉，5%","滌綸",""}，所有的數(shù)值型數(shù)據(jù)已經(jīng)變?yōu)榭罩怠?/p>

???MID(TEXT()):從TEXT返回值的第2位開始提取長度為99的字符串，結果為{"","","綸","";"","5%","綸","";"95%","，5%","綸","";"s95%","棉，5%","綸","";"s95%","造棉，5%","綸",""}。

???利用減負將文本型數(shù)據(jù)轉換為錯誤值，在用IFERROR函數(shù)將錯誤值轉換為0，其結果為{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ù)解析，還有問題的，請直接跳轉到文末看疑問解答。

——錯誤處理。咦？為什么最后一行結果是300%？

原來這行數(shù)據(jù)有個特殊的地方，字符串的最后有個數(shù)字2，而EXCEL在最后計算是將它也加入到計算中了，因此需要對公式進行調整，在單元格尾巴上也添加一個非數(shù)值字符“s”。如下圖示：

原公式中的"ss"&B2調整為"ss"&B2&"s"后就完美地解決了問題。增加的“s”是在內存數(shù)組中將數(shù)字2變成了文本字符串“2s”，從而避開了運算。

好了，今天就和大家分享這么多吧！學習更多技巧，請收藏關注部落窩教育excel圖文教程。

如果你堅持看到這里，那就再送幾個疑難解答給你，有利完整理解公式。

——疑問解答。

1.為何用RIGHT提取字符串時要依次提取2~6個字符？

這與百分比數(shù)字字符長度和TEXT函數(shù)有關。

我們當前百分比數(shù)據(jù)數(shù)位最長的是90.5%，有5位。按道理用RIGHT函數(shù)從右往左依次提取2~5個數(shù)字肯定就能把位數(shù)最長的“90.5%”提取到；但是后面省略第二參數(shù)的TEXE函數(shù)會把提取到的90.5%當做空值處理。所以我們必須多提取一位，得到“s90.5%”，這樣才能在后續(xù)的提取中得到90.5%。

也就是說提取字符數(shù)必須是百分數(shù)最長字符數(shù)+1。又因百分數(shù)最小也有2個字符長度，所以是提取2~6個字符。

2.為何要添加“s”字符？

很簡單，因為使用了省略第2參數(shù)的TEXT函數(shù)。這個函數(shù)確定了百分數(shù)前面必須有至少一個非數(shù)值的字符，否則提取到的數(shù)字會被TEXT當做空值處理掉。當前數(shù)據(jù)中，?B2、B3、B5單元格中的第一個百分數(shù)前缺少非數(shù)值字符，所以我們得添加非數(shù)值字符。你添加“S”或者“人”“，”等非數(shù)值字符都可以。

3.為何要添加兩個“s”字符？

前一個問題已經(jīng)明確了百分數(shù)前必須至少有一個非數(shù)值的字符。那為何是加兩個“s”呢？為了公式能用于整個數(shù)據(jù)，所以式中是按照百分數(shù)最長字符數(shù)+1進行多次提取的。譬如B2單元格的95%，字長3位，如果只提取它，只需要RIGHT依次提取2、3、4個字符即可；現(xiàn)在為了“照顧”5位值的百分數(shù)，則要依次提取2、3、4、5、6個字符，多提取了兩次，因此，就得多一個s，讓多提取的部分都是“ss95%”，如此，最后一次用MID從第2位開始提取的時候，多提取部分得到的都是s95%,避免了多返回兩個95%，造成計算錯誤。

結論：只要百分比的位數(shù)不等，為了避免多次返回同一數(shù)值，必須保證每個百分比前至少有兩個非數(shù)值字符。

4.?公式中TEXT函數(shù)起什么作用？

對比這兩組公式我們可以看到，當省略TEXT函數(shù)的第二個參數(shù)時，TEXT函數(shù)只返回文本數(shù)據(jù)，所有數(shù)值型的數(shù)據(jù)都當做空值處理。上面函數(shù)公式就利用了TEXT函數(shù)的這個特點，將RIGHT返回值中的所有數(shù)值變成了空值！

童鞋們，今天的公式有點長，不過都有解析，可以設計一個案例自己動手試試！學習更多技巧，請收藏關注部落窩教育excel圖文教程。

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

＊＊＊＊部落窩教育-excel文本中多個百分數(shù)求和＊＊＊＊

原創(chuàng)：Excel應用之家/部落窩教育（未經(jīng)同意，請勿轉載）

更多教程：部落窩教育

微信公眾號：exceljiaocheng，+v：blwjymx2

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

掃下方二維碼關注公眾號，可隨時隨地學習

相關推薦：

流水賬中文本數(shù)據(jù)求和：賬目中文字和數(shù)據(jù)記錄在一起怎么求和？

從訂單中提取手機號碼：如何從多人拼單的訂單中提取各顧客的手機號？

從混合文本中提取數(shù)字：Excel數(shù)字提取技巧:用簡單公式從混合文本中提取數(shù)字的3種情景

文本數(shù)據(jù)提取的經(jīng)典案例：Excel腦洞大開：用99個空格來提取單元格數(shù)據(jù)，你會嗎？