编按:同一单元格的文字里包含了多个百分数 。如何直接求百分数的和而不借助分列?
直接汇总文本中的数据很困难 。在前面我们分享过报销事项和金额记在一起的流水账汇总 。当时每条文本中只有一个数字 。如果每条文本中有多个百分数又怎么直接相加求和呢?
今天我们就来说说直接汇总文本中的多个百分数 。学习更多技巧,请收藏关注部落窝教育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” 。如下图示:
推荐阅读
- Excel如何批量查找指定内容并导出 excel表格怎么查找指定内容
- Excel分页打印两个表格妙招 excel分页打印
- Excel表格中有大量空白行 excel怎么删除空白页
- 最简单的方法你知道吗 cad表格导出到excel快捷键
- 如何快速通过excel表格制作工资条 excel员工工资表格制作
- EXCEL多种插入技巧都在这里啦 excel表格怎么增加多行
- 如何利用Text函数将小数转化为百分数 excel百分比函数公式
- excel表格一个单元格大小如何调整 excel单元格大小设置
- 最简单的工具都在这 excel表格如何转换成word文档
- Excel中多个工作表汇总求和 excel怎么求各个合计的总和