Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1720to1724
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Sortierfunktion

Sortierfunktion
18.11.2019 04:18:36
Steve
Hallo Excel-Profis.
ich habe einen Zellbreich mit 13 Spalten und 6 Zeilen, der mit Zeichenketten gefüllt ist.
z.B. BCAE, ACD, EC ,und weitere. Auch leere Zellen sind darunter.
Ich habe mir eine Funktion geschrieben, die mir jede einzelne Zelle alphabetisch sortiert (ABCE, ACD,CE,....)
Leider ist diese sehr langsam. Gibt es eine schnellere Alternative?
Hier die Funktion:

'____________________________________________________________
Public Function SortiereArray(ByRef arrChar() As String) As String()
Dim strTemp As String                      'temp. Tauschstring
Dim arrSort() As String                     'Sortierarray
Dim lngIndex As Long                       'Index des Sortierarrays
Dim blnSortiert As Boolean                'Sortieren beendet? true/false
arrSort = arrChar
Do
blnSortiert = True
For lngIndex = LBound(arrSort) To UBound(arrSort) - 1
If arrSort(lngIndex) > arrSort(lngIndex + 1) Then
blnSortiert = False
strTemp = arrSort(lngIndex)
arrSort(lngIndex) = arrSort(lngIndex + 1)
arrSort(lngIndex + 1) = strTemp
End If
Next lngIndex
Loop Until blnSortiert = True
SortiereArray = arrSort
End Function
'___________________________________________________________
'Hier das Sub zum Aufrufen der Funktion:
Sub Sortieren()
Dim rngC As Range
Dim Text As String
Dim Vergleich As String
Dim Char As Integer
Dim arrChar() As String
Application.ScreenUpdating = False
ActiveSheet.Unprotect
For Each rngC In Range("L11:AP17")
Vergleich = ""
Text = rngC.Value
ReDim arrChar(Len(Text))
For Char = 1 To Len(Text)
arrChar(Char - 1) = Mid(Text, Char, 1)
Next Char
arrChar = SortiereArray(arrChar)
For Char = 1 To Len(Text)
Vergleich = Vergleich & arrChar(Char)
Next Char
rngC.Offset(240, 0).Value = Vergleich
Next rngC
ActiveSheet.Protect , AllowFiltering:=True
Application.ScreenUpdating = True
End Sub

