ArrayFormula()的小计 [英] Subtotals with ArrayFormula()

问题描述

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

• A表示小计线.
• B包含先前小计的行号.这只是简化公式的额外字段.
• C包含一些数量.
• D包含上一小计行与当前小计行之间的金额小计.

小计公式具有以下视图:

  = 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));)) 

问题在于公式太慢了.有没有办法使其更快?

示例文件:

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()的小计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！