Tryk på F9 indtil luk - Excel-tip

Brug af Excel til at løse enhver kompleks model

Lev er kommissær for en konkurrencedygtig svømmeliga. Han skriver: "Jeg er kommissær for en svømmeliga. Der er otte hold i år. Hvert hold er vært for et møde og er hjemmeholdet. Et møde vil have 4 eller 5 hold. Sådan arrangeres tidsplanen, så hvert hold svømmer imod hvert andet hold to gange? Tidligere, da vi havde 5, 6 eller 7 hold, kunne jeg løse det ved at trykke på F9 indtil tæt. Men i år, med 8 hold, kommer det ikke ud. "

En af begrænsningerne er, at nogle puljer kun tilbyder 4 baner, så du kan kun have 4 hold, når denne pool er vært for gallaen. For andre puljer har de muligvis 5, 6 eller flere baner, men det ideelle møde har hjemmelaget plus fire andre.

Mit forslag: Tryk hurtigere på F9! For at hjælpe med det: udvikle et "mål for nærhed" i din model. På den måde, når du trykker på F9, kan du holde øje med et nummer. Når du finder en "bedre" løsning end den bedste, du har fundet, skal du gemme den som den mellemliggende bedste løsning.

Trin, der er specifikke for svømningsproblemet

  • Liste over de 8 hjemmehold på toppen.
  • Hvor mange måder at udfylde de andre 4 baner på?
  • Liste over alle måder.
  • Hvor mange måder at udfylde de andre 3 baner (for små spillesteder?). Liste over alle måder.
  • Brug RANDBETWEEN(1,35)til at vælge hold til hver kamp.

Bemærk, at der er 35 8 mulige måder at arrangere sæsonen på (2,2 billioner). Det ville være "umuligt" at gøre dem alle med en hjemme-pc. Hvis der kun var 4000 muligheder, kunne du gøre dem alle, og det er en video til en anden dag. Men med 2,2 billioner muligheder er tilfældet med at gætte mere sandsynligt at finde løsninger.

Udvikle et mål for nærhed

I svømmescenariet er den vigtigste ting Svømmer hvert hold mod hvert andet hold to gange?

Tag de aktuelle 8 tilfældige tal, og brug formler til at tegne alle match-ups. Liste over de 28 mulige match ups. Brug COUNTIFtil at se, hvor mange gange hver match-up sker med de aktuelle tilfældige tal. Tæl hvor mange der er 2 eller derover. Målet er at få dette tal til 28.

Sekundært mål: Der er 28 matchups. Hver skal ske to gange. Det er 56 matchups, der skal ske. Med 8 puljer og 6 med fem baner får du 68 matchups. Det betyder, at nogle hold svømmer mod andre hold 3 gange og muligvis 4 gange. Sekundært mål: Sørg for, at så få hold som muligt har 4 match-ups. Tertiært mål: Minimer Max.

Langsom måde at løse dette på

Tryk på F9. Se på resultatet. Tryk på F9 et par gange for at se, hvilke resultater du får. Når du får et højt resultat, skal du gemme de 8 indgange og de tre outputvariabler. Bliv ved med at trykke på F9, indtil du får et bedre resultat. Gem den ved at optage de 8 inputceller og de 3 resultatceller.

Makro til at gemme det aktuelle resultat

Denne makro gemmer resultaterne til næste række.

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 for at trykke F9 gentagne gange og kontrollere resultaterne

Skriv en makro for at trykke F9 gentagne gange, og log kun "bedre" løsninger. Lad makroen stoppe, når du når de ønskede resultater på 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

Sidepanel om ScreenUpdating

Sidebjælke: Først er det "sjovt" at se gentagelserne rulle forbi. Men til sidst indser du, at du måske bliver nødt til at teste millioner af muligheder. At have Excel til at tegne skærmen igen, nedsætter makroen. Brug Application.ScreenUpdating = Falsk for ikke at male skærmen igen.

Hver gang du får et nyt svar eller hver 1000, skal du tegne skærmen igen. Problem: Excel tegner ikke skærmen igen, medmindre cellemarkøren bevæger sig. Jeg fandt ud af, at ved at vælge en ny celle, mens ScreenUpdating er sand, ville Excel male igen skærmen. Jeg besluttede at få det til at skifte mellem tællercellen og de bedste resultater hidtil.

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øsningsløsninger

Jeg overvejede mange titler til denne video: Tryk på F9 indtil tæt, gæt indtil korrekt, Brute Force Solving, måling af nærhed

Bemærk, at jeg prøvede at bruge Solver til at løse problemet. Men Solver kunne ikke komme tæt på. Det blev aldrig bedre end 26 hold, da målet var 28.

Bemærk også, at enhver løsning, jeg får i denne video, er "dum-held". Der er ikke noget intelligent ved løsningsmetoden. For eksempel siger makroen ikke: "Vi skal starte med den bedste løsning hidtil og foretage nogle mikrojusteringer." Selvom du får en løsning, der kun er et nummer væk, trykker den blindt på F9 igen. Der er sandsynligvis en mere intelligent måde at angribe problemet på. Men … lige nu … for vores svømmekommissær fungerede denne tilgang.

Download arbejdsbogen

Se video

Download fil

Download eksempelfilen her: Podcast2180.zip

Interessante artikler...