yjh_27 发表于 2013-11-25 11:27
参见附件。
二维数组排序的精髓只是对其记录的位置进行排序,排序过程中原数组都没有动,可以称之为“虚拟排序”吧。从一列拆分为三列进行排序,再由三列合并为一列,有点绕。- Sub test()
- Dim a(), w() As Long
- r = Range("a2", [a65536].End(3))
- ReDim a(1 To UBound(r), 1 To 3)
- With CreateObject("VBSCRIPT.REGEXP")
- .Pattern = "(\D+)(\d+)(\D+)(\d+)"
- For i = 1 To UBound(r)
- t = Split(.Replace(r(i, 1), "$1 $2 $4"))
- a(i, 1) = t(0)
- a(i, 2) = Val(t(1))
- a(i, 3) = Val(t(2))
- Next
- End With
- ZSort a, w, 1, 0, 2, 0, 3
- ReDim a(1 To UBound(r), 1 To 1)
- For i = 1 To UBound(r)
- a(i, 1) = r(w(i), 1)
- Next
- [c2].Resize(UBound(r)) = a
- End Sub
- Public Sub ZSort(Olda(), w() As Long, ParamArray c())
- 'Olda():为排序数组
- 'Newa():为存放位置数组
- 'ParamArray c():传递排序参数数组,奇数个为排序列号,偶数为升降序,0或者省略为升序
- Dim P() As Long, B() As Boolean
- Dim i&, j&, k&, n1&, n2&, nb&, ne&
- k = UBound(c)
- If k = -1 Then
- ReDim P(1)
- P(0) = 1
- Else
- If k Mod 2 Then ReDim P(k) Else ReDim P(k + 1)
- For i = 0 To k
- P(i) = c(i)
- Next
- End If
- n1 = LBound(Olda)
- n2 = UBound(Olda)
- ReDim w(n1 To n2)
- ReDim B(n1 To n2)
- For i = n1 To n2
- w(i) = i
- Next
- If P(1) = 0 Then QSort Olda, w, P(0), n1, n2 Else QSort2 Olda, w, P(0), n1, n2
- For i = 2 To k Step 2
- nb = n1
- ne = n1
- While ne < n2
- Do
- ne = ne + 1
- If ne > n2 Then Exit Do
- Loop Until B(ne) Or Olda(w(ne), P(i - 2)) <> Olda(w(ne - 1), P(i - 2))
- If ne - nb > 1 Then
- If P(i + 1) = 0 Then QSort Olda, w, P(i), nb, ne - 1 Else QSort2 Olda, w, P(i), nb, ne - 1
- End If
- If ne <= n2 Then B(ne) = True
- nb = ne
- Wend
- Next
- End Sub
- Private Sub QSort(r(), w() As Long, Key&, L&, H&)
- Dim i&, j&, x, y
- i = L
- j = H
- x = r(w(L + 1 + Int((H - L - 1) * Rnd)), Key)
- While (i <= j)
- While (r(w(i), Key) < x And i < H)
- i = i + 1
- Wend
- While (x < r(w(j), Key) And j > L)
- j = j - 1
- Wend
- If (i <= j) Then
- y = w(i)
- w(i) = w(j)
- w(j) = y
- i = i + 1
- j = j - 1
- End If
- Wend
- If (L < j) Then QSort r, w, Key, L, j
- If (i < H) Then QSort r, w, Key, i, H
- End Sub
- Private Sub QSort2(r(), w() As Long, Key&, L&, H&)
- Dim i&, j&, x, y
- i = L
- j = H
- x = r(w(L + 1 + Int((H - L - 1) * Rnd)), Key)
- While (i <= j)
- While (r(w(i), Key) > x And i < H)
- i = i + 1
- Wend
- While (x > r(w(j), Key) And j > L)
- j = j - 1
- Wend
- If (i <= j) Then
- y = w(i)
- w(i) = w(j)
- w(j) = y
- i = i + 1
- j = j - 1
- End If
- Wend
- If (L < j) Then QSort2 r, w, Key, L, j
- If (i < H) Then QSort2 r, w, Key, i, H
- End Sub
复制代码 |