Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1788to1792
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

Sortierung nach Cluster

Sortierung nach Cluster
28.10.2020 18:43:13
Gugelhupf
Hallo,
ich brüte schon den ganzen Tag über einem Problem und komme nicht mehr weiter. Auch Google bzw. die Foren-Suche hat mir nicht geholfen.
Es geht um folgendes:
Ich habe eine Tabelle mit Daten die ich nach mehreren Kriterien, jeweils aufsteigend mittels Makro sortiert habe.
Kriterien sind für alle Aufträge in einem Bereich:
- WochenBereich (Zusammengefasst z.B. 3 Wochen)
- Attribut 1 xxx
- Attribut 2 yyy
- Attribut 3 zzz
Das hat soweit gut funktioniert. Nun habe ich aber an den Übergangsbereichen der Wochen einen unschönen Effekt. Attribut 1 (das wichtigste) soll an dieser Stelle mit dem letzten Eintrag aus dem Wochenbereich davor fortgeführt werden. Meine Sortierung beginnt an dieser Stelle aber wieder aufsteigend bei Attribut 1. Hat jemand eine Idee, wie ich so was reparieren kann? Es müsste sich quasi die Sortierreihenfolge ändern. Ich habe mit Vergleich eine Reihenfolge erstellen können. Das ist aber aktuell ein manueller Weg. Eigentlich brauche ich es automatisiert in einem Makro, weil später andere Personen mit dieser Datei arbeiten sollen.
Ich habe mal hier die Datei hochgeladen: https://www.herber.de/bbs/user/141154.xlsm

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sortierung nach Cluster
29.10.2020 16:05:48
Matthias
Moin!
Kannst du das ggf. mal an der Datei erklären, wo es hängt (also was Att 1 ist - laut der Liste in Tab Menue?). Hilfreich wäre, eine Gegenüberstellung der Ausgangsdaten, der gewünschten Daten und was dein Code derzeit ausgibt.
Sind die Daten in der Datei schon die sortierten (nach Makro oder die händischen) oder noch die Rohdaten?
VG
AW: Sortierung nach Cluster
03.11.2020 09:01:18
Gugelhupf
Die Daten in der Datei befinden sich im Reiter "WorkBench" und sind bereits 1x sortiert. Der Plan war die Aufträge hintereinander nach verschiedenen Kriterien sortieren zu lassen um eine optimale Reihenfolge zu bekommen. Das erste Kriterium ist der Termin, dazu werden Die Termine in Blöcken von z.B. 3 Wochen zusammengefasst. In diesen Blöcken soll dann nach 4 weiteren Kriterien sortiert werden. Die Kriterien sind von links nach rechts absteigend nach Wichtigkeit geordnet.
1. Schritt: SortFirmItemsOut
Hier werden fixierte Aufträge nach vorn sortiert. Sind späten vom umsortieren ausgeschlossen.
--> Sortierung nur über Maschine, keine Anpassung der Reihenfolge...
--> bis jetzt kein Problem
Sub SortFirmItemsOut(LastRow1, strBereich, strSpalte1, strSpalte18, strSpalte20)
'Sortierung Makro
'Makro sortiert die aufgelisteten Fertigungsaufträge so, dass die fixierten aufträge am Anfang  _
stehen bleiben.
'Bestand in Bearbeitung und Durchlaufzeiten sind nicht berücksichtigt.
'   Define Constants
'        strSpalte1  = Machine
'        strSpalte18 = Due Date
'        strSpalte20 = Firm Status
'Sort SAB's - Sortierung kann nur 3 KEY's aufnehmen, daher Sortierung von "innen nach außen" _
, d.h. die Hauptattribute kommen am Ende
With Tabelle1.Range(strBereich1)
'Sort Lvl 1
.Sort _
Key1:=Range(strSpalte20 & strZeile1), Order1:=xlAscending, _
Key2:=Range(strSpalte1 & strZeile1), Order2:=xlAscending, _
Key3:=Range(strSpalte18 & strZeile1), Order3:=xlAscending, Header:=xlYes,  _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3:=xlSortTextAsNumbers
End With
FirstRowAfterFirm = Evaluate("=LOOKUP(2,1/(E1:E65535=""firm""),ROW(E:E))") + 1
End Sub

