Apăsați F9 până la închidere - Sfaturi Excel

Utilizarea Excel pentru rezolvarea oricărui model complex

Lev este comisarul unei ligi competitive de înot. El scrie: "Sunt comisarul unei ligi de înot. Există opt echipe în acest an. Fiecare echipă găzduiește o întâlnire și este echipa de acasă. O întâlnire va avea 4 sau 5 echipe. Cum să aranjați programul, astfel încât fiecare echipă să înoate împotriva orice altă echipă de două ori? În trecut, când aveam 5, 6 sau 7 echipe, puteam rezolva apăsând F9 până la închidere. Dar anul acesta, cu 8 echipe, nu mai iese. "

Una dintre constrângeri este că unele piscine oferă doar 4 benzi, deci nu puteți avea 4 echipe decât atunci când acea piscină găzduiește gala. Pentru alte bazine, acestea ar putea avea 5, 6 sau mai multe benzi, dar întâlnirea ideală va avea echipa de acasă plus alte patru.

Sugestia mea: apăsați F9 mai repede! Pentru a ajuta la acest lucru: dezvoltați o „măsură de apropiere” în modelul dvs. În acest fel, atunci când apăsați F9, puteți să vă urmăriți un singur număr. Când găsiți o soluție „mai bună” decât cea mai bună pe care ați găsit-o, salvați-o ca cea mai bună soluție intermediară.

Pași specifici problemei înotului

• Enumerați cele 8 echipe de acasă din partea de sus.
• Câte modalități de a umple celelalte 4 benzi?
• Enumerați toate căile.
• Câte modalități de a umple celelalte 3 benzi (pentru locuri mici?). Enumerați toate căile.
• Folosiți RANDBETWEEN(1,35)pentru a alege echipe pentru fiecare meci.

Rețineți că există 35 8 modalități posibile de a aranja sezonul (2,2 trilioane). Ar fi „imposibil” să le faci pe toate cu un PC de acasă. Dacă ar exista doar 4000 de posibilități, le-ați putea face pe toate și acesta este un videoclip pentru o altă zi. Dar cu 2,2 trilioane de posibilități, ghicirea la întâmplare este mai probabil să găsească soluții.

Elaborați o măsură de apropiere

În scenariul de înot, cel mai important lucru este Fiecare echipă înoată împotriva oricărei alte echipe de două ori?

Luați cele 8 numere aleatorii actuale și utilizați formule pentru a trasa toate potrivirile. Enumerați cele 28 de posibile potriviri. Utilizați COUNTIFpentru a vedea de câte ori se întâmplă fiecare meci cu numerele aleatorii actuale. Numărați câte sunt 2 sau mai mari. Scopul este de a aduce acest număr la 28.

Obiectiv secundar: sunt 28 de meciuri. Fiecare trebuie să se întâmple de două ori. Adică 56 de meciuri care trebuie să se întâmple. Cu 8 bazine și 6 cu cinci benzi, veți avea 68 de meciuri. Asta înseamnă că unele echipe vor înota împotriva altor echipe de 3 ori și posibil de 4 ori. Scop secundar: Asigurați-vă că cât mai puține echipe au 4 meciuri. Scop terțiar: Minimizați max.

Mod lent de a rezolva acest lucru

Apăsați F9. Uită-te la rezultat. Apăsați F9 de câteva ori pentru a vedea ce rezultate obțineți. Când obțineți un rezultat ridicat, salvați cele 8 intrări și cele trei variabile de ieșire. Continuați să apăsați F9 până când obțineți un rezultat mai bun. Salvați-l înregistrând cele 8 celule de intrare și cele 3 celule rezultate.

Macro pentru a salva rezultatul curent

Această macrocomandă salvează rezultatele pe rândul următor.

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

Macro pentru a apăsa F9 în mod repetat și verificați rezultatele

Scrieți o macro pentru a apăsa F9 în mod repetat, înregistrând doar soluții „mai bune”. Opriți macrocomanda când ajungeți la rezultatele dorite de 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 Sub

Bara laterală despre ScreenUpdating

Bara laterală: la început, este „distractiv” să urmăriți iterațiile trecând. Dar în cele din urmă îți dai seama că s-ar putea să trebuiască să testezi milioane de posibilități. Dacă Excel re-desenează ecranul încetinește macro-ul. Utilizați Application.ScreenUpdating = Fals pentru a nu revopsi ecranul.

De fiecare dată când primiți un răspuns nou sau la fiecare 1000, lăsați Excel să deseneze din nou ecranul. Problemă: Excel nu redesenează ecranul decât dacă se deplasează indicatorul de celulă. Am constatat că, selectând o nouă celulă în timp ce ScreenUpdating este True, Excel va revopsi ecranul. Am decis să o alternez între celula Counter și cele mai bune rezultate de până acum.

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

Soluții alternative de rezolvare

Am luat în considerare multe titluri pentru acest videoclip: Apăsați F9 Până la închidere, Ghici până la corect, Rezolvarea forței brute, Măsura apropierii

Rețineți că am încercat să folosesc Solver pentru a rezolva problema. Dar Solver nu s-a putut apropia. Nu a fost niciodată mai bun decât 26 de echipe când golul era 28.

De asemenea, rețineți că orice soluție pe care o primesc în acest videoclip este „noroc”. Nu există nimic inteligent în metoda de rezolvare. De exemplu, macro-ul nu spune: „Ar trebui să pornim de la cea mai bună soluție de până acum și să facem câteva micro-ajustări”. Chiar dacă obțineți o soluție care este la doar un număr distanță, ea apasă orbește din nou pe F9. Există probabil un mod mai inteligent de a ataca problema. Dar … chiar acum … pentru comisarul nostru de înot, această abordare a funcționat.

Descărcați registrul de lucru

Urmăriți videoclipul

Descărcare fișier

Descărcați exemplarul de fișier aici: Podcast2180.zip