|
本帖最后由 cxloen 于 2013-8-11 10:48 编辑
在不增加辅助列的情况下,如何把A例数据排序成c列的顺序(用技巧法最好),或者VBA形式
- Sub Test() 'by kagawa
- Static k
- arr = [a1].CurrentRegion
- m = UBound(arr)
- For i = 1 To m
- If Len(arr(i, 1)) > n Then n = Len(arr(i, 1))
- Next
-
- ReDim brr(1 To m, 1 To 2)
- For i = 1 To m
- brr(i, 1) = arr(i, 1)
- brr(i, 2) = Reform(arr(i, 1), n - 1)
- Next
-
- Call ShellSort5(brr, 2, 1)
-
- [e1].CurrentRegion = ""
- If k Then k = 0 Else k = 1
- [e1].Resize(m, k + 1) = brr '此处选择仅输出排序结果1列,或连同辅助列的2列一起输出。
-
- End Sub
- Function Reform(txt, Optional n = 4)
- With CreateObject("VBScript.RegExp")
- .Global = True
- .Pattern = "\d"
- t1 = .Replace(txt, "")
- .Pattern = "\D"
- t2 = .Replace(txt, "")
- End With
- Reform = t1 & Right(String(n, "0") & t2, n)
- End Function
- Sub ShellSort5(trr, x, y) 'n=5
- Dim h&, i&, j&, l&, u&, t1, t2
-
- l = LBound(trr): u = UBound(trr): h = u
- Do
- h = (h \ 5) * 2 + 1
- For i = l + h To u
- t1 = trr(i, x): t2 = trr(i, y)
- For j = i - h To l Step -h
- If trr(j, x) < t1 Then Exit For
- trr(j + h, x) = trr(j, x): trr(j + h, y) = trr(j, y)
- Next
- trr(j + h, x) = t1: trr(j + h, y) = t2
- Next
- Loop Until h = 1
- End Sub
复制代码
|
|