|
- Sub 多列多条件汇总()
- Range("a2:f65536").ClearContents
- Application.ScreenUpdating = False
- Dim dc As Object, arr, i&, j%, s&, w, k%, tmp
- Set dc = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1:f" & Sheet1.Range("a65536").End(3).Row)
- ReDim brr(UBound(arr) - 1, 1 To 6)
- For i = 1 To UBound(arr, 2)
- brr(0, i) = arr(1, i)
- Next
- For i = 2 To UBound(arr)
- w = dc(arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3) & "," & arr(i, 4))
- If w = "" Then
- s = s + 1
- For j = 1 To 6
- brr(s, j) = arr(i, j)
- Next
- dc(arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3) & "," & arr(i, 4)) = s
- w = s
- Else
- For j = 5 To 6
- brr(w, j) = brr(w, j) + arr(i, j)
- Next
- End If
- Next
- ReDim tmp(1 To UBound(brr, 2))
- For i = 1 To s
- For j = 1 To s - i + 1
- If brr(j, 1) & "|" & brr(j, 2) & "|" & brr(j, 3) & "|" & brr(j, 4) > brr(j + 1, 1) & "|" & brr(j + 1, 2) & "|" & brr(j + 1, 3) & "|" & brr(j + 1, 4) Then
- For k = 1 To UBound(brr, 2)
- tmp(k) = brr(j, k)
- brr(j, k) = brr(j + 1, k)
- brr(j + 1, k) = tmp(k)
- Next
- End If
- Next
- Next
- [a1].Resize(s + 1, 6) = brr
- Application.ScreenUpdating = True
- End Sub
复制代码 中文的排序好像效果不怎么好。。。我测试了下,"上海"<"北京" |
评分
-
查看全部评分
|