SumProduct超过一组单元格（不连续） [英] SumProduct over sets of cells (not contiguous)
问题描述
= SUM（D32 * D2，D94 * D64，D156 * D126，D218 * D188）/ SUM D32，D94，D156，D218）
我宁愿使用SumProduct，如果我可以让它更多可读。我试图做：
= SUMPRODUCT（（D2，D64，D126，D188），（D32，D94，D156，D218 ））/ SUM（D32,94，D156，D218）
但正如你可以告诉我的发布在这里，没有工作。有没有办法做我想要的SumProduct？
可能是高效的excel-fu，但即使可以完成，也不太可能更可读比你原来的解决方案。问题是，即使在20多年以后，Excel仍然是不连续的范围。命名它们将无法正常工作，数组公式将无法正常工作，正如您使用SUMPRODUCT看到的那样，它们通常不会在元组数组函数中工作。你最好的打算是想出一个自定义的功能。
更新
你有问题让我考虑如何处理不连续的范围。过去，我不得不处理很多事情。我没有时间给出一个更好的答案，当你提出问题，但现在我已经有几分钟，我已经打了一个自定义功能，将做你想要的：
函数gvSUMPRODUCT（ParamArray rng（）As Variant）
Dim sumProd As Integer
Dim valuesIndex As Integer
Dim值（）As Double
对于每个r在rng（）
对于每个c在r.Cells
错误GoTo VBAIsSuchAPainInTheAssSometimes
valuesIndex = UBound（values ）+ 1
错误GoTo 0
ReDim保存值（valuesIndex）
值（valuesIndex）= c.Value
下一步c
下一步r
如果valuesIndex Mod 2 = 1然后
对于i = 0 To（valuesIndex - 1）/ 2
sumProd = sumProd + values（i）*值（i +（valuesIndex + 1）/ 2）
Next i
gvSUMPRODUCT = sumProd
退出函数
Else
g vSUMPRODUCT = CVErr（xlErrValue）
退出函数
结束如果
VBAIsSuchAPainInTheAssSometimes：
valuesIndex = 0
简历Next
结束功能
一些注释：
- Excel枚举列，然后列列，如果您有一个连续的范围，其中数据按列组织，您必须选择单独的范围：gvSUMPRODUCT（A1：A10，B1：B10）而不是gvSUMPRODUCT（A1： B10）。
- 该功能通过将前半部分与第二个单元格成对乘法，然后将这些乘积相加：gvSUMPRODUCT（A1，C3，L2，B2，G5，F4）= A1 * B2 + C3 * G5 + L2 * F4。即
- 您可以通过执行像gvNSUMPRODUCT（n，ranges）这样的方式将函数扩展为包括n-wise乘法。
- 如果有奇数个单元格（不是范围），它返回#VALUE错误。
I have a total data set that is for 4 different groupings. One of the values is the average time, the other is count. For the Total I have to multiply these and then divide by the total of the count. Currently I use:
=SUM(D32*D2,D94*D64,D156*D126,D218*D188)/SUM(D32,D94,D156,D218)
I would rather use a SumProduct if I can to make it more readable. I tried to do:
=SUMPRODUCT((D2,D64,D126,D188),(D32,D94,D156,D218))/SUM(D32,94,D156,D218)
But as you can tell by my posting here, that did not work. Is there a way to do SumProduct like I want?
It might be possible with masterful excel-fu, but even if it can be done, it's not likely to be more readable than your original solution. The problem is that even after 20+ years, Excel still borks discontinuous ranges. Naming them won't work, array formulas won't work and as you see with SUMPRODUCT, they don't generally work in tuple-wise array functions. Your best bet here is to come up with a custom function.
UPDATE
You're question got me thinking about how to handle discontinuous ranges. It's not something I've had to deal with much in the past. I didn't have the time to give a better answer when you asked the question but now that I've got a few minutes, I've whipped up a custom function that will do what you want:
Function gvSUMPRODUCT(ParamArray rng() As Variant)
Dim sumProd As Integer
Dim valuesIndex As Integer
Dim values() As Double
For Each r In rng()
For Each c In r.Cells
On Error GoTo VBAIsSuchAPainInTheAssSometimes
valuesIndex = UBound(values) + 1
On Error GoTo 0
ReDim Preserve values(valuesIndex)
values(valuesIndex) = c.Value
Next c
Next r
If valuesIndex Mod 2 = 1 Then
For i = 0 To (valuesIndex - 1) / 2
sumProd = sumProd + values(i) * values(i + (valuesIndex + 1) / 2)
Next i
gvSUMPRODUCT = sumProd
Exit Function
Else
gvSUMPRODUCT = CVErr(xlErrValue)
Exit Function
End If
VBAIsSuchAPainInTheAssSometimes:
valuesIndex = 0
Resume Next
End Function
Some notes:
- Excel enumerates ranges by column then row so if you have a continuous range where the data is organized by column, you have to select separate ranges: gvSUMPRODUCT(A1:A10,B1:B10) and not gvSUMPRODUCT(A1:B10).
- The function works by pairwise multiplying the first half of cells with the second and then summing those products: gvSUMPRODUCT(A1,C3,L2,B2,G5,F4) = A1*B2 + C3*G5 + L2*F4. I.e. order matters.
- You could extend the function to include n-wise multiplication by doing something like gvNSUMPRODUCT(n,ranges).
- If there are an odd number of cells (not ranges), it returns the #VALUE error.
这篇关于SumProduct超过一组单元格（不连续）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
