Aufstellungsgenerator

[Kaipirinha]

Hallo zusammen,

ich habe ein Problem mit Excel und komme mit meinem Wissen - ich bin ziemlich weit davon entfernt Profi zu sein, wie man vermutlich sieht - und der Hilfe des Internets nicht mehr weiter. Mir wurde daraufhin dieses Forum empfohlen.

Mein Problem schildert sich wie folgt:

Ich bin Fußball-Trainer und habe viel Spaß daran, mit Excel zu arbeiten und neue Sachen auszuprobieren. Ich hatte die Idee, meine Mannschaftsaufstellung mit Excel berechnen zu lassen. Deshalb auch "Aufstellungsgenerator". Das Problem befindet sich im Arbeitsblatt "Aufstellung". Wer sich das folgende - nachvollziehbar - nicht durchlesen möchte, kann also dort einsteigen.

Die Namen der Spieler in der Datei sind natürlich frei erfunden, spielen aber auch nicht die größte Rolle. Vielleicht erläutere ich meine Arbeitsblätter kurz, bevor ich zu meinem Problem komme:

- Kader: Hier habe ich alle Spieler, die mir zur Verfügung stehen aufgelistet. Grundsätzlich möchte ich mit 3 Formationen arbeiten. Jedem Spieler habe ich die Position zugeordnet, die ich ihm in der jeweiligen Formation unabhängig von Werten und Berechnungen zutraue. Für diejenigen, die sich mit Fußball nicht auskennen / sich nicht dafür interessieren: Eine Formation gibt eine bestimmte Spielausrichtung und die Position der jeweiligen Spieler vor. Das 4-2-3-1 gibt also vor, mit 4 Verteidigern, 2 defensiven Mittelfeldspielern, 3 offensiven Mittelfeldspielern und einem Stürmer zu spielen. Weiter rechts habe ich die 10 für mich wichtigsten Attribute der Spieler bewertet. Unter den Bewertungen habe ich die jeweiligen Attribute für die jeweiligen Positionen in den jeweiligen Formationen mit Prozenten gewichtet. Anhand dessen lassen sich die Werte der Spieler berechnen.

- Werte: Hier habe ich momentan nur die Durchschnitte der jeweiligen Attribute errechnen lasse, damit ich für mein Training weiß, was die momentan größte Schwachstelle der gesamten Mannschaft ist. Hier möchte ich später auch die jeweiligen Durchschnittswerte der Positionen ermitteln, um das Training entsprechend (auch individuell) anpassen zu können.

- Training: Das große Ziel hierbei ist, mir früher oder später Trainingsübungen mit dem Hintergrund der Durschnittswerte vorschlagen zu lassen. Das steht momentan aber noch in den Sternen.

- Leistung: Hierüber möchte ich in Zukunft Training- und Spielleistungen bewerten und damit die Werte im Arbeitsblatt "Kader" je nach Leistung ändern lassen.

- Übersicht1: Hier habe ich eine einfache Übersicht, in der ich die Spieler auf den einzelnen Positionen genauer vergleichen kann.

- Kader2: "Kader" ist dafür gedacht, eine generelle Bewertung der Fähigkeiten der Spieler zu ermöglichen. "Kader2" soll mir ermöglichen, die Werte über die Gewichtung der Attribute je nach Gegner ändern zu können. "Kader2" ist zudem Rechengrundlage für die Arbeitsblätter "Übersicht2" und "Aufstellung".

- Gegner: Hier möchte ich in Zukunft den Gegner einschätzen und mir darüber zum einen die für das Spiel angedachte Formation, als auch taktische Vorgaben ausgeben zu lassen.

- Übersicht2: Eine weitere Übersicht, in der ich die Anwesenheit der Spieler am Spieltag eingeben kann. Wer nicht da ist, kann auch nicht spielen.

- Aufstellung: Das Herzstück und eigentliche Ziel des Ganzen. Ziel ist, dass in Zelle A1 die aus dem Arbeitsblatt "Gegner" ausgegebene Formation erscheint. Momentan muss die Eingabe noch manuell erfolgen. Je nach Eingabe ("4-2-3-1"; "3-4-1-2"; "3-1-4-2") ändern sich die Positionen und die auf dieser Position besten Spieler werden errechnet. Soweit so gut. Es kann aber durchaus sein, dass ein Spieler auf mehreren Positionen die höchste Bewertung hat. Das Ziel ist selbstverständlich, die besten verfügbaren Spieler aufzustellen. Deshalb bin ich mittlerweile seit einigen Tagen damit beschäftigt, wie ich es schaffe, dass in einem solchen Fall der zweitstärkste Spieler in die Aufstellung rutsch (sollte der schon in der Aufstellung stehen, der drittstärkste usw.). Der beste Spieler sollte demnach natürlich auf der Position bleiben, auf der es für die Mannschaft am meisten Sinn macht. Mit anderen Worten: Ich habe wie erwähnt Bewertungen je Spieler und bei 11 Spielern soll die Mannschaft spielen, die den höchsten Gesamtwert erreicht.

Ich hoffe, mein Problem ist verständlich geworden und ist für Sie / Euch trotz einem eventuellen Desinteresse am Fußball aus Excel-Sicht sehr interessant. Ich sehe das hier als letzte Möglichkeit, um mein Projekt nicht verwerfen zu müssen.

Über jede Antwort / Rückfrage würde ich mich sehr freuen. Ich wünsche Ihnen / Euch einen schönen Abend und bedanke mich in jedem Fall für das Interesse, die Aufmerksamkeit, und die investierte Zeit.

Liebe Grüße
Kai


  Aufstellungsgenerator - Kopie.xlsx (Größe: 143,76 KB / Downloads: 12)

[maninweb]

Hallo,

ich habe mal reingeschaut und wenn ich das richtig verstehe, ziehst Du die Bewertungen aus Übersicht2. Excel mag es sehr gerne, wenn Daten
untereinander als Tabelle abgelegt sind und weniger als Blöcke. Mein Tipp wäre jetzt eher statt der blockweise Darstellung der Übersichtsergebnisse
diese in Spalten und Zeilen darzustellen. Also meine Empfehlung wäre, zunächst das Datenmodell zu verfeinern.

Beispielhaft in einer Spalte das System, in der zweiten eine Art Kategorie (TW, LV, LIV, ...), in einer dritten die Schlüssel, dann die Namen usw.
Das wird es Dir einfacher machen, die Daten herauszuziehen. Und gegebenenfalls Vergleiche der Scores einzelner Personen zu ermitteln.
Das gleiche würde gelten für Übersicht1 und andere Tabellen.

Gruß

[Ego]

Hallo Kai,

in deinen Beispieldaten gibt es für die Formation "3-1-4-2" theoretisch 36.450.000 mögliche Kombinationen.
Hier einige Bemerkungen:

A) Formeln
Bei dieser Anzahl der Kombinationsmöglichkeiten sehen ich keine Chance das optimale Ergebnis über Formeln zu ermitteln.

B) Solver
Für solche Optimierungsfragestellungen gibt es in Excel den Solver. Er versucht durch Variation der Parameter unter Berücksichtigung von Nebenbedingungen das optimale Ergebnis zu ermitteln.
In der Anlage habe ich in einem Zusatzblatt ("Blatt1") ein Problem für den Solver aufgestellt und erst einmal manuell mit den Daten für die Formation "4-2-3-1" gefüllt.
Die Zuordnung der der Leistung zu dem Spieler auf einer Position sollten in einer Tabelle ("Leistung") zusammengefasst werden.
Aufgerufen wird der Solver über "Daten">"Analyse">"Solver" und dann über "Start" gestartet.
Falls du dies auch für die anderen Formationen nutzen möchtest, must du die Tabellen "Positionen" und "Leistung" vervollständigen.
Achtung !!
Das Solverproblem benötigt die Lösungsmethode "EA(...)". Diese Methode arbeitet mit zufälligen Änderungen der Variablen und muss zeitlich begrenzt werden. Daher kann es sein, dass nicht in jedem Lauf das optimale Ergebenis gefunden wird. Du solltest den Solver mehrfach starten und die Reihenfolge der Variabelen (gelbe Felder "ID") des bisher besten Laufs zwischenspeichern.

C) VBA-Programm
Da es in deinen Beispieldaten für alle Formationen "nur" maximal 36 Mill. Kombinationen gibt, glaube ich, dass man auch mit einem Programm, dass geschickt "alle" Kombinationen ausprobiert, in vertretbarer Zeit ein Optimum finden kann.
Wäre das für dich eine Lösung?
Ein Vorteil könnte sein, dass kein zusätzliches Blatt für das Soverproblem benötigt wird. Ich könnte das Programm in das Blatt Übersicht2 einpassen.
Ein zweiter Vorteil ist, wenn wie ich vermute die Rechenzeiten vertretbar sind, dass auf jeden Fall bei jedem Lauf das optimale Ergebnis gefunden wird.
Ein Nachteil ist vielleicht, dass es ein Programm ist.

[Kaipirinha]

@maninweb

Danke für deine Antwort. Dass ich meine Tabelllen besser sortieren und aufräumen sollte, dabei muss ich dir Recht geben. Das würde mir die Übersicht deutlich vereinfachen, löst aber, soweit ich Dich verstehe, nicht mein Problem.

---

@Ego

Auch Dir vielen Dank für deine Antwort. Mir war die Anzahl von Möglichkeiten und die damit verbundene Rechenkapazität bisher gar nicht bewusst.

Ich denke, am sinnvollsten wäre es, das Problem über VBA zu lösen. Das ist für mich auf jeden Fall eine Lösung. Wenn dadurch der Solver umgangen werden kann, wäre das optimal. Würde dann die Ansicht bleiben wie es momentan in "Aufstellung" ist, oder würde mir die Aufstellung aufgelistet werden, wie Du es mit dem Solver gemacht hast? Von welchen Rechenzeiten gehst Du aus? Wären die Namen und Positionen weiterhin über eine Formel anpassbar, sodass diese Sachen in allen Arbeitsblätter und somit auch in der Aufstellung abgeändert werden? Beispielhaft verlassen Spieler nach einer Saison schon mal den Verein / die Mannschaft und neue stoßen hinzu. Wie einfach wäre es, diese hinzuzufügen / zu entfernen?

