Używanie programu Excel do rozwiązywania złożonych modeli
Lev jest komisarzem konkurencyjnej ligi pływackiej. Pisze: „Jestem komisarzem ligi pływackiej. W tym roku jest osiem drużyn. Każda drużyna jest gospodarzem jednego spotkania i jest gospodarzem. Spotkanie będzie miało 4 lub 5 drużyn. Jak ułożyć harmonogram, aby każda drużyna pływała przeciwko co drugi zespół dwa razy? W przeszłości, gdy mieliśmy 5, 6 lub 7 zespołów, mogłem rozwiązać ten problem naciskając klawisz F9 aż do zamknięcia. Ale w tym roku, przy 8 zespołach, to nie wychodzi ”.
Jednym z ograniczeń jest to, że niektóre baseny oferują tylko 4 tory, więc możesz mieć tylko 4 drużyny, gdy w tej puli odbywa się gala. W przypadku innych pul mogą mieć 5, 6 lub więcej pasów, ale idealne spotkanie będzie miało drużynę gospodarzy i cztery inne.
Moja sugestia: naciśnij F9 szybciej! Aby w tym pomóc: opracuj „miarę bliskości” w swoim modelu. W ten sposób, naciskając klawisz F9, możesz mieć oko na jedną liczbę. Gdy znajdziesz „lepsze” rozwiązanie niż najlepsze, które znalazłeś, zapisz je jako najlepsze rozwiązanie pośrednie.
Kroki specyficzne dla problemu pływania
- Wymień 8 drużyn gospodarzy u góry.
- Na ile sposobów zapełnienia pozostałych 4 pasów?
- Wypisz wszystkie sposoby.
- Na ile sposobów zapełnienia pozostałych 3 pasów (w przypadku małych obiektów?). Wypisz wszystkie sposoby.
- Służy
RANDBETWEEN(1,35)do wybierania drużyn do każdego meczu.
Zauważ, że istnieje 35 8 możliwych sposobów zorganizowania sezonu (2,2 biliona). Wykonanie ich wszystkich na domowym komputerze byłoby „niemożliwe”. Gdyby było tylko 4000 możliwości, mógłbyś zrobić je wszystkie, a to jest film na kolejny dzień. Ale przy 2,2 bilionach możliwości losowe zgadywanie ma większe szanse na znalezienie rozwiązań.
Opracuj miarę bliskości
W scenariuszu pływania najważniejszą rzeczą jest to, czy każda drużyna pływa przeciwko każdej innej drużynie dwa razy?
Weź aktualne 8 liczb losowych i użyj formuł, aby wykreślić wszystkie dopasowania. Wymień 28 możliwych pojedynków. Użyj, COUNTIFaby zobaczyć, ile razy ma miejsce każdy pojedynek z aktualnymi liczbami losowymi. Policz, ile jest 2 lub więcej. Celem jest zwiększenie tej liczby do 28.
Drugorzędny cel: jest 28 pojedynków. Każdy musi się zdarzyć dwa razy. To 56 pojedynków, które muszą się wydarzyć. Z 8 pulami i 6 z pięcioma torami rozegranych zostanie 68 pojedynków. Oznacza to, że niektóre drużyny będą pływać przeciwko innym drużynom 3 razy, a być może 4 razy. Cel drugorzędny: Upewnij się, że jak najmniej drużyn rozegra 4 pojedynki. Trzeci cel: zminimalizować maks.
Powolny sposób na rozwiązanie tego problemu
Naciśnij klawisz F9. Spójrz na wynik. Naciśnij kilka razy klawisz F9, aby zobaczyć, jakie wyniki uzyskasz. Kiedy uzyskasz wysoki wynik, zapisz 8 wejść i trzy zmienne wyjściowe. Naciskaj klawisz F9, aż uzyskasz lepszy wynik. Zapisz ten, rejestrując 8 komórek wejściowych i 3 komórki wynikowe.
Makro, aby zapisać bieżący wynik
To makro zapisuje wyniki w następnym wierszu.
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, aby kilkakrotnie nacisnąć klawisz F9 i sprawdzić wyniki
Napisz makro, aby kilkakrotnie nacisnąć klawisz F9, rejestrując tylko „lepsze” rozwiązania. Zatrzymaj makro, gdy osiągniesz pożądane wyniki 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 SubPasek boczny o ScreenUpdating
Pasek boczny: Na początku „zabawnie” jest obserwowanie kolejnych iteracji. Ale w końcu zdajesz sobie sprawę, że być może będziesz musiał przetestować miliony możliwości. Ponowne narysowanie ekranu w programie Excel spowalnia działanie makra. Użyj Application.ScreenUpdating = False, aby nie odświeżać ekranu.
Za każdym razem, gdy otrzymasz nową odpowiedź lub co 1000, pozwól programowi Excel ponownie narysować ekran. Problem: Excel nie odświeża ekranu, dopóki wskaźnik komórki się nie poruszy. Zauważyłem, że wybierając nową komórkę, gdy ScreenUpdating ma wartość True, program Excel ponownie malował ekran. Postanowiłem, że będzie to naprzemiennie między komórką Counter i najlepszymi dotychczas wynikami.
Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").ValueAlternatywne rozwiązania w zakresie rozwiązywania problemów
Rozważałem wiele tytułów tego filmu: Press F9 Until Close, Guess Until Correct, Brute Force Solving, Measure of Closeness
Zauważ, że próbowałem użyć Solvera do rozwiązania problemu. Ale Solver nie mógł się zbliżyć. Nigdy nie było lepiej niż 26 drużyn, gdy bramka wynosiła 28.
Zwróć również uwagę, że każde rozwiązanie, które otrzymałem w tym filmie, to „głupie szczęście”. W metodzie rozwiązywania nie ma nic inteligentnego. Na przykład makro nie mówi: „Powinniśmy zacząć od najlepszego do tej pory rozwiązania i dokonać drobnych korekt”. Nawet jeśli otrzymasz rozwiązanie, które jest oddalone tylko o jedną liczbę, ponownie na ślepo naciska klawisz F9. Prawdopodobnie istnieje bardziej inteligentny sposób rozwiązania problemu. Ale… teraz… dla naszego komisarza ds. Pływania takie podejście zadziałało.
Pobierz skoroszyt
Obejrzyj wideo
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2180.zip
