如何在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列的列值是平方的.

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是带有ctrl + shift + enter的= MAX(A1:A6 + B1:B6)，可以正常工作并给出正确的结果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，并给我最后的行最大值.

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

此函数有效并且在少于10万行的情况下非常快，但是，如果范围中的行数接近可能的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 + 输入！

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屋！