[已解决]一维数组中怎样排序

爱疯 · 发表于 2010-2-10 20:29

不看不看老师的讲解《冒泡排序法原理》：http://www.excelpx.com/forum.php?mod=viewthread&tid=69568

单元格中排序，我们可以去看帮助，学用Range.Sort 方法。但如果必须在一维数组里排序的话，除此一路，还有没有密道呀？

PS：也许告诉我我也不懂，但还是想问问，满足下好奇心，见见好玩的[em11]

最佳答案

月排行榜 / 总排行榜

amulee

2010-2-11 08:30

想起了我的那堂试讲课。代码全给你，自己去试试看。

Sub 系统排序()
    Dim MyRng As Range
    Set MyRng = Range("A9:A21")
    MyRng.Sort Key1:=MyRng.Cells(1, 1), Order1:=xlAscending, Header:=xlYes, _
        MatchCase:=False, Orientation:=xlSortColumns
End Sub
Sub 随机数据()
    Dim i%
    Randomize
    For i = 10 To 21
        Cells(i, 1) = Int(Rnd * 1000) + 1000
    Next i
End Sub
Sub 冒泡排序()
    Dim i As Integer, Temp As Variant
    Dim arr() As Variant
    Dim OK As Boolean
    arr = Application.Transpose(Range("A10:A21"))
    Do
        OK = True
        For i = UBound(arr) To 2 Step -1
            If arr(i - 1) > arr(i) Then
                Temp = arr(i - 1)
                arr(i - 1) = arr(i)
                arr(i) = Temp
                OK = False
            End If
        Next i
    Loop Until OK
    Range("A10:A21") = Application.Transpose(arr)
End Sub
Sub 选择排序()
    Dim i As Integer, j As Integer
    Dim min As Integer, Temp As Variant
    Dim arr() As Variant
    arr = Application.Transpose(Range("A10:A21"))
    For i = 1 To UBound(arr) - 1
        min = i
        For j = i + 1 To UBound(arr)
            If arr(j) < arr(min) Then min = j
        Next j
        If min <> i Then
            Temp = arr(min)
            arr(min) = arr(i)
            arr(i) = Temp
        End If
    Next i
    Range("A10:A21") = Application.Transpose(arr)
End Sub
Sub 计数排序()
    Dim counts() As Variant, i As Integer, j As Integer
    Dim next_index As Integer
    Dim min_value As Integer, max_value As Integer
    Dim arr() As Variant
    arr = Application.Transpose(Range("A10:A21"))
    min_value = WorksheetFunction.min(arr)
    max_value = WorksheetFunction.max(arr)
    ReDim counts(min_value To max_value)
    For i = 1 To UBound(arr)
        counts(arr(i)) = counts(arr(i)) + 1
    Next i
    next_index = 1
    For i = min_value To max_value
        For j = 1 To counts(i)
            arr(next_index) = i
            next_index = next_index + 1
        Next j
    Next i
    Range("A10:A21") = Application.Transpose(arr)
End Sub
Sub 插入排序()
    Dim i As Integer, j As Integer, Temp As Variant
    Dim arr() As Variant
    arr = Application.Transpose(Range("A10:A21"))
    For i = 1 To UBound(arr)
        Temp = arr(i)
        j = i - 1
        Do
            If j < 1 Then Exit Do
            If arr(j) <= Temp Then Exit Do
            arr(j + 1) = arr(j)
            j = j - 1
        Loop
        If j <> (i - 1) Then arr(j + 1) = Temp
    Next i
    Range("A10:A21") = Application.Transpose(arr)
End Sub
Sub 希尔排序()
    Dim i As Integer, j As Integer
    Dim inc As Integer, Temp As Variant
    Dim arr() As Variant
    arr = Application.Transpose(Range("A10:A21"))
    inc = 1
    Do While inc <= UBound(arr) - 1
        inc = 3 * inc + 1
    Loop
    Do While inc > 1
        inc = inc \ 3
        For i = 1 + inc To UBound(arr)
            Temp = arr(i)
            j = i - inc
            Do
                If j < inc Then Exit Do
                If arr(j) <= Temp Then Exit Do
                arr(j + inc) = arr(j)
                j = j - inc
            Loop
            If j <> (i - inc) Then arr(j + inc) = Temp
        Next
    Loop
    Range("A10:A21") = Application.Transpose(arr)
