使用 ArrayFormula() 进行小计

本文介绍了使用 ArrayFormula() 进行小计的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我创建了一个简单的表格并尝试使用小计拆分数据.

A 表示小计行.
B 包含先前小计的行数.这只是用于简化公式的额外字段.
C 包含一些数量.
D 包含前一个和当前小计行之间金额的小计.

小计公式有如下视图:

=ArrayFormula(如果($A2:$A；MMULT(($B2:$B

问题是公式非常慢.有没有办法让它更快?

示例文件:

I've created a simple table and trying to split data with subtotals.

A indicates the subtotal lines.
B contains the rows number for previous subtotal. This is just extra field to simplify formulas.
C Contains some amounts.
D Contains subtotals of amounts between previous and current subtotal line.

The subtotal formula has the following view:

=ArrayFormula(
    IF($A2:$A; MMULT(
        ($B2:$B < TRANSPOSE(ROW($A2:$A))) * (TRANSPOSE(ROW($A2:$A)) < ROW($A2:$A));
        IF(ISNUMBER(C2:C); C2:C; 0)
    ); )
)

The problem is that the formula is extrimely slow. Is there a way to make it faster?

Example file:

https://docs.google.com/spreadsheets/d/1HPGeLZfar2s6pIQMVdQ8mIPzNdw2ESqKAwZfo4IicnA/edit?usp=sharing

解决方案

You could also try this much simpler formula:

=ArrayFormula(
   if(B3:B="","",
         sumif(row(B3:B),"<="&row(B3:B),C3:C)-
         sumif(row(B3:B),"<="&B3:B,C3:C)
   )
)

                        这篇关于使用 ArrayFormula() 进行小计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！