2. Schritt: SortMinimizeSetUp
Ich verwende einen Excelfilter und lasse die Daten jeweils aufsteigend sortieren.
das oberste Kriterium sind Zeitblöcke (Termine der Aufträge in einen 3 Wocheblock einsortiert).
Die Aufträge sind nun nach den Blöcken sortiert und innerhalb der Blöcke wird nach weiteren Kriterien sortiert.
--> Das hat funktioniert!
sieht bei mir so aus:
Sub SortMinimizeSetUp(LastRow1, strBereich, strSpalte1, strSpalte2, strSpalte3, strSpalte4,  _
strSpalte5, strSpalte6, strSpalte7, strSpalte8, strSpalte9, strSpalte10, strSpalte11, strSpalte12, strSpalte13, strSpalte14, strSpalte15, strSpalte16, strSpalte17, strSpalte18)
'Sortierung Makro
'Makro sortiert die aufgelisteten Fertigungsaufträge so, dass die Rüstzeiten minimiert werden.
'Bestand in Bearbeitung und Durchlaufzeiten sind nicht berücksichtigt.
'strSpalte18 = Due Date
'Sort SAB's - Sortierung kann nur 3 KEY's aufnehmen, daher Sortierung von "innen nach außen" _
, d.h. die Hauptattribute kommen am Ende
With Tabelle1.Range(strBereich)
'Sort Lvl 6
.Sort _
Key1:=Range(strSpalte16 & strZeile1), Order1:=xlAscending, _
Key2:=Range(strSpalte17 & strZeile1), Order2:=xlAscending, _
Key3:=Range(strSpalte18 & strZeile1), Order3:=xlAscending, Header:=xlYes,  _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3:=xlSortTextAsNumbers
'Sort Lvl 5
.Sort _
Key1:=Range(strSpalte13 & strZeile1), Order1:=xlAscending, _
Key2:=Range(strSpalte14 & strZeile1), Order2:=xlAscending, _
Key3:=Range(strSpalte15 & strZeile1), Order3:=xlAscending, Header:=xlYes,  _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3:=xlSortTextAsNumbers
'Sort Lvl 4
.Sort _
Key1:=Range(strSpalte10 & strZeile1), Order1:=xlAscending, _
Key2:=Range(strSpalte11 & strZeile1), Order2:=xlAscending, _
Key3:=Range(strSpalte12 & strZeile1), Order3:=xlAscending, Header:=xlYes,  _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3:=xlSortTextAsNumbers
'Sort Lvl 3
.Sort _
Key1:=Range(strSpalte7 & strZeile1), Order1:=xlAscending, _
Key2:=Range(strSpalte8 & strZeile1), Order2:=xlAscending, _
Key3:=Range(strSpalte9 & strZeile1), Order3:=xlAscending, Header:=xlYes,  _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3:=xlSortTextAsNumbers
'Sort Lvl 2
.Sort _
Key1:=Range(strSpalte4 & strZeile1), Order1:=xlAscending, _
Key2:=Range(strSpalte5 & strZeile1), Order2:=xlAscending, _
Key3:=Range(strSpalte6 & strZeile1), Order3:=xlAscending, Header:=xlYes,  _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3:=xlSortTextAsNumbers
'Sort Lvl 1
.Sort _
Key1:=Range(strSpalte1 & strZeile1), Order1:=xlAscending, _
Key2:=Range(strSpalte2 & strZeile1), Order2:=xlAscending, _
Key3:=Range(strSpalte3 & strZeile1), Order3:=xlAscending, Header:=xlYes,  _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3:=xlSortTextAsNumbers
End With
End Sub
3. Schritt: SortCrossing
Die Übergänge der Blöcke sind nun aufgrund der Sortierreihenfolge (Aufsteigend) nicht optimal. EXCEL sortiert z.B.:
- Block 1 = A / A / B / C / C / C,
- Block 2 = A / A / C / C / D / D / D,...
Hier wäre es schön, der 2. Block würde dann mit C beginnen, also dem Wert, mit dem der 1. Block endet. Danach kann EXCEL wieder aufsteigend sortieren.
Mir fehlt hier ein Ansatz das in EXCEL mit einem Makro umzusetzen.
Die Idee war innerhalb der Blöcke nochmals zu sortieren aber diesmal mit dem wichtigsten Attribut1 als oberster Filter. Der Bereich sollte laufen vom letzten Eintrag des vorangegangenen Blocks bis zum letzten Eintrag des aktuellen Blocks. Ziel ist die oben beschriebene Ausrichtung zu bekommen.
Momentan habe ich das Sortier-Makro so umgesetzt:
Option Explicit
Dim strBereich As String                                'Area of the scheduling workbench after  _
the firmed orders
Dim strBereich1 As String                               'Total area of the scheduling workbench
Dim LastRow1 As Integer                                 'Letze Zeile - Tabelle 1
Dim LastRowFirm As Integer                              'Letze Zeile - Tabelle 1 Firm
Dim FirstRowAfterFirm As Integer                        'Erste Zeile nach Fixierten Aufträgen -  _
Tabelle 1
Dim strZeile1 As String                                 'Row where the data in the workbench  _
starts (incl. headers)
Dim strSpalte1, strSpalte2, strSpalte3 As String        'Columns with Attributes - Lvl 1
Dim strSpalte4, strSpalte5, strSpalte6 As String        'Columns with Attributes - Lvl 2
Dim strSpalte7, strSpalte8, strSpalte9 As String        'Columns with Attributes - Lvl 3
Dim strSpalte10, strSpalte11, strSpalte12 As String     'Columns with Attributes - Lvl 4
Dim strSpalte13, strSpalte14, strSpalte15 As String     'Columns with Attributes - Lvl 5
Dim strSpalte16, strSpalte17, strSpalte18 As String     'Columns with Attributes - Lvl 6
Dim strSpalte19, strSpalte20 As String                  'Columns with Attributes - Lvl 7
Sub LineUp()
'Makro baut einen Lineup nach gegebener Regel auf
Application.ScreenUpdating = False
'Define Constants
strZeile1 = Tabelle3.Cells(9, 4)    '1st Line Workbench
strSpalte1 = Tabelle3.Cells(39, 6)  'Machine
strSpalte2 = Tabelle3.Cells(43, 6)  'Setup Grp
strSpalte3 = Tabelle3.Cells(49, 6)  'SAB-01
'strSpalte19 = Tabelle3.Cells(47, 6) 'Material available
strSpalte20 = Tabelle3.Cells(45, 6) 'Firm Status
strSpalte4 = Tabelle3.Cells(51, 6)  'SAB-02
strSpalte5 = Tabelle3.Cells(53, 6)  'SAB-03
strSpalte6 = Tabelle3.Cells(55, 6)  'SAB-04
strSpalte7 = Tabelle3.Cells(57, 6)  'SAB-05
strSpalte8 = Tabelle3.Cells(59, 6)  'SAB-06
strSpalte9 = Tabelle3.Cells(61, 6)  'SAB-07
strSpalte10 = Tabelle3.Cells(63, 6) 'SAB-08
strSpalte11 = Tabelle3.Cells(65, 6) 'SAB-09
strSpalte12 = Tabelle3.Cells(67, 6) 'SAB-10
strSpalte13 = Tabelle3.Cells(69, 6) 'SAB-11
strSpalte14 = Tabelle3.Cells(71, 6) 'SAB-12
strSpalte15 = Tabelle3.Cells(73, 6) 'SAB-13
strSpalte16 = Tabelle3.Cells(75, 6) 'SAB-14
strSpalte17 = Tabelle3.Cells(77, 6) 'SAB-15
strSpalte18 = Tabelle3.Cells(41, 6) 'Due Date
'go to Workbench-Sheet
Tabelle1.Activate
'Build LineUp
LastRow1 = Tabelle1.Cells(Rows.Count, 1).End(xlUp).Row                       'last  _
filled Row of Sheet1
strBereich1 = Tabelle3.Cells(5, 4) & "2:" & Tabelle3.Cells(7, 4) & LastRow1  'Total  _
area of the scheduling workbench, for example. strBereich = "A2:W10"
Call SortFirmItemsOut(LastRow1, strBereich, strSpalte1, strSpalte18, strSpalte20)
strBereich = Tabelle3.Cells(5, 4) & FirstRowAfterFirm & ":" & Tabelle3.Cells(7, 4) &  _
LastRow1  'Area of the scheduling workbench after the firmed orders
Call SortMinimizeSetUp(LastRow1, strBereich, strSpalte1, strSpalte2, strSpalte3,  _
strSpalte4, strSpalte5, strSpalte6, strSpalte7, strSpalte8, strSpalte9, strSpalte10, strSpalte11, strSpalte12, strSpalte13, strSpalte14, strSpalte15, strSpalte16, strSpalte17, strSpalte18)
' --> Einsortieren der Firms noch einfügen
Call SortCrossing(FirstRowAfterFirm, strBereich1)
Application.ScreenUpdating = True
'ActiveWorkbook.Worksheets("Tabelle1").Sort.SortFields.Clear  'delete sort key from sort  _
fields
MsgBox "LineUp build finished!" & vbCrLf & vbCrLf & "SortFirmItemsOut - done" & vbCrLf & " _
SortMinimizeSetUp - done"
End Sub
Mir fehlt noch das "Richten" der Übergänge sowie später dann das Einsortieren der fixierten Aufträge...
Eigentlich brauche ich eine Idee, wie ich die Bereiche (Zeitblöcke) zum Sortieren auswählen kann. Im Prinzip wäre das eigentlich "nur" eine Schleife über alle Blöcke und darin sortiere nach Zeitblock, Attribut1 und Attribut2. Ich krieg die Blöcke nicht zu greifen, weil die ja dynamisch sind (unterschiedliche Größe, unterschiedliche Start-/Endeposition innerhalb der Spalten). Vielleicht selektierbar über die Kombination der Spalten "Machine" und "Setup Grp" (Für alle Elemente in "Setup Grp" auf Machine-A --> sortiere...) ?
Gruß
Gugelhupf
Anzeige
AW: Sortierung nach Cluster
03.11.2020 15:46:19
Matthias
Moin!
Bin mir noch nicht sicher, ob deine Sortierung schon wie gewünscht klappt. Bei den Daten im Blatt Workbench sind in Zeile 12 und 13 noch Daten der SetGroup1. Müssten die nicht nach oben zu den anderen Daten der Group 1?
Zu deinen beiden Problemen ein paar Ideen.
1. Einfügen der festen Werte.
Da du vorher einiges Individuell gefiltert hast, wird das m.E. nicht automatisch mit den Excelmitteln gehen. Da dann in einer Schleife die Werte durchgehen und schauen, wo der Wert rein muss. Dorthin dann die Zeile kopieren. Dabei brauchst du ja nur den Bereich mit der selben SetGroup durchgehen.
2. Die Übergänge anpassen.
Wenn du deine Liste soweit gefiltert hast, gehe sie noch einmal in einer Schleife durch. Dabei nimmst du immer den gewünschten Bereich (bspw. Setgroup1). Falls der 1. Werrt nicht passt, erstellst du dafür eine benutzerdefinierte LIste und sortierst den Bereich danach. Danach löscht du wieder die Liste uNd gehst zum nächsten Bereich. Für die Liste reicht es , wenn du dort nur den Buchstaben / Wert der zuerst kommen soll eingibst. Der Rest wird dann aufsteigend sortiert.
Achja, das Einfügen der festen Werte ganz am Ende. Die Übergänge vorher anpassen.
Wenn das mit den Übergängen in deiner Beispieldatei auch schon auftritt, sage mal bitte, in welcher Zeile (dort passt m.E. alles). Dann kann man das auch mal testen.
VG
Anzeige
AW: Sortierung nach Cluster
06.11.2020 08:53:09
Gugelhupf
Sorry, ich habe etwas gebraucht um herauszufinden, wie ich hier weiterposten kann :)... Erstmal Danke für die Tips.
Bin mir noch nicht sicher, ob deine Sortierung schon wie gewünscht klappt. Bei den Daten im Blatt Workbench sind in Zeile 12 und 13 noch Daten der SetGroup1. Müssten die nicht nach oben zu den anderen Daten der Group 1?
--> Nein, das ist richtig so, weil das in Spalte A eine andere Maschine ist.
--> Die Einträge in Spalte 3 und 4 sind auch ok. Die sind firm und sollen nach vorn sortiert werden.
Wenn das mit den Übergängen in deiner Beispieldatei auch schon auftritt, sage mal bitte, in welcher Zeile (dort passt m.E. alles). Dann kann man das auch mal testen.
--> Der "Fehler" tritt z. B. beim Übergang von Zeile 6 nach Zeile 7. dort sortiert EXCEL zwar prinzipiell richtig aufsteigend aber eigentlich wäre es besser die 2. Gruppe mit dem Eintrag 2 in Spalte N zu beginnen.
2. Die Übergänge anpassen.
Wenn du deine Liste soweit gefiltert hast, gehe sie noch einmal in einer Schleife durch. Dabei nimmst du immer den gewünschten Bereich (bspw. Setgroup1). Falls der 1. Wert nicht passt, erstellst du dafür eine benutzerdefinierte Liste und sortierst den Bereich danach. Danach löscht du wieder die Liste und gehst zum nächsten Bereich. Für die Liste reicht es , wenn du dort nur den Buchstaben / Wert der zuerst kommen soll eingibst. Der Rest wird dann aufsteigend sortiert.

--> Das klingt gut. So hatte ich es mir im Prinzip auch vorgestellt. Ich müsste lediglich den letzten Wert aus Spalte N vom vorangegangenen Cluster aus Spalte M nehmen und den als Startwert vorgeben. Danach kann Excel wieder aufsteigend sortieren. Wäre es ok, wenn ich da ein kurzes Beispiel anfrage? ich weiß nicht, wie ich diesen Startwert setzen kann. Ich habe mal versucht ein Makro entsprechend aufzuzeichnen:
Range("P3:P13").Select
Application.AddCustomList ListArray:=Array("Hoch", "Mittel", "Niedrig")
ActiveWorkbook.Worksheets("WorkBench").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("WorkBench").Sort.SortFields.Add Key:=Range( _
"P3:P13"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Hoch,Mittel,Niedrig", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("WorkBench").Sort
.SetRange Range("P2:P13")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
kann ich dieses Application.AddCustomList ListArray:=Array("Hoch", "Mittel", "Niedrig")
auch in sort verwenden? Das wäre ja die Lösung :)...
Gruß
Gugelhupf
Anzeige
AW: Sortierung nach Cluster
03.11.2020 09:02:46
Gugelhupf
...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige