Druk op F9 tot sluiten - Excel-tips

Inhoudsopgave

Excel gebruiken om elk complex model op te lossen
Stappen die specifiek zijn voor het zwemprobleem
Ontwikkel een mate van nabijheid
Langzame manier om dit op te lossen
Macro om het huidige resultaat op te slaan
Macro om herhaaldelijk op F9 te drukken en de resultaten te controleren
Zijbalk over ScreenUpdating
Alternatieve oplossingsoplossingen
Download het werkboek
Bekijk video

Excel gebruiken om elk complex model op te lossen

Lev is commissaris van een competitieve zwemcompetitie. Hij schrijft: "Ik ben de commissaris van een zwemcompetitie. Er zijn dit jaar acht teams. Elk team organiseert één wedstrijd en is de thuisploeg. Een wedstrijd heeft 4 of 5 teams. Hoe het schema te regelen zodat elk team tegen zwemt om het andere team twee keer? In het verleden, toen we 5, 6 of 7 teams hadden, kon ik het oplossen door tot slot op F9 te drukken. Maar dit jaar, met 8 teams, komt het er niet uit. "

Een van de beperkingen is dat sommige pools maar 4 banen bieden, dus je kunt maar 4 teams hebben als die pool het gala organiseert. Voor andere poules hebben ze misschien 5, 6 of meer banen, maar de ideale ontmoeting heeft het thuisteam plus vier andere.

Mijn suggestie: druk sneller op F9! Om daarbij te helpen: ontwikkel een "mate van nabijheid" in uw model. Op die manier kunt u één nummer in de gaten houden wanneer u op F9 drukt. Als je een "betere" oplossing vindt dan de beste die je hebt gevonden, bewaar die dan als de beste tussenoplossing.

Stappen die specifiek zijn voor het zwemprobleem

Maak een lijst van de 8 thuisteams bovenaan.
Hoeveel manieren om de andere 4 rijstroken te vullen?
Maak een lijst van alle manieren.
Hoeveel manieren om de andere 3 rijstroken te vullen (voor kleine locaties?). Maak een lijst van alle manieren.
Gebruik RANDBETWEEN(1,35)om teams voor elke wedstrijd te kiezen.

Merk op dat er 35 8 mogelijke manieren zijn om het seizoen in te delen (2,2 biljoen). Het zou "onmogelijk" zijn om ze allemaal met een pc thuis te doen. Als er maar 4000 mogelijkheden waren, zou je ze allemaal kunnen doen, en dat is een video voor een andere dag. Maar met 2,2 biljoen mogelijkheden is het waarschijnlijker dat willekeurig gissen oplossingen vindt.

Ontwikkel een mate van nabijheid

In het zwemscenario is het belangrijkste: Zwemt elk team twee keer tegen elk ander team?

Neem de huidige 8 willekeurige getallen en gebruik formules om alle match-ups uit te zetten. Maak een lijst van de 28 mogelijke match-ups. Gebruik COUNTIFom te zien hoe vaak elke match plaatsvindt met de huidige willekeurige getallen. Tel hoeveel er 2 of meer zijn. Het doel is om dit aantal op 28 te krijgen.

Secundair doel: er zijn 28 matchups. Elk moet twee keer gebeuren. Dat zijn 56 matchups die moeten gebeuren. Met 8 pools en 6 met vijf banen, zul je 68 matchups hebben. Dat betekent dat sommige teams 3 keer en mogelijk 4 keer tegen andere teams zullen zwemmen. Secundair doel: zorg ervoor dat zo min mogelijk teams 4 wedstrijden hebben. Tertiair doel: Minimaliseer de Max.

Langzame manier om dit op te lossen

Druk op F9. Kijk naar het resultaat. Druk een paar keer op F9 om te zien welke resultaten u krijgt. Als u een hoog resultaat krijgt, slaat u de 8 ingangen en de drie uitgangsvariabelen op. Blijf op F9 drukken totdat u een beter resultaat krijgt. Bewaar die door de 8 invoercellen en de 3 resultaatcellen op te nemen.

Macro om het huidige resultaat op te slaan

Deze macro slaat de resultaten op in de volgende rij.

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

Macro om herhaaldelijk op F9 te drukken en de resultaten te controleren

Schrijf een macro om herhaaldelijk op F9 te drukken en log alleen "betere" oplossingen in. Laat de macro stoppen wanneer u de gewenste resultaten van 28 & 0 bereikt.

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

Zijbalk over ScreenUpdating

Zijbalk: in het begin is het "leuk" om de iteraties voorbij te zien komen. Maar uiteindelijk realiseer je je dat je misschien miljoenen mogelijkheden moet testen. Door Excel het scherm opnieuw te laten tekenen, wordt de macro langzamer. Gebruik Application.ScreenUpdating = False om het scherm niet opnieuw te schilderen.

Elke keer dat u een nieuw antwoord krijgt of elke 1000, laat Excel het scherm opnieuw tekenen. Probleem: Excel tekent het scherm pas opnieuw als de celaanwijzer beweegt. Ik ontdekte dat Excel het scherm opnieuw zou schilderen door een nieuwe cel te selecteren terwijl ScreenUpdating True is. Ik besloot om het af te wisselen tussen de tellercel en de beste resultaten tot nu toe.

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

Alternatieve oplossingsoplossingen

Ik heb veel titels voor deze video overwogen: Druk op F9 tot dichtbij, Guess Until Correct, Brute Force Solving, Measure of Closeness

Merk op dat ik heb geprobeerd Solver te gebruiken om het probleem op te lossen. Maar Oplosser kon niet dichtbij komen. Het werd nooit beter dan 26 teams toen het doelpunt 28 was.

Merk ook op dat elke oplossing die ik in deze video krijg "dom geluk" is. Er is niets intelligent aan de oplossingsmethode. De macro zegt bijvoorbeeld niet: "We moeten uitgaan van de beste oplossing tot nu toe en enkele micro-aanpassingen maken." Zelfs als je een oplossing krijgt die maar één cijfer verwijderd is, drukt hij weer blindelings op F9. Er is waarschijnlijk een intelligentere manier om het probleem aan te pakken. Maar… op dit moment… voor onze zwemcommissaris werkte deze aanpak.