vba 将两个范围/数组逐个单元格相乘
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26289736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiply two ranges/arrays cell by cell
提问by smokysunday
I'm trying to write an Excel UDF (User Defined Function) that takes two ranges and multiplies the first cell in range1 with the first cell in range2, second cell in range1 by second cell in range2, and so on, then stores the result in an array.
我正在尝试编写一个 Excel UDF（用户定义函数），它采用两个范围并将 range1 中的第一个单元格与 range2 中的第一个单元格、range1 中的第二个单元格乘以 range2 中的第二个单元格，依此类推，然后存储结果在一个数组中。
Imagine array1 in cells A1:A4 {1,0,1,2} and array2 in cells B1:B4 {1,1,0,1}. My function VECTORMULT(A1:A4, B1:B4) would return {1,0,0,2}.
想象一下单元格 A1:A4 {1,0,1,2} 中的 array1 和单元格 B1:B4 {1,1,0,1} 中的 array2。我的函数 VECTORMULT(A1:A4, B1:B4) 将返回 {1,0,0,2}。
Function VECTORMULT(array1 As Range, array2 As Range) As Variant
'takes 2 ranges and multiplies cell1 by cell1, cell2 by cell2, etc _
and stores it in a vector array
Dim Result() As Variant
Dim largerArray As Range
Dim smallerArray As Range
Dim i As Integer
'determine the smaller range to determine UBound in Result() array
If array1.Cells.Count >= array2.Cells.Count Then
Set largerArray = array1
Set smallerArray = array2
Else
Set largerArray = array2
Set smallerArray = array1
End If
ReDim Result(1 To smallerArray.Cells.Count)
'THIS IS THE PART THAT FAILS
For i = 1 To smallerArray.Cells.Count
Result(i) = largerArray.Item(i).value * smallerArray.Item(i).value
Next i
VECTORMULT = Result
End Function
I had envisioned writing a more general function that accepted unlimited ParamArray Args() and parsed each Arg as an array, BUT I can't even solve this seemingly simple cell iterator function. I would think VBA could handle stepping through a range in some default manner like
我曾设想编写一个更通用的函数，它接受无限制的 ParamArray Args() 并将每个 Arg 解析为一个数组，但我什至无法解决这个看似简单的单元格迭代器函数。我认为 VBA 可以以某种默认方式处理跨一个范围，例如
Range(someRange).Item(i)
but it doesn't... For what it's worth, I DO seem to get correct values when I substitute correct Row/Column indeces for the Item function, like below; but then Result only works on 1 cell (instead of an array). I need to figure out how to pass "i".
但它没有......对于它的价值，当我用正确的 Row/Column indeces 替换 Item 函数时，我似乎确实得到了正确的值，如下所示；但随后 Result 仅适用于 1 个单元格（而不是数组）。我需要弄清楚如何传递“i”。
'substitute Item(1,1) for Item(i) and it DOES work
For i = 1 To smallerArray.Cells.Count
Result(i) = largerArray.Item(1,1).value * smallerArray.Item(1,1).value
Next i
回答by
The INDEXfunction performs the task of delivering an array of modified information by blanking out the row_numand column_numparameters in its array form, e.g. INDEX((A1:A4)*(B1:B4),,). The size of the ranges has to match but should unless you are referencing named ranges that could dynamically change their shape. Examples (using your sample data):
该INDEX函数通过将row_num和column_num参数以数组形式清空来执行传递修改信息数组的任务，例如INDEX((A1:A4)*(B1:B4),,)。范围的大小必须匹配，但应该匹配，除非您正在引用可以动态更改其形状的命名范围。示例（使用您的示例数据）：
=SUM(INDEX((A1:A4)*(B1:B4),,)) '? 3
=MIN(INDEX((A1:A4)*(B1:B4),,)) '? 0
=MAX(INDEX((A1:A4)*(B1:B4),,)) '? 2
=AVERAGE(INDEX((A1:A4)*(B1:B4),,)) '? 0.75
FWIW, I use this form of INDEXto provide many standard formulas when it seems that only an array formula would work. It is processed as an array but does not require Ctrl+Shift+Enter.
FWIW，INDEX当似乎只有数组公式可以工作时，我使用这种形式来提供许多标准公式。它作为数组处理，但不需要Ctrl+Shift+Enter。
MINIF, MAXIF and MODEIF with Standard Formulas
For Excel 2010 and higher, look into the new AGGREGATE functionfor additional functionality.
对于 Excel 2010 及更高版本，请查看新的AGGREGATE 函数以获取其他功能。
回答by pnuts
How you plan to use this in conjunction with other formulae may make a big difference but:
您打算如何将其与其他公式结合使用可能会产生很大的不同，但是：
=A1:A4*B1:B4
entered as an array formula will return {1,0,0,2} - though not visible until say copied down. But the elements of the array may be accessed independently, say:
作为数组公式输入将返回 {1,0,0,2} - 尽管在复制之前不可见。但是数组的元素可以独立访问，比如：
=INDEX(A1:A4*B1:B4,4)
(also an array formula) to return 2.
（也是一个数组公式）返回2.
