![乘以 first * last 而不是 first * first 的乘积和](https://linux22.com/image/1288681/%E4%B9%98%E4%BB%A5%20first%20*%20last%20%E8%80%8C%E4%B8%8D%E6%98%AF%20first%20*%20first%20%E7%9A%84%E4%B9%98%E7%A7%AF%E5%92%8C.png)
我正在寻找 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 部分)“移动”或返回值数组所必需的。