☰

乘以 first * last 而不是 first * first 的乘积和

tag-icon • tag-icon microsoft-excel microsoft-excel-2007
乘以 first * last 而不是 first * first 的乘积和

我正在寻找 Excel 2007 中的一些函数或公式来执行以下操作：

我有两行数字：

1 -1  2 5 10 
1  2 -1 2  5

我想做类似 sumproduct 的操作，即将每列中的条目相乘，然后相加总数。但是，我想将其中一行中的第一个条目乘以另一行中的最后一个条目，然后将第二个条目乘以倒数第二个条目，依此类推。

所以：

1 * 5 + -1 * 2 + 2 * -1 + 5 * 2 + 10 * 1

而不仅仅是

1*1+-1*2+...

是否有一些合理的方法来进行这种逆序和积类型的计算？

我不希望为我想要计算的每个和积都按相反的顺序创建额外的行。

答案1

您可以使用以下公式添加另一行（例如第 3 行）：

=INDEX($A$2:$J$2,COUNTA($A$2:$J$2)-COLUMN()+1)

然后处理SUMPRODUCT第一行和第三行。

我看不出只用一条语句就能做到这一点

答案2

假设您的示例放在和中A1:E1，A2:E2以下内容将在单个公式中完成您需要的操作，只需修改第二个范围如何输入到即可SUMPRODUCT()：
（为了清晰起见并避免出现滚动条，请换行）

=SUMPRODUCT(A1:E1,
            N(OFFSET(A2:E2,0,COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1))
           )

快速浏览一下组件：

OFFSET(A2:E2,0,COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1)

SUMPRODUCT()这实际上在范围 上进行迭代（因为它被 用作数组） A2:E2。它在同一行（0）上执行此操作，但然后使用 计算的水平偏移量COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1，它将从 中的列数A2:E2减一（即最后一个条目）开始并减少到零（即第一个条目），因此当将其作为数组读取时，它将以相反的顺序读取。

这包含在一个N()调用中，以确保任何空单元格、文本等都读取为零，并防止#VALUE!在这些情况下出现错误。如果你想这会产生错误，那么不要使用N()。

然后将其简单地放入SUMPRODUCT()要使用的第二个数组中。

答案3

这里有一个 UDF 可以帮助您入门。

Function SUMPRODREV(rForward As Range, rBackward As Range) As Double

    Dim i As Long, j As Long
    Dim vaForw As Variant
    Dim vaBack As Variant
    Dim dReturn As Double

    'put range values into arrays
    vaForw = rForward.Value: vaBack = rBackward.Value

    'if only 1 row, multiply columns
    If UBound(vaForw, 1) = 1 Then
        For i = LBound(vaForw, 2) To UBound(vaForw, 2)
            dReturn = dReturn + (vaForw(1, i) * vaBack(1, UBound(vaForw, 2) - (i - 1)))
        Next i
    Else 'if only 1 column, multiply rows
        For i = LBound(vaForw, 1) To UBound(vaForw, 1)
            dReturn = dReturn + (vaForw(i, 1) * vaBack(UBound(vaForw, 1) - (i - 1), 1))
        Next i
    End If

    SUMPRODREV = dReturn

End Function

答案4

这里有一个可以满足您需求的公式，但输入它有一个技巧。

假设您的单元格范围如下所示。我已将范围命名为“顶部”和“底部”。

在此处输入图片描述

1
突出显示任意 2 个空白单元格。在本例中，我选择了单元格 B4:C4，如下所示：

在此处输入图片描述

2
将其输入到公式栏作为数组公式（按Ctrl++ Shift）Enter：

=SUM(top*INDEX(bottom,1,LARGE(COLUMN(bottom),COLUMN(top))))

或者这样（不使用命名范围）：

=SUM(A1:J1*INDEX(A2:J2,1,LARGE(COLUMN(A2:J2),COLUMN(A1:J1))))

在此处输入图片描述

您将获得类似上述结果；常规SUMPRODUCT收益为 220。

缺点是，您只能通过更改字体颜色或隐藏其列或行来隐藏额外的单元格。额外的单元格是强制公式的第二部分（INDEX 部分）“移动”或返回值数组所必需的。

相关内容