Tryck på F9 tills du stänger - Excel Tips

Använda Excel för att lösa alla komplexa modeller

Lev är kommissionär för en tävlingslig liga. Han skriver: "Jag är kommissionär för en simliga. Det finns åtta lag i år. Varje lag är värd för ett möte och är hemmalaget. Ett möte kommer att ha fyra eller fem lag. Hur man ordnar schemat så att varje lag simmar mot vartannat lag två gånger? Tidigare, när vi hade 5, 6 eller 7 lag, kunde jag lösa det genom att trycka på F9 tills nära. Men i år, med 8 lag, kommer det inte ut. "

En av begränsningarna är att vissa pooler bara erbjuder 4 banor, så du kan bara ha fyra lag när poolen är värd för galan. För andra pooler kan de ha 5, 6 eller fler banor, men det perfekta mötet kommer att ha hemmalaget plus fyra andra.

Mitt förslag: Tryck F9 snabbare! För att hjälpa till med det: utveckla ett "mått på närhet" i din modell. På det sättet kan du hålla ett öga på ett nummer när du trycker på F9. När du hittar en "bättre" lösning än den bästa du har hittat, spara den som den mellanliggande bästa lösningen.

Specifika steg för badproblemet

  • Lista de 8 hemmalagen överst.
  • Hur många sätt att fylla de andra fyra banorna?
  • Lista över alla sätt.
  • Hur många sätt att fylla de andra 3 banorna (för små platser?). Lista över alla sätt.
  • Använd för RANDBETWEEN(1,35)att välja lag för varje match.

Observera att det finns 35 8 möjliga sätt att ordna säsongen (2,2 biljoner). Det skulle vara "omöjligt" att göra dem alla med en hemdator. Om det bara fanns 4000 möjligheter kunde du göra dem alla, och det är en video för en annan dag. Men med 2,2 biljoner möjligheter är det mer troligt att slumpmässigt gissning hittar lösningar.

Utveckla ett mått på närhet

I simscenariot är det viktigaste att simma varje lag mot vartannat lag två gånger?

Ta de nuvarande 8 slumpmässiga siffrorna och använd formler för att rita upp alla matchningar. Lista de 28 möjliga match ups. Använd för COUNTIFatt se hur många gånger varje matchning sker med de aktuella slumptalen. Räkna hur många som är två eller fler. Målet är att få detta nummer till 28.

Sekundärt mål: Det finns 28 matchups. Var och en måste hända två gånger. Det är 56 matchningar som måste hända. Med 8 pooler och 6 med fem banor kommer du att ha 68 matchningar. Det betyder att vissa lag simmar mot andra lag tre gånger och eventuellt fyra gånger. Sekundärt mål: Se till att så få lag som möjligt har fyra match-ups. Tertiärt mål: Minimera Max.

Långsamt sätt att lösa detta

Tryck på F9. Titta på resultatet. Tryck på F9 några gånger för att se vilka resultat du får. När du får ett högt resultat sparar du de 8 ingångarna och de tre utgångsvariablerna. Fortsätt trycka på F9 tills du får ett bättre resultat. Spara den genom att spela in de 8 inmatningscellerna och de 3 resultatcellerna.

Makro för att spara det aktuella resultatet

Detta makro sparar resultaten till nästa rad.

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 för att trycka på F9 upprepade gånger och kontrollera resultaten

Skriv ett makro för att trycka på F9 upprepade gånger och logga bara "bättre" lösningar. Stoppa makrot när du når önskat resultat av 28 & 0.

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

Sidofält om ScreenUpdating

Sidofält: Först är det "kul" att se iterationerna rulla förbi. Men så småningom inser du att du kanske måste testa miljontals möjligheter. Att Excel ritar om skärmen saktar ner makrot. Använd Application.ScreenUpdating = Falsk för att inte måla om skärmen.

Varje gång du får ett nytt svar eller var 1000: e, låt Excel rita upp skärmen igen. Problem: Excel ritar inte om skärmen om inte cellpekaren rör sig. Jag fann att genom att välja en ny cell medan ScreenUpdating är sann skulle Excel måla skärmen på nytt. Jag bestämde mig för att låta den växla mellan räknarcellen och de bästa resultaten hittills.

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

Alternativa lösningar

Jag tänkte på många titlar för den här videon: Tryck på F9 tills nära, gissa tills rätt, brute force solving, mått på närhet

Observera att jag försökte använda Solver för att lösa problemet. Men Solver kunde inte komma nära. Det blev aldrig bättre än 26 lag när målet var 28.

Observera också att alla lösningar som jag får i den här videon är "dum-lycka". Det finns inget intelligent med lösningsmetoden. Makrot säger till exempel inte "Vi bör börja från den bästa lösningen hittills och göra några mikrojusteringar." Även om du får en lösning som bara är ett nummer bort, trycker den blindt på F9 igen. Det finns sannolikt ett mer intelligent sätt att attackera problemet. Men … just nu … för vår simkommissionär fungerade denna strategi.

Ladda ner arbetsboken

Kolla på video

Nedladdning fil

Ladda ner exempelfilen här: Podcast2180.zip

Intressanta artiklar...