Stiskněte klávesu F9 do zavření - Excel tipy

Vyřešení komplexního modelu pomocí aplikace Excel

Lev je komisařem soutěžní plavecké ligy. Píše: "Jsem komisař plavecké ligy. Letos je jich osm týmů. Každý tým hostí jedno setkání a je domácím týmem. Setkání bude mít 4 nebo 5 týmů. Jak uspořádat harmonogram, aby každý tým plaval proti každý druhý tým dvakrát? V minulosti, když jsme měli 5, 6 nebo 7 týmů, mohl jsem to vyřešit stisknutím F9 až do zavření. Ale letos, s 8 týmy, to nevychází. “

Jedním z omezení je, že některé bazény nabízejí pouze 4 pruhy, takže můžete mít pouze 4 týmy, když tento fond hostí gala. U ostatních bazénů mohou mít 5, 6 nebo více jízdních pruhů, ale ideální setkání bude mít domácí tým plus čtyři další.

Můj návrh: Stiskněte klávesu F9 rychleji! K tomu vám pomůže: ve svém modelu vytvořte „míru blízkosti“. Tímto způsobem, když stisknete F9, můžete sledovat jedno číslo. Když najdete „lepší“ řešení než to nejlepší, co jste našli, uložte jej jako přechodné nejlepší řešení.

Kroky specifické pro problém s plaváním

  • Seznam 8 domácích týmů nahoře.
  • Kolik způsobů, jak vyplnit další 4 pruhy?
  • Seznam všech způsobů.
  • Kolik způsobů, jak zaplnit další 3 pruhy (pro malá místa?). Seznam všech způsobů.
  • Pomocí RANDBETWEEN(1,35)vyberte týmy pro každý zápas.

Všimněte si, že existuje 35 8 možných způsobů, jak uspořádat sezónu (2,2 bilionu). Bylo by „nemožné“ je všechny provést pomocí domácího počítače. Kdyby existovalo jen 4000 možností, mohli byste je všechny udělat, a to je video na další den. Ale s 2,2 biliony možností je náhodná hádka pravděpodobnější najít řešení.

Vypracujte míru blízkosti

Ve scénáři plavání je nejdůležitější věcí plave každý tým dvakrát proti každému jinému týmu?

Vezměte současných 8 náhodných čísel a použijte vzorce k vykreslení všech shod. Seznam 28 možných zápasů. Slouží COUNTIFk zobrazení, kolikrát se každý zápas shoduje s aktuálními náhodnými čísly. Spočítejte, kolik je 2 nebo více. Cílem je dostat toto číslo na 28.

Sekundární cíl: Existuje 28 zápasů. Každý se musí stát dvakrát. To je 56 zápasů, které se musí stát. S 8 bazény a 6 s pěti drahami budete mít 68 zápasů. To znamená, že některé týmy budou plavat proti jiným týmům 3krát a možná 4krát. Sekundární cíl: Zajistěte, aby co nejméně týmů mělo 4 zápasy. Terciární cíl: Minimalizace max.

Pomalý způsob, jak to vyřešit

Stiskněte klávesu F9. Podívejte se na výsledek. Několikrát stiskněte klávesu F9, abyste zjistili, jaké výsledky dosahujete. Když dosáhnete vysokého výsledku, uložte 8 vstupů a tři výstupní proměnné. Držte stisknutou klávesu F9, dokud nedosáhnete lepšího výsledku. Uložte ten tím, že zaznamenáte 8 vstupních buněk a 3 buňky výsledků.

Makro pro uložení aktuálního výsledku

Toto makro uloží výsledky do dalšího řádku.

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 opakovaně stiskněte klávesu F9 a zkontrolujte výsledky

Napište makro a opakovaně stiskněte klávesu F9, přihlašujte pouze „lepší“ řešení. Když se dostanete k požadovaným výsledkům 28 a 0, nechte makro zastavit.

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

Postranní panel o aktualizaci obrazovky

Postranní panel: Zpočátku je „zábavné“ sledovat procházení iterací. Ale nakonec si uvědomíte, že možná budete muset vyzkoušet miliony možností. Když Excel znovu nakreslí obrazovku, zpomalí to makro. K překreslení obrazovky použijte Application.ScreenUpdating = False.

Pokaždé, když dostanete novou odpověď nebo každých 1000, nechte Excel znovu nakreslit obrazovku. Problém: Excel nepřekresluje obrazovku, dokud se ukazatel buňky nepohybuje. Zjistil jsem, že výběrem nové buňky, zatímco ScreenUpdating je True, by Excel znovu vymaloval obrazovku. Rozhodl jsem se, že to bude střídat buňku Counter a zatím nejlepší výsledky.

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

Alternativní řešení řešení

Zvažoval jsem mnoho titulů pro toto video: Stiskněte F9 do zavření, Hádejte, dokud nebude správné, Řešení hrubou silou, Míra blízkosti

Všimněte si, že jsem se pokusil problém vyřešit pomocí Řešitele. Řešitel se ale nemohl přiblížit. Když byl gól 28, nikdy to nebylo lepší než 26 týmů.

Všimněte si také, že jakékoli řešení, které v tomto videu dostanu, je „hloupé štěstí“. Na metodě řešení není nic inteligentního. Makro například neříká: „Měli bychom začít od dosud nejlepšího řešení a provést nějaké mikroúpravy.“ I když máte geta řešení, které je jen o jedno číslo, slepě znovu stiskne F9. Pravděpodobně existuje inteligentnější způsob, jak na problém zaútočit. Ale … právě teď … pro našeho komisaře plavání tento přístup fungoval.

Stáhněte si sešit

Sledovat video

Stáhnout soubor

Stáhněte si ukázkový soubor zde: Podcast2180.zip

Zajímavé články...