分列显示在各个单元格的数据求和如何计算（类似票据中的金额汇总）？

作者：绿色风分类：时间：2022-08-17 浏览：129

楼主 Zaezhong	Q: 分列显示在各个单元格的数据求和如何计算（类似票据中的金额汇总）？ A: =LEFT(RIGHT(TEXT(SUM(TEXT($B2:$J8,"0;;0;\0")10^(7-COLUMN($A:$I)))/1%," ￥000;;"),COLUMNS(B:$J))) 说明： 1、附件的数字格式为数字； 2、公式先通过将表格的空白和文本字符转换为0值，得到 {"0","0","0","0","0","0","1","0","0";"0","0","0","0","0","1","2","0","0";"0","0","0","0","1","2","3","0","0";"0","0","1","2","3","4","5","0","0";"0","0","0","0","0","1","2","3","4";"0","0","0","0","1","2","3","4","5";"0","0","0","1","2","3","4","5","6"} 后再与 {1000000,100000,10000,1000,100,10,1,0.1,0.01} 相乘得到各行的数据值 {0,0,0,0,0,0,1,0,0;0,0,0,0,0,10,2,0,0;0,0,0,0,100,20,3,0,0;0,0,10000,2000,300,40,5,0,0;0,0,0,0,0,10,2,0.3,0.04;0,0,0,0,100,20,3,0.4,0.05;0,0,0,1000,200,30,4,0.5,0.06} （以分号为界），最后通过SUM求和得出总数显示相应的格式后在外部通过RIGHT取得SUM结果的字符串，随着公式的右拉，取得的字符个数相应的将变少 =LEFT(RIGHT(TEXT(SUM((0&SUBSTITUTE(B2:J8,"￥",))10^(9-COLUMN(A:I)))," ￥000"),10-COLUMN(A:I))) =LEFT(RIGHT(" ￥"&SUM(--SUBSTITUTE(PHONETIC(OFFSET($C1:$J1,ROW(1:7),)),"￥","")),COLUMNS(B:$J))) 其他的一些解法可以参考 http://www.exceltip.net/thread-13182-1-1.html 2010-11-27_085226.gif 分列金额求和.rar
2楼 wjc2090742	10^(7-COLUMN($A:$I)))/1% 这部分/1%可以合并进去，写成10^(9-COLUMN($A:$I)))
3楼 Zaezhong	有一点不是很明白，将求和的结果按格式显示的时候用了3个0，但是在这里如果一个0最后的结果也是一样的？
4楼 wjc2090742	看要哪种效果了。试下下面这种情况就会出现区别了。
5楼 Zaezhong	测试了下，明白了，原来圆角分的问题，3个0的时候是0元0角6分，一个0的时候是6分
6楼 lrlxxqxa	思路解析很详细
7楼 qinhuan66	值得好好学习

免责声明

有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素，经与ExcelTip.Net站长Apolloh商议并征得其同意，现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示，供有需要的人士查询使用，也慰缅曾经的论坛时代。所示各个帖子的原作者如对版权有异议，可与本人沟通提出，或于本站点留言，我们会尽快处理。在此，感谢ExcelTip.Net站长Apolloh的支持，感谢本站点所有人**绿色风（QQ:79664738)**的支持与奉献，特此鸣谢！
------本人网名**KevinChengCW（QQ:1210618015)**原ExcelTip.Net总版主之一

评论列表