一维数组乘以一维数组或常量-VBA [英] Multiply 1D-array by 1D-array or Constant - VBA
问题描述
冒着成为主题的风险，我决定分享一些代码，
然后，因为我们基本上给 .Index 这样的数组，如 {1,2,3,4,5,6,7,8,9,10} ，但是以一种动态的方式，对于行，对于第一列，它仅为1， .Index 将从该2D数组中切出一个1D数组：
将1D数组乘以1D数组
工作是一样的。让我们想象以下内容：
Sub Multiply_1D_by1D（）
Dim arr1 As Variant：arr1 = Array（ 1，4，3，5，10，15，13，11，6，9）
Dim arr2作为变体：arr2 = Array（2，1，4，4，1，2，3，2，5，2 ，1）
带有应用程序
Dim y只要long：y = UBound（arr1）+ 1
Dim arr3作为变量：arr3 = .Evaluate（ TRANSPOSE （ROW（1：& y&）））
Dim arr4作为变体：arr4 = .Index（.MMult（.Transpose（arr1），arr2），arr3，arr3）
结尾为
结尾子
这次我们不t告诉 .Index 从 .MMult 的结果中提取相同的，恒定的第一列，但是我们给它与行相同的值数组。这些值必须是一维数组，因此我们使用 .Evaluate 动态返回数组。因此，上面的代码返回了一维数组，例如：
{2，4，12，5，5，20，45，26，55，12， 9}
要直观地了解其工作原理，请执行以下操作： .MMult 将返回2D-上面的示例中的数组是这样的：
然后，因为我们基本上给 .Index 两个数组，例如 {1,2,3,4,5,6,7,8,9,10} ，但以动态方式， .Index 将从该2D数组中切出一个1D数组：
可以使用 .Index 从2D数组中切出任何1D数组，只要您都使用有效的1D数组指定rows和columns参数即可。我希望这对任何人都有帮助。
At risk of being of topic, I decided to share some code, Q&A-style. If the general opinion is such that this would be off-topic I'll be happy to delete if need be.
Background:
I've been wondering if it was possible to return a 1D-array from multiplying another 1D-array by either a constant value or a third 1D-array (of the same size) without iteration.
So the process I'm looking for would look like:
- Multiply by Constant > Derive
{3,6,9}directly from{1,2,3}*3 - Multiply by array > Derive
{3,8,15}directly from{1,2,3}*{3,4,5}
Sample Code:
I have seen questions regarding this topic, but I've not yet seen an answer that would do this without iteration. The closest I've seen is from @SiddharthRout, on an external forum.
But usually one would opt for iteration:
- Multiply by constant
Sub Test()
Dim arr1 As Variant: arr1 = Array(1,2,3)
Dim y As Long, x As Long: x = 3 'Our constant
For y = LBound(arr1) To UBound(arr1)
arr1(y) = arr1(y) * x
Next y
End Sub
- Multiply by array
Sub Test()
Dim arr1 As Variant: arr1 = Array(1, 2, 3)
Dim arr2 As Variant: arr2 = Array(3, 4, 5)
Dim y As Long
For y = LBound(arr1) To UBound(arr1)
arr1(y) = arr1(y) * arr2(y)
Next y
End Sub
Question:
How could you retrieve a 1D-array from multiplying another 1D-array by any constant or another (equally sized) 1D-array without iteration?
As of what I found was that the key to the answer would lay in MMULT, returning an array from multiplying rows*columns.
Multiply 1D-Array by Constant
Sub Multiply_1D_byConstant()
Dim arr1 As Variant: arr1 = Array(1, 4, 3, 5, 10, 15, 13, 11, 6, 9)
With Application
Dim x As Long: x = 3 'Our constant
Dim y As Long: y = UBound(arr1) + 1
Dim arr2 As Variant: arr2 = .Evaluate("TRANSPOSE(ROW(" & x + 1 & ":" & x + y + 1 & ")-ROW(1:" & y + 1 & "))")
Dim arr3 As Variant: arr3 = .Evaluate("TRANSPOSE(ROW(1:" & y & "))")
Dim arr4 As Variant: arr4 = .Index(.MMult(.Transpose(arr1), arr2), arr3, 1)
End With
End Sub
Here, .Evaluate will quickly return a 1D-array n times our constant, n being Ubound(arr1)+1. In the above case: {3,3,3,3,3,3,3,3,3,3}
We than .Transpose arr1 within our .MMult(.Transpose(arr1), arr2) which will return a 2D-array. Because we would need to iterate that, we rather cut into the array to extract a 1D-array by .Index. The result of the above would be:
{3, 12, 9, 15, 30, 45, 39, 33, 18, 27}
To visualize how this works: .MMult will return a 2D-array from above example like so:
Then, because we basically give .Index an array like {1,2,3,4,5,6,7,8,9,10}, but in a dynamic way, for rows and just a 1 for the first column, .Index will slice a 1D-array out of this 2D-array:
Multiply 1D-Array by 1D-Array
This would work kind of the same. Let's imagine the below:
Sub Multiply_1D_by1D()
Dim arr1 As Variant: arr1 = Array(1, 4, 3, 5, 10, 15, 13, 11, 6, 9)
Dim arr2 As Variant: arr2 = Array(2, 1, 4, 1, 2, 3, 2, 5, 2, 1)
With Application
Dim y As Long: y = UBound(arr1) + 1
Dim arr3 As Variant: arr3 = .Evaluate("TRANSPOSE(ROW(1:" & y & "))")
Dim arr4 As Variant: arr4 = .Index(.MMult(.Transpose(arr1), arr2), arr3, arr3)
End With
End Sub
This time we don't tell .Index to extract the same, constant, first column from the result of .MMult, but we give it the same array of values as the rows. These values need to be a 1D-array so therefor we use the .Evaluate to return the array dynamically. So the above returns a 1D-array like:
{2, 4, 12, 5, 20, 45, 26, 55, 12, 9}
To visualize how this works: .MMult will return a 2D-array from above example like so:
Then, because we basically give .Index two arrays like {1,2,3,4,5,6,7,8,9,10}, but in a dynamic way, .Index will slice a 1D-array out of this 2D-array:
In this same fashion you can slice out any 1D-array from a 2D-array using .Index as long as you both specify the rows and columns parameter with a valid 1D-array. I hope this will be helpfull to anyone.
这篇关于一维数组乘以一维数组或常量-VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
