lösung (VBA?Solver?) für clevere Sortierung/Zusammenfassung

[Ego]

Hallo h...,


A) zu:

Wie kann bevor die Gruppen überhaupt bestehen/definiert sind, ...

Ich hatte geschrieben, dass ich zu Beginn für jede auftretende Kombination eine Gruppe mit einem Element bilde (im Beispiel 77).

B) zur Farbgebung:
Die Farbgebung ist mit bedingter Formatierung realisiert. Du kannst in den Bedinungen nur den letzten Teil der UND-Funktion nutzen.

[Käpt'n Blaubär]

Hallo Case,

was soll ich sagen? Man wird alt wie 'ne Kuh, und lernt immer noch dazu.

Zu meiner Entschuldigung vielleicht; die letzten fünf Jahre war ich gesundheitsbedingt
excelmäßig eher Offline. Da habe ich Einiges auf- und nachzuholen.

Vielen Dank für Deinen Hinweis.
Aber die Zelle bleibt trotzdem dort wo sie ist. Nur der Zellinhalt ist nicht mehr vor Ort.
Also Kopieren plus Einfügen plus Zellinhalt des Ursprungs löschen = .cut
Oder habe ich noch was nicht verstanden bzw. überlesen?

[halihalöle]

Hallo Helmut,

die VBA-Lösung kann mein problem leider nicht lösen. Ich habe mal paar Gruppierungen überprüft (siehe angehängte Excels):
- 5 Gruppierungen (Art <> 1)
- 6 Gruppierungen (Art <> 1)
- 6 Gruppierungen (Art = 1)

Bei 6 Gruppierungen (Art = 1) macht die Lösung z.B. auch gar keinen Sinn, da die blauen und grauen Gruppierungen exakt die gleichen Spalten abdecken. Ist also die gleiche Gruppierung.
Bei 5 Gruppierungen (Art <> 1) und 6 Gruppierungen (Art <> 1) sind die Lösungen leider nicht gut/sinnvoll. Zum Vergleich: Das Ergebnis bei 6 Gruppierungen (Art <> 1) sind 942 Zellen (W12), die in Gruppierungen enthalten sind, aber keinen Inhalt haben. Meine "manuelle Lösung" gibt für 6 Gruppierungen 448 Zellen aus (siehe Tabellenblatt "mögliche manuelle Lösung").

Ich habe die farblichen Markierungen nachtäglich auch nochmal so angepasst, dass man die Gruppierungen auch vollständig erkennt (immer alle enthaltenen Spalten markiert).

Hoffentlich ist es etwas verständlicher geworden und vielleicht kannst du mir ja nochmal helfen

Danke trotzdem!

[WillWissen]

Hallo,

du brauchst nicht immer vollzitieren. In der Regel reicht es, wenn du einem bestimmten Helfer antworten möchtest, ihn mit seinem Namen/Usernamen anzusprechen.

[Ego]

Hallo h...,

da hab ich wohl etwas falsch verstanden (hatte mich auch schon über die Farbgebung deiner manuellen Lösung gewundert).

Ist es richtig, dass die Anzahl der Maluszellen minimiert werden sollen?
Was bedeutet "keinem Datenpaket zugeordnet in deiner Lösung?

Ich komme vielleicht morgen dazu einige neue Überlegungen zu machen.

Zur Zeit habe ich noch keinen Algorithmus wie ich das Problem angehen könnte. Hast du vielleicht eine Idee?

[halihalöle]

Hallo Helmut,

genau, die Anzahl der Maluszellen soll minimal sein.
"Keinem Datenpaket zugeordnet" heißt: Ich wollte fünf Datenpakete (Gruppierungen) machen. Nachdem ich diese fünf Gruppierungen gemacht hatte, fiel mir auf, dass ich die zwei roten Datenfelder (Zeilen) noch übrig hatte und keiner Gruppierung zuordnen konnte, weshalb die zwei roten Datenfelder/Zeilen sozusagen die sechste Gruppierung darstellen.

Bezüglich des Algorithmus:
- es müssen ja irgendwie alle kombinatorischen Möglichkeiten durchlaufen werden
   - Anzahl der Möglichkeiten: (2^13)*(2^13-1)*(2^13-2)*(2^13-3)*(2^13-4) = 8192*8191*8190*8189*8188 =... ?
   - Aber wie geht man vor, um die Möglichkeiten durchzugehen?
- Ziel = Minimum der Maluszellen (=Maximierung von nicht farbigen Zellen)
- ...

Ziemlich tricky.. Ich schlafe auch nochmal eine Nacht drüber.

Viele Grüße!

[Gast 123]

Hallo

 die Anzahl der Maluszellen soll minimal sein

Frage:  was sind Maluszellen, nie gehört!!

Dafür mal ein unkeonventioneller Lösungsvorschlag, in der Hoffnung das er als verblüffend einfache Lösung weiterhilft. Ich habe per Makro eine Binaer Auswertung gemacht und nach Spalte R sortiert. Vorher muss in Spalte A eine Lauf-Nummer von 1-202 stehen damit man die Daten wieder zurück Sortieren kann! 

Dann sieht man ganze Blöcke von Daten, die man manuell weiter auswerten muss.  Hilft das weiter eine praktikable Lösung zu finden???

mfg  Gast 123

Code:

Option Explicit
Const FeldBereich = "B3:B202"
Const SortBereich = "A3:R202"


Sub BinaerCode_Auswertung_()
Dim AC As Range, Asw As String, j As Integer
Worksheets("Ausgangssituation").Select
  'Lauf-Nummer muss in Spalte A vorhanden sein wegen Sortier Routine!!
  If Cells(Rows.Count, 1).End(xlUp).Row < 200 Then
     MsgBox "Spalte A hat keine Lauf-Nummer - Abbruch!!": Exit Sub
  End If
  For Each AC In Range(FeldBereich)
     Asw = Empty
     For j = 1 To 13
        If AC.Offset(0, j + 2) <> "" Then
           Asw = Asw & "1,"
        Else
           Asw = Asw & "0,"
        End If
     Next j
     Asw = Left(Asw, Len(Asw) - 1)
     Cells(AC.Row, "R") = Asw
  Next AC
  Call Sortieren_SpalteR
End Sub


Sub Sortieren_SpalteR()
   Range(SortBereich).Select
   ActiveWorkbook.Worksheets("Ausgangssituation").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("Ausgangssituation").Sort.SortFields.Add Key:=Range _
       ("R3"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
   With ActiveWorkbook.Worksheets("Ausgangssituation").Sort
       .SetRange Range(SortBereich)
       .Header = xlGuess
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
End Sub



Sub Zurück_Sortieren_SpalteA()
   Range(SortBereich).Select
   ActiveWorkbook.Worksheets("Ausgangssituation").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("Ausgangssituation").Sort.SortFields.Add Key:=Range _
       ("A3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   With ActiveWorkbook.Worksheets("Ausgangssituation").Sort
       .SetRange Range(SortBereich)
       .Header = xlGuess
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
End Sub

[halihalöle]

Hallo Gast 123,

ich verstehe zwar nicht ganz genau, wonach in Spalte R sortiert wird, jedoch hilft es nicht wirklich weiter. An sich habe ich auch schon verschiedene Sortierungen durchgeführt und dann weiter versucht manuell Gruppierungen zu erstellen. Jedoch ist es aufgrund der hohen Anzahl an Datenfelder und verschiedener Kombinationen sehr schwierig, dann eine gute Lösung bekommen.
Daher wäre ein "Programm" sehr hilfreich.
Maluszelle ist in diesem Thread definiert als eine Zelle, die in der zugehörigen Gruppierung einer Spalte zugeordnet ist, jedoch für diese Spalte keinen Wert besitzt (zum Beispiel im Tabellenblatt "mögliche manuelle Lösung" die Zelle N24 oder O92. Diese Zellen gilt es zu minimieren.

Vielen vielen Dank trotzdem! Ich bin für jede Hilfe/Hinweis dankbar.

[Ego]

Hallo h...,

A) realisiert: Optimierung mit dem Solver über Zuordnung der [b][b]Kombinationen zu [/b]Gruppen[/b]
Ich habe gestern einmal das Problem so aufbereitet, dass es mit dem Excel-Solver eine "möglichst gute Gruppenzuordnung" ermittelt werden kann (siehe Anlage).


1. Beschreibung:
a) Buton "tuwat"
Über den Button  wird nun nur noch die Matrix mit den auftretenden Kombinationen (Spalten V-AK) erstellt. Hierbei werden vorerst alle Kombinationen einer Gruppe zugeordnet.

b) Malusberechnung
In der Zelle W3 berechne ich über eine benutzerdefinierten Funktion die Anzahl der "Maluszellen" für die aktuelle Gruppenzuordnung (W6:W82).
Falls sich die Anzahl der auftretenden Kombinationen ändert, mss die Formel angepasst werden.


c) Solverparameter
Ich habe dann für den Solver die Parameter so eingestellt, dass der Maluswert (W3) minimiert werden soll und die Gruppenzuordnungen in den Grenzen 1-6 variiert werden können.
Falls sich die Anzahl der auftretenden Kombinationen ändert, müssen die Parameter angepasst werden.
Als Lösungsmethode muss der Evolutionäre Algorithmus gewählt werden.

2. Nachteile
a) nur 101 unterschiedliche Kombinationen möglich
Da der Excel-Solver nur 100 änderbare Zellen erlaubt dürfen nur 101 unterschiedliche Kombinationen auftreten.
b) zu grosser Suchraum > keine sehr guten Ergebnisse
Ich habe den Solver etliche Male (auch mit unterschiedlichen Solver-Optionen) für 6 Gruppen durchlaufen lassen.
Das beste Ergebnis war ein Maluswert von 468 (Spalte AT).
Sehr häufig traten Maluswerte um 480 auf.
Da du in deiner manuellen Zusammenstellung schon einen (wie du geschrieben hast noch nicht optimalen) Maluswert von 441 erreichst sind die Ergebnisse wohl nicht so gut.
Ein Grund ist die unvorstellbare Grösse des Suchraumes (6^77 =~ 10^60 mögliche Gruppenzuordnungen), so dass Solveralgorithmus sich in lokalen Minima verfängt.

B) alternativ möglich: Optimierung mit dem Solver über Zuordnung der Spalten zu Gruppen

Der Aufwand der Berechnung des Maluswertes wäre ein zwar wenig aufwändiger (Faktor <6) , da ich erst ermitteln muss in welcher Gruppe die einzelne Kombination am besten ist. In den beiden oben genannten Nachteilen treten aber massgebliche Verbesserungen auf.

