Používanie programu Excel na riešenie ľubovoľného zložitého modelu
Lev je komisárom súťažnej plaveckej ligy. Píše: "Som komisárom plaveckej ligy. Tento rok má osem tímov. Každý tím hostí jedno stretnutie a je domácim tímom. Stretnutie bude mať 4 alebo 5 tímov. Ako zariadiť harmonogram, aby každý tím plával proti každý druhý tím dvakrát? V minulosti, keď sme mali 5, 6 alebo 7 tímov, som to mohol vyriešiť stlačením F9 až do konca. Tento rok to ale s 8 tímami nevychádza. “
Jednou z obmedzení je, že niektoré bazény ponúkajú iba 4 jazdné pruhy, takže môžete mať iba 4 tímy, keď sa v tomto bazéne koná gala. Pre ostatné bazény môžu mať 5, 6 alebo viac pruhov, ale ideálne stretnutie bude mať domáci tím a ďalšie štyri.
Môj návrh: Stlačte kláves F9 rýchlejšie! Pomôcť s tým: vytvoriť „mieru blízkosti“ vo svojom modeli. Takto môžete po stlačení klávesu F9 sledovať jedno číslo. Ak nájdete „lepšie“ riešenie ako to najlepšie, čo ste našli, uložte si ho ako stredne najlepšie riešenie.
Kroky špecifické pre problém s plávaním
- Zoznam 8 domácich tímov v hornej časti.
- Koľko spôsobov vyplnenia ďalších 4 jazdných pruhov?
- Uveďte všetky spôsoby.
- Koľko spôsobov vyplnenia ďalších 3 pruhov (pre malé podniky?). Uveďte všetky spôsoby.
- Pomocou
RANDBETWEEN(1,35)vyberte tímy pre každý zápas.
Upozorňujeme, že existuje 35 8 možných spôsobov usporiadania sezóny (2,2 bilióna). Bolo by „nemožné“ urobiť ich všetky pomocou domáceho počítača. Keby bolo iba 4000 možností, mohli by ste ich všetky urobiť, a to je video na ďalší deň. Ale s 2,2 biliónmi možností je pravdepodobné, že riešenie nájde náhodný odhad.
Vytvorte mieru blízkosti
V scenári plávania je najdôležitejšia vec Pláva každé mužstvo proti každému druhému dvakrát?
Vezmite súčasných 8 náhodných čísel a pomocou vzorcov vykreslite všetky zhody. Zoznam 28 možných zápasov. Pomocou COUNTIFtohto tlačidla môžete zistiť, koľkokrát sa jednotlivé zápasy zhodujú s aktuálnymi náhodnými číslami. Spočítajte, koľko je 2 alebo viac. Cieľom je dostať toto číslo na 28.
Sekundárny gól: Existuje 28 zápasov. Každá sa musí stať dvakrát. To je 56 zápasov, ktoré sa musia stať. S 8 bazénmi a 6 s piatimi pruhmi budete mať 68 zápasov. To znamená, že niektoré tímy budú plávať proti iným tímom trikrát a možno štyrikrát. Sekundárny cieľ: Zabezpečte, aby čo najmenej tímov malo 4 zápasy. Terciárny cieľ: Minimalizovať max.
Pomalý spôsob riešenia tohto problému
Stlačte kláves F9. Pozri sa na výsledok. Niekoľkokrát stlačte kláves F9, aby ste zistili, aké výsledky dosahujete. Ak získate vysoký výsledok, uložte 8 vstupov a tri výstupné premenné. Stále stlačte kláves F9, kým nedosiahnete lepší výsledok. Ten uložte zaznamenaním 8 vstupných buniek a 3 buniek s výsledkami.
Makro na uloženie aktuálneho výsledku
Toto makro uloží výsledky do nasledujúceho riadku.
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 na opakované stlačenie klávesu F9 a kontrola výsledkov
Napíšte makro, aby ste opakovane stláčali kláves F9, pričom sa prihlasujú iba „lepšie“ riešenia. Nechajte makro zastaviť, keď získate požadované výsledky 28 a 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čný panel o aktualizácii obrazovky
Bočný panel: Spočiatku je „zábavné“ sledovať prechádzajúce iterácie. Ale nakoniec si uvedomíte, že možno budete musieť otestovať milióny možností. Ak Excel znova nakreslí obrazovku, makro sa spomalí. Na opätovné vymaľovanie obrazovky použite Application.ScreenUpdating = False.
Zakaždým, keď dostanete novú odpoveď alebo každých 1 000, nechajte program Excel znova nakresliť obrazovku. Problém: Program Excel neprekresľuje obrazovku, pokiaľ sa neposunie ukazovateľ bunky. Zistil som, že výberom novej bunky, zatiaľ čo ScreenUpdating je True, program Excel znova vymaľuje obrazovku. Rozhodol som sa, že to budem striedať medzi bunkou Počítadlo a Doteraz najlepšími výsledkami.
Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").ValueAlternatívne riešenia riešenia
Zvažoval som veľa titulov pre toto video: stlačte F9 do zatvorenia, hádajte, kým nebude správne, riešenie hrubou silou, miera blízkosti
Všimnite si, že som sa na vyriešenie problému pokúsil použiť Riešiteľ. Riešiteľ sa ale nedokázal priblížiť. Keď bol gól 28, nikdy to nebolo lepšie ako 26 tímov.
Upozorňujeme tiež, že každé riešenie, ktoré v tomto videu dostanem, je „hlúposť“. Na metóde riešenia nie je nič inteligentného. Napríklad makro nehovorí: „Mali by sme vychádzať z zatiaľ najlepšieho riešenia a urobiť nejaké mikroúpravy.“ Aj keď máte riešenie geta, ktoré je len o jedno číslo, znova slepo stlačí kláves F9. Pravdepodobne existuje inteligentnejší spôsob riešenia problému. Ale … práve teraz … pre nášho komisára pre plávanie tento prístup fungoval.
Stiahnite si zošit
Pozeraj video
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2180.zip
