|
想把大量数据合并整理,涉及到重复合并的一些问题,详情见附件,请老师们得空指点一二!
速度还OK,我的配置的话,0.2S左右
- Sub juSTTest()
- Dim Arr, D As New Dictionary, At(1 To 2) As New Dictionary, K&, ArrT(1 To 10000, 1 To 5)
- Dim i&, S$, Ar, j&, Akd, Ak, Ai, ASp, a As Byte, AStr(1 To 2) As String, t
- t = Timer
- Arr = Range("a1:e" & Cells(Rows.Count, 1).End(3).Row).Value
- For i = 1 To UBound(Arr)
- S = Arr(i, 1) & vbTab & Arr(i, 4) & vbTab & Arr(i, 5)
- If D.Exists(S) Then
- Ar = D(S)
- If Ar(1).Exists(Arr(i, 2)) Then
- Ar(1)(Arr(i, 2)) = Ar(1)(Arr(i, 2)) + 1
- Else
- Ar(1).Add Arr(i, 2), 1
- End If
- If At(2).Exists(Arr(i, 3)) Then
- Ar(2)(Arr(i, 3)) = Ar(2)(Arr(i, 3)) + 1
- Else
- Ar(2).Add Arr(i, 3), 1
- End If
- D(S) = Ar
- Else
- Erase At
- At(1).Add Arr(i, 2), 1
- At(2).Add Arr(i, 3), 1
- D.Add S, At
- End If
- Next
- Akd = D.Keys
- For i = 0 To UBound(Akd)
- Ar = D(Akd(i))
- For a = 1 To 2
- Ak = Ar(a).Keys: Ai = Ar(a).Items
- For j = 0 To UBound(Ak)
- AStr(a) = AStr(a) & ";" & Ak(j) & "(" & Ai(j) & ")"
- Next j
- Next a
- K = K + 1: ASp = Split(Akd(i), vbTab)
- ArrT(K, 1) = ASp(0): ArrT(K, 4) = ASp(1): ArrT(K, 5) = ASp(2)
- ArrT(K, 2) = Mid(AStr(1), 2)
- ArrT(K, 3) = Replace(Mid(AStr(2), 2), ";", "_")
- Next i
- With Sheet2
- .Range("a2:e" & .Rows.Count).ClearContents
- .Range("A2").Resize(K, 5) = ArrT
- .Activate
- End With
- MsgBox "处理完毕,用时" & Timer - t
- Set D = Nothing
- End Sub
复制代码
book2.rar
(19.15 KB, 下载次数: 87)
|
|