具有多列的 Arrayformula 运行总计 [英] Arrayformula Running Total with multiple columns
问题描述
我有以下任务要解决，但我无法解决它.
不同年份有几个数字.我想要 J 列中所有列(= 范围 C:I)的运行总和.我可以在 J 的每个单元格中使用公式来做到这一点 - 但是我需要在 J2 中使用单个数组公式来实现它.
经过大量研究，我认为有两个步骤:
- 用0替换空单元格，因为arrayformulas显然有一些空单元格的问题
- 对每一行求和
对于第 1 步，公式为:=ARRAYFORMULA(IF(ISBLANK(C2:I15),0,C2:I15)) 这给了我一个临时数组，如:
因为这是一个 15x7 的数组，所以我需要一个类似 7x1 的数组来乘以得到一个 15x1 的数组.
那个公式是:=ARRAYFORMULA(TRANSPOSE(COLUMN(C1:I1)^0))
所以最后我的公式到目前为止看起来像: =ARRAYFORMULA(MMULT(IF(ISBLANK(C2:I15),0,C2:I15),TRANSPOSE(COLUMN(C1:I1)^0)))
这会产生一个 15x1 的数组，它给出了每行的总和，但不是所有行的总和.
这就是我被困的地方 - 非常感谢任何帮助和想法.
添加了一个共享版本供您摆弄:
I've got the following task to solve, but I can't wrap my head around it.
There are a couple of numbers for different years. I would like to have a running total sum of all columns (= range C:I) in column J. I can do that with a formula in every cell of J - however I need to achieve it with a single arrayformula in J2.
What i figured after a lot of research are 2 steps:
- replace empty cells with 0s, since arrayformulas obviously have some problems with empty cells
- make a sum of each row
For step 1 the formula is: =ARRAYFORMULA(IF(ISBLANK(C2:I15),0,C2:I15)) which gives me back a temporary array like:
As this is a 15x7 array, I need something like a 7x1 array to multiply with in order to get a 15x1 array.
That formula would be: =ARRAYFORMULA(TRANSPOSE(COLUMN(C1:I1)^0))
So in the end my formula so far look like: =ARRAYFORMULA(MMULT(IF(ISBLANK(C2:I15),0,C2:I15),TRANSPOSE(COLUMN(C1:I1)^0)))
And this results in a 15x1 array that gives me the sum per row, but not a total running sum of all rows.
This is where I am stuck - any help and ideas are greatly appreciated.
EDIT: added a shared version for you to fiddle: https://docs.google.com/spreadsheets/d/1cqNEsWHqBaHdDrMY8x4DUKpEkYprRZ8AibEe7d0knPY/edit?usp=sharing
try:
=ARRAYFORMULA(IF(B2:B="";;MMULT(TRANSPOSE((ROW(M2:M)<=TRANSPOSE(ROW(M2:M)))*
MMULT(C2:I*1; ROW(A1:A7)^0)); SIGN(MMULT(C2:I*1; ROW(A1:A7)^0))^0)))
这篇关于具有多列的 Arrayformula 运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
