总总结然后小计？

Question

同事在"X4:X38“中有一系列值。由于这些值都在可能被过滤的表中，所以她希望使用小计函数对它们进行求和--但是希望首先对所有的值进行舍入。

={SUM(ROUNDUP(X4:X38,0))}

效果很好。然而，

{SUBTOTAL(9,ROUNDUP(X4:X38,0))}

生成泛型“您键入的公式包含错误”消息。我尝试过各种显而易见的事情，比如在“综述”部分加上额外的括号，等等。

任何帮助都将不胜感激。

Answer 1

通过使用以下公式，可以在不使用助手列的情况下执行此操作：

=SUMPRODUCT(SUBTOTAL(2,OFFSET(X4:X38,ROW(X4:X38)-MIN(ROW(X4:X38)),0,1)),ROUNDUP(X4:X38,0))

OFFSET有效地将范围分解到传递给SUBTOTAL函数的单个单元格中，该单元格根据每个单元格在筛选后是否可见而返回1或0值的数组--该数组乘以四舍五入的值，得到圆形可见值的总体和。

另一种方法是像这样使用AGGREGATE函数

=SUMPRODUCT(ROUNDUP(AGGREGATE(15,7,X4:X38,ROW(INDIRECT("1:"&SUBTOTAL(2,X4:X38)))),0))

考虑到复杂性，辅助列可能是更好的方法。

Answer 2

经过调查，看起来没有助手列是不可能的。

添加一个助手列，该列舍入列X中的单个值，例如，在单元格Y4中键入以下公式，然后向下拖到Y38

= ROUNDUP(X4,0)

然后代替了

= SUBTOTAL(9,ROUNDUP(X4:X38,0))

用途：

= SUBTOTAL(9,Y4:Y38)

如果有必要，只需隐藏helper列即可。当然，helper列不必是列Y，它可以是任何列，例如，数据结束位置右侧的一个列。