Trykk F9 til nær - Excel Tips

Bruke Excel til å løse en hvilken som helst kompleks modell

Lev er kommisjonær for en konkurransedyktig svømmeliga. Han skriver: "Jeg er kommisjonær for en svømmeliga. Det er åtte lag i år. Hvert lag er vert for et møte og er hjemmelaget. Et møte vil ha 4 eller 5 lag. Hvordan ordne timeplanen slik at hvert lag svømmer mot hvert annet lag to ganger? Tidligere, da vi hadde 5, 6 eller 7 lag, kunne jeg løse det ved å trykke på F9 til nært hold. Men i år, med 8 lag, kommer det ikke ut. "

En av begrensningene er at noen bassenger bare tilbyr 4 baner, så du kan bare ha 4 lag når det bassenget er vert for gallaen. For andre bassenger kan de ha 5, 6 eller flere baner, men det ideelle møtet vil ha hjemmelaget pluss fire andre.

Mitt forslag: Trykk F9 raskere! For å hjelpe deg med det: utvikle et "mål for nærhet" i modellen din. På den måten, når du trykker på F9, kan du holde øye med ett nummer. Når du finner en "bedre" løsning enn den beste du har funnet, kan du lagre den som den beste løsningen.

Fremgangsmåte som er spesifikke for svømmeproblemet

  • Liste de 8 hjemmelagene på toppen.
  • Hvor mange måter å fylle de 4 andre banene på?
  • Liste opp alle måtene.
  • Hvor mange måter å fylle de andre 3 banene (for små arenaer?). Liste opp alle måtene.
  • Bruk RANDBETWEEN(1,35)til å velge lag for hver kamp.

Merk at det er 35 8 mulige måter å arrangere sesongen på (2,2 billioner). Det ville være "umulig" å gjøre dem alle med en hjemme-PC. Hvis det bare var 4000 muligheter, kunne du gjøre dem alle, og det er en video for en annen dag. Men med 2,2 billioner muligheter, er tilfeldig gjetting mer sannsynlig å finne løsninger.

Utvikle et mål for nærhet

I svømmescenariet er det viktigste å svømme hvert lag mot hvert annet lag to ganger?

Ta gjeldende 8 tilfeldige tall og bruk formler for å plotte ut alle match-ups. Liste over de 28 mulige match ups. Bruk for COUNTIFå se hvor mange ganger hver kamp skjer med gjeldende tilfeldige tall. Tell hvor mange som er 2 eller større. Målet er å få dette tallet til 28.

Sekundært mål: Det er 28 matchups. Hver må skje to ganger. Det er 56 matchups som må skje. Med 8 bassenger og 6 med fem baner vil du ha 68 matchups. Det betyr at noen lag vil svømme mot andre lag 3 ganger og muligens 4 ganger. Sekundært mål: Sørg for at færrest mulig lag har 4 match-ups. Tertiært mål: Minimer Max.

Langsom måte å løse dette på

Trykk på F9. Se på resultatet. Trykk på F9 et par ganger for å se hvilke resultater du får. Når du får et høyt resultat, lagrer du de 8 inngangene og de tre utgangsvariablene. Fortsett å trykke på F9 til du får et bedre resultat. Lagre den ved å registrere de 8 inngangscellene og de 3 resultatcellene.

Makro for å lagre det nåværende resultatet

Denne makroen lagrer resultatene til neste 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 for å trykke F9 gjentatte ganger og sjekk resultatene

Skriv en makro for å trykke F9 gjentatte ganger, og logg bare på "bedre" løsninger. Stopp makroen når du kommer til de ønskede resultatene 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

Sidefelt om ScreenUpdating

Sidefelt: Først er det "morsomt" å se gjentagelsene rulle forbi. Men til slutt innser du at du kanskje må teste millioner av muligheter. Når du får Excel til å tegne skjermen på nytt, reduseres makroen. Bruk Application.ScreenUpdating = Usant for ikke å male skjermen på nytt.

Hver gang du får et nytt svar eller hver 1000, la Excel tegne skjermen på nytt. Problem: Excel tegner ikke skjermen på nytt med mindre cellepekeren beveger seg. Jeg fant ut at ved å velge en ny celle mens ScreenUpdating er sann, vil Excel male skjermen på nytt. Jeg bestemte meg for å få den til å veksle mellom tellercellen og de beste resultatene så langt.

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 vurderte mange titler for denne videoen: Trykk F9 til nær, Gjett inntil riktig, Brute Force Solving, Mål på nærhet

Merk at jeg prøvde å bruke Solver for å løse problemet. Men Solver kunne ikke komme i nærheten. Det ble aldri bedre enn 26 lag da målet var 28.

Legg også merke til at enhver løsning som jeg får i denne videoen er "dum-flaks". Det er ikke noe intelligent med løsningsmetoden. Makroen sier for eksempel ikke: "Vi bør starte fra den beste løsningen så langt og gjøre noen mikrojusteringer." Selv om du får en løsning som bare er ett nummer unna, trykker den blindt på F9 igjen. Det er sannsynligvis en mer intelligent måte å angripe problemet på. Men … akkurat nå … for vår svømmekommisjonær fungerte denne tilnærmingen.

Last ned arbeidsboken

Se på video

Last ned fil

Last ned eksempelfilen her: Podcast2180.zip

Interessante artikler...