16.05.2020, 21:25
16.05.2020, 21:40
(16.05.2020, 21:25)snb schrieb: [ -> ]@Kuwer,
Du bezeichnest Arraylist mit 'Collection' ?
Wo? :19:
Gruß Uwe
17.05.2020, 16:07
Mein Deutsch ist offensichtlich ausreichend.... :28:
17.05.2020, 16:14
Kuwer hatte Arraylist in seinem Vergleich, bevor Du Dich (mit collection) erstmals gemeldet hast, snb. Dann kann er das schlecht meinen.
17.05.2020, 16:27
Und was bringt das normale Sortieren in Excel ?
Code:
Sub M_snb()
sn = Tabelle6.Cells(1).CurrentRegion
ReDim sp(UBound(sn) * UBound(sn, 2), 0)
For j = 1 To UBound(sp)
sp(j - 1, 0) = sn((j - 1) \ 10 + 1, (j - 1) Mod 10 + 1)
Next
Cells(1, 14).Resize(UBound(sp)) = sp
Cells(1, 14).CurrentRegion.Sort Cells(1, 14), , , , , , , 0
sq = Cells(1, 14).Resize(UBound(sp))
For j = 1 To UBound(sq)
sn((j - 1) \ 10 + 1, (j - 1) Mod 10 + 1) = sq(j, 1)
Next
Tabelle6.Cells(1).CurrentRegion.Offset(, 16) = sn
End Sub
17.05.2020, 18:03
Sub Formelsortierung() 'Sortiert mit ,,-1)," absteigend, mit )," aufsteigend (dritte Formelzeile)
On Error Resume Next: ActiveWorkbook.Names("ii").Delete
[L1].Clear: A = Timer
ActiveWorkbook.Names.Add Name:="ii", RefersToR1C1:="=MatrixBlatt!R1C1:R20000C10"
[L1].Formula2R1C1 = "=INDEX(SORT(INDEX(ii,(" & Chr(10) & _
"SEQUENCE(ROWS(ii)*COLUMNS(ii),,COLUMNS(ii)))/COLUMNS(ii),MOD(" & Chr(10) & _
"SEQUENCE(ROWS(ii)*COLUMNS(ii),,COLUMNS(ii)),COLUMNS(ii))+1),,-1)," & Chr(10) & _
"SEQUENCE(ROWS(ii),,0)*COLUMNS(ii)+MOD(SEQUENCE(,COLUMNS(ii),0),COLUMNS(ii))+1)"
MsgBox Timer - A
' Ersetze die letzte Formelzeile für
' Telefonbuchsortierung:
' "SEQUENCE(,COLUMNS(ii),0)*ROWS(ii)+MOD(SEQUENCE(ROWS(ii),,0),ROWS(ii))+1)"
' Leserichtung-Sort:
' "SEQUENCE(ROWS(ii),,0)*COLUMNS(ii)+MOD(SEQUENCE(,COLUMNS(ii),0),COLUMNS(ii))+1)"
End Sub
kommt auf meinem I5 - 8GB - Surface 6 auf 1,2 - 2,7 Sekunden (Kuwers Datei, 200.000 Einträge)
_____________________________________________________________________
Die Formel #13 (die dem Code zugrunde liegt) hatte ich aus Versehen falsch umgebrochen, so dass sie nicht funktionierte. Richtig:
=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)
On Error Resume Next: ActiveWorkbook.Names("ii").Delete
[L1].Clear: A = Timer
ActiveWorkbook.Names.Add Name:="ii", RefersToR1C1:="=MatrixBlatt!R1C1:R20000C10"
[L1].Formula2R1C1 = "=INDEX(SORT(INDEX(ii,(" & Chr(10) & _
"SEQUENCE(ROWS(ii)*COLUMNS(ii),,COLUMNS(ii)))/COLUMNS(ii),MOD(" & Chr(10) & _
"SEQUENCE(ROWS(ii)*COLUMNS(ii),,COLUMNS(ii)),COLUMNS(ii))+1),,-1)," & Chr(10) & _
"SEQUENCE(ROWS(ii),,0)*COLUMNS(ii)+MOD(SEQUENCE(,COLUMNS(ii),0),COLUMNS(ii))+1)"
MsgBox Timer - A
' Ersetze die letzte Formelzeile für
' Telefonbuchsortierung:
' "SEQUENCE(,COLUMNS(ii),0)*ROWS(ii)+MOD(SEQUENCE(ROWS(ii),,0),ROWS(ii))+1)"
' Leserichtung-Sort:
' "SEQUENCE(ROWS(ii),,0)*COLUMNS(ii)+MOD(SEQUENCE(,COLUMNS(ii),0),COLUMNS(ii))+1)"
End Sub
kommt auf meinem I5 - 8GB - Surface 6 auf 1,2 - 2,7 Sekunden (Kuwers Datei, 200.000 Einträge)
_____________________________________________________________________
Die Formel #13 (die dem Code zugrunde liegt) hatte ich aus Versehen falsch umgebrochen, so dass sie nicht funktionierte. Richtig:
=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)
17.05.2020, 21:23
Hallo snb,
das normale Sortieren hatte ich unter dem Namen TabSort schon mit drin (siehe #10). Das ist schneller als ich vermutete. Ich hatte da aber noch zwei unnötige Schleifen drin, die nun durch (Deine) Modulo-Schleifen nicht mehr nötig sind. Das macht es nochmals um einiges schneller. Übrigens ist Deine Version im selben Blatt langsamer als in einer neuen Datei.
Hier das Makro mit normaler Excelsortierung:
Vergleich
LCohens Variante kann ich leider mit meiner Excelversion nicht testen.
Gruß Uwe
das normale Sortieren hatte ich unter dem Namen TabSort schon mit drin (siehe #10). Das ist schneller als ich vermutete. Ich hatte da aber noch zwei unnötige Schleifen drin, die nun durch (Deine) Modulo-Schleifen nicht mehr nötig sind. Das macht es nochmals um einiges schneller. Übrigens ist Deine Version im selben Blatt langsamer als in einer neuen Datei.
Hier das Makro mit normaler Excelsortierung:
Sub Sort_Matrix_Tabellenblatt_Kuwer()Hier noch mal ein Vergleich:
Dim i As Long, x As Long, y As Long
Dim datStart As Date, datStopp As Date
Dim rngBereich As Range
Dim varB As Variant, varS As Variant
Range("L1").CurrentRegion = ""
datStart = Timer
Application.ScreenUpdating = False
' Set rngBereich = Range("A1:J100")
' Set rngBereich = Range("A1:J20000")
Set rngBereich = Range("A1:J100000")
varB = rngBereich.Value
x = UBound(varB, 1)
y = UBound(varB, 2)
ReDim varS(1 To x * y, 1 To 1)
For i = 1 To UBound(varS)
varS(i, 1) = varB((i - 1) \ y + 1, (i - 1) Mod y + 1)
Next
With Workbooks.Add(xlWBATWorksheet).Sheets(1).Cells(1)
.Resize(UBound(varS)).Value = varS
.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
varS = .CurrentRegion.Value
.Parent.Parent.Close False
End With
For i = 1 To UBound(varS)
varB((i - 1) \ y + 1, (i - 1) Mod y + 1) = varS(i, 1)
Next
rngBereich.Offset(, 11).Value = varB
Application.ScreenUpdating = True
datStopp = Timer
Debug.Print datStopp - datStart & " (TabSort)"
MsgBox datStopp - datStart
End Sub
Vergleich
A | B | C | D | |
1 | Sortiermethode | 100*10 | 20000x10 | 100000x10 |
2 | FormelSort | 0,078 | 3,531 | 41,195 |
3 | FormelSort | 0,078 | 3,395 | 41,719 |
4 | FormelSort | 0,063 | 3,379 | 40,188 |
5 | ||||
6 | QuickSort | 0,086 | 2,598 | 14,969 |
7 | QuickSort | 0,055 | 2,508 | 14,609 |
8 | QuickSort | 0,063 | 2,891 | 15,453 |
9 | ||||
10 | TabSort | 0,125 | 2,258 | 12,742 |
11 | TabSort | 0,125 | 2,211 | 12,992 |
12 | TabSort | 0,125 | 2,203 | 12,836 |
13 | ||||
14 | ADODB | 0,078 | 3,094 | 17,172 |
15 | ADODB | 0,063 | 3,438 | 17,164 |
16 | ADODB | 0,070 | 3,281 | 17,602 |
17 | ||||
18 | ArrayList | 0,156 | 2,555 | 13,867 |
19 | ArrayList | 0,164 | 2,586 | 13,875 |
20 | ArrayList | 0,156 | 2,570 | 14,320 |
LCohens Variante kann ich leider mit meiner Excelversion nicht testen.
Gruß Uwe
17.05.2020, 22:14
Ola Uwe:
Danke für's testen.
Meiner letzte Vorschlag kann schneller werden mit:
Es gibt noch eine Methode: sortedlist (ich fürchte viel langsamer)
Danke für's testen.
Meiner letzte Vorschlag kann schneller werden mit:
Code:
Sub M_snb()
Application.ScreenUpdating = False
Application.Calculation = -4135
Dim j As Long
t1 = Timer
sn = Tabelle6.Cells(1).CurrentRegion
ReDim sp(UBound(sn) * UBound(sn, 2), 0)
For j = 1 To UBound(sp)
sp(j - 1, 0) = sn((j - 1) \ 10 + 1, (j - 1) Mod 10 + 1)
Next
With Cells(1, 14)
.Resize(UBound(sp)) = sp
.CurrentRegion.Sort Cells(1, 14), , , , , , , 0
sq = .Resize(UBound(sp))
End With
For j = 1 To UBound(sq)
sn((j - 1) \ 10 + 1, (j - 1) Mod 10 + 1) = sq(j, 1)
Next
Tabelle6.Cells(1).CurrentRegion.Offset(, 16) = sn
End Sub
Es gibt noch eine Methode: sortedlist (ich fürchte viel langsamer)
Code:
Sub snb_sortedlist()
Application.ScreenUpdating = False
Application.Calculation = -4135
Dim j As Long
sn = Tabelle6.Cells(1).CurrentRegion
With CreateObject("System.Collections.SortedList")
For Each it In sn
.Item(it) = it
Next
For j = 0 To .Count - 1
sn(j \ 10 + 1, j Mod 10 + 1) = .getkey(j)
Next
End With
Tabelle6.Cells(1).CurrentRegion.Offset(, 16) = sn
End Sub:
17.05.2020, 23:12
(17.05.2020, 22:14)snb schrieb: [ -> ]Es gibt noch eine Methode: sortedlist (ich fürchte viel langsamer)
Deine Befürchtung hat sich bewahrheitet: 29,5 Sekunden für 100000x10 Zellen.
Das Umschalten auf manuelle Berechnung bringt bei mir nichts. Es geht ja aber auch nicht um die absoluten Zeiten, sondern den Vergleich der Methoden zueinander.
Gruß Uwe
18.05.2020, 03:52
Meine #26 habe ich jetzt auch mit 100.000 x 10 Zellen gemessen:
020.000 x 10 Zellen: ca. 1,2 bis 2,7 Sekunden
100.000 x 10 Zellen: ca. 5,2 bis 6,1 Sekunden (Surface 6 - i5 - 8GB RAM - nur SSD, kein HD)
Es sind 4 Sortierungen im Code einstellbar (dort kommentiert):
1 2 3
4 5 6 Leserichtung aufsteigend
6 5 4
3 2 1 Leserichtung absteigend
1 3 5
2 4 6 Telefonbuch aufsteigend
6 4 2
5 3 1 Telefonbuch absteigend
Übrigens, in Ansicht des snb-Codes: Bei mir ist nix mit ScreenUpdating = False (und nicht wieder True)! Ganz normales Formel-Eintragen. ScreenUpdating ist somit normal als "True" enthalten, müsste man für den Vergleich also noch abziehen. Das gleiche gilt für Calculation. Ganz normal auf "Automatisch".
Nachteil meiner Lösung: Bei 104.857 x 10 ist Schluss, da Excel nur 1.048.576 Zeilen hat und der innere INDEX aus dem Bereich eine einzelne Sortierspalte erstellt, die nicht länger sein kann, als die Tabelle. Das müsste bei RPP63 ("Formelsort") auch so sein; die anderen Lösungen mit VBA-Sort hingegen sind nicht an dieser Grenze beschränkt (sondern erst später).
020.000 x 10 Zellen: ca. 1,2 bis 2,7 Sekunden
100.000 x 10 Zellen: ca. 5,2 bis 6,1 Sekunden (Surface 6 - i5 - 8GB RAM - nur SSD, kein HD)
Es sind 4 Sortierungen im Code einstellbar (dort kommentiert):
1 2 3
4 5 6 Leserichtung aufsteigend
6 5 4
3 2 1 Leserichtung absteigend
1 3 5
2 4 6 Telefonbuch aufsteigend
6 4 2
5 3 1 Telefonbuch absteigend
Übrigens, in Ansicht des snb-Codes: Bei mir ist nix mit ScreenUpdating = False (und nicht wieder True)! Ganz normales Formel-Eintragen. ScreenUpdating ist somit normal als "True" enthalten, müsste man für den Vergleich also noch abziehen. Das gleiche gilt für Calculation. Ganz normal auf "Automatisch".
Nachteil meiner Lösung: Bei 104.857 x 10 ist Schluss, da Excel nur 1.048.576 Zeilen hat und der innere INDEX aus dem Bereich eine einzelne Sortierspalte erstellt, die nicht länger sein kann, als die Tabelle. Das müsste bei RPP63 ("Formelsort") auch so sein; die anderen Lösungen mit VBA-Sort hingegen sind nicht an dieser Grenze beschränkt (sondern erst später).