產(chǎn)品成分表中各產(chǎn)品的成分和百分含量（百分比）都記錄在同一單元格。如何快速提取這些百分比并求和？本文提供一種不分列的函數(shù)方法。適合從文本中提取任意多個(gè)百分?jǐn)?shù)。

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

今天我們就來說說直接匯總文本中的多個(gè)百分?jǐn)?shù)。

下圖是產(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)算。

好了，今天就和大家分享這么多吧！

如果你堅(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>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ù)值變成了空值！

本文配套的練習(xí)課件請(qǐng)加入QQ群：264539405下載。

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

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(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ì)嗎？

版權(quán)申明：

本文為部落窩教育獨(dú)家授權(quán)作品。作者Excel應(yīng)用之家。如需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。