Korištenje Excela za rješavanje bilo kojeg složenog modela
Lev je povjerenik natjecateljske plivačke lige. Piše: "Ja sam povjerenik plivačke lige. Ove godine postoji osam momčadi. Svaka momčad domaćin je jednog susreta i domaća je momčad. Susret će imati 4 ili 5 momčadi. Kako organizirati raspored tako da svaka momčad pliva svaka druga momčad dva puta? U prošlosti, kad smo imali 5, 6 ili 7 momčadi, mogao sam to riješiti pritiskom na F9 do kraja. Ali ove godine s 8 timova to ne izlazi. "
Jedno od ograničenja je da neki bazeni nude samo 4 staze, tako da možete imati samo 4 momčadi kada je taj bazen domaćin svečanosti. Za ostale bazene mogli bi imati 5, 6 ili više staza, ali idealni susret imat će domaća momčad i još četiri.
Moj prijedlog: Brže pritisnite F9! Da biste u tome pomogli: razvijte "mjeru bliskosti" u svom modelu. Na taj način, kad pritisnete F9, možete pripaziti na jedan broj. Kad pronađete "bolje" rješenje od najboljeg koje ste pronašli, spremite ga kao srednje najbolje rješenje.
Koraci specifični za problem plivanja
- Navedite 8 domaćih timova s vrha.
- Na koliko načina ispuniti ostale 4 trake?
- Nabroji sve načine.
- Na koliko načina ispuniti ostale 3 trake (za mala mjesta?). Nabroji sve načine.
- Koristite
RANDBETWEEN(1,35)za odabir momčadi za svaku utakmicu.
Imajte na umu da postoji 35 8 mogućih načina za raspored sezone (2,2 bilijuna). Bilo bi "nemoguće" sve ih napraviti s kućnim računalom. Da postoji samo 4000 mogućnosti, mogli biste ih sve napraviti, a to je videozapis za neki drugi dan. Ali s 2,2 bilijuna mogućnosti, nasumično pogađanje vjerojatnije će pronaći rješenja.
Razvijte mjeru bliskosti
U scenariju plivanja najvažnije je Pliva li svaka momčad dva puta protiv svake druge momčadi?
Uzmite trenutnih 8 slučajnih brojeva i upotrijebite formule za crtanje svih poklapanja. Navedi 28 mogućih uparivanja. Upotrijebite COUNTIFda biste vidjeli koliko se puta svako poklapanje događa s trenutnim slučajnim brojevima. Izbrojite koliko je 2 ili veće. Cilj je ovaj broj dovesti do 28.
Sekundarni cilj: Postoji 28 podudaranja. Svaka se treba dogoditi dva puta. To je 56 uparivanja koja se moraju dogoditi. Sa 8 bazena i 6 s pet staza, imat ćete 68 utakmica. To znači da će neke momčadi plivati protiv drugih 3 puta, a možda i 4 puta. Sekundarni cilj: Pobrinite se da što manje timova ima 4 uparivanja. Tercijarni cilj: Smanjiti maks.
Polaki način da se to riješi
Pritisnite F9. Pogledajte rezultat. Pritisnite F9 nekoliko puta da biste vidjeli kakve rezultate postižete. Kad dobijete visok rezultat, spremite 8 ulaza i tri izlazne varijable. Nastavite pritiskati F9 dok ne postignete bolji rezultat. Spremite to snimanjem 8 ulaznih ćelija i 3 ćelije rezultata.
Makronaredba za spremanje trenutnog rezultata
Ova makronaredba sprema rezultate u sljedeći redak.
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 SubMakro za ponovni pritisak na F9 i provjeru rezultata
Napišite makronaredbu da biste više puta pritisnuli F9, bilježeći samo "bolja" rješenja. Neka se makronaredba zaustavi kad dođete do željenih rezultata 28 i 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 SubBočna traka o Ažuriranju zaslona
Bočna traka: U početku je "zabavno" gledati kako se ponavljaju iteracije. Ali na kraju shvatite da ćete možda morati iskušati milijune mogućnosti. Ako Excel ponovno nacrta zaslon, usporava se makronaredba. Upotrijebite Application.ScreenUpdating = False da ne biste ponovno slikali zaslon.
Svaki put kad dobijete novi odgovor ili svakih 1000, dopustite da Excel ponovno nacrta zaslon. Problem: Excel ne precrtava zaslon ako se pokazivač ćelije ne pomakne. Otkrio sam da će odabirom nove ćelije dok je ScreenUpdating True (istinito ažuriranje zaslona) istinito, Excel ponovno slikati zaslon. Odlučio sam da se izmjenjuje između brojača i najboljih dosadašnjih rezultata.
Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").ValueAlternativna rješenja za rješavanje
Razmotrio sam mnoge naslove za ovaj video: Pritisnite F9 dok se ne zatvori, Pogodi dok se ne ispravi, Brute Force Solving, Mjera bliskosti
Imajte na umu da sam pokušavao riješiti problem pomoću rješenja Solver. Ali Solver se nije mogao približiti. Nikad nije bilo bolje od 26 momčadi kada je cilj bio 28.
Također imajte na umu da je svako rješenje koje nađem u ovom videozapisu "glupa sreća". U načinu rješavanja nema ništa pametno. Na primjer, makronaredba ne kaže: "Trebali bismo krenuti od najboljeg rješenja do sada i izvršiti neke mikro prilagodbe." Čak i ako dobijete rješenje koje je udaljeno samo jedan broj, slijepo ponovno pritisne F9. Vjerojatno postoji inteligentniji način napada na problem. Ali … upravo sada … za našeg povjerenika za plivanje ovaj je pristup uspio.
Preuzmite Radnu bilježnicu
Gledaj video
Preuzmi datoteku
Preuzmite uzorak datoteke ovdje: Podcast2180.zip