24
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Mach mal bitte ein Beispiel von 3 Zeilen und
18.11.2019 05:38:41
3
2 Spalten
- vorher und
- Wunschergebnis nachher (nach Sort).
Bitte ohne Code, nur als Beispiel wie folgt:
df hi
cw kl
zk aw
? ?
? ?
? ?
Vermutlich kann man Dein Ergebnis
- durch einen oder mehrere Excel-Sorts (seit eh und je vorhandenGib) oder
- durch Einsatz der neuen Funktion =SORTIEREN() oder =SORTIERENNACH()
beschleunigen und im Code raffen.
AW: Mach mal bitte ein Beispiel von 3 Zeilen und
18.11.2019 07:21:46
3
Also Beispiel:
Vor Sortierung
_¦_A_¦_B_¦__C
1¦ADC¦___¦_DB
2¦___¦_D_¦_EC
Nach Sortierung
_¦_A_¦_B_¦__C
1¦ACD¦___¦_BD
2¦___¦_D_¦_CE
Zellen enthalten Strings mit Kombinationen aus max 5 Buchstaben. Ich hoffe es wird etwas klarer jetzt. Sry für die Optik aber am Handy geht das nich so gut ^^'
Anzeige
In xl365: Per =SORTIEREN() für Zelle A1
18.11.2019 06:29:50
lupo1
B1: =TEXTVERKETTEN(;;SORTIEREN(GLÄTTEN(TEIL(TEXTVERKETTEN(WIEDERHOLEN(" ";99);;TEIL(A1;ZEILE($1:$49);1));ZEILE($1:$49)*99-98;99))))
als normale, nicht als {}-Funktion. Die Zelle A1 selbst wird nur ein einziges Mal genannt! Statt A1 kann da nun mit Leichtigkeit alles Mögliche stehen, also z.B. auch TEXTVERKETTEN(;;A1:D1).
Weitere Zellen für sich allein dann durch Kopieren der Funktion nach rechts und unten.
Viel, viel kürzer, falls nur einzelne Buchstaben
18.11.2019 06:34:34
lupo1
B1: =TEXTVERKETTEN(;;SORTIEREN(TEIL(A1;ZEILE($1:$49);1)))
AW: aber eben leider nicht mit XL2016 des TE owT
18.11.2019 06:54:06
neopa
Gruß Werner
.. , - ...
Tja, xl365 ist halt ein Quantensprung ...
18.11.2019 07:28:08
lupo1
... sollte man sich gönnen!
99 Euro p.a. (oder auch etwas günstiger, wenn man sich umhört) für fünf Lizenzen in der HOME-Version ... das ist nicht so teuer, wenn man die unter der Familie mit verteilt. Vor allem sind die meisten der Office-Programme mit dabei, wobei ich davon Excel zu 90% und Word zu 9% nutze (mir würde also auch die billigste feste Suite ausreichen).
Wer gar nichts ausgeben möchte, kann diese Funktionen 1:1 in GDocs verwenden - und vermutlich auch schon (bald) in LO (VERBINDEN gibt es dort schon).
Userbild
Anzeige
AW: Tja, xl365 ist halt ein Quantensprung ...
18.11.2019 07:52:31
Steve
Zuhause hab ich auch xl365 aber halt nich auf Arbeit
Dann nimm auf Arbeit LO - oder falls erl., GDocs -
18.11.2019 07:57:47
lupo1
... wo es halt Vorteile bringt, zusätzlich.
Übrigens war GDocs zuerst mit =SORT() und =FILTER() da, vielleicht auch bei =TEXTJOIN().
MS hat spätestens damit die Trendführerschaft bei Excel aufgegeben.
AW: viele können/gar dürfen nicht so frei sein owT
18.11.2019 09:29:10
neopa
Gruß Werner
.. , - ...
Mit 365 sind sie es.
18.11.2019 09:40:49
lupo1
Entweder, oder - aber rumjammern "güldet nich".
AW: nur wenn der AG die Voraussetzung schafft owT
18.11.2019 09:43:38
neopa
Gruß Werner
.. , - ...
Das geht auch anders ...
18.11.2019 10:07:38
lupo1
Wie wäre es, dem Arbeitgeber etwas vorzuschlagen?
Ich war mit 27 der am meisten fortgeschrittene am PC in der Firma. Das Plasmaorange der Toshibas 5100, 5200 und 3100 ging mir voll auf den Senkel. Ich bestellte mir ein LCD-Notebook (war nicht viel teurer, aber die Plasmas waren noch nicht kaputt) und bekam es.
Dabei ging es um 3000 Mark. Hier geht es um viel weniger - aber sicherlich genauso das Hauptwerkzeug.
.
Anzeige
AW: sollte allgemein zutreffend sein, ...
18.11.2019 13:56:33
neopa
Hallo,
... aber ich weiß von einigen, dass diese oft für viel "kleinere Wünsche" Ablehnungen erhalten haben (weil es ja oft nicht nur eine oder zwei Personen betrifft).
Gruß Werner
.. , - ...
wobei man jetzt Stück Hardware nicht mit einer
18.11.2019 19:20:37
Daniel
Standardsoftware, die im Prinzip auf allen Bürorechnern installiert ist, vergleichen kann.
allein schon wegen der Zusammenarbeit und Austauschbarkeit würde es dann ja erforderlich sein, auf allen Rechnern entsprechend Office 365 zu installieren und da kommst du, je nach Größe der Firma, mit 3000 € nicht weit.
AW: wobei man jetzt Stück Hardware nicht mit einer
18.11.2019 22:24:48
Steve
Eben. Ich bin nur ein kleines Licht auf unserer Unternehmenstorte ;)
Bei kleineren Firmen mag das vllt noch gehen, aber nicht bei uns.
Anzeige
Richtig, und jaaanz loggä bleibe
19.11.2019 09:58:20
lupo1
1. Zum Glück klappt Dein VBA-Code.
2. Beim regulären Austausch der PC (Plural) in der Firma (bestimmt spätestens nach 5-6 Jahren) fallen für den PC keine zusätzlichen Kosten an, als sowieso schon; dann halt nur für Office 365, aber auch dafür nicht, denn beim neuen PC gäbe es ja auch ein neues Office, weil man sonst - zumindest langfristig - von MS keine Updates mehr bekommt. Man läuft also auch von selbst in den Fortschritt hinein, ohne extra, als sowieso schon, zu löhnen.
Leider ist diese sehr langsam. heißt was?
18.11.2019 08:01:03
Matthias
Hallo
Zitat:
Leider ist diese sehr langsam.
Heißt bitte was?
Mein Test ergab in Punkto Geschwindigkeit keine Probleme.
Als Tipp:
Benutze keine Kombinationen aus Groß- und KleinBuchstaben!
Sonst geht Deine Sortierung daneben. Es sei denn Du benutzt LCase oder UCase.
Was ich persönlich auch nicht für optimal halte ist, das Du den Zielbereich ohne Nachfrage überschreibst.
Gruß Matthias
Anzeige
AW: Leider ist diese sehr langsam. heißt was?
18.11.2019 22:22:32
Steve
Ich hab das auf arbeit durchlaufen lassen und der hat für jede Zelle ca 1s gebraucht.
das sind bei 6*31 Zellen über 3 Minuten. Das is schon arg lang.
Und es sind nur die 5 Großbuchstaben A;B;C;D;E die in beliebiger Reihenfolge auftreten und anschließend sortiert werden sollen.
AW: Leider ist diese sehr langsam. heißt was?
19.11.2019 09:53:57
Daniel
Das ist sehr langsam.
Sind da noch weitere Formeln, die ggf bei jedem Einfügen neu berechnet werden oder gibt es change-Makro im Modul des Tabellenblatts?
Dann könntest du noch neben dem ScreenUpdating auch noch die Calculation und das EnableEvents ausschalten, um fue Sache zu beschleunigen (einschalten nicht vergessen) oder wir von mir gezeigt mit einem Array arbeiten.
Gruß Daniel
Anzeige
AW: Leider ist diese sehr langsam. heißt was?
19.11.2019 11:21:14
peterk
Hallo Daniel
Probier mal:
Function SortString(mystr As String) As String

    Dim i As Long
    Dim objArrLst As Object

    Set objArrLst = CreateObject("System.collections.arraylist")

    For i = 1 To Len(mystr)
        objArrLst.Add Mid(mystr, i, 1)
    Next i

    objArrLst.Sort
    SortString = Join(objArrLst.ToArray, "")

    Set objArrLst = Nothing

End Function

Sub test()

    Dim rngC As Range

    Application.ScreenUpdating = False
    
    '******************************** 
    ' Range und Offset Bitte anpassen 
    '******************************** 
    
    For Each rngC In Range("A1:C4")
        rngC.Offset(10, 0).Value = SortString(rngC.Value)
    Next rngC
    
    Application.ScreenUpdating = True
End Sub

Anzeige
AW: Sortierfunktion
18.11.2019 19:11:38
Daniel
HI
nur so als Tip:
lade die Werte zunächst in ein Array und arbeite dann mit diesem Array
am Ende schreibst du dann das Array als ganzes in den Zellbreich zurück.
das ist schneller, weil vor allem jeder Schreibvorgang in eine Zelle in Excel einiges an Aktionen auslöst. das kann man über ein Array optimieren weil das Rückschreiben des Arrays für Excel ein Vorgang ist, auch wenn mehrere Zellen übergeben werden.
beim Sortieren von größeren Datenmengen sollte man sich mal den Qicksort anschauen.
das ist sehr schnell, aber auch kompliziert.
hier könnte man, wenn ma davon ausgeht dass ein Buchstabe nur einmal vorkommt und nur die 26 Buchstaben des standardaphabets vorkommen, auch etwas anders sortieren, vielleicht ist das ja schneller:
Sub Sortieren()
Dim arr
Dim z As Long, s As Long
Dim SortText() As String
Dim B As String
Dim i As Long
With Range("a1:A3")
arr = .Value
For z = 1 To UBound(arr, 1)
For s = 1 To UBound(arr, 2)
ReDim SortText(1 To 255)
For i = 1 To Len(arr(z, s))
B = Mid(arr(z, s), i, 1)
SortText(Asc(B)) = B
Next
arr(z, s) = Join(SortText, "")
Next
Next
.Value = arr
End With
End Sub

da es hier nur um Buchstaben geht, verwende ich folgenden Trick:
ich lege ein Array mit 255 Plätzen an und schreibe jeden Buchstaben des Textex an die Postition seines ASCII-Wertes.
danach wird das Array mit Join zu einem Text verbunden.
kann man hier in dem Speziellen Fall so machen und ist einfach zu programmieren.
Gruß Daniel
Anzeige
AW: Sortierfunktion
18.11.2019 22:36:57
Steve
Das probier ich morgen Nacht mal aus, wen ich wieder auf Arbeit bin.
Danke im vorraus.
andere Überlegung:
Da die maximale Länge eines Strings 5 ist, kann man nicht mit 5 Variablen(a,b,c,d,e) und einer Select Case Anweisung in einer Schleife arbeiten?
Bsp.:
Der String wäre BCADE. mit Mid geht man jeden Buchstaben einzeln durch.
select case buchstabe A? => a = A B? => b = B ,....
und dann ausgabe als a & b & c & ...
AW: Sortierfunktion
19.11.2019 23:16:03
Steve
Das geht ja super fix. kein Vergleich zu meinem Code.
Muss wohl noch viel lernen.
Danke dir vielmals.
AW: Sortierfunktion
20.11.2019 11:04:24
Daniel
Hi
jetzt solltest du mal testen, was den größeren Anteil am geschwindikeitszuwachs hat:
das Sortieren oder der Einsatz des Arrays anstelle der direkten Bearbeitung jeder Zelle.
Gruß Daniel
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige