使用 ArrayFormula() 进行小计 [英] Subtotals with 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() 进行小计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
