Utilizzo di Excel per risolvere qualsiasi modello complesso
Lev è commissario di un campionato di nuoto competitivo. Scrive: "Sono il commissario di un campionato di nuoto. Ci sono otto squadre quest'anno. Ogni squadra ospita un incontro ed è la squadra di casa. Un incontro avrà 4 o 5 squadre. Come organizzare il programma in modo che ogni squadra nuoti contro ogni altra squadra due volte? In passato, quando avevamo 5, 6 o 7 squadre, potevo risolverlo premendo F9 fino a chiudere. Ma quest'anno, con 8 squadre, non esce ".
Uno dei vincoli è che alcune piscine offrono solo 4 corsie, quindi puoi avere solo 4 squadre quando quella piscina ospita il gala. Per gli altri pool, potrebbero avere 5, 6 o più corsie, ma l'incontro ideale avrà la squadra di casa più altre quattro.
Il mio consiglio: premi F9 più velocemente! Per aiutare in questo: sviluppa una "misura di vicinanza" nel tuo modello. In questo modo, quando premi F9, puoi tenere d'occhio un numero. Quando trovi una soluzione "migliore" della migliore che hai trovato, salvalo come soluzione migliore intermedia.
Passaggi specifici per il problema del nuoto
- Elenca le 8 squadre di casa in alto.
- Quanti modi per riempire le altre 4 corsie?
- Elenca tutti i modi.
- Quanti modi per riempire le altre 3 corsie (per piccoli locali?). Elenca tutti i modi.
- Utilizzare
RANDBETWEEN(1,35)per scegliere le squadre per ogni partita.
Nota che ci sono 35 8 modi possibili per organizzare la stagione (2,2 trilioni). Sarebbe "impossibile" farli tutti con un PC di casa. Se ci fossero solo 4000 possibilità, potresti farle tutte, e questo è un video per un altro giorno. Ma con 2,2 trilioni di possibilità, è più probabile che indovinare casualmente trovi soluzioni.
Sviluppa una misura di vicinanza
Nello scenario di nuoto, la cosa più importante è che ogni squadra nuota contro ogni altra squadra due volte?
Prendi gli attuali 8 numeri casuali e usa le formule per tracciare tutti gli abbinamenti. Elenca i 28 possibili abbinamenti. Utilizzare COUNTIFper vedere quante volte si verifica ogni incontro con i numeri casuali correnti. Conta quanti sono 2 o più. L'obiettivo è portare questo numero a 28.
Obiettivo secondario: ci sono 28 matchup. Ciascuno deve accadere due volte. Sono 56 matchup che devono avvenire. Con 8 pool e 6 con cinque corsie, avrai 68 matchup. Ciò significa che alcune squadre nuoteranno contro altre squadre 3 volte e possibilmente 4 volte. Obiettivo secondario: assicurati che il minor numero di squadre possibile abbia 4 match-up. Obiettivo terziario: ridurre al minimo il valore max.
Modo lento per risolvere questo problema
Premi F9. Guarda il risultato. Premi F9 alcune volte per vedere quali risultati stai ottenendo. Quando si ottiene un risultato elevato, salvare gli 8 input e le tre variabili di output. Continua a premere F9 fino a ottenere un risultato migliore. Salva quello registrando le 8 celle di input e le 3 celle di risultato.
Macro per salvare il risultato corrente
Questa macro salva i risultati nella riga successiva.
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 SubMacro per premere ripetutamente F9 e controllare i risultati
Scrivi una macro per premere ripetutamente F9, registrando solo soluzioni "migliori". Fermare la macro quando si arriva ai risultati desiderati di 28 e 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 SubBarra laterale su ScreenUpdating
Barra laterale: all'inizio, è "divertente" guardare le iterazioni scorrere. Ma alla fine ti rendi conto che potresti dover testare milioni di possibilità. Fare in modo che Excel ridisegna lo schermo rallenta la macro. Usa Application.ScreenUpdating = False per non ridipingere lo schermo.
Ogni volta che ottieni una nuova risposta o ogni 1000, lascia che Excel ridisegni lo schermo. Problema: Excel non ridisegna lo schermo a meno che il puntatore della cella non si sposti. Ho scoperto che selezionando una nuova cella mentre ScreenUpdating è True, Excel ridipinge lo schermo. Ho deciso di alternare tra la cella Counter e i migliori risultati finora.
Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").ValueSoluzioni di risoluzione alternative
Ho considerato molti titoli per questo video: Press F9 Until Close, Guess Until Correct, Brute Force Solving, Measure of Closeness
Nota che ho provato a utilizzare Risolutore per risolvere il problema. Ma il Risolutore non è riuscito ad avvicinarsi. Non è mai andata meglio di 26 squadre quando l'obiettivo era 28.
Nota anche che qualsiasi soluzione che ottengo in questo video è "scema". Non c'è niente di intelligente nel metodo di risoluzione. Ad esempio, la macro non dice: "Dovremmo partire dalla migliore soluzione finora e fare alcuni micro aggiustamenti". Anche se ottieni una soluzione distante solo un numero, preme nuovamente F9 alla cieca. Probabilmente esiste un modo più intelligente per affrontare il problema. Ma … in questo momento … per il nostro responsabile del nuoto, questo approccio ha funzionato.
Scarica la cartella di lavoro
Guarda un video
Download file
Scarica il file di esempio qui: Podcast2180.zip