End Sub
Sub 快速排序(ByRef InputArray As Variant, ByVal lb As Long, ByVal ub As Long)
    Dim Temp As Variant, hi As Integer, low As Integer, i As Integer
    If lb >= ub Then Exit Sub
    i = Int((ub + lb) / 2)
    Temp = InputArray(i)
    InputArray(i) = InputArray(lb)
    lo = lb
    hi = ub
    Do
        Do While InputArray(hi) >= Temp
            hi = hi - 1
            If hi <= lo Then Exit Do
        Loop
        If hi <= lo Then
            InputArray(lo) = Temp
            Exit Do
        End If
        InputArray(lo) = InputArray(hi)
        lo = lo + 1
        Do While InputArray(lo) < Temp
            lo = lo + 1
            If lo >= hi Then Exit Do
        Loop
        If lo >= hi Then
            lo = hi
            InputArray(hi) = Temp
            Exit Do
        End If
        InputArray(hi) = InputArray(lo)
    Loop
    快速排序 InputArray, lb, lo - 1
    快速排序 InputArray, lo + 1, ub
End Sub
Sub 快速排序主程序()
    Dim arr() As Variant
    arr = Application.Transpose(Range("A10:A21"))
    快速排序 arr, 1, UBound(arr)
    Range("A10:A21") = Application.Transpose(arr)
End Sub

跳转到最佳答案楼层

小线 · 发表于 2010-2-10 20:36

貌似冒泡是最佳选择。。其他的我也不知道

领风 · 发表于 2010-2-10 21:34

MS还有一种叫选择比较法~~

青城山苦丁茶 · 发表于 2010-2-10 21:38

这里有几种排序：http://excelpx.com/dispbbs.asp?BoardID=5&replyID=195262&ID=32276&skin=1

吕?布 · 发表于 2010-2-10 21:50

快速排序比较快

青城山苦丁茶 · 发表于 2010-2-10 21:54

对某个样本最适宜的算法对另一个样本就不一定了，每种排序算法都有其优点。

amulee · 发表于 2010-2-11 08:30

这个最佳答案由 amulee 给出，感谢 amulee 的回答。

单击隐藏图章

想起了我的那堂试讲课。代码全给你，自己去试试看。

Sub 系统排序()
    Dim MyRng As Range
    Set MyRng = Range("A9:A21")
    MyRng.Sort Key1:=MyRng.Cells(1, 1), Order1:=xlAscending, Header:=xlYes, _
        MatchCase:=False, Orientation:=xlSortColumns
End Sub
Sub 随机数据()
    Dim i%
    Randomize
    For i = 10 To 21
        Cells(i, 1) = Int(Rnd * 1000) + 1000
    Next i
End Sub
Sub 冒泡排序()
    Dim i As Integer, Temp As Variant
    Dim arr() As Variant
    Dim OK As Boolean
    arr = Application.Transpose(Range("A10:A21"))
    Do
        OK = True
        For i = UBound(arr) To 2 Step -1
            If arr(i - 1) > arr(i) Then
                Temp = arr(i - 1)
                arr(i - 1) = arr(i)
                arr(i) = Temp
                OK = False
            End If
        Next i
    Loop Until OK
    Range("A10:A21") = Application.Transpose(arr)
End Sub
Sub 选择排序()
    Dim i As Integer, j As Integer
    Dim min As Integer, Temp As Variant
    Dim arr() As Variant
    arr = Application.Transpose(Range("A10:A21"))
    For i = 1 To UBound(arr) - 1
        min = i
        For j = i + 1 To UBound(arr)
            If arr(j) < arr(min) Then min = j
        Next j
        If min <> i Then
            Temp = arr(min)
            arr(min) = arr(i)
            arr(i) = Temp
        End If
    Next i
    Range("A10:A21") = Application.Transpose(arr)
End Sub
Sub 计数排序()
    Dim counts() As Variant, i As Integer, j As Integer
    Dim next_index As Integer
    Dim min_value As Integer, max_value As Integer
    Dim arr() As Variant
    arr = Application.Transpose(Range("A10:A21"))
    min_value = WorksheetFunction.min(arr)
    max_value = WorksheetFunction.max(arr)
    ReDim counts(min_value To max_value)
    For i = 1 To UBound(arr)
        counts(arr(i)) = counts(arr(i)) + 1
    Next i
    next_index = 1
    For i = min_value To max_value
        For j = 1 To counts(i)
            arr(next_index) = i
            next_index = next_index + 1
        Next j
    Next i
    Range("A10:A21") = Application.Transpose(arr)