[Ego]

Hallo Kai,

A) zu

Von welchen Rechenzeiten gehst Du aus?

Die Rechenzeit ist sehr stark abhängig von den Daten.
1. von der Anzahl der möglichen Spieler pro Position (die Anzahl der theoretischen Kombinationen ist das Produkt der 11 Zahlen) und
2. von der Leistungsdifferenz der Spieler pro Position (die entscheidet ob ein ganzer Zweig möglicher Kombinationen nicht weiter berechnet werden müss).
Ich vermute, dass mit den Beispieldaten die Rechenzeit eher im einstelligen Minutenbereich vielleicht sogar kleiner als eine Minute ist.
Ich weis nicht ob ich noch heute das Programm schreiben kann, aber spätestens morgen werde ich eine lauffähige Version haben.

B) zu

Wären die Namen und Positionen weiterhin...

Ich habe die Absicht deine Struktur, so wie ich sie verstanden habe, weiter zu nutzen und möchte nur im Blatt "Übersicht2" einige Ergänzungen und Änderungen machen.

1. Ausgabe der Ergebnisse hinter den Namen (H2:H25). Die Formeln im Blatt Aufstellung und die Formeln für die Bank können darauf zugreifen.
2. In den Blöcken der Leistungen pro Formation werde ich alle 11 Positionen füllen, damit das Programm erkennen kann (bzw. nicht wissen muss) welche Position doppelt zu belegen ist.
3. Da ich in Programmen ungern mit festen Adressen arbeite werde ich einigen Zellen und Bereichen einen Namen geben. Bei Änderungen im Arbeitsblatt muss dann keine Änderung im Programm erfolgen.

Ich werde, wenn du mit dem Progamm zufrieden bist ausführlich beschreiben was bei Änderung des Datenbestandes anzupassen ist.

C) zu Tabellenkalkulation <> Blockmeisselung
maninweb hat natürlich Recht, dass deine Strukturen nicht optimal zur Nutzung in Excel sind. Excel ist ein Programm zur Tabellenkalkulation und nicht zum einzelnen Zusammestellen von Blöcken.
Vielleich versuchst du es einmal in zukünftigen Dateien die Daten zur Bearbeitung in Tabellen zu ordnen und nur für die Ausgabe Kreuztabellen (und Blöcke) zu nutzen. Dann must du auch nicht fast alle Formeln anders Schreiben, sondern kannst eine Formel für grössere Bereiche nutzen.
Man kann sich daran gewöhnen und es erleichter die Arbeit ungemein.

[Ego]

Hallo Kai,
in der Anlage eine erste Programmversion. Und hier noch einige Bemerkungen:

1. Aufruf
Gestartet wird das Programm über den Button "tuwat" im Blatt "Übersicht2".

2. Zeiten
Ich hatte zwar auf Rechenzeiten deutlich unter einer Minute gehofft, aber die realen Zeiten haben mich doch überrascht.

3. Namen
Über den Namensmanager unter "Formeln">"Definierte Namen">"Namensmanager" kannst du sehen für welche Zellen ich welche Namen vergeben habe. Über F5 kannst du auch direkt zu den benannten Zellen gelangen.

4. Formation
In Blatt "Übersicht2" Zelle G1 hat die Formation keine Bindestriche, da aus dieser Zelle der Name des zu berücksichtigenden Blockes gebildet wird. Diese Namen dürfen keine Bindestriche enthalten.

5. Positionen
Ich habe Positionen, die in einer Formation doppelt vorkommen, unterscheidbar gemacht. Für das Programm ist diese Unterscheidbarkeit nicht notwendig. Sie vereinfacht nur die Zuordnung im Blatt "Aufstellung". In den einzelnen Blöcken habe ich immer für 11 Positionen die mögliche Zuordnung eingerichtet.

6. keine Aufstellung möglich
Falls für eine Position kein Spieler anwesend ist, wird dies als Fehlermeldung ausgegeben. Falls aber für alle Positionen ein Spieler anwesend ist, eine vollständige Aufstellung aber nicht erfolgen kann, da ein Spieler an zwei Positionen spielen müste, wird das Programm nur mit einem leeren Ausgabebereich beendet.

7. meine Farben
Wenn ich an einer Exceltabelle arbeite Färbe ich die von mir genutzten Zellen gerne ein. Hier:
grün für manuell einzutragende Werte,
grau für Formeln und
beige für vom Programm ermittelte Werte.

[Kaipirinha]

Guten Morgen Ego.

Was soll ich sagen? Ich bin überwältigt. An meinem Computer hier ist die Bearbeitungszeit deutlich unter einer Sekunde und es wird genau das errechnet, was ich brauche. Endlich kann ich weiter machen. Vielen vielen Dank!

Liebe Grüße
Kai