Drücken Sie F9 bis zum Schließen - Excel-Tipps

Verwenden von Excel zum Lösen eines komplexen Modells

Lev ist Kommissar einer wettbewerbsfähigen Schwimmliga. Er schreibt: "Ich bin der Kommissar einer Schwimmliga. In diesem Jahr gibt es acht Teams. Jedes Team veranstaltet ein Treffen und ist die Heimmannschaft. Ein Treffen besteht aus 4 oder 5 Teams jedes zweite Team zweimal? In der Vergangenheit, als wir 5, 6 oder 7 Teams hatten, konnte ich es lösen, indem ich F9 bis zum Ende drückte. Aber dieses Jahr mit 8 Teams kommt es nicht heraus. "

Eine der Einschränkungen ist, dass einige Pools nur 4 Bahnen bieten, sodass Sie nur 4 Teams haben können, wenn in diesem Pool die Gala stattfindet. Für andere Pools haben sie möglicherweise 5, 6 oder mehr Bahnen, aber das ideale Treffen besteht aus der Heimmannschaft und vier weiteren.

Mein Vorschlag: Drücken Sie F9 schneller! Um dies zu unterstützen: Entwickeln Sie ein "Maß für die Nähe" in Ihrem Modell. Auf diese Weise können Sie beim Drücken von F9 eine Zahl im Auge behalten. Wenn Sie eine "bessere" Lösung als die beste finden, die Sie gefunden haben, speichern Sie diese als die beste Zwischenlösung.

Spezifische Schritte für das Schwimmproblem

  • Listen Sie die 8 Heimmannschaften ganz oben auf.
  • Wie viele Möglichkeiten, die anderen 4 Spuren zu füllen?
  • Listen Sie alle Möglichkeiten auf.
  • Wie viele Möglichkeiten, die anderen 3 Fahrspuren zu füllen (für kleine Veranstaltungsorte?). Listen Sie alle Möglichkeiten auf.
  • Verwenden Sie RANDBETWEEN(1,35)diese Option , um Teams für jedes Spiel auszuwählen.

Beachten Sie, dass es 35 8 Möglichkeiten gibt, die Saison zu arrangieren (2,2 Billionen). Es wäre "unmöglich", sie alle mit einem Heim-PC zu machen. Wenn es nur 4000 Möglichkeiten gäbe, könnten Sie sie alle machen, und das ist ein Video für einen anderen Tag. Bei 2,2 Billionen Möglichkeiten ist es jedoch wahrscheinlicher, dass zufällige Schätzungen Lösungen finden.

Entwickeln Sie ein Maß für die Nähe

Im Schwimmszenario ist das Wichtigste: Schwimmt jedes Team zweimal gegen jedes andere Team?

Nehmen Sie die aktuellen 8 Zufallszahlen und verwenden Sie Formeln, um alle Übereinstimmungen zu zeichnen. Listen Sie die 28 möglichen Match-Ups auf. Verwenden Sie COUNTIFdiese Option, um zu sehen, wie oft jedes Match mit den aktuellen Zufallszahlen stattfindet. Zählen Sie, wie viele 2 oder mehr sind. Das Ziel ist es, diese Zahl auf 28 zu bringen.

Sekundäres Ziel: Es gibt 28 Matchups. Jeder muss zweimal passieren. Das sind 56 Matchups, die passieren müssen. Bei 8 Pools und 6 mit fünf Bahnen treten 68 Matchups auf. Das bedeutet, dass einige Teams dreimal und möglicherweise viermal gegen andere Teams schwimmen. Sekundäres Ziel: Stellen Sie sicher, dass so wenige Teams wie möglich 4 Match-Ups haben. Tertiäres Ziel: Minimieren Sie die max.

Langsamer Weg, dies zu lösen

Drücken Sie F9. Schauen Sie sich das Ergebnis an. Drücken Sie einige Male F9, um zu sehen, welche Ergebnisse Sie erhalten. Wenn Sie ein hohes Ergebnis erhalten, speichern Sie die 8 Eingänge und die drei Ausgangsvariablen. Halten Sie F9 gedrückt, bis Sie ein besseres Ergebnis erhalten. Speichern Sie diese, indem Sie die 8 Eingabezellen und die 3 Ergebniszellen aufzeichnen.

