Office: (Office 2016) Stärkste Kombination berechnen/ausgeben

Hallo,

ich bräuchte Hilfe um mir eine automatische Berechnung der stärksten Teamkombination nach ihren jeweiligen Fähigkeiten ausgeben zu lassen.

Und zwar habe ich in den Zeilen die jeweiligen "Personen", und in den Spalten ihre jeweiligen Fähigkeiten verteilt.
Bei den Fähigkeiten kann es natürlich Dopplungen usw. geben. Am Ende soll mir aber unten in den Spalten B14 bis C23 die entsprechend stärkste Kombination der Personen mit ihren Fähigkeiten ausgegeben werden.
In der Datei habe ich versucht das stärkste Team entsprechend zu kennzeichnen (dicker Rahmen) und unten in die Auflistung zu schreiben. (Ich hoffe die 470 in Summe sind auch das höchste)

Fragen versuche ich natürlich so gut ich kann zu beantworten. Vielen Dank schonmal an jeden der sich das hier überhaupt durchgelesen hat. :)
Gruß
Fitti

 

Tabelle1

	B	C	D	E	F	G	H	I	J	K
2	10	20	30	40	50	50	35	40	25	15
3	15	25	30	45	50	35	30	45	30	20
4	20	20	35	40	30	40	35	35	35	25
5	25	25	35	40	20	15	10	35	35	25
6	45	25	15	40	35	30	15	15	40	30
7	50	45	40	35	25	20	20	20	50	50
8	35	25	15	25	40	25	20	35	45	45
9	25	30	25	30	15	30	25	45	15	25
10	15	50	45	25	15	60	25	25	25	30
11	30	35	60	50	45	20	15	30	40	35

die bedingte Formatierung Fülleffekte, Symbole, Datenbalken
werden in dieser Tabelle nicht dargestellt
Schriftart wird in dieser Tabelle nicht dargestellt

Bedingte Formatierung Haupttabelle 1
Wird angewendet auf	Nr. Bed. Regeltyp	Operator	Formel1	Formel2	Format Schrift Füll-farbe	Unterstrichen	Schrift- farbe	Muster	Muster-farbe	Typ	Bereich
$B$2:$K$11	01.Bed.: Formel ist		=B2=KGRÖSSTE($B2:$K2;1)		255					2	$B$2:$K$11
$B$2:$K$11	02.Bed.: Formel ist		=B2=MAX(WENN($B2:$K2<>MAX($B2:$K2);$B2:$K2))		12611584					2	$B$2:$K$11

Bedingte Formatierung Haupttabelle 2					oberere/unterer Bereich
Wird angewendet auf	Nr. Bed.	Format Zelle	Anhalten	Auswahl	Anzeige	Anzahl	Durchschnitt	Typ	Bereich
$B$2:$K$11	01.Bed.		Falsch					2	$B$2:$K$11
$B$2:$K$11	02.Bed.		Falsch					2	$B$2:$K$11

Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.

Füllfarben und Muster
Zelle	Rot	Grün	Blau	Color	Muster	Farbe
B6:B7, D11, E4:F3, G2, G4, G10, I9, J7:K8	106	168	79	5220458
C7, C9:E9, D4:E3, E11, F8, G9, H4, I2:I4, I5:J6	255	217	102	6740479

Zellen mit Füllfarbe automatisch werden nicht dargestellt

Schriftformate
Zelle	Rot	Grün	Blau	Color	Stil	Unterstreichung	Effekte	Durchgestrichen	Schriftart
B2:K11	0	0	0	0					Arial

Zellen mit Schriftformatierung automatisch werden nicht dargestellt

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip

http://Hajo-Excel.de/tools.htm

Add-In-Version 25.23 einschl. 64 Bit

XHTML-Tabelle zur Darstellung in Foren, einschl. Funktion zum Zurücklesen in eine Excel-Tabelle

 

Guten Morgen Hajo,

erstmal Danke für deine Antwort, leider ist das nicht genau das was ich brauche, zumindest wenn ich es richtig verstehe. In deiner Lösung geht es glaub ich nur um die bedingte Formatierung oder?

Was ich aber bräuchte wäre die Berechnung des stärksten möglichen Teams. Also unten in B14:B23 die Personen und in C14:C23 die werte der entsprechenden Fähigkeit.

Als Beispiel vielleicht Fähigkeit 9 + 10
Hier sind die Personen 6 u. 7 die stärksten mit den werten 50/45.
Dann sollte die Berechnung für Fähigkeit 9 + 10 ergeben das dort bei Fähigkeit 9 einmal die Person 6 steht, und bei Fähigkeit 10 die Person 7 so dass für die Team-Summe schonmal 95 Punkte zusammen kommen.
Und diese Berechnung müsste für jede Fähigkeit geschehen, so das am Schluss in C24 die höchst mögliche Summe erscheint, OHNE das eine Person doppelt genannt wird.

Ich weiß leider nicht wie ich das besser beschreiben könnte, und hoffe das es weiterhilft.
Auf Fragen werde ich natürlich versuchen so gut ich kann zu antworten. :)

Gruß
Fitti

 

Hallo Fitti,

Gut dan habe ich die Aufgabe falsch verstanden. Ich habe mich an Deine Markierung gehalten. Das war dann wohl falsch.
Ich bin dann raus.

Gruß Hajo

 

Ok, Schade. Aber bist du jetzt raus wegen der Markierungen oder weil meine Anfrage nicht möglich ist?
Vielen Dank trotzdem *für den* Lösungsansatz.
Gruß
Fitti

Edit in *

 

Hola,
ich verstehe es nicht wirklich. Warum ist bei Fähigkeit 1 Person5 aufgeführt, obwohl Person6 einen höheren Wert hat?
Hängen die Fähigkeiten irgendwie zusammen? Du hast als einziges Beispiel nur Fähigkeit 9 und 10 erwähnt.
Gruß,
steve1da

 

Hallo Steve,
bei Fähigkeit 1 ist Person 5 aufgeführt weil diese Person hier (nach Person 6) den nächst höchsten Wert hat.
Person 6 ist für Fähigkeit 9 eingetragen weil sie dort ebenfalls den höchsten Wert (50) hat.
So ergeben die beiden Personen in Summe einen Wert von 95 für die beiden Fähigkeiten.

Wenn hier bei Fähigkeit 1, Person 6 mit den 50 gewählt würde,
dann wäre bei Fähigkeit 9, Person 5 mit dem Wert von 40 zu wählen,
da Person 7 (welche bei Fähigkeit 9 den 2. höchsten Wert mit 45 hat)
schon bei Fähigkeit 10 gewählt werden muss.
Hier wäre dann für die 3 Fähigkeiten eine Summe von 135 statt wie im Beispiel 140.

Also jede Person hat alle 10 Fähigkeiten in verschieden hoher Ausprägung, aber jede der 10 Fähigkeiten darf nur einmal besetzt werden. Die Krux ist es jetzt Excel ausrechnen zu lassen welche "Aufstellung" (ohne Doppelbesetzung) die beste ist.
Es können natürlich (und müssen wahrscheinlich) gern diverse Hilfsspalten/Tabellen erstellt werden.

Gruß
Fitti

 

Person 6 ist für Fähigkeit 9 eingetragen weil sie dort ebenfalls den höchsten Wert (50) hat.
Und warum nicht Fähigkeit 10, dort ist der Wert doch auch 50?

 

Das wäre in dem Fall tatsächlich egal.
Fähigkeit 9 - Person 6 - Wert 50
Fähigkeit 10 - Person 7 - Wert 45
Summe 95

Fähigkeit 9 - Person 7 - Wert 45
Fähigkeit 10 - Person 6 - Wert 50
Summe 95

 

Hallo,

bei diesem Problem handelt es sich um ein Optimierungsproblem: alle 10 Personen sollen derart den 10 Fähigkeiten zugeordnet werden (anhand der Tabelle), sodass die Fähigkeitensumme dieser 10 Personen maximal wird.

Das wäre an und für sich ein klassisches Problem für den Excel-Solver, der schon seit langer Zeit als Add-In in Excel aktivierbar ist. Mein Problem bei der Umsetzung im Excel-Solver ist, wie man die 10! = 3.628.800 Permutationen der 10 Personen (zu ihren Fähigkeitswerten) dort implementiert. Denn diejenige Permutation bzw. Permutationen, die die maximale Fähigkeitensumme liefert/liefern, stellt die Lösung(en) dar.

 

Hallo,

jetzt habe ich es geschafft, im Excel-Solver die permutierende Einstellung für die 10 Primärvariablen hinzubekommen, sodass der Solver ohne Protest mit korrektem Resultat die Optimierung durchgeführt hat:

 

Hallo Anton,
deine Lösung sieht wirklich sehr gut aus.
Ich habe jetzt versucht sie in meiner Tabelle entsprechen einzubauen.
Den Solver habe ich "installiert", und nach anfänglichen Schwierigkeiten auch eingestellt bekommen.
Funktioniert gut, vielen Dank dafür. :)

Würde das mit diesem Solver auch funktionieren wenn sich die Personenanzahl erhöht?
Also sprich, eine Berechnung der 10 besten Fähigkeiten/Personen aus 15 oder 20 Personen?

Vielen Dank nochmal.
Gruß
Fitti

 

Hallo,

Natürlich! Es müssten mindestens die Bereichsangaben (für das Ziel, für die Variablenzellen bzw. für die Nebenbedingungen) angepasst werden und ebenso auch die Tabelle der Personen/Fähigkeiten-Aufschlüsselung.

Man muss dabei aber bedenken, dass im schlimmsten Fall nicht bloß
Permutationen 10! = 3.628.800 vorliegen, sondern
Permutationen 15! = 1.307.674.368.000 bzw.
Permutationen 20! = 2.432.902.008.176.640.000 (=2,43 Trillionen) anfallen.
Das ist die Anzahl an unterschiedlichen Anordnungen der Personen 1, 2, 3, ..., 19, 20 in den Variablenzellen (=Primärvariablen), die theoretisch durchprobiert werden müssten, ob doch nicht ein noch höherer Zielwert in der Zielzelle gefunden werden kann.

 

Hallo,

ich habe mir das mal mit 15 Personen eingestellt zum testen. Leider funktioniert die Berechnung nicht.
Er zieht weiterhin nur die ersten 10 Personen in die Berechnung ein.

Also ich habe die Tabelle um 5 Personen erweitert, und die INDEX Formel angepasst auf die jetzt 15 Personen. Außerdem auch auf Fakultät(15) geändert.
Im Solver ist ja nichts zu ändern da sich die untere "Ausgabetabelle" ja nicht verändert.
Es bleiben ja weiterhin 10 Fähigkeiten, denen die 10 stärksten Personen aus den jetzt 15 Personen zugeordnet werden sollen.

 

Hallo,

genau da liegt höchstwahrscheinlich das Problem:

1) Bisher hattest du eine 10x10-Person/Fähigkeits-Zuordnungstabelle, jetzt wird das mindestens eine 15x10 bzw. 15x15 Tabelle. Du wirst also auf alle Fälle mehr Zeilen benötigen wie bisher.

2) Wegen 1) wird der Bereich für die Primärvariablen (bisher 10: B14:B23) um mindestens 5 Zeilen nach unten verschoben, wobei sich neben diesem Bereich der Bereich für die Zielfunktion (bisher C14:C23) zusammen mit der maximalen Zielwert-Zelle (bisher C24) befand. Dabei greifen die Index-Formeln der Zielfunktion auf die eigentliche Tabelle mit den Nebenbedingungen (bisher B2:K11) zu.

3) Diese 4 fett geschriebenen Ausdrücke sind Fachausdrücke aus dem mathematischen Teilgebiet der "linearen Optimierung", das sich erst nach dem 2.Weltkrieg aufgrund militärischer Logistikprobleme entwickelt hat.
Die Primärvariablen können dabei beliebige Werte annehmen, solange/soweit sie die Nebenbedingungen einhalten. Es wird dann diejenige Wertekombination unter den Primärvariablen gesucht, die die Zielwert-Vorgabe (=maximaler bzw. minimaler Zielwert) erfüllt. In unserem Fall ist das "maximaler Zielwert".
In unserem (zuerst genannten) Fall handelt es sich ferner um ein diskretes Optimierungsproblem: die Primärvariablen dürfen nur ganzzahlige Werte annehmen (deshalb die Nebenbedingung: $B$14:$B$23=AllDifferent), wobei diese Nebenbedingung noch weiter einschränkt: Da es sich hier um 10 Zellen handelt, liegen somit 10 Primärvariablen vor, deren Werte aus der Menge {1, 2, 3, ..., 9, 10} genommen werden müssen und die alle unterschiedliche Werte haben müssen. Somit wird vermutlich das Add-In mit eben dieser Wertzuweisung starten, wenn man auf den "Lösen"-Button klickt. Durch Vertauschen (=Permutieren) dieser Werte untereinander innerhalb der Primärvariablen wird so diejenige Primärvariablen-Wertekombination ermittelt, die den maximalen Zielwert liefert.
Welches Verfahren dabei Microsoft mit der Lösungsmethode "EA - Evolutionärer Algorithmus" implementiert hat, weiß ich nicht (weil dies vermutlich auch nicht öffentlich dokumentiert sein wird). Mit letzter Sicherheit wird man dabei vermutlich erst dann wissen, welche Primärvariablen-Kombination den maximalen Wert liefert, wenn alle Permutationen (10! = 3.628.800) durchprobiert sind. Diese Lösungsmethode bricht das Durchprobieren jedoch ab, wenn eine gewisse Zeit lang sich keine Veränderung beim (bisher maximalen) Zielwert ergeben hat.

4) Dein zuerst präsentiertes Problem sah vor, 10 Personen genau 10 Fähigkeiten zuzuordnen. Durch Permutieren dieser 10 Personen war die maximale Zielfunktion/Zielwert zu ermitteln. Im Excel-Solver kann man dies erreichen über die AllDifferent-Nebenbedingung.
Dein jetziges Problem ist dem gegenüber komplexer: Es bedeutet, dass 15 Personen den 10 Fähigkeiten zugeordnet werden müssen. 5 Personen sind bei jeder Zielfunktion/Zielwert-Ermittlung auszuscheiden und die restlichen 10 Personen dafür heranzuziehen, was insgesamt 15!/5! = 10.897.286.400 Kombinationen/Permutationen ergibt, die auf Optimalität zu testen wären. Ich weiß auch nicht, wie diese Konstellation einzugeben wäre in den vorliegenden Excel-Solver. Ich vermute, dass dies dessen Möglichkeiten überfordert.

5) Der vorliegende Solver wurde offenbar nicht von Microsoft erstellt, sondern von der Firma Frontline Systems, Inc, wo detailierte Hilfe und Information zu beziehen ist unter folgendem Link:
Solver-Hilfe unter www.solver.com.
Der Excel-Solver stellt offenbar eine abgespeckte (Basis)-Version der dort vorhandenen Optimierer dar, um den Mund wässrig zu machen auf mehr.

6) Die Angabe der Anzahl an Permutationen bewirkt im Excel-Solver nichts, ich habe diese Zahl nur angeführt, um den Umfang des Problems deutlich zu machen.