|
附件稍等
- Option Explicit
- Sub samesum()
- Dim i As Long, n As Long, arr(), j As Long, y As Long
- Dim d, ds, k, t As Single
- t = Timer
- Worksheets(2).UsedRange.Offset(1).ClearContents
- Set d = CreateObject("Scripting.Dictionary")
- Set ds = CreateObject("Scripting.Dictionary")
- n = Worksheets(1).[b65536].End(xlUp).Row
- arr = Worksheets(1).Range("b2:c" & n).Value
- For i = 1 To UBound(arr)
- d(arr(i, 1) & arr(i, 2)) = d(arr(i, 1) & arr(i, 2)) + 1
- ds(arr(i, 1)) = ds(arr(i, 1)) + 1
- Next
- k = ds.keys
- Worksheets(2).[a2].Resize(ds.Count, 1) = Application.Transpose(k)
- Erase k
- Erase arr
- n = Worksheets(2).[a65536].End(xlUp).Row
- y = Worksheets(2).[iv1].End(xlToLeft).Column
- arr = Worksheets(2).Range(Worksheets(2).Cells(1, 1), Worksheets(2).Cells(n, y)).Value
- For i = 1 To UBound(arr, 1) - 1
- For j = 1 To UBound(arr, 2) - 1
- arr(i + 1, j + 1) = d(arr(i + 1, 1) & arr(1, j + 1))
- Next
- Next
- Worksheets(2).[a1].Resize(UBound(arr, 1), UBound(arr, 2)) = arr
- Erase arr
- Set d = Nothing
- Set ds = Nothing
- MsgBox Timer - t & "秒"
- End Sub
复制代码 |
|