☰

简单来说

tag-icon • tag-icon microsoft-excel
简单来说

[请勿使用 VB。]

简单来说

在 Excel 中，假设我在动态数组 (DA) 中有一个数字数据块 - 即它正在溢出，而我不知道（也不想知道）行数或列数。我想对每列求和，最终这些和也位于 DA 中（溢出）。

举例来说，考虑一个简单的 4x4 数组，其根是 A1：

 1  2  3  4
 5  6  7  8
 9 10 11 12
13 14 15 16

我希望最终得到以 A10 为根的 DA 中的四列总和，因此：

28 32 36 40

我怎么做？

细节

我知道意义每个结果单元格应具有：

A10=SUM(INDEX(A1#,0,1))
B10=SUM(INDEX(A1#,0,2))
C10=SUM(INDEX(A1#,0,3)) and
D10=SUM(INDEX(A1#,0,4))

因此，我一直在尝试找到一种方法将数组 {1,2,3,4} 放入 INDEX 的第三个参数中。如果我在垂直方向上牺牲动态性（动态性？动态性？...），这很简单。例如：

A10=SUM(INDEX($A$1#,0,COLUMNS($A:A)))
B10=SUM(INDEX($A$1#,0,COLUMNS($A:B)))
C10=SUM(INDEX($A$1#,0,COLUMNS($A:C)))
D10=SUM(INDEX($A$1#,0,COLUMNS($A:D)))

但正如我所说，在实践中，我不知道有多少行（或列），所以这一切都需要保持动态/可溢出。到目前为止，我尝试了各种越来越绝望的事情，例如：

A10=SUM(INDEX(A1#,0,SEQUENCE(1,COLUMNS(A1#))))

但这给了我一个标量（单细胞）答案（第一个排因为某些原因）。

我尝试将该 SEQUENCE 放入辅助行（作为 DA），然后改用它。因此，如果辅助行的根位于 A9，我会得到：

A10=SUM(INDEX(A1#,0,A9#))

这会产生与上述相同的结果。（我想我至少应该感到高兴，因为 Excel 始终没有按照我想要的方式运行！）

答案1

如果你重新考虑避免使用 VBA

在Sheet1 A1我输入：

={1,2,3,4,5,6;7,8,9,10,11,99;100,0,0,0,100,0}

这将生成一个很好的动态二维数组：

在此处输入图片描述

在标准模块中输入以下用户定义函数：

Public Function foo(r)
    Dim arr, temp, U1 As Long, U2 As Long, i1 As Long, i2 As Long

    arr = Evaluate(r.Formula)
    U1 = UBound(arr, 1)
    U2 = UBound(arr, 2)

    ReDim temp(1 To 1, 1 To UBound(arr, 2))

    For i2 = 1 To U2
        temp(1, i2) = 0
        For i1 = 1 To U1
            temp(1, i2) = temp(1, i2) + arr(i1, i2)
        Next i1
    Next i2

    foo = temp
End Function

然后在A10进入：

=foo(A1)

在此处输入图片描述

它会根据需要进行求和并“溢出”。

答案2

您可能想要尝试对每列求和（假设 A1# 是包含源数据的动态范围）：

=SUBTOTAL(9,OFFSET(A1#,0,SEQUENCE(1,COLUMNS(A1#))-1,ROWS(A1#),1))

只需将 SUBTOTAL 函数的第一个参数更改为使用任何可用的聚合函数即可。经过一些调整，这可以用于行总计

答案3

您可以制定如下通用 DA 公式：

=LET( array, A1#,
       ones, SIGN( SEQUENCE( 1, ROWS( array ) ) ),
       MMULT( ones, array ) )

大批是要求和的范围。我将其设置为A1 #以符合你的例子。

可能有更好的方法来创建一系列 1，但我想到的就是这样。无论如何，这个想法是对一系列 MMULT那些乘以每列大批然后，MMULT 将这些垂直列相加为具有相同列数的 DA。

在这种方法中，你不需要知道数组中的列数或行数。它会根据你输入的内容进行调整大批。

如果您没有 Microsoft 365 并且无法使用 LET，那么您可以执行以下操作：

=MMULT( SIGN( SEQUENCE( 1, ROWS( A1# ) ) ), A1# )

工作原理相同，但可读性较差......

相关内容