我正在嘗試創建兩組陣列 arr 和 arr_two。我在 arr 中生成亂數，它有 2000 行和 20 列。作為第二步，我想將 arr 中的前兩列相乘并將其捕獲為 arr_two 中的第一列，將前三列相乘并將其捕獲在 arr_two 的第二列中，而 arr_two 的第 19 列將是arr 的所有 20 列。我想知道如何去創建這個 arr_two。我創建了 arr 如下：
Sub arraytest()
Dim arr(2000, 20) As Variant
Dim arr_two(2000, 20) As Variant
For i = 0 To 2000
For j = 0 To 20
arr(i, j) = WorksheetFunction.Sum(WorksheetFunction.Norm_Inv(Rnd(), 0, 1) / 100, 1)
Next j
Next i
End Sub
uj5u.com熱心網友回復：
將陣列列相乘
Sub ArrayTest()
Dim tT As Double: tT = Timer
Const rCount As Long = 2000 ' the same
Const scCount As Long = 20 ' different
' Populate the source array ('sData').
Dim sData() As Double: ReDim sData(1 To rCount, 1 To scCount)
Dim r As Long
Dim sc As Long
For r = 1 To rCount ' by rows
For sc = 1 To scCount
With WorksheetFunction
On Error Resume Next ' 'Norm_Inv' may raise an error
sData(r, sc) = .Sum(.Norm_Inv(Rnd(), 0, 1) / 100, 1)
On Error GoTo 0
End With
Next sc
Next r
Dim sT As Double: sT = Timer - tT
' Populate the destination array ('dData').
Dim dcCount As Long: dcCount = scCount - 1 ' one less column
Dim dData() As Double: ReDim dData(1 To rCount, 1 To dcCount)
Dim nsc As Long ' next
Dim dc As Long ' current
Dim pdc As Long ' previous
' First column:
For r = 1 To rCount ' current source * next source
dData(r, 1) = sData(r, 1) * sData(r, 2)
Next r
' Remainder of columns:
For dc = 2 To dcCount ' by columns
nsc = dc 1
pdc = dc - 1
For r = 1 To rCount ' previous destination * next source
dData(r, dc) = dData(r, pdc) * sData(r, nsc)
Next r
Next dc
tT = Timer - tT
Debug.Print "Source time = " & sT
Debug.Print "Destination time = " & tT - sT
Debug.Print "Total time = " & tT
' Write to a worksheet.
' With Sheet1
' .UsedRange.Clear
' With .Range("A1").Resize(, dcCount)
' .Value = Application.Transpose( _
' .Worksheet.Evaluate("""Col ""&ROW(1:" & dcCount & ")"))
' .Offset(1).Resize(rCount).Value = dData
' .EntireColumn.AutoFit
' End With
' End With
End Sub
結果
Source Time = 0,4609375
Destination Time = 0,01513671875
Total Time = 0,47607421875
200k 行的結果顯示效率更高：
Source time = 48,181884765625
Destination time = 0,3291015625
Total time = 48,510986328125
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/514484.html
標籤：数组擅长vba
下一篇：您如何參考VBA中的作業表串列？