Daten in Matrixform aufsteigend sortieren

[snb]

@Kuwer,

Du bezeichnest Arraylist mit 'Collection'  ?

[Kuwer]

@Kuwer,

Du bezeichnest Arraylist mit 'Collection'  ?

Wo? :19:

Gruß Uwe

[snb]

Mein Deutsch ist offensichtlich ausreichend.... :28:

[LCohen]

Kuwer hatte Arraylist in seinem Vergleich, bevor Du Dich (mit collection) erstmals gemeldet hast, snb. Dann kann er das schlecht meinen.

[snb]

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

[LCohen]

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)

[Kuwer]

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:

Sub Sort_Matrix_Tabellenblatt_Kuwer()
  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

Hier noch mal ein Vergleich:

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

[snb]

Ola Uwe:

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:

[Kuwer]

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

[LCohen]

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).