Makro zum Speichern des aktuellen Ergebnisses

Dieses Makro speichert die Ergebnisse in der nächsten Zeile.

Sub SaveThis() NR = Range("Z1048576").End(xlUp).Row + 1 Cells(NR, 26).Resize(1, 11).Value = Array(Range("c8").Value, _ Range("D8").Value, Range("E8").Value, Range("F8").Value, _ Range("G8").Value, Range("H8").Value, Range("I8").Value, _ Range("J8").Value, Range("O1").Value, Range("P1").Value, _ Range("Q1").Value) End Sub

Makro, um F9 wiederholt zu drücken und die Ergebnisse zu überprüfen

Schreiben Sie ein Makro, um wiederholt F9 zu drücken, und protokollieren Sie nur "bessere" Lösungen. Lassen Sie das Makro anhalten, wenn Sie die gewünschten Ergebnisse von 28 & 0 erhalten.

Sub TrySome() NR = Range("Z1048576").End(xlUp).Row + 1 Ctr = Range("T1").Value Application.ScreenUpdating = Range("AH2").Value SolutionFound = False GoAgain: ActiveSheet.Calculate Ctr = Ctr + 1 UseIt = 0 If Range("O1").Value> Range("AK1").Value Then UseIt = 1 ElseIf Range("O1").Value = Range("AK1").Value Then If Range("P1").Value 300 Then Application.ScreenUpdating = True Exit Sub End If If SolutionFound = True Then Application.ScreenUpdating = True Exit Sub End If If Ctr Mod 1000 = 0 Then Range("T1").Value = Ctr Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").Value End If GoTo GoAgain End Sub

Seitenleiste zum Thema ScreenUpdating

Seitenleiste: Zuerst macht es "Spaß", die Iterationen vorbeiziehen zu sehen. Aber irgendwann wird Ihnen klar, dass Sie möglicherweise Millionen von Möglichkeiten testen müssen. Wenn Excel den Bildschirm neu zeichnet, wird das Makro verlangsamt. Verwenden Sie Application.ScreenUpdating = False, um den Bildschirm nicht neu zu zeichnen.

Jedes Mal, wenn Sie eine neue Antwort erhalten oder alle 1000, lassen Sie Excel den Bildschirm neu zeichnen. Problem: Excel zeichnet den Bildschirm nur neu, wenn sich der Zellenzeiger bewegt. Ich fand heraus, dass Excel durch Auswahl einer neuen Zelle, während ScreenUpdating True ist, den Bildschirm neu malen würde. Ich habe mich dafür entschieden, zwischen der Zählerzelle und den bisher besten Ergebnissen zu wechseln.

Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").Value

Alternative Lösungslösungen

Ich habe viele Titel für dieses Video in Betracht gezogen: Drücken Sie F9 bis zum Schließen, Vermutung bis zur Korrektur, Brute-Force-Lösung, Maß für die Nähe

Beachten Sie, dass ich versucht habe, Solver zu verwenden, um das Problem zu lösen. Aber Solver konnte nicht näher kommen. Es wurde nie besser als 26 Teams, als das Ziel 28 war.

Beachten Sie auch, dass jede Lösung, die ich in diesem Video bekomme, "Pech" ist. An der Lösungsmethode ist nichts Intelligentes. Zum Beispiel sagt das Makro nicht: "Wir sollten von der bisher besten Lösung ausgehen und einige Mikroanpassungen vornehmen." Selbst wenn Sie eine Lösung erhalten, die nur eine Nummer entfernt ist, wird F9 erneut blind gedrückt. Es gibt wahrscheinlich einen intelligenteren Weg, um das Problem anzugreifen. Aber… gerade jetzt… für unseren Schwimmkommissar hat dieser Ansatz funktioniert.

Laden Sie die Arbeitsmappe herunter

Schau Video

Download-Datei

Laden Sie die Beispieldatei hier herunter: Podcast2180.zip

Interessante Beiträge...