Maximalberechnung mit Solver?

Computer: Software & Programmierung Office Software
#1

Hallo,

ich habe ein Problem mit dem ich mich gerade herumschlage und hoffe, dass ihr mir hier helfen könnt.
Es geht darum, eine Kombination von Rennen zu finden, wo ich einen möglichst großen Abstand zwischen dem Führenden und dem zweiten habe.

  • ich habe 4 verschiedene Fahrer (plus den Führenden)
  • ich habe zehn verschiedene Rennstrecken
  • ich habe zwei bis vier verschiedene Jahre (wo das Rennen stattgefunden hat)

Ich habe ein Bild angehängt, um das Ganze hoffentlich besser zu erklären…

Bild3
Bild3994×451 45.9 KB

Ludwig hat alle Rennen gewonnen, die anderen vier Fahrer haben die Plätze dahinter belegt bzw. sind bei einigen Rennen nicht angetreten. Nun möchte ich für jede Strecke genau ein Jahr auswählen, so dass der Abstand zwischen Ludwig und dem Gesamt-Zweitbesten (egal welcher der anderen vier Fahrer) möglichst groß ist.
Wenn ich mich z.B. bei Frankfurt für 2016 entscheide, dann muss ich das für alle vier Fahrer tun, ich kann also nicht für einen Frankfurt 2015 nehmen, für den anderen 2016.

Ich habe nun daran gedacht, das Ganze mit dem Solver zu lösen, aber damit komme ich nicht so richtig weiter. Ich habe bis jetzt nicht mal eine Idee, wie ich ihm beibringen soll, dass er immer genau ein Jahr auswählt. Vielleicht hat hier jemand aber auch noch eine ganz andere Idee.

Habe gerade festgestellt, dass ich mich schwer tue, es überhautp richtig darzustellen. Falls es mir nicht gelungen ist, bitte fragen.

Schon mal vielen Dank und Grüße

#2

Hallo.

Versteh ich nicht. Wenn du dich für Frankfurt 2016 entscheidest, gibt es einen Sieger und einen Zweitplatzierten. Der Abstand ist 1 :- )
Soll eine Art „Gesamtpunktzahl“ aus allen Rennen eines Jahrgangs gebildet werden, oder wie soll denn die „Kombination von Rennen“ zustandekommen?

Gruß

Kannitverstan

#3

Hallo,

ich habe geahnt, dass es unverständlich war…

mal als Beispiel:

Ich nehme Berlin 2016, Frankfurt 2015 und Stuttgart 2014 (für jeden Standort muss ein Jahr gewählt werden)

Das würde bedeuten: Ludwig hat 3 Punkte, Otto hat 12 Punkte, Thomas hat 27 Punkte (3. + 4. Platz + 20 „Strafplätze“ weil er Stuttgart 2014 nicht dabei war), Martin hat 31 Punkte und Karl hat 16 PUnkte. Damit wäre Otto der beste hinter Ludwig mit einem Abstand von 9 Punkten

nehme ich nun statt Stuttgart 2014 Stuttgart 2015 würden sich folgende Punkte ergeben: Otto 16; THomas 10, Martin, 42 und Karl 16 Punkte. Damit wäre Thomas der zweitbeste hinter Ludwig mit einem Abstand von 7 Punkten.

Da ich einen möglichst großen Abstand zwischen Ludwig und dem zweitbesten haben will, müsste Beispiel A ausgegeben werden.

Wenn ich nun nur die Anzahl der angegebenen Rennen hätte, könnte man vielleicht noch alle Möglichkeiten durchspielen bei 10-15 Rennen wird das aber eindeutig zu viel.

Grüße

#4

Hallo,

ist das wirklich für die gesamte Tabelle gültig?

Gruß
Tobias

#5

Hallo,

ja, Ludwig hat alle Rennen gewonnen, die in der Tabelle sind (also auch in der Gesamttabelle, nicht nur in den paar Zeilen, die auf dem Bild sind).

Grüße

#6

Hallo.

Keine Ahnung, ob das irgendwie mit dem Solver geht.
Ich würde ein VBA-Makro schreiben, das die Tabelle nach dem Standort sortiert (muß man nicht, macht die Sache aber einfacher) und für jeden Austragungsort eine Schleife durchlaufen läßt.
D.h. es gibt bei x Austragungsorten x ineinander verschachtelte Schleifen.
Für jede sich ergebende Jahreskombination werden die Punkte ermittelt und eben die Kombination der Schleifenzähler festgehalten, bei der der Abstand größer ist als beim vorhergehenden Durchlauf.

Gruß,

Kannitverstan

#7

Hallo,

leider endet mein VBA-Wissen mit der Anpassung eines aufgezeichneten Makros, aber trotzdem danke.

Grüße

#8

Hallo,

das macht es etwas einfacher.

Wenn ich nichts falsch verstanden habe, habe ich hier eine passende Lösung, allerdings bin ich dabei nicht um die Funktion INDIREKT() herumgekommen.

