Usar Excel para resolver cualquier modelo complejo
Lev es comisionado de una liga de natación competitiva. Él escribe: "Soy el comisionado de una liga de natación. Hay ocho equipos este año. Cada equipo organiza una competencia y es el equipo local. Una competencia tendrá 4 o 5 equipos. Cómo organizar el calendario para que cada equipo nade contra ¿Cada dos equipos dos veces? En el pasado, cuando teníamos 5, 6 o 7 equipos, podía resolverlo presionando F9 hasta cerrar. Pero este año, con 8 equipos, no sale ".
Una de las limitaciones es que algunos grupos solo ofrecen 4 carriles, por lo que solo puede tener 4 equipos cuando ese grupo alberga la gala. Para otros grupos, pueden tener 5, 6 o más carriles, pero el encuentro ideal tendrá el equipo local más otros cuatro.
Mi sugerencia: ¡Presione F9 más rápido! Para ayudar con eso: desarrolle una "medida de cercanía" en su modelo. De esa manera, cuando presione F9, podrá vigilar un número. Cuando encuentre una solución "mejor" que la mejor que haya encontrado, guárdela como la mejor solución intermedia.
Pasos específicos para el problema de natación
- Enumere los 8 equipos locales en la parte superior.
- ¿Cuántas formas de llenar los otros 4 carriles?
- Enumere todas las formas.
- ¿Cuántas formas de llenar los otros 3 carriles (para lugares pequeños?). Enumere todas las formas.
- Úselo
RANDBETWEEN(1,35)para elegir equipos para cada partido.
Tenga en cuenta que hay 35 8 formas posibles de organizar la temporada (2,2 billones). Sería "imposible" hacerlos todos con una PC doméstica. Si solo hubiera 4000 posibilidades, podrías hacerlas todas, y ese es un video para otro día. Pero con 2,2 billones de posibilidades, es más probable que adivinar al azar encuentre soluciones.
Desarrollar una medida de cercanía
En el escenario de natación, lo más importante es: ¿Todos los equipos nadan dos veces contra todos los demás equipos?
Tome los 8 números aleatorios actuales y use fórmulas para trazar todas las coincidencias. Enumera los 28 posibles emparejamientos. Úselo COUNTIFpara ver cuántas veces ocurre cada emparejamiento con los números aleatorios actuales. Cuenta cuántos son 2 o más. El objetivo es llevar este número a 28.
Objetivo secundario: Hay 28 enfrentamientos. Cada uno debe suceder dos veces. Son 56 enfrentamientos los que tienen que suceder. Con 8 grupos y 6 con cinco carriles, se producirán 68 enfrentamientos. Eso significa que algunos equipos nadarán contra otros equipos 3 veces y posiblemente 4 veces. Objetivo secundario: Asegúrate de que el menor número posible de equipos tengan 4 enfrentamientos. Objetivo terciario: Minimizar el Max.
Manera lenta de resolver esto
Presione F9. Mira el resultado. Presione F9 varias veces para ver qué resultados obtiene. Cuando obtenga un resultado alto, guarde las 8 entradas y las tres variables de salida. Sigue presionando F9 hasta que obtengas un mejor resultado. Guárdelo registrando las 8 celdas de entrada y las 3 celdas de resultado.
Macro para guardar el resultado actual
Esta macro guarda los resultados en la siguiente fila.
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 para presionar F9 repetidamente y verificar los resultados
Escriba una macro para presionar F9 repetidamente, registrando solo soluciones "mejores". Haga que la macro se detenga cuando llegue a los resultados deseados de 28 y 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 lateral sobre ScreenUpdating
Barra lateral: Al principio, es "divertido" ver pasar las iteraciones. Pero finalmente te das cuenta de que es posible que tengas que probar millones de posibilidades. Hacer que Excel vuelva a dibujar la pantalla ralentiza la macro. Utilice Application.ScreenUpdating = False para no volver a pintar la pantalla.
Cada vez que obtenga una nueva respuesta o cada 1000, deje que Excel vuelva a dibujar la pantalla. Problema: Excel no vuelve a dibujar la pantalla a menos que se mueva el puntero de la celda. Descubrí que al seleccionar una nueva celda mientras ScreenUpdating es True, Excel volvería a pintar la pantalla. Decidí alternar entre la celda Contador y los Mejores resultados hasta ahora.
Application.ScreenUpdating = True If Selection.Address = "$T$1" Then Cells(NR, 34).Select Else Range("T1").Select End If Application.ScreenUpdating = Range("AH2").ValueSoluciones de resolución alternativas
Consideré muchos títulos para este video: Presione F9 hasta cerrar, Adivine hasta que sea correcto, Resolución de fuerza bruta, Medida de cercanía
Tenga en cuenta que intenté usar Solver para resolver el problema. Pero Solver no pudo acercarse. Nunca mejoró que 26 equipos cuando el gol era 28.
También tenga en cuenta que cualquier solución que obtengo en este video es "mala suerte". No hay nada inteligente en el método de resolución. Por ejemplo, la macro no dice: "Debemos partir de la mejor solución hasta ahora y hacer algunos microajustes". Incluso si obtiene una solución que está a solo un número de distancia, presiona ciegamente F9 nuevamente. Es probable que exista una forma más inteligente de atacar el problema. Pero … ahora mismo … para nuestro comisionado de natación, este enfoque funcionó.
Descarga el libro de trabajo
Ver video
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2180.zip
