组合2D(二维)阵列 [英] Combining 2D (2-dimensional) arrays
问题描述
我正在Excel中使用VBA来使用XML文件并将特定信息转储到各个选项卡中.我希望能够组合二维数组.数组的列数为已知"，而行数为未知".考虑以下两个数组:
I am using VBA in Excel to consume an XML file and dump specific information into individual tabs. I want to be able to combine 2-dimensional arrays. The arrays have a "known" number of columns but an "unknown" number of rows. Consider the following two arrays:
array1:
a b c
d e f
array2:
1 2 3
4 5 6
如果我想要以下结果，如何将它们组合到数组中:
How do I combine these to arrays if I want the following result:
array3:
a b c
d e f
1 2 3
4 5 6
出于好奇，如果我想添加到右侧而不是底部，我将如何编码，像这样:
And just out of curiosity, how would I code if instead I wanted to add to the right instead of the bottom, like this:
array4:
a b c 1 2 3
d e f 4 5 6
我似乎在任何地方都找不到答案.
I can't seem to find the answer to this anywhere.
请记住，我上面的示例很小，但是实际上，我正在尝试一次处理大约100,000行数据.如果需要的话，只有六列数据.
Please keep in mind my example above is rather small, but in reality, I'm trying to do this with approx 100,000 rows of data at once. There are only six columns of data, if that matters.
这里的目标是组装一个大数组，然后一步将其全部写入Excel工作表，因为当我分段执行时，性能确实很差.
The goal here is to assemble a large array and then write it to an Excel sheet all in one step because when I do it in pieces the performance is really poor.
如果可能的话，我宁愿不需要迭代的解决方案.
If possible, I'd prefer a solution that does not require iteration.
我问这两种方法的原因是，实际上我想按顺序添加一种.例如，假设我有四个数组，A，B，C，D.
The reason I ask about both ways is that in reality I want to add kind of sequentially. For instance, assume I have four arrays, A, B, C, D.
首先，添加数组A:
A
然后，添加数组B:
A B
然后，添加数组C:
A B
C
然后，添加数组D:
A B
C D
以此类推...
请记住，上述每个数组的大小都将使其正确地适合"，这意味着A和B的行数相同，但列数不同.另一方面，A和C具有相同的列数但具有不同的行数.等等...
Keep in mind that each of the above arrays would be sized such that they "fit" correctly meaning A and B have the same number of rows, but different number of columns. A and C on the other hand have the same number of columns but a different number of rows. And so on...
我想从下面使用Macro Man的代码添加一个演示.这是他提供的内容(我加了一点，以便读者可以复制/粘贴):
I wanted to add a demonstration using Macro Man's code from below. Here is what he provided (I added a bit so readers can just copy/paste):
Option Explicit
Sub Testing()
Dim Array1(0 To 1, 0 To 2) As String
Array1(0, 0) = "a"
Array1(0, 1) = "b"
Array1(0, 2) = "c"
Array1(1, 0) = "d"
Array1(1, 1) = "e"
Array1(1, 2) = "f"
Dim Array2(0 To 1, 0 To 2) As String
Array2(0, 0) = "1"
Array2(0, 1) = "2"
Array2(0, 2) = "3"
Array2(1, 0) = "4"
Array2(1, 1) = "5"
Array2(1, 2) = "6"
Dim i As Long
For i = 1 To 25000
With Range("A" & Rows.Count).End(xlUp).Offset(IIf(IsEmpty([A1]), 0, 1), 0)
.Resize(UBound(Array1, 1) - LBound(Array1, 1) + 1, _
UBound(Array1, 2) - LBound(Array1, 2) + 1).Value = Array1
End With
With Range("A" & Rows.Count).End(xlUp).Offset(IIf(IsEmpty([A1]), 0, 1), 0)
.Resize(UBound(Array2, 1) - LBound(Array2, 1) + 1, _
UBound(Array2, 2) - LBound(Array2, 2) + 1).Value = Array2
End With
Next i
End Sub
运行上面的代码(每次写入少量数据都会返回到电子表格)时，这需要很长时间才能运行.在我的双Xeon机器上，大约需要25到30秒.
When you run the above code, which goes back to the spreadsheet each time to write the small amount of data, this takes a long time to run. On my dual Xeon machine, like 25-30 seconds.
但是，如果您重写并填充数组FIRST，然后一次写入电子表格，它将在大约一秒钟内运行.
However, if you rewrite and populate the array FIRST, then write to the spreadsheet ONCE, it runs in about one second.
Option Explicit
Sub Testing()
Dim Array1(0 To 99999, 0 To 2) As String
Array1(0, 0) = "a"
Array1(0, 1) = "b"
Array1(0, 2) = "c"
Array1(1, 0) = "d"
Array1(1, 1) = "e"
Array1(1, 2) = "f"
Dim i As Long
For i = 0 To 99999
Array1(i, 0) = "a"
Array1(i, 1) = "b"
Array1(i, 2) = "c"
Next i
With Range("A" & Rows.Count).End(xlUp).Offset(IIf(IsEmpty([A1]), 0, 1), 0)
.Resize(UBound(Array1, 1) - LBound(Array1, 1) + 1, _
UBound(Array1, 2) - LBound(Array1, 2) + 1).Value = Array1
End With
End Sub
除了能够添加数据块"而不是单个项之外，我希望看到一种解决方案，它具有相同的作用.理想情况下，将数组添加到更大的数组中.更好的是，如果父"数组以某种方式动态调整自身大小.
I would like to see a solution which does the same thing, except being able to add "chunks" of data instead of individual items. Adding arrays to bigger arrays, ideally. Even better would be if the "parent" array somehow dynamically resized itself.
约翰·科尔曼(John Coleman)在下面的回答非常有效.
John Coleman's answer below worked great.
我实际上结合了Macro Man和John的test()子例程，这可以动态地调整范围的大小:
I actually combined a bit of Macro Man's with John's test() subroutine and this dynamically re-sizes the range:
Option Explicit
Sub test()
Dim A As Variant, B As Variant
ReDim A(0 To 1, 0 To 1)
ReDim B(0 To 1, 0 To 1)
A(0, 0) = 1
A(0, 1) = 2
A(1, 0) = 3
A(1, 1) = 4
B(0, 0) = 5
B(0, 1) = 6
B(1, 0) = 7
B(1, 1) = 8
Dim Array1 As Variant
Array1 = Combine(A, B)
With Range("A" & Rows.Count).End(xlUp).Offset(IIf(IsEmpty([A1]), 0, 1), 0)
.Resize(UBound(Array1, 1) - LBound(Array1, 1) + 1, _
UBound(Array1, 2) - LBound(Array1, 2) + 1).Value = Array1
End With
End Sub
推荐答案
这是一个VBA函数，可以将两个二维数组组合为一个二维数组.它既可以从VBA中使用，也可以直接在Excel中用作数组公式.在VBA中，迭代是不可避免的，因为该语言没有用于连接数组之类的原语:
Here is a VBA function that can combine two 2-dimensional arrays into a single 2-dimensional array. It can be used either from VBA or as an array-formula directly in Excel. Iteration is unavoidable here in VBA since the language doesn't have primitives for things like concatenating arrays:
Function Combine(A As Variant, B As Variant, Optional stacked As Boolean = True) As Variant
'assumes that A and B are 2-dimensional variant arrays
'if stacked is true then A is placed on top of B
'in this case the number of rows must be the same,
'otherwise they are placed side by side A|B
'in which case the number of columns are the same
'LBound can be anything but is assumed to be
'the same for A and B (in both dimensions)
'False is returned if a clash
Dim lb As Long, m_A As Long, n_A As Long
Dim m_B As Long, n_B As Long
Dim m As Long, n As Long
Dim i As Long, j As Long, k As Long
Dim C As Variant
If TypeName(A) = "Range" Then A = A.Value
If TypeName(B) = "Range" Then B = B.Value
lb = LBound(A, 1)
m_A = UBound(A, 1)
n_A = UBound(A, 2)
m_B = UBound(B, 1)
n_B = UBound(B, 2)
If stacked Then
m = m_A + m_B + 1 - lb
n = n_A
If n_B <> n Then
Combine = False
Exit Function
End If
Else
m = m_A
If m_B <> m Then
Combine = False
Exit Function
End If
n = n_A + n_B + 1 - lb
End If
ReDim C(lb To m, lb To n)
For i = lb To m
For j = lb To n
If stacked Then
If i <= m_A Then
C(i, j) = A(i, j)
Else
C(i, j) = B(lb + i - m_A - 1, j)
End If
Else
If j <= n_A Then
C(i, j) = A(i, j)
Else
C(i, j) = B(i, lb + j - n_A - 1)
End If
End If
Next j
Next i
Combine = C
End Function
我以4种不同的方式对其进行了测试.首先，我在电子表格中输入了两个示例数组，并直接在excel中将 Combine 用作数组公式:
I tested it in 4 different ways. First I entered your two example arrays in the spreadsheets and used Combine directly in excel as an array formula:
A7:C10包含数组公式
Here A7:C10 contains the array formula
{=combine(A1:C2,A4:C5)}
和A12:F13包含数组公式
and A12:F13 contains the array formula
{=combine(A1:C2,A4:C5,FALSE)}
然后，我运行了以下子程序:
Then, I ran the following sub:
Sub test()
Dim A As Variant, B As Variant
ReDim A(0 To 1, 0 To 1)
ReDim B(0 To 1, 0 To 1)
A(0, 0) = 1
A(0, 1) = 2
A(1, 0) = 3
A(1, 1) = 4
B(0, 0) = 5
B(0, 1) = 6
B(1, 0) = 7
B(1, 1) = 8
Range("A15:B18").Value = Combine(A, B)
Range("C15:F16").Value = Combine(A, B, False)
End Sub
输出:
这篇关于组合2D(二维)阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
