excel求和和公式求和的值不一致（文本中多个百分数汇总求和）

我们当前百分比数据数位最长的是90.5%，有5位。也就是说提取字符数必须是百分数最长字符数1。很简单，因为使用了省略第2参数的TEXT函数。这个函数确定了百分数前面必须有至少一个非数值的字符，否则提取到的数字会被TEXT当做空值处理掉。

excel求和和公式求和的值不一致?编按：同一单元格的文字里包含了多个百分数如何直接求百分数的和而不借助分列？，现在小编就来说说关于excel求和和公式求和的值不一致?下面内容希望能帮助到你，我们来一起看看吧!

excel求和和公式求和的值不一致

编按：同一单元格的文字里包含了多个百分数。如何直接求百分数的和而不借助分列？

直接汇总文本中的数据很困难。在前面我们分享过报销事项和金额记在一起的流水账汇总。当时每条文本中只有一个数字。如果每条文本中有多个百分数又怎么直接相加求和呢？

今天我们就来说说直接汇总文本中的多个百分数。学习更多技巧，请收藏关注部落窝教育excel图文教程。

下图是产品的成分表，我们需要汇总成分总和。

这类汇总，为了效率和准确性，肯定不能口算、笔算、按计算器算。那要怎么算才又快又准呢？

——用函数公式。这道题目的公式如下。

在单元格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），并向下拖曳即可。如下图：

公式很长，很上头？——看解析！函数解析：

? 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个空格来提取单元格数据，你会吗？》

? 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%","涤纶",""}，所有的数值型数据已经变为空值。

? MID(TEXT()):从TEXT返回值的第2位开始提取长度为99的字符串，结果为{"","","纶","";"","5%","纶","";"95%","，5%","纶","";"s95%","棉，5%","纶","";"s95%","造棉，5%","纶",""}。

? 利用减负将文本型数据转换为错误值，在用IFERROR函数将错误值转换为0，其结果为{0,0,0,0;0,0.05,0,0;0.95,0,0,0;0,0,0,0;0,0,0,0}。

? SUM函数求和得到1（100%）。

如果看了函数解析，还有问题的，请直接跳转到文末看疑问解答。

——错误处理。咦？为什么最后一行结果是300%？

原来这行数据有个特殊的地方，字符串的最后有个数字2，而EXCEL在最后计算是将它也加入到计算中了，因此需要对公式进行调整，在单元格尾巴上也添加一个非数值字符“s”。如下图示：

原公式中的"ss"&B2调整为"ss"&B2&"s"后就完美地解决了问题。增加的“s”是在内存数组中将数字2变成了文本字符串“2s”，从而避开了运算。

好了，今天就和大家分享这么多吧！学习更多技巧，请收藏关注部落窝教育excel图文教程。

如果你坚持看到这里，那就再送几个疑难解答给你，有利完整理解公式。

——疑问解答。

1.为何用RIGHT提取字符串时要依次提取2~6个字符？

这与百分比数字字符长度和TEXT函数有关。

我们当前百分比数据数位最长的是90.5%，有5位。按道理用RIGHT函数从右往左依次提取2~5个数字肯定就能把位数最长的“90.5%”提取到；但是后面省略第二参数的TEXE函数会把提取到的90.5%当做空值处理。所以我们必须多提取一位，得到“s90.5%”，这样才能在后续的提取中得到90.5%。

也就是说提取字符数必须是百分数最长字符数 1。又因百分数最小也有2个字符长度，所以是提取2~6个字符。

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

很简单，因为使用了省略第2参数的TEXT函数。这个函数确定了百分数前面必须有至少一个非数值的字符，否则提取到的数字会被TEXT当做空值处理掉。当前数据中， B2、B3、B5单元格中的第一个百分数前缺少非数值字符，所以我们得添加非数值字符。你添加“S”或者“人”“，”等非数值字符都可以。

3.为何要添加两个“s”字符？

前一个问题已经明确了百分数前必须至少有一个非数值的字符。那为何是加两个“s”呢？为了公式能用于整个数据，所以式中是按照百分数最长字符数 1进行多次提取的。譬如B2单元格的95%，字长3位，如果只提取它，只需要RIGHT依次提取2、3、4个字符即可；现在为了“照顾”5位值的百分数，则要依次提取2、3、4、5、6个字符，多提取了两次，因此，就得多一个s，让多提取的部分都是“ss95%”，如此，最后一次用MID从第2位开始提取的时候，多提取部分得到的都是s95%,避免了多返回两个95%，造成计算错误。

结论：只要百分比的位数不等，为了避免多次返回同一数值，必须保证每个百分比前至少有两个非数值字符。

4. 公式中TEXT函数起什么作用？

对比这两组公式我们可以看到，当省略TEXT函数的第二个参数时，TEXT函数只返回文本数据，所有数值型的数据都当做空值处理。上面函数公式就利用了TEXT函数的这个特点，将RIGHT返回值中的所有数值变成了空值！

童鞋们，今天的公式有点长，不过都有解析，可以设计一个案例自己动手试试！学习更多技巧，请收藏关注部落窝教育excel图文教程。

＊＊＊＊部落窝教育-excel文本中多个百分数求和＊＊＊＊

原创：Excel应用之家/部落窝教育（未经同意，请勿转载）

更多教程：部落窝教育

exceljiaocheng， v：blwjymx2

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！