https://1drv.ms/x/s!Amqh9yFUV_1jkVb1P14g-Q0JEPOO

Damit SVERWEIS() funktioniert, musste ich die leeren Zellen mit „0“ ausfüllen. Optisch könnte man die Zellen aber wieder leer machen indem man von einer bedingten Formatierung die Schriftfarbe ändern lässt.

In die gelben Bereiche nichts reinschreiben, die werden für die Funktionen benötigt bzw. automatisch ausgefüllt. Man kann sie aber einfach ausblenden.

Wenn mehr Zeilen für die Liste eingefügt werden, müssen sie zwischen Zeile 4 und 11 eingefügt werden, da sich die Formeln in A4 und A11 von den restlichen unterscheiden und am Anfang bzw. Ende stehen müssen. Man kann z.B. zwischen Zeile 10 und 11 neue Zeilen einfügen und dann die Formeln von Zeile 10 auf die neuen Zeilen erweitern.

Um mehr Städte hinzuzufügen, müssen neue Zeilen zwischen den Zeilen 12 und 14 eingefügt werden. Die Formeln sollten sich problemlos auf die neuen Zeilen erweitern lassen. Wenn in mehreren Zellen der Fehler #ZAHL! erscheint, dürfte das daran liegen, dass die Zeilen 12 bis 14 auf mehr Zeilen erweitert wurden als Städte in der Liste eingetragen sind.

Gruß
Tobias

#9

Hallo,

erst mal vielen Dank für deine Mühe!

Ich habe deine Datei versucht entsprechend anzupassen.
Ist es richtig, dass ich im Bereich G12:J14 die Platzzahlen habe, die für den Fahrer am schlechtesten sind und damit ja nach meiner Vorgabe gewählt werden müssten? Allerdings ist mir hierbei aufgefallen, dass für jeden Fahrer der schlechteste Platz bei einer Stadt gewählt wurde und dabei die Randbedingung außer acht gelassen wurde, dass alle Fahrer bei einer Stadt die gleiche Jahreszahl haben müssen. Also, wenn ich für Otto bei Stuttgart 2016 wähle, dann muss ich dies auch für Martin tun und damit seinen „guten Platz“ in Kauf nehmen und kann nicht seinen 9. Platz aus dem Jahr 2014 nehmen.

Danke und Grüße

#10

Hallo,

  • im Bereich G12:J14 werden die schlechtesten Punktewerte jedes Fahrers auf der jeweiligen Strecke gesucht, unabhängig vom Jahr
  • in G15:J15 werden diese Werte zusammengerechnet
  • K12:K14 übernimmt die Zahlen vom Spieler mit dem schlechtesten Wert
  • in G16:J16 wird beim Fahrer mit dem schlechtesten Wert die Spaltenzahl angezeigt
  • in E12:E14 wird mit SVERWEIS() das betreffende Jahr bestimmt
  • mit der Spaltenzahl wird der Spaltenindex für SVERWEIS() bestimmt
  • die Bestimmung des Suchbereichs mit INDEX() habe ich, um die Formel kürzer zu machen, nach C12:C14 ausgelagert
  • die Werte in K12:K14 sind die Suchkriterien

Es kann sein dass ich da etwas überlesen hatte, ich dachte es geht nur um Ludwig und den schlechtesten.
Wenn du bei meiner Beschreibung einigermaßen durchblickst, siehst du dass die Daten benötigt Grundlage für das meiste und deshalb nötig sind.
Es würde allerdings nichts dagegen sprechen das ganze auszublenden. Dann könntest z.B. für G17 die folgende Formel benutzen und sie per Autoausfüllen auf den Bereich G17:J19 erweitern, bei Bedarf natürlich auch noch weiter:

=SVERWEIS($E12;INDIREKT("Z"&$A12&"S"&SPALTE($E$3)&":Z"&$B12&"S"&SPALTE();FALSCH);6+SPALTE()-SPALTE($J$17);FALSCH)

Mit der Formel werden per SVERWEIS() für jeden Fahrer die entsprechenden Werte für die gefunden Jahre gesucht.

Gruß
Tobias

#11

HAllo,

sorry, aber ich kann dir nicht ganz folgen.

Deine neue Formel ist die Lösung dafür, dass ich für jeden Fahrer das gleiche Jahr pro Stadt wählen muss?

Ich muss mich damit morgen noch mal in Ruhe beschäftigen!

Grüße

#12

Hallo,

genau.
Ich habe die Datei angepasst. Onedrive spinnt bei mir gerade etwas rum aber es scheint unter dem selben Link zu funktionieren: https://1drv.ms/x/s!Amqh9yFUV_1jkVb1P14g-Q0JEPOO
Ich habe alle gelben, also quasi Hilfsbereiche, ausgeblendet und die neue Formel in dem grünen Bereich verwendet um die Plätze der Fahrer auf der jeweiligen Strecke im entsprechenden Jahr anzuzeigen.

Gruß
Tobias

#13

Hallo,

danke für deine Unterstützung. Hat mir wirklich weiter geholfen.

Grüße