Pritisnite F9, dokler ne zaprete - Excel Nasveti

Uporaba Excela za reševanje poljubnega kompleksnega modela

Lev je komisar tekmovalne plavalne lige. Piše: "Jaz sem komisar v plavalni ligi. Letos je osem ekip. Vsaka ekipa gosti eno srečanje in je domača ekipa. Na sestanku bo 4 ali 5 ekip. Kako urediti urnik, tako da vsaka ekipa plava proti vsako drugo ekipo dvakrat? V preteklosti, ko smo imeli 5, 6 ali 7 ekip, sem to lahko rešil tako, da sem pritisnil F9 do konca. Letos pa z 8 ekipami to ne izide. "

Ena od omejitev je, da nekateri bazeni ponujajo samo 4 pasove, tako da lahko imate samo 4 ekipe, ko ta bazen gosti slavnostne prireditve. Za druge bazene imajo morda 5, 6 ali več pasov, idealno srečanje pa bodo imeli domači in še štirje.

Moj predlog: Hitreje pritisnite F9! Za pomoč pri tem: v svojem modelu razvijte "mero bližine". Tako lahko, ko pritisnete F9, pazite na eno številko. Ko najdete "boljšo" rešitev od najboljše, ki ste jo našli, jo shranite kot vmesno najboljšo rešitev.

Posebni koraki za težavo s plavanjem

  • Na vrhu navedite 8 domačih ekip.
  • Na koliko načinov zapolniti ostale 4 pasove?
  • Naštej vse načine.
  • Koliko načinov za zapolnitev ostalih 3 pasov (za majhna prizorišča?). Naštej vse načine.
  • Uporabite RANDBETWEEN(1,35)za izbiro ekip za vsako tekmo.

Upoštevajte, da obstaja 35 8 možnih načinov za ureditev sezone (2,2 bilijona). Z domačim računalnikom bi bilo vse nemogoče. Če bi bilo na voljo le 4000 možnosti, bi lahko naredili vse, to pa je video za kakšen drug dan. Toda z 2,2 bilijona možnosti bo naključno ugibanje bolj verjetno našlo rešitve.

Razvijte mero bližine

V scenariju plavanja je najpomembnejše Ali vsaka ekipa dvakrat plava proti vsaki drugi ekipi?

Vzemite trenutnih 8 naključnih števil in s pomočjo formul začrtajte vsa ujemanja. Naštejte 28 možnih ujemanj. Uporabite, COUNTIFda vidite, kolikokrat se vsako ujemanje dogaja s trenutnimi naključnimi številkami. Preštejte, koliko jih je 2 ali več. Cilj je povečati to število na 28.

Sekundarni cilj: Obstaja 28 ujemanj. Vsak se mora zgoditi dvakrat. To je 56 tekem, ki se morajo zgoditi. Z 8 bazeni in 6 s petimi stezami boste imeli 68 ujemanj. To pomeni, da bodo nekatere ekipe proti drugim ekipam plavale 3-krat in morda 4-krat. Sekundarni cilj: Poskrbite, da bo čim manj ekip imelo 4 dvoboje. Terciarni cilj: zmanjšati maks.

Počasi to rešiti

Pritisnite F9. Poglejte rezultat. Nekajkrat pritisnite F9, da vidite, katere rezultate dosežete. Ko dobite visok rezultat, shranite 8 vhodov in tri izhodne spremenljivke. Pritiskajte F9, dokler ne dosežete boljših rezultatov. To shranite tako, da zabeležite 8 vhodnih celic in 3 celice rezultatov.

Makro za shranjevanje trenutnega rezultata

Ta makro shrani rezultate v naslednjo vrstico.

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 pritisnite na F9 večkrat in preverite rezultate

Napišite makro, da večkrat pritisnete F9 in zabeležite samo "boljše" rešitve. Naj se makro ustavi, ko pridete do želenih rezultatov 28 in 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

Stranska vrstica o posodabljanju zaslona

Stranska vrstica: Sprva je "zabavno" opazovati ponavljanje. Toda sčasoma se zaveš, da boš morda moral preizkusiti milijone možnosti. Če Excel na novo nariše zaslon, upočasni makro. Uporabite Application.ScreenUpdating = False, da zaslona ne prebarvate.

Vsakič, ko prejmete nov odgovor ali vsakih 1000, pustite, da Excel znova nariše zaslon. Težava: Excel ne prerisuje zaslona, ​​razen če se kazalec celice premakne. Ugotovil sem, da bi Excel z izbiro nove celice, medtem ko ScreenUpdating je True, znova naslikal zaslon. Odločil sem se, da bo zamenjal celico Counter in najboljše rezultate doslej.

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

Nadomestne rešitve za reševanje

Upošteval sem veliko naslovov za ta video: Pritisnite F9 do konca, Ugani do pravilnega, Brute Force Solving, Measure of Closeness

Upoštevajte, da sem poskusil s pomočjo rešitve rešiti težavo. Toda Solver se ni mogel približati. Nikoli ni bilo boljše od 26 ekip, ko je bil cilj 28.

Upoštevajte tudi, da je vsaka rešitev, ki jo dobim v tem videoposnetku, "neumna sreča". V načinu reševanja ni nič pametnega. Na primer, makro ne pravi: "Morali bi izhajati iz najboljše rešitve doslej in narediti nekaj mikro prilagoditev." Tudi če imate geta rešitev, ki je oddaljena le eno številko, znova slepo pritisne F9. Verjetno obstaja bolj inteligenten način napada problema. Ampak … zdaj … za našega komisarja za plavanje je ta pristop deloval.

Prenesite delovni zvezek

Oglejte si video

Prenesite datoteko

Prenesite vzorčno datoteko tukaj: Podcast2180.zip

Zanimive Članki...