使用新的Excel动态数组公式计算累积总和公式

Question

我正在使用Excel中的新动态（也称为溢出）公式。我需要一个动态数组，它是另一个动态数组的累加或运行总计。

假设我在A1:A8中有以下动态数据：

12
20
14
13
12
13
26
11

在这个数组中找到差异是很琐碎的：

=OFFSET(A1#,1,0)-A1#

8
-6
-1
-1
1
13
-15
-11

但是如何使用新的动态公式获得运行总数？

12
32
46
59
71
84
110
121

- kale

Answer 1

累积求和公式，使用 SCAN() 和 LAMBDA() -- 动态Spill数组公式

---

• 单元格 B1 中使用的公式 --> 使用 SCAN() 和 LAMBDA() 方法 --> 适用于 MS365

=SCAN(0,A1:A8,LAMBDA(x,y,x+y))

---

• 单元格 C1 中使用的公式 --> 使用 VSTACK()、SCAN() 和 LAMBDA() 的方法 --> 适用于 MS365 Office Insiders Beta 渠道。

=VSTACK(A1,SCAN(A1,A2:A8,LAMBDA(x,y,x+y)))

---

• 单元格 D1 中使用的公式 --> 使用 LET(), SCAN() 和 LAMBDA() 的方法 --> 适用于 MS365

=LET(x,A1:A8,
sum,LAMBDA(z,y,z+y),
SCAN(0,x,sum))

---

使用MAP()和LAMBDA()

• 在单元格B1中使用的公式

=MAP(A1:A8,LAMBDA(x,SUM(A1:x)))

- Mayukh Bhattacharya

---

非常流畅。Lambda函数对于Excel来说是一个改变游戏规则的东西。 - kale

绝对地说，LAMBDA() 对于 Excel 真的是一个改变游戏规则的函数。 - Mayukh Bhattacharya

矩阵函数很好，但现在应该接受这个答案。 - Lukas

非常漂亮的字体适用于Excel。它是什么字体？（我也喜欢这个答案） - undefined

11/15/23之前的更新是Biome字体，而11/15/23的更新是Daytona。 - undefined

Answer 2

以下是使用矩阵乘法的另一种方法

=MMULT(TRANSPOSE((ROW(I3#) <= TRANSPOSE(ROW(I3#)))*I3#),SIGN(I3#))

- chris neilsen

---

这是杰出的。 - Dillon

Answer 3

新回答 2022年6月20日

要使用 A1 中的动态数组，请使用以下公式：

=MMULT(N(ROW(A1#)>=TRANSPOSE(ROW(A1#))),A1#)

如果A1 中的动态数组有多列，此公式将返回每列的累积总和，不像我更复杂的原始答案。

原始答案 2022年1月13日

通过使用 SIGN(I3#)，chris neilsen 的解决方案会累加绝对值。为了适应负数，请在累积总和中将SIGN(I3#) 替换为 1*(I3#=I3#)：

=MMULT(TRANSPOSE((ROW(I3#) <= TRANSPOSE(ROW(I3#)))*I3#),1*(I3#=I3#))

或者，可以使用以下公式生成一个动态数组来替换 1*(I3#=I3#)：

SEQUENCE(ROWS(I3#),,,0)

（我没有足够的声望进行评论。）

- bkraines

---

嗨@bkraines。感谢您的更新。 - kale

以下公式在我看来稍微简单一些，但基本上是相同的：MMULT((ROW(D2#)>=TRANSPOSE(ROW(D2#)))*1,D2#)；通过将<=改为>=，你可以省去一个TRANSPOSE。 - Lukas

Answer 4

我曾经用过一种方法来实现它，但想知道是否有更简单/更高效的方法来实现...

=SUBTOTAL(9,OFFSET(A1#,0,0,SEQUENCE(COUNT(A1#))))

- kale

Answer 5

这个解决了滚动十二个数字的总和。

=SUBTOTAL(9,OFFSET(A1#,SEQUENCE(COUNTA(A1#),1,0),0,12))

- Eduardo Nozawa

Answer 6

你也可以使用以下方法：

=SUBTOTAL(9,INDIRECT(CELL("Address",A1)&":"&CELL("address",OFFSET(A1,SEQUENCE(NumberOfRows,0,0,1),0))))

假设在上面的单元格A1中包含了你想要求和的溢出公式。你也可以使用一个辅助列来使用SEQUENCE()函数，如果你在B列中这样做，你将会使用：

=SUBTOTAL(9,INDIRECT(CELL("Address",A1)&":"&CELL("address",OFFSET(A1,B1#,0))))

如果您使用= SUBTOTAL（6，...），这也适用于累积乘积

我发现上面给出的答案对我不起作用。

• 对于矩阵乘法，它适用于累积总和，但不适用于累积乘积。因此，我的答案更加通用。
• 对于早期使用SUBTOTAL（）的答案，由于某种原因，我必须向SEQUENCE（）生成的值添加一个数字。在我的情况下，我必须添加3，这似乎是随机的。我找不到必须添加它的原因，但是没有它，累积总和会少3行。

我的答案似乎不适用于PRODUCT（）和SUM（）公式，仅适用于SUBTOTAL（）。我猜测PRODUCT（）和SUM（）尚未更改为溢出/数组类型公式，但SUBTOTAL已经更改了。

- Punter