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
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
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).
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
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)
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
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
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
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
Hallo Uwe,
danke.
Ich habe den Quick-Sort gespeichert.
mfg