如何将 SUM 函数与 Excel 中的(新)动态数组一起使用 [英] How to use SUM function with (new) dynamic arrays in Excel

问题描述

假设 3 列(A、B、C)是动态数组，我想创建第四个/最后一个动态数组公式，它是 D 列中每一行的这 3 列的总和.为了清楚起见，我正在寻找用于最后一列中每一行的逐行总和.

Let's say 3 columns (A, B, C) are dynamic arrays and I want to create a fourth/final dynamic array formula that is the sum of these 3 columns for each row in column D. For clarity, I am looking for the row-by-row sum of each row in this final column.

这将起作用:=A2#+B2#+C2#

如何使用 SUM 函数实现相同的功能?我问的原因是这更容易用于更大范围的数据.

How can the same be accomplished by using the SUM function? The reason I ask is that this is easier to use on larger ranges of data.

下面给出了一个#REF！错误:=SUM(A2:C2#)

The following gives a #REF! error: =SUM(A2:C2#)

推荐答案

---

新

通过添加 BYROW 和 LAMBDA，我们可以比我在下面的原始答案更容易做到这一点:

With the addition of BYROW and LAMBDA we can do this a little easier than my original answer below:

=BYROW(A1#:C1#,LAMBDA(x,SUM(x)))

BYROW 将每一行传递到 LAMBDA 中，该 LAMBDA 迭代地执行 SUM 并返回一个数组:

The BYROW passes each row into the LAMBDA which does the SUM iteratively and returns an array:

原始答案

问题是 SUM、MAX、MIN 都允许数组并在完整数组上执行整个操作.所以我们需要使用一些使用数组并溢出单个结果的东西.这就是 MMULT 的目的.:

The problem is that SUM,MAX,MIN all allow arrays and do the whole on the full array. So we need to use something that uses arrays and spills individual results. That is what MMULT was built for.:

=MMULT(A2#:C2#,TRANSPOSE(COLUMN(A2:C2)^0))

刚刚通过我们有 SEQUENCE 的动态数组实现:

Just realized with the dynamic arrays we have SEQUENCE:

=MMULT(A2#:C2#,SEQUENCE(COLUMNS(A2:C2),,1,0))

这篇关于如何将 SUM 函数与 Excel 中的(新)动态数组一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！