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

[Ego]

Hallo h...

Wie in meinem letzten Beitrag geschrieben ist eine Optimierung über "Zuordnung der Spalten zu Gruppen" erfolgreicher als mein bisheriger Vorschlag.

Ich hab das einmal umgesetzt und bekomme im Beispiel für 6 Gruppen Ergebnisse von fast immer 444 Maluszellen.

In der Anlage vorab eine Datei in der ich das Ergebnis über bedingte Formatierung auch eingefärbt habe.

Vielleicht komme ich heute Abend noch dazu die etwas aufwendigere Handhabung zu beschreiben, ansonsten auf jeden Fall morgen.


ps.
Hab gerade einmal deine manuelle Lösung angeschaut. Du hast zumindest etliche male vergessen die Spalte 13 grün einzufärben, so dass dein manuelles Ergebnis manuell nachgerechnet bei 500 Maluszellen liegt. Dann sind 444 doch kein schlechtes Ergebnis.

[Ego]

Hallo h...,

hier einmal ein Versuch einer ausführlichen Beschreibung:

A) Ablauf
1. Zusammenstellung der Solverparameter (Button "tuwat")
2. Überprüfen ob die Verweise in den beiden benutzerdefinierten Funktionen ("malus" zur Zeit Zelle Z19 und "Malus2" zur Zeit Zelle W2) angepasst werden müssen.
3. Überprüfen, ob die Solverparameter angepasst werden müssen.
4. Aufrufen des Solvers mit der Lösungsmethode "EA"
5. Ausweisen der Gruppenzuordnung (Button "Ergebnis anzeigen")


B) Grenzen
Ich sehe hier nur die Begrenzung des Excel-Solvers, der nur 100 änderbare Zellen erlaubt.
Im Normalfall heist es, dass das Produkt von Gruppen und Spalten <= 100 sein muss.
Falls das Produkt aber ein wenig über 100 ist, kann man nach der Zusammenstellung der Optimierungsparameter (Button "tuwat") im Bereich "Gruppen", abhängig von den auftretenden Varianten, einige Zellen mit - 1 belegen. Für diese Gruppen wird diese Spalte immer eingetragen und man kann dem Solver jeweils eine änderbare Zelle weniger übergeben.

C) Beschreibung der Elemente

1) benannte Zellen
Die Programme benutzen 5 benannte Zellen ("Anzahl", "Tab","Parameter","Gruppen" und "Varianten").
Bei den letzten vier ist zu beachten, dass sie den Anfang von Bereichen kennzeichnen, die von den Programmen mit ".CurrentRegion" ausgewertet werden. Das bedeutet, dass die Zellen direkt über, unter, vor und hinter den Bereichen leer bleiben müssen. (Also auch keine zusätzlichen Überschriften direkt über den Bereichen.)
a) "Anzahl"
hier wird die Anzahl der gewünschten Gruppen festgelegt für die das Programm "tuwat" die Parameter für die Funktion "Malus2" zusammenstellt.
b) "Tab"
Ab hier beginnen die auszuwertenden Datenfelder. Der Bereich muss mehr als eine Zeile und mehr als 3 Spalten haben. Die erste Zeile und die ersten drei Spalten werden nicht berücksichtigt. Es wird nur geprüft, ob die Zelle leer ist oder nicht.
c) "Parameter"
Die Zellen unterhalb werden vom Programm "tuwat" gelöscht und dann mit der neuen Anzahl an Einsen für die Parameter des Solvers überschrieben.
d) "Gruppen"
Auch die Zellen des Bereiches werden vom Program "tuwat" gelöscht und mit Einsen überschrieben. Falls du für einzelne Gruppen feste Zuordnungen geben möchtest, kannst du anschliessend vor dem Aufrufen des Solvers einzelne Zellen mit -1 überschreiben und die Anzahl der an dem Solver zu übergebenden Parameter kürzen.
Das Programm "Ergebnisse anzeigen" trägt die Ergebnisse des letzten Solveraufrufes ein, da benutzerdefinierte Funktionen keine Zellen beschreiben können.
e) "Varianten"
Das Programm "tuwat" löscht den Bereich und überschreibt ihn neu. In der zweiten Spalte des Bereiches, in dem die Gruppenzuordnung ist wird eine 1 eingetragen.
Das Programm "Ergebnisse anzeigen" trägt in die zweite Spalte die Ergebnisse des letzten Solveraufrufes ein, da benutzerdefinierte Funktionen keine Zellen beschreiben können.

2. benutzerdefinierte Funktionen
a) "Malus" (zur Zeit Zelle Z19)
Die Funktion ist eigentlich nicht mehr notwendig. Sie zeigt erst nach dem Aufrufen des Programmes "Ergebnisse anzeigen" den gleichen Wert wie die Funktion "Malus2" an. Falls sich die Anzahl der auszuwertenden Spalten oder die Anzahl der auftretenden Varianten (zur Zeit 77) geändert hat, muss der Parameterbereich angepasst werden.
b) "Malus2" (zur Zeit Zelle W2)
Die Funktion ermittelt für den Solver den zu minimierenden Wert.
Falls sich die Anzahl der auszuwertenden Spalten, die Anzahl der Gruppen  oder die Anzahl der auftretenden Varianten (zur Zeit 77) geändert hat, müssen die Parameterbereiche angepasst werden.

3. Programmaufrufe
a) "tuwat"
Es werden die Daten des Bereiches "Tab" ausgewertet und die Daten der Bereiche "Parameter", "Gruppen" und "Varianten" für den Solver aufbereitet.
Zusätzlich wird in der Spalte hinter dem Bereich "Tab" (zur Zeit Spalte T) die Nummer der Variante eingetragen.
b) "Ergebnisse anzeigen"
Da die benutzerdefinierte Funktion, die während des Solverlaufes immer wieder zur Berechnung des aktuellen Ergebnisses aufgerufen wird, keine Zellen beschreiben kann, wird dieses Programm zur Ausweisung des Solverergebnisses benötigt. Es trägt in dem Bereich "Gruppen" die aktuelle Kennungen und in dem Bereich "Varianten" die aktuelle Gruppenzuordnung ein.

4. Solverparameter
a) Parameter
Hier werden die Zellen des Bereiches "Parameter" als änderbare Zellen mit der Nebenbedingung "binär" eingetragen.
Wenn sich die Anzahl der Spalten oder Gruppen ändert müssen sie angepasst werden.
b) Methode
Es muss die Methode "EA" ausgewählt werden.

5. Farbgebung
Die Farbgebung erfolgt über bedingte Formatierung hierzu wird neben der zugeordneten Gruppe (Formel in Spalte T) auch der Bereich "Gruppen" benötigt.
Falls sich die Anzahl der Variantenüber 123 ist, muss die Formel in Spalte T angepasst werden.
Falls sich die Anzahl der Spalten erhöht (zur Zeit 13), müssen die bedingten Formatierungen angepasst werden.
Falls sich die Anzahl der Gruppen erhöht ( zur Zeit 6), muss eine neue Bedingung hinzugefügt werden.
Falls sich die Anzahl der Datenfelder erhöht, müssen die Formatierungen übernommen werden.

[Ego]

Hallo h...,

zu:

Eine Frage habe ich noch: Die Binärzahlen in Spalte W: Wann sind die 1 und wann sind die 0?

Die Zellen werden erstmalig durch die Funktion "tuwat" mit 1en vorbelegt.
Für den Solver werden diese Zellen als änderbare Zellen mit der Nebenbedingung binär definiert.
Dh. der Solver verändert zigtausend mal die 0en und 1en und prüft ob sich der Wert der Ergebniszelle verbessert hat. Die Kombination, die während des Solverlaufes das beste Ergebnis hat bleibt zum Ende eingetragen.

Ich hätte es auch ohne diese Spalte umsetzen können, indem ich die Zellen des Bereiches "Gruppen" als änderbare Zellen definiert hätte.
Ich habe die zusätzlichen Zellen genutzt, damit der Solver (der ja auf 100 änderbare Zellen begrenzt ist) auch noch genutzt werden kann, wenn das Produkt AnzahlSpalten*AnzahlGruppen ein wenig über 100 ist.
Wie im letzten Beitrag (unter "B) Grenzen") geschrieben müssen dann im Bereich "Gruppen" entsprechend viele Zellen mit -1 vorbelegt werden.
Die benutzerdefiniert Funktion "Malus2" liest zu Beginn den Bereich "Gruppen" ein und ersetzt hintereinander alle Zahlen >=0 mit den entsprechenden Werten, die der Solver in Spalte W eingetragen hat.

[halihalöle]

Hallo Helmut,

wenn ich den Solver jetzt also mit 10 Gruppen und 13 Spalten (Produkt = 130) laufen gelassen habe, ohne Zellen mit -1 vorzubelegen, was bedeutet das?
Ich habe einen Maluswert von 260 erhalten.

Danke danke danke..

Viele Grüße!

[Ego]

Hallo h...,

zu:

... , was bedeutet das?

Das bedeutet, für deinen Optimierungsfall nichts.

Aber die Grenze, die ich aus dieser MS-Seite habe: https://support.office.com/de-de/article...9d656771c3 , gilt zumindest nicht für binäre Variablen. Hier ist die Obergrenze 200. Alle meine Bemerkungen zu der Grenze gelten erst ab 200 und treten wahrscheinlich in deinen Anwendungen nicht auf.