a) Da ich in jeder der 6 Gruppen "nur" 13 Spalten habe, habe ich unabhängig von den auftretenden Kombinationen maximal 78 änderbare Werte für den Solver.

b) Der Suchraum ist sehr sehr viel kleiner.
Anstatt 10^60  habe ich wie du aufgezeigt hast maximal 10^24 (~ 2^78 = 2^(13*6) ) mögliche Zuordnungen.
Mann kann den Suchraum auch weiter verkleinern:
Es gibt in deinem Beispiel eine Kombination mit 12 Spaltenzuordnungen. Für die Gruppe, in der diese Kombination sein muss, habe ich nur noch die Wahl, ob auch die 13. Spalte zur Gruppe gehört oder nicht. Daher habe ich "nur" noch 10^20 (~2^(13*5+1)) mögliche Zuordnungen.

Eine Idee könnte sein folgende 4 Kombinationen in unterschiedliche Gruppen einzuordnen und damit in den Gruppen die Anzahl der änderbaren Spalten zu reduzieren:
1. Die Kombination die zwischen 10 und 13 Spaltenzuordnungen hat und hierbei am häufigsten auftritt.
2. Die Kombination die zwischen 7 und 9 Spaltenzuordnungen hat und hierbei am häufigsten auftritt.
3. Die Kombination die zwischen 5 und 6 Spaltenzuordnungen hat und hierbei am häufigsten auftritt.
4. Die Kombination die zwischen 3 und 4 Spaltenzuordnungen hat und hierbei am häufigsten auftritt.
Der Suchraum hat dann "nur" noch 10^14 (~ 2^45) mögliche Zuordnungen

[halihalöle]

Hallo Helmut,

richtig richtig gut! Kann dir gar nicht genug danken für deine Hilfe!
Eine Frage habe ich gerade, die du mir hoffentlich kurz beantworten kannst:
Du schreibst, dass die Parameter angepasst werden müssen, wenn sich die Anzahl der auftrendenden Kombinationen ändert. Wo und wie kann ich die ändern?

Viele Grüße!