如何在Excel中的数组公式中进行逐行求和? [英] How to do a row-wise sum in an array formula in Excel?

问题描述

假设我有以下基本电子表格:

Say I have the following basic spreadsheet:

    A   B   C   D
1  -2   4   2  12
2  -1   1   0
3   0   0   0  22
4   1   1   2  12
5   2   4   6
6   3   9  12

A 列包含从 -2 到 3 的整数.

The A column has integers from -2 to 3.

B 列具有 a 列值的平方.

The B column has the a column value squared.

C 列是 A 和 B 的行和，因此 C1 为 =SUM(A1:B1).

The C column is the row sum of A and B so C1 is =SUM(A1:B1).

D1 有 =MAX(C1:C6)，这个最大值是我需要用一个公式得到的结果.

D1 has =MAX(C1:C6) and this max is the result I need to get with a single formula.

D3 是用 Ctrl+Shift+Enter 输入的 =MAX(SUM(A1:B6))，但它只会产生一个常规的总和.D4 是 =MAX(A1:A6+B1:B6) 和 ctrl+shift+enter，这有效并给出了 12 的正确结果.

D3 is =MAX(SUM(A1:B6)) entered with Ctrl+Shift+Enter, but it just results in a regular sum. D4 is =MAX(A1:A6+B1:B6) with ctrl+shift+enter, and this works and gives the correct result of 12.

但是，D4 的问题是我需要能够处理大动态范围，而无需输入无穷无尽的总和.假设 SUM(A1:Z1000) 将是 A1:A1000+B1:B1000+....+Z1:Z1000，这不是一个合理的公式.

However the problem with D4 is that I need to be able to handle large dynamic ranges without entering endless sums. Say SUM(A1:Z1000) would be A1:A1000+B1:B1000+....+Z1:Z1000 which is not a reasonable formula.

那么我该如何做类似 =MAX(SUM(A1:Z1000)) 的事情，这样它将 A1:Z1 到 A1000:Z1000 的行相加，并给我最终的 row-wize max.

So how can I do something like =MAX(SUM(A1:Z1000)) such that it would sum the rows A1:Z1 to A1000:Z1000 and give me the final row-wize max.

我只能使用基本 Excel，所以没有辅助列，也没有 VBA 函数.

I can only use base Excel, so no helper columns and no VBA function.

更新由于没有任何成功的答案，我不得不假设当前的 Excel 版本是不可能的.

UPDATE Since there have not been any successful answers, I have to assume it is not possible with current Excel versions.

所以我正在尝试在 VBA 中构建这个函数，这就是我目前所拥有的.

So I am trying to build this function in VBA and this is what I have so far.

Function MAXROWSUM(Ref As Range) As Double
    Dim Result, tempSum As Double
    Dim Started As Boolean
    Started = False
    Result = 0
    For Each Row In Ref.Rows
        tempSum = Application.WorksheetFunction.Sum(Row)
        If (Started = False) Then
            Result = tempSum
            Started = True
        ElseIf tempSum > Result Then
            Result = tempSum
        End If
    Next Row
    MAXROWSUM = Result
End Function

这个函数可以在少于 100k 行的情况下运行并且非常快，但是如果范围内的行数接近可能的 100 万行，即使该范围的大部分是空的，该函数也会变得非常慢，需要几秒钟.有没有办法通过过滤掉任何空行来显着优化当前代码?在我的示例中，如果我输入 MAXROWSUM(A1:B1000000) 它将起作用，但会很慢，我可以让它非常快吗?

This function works and is quite fast with less than 100k rows, but if the row count in the range approaches the possible 1 million, the function becomes very slow taking several seconds, even if most of the range is empty. Is there a way to significantly optimize the current code, by possibly filtering out any empty rows? In my example if I enter MAXROWSUM(A1:B1000000) it will work, but will be slow, can I make this very fast?

推荐答案

你的解决方案是 矩阵乘法，通过 MMULT函数

Your solution is Matrix Multiplication, via the MMULT function

其工作原理如下:目前，您有一个 X*N 数组/矩阵，您希望将其更改为 X*1 矩阵(其中每个新行是旧矩阵中行的总和)，然后取最大值.为此，您需要将它乘以一个 N*1 矩阵:两个 N 抵消".

How this works is as follows: currently, you have an X*N array/matrix, which you want to change into an X*1 matrix (where each new row is the sum of the rows in the old matrix), and then take the maximum value. To do this, you need to multiply it by an N*1 matrix: the two Ns "cancel out".

第一步很简单:第二个矩阵中的每个值都是1

The first step is simple: every value in your second matrix is 1

示例:[6*2] ∙ [2*1] = [6*1]

Example: [6*2] ∙ [2*1] = [6*1]

[[-2][ 4]             [[ 2]
 [-1][ 1]    [[ 1]     [ 0]
 [ 0][ 0]  ∙  [ 1]] =  [ 0]
 [ 1][ 1]              [ 2]
 [ 2][ 4]              [ 6]
 [ 3][ 9]]             [12]]

我们然后MAX这个:

=MAX(MMULT(A1:B6,{1;1}))

要动态生成第二个数组(即任何大小)，我们可以使用表格的第一行，将其完全转换为1(例如，列号> 0")，然后 TRANSPOSE 这是一列而不是一行.这可以写成 TRANSPOSE(--(COLUMN(A1:B1)>0))

To generate our second array dynamically (i.e. for any size), we can use the first Row of our table, convert it entirely to 1 (for example, "Column number > 0"), and then TRANSPOSE this to be a column instead of a row. This can be written as TRANSPOSE(--(COLUMN(A1:B1)>0))

把它们放在一起，我们得到:

Put it all together, and we get:

=MAX(MMULT(A1:B6, TRANSPOSE(--(COLUMN(A1:B1)>0))))

由于 MMULT 与数组一起使用 - 例如 SUMPRODUCT - 我们实际上不需要使用 Ctrl+<将其定义为数组公式kbd>Shift+Enter也可以！

Since MMULT works with arrays - like SUMPRODUCT - we don't actually need to define this as an Array Formula with Ctrl+Shift+Enter either!

如果您想按列执行此操作，则需要交换数组 - 矩阵乘法不可交换:

If you wanted to do this column-wise instead, then you would need to swap the arrays around - Matrix Multiplication is not commutative:

=MAX(MMULT(TRANSPOSE(--(ROW(A1:A6)>0)), A1:B6))

[1*6] ∙ [6*2] = [2*1]

[1*6] ∙ [6*2] = [2*1]

                              [[-2][ 4]
                               [-1][ 1]
[[ 1][ 1][ 1][ 1][ 1][ 1]]  ∙  [ 0][ 0]   =  [[ 3][19]]
                               [ 1][ 1]
                               [ 2][ 4]
                               [ 3][ 9]]

这篇关于如何在Excel中的数组公式中进行逐行求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！