[µo°Ý] ¦p¦ó±NARR¸ê®ÆÂà¤JBÄ檺ÅçÃÒ²M³æ

dou10801

¦p¦ó±N¥h­«ªºARR¸ê®ÆÂà¤JBÄ檺ÅçÃÒ²M³æ¤¤,·P®¦.

Scott090

¦^´_ 15# singo1232001

·P®¦ ¦A¦¸²`¤J¦a»¡©ú

singo1232001

¦^´_ 14# Scott090

¤]¦³½Õ¥ÎSQLªº¼gªk   ¦¹ºØ¼gªk­n³Ð¤@±i¦W¬°"sortSQL"ªº¤u§@ªí,ªí¥iÁôÂà ÀÉ®×­n¦³ÀɦW
Sub SortArray()
arr = Array("a", "b", "c", "d", "e", "C", "b")
orderSQL arr
Debug.Print Join(arr, ",")
End Sub

Function orderSQL(ar)
With CreateObject("adodb.connection"): V = Application.Version
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; "
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; "
.Open V & "Data Source=" & ThisWorkbook.FullName
Set s = Sheets("sortSQL"): s.[A:A].ClearContents: s.[a1] = "sort"
s.[a2].Resize(UBound(ar) + 1, 1) = Application.Transpose(ar)
q = "select * from [sortSQL$A:A] order by sort"
ar = Application.Index(.Execute(q).getrows, 1, 0): End With
End Function

Scott090

¦^´_ 13# Andy2483

¤Óºëªö¤F¤]¾Ç²ß¤F
ÁÂÁÂ

Andy2483

¦^´_ 11# singo1232001


    ÁÂÁ«e½ú«ü¾É
«á¾ÇÂǦ¹©«²ß±o°}¦C±Æ§Ç¬ÛÃöª¾ÃÑ,½Ð«e½ú¦A«ü¾É

°õ¦æµ²ªG:

¤U¸ü (7.74 KB)

2022-12-26 13:42

µ{¦¡½X¤ß±oµù¸Ñ¦p¤U:

Option Explicit
Sub SortArray()
Dim Arr, Y, i
'¡ô«Å§iÅܼÆ:Arr,Y,i¬O³q¥ÎÅܼÆ
Arr = Array("4A", "5A", "6A", "7A", "2A", "4A", "6A", "1A", "2A", "3A")
'¡ô¥OArr¬O¤@ºû°}¦C
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY¬O ¦r¨å
For Each i In Arr
'¡ô³]¶¶°j°é!¥Oi¬O Arr°}¦C¸Ìªº¤@­û,±q«e­±½ü¨ì«á­±
   Y(i) = ""
   '¡ô¥O¥HiÅܼƭȬ°key,item¬OªÅ¦r¤¸,©ñ¤JY¦r¨å¤¤
Next
Arr = Y.KEYS()
'¡ô¥OArr­Ë½Õ­ì¨Ó°}¦C­È,´«¸ËY¦r¨åkey²Õ¦¨ªº°}¦C­È
QuickSort Arr, LBound(Arr), UBound(Arr)
'¡ôµ¹QuickSort³o¦Û­q¨ç¼Æ3­Ó ÅܼƥO¨ä¦^¶Ç­È
'1.arÅܼÆ: Arr°}¦C
'2.iLo&: LBound(Arr):Arr°}¦C³Ì¤p¯Á¤Þ¸¹¼Æ
'3.iHi&: UBound(Arr):Arr°}¦C³Ì¤j¯Á¤Þ¸¹¼Æ
Debug.Print Join(Arr, ",")
'¡ô¦b§Y®É¹Bºâµ¡Åã¥Ü: ¥H","²Å¸¹³sµ² Arr°}¦C­Èªº¦r¦êµ²ªG
End Sub


Function QuickSort(ByRef ar, ByVal iLo As Long, ByVal iHi As Long)
'«Å§iar¬O³q¥Î«¬ÅܼÆ,ByRef_«ü©w¶Ç»¼¤Þ¼Æªº¤è¦¡¬O©I¥sµ{¦¡¥i¥HÅܧó©I¥sµ{¦¡¤º¤Þ¼Æ°ò¦ªºÅܼƭȡC
'«Å§iiLo, iHi ¬Oªø¾ã¼Æ,ByVal«ü©w¤Þ¼Æ¬O¥H ¶Ç­È¤è¦¡¶Ç»¼
Dim pivot As Variant, tmp As Variant, lo As Long, hi As Long
'¡ô«Å§iÅܼÆ:(pivot,tmp)¬O³q¥Î«¬ÅܼÆ,(lo,hi)¬Oªø¾ã¼Æ
lo = iLo
'¡ô¥Olo³o³q¥Î«¬ÅܼƬOiLoÅܼƥNªº­È (Arr°}¦C³Ì¤p¯Á¤Þ¸¹¼Æ)
hi = iHi
'¡ô¥Ohi³o³q¥Î«¬ÅܼƬOiHiÅܼƥNªº­È (Arr°}¦C³Ì¤j¯Á¤Þ¸¹¼Æ)
pivot = UCase(ar((lo + hi) \ 2))
'¡ô¥Opivot¬O ((Arr°}¦C³Ì¤p¯Á¤Þ¸¹¼Æ+³Ì¤j¯Á¤Þ¸¹¼Æ)/2 ¥h§À¾ã¼Æ­È)¯Á¤Þ¸¹ªºar°}¦C­È¦AÂର¤j¼g­^¤å¦r
While lo <= hi
'¡ô³]·íloÅܼÆ<= hiÅÜ¼Æ ±ø¥ó¦¨¥ß°õ¦æªº°j°é
   While (UCase(ar(lo)) < pivot And lo < iHi)
   '¡ô³]·íloÅܼư}¦C­È¦AÂà¤j¼g < pivotÅܼÆ,¦Ó¥BloÅÜ¼Æ < iHiÅܼÆ,±ø¥ó¦¨¥ß°õ¦æªº°j°é
      lo = lo + 1
      '¡ô¥OloÅܼƲ֥[ 1
   Wend
   While (UCase(ar(hi)) > pivot And hi > iLo)
   '¡ô³]·íhiÅܼư}¦C­È¦AÂà¤j¼g > pivotÅܼÆ,¦Ó¥BhiÅÜ¼Æ > iLoÅܼÆ,±ø¥ó¦¨¥ß°õ¦æªº°j°é
      hi = hi - 1
      '¡ô¥OhiÅܼƳv¦¸´î 1
   Wend
   If lo <= hi Then
   '¡ô¦pªG loÅÜ¼Æ <= hiÅܼÆ
      tmp = ar(lo)
      '¡ô¥Otmp³o³q¥Î«¬ÅܼƬO loÅܼƯÁ¤Þ¸¹ªºar°}¦C­È
      ar(lo) = ar(hi)
      '¡ô¥OloÅܼƯÁ¤Þ¸¹ªºar°}¦C­È¬O hiÅܼƯÁ¤Þ¸¹ªºar°}¦C­È
      ar(hi) = tmp
      '¡ô¥OhiÅܼƯÁ¤Þ¸¹ªºar°}¦C­È¬O tmp³o³q¥Î«¬ÅܼƭÈ
      lo = lo + 1
      '¡ô¥OloÅܼƲ֥[ 1
      hi = hi - 1
      '¡ô¥OhiÅܼƳv¦¸´î 1
      tmp = ""
      '¡ô¥Otmp³o³q¥Î«¬ÅܼƬO ªÅ¦r¤¸
   End If
Wend
If hi > iLo Then QuickSort ar, iLo, hi
'¡ô¦pªGhiÅÜ¼Æ > iLoÅܼÆ,µ¹QuickSort³o¦Û­q¨ç¼Æ3­Ó ÅܼƥO¨ä¦^¶Ç­È
'1.arÅܼÆ: ar°}¦C
'2.iLo&: iLoÅܼÆ
'3.iHi&: hiÅܼÆ
If lo < iHi Then QuickSort ar, lo, iHi
'¡ô¦pªGloÅÜ¼Æ < iHiÅܼÆ,µ¹QuickSort³o¦Û­q¨ç¼Æ3­Ó ÅܼƥO¨ä¦^¶Ç­È
'1.arÅܼÆ: ar°}¦C
'2.iLo&: loÅܼÆ
'3.iHi&: iHiÅܼÆ
End Function

dou10801

¦^´_ 11# singo1232001 ·PÁ«ü¾É,¦¬¤U,ºCºC®ø¤Æ.

singo1232001

¦^´_ 10# dou10801


    Sub SortArray()
  arr = Array("a", "b", "c", "d", "e", "C", "b")
  'arr = Y.keys()
  QuickSort arr, LBound(arr), UBound(arr)
  Debug.Print Join(arr, ",")
End Sub

Function QuickSort(ByRef ar, ByVal iLo As Long, ByVal iHi As Long)
  Dim pivot As Variant, lo, hi As Long
  
  lo = iLo: hi = iHi
  pivot = UCase(ar((lo + hi) \ 2))
  
  While lo <= hi
    While (UCase(ar(lo)) < pivot And UCase(lo) < UCase(iHi)): lo = lo + 1: Wend
    While (UCase(ar(hi)) > pivot And UCase(hi) > UCase(iLo)): hi = hi - 1: Wend
    If (lo <= hi) Then
      tmp = ar(lo): ar(lo) = ar(hi): ar(hi) = tmp
      lo = lo + 1: hi = hi - 1: tmp = ""
    End If
  Wend
  
  If hi > iLo Then QuickSort ar, iLo, hi
  If lo < iHi Then QuickSort ar, lo, iHi
End Function

dou10801

·PÁ¦U¦ì«e½ú«ü¾É,¦p¦ó±NY.KEYS§@±Æ§Ç,ÁÂÁÂ.

­ã´£³¡ªL

Formula1:=Join(Y.keys, ",")
¥»¨­§Y¬O°}¦C, ¤£¶·¦AÂ൹¨ä¥¦°}¦CÅܼÆ

Andy2483

¦^´_ 6# singo1232001


    «e½úªº¸É¥Rª¾ÃÑ¥]§t«Ü¼s,¦³¨Ç«á¾ÇÁÙ¤£·|¥Î,½Ð«e½ú¥H«á¦A´£ÂI
¦Ü©ó¦r¨å¸Ìªº°}¦C¤j¤p¤£¯à§ï,«á¾Ç¦³¾Ç¨ì¥ý±N¦r¨å¸Ì©ñ¤J¤@­Ó°÷¥ÎªºªÅ°}¦C,´N¥i¥HÅܳq¨Ï¥Î
ÁÂÁ«e½ú¦A«ü¾É

¸É¥R¤ß±o:
¦r¨å¥i³z¹LAdd,Remove,RemoveAll°µ¦r¨å¼W´î