|
精简了一下无用的判断,速度又提升25%
- Sub 双表合并()
- 'On Error Resume Next
- t = Timer
- Set d_left = CreateObject("scripting.dictionary") '左侧表头
- Set d_top = CreateObject("scripting.dictionary") '上部表头
- Set d = CreateObject("scripting.dictionary") '数据字典
- arr_1 = Sheets("第1张表").[A1].CurrentRegion
- arr_2 = Sheets("第2张表").[A1].CurrentRegion
- '''左侧表头装入字典
- For i = 1 To UBound(arr_1)
- d_left(arr_1(i, 1)) = ""
- Next i
- For i = 1 To UBound(arr_2)
- d_left(arr_2(i, 1)) = ""
- Next i
- '''头部表头装入字典
- For j = 1 To UBound(arr_1, 2)
- d_top(arr_1(1, j)) = ""
- Next j
- For j = 1 To UBound(arr_2, 2)
- d_top(arr_2(1, j)) = ""
- Next j
- ''数据部分装入到字典
- For i = 2 To UBound(arr_1)
- For j = 2 To UBound(arr_1, 2)
- d(arr_1(i, 1) & "-" & arr_1(1, j)) = arr_1(i, j)
- Next j
- Next i
- For i = 2 To UBound(arr_2)
- For j = 2 To UBound(arr_2, 2)
- d(arr_2(i, 1) & "-" & arr_2(1, j)) = arr_2(i, j)
- Next j
- Next i
- ''输出到数组brr
- ReDim brr(1 To d_left.Count, 1 To d_top.Count)
- For i = 1 To UBound(brr)
- For j = 1 To UBound(brr, 2)
- If i = 1 Then brr(i, j) = d_top.Keys()(j - 1)
- If j = 1 Then brr(i, j) = d_left.Keys()(i - 1)
- If i > 1 And j > 1 Then
- brr(i, j) = d(brr(i, 1) & "-" & brr(1, j))
- End If
- Next j
- Next i
- With Sheets("合并后结果")
- .Cells.Clear
- .Range(Cells(1, 1), Cells(UBound(brr), UBound(brr, 2))) = brr
- End With
- MsgBox "运行完毕,用时" & Timer - t
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|