多列的Arrayformula Running Total

问题描述

我有以下任务要解决，但是我无法解决这个问题.

不同年份有几个数字.我想获得J列中所有列(=范围C:I)的运行总和.我可以在J的每个单元格中使用一个公式来实现这一点-但是我需要在J2中使用单个arrayformula来实现./p>

经过大量研究，我认为是两个步骤:

1. 将空单元格替换为0，因为arrayformulas显然对空单元格存在一些问题
2. 对每一行求和

对于第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:

1. replace empty cells with 0s, since arrayformulas obviously have some problems with empty cells
2. 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 Running Total的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！