End Sub
Sub 插入排序()
    Dim i As Integer, j As Integer, Temp As Variant
    Dim arr() As Variant
    arr = Application.Transpose(Range("A10:A21"))
    For i = 1 To UBound(arr)
        Temp = arr(i)
        j = i - 1
        Do
            If j < 1 Then Exit Do
            If arr(j) <= Temp Then Exit Do
            arr(j + 1) = arr(j)
            j = j - 1
        Loop
        If j <> (i - 1) Then arr(j + 1) = Temp
    Next i
    Range("A10:A21") = Application.Transpose(arr)
End Sub
Sub 希尔排序()
    Dim i As Integer, j As Integer
    Dim inc As Integer, Temp As Variant
    Dim arr() As Variant
    arr = Application.Transpose(Range("A10:A21"))
    inc = 1
    Do While inc <= UBound(arr) - 1
        inc = 3 * inc + 1
    Loop
    Do While inc > 1
        inc = inc \ 3
        For i = 1 + inc To UBound(arr)
            Temp = arr(i)
            j = i - inc
            Do
                If j < inc Then Exit Do
                If arr(j) <= Temp Then Exit Do
                arr(j + inc) = arr(j)
                j = j - inc
            Loop
            If j <> (i - inc) Then arr(j + inc) = Temp
        Next
    Loop
    Range("A10:A21") = Application.Transpose(arr)
End Sub
Sub 快速排序(ByRef InputArray As Variant, ByVal lb As Long, ByVal ub As Long)
    Dim Temp As Variant, hi As Integer, low As Integer, i As Integer
    If lb >= ub Then Exit Sub
    i = Int((ub + lb) / 2)
    Temp = InputArray(i)
    InputArray(i) = InputArray(lb)
    lo = lb
    hi = ub
    Do
        Do While InputArray(hi) >= Temp
            hi = hi - 1
            If hi <= lo Then Exit Do
        Loop
        If hi <= lo Then
            InputArray(lo) = Temp
            Exit Do
        End If
        InputArray(lo) = InputArray(hi)
        lo = lo + 1
        Do While InputArray(lo) < Temp
            lo = lo + 1
            If lo >= hi Then Exit Do
        Loop
        If lo >= hi Then
            lo = hi
            InputArray(hi) = Temp
            Exit Do
        End If
        InputArray(hi) = InputArray(lo)
    Loop
    快速排序 InputArray, lb, lo - 1
    快速排序 InputArray, lo + 1, ub
End Sub
Sub 快速排序主程序()
    Dim arr() As Variant
    arr = Application.Transpose(Range("A10:A21"))
    快速排序 arr, 1, UBound(arr)
    Range("A10:A21") = Application.Transpose(arr)
End Sub

xdwy81129 · 发表于 2010-4-9 18:07

QUOTE:

以下是引用amulee在2010-2-11 8:30:00的发言：

想起了我的那堂试讲课。代码全给你，自己去试试看。

Sub 系统排序()
    Dim MyRng As Range
    Set MyRng = Range("A9:A21")
    MyRng.Sort Key1:=MyRng.Cells(1, 1), Order1:=xlAscending, Header:=xlYes, _
        MatchCase:=False, Orientation:=xlSortColumns
End
  Sub
Sub 随机数据()
    Dim i%
    Randomize
    For i = 10 To 21
        Cells(i, 1) = Int(Rnd * 1000) + 1000
    Next i
End
  Sub
Sub 冒泡排序()
    Dim i As
  Integer, Temp As
  Variant
    Dim arr() As
  Variant
    Dim OK As
  Boolean
    arr = Application.Transpose(Range("A10:A21"))
    Do
        OK = True
        For i = UBound(arr) To 2 Step -1
            If arr(i - 1) > arr(i) Then
                Temp = arr(i - 1)
                arr(i - 1) = arr(i)
                arr(i) = Temp
                OK = False
            End
  If
        Next i
    Loop
  Until OK
    Range("A10:A21") = Application.Transpose(arr)
End
  Sub
Sub 选择排序()
    Dim i As
  Integer, j As
  Integer
    Dim min As
  Integer, Temp As
  Variant
    Dim arr() As
  Variant
    arr = Application.Transpose(Range("A10:A21"))
    For i = 1 To
  UBound(arr) - 1
        min = i
        For j = i + 1 To
  UBound(arr)
            If arr(j) < arr(min) Then min = j
        Next j
        If min <> i Then
            Temp = arr(min)
            arr(min) = arr(i)
            arr(i) = Temp
        End
  If
    Next i
    Range("A10:A21") = Application.Transpose(arr)
End
  Sub
Sub 计数排序()
    Dim counts() As
  Variant, i As
  Integer, j As
  Integer
    Dim next_index As
  Integer
    Dim min_value As
  Integer, max_value As
  Integer
    Dim arr() As
  Variant
    arr = Application.Transpose(Range("A10:A21"))
    min_value = WorksheetFunction.min(arr)
    max_value = WorksheetFunction.max(arr)
    ReDim counts(min_value To max_value)
    For i = 1 To
  UBound(arr)
        counts(arr(i)) = counts(arr(i)) + 1
    Next i
    next_index = 1
    For i = min_value To max_value
        For j = 1 To counts(i)
            arr(next_index) = i
            next_index = next_index + 1
        Next j
    Next i
    Range("A10:A21") = Application.Transpose(arr)
End
  Sub
Sub 插入排序()
    Dim i As
  Integer, j As
  Integer, Temp As
  Variant
    Dim arr() As
  Variant
    arr = Application.Transpose(Range("A10:A21"))
    For i = 1 To
  UBound(arr)
        Temp = arr(i)
        j = i - 1
        Do
            If j < 1 Then
  Exit
  Do
            If arr(j) <= Temp Then
  Exit
  Do
            arr(j + 1) = arr(j)
            j = j - 1
        Loop
        If j <> (i - 1) Then arr(j + 1) = Temp
    Next i
    Range("A10:A21") = Application.Transpose(arr)
End
  Sub
Sub 希尔排序()
    Dim i As
  Integer, j As
  Integer
    Dim inc As
  Integer, Temp As
  Variant
    Dim arr() As
  Variant
    arr = Application.Transpose(Range("A10:A21"))
    inc = 1
    Do
  While inc <= UBound(arr) - 1
        inc = 3 * inc + 1
    Loop
    Do
  While inc > 1
        inc = inc \ 3
        For i = 1 + inc To
  UBound(arr)
            Temp = arr(i)
            j = i - inc
            Do
                If j < inc Then
  Exit
  Do
                If arr(j) <= Temp Then
  Exit
  Do
                arr(j + inc) = arr(j)
                j = j - inc
            Loop
            If j <> (i - inc) Then arr(j + inc) = Temp
        Next
    Loop
    Range("A10:A21") = Application.Transpose(arr)
End
  Sub
Sub 快速排序(ByRef InputArray As
  Variant, ByVal lb As
  Long, ByVal ub As
  Long)
    Dim Temp As
  Variant, hi As
  Integer, low As
  Integer, i As
  Integer
    If lb >= ub Then
  Exit
  Sub
    i = Int((ub + lb) / 2)
    Temp = InputArray(i)
    InputArray(i) = InputArray(lb)
    lo = lb
    hi = ub
    Do
        Do
  While InputArray(hi) >= Temp
            hi = hi - 1
            If hi <= lo Then
  Exit
  Do
        Loop
        If hi <= lo Then
            InputArray(lo) = Temp
            Exit
  Do
        End
  If
        InputArray(lo) = InputArray(hi)
        lo = lo + 1
        Do
  While InputArray(lo) < Temp
            lo = lo + 1
            If lo >= hi Then
  Exit
  Do
        Loop
        If lo >= hi Then
            lo = hi
            InputArray(hi) = Temp
            Exit
  Do
        End
  If
        InputArray(hi) = InputArray(lo)
    Loop
    快速排序 InputArray, lb, lo - 1
    快速排序 InputArray, lo + 1, ub
End
  Sub
Sub 快速排序主程序()
    Dim arr() As
  Variant
    arr = Application.Transpose(Range("A10:A21"))
    快速排序 arr, 1, UBound(arr)
    Range("A10:A21") = Application.Transpose(arr)
End
  Sub

哇，n繁杂。楼主和我的问题差不多

开辆小富康 · 发表于 2010-4-9 19:01

哈。阿木老师一下子全抖出来了

爱疯 · 发表于 2010-4-9 19:44

谢谢阿木

收着

[em04]

		自动登录	找回密码
密码			注册