Daten in Matrixform aufsteigend sortieren

[RPP63]

Interessant, @Uwe!
Ich gestehe ja, dass ich (aus Faulheit!) nicht an eine Massenverarbeitung gedacht habe.
Aus Erfahrung weiß ich hingegen, dass
.Value = .Value
Performance-Probleme hat.
Wenn ich hingegen statt obigem jeweils (2*) folgendes nehme:
.Copy
.PasteSpecial xlPasteValues
verbessere ich (bei mir) den Durchlauf der großen Matrix
von 3,4 auf 2,4 Sekunden!
(immerhin grob 30% und damit gleichwertig zu QuickSort!)

Gruß Ralf

[Kuwer]

Hi Ralf,

kann ich bestätigen. Allerdings ist die Copy/Paste-Methode beim kleinen Bereich wiederum langsamer.

Hier noch mal ein Vergleich bei 10 x 100000:

Code:

41,1953125  (FormelSort)
41,71875    (FormelSort)
40,1875     (FormelSort)

16          (QuickSort)
16,34375    (QuickSort)
17,03515625 (QuickSort)

14,09765625 (TabSort)
14,109375   (TabSort)
14,015625   (TabSort)

Gruß Uwe

[LCohen]

In den neuen Excel-Versionen mit =SORTIEREN() geht folgendes als einzelne Formel 
(bitte an einer Stelle eintragen, wohinter nichts folgt, wegen #ÜBERLAUF!-Fehler):

=INDEX(SORTIEREN(INDEX(ii;
(SEQUENZ(ZEILEN(ii)*SPALTEN(ii);;SPALTEN(ii)))/SPALTEN(ii);REST
(SEQUENZ(ZEILEN(ii)*SPALTEN(ii);;SPALTEN(ii));SPALTEN(ii))+1));
SEQUENZ(ZEILEN(ii);;0)*SPALTEN(ii)+REST(SEQUENZ(;SPALTEN(ii);0);SPALTEN(ii))+1)

Der Platzhalter ii kann (bzw. muss) durch das A1:E3 des Beispiels ersetzt werden: Strg-H, ii, A1:E3.

Oder ii stellt den definierten Namen über den Bereich A1:E3 dar. Dann kann es so bleiben. Ergibt Sinn für die Lesbarkeit.

Dadurch, dass die Formel dynamisch ist, kann man nun beliebig in A1:E3 Zeilen und Spalten einfügen oder löschen (natürlich nur so, dass der Bereich nicht #BEZUG!-korrumpiert wird).

[Elex]

Hi

Leider liegen die Testzeiten auf meinem Rechner weit weg von Uwe seinen Zeiten. (fast doppelt so lange)
Eine Code Alternative wäre es mit ArrayList zu arbeiten. Zeitlich sollte das bei den anderen liegen.

Code:

Public Sub test()
Dim j, Werte, ArrL As Object, z As Long, s As Long, a As Long, t

t = Timer
Range("L1").CurrentRegion = ""

Set ArrL = CreateObject("System.Collections.ArrayList")
Werte = Sheets(1).Cells(1).CurrentRegion
   For Each j In Werte
      ArrL.Add j
   Next j
   ArrL.Sort
   j = ArrL.toarray
   For z = 1 To UBound(Werte)
      For s = 1 To UBound(Werte, 2)
          Werte(z, s) = j(a)
          a = a + 1
      Next s
   Next z
Sheets(1).Cells(1).CurrentRegion.Offset(, 11) = Werte

Set ArrL = Nothing
MsgBox Timer - t
End Sub

Gruß Elex

[schauan]

Hallöchen,

hier mal meine Ergebnisse, i7-4910MQ, 16 GB, W10/Excel 2019

kurz
0,02734375 (FormelSort)
0,03125 (FormelSort)
0,04296875 (FormelSort)
0,04296875 (QuickSort)
0,02734375 (QuickSort)
0,04296875 (QuickSort)
0,28515625 (TabSort)
0,2734375 (TabSort)
0,296875 (TabSort)
0,140625(Elex)
0,1445313(Elex)
0,1210938(Elex)

lang
4,0859375 (FormelSort)
3,98828125 (FormelSort)
4,01953125 (FormelSort)
2,5703125 (QuickSort)
2,56640625 (QuickSort)
2,62109375 (QuickSort)
2,48046875 (TabSort)
2,484375 (TabSort)
2,4453125 (TabSort)
2,597656(Elex)
2,609375(Elex)
2,601563(Elex)

[snb]

Oder:

Code:

Sub M_snb()
    sn = Range("A1:J20000")
    ReDim sp((UBound(sn) - 1) * (UBound(sn, 2) - 1), 9)
   
    With CreateObject("ADODB.recordset")
        .Fields.Append "item", 129, 6
        .Open
       
        For Each it In sn
            .AddNew
            .Fields("item") = it
            .Update
        Next
        .Sort = "item"
        st = .Getrows
    End With
   
    For j = 0 To UBound(st, 2)
      sp(j \ 10, j Mod 10) = st(0, j)
    Next
   
    Cells(1, 16).Resize(20000, 10) = sp
End Sub

[Kuwer]

Hallo,

als Hinweis: snb`s ADODB erlaubt nur Textlängen von 6 Zeichen.

200000 Zellen (20000 x 10):

3,53125    (FormelSort)
3,39453125  (FormelSort)
3,37890625  (FormelSort)

2,59765625  (QuickSort)
2,5078125  (QuickSort)
2,890625    (QuickSort)

2,35546875  (TabSort)
2,1953125  (TabSort)
2,2890625  (TabSort)

3,09375    (ADODB)
3,4375      (ADODB)
3,28125    (ADODB)

2,554688    (ArrayList)
2,585938    (ArrayList)
2,570313    (ArrayList)


1000000 Zellen (100000 x 10):

41,1953125  (FormelSort)
41,71875    (FormelSort)
40,1875    (FormelSort)

16          (QuickSort)
16,34375    (QuickSort)
17,03515625 (QuickSort)

14,09765625 (TabSort)
14,109375  (TabSort)
14,015625  (TabSort)

17,17188    (ADODB)
17,16406    (ADODB)
17,60156    (ADODB)

13,86719    (ArrayList)
13,875      (ArrayList)
14,32031    (ArrayList)


Gruß Uwe

[Fennek]

Hallo,

hier ist noch eine Alternative mit "Collection":

Code:

Public Col As Collection

Sub Anlegen()
For i = 1 To 10
    For j = 1 To 10
        Cells(i, j) = "'" & Right("00" & Hex(Rnd() * 20000000), 6)
    Next j
Next i

End Sub

Sub SortKuwer() ' sorry einfach übernommen
  Dim i As Long, j As Long, k As Long
    Set Col = New Collection
  
anf = Timer

  varB = Cells(1).CurrentRegion
  
  For i = 1 To UBound(varB, 1)
    For j = 1 To UBound(varB, 2)
      Col.Add varB(i, j)
    Next j
  Next i
  
  Col_sort
  
  k = 0
  For i = 1 To UBound(varB, 1)
    For j = 1 To UBound(varB, 2)
      k = k + 1
      varB(i, j) = Col(k)
    Next j
  Next i

  Sheets(2).Range("A1").Resize(UBound(varB), UBound(varB, 2)) = varB
Debug.Print Timer - anf
End Sub

Sub Col_sort()
For i = 1 To Col.Count - 1
    For i2 = i + 1 To Col.Count
        If Col(i) > Col(i2) Then
           temp = Col(i2)
           Col.Remove i2
           Col.Add temp, temp, i
        End If
    Next i2
Next i
End Sub

Anstelle der Strings werden (beliebig) viele Hex generiert. Die Collection wird mit einem selbst-geschriebenen Algorithmus sortiert.

Könnte jemand einen Geschwindigkeitsvergleich ermitteln?

mfg

[Kuwer]

Hallo Fennek,

die VBA-Collection ist schnarchlangsam und für größere Datenmengen leider unbrauchbar. :22:

0,0390625 für 100 Zellen
2,515625 für 1000 Zellen
21,29688 für 2000 Zellen
74,52344 für 3000 Zellen

Gruß Uwe

[Fennek]

Hallo Uwe,

danke.

Ich habe den Quick-Sort gespeichert.

mfg