Una vez mas usaremos Excel para resolver casos de la vida diaria, pero en esta ocasión le toca al sistema SOLVER.
SOLVER es una herramienta de análisis de escenario incluida (pero no activada) en la paquetería de Excel con la cual podemos resolver escenarios complejos indicándole a Excel las condiciones para resolverlo.
En mi caso tenia que resolver como armar tres equipos de futbol de siete personas cada uno, pero deben de quedar lo mas equilibrados posibles para que la competencia sea justa. Cada jugador tiene una calificación promedio que se le asigno por sus habilidades en escala 0 a 1.
Lo primero que se me ocurrió fue obtener el promedio de todos los jugadores y después obtener la diferencia de su calificación vs su promedio (ordenándolos así del más grande al más chico). Esto me ayudaría a sabes cuales jugadores eran los mejores y cuales los peores.
Damos un poco de formato y agregamos las columnas donde seleccionaremos a nuestros jugadores para cada equipo. Adicional, agregaremos una columna al final para comprobar que ningún jugador se repita mas de una vez por equipo y, en la parte de debajo de cada columna, una suma para comprobar cuantos jugares hay en cada equipo.
Como también quiero saber la calificación en habilidad promedio de cada equipo agregaremos en la parte inferior SUMAPRODUCTO para que, por cada jugador, nos de el acumulado del equipo. El punto final para SOLVER será darle una celda objetivo, por lo que crearemos una suma de los SUMAPRODUCTO para saber que tan grande es la diferencia de los totales de habilidad de cada equipo.
Ahora usemos SOLVER que se encuentra en la pestaña Datos de la cinta de opciones. Si no lo tuvieras puedes ir a Programador-Complementos de Excel y activar la opción que dice SOLVER.
Una vez dentro, veremos una pantalla así, en la cual iremos dando forma a nuestras condiciones y celda de referencia.
Ahora, entendamos que es lo que queremos:
- Queremos que nuestra ABS(SUMA) sea lo menor posible para dejar los equipos equilibrados, por lo que esta será nuestra celda objetivo con la opción MIN
- Queremos que las celdas que cambien sean las que designan a cada jugador en su respectivo equipo, por lo que ese será nuestro rango de cambio de valores.
- Las condiciones para la designación de equipo son
- Tiene que haber 7 jugadores por equipo.
- No puede repetirse ningún jugador en ningún equipo.
- El resultado total de los tres equipos debe de ser de 21.
- No puede estar un jugador dos veces en el mismo equipo.
Por lo tanto, nuestra pantalla en SOLVER queda así:
El tiempo del resultado debe de ser inferior a 1 min según la máquina, pero al finalizar presionaremos “Conservar solución de Solver” y veremos cómo está resuelto.
Si bien la respuesta esta hecha cumpliendo todas las condiciones hay algo que a mi no me acabo de gustar, así que les compartiré algo mas avanzado cuando lo encuentre jeje.
Prueben a usar SOLVER, es una maravilla para escenarios difíciles y para las dudas nos vemos abajo.