Clever-Excel-Forum

Hallo ihr Lieben,

ich habe folgendes Problem:

Ich habe eine Auswertung einer Studie über eine Sportmannschaft vorliegen. in Spalte A befinden sich 50 Spielernamen denen in Spalte B bis Spalte L Positionen mit entsprechenden zugeordnet sind. Sprich jedem Spieler sind 11 Werte zugeordnet. 

Jetzt soll ich eine Excel Tabelle entwerfen, die automatisch berechnet, welche 11 Spieler die Mannschaft wählen sollte um bestmöglich aufgestellt zu sein. Ausgegeben werden sollen die Namen der 11 Spieler die zusammen die bestmögliche Kombination ergeben.


Ich bin leider noch Excel Anfänger und baue daher auf eure Hilfe!  
.. andernfalls bleibt mir nichts anderes als die 550 verschiedenen Möglichkeiten so zu addieren 

BESTEN DANK IM VORAUS!

Und wir dürfen deine Datei erstellen ?

 nein, so war das nicht gemeint. 
Mir wäre sehr geholfen, wenn ihr mir zeigen könntet wie man an solche Problemstellungen herangeht, welche Funktionen man benötigt und welchen Zweck sie erfüllen. Im Idealfall wäre ich gern in der Lage solche Fälle in Zukunft selbst lösen zu können.

---

(30.08.2021, 12:03)trentadue schrieb: [ -> ] nein, so war das nicht gemeint. 
Mir wäre sehr geholfen, wenn ihr mir zeigen könntet wie man an solche Problemstellungen herangeht, welche Funktionen man benötigt und welchen Zweck sie erfüllen. Im Idealfall wäre ich gern in der Lage solche Fälle in Zukunft selbst lösen zu können.

Ich hatte es erst mit der Max Funktion pro Position versucht, jedoch komme ich so zu keiner richtigen Lösung da es Spieler gibt, die auf mehreren Positionen den Maximalwert besitzen und ich Positionen nicht doppelt besetzten kann.

Bitte kein Zitieren! Vor allem nicht im Selbstgespräch (es sei denn, die Passage iiegt lange zurück), im Zwiegespräch. Und im Mehrgespräch immer nur das Allernötigste, niemals Fullquote!

Hallo t...,

A) Solver
Für solche Optimierungsfragen gibt es in Excel eigentlich den Solver. Da die Zuordnung der Spielernummer auf die Positionen nicht kontinuierlich ist, müste die Methode EA (evolutionärer Algorithmus) gewählt werden. In ihr werden die Zuordnungen über Zufallsprozesse für eine vorgegebene Zeit variiert und am Ende wird das bisher beste Ergebnis ausgewiesen.
Bei 11 Positionen aus 50 Spielern gibt es ca 10^18 Variationen. Ich nehme an, dass der Solver bei dieser Grössenordnung in den meisten Fällen nicht das Optimum findet häufig auch sehr weit weg endet.

B) Reduzierung der Variationen
1. Bei 11 Positionen könnte es sich um Fussball handeln. Hierbei hat die Position des Torwarts nach meinem Kenntnisstand eine besondere Rolle, so dass ein trainierter Torwart nicht unbedingt ein guter Feldspieler ist und ein Feldspieler nicht unbedingt ein guter Torwart.
Wenn in den 50 Spielern 3 Torwarte sind, könnte man aus ihnen den Torwert ermitteln  und aus den restlichen 47 Spielern die anderen 10 Positionen. Dann habe ich "nur" noch 10^16 Variationen (um den Faktor 100 kleiner).
2. Wahrscheinlich stehen auch zeitlich nicht immer alle Spieler zur Verfügung. Wenn ich die Zuordnung zB nur für 30 Spieler durchführe, habe ich ca 10^14 Variationen (noch einmal um den Faktor 100 kleiner).

Beide Zahlen sind meiner Einschätzung nach immer noch sehr viel zu hoch für den Excel-Solver

C) VBA
In einem anderen Forum habe ich im letzten Jahr für eine Optimierung mit 30 Spielern und 10 Positionen ein Makro geschrieben. Alle Variationen  durchzurechnen ist auch für ein Excel-Makro nicht möglich. Durch geschickte Sortierung konnte ich aber bei vielen Zweigen weit vor dem Ende erkennen, dass das Ergebnis nicht mehr verbessert werden kann, und diesen Zweig abbrechen. Für die Beispieldaten konnte ich in wenigen Sekunden die optimale Aufstellung ermitteln.

https://www.ms-office-forum.net/forum/sh...p?t=367535&page=2&highlight=Spieler


D) Zur Zeit habe ich hier keine Excel
daher kann ich die Datei selbst zur Zeit nicht lesen oder bearbeiten. Aber vielleicht könnte ja ein anderer Helfer bei Fragestellungen zur Erweiterung aushelfen.
Eine Erweiterung auf mehr zu berücksichtigende Spieler müste über die Erweiterung eines benannten Bereiches (wahrscheinlich "Liste") aber leicht möglich sein.


ps. Den Witz mit den 550 Möglichkeiten habe ich nicht verstanden, es gibt doch Trillionen möglicher Variationen.

Zur Info:
https://www.vba-forum.de/View.aspx?ziel=..._bestimmen