二维码 购物车
部落窝在线教育欢迎您!

用什么方法快速提取产品成分表中的百分比并求和

 

作者:Excel应用之家来源:部落窝教育发布时间:2020-11-12 09:30:48点击:442

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。
编按:

产品成分表中各产品的成分和百分含量(百分比)都记录在同一单元格。如何快速提取这些百分比并求和?本文提供一种不分列的函数方法。适合从文本中提取任意多个百分数。

 

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

 

今天我们就来说说直接汇总文本中的多个百分数。

 

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

 

图一

 

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

 

用函数公式。

 

这道题目的公式如下。

在单元格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的空格;然后依次从第1100199298处各提取长度为99的字符串,最后去掉提取的字符串中的空格,结果是{"ss95%","人造棉,5%","涤纶",""}。这段如果看不懂,可以看《Excel脑洞大开:用99个空格来提取单元格数据,你会吗?

Ø  RIGHT(TRIM()):TRIM返回值中从右向左依次提取长度分别是23456的字符,得到的结果是{"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函数求和得到1100%)。

 

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

 

错误处理。

咦?为什么最后一行结果是300%

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

 

图五

 

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

 

好了,今天就和大家分享这么多吧!

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

 

疑问解答。

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当做空值处理掉。当前数据中, B2B3B5单元格中的第一个百分数前缺少非数值字符,所以我们得添加非数值字符。你添加“S”或者”“等非数值字符都可以。

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

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

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

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

 

图二

 

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

 

本文配套的练习课件请加入QQ群:264539405下载。

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

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

流水账中文本数据求和:账目中文字和数据记录在一起怎么求和?

从订单中提取手机号码:如何从多人拼单的订单中提取各顾客的手机号?

从混合文本中提取数字:Excel数字提取技巧:用简单公式从混合文本中提取数字的3种情景

文本数据提取的经典案例:Excel脑洞大开:用99个空格来提取单元格数据,你会吗?

版权申明:

本文为部落窝教育独家授权作品。作者Excel应用之家。如需转载请联系部落窝教育。