Solver ha sido un complemento gratuito desde los días de Lotus 1-2-3
Excel no fue el primer programa de hojas de cálculo. Lotus 1-2-3 no fue el primer programa de hoja de cálculo. El primer programa de hoja de cálculo fue VisiCalc en 1979. Desarrollado por Dan Bricklin y Bob Frankston, VisiCalc fue publicado por Dan Fylstra. Hoy, Dan dirige Frontline Systems. Su empresa escribió el Solver utilizado en Excel. También ha desarrollado un conjunto completo de software de análisis que funciona con Excel.
Si tiene Excel, tiene Solver. Puede que no esté habilitado, pero lo tiene. Para habilitar Solver en Excel, presione alt = "" + T seguido de I. Agregue una marca de verificación junto a Solver.
Para usar Solver con éxito, debe crear un modelo de hoja de trabajo que tenga tres elementos:
- Tiene que haber una sola celda de Objetivo. Esta es una celda que desea minimizar, maximizar o establecer en un valor particular.
- Puede haber muchas celdas de entrada. Esta es una mejora fundamental con respecto a Goal Seek, que solo puede tratar con una celda de entrada.
- Puede haber limitaciones.
Su objetivo es establecer los requisitos de programación para un parque de atracciones. Cada empleado trabajará cinco días seguidos y luego tendrá dos días libres. Hay siete formas diferentes de programar a alguien para cinco días seguidos y dos días libres. Estos se muestran como texto en A4: A10. Las celdas azules en B4: B10 son las celdas de entrada. Aquí es donde especifica cuántas personas tiene trabajando en cada horario.
La celda Objetivo es la nómina total por semana, que se muestra en B17. Esto es pura matemática: Personas totales de B11 multiplicado por $ 68 de salario por persona por día. Le pedirá a Solver que encuentre una manera de minimizar la nómina semanal.
El cuadro rojo muestra valores que no cambiarán. Esta es la cantidad de personas que necesita para trabajar en el parque cada día de la semana. Necesita al menos 30 personas en los ajetreados días de fin de semana, pero tan solo 12 los lunes y martes. Las celdas naranjas usan SUMPRODUCT para calcular cuántas personas se programarán cada día según las entradas de las celdas azules.
Los íconos en la fila 15 indican si necesita más personas o menos personas o si tiene exactamente el número correcto de personas.
Primero, traté de resolver esto sin Solver. Fui con 4 empleados cada día. Eso fue genial, pero no tuve suficiente gente el domingo. Entonces, comencé a aumentar los horarios que me darían más empleados los domingos. Terminé con algo que funciona: 38 empleados y $ 2,584 de nómina semanal.
Haga clic en el icono de Solver en la pestaña Datos. Dígale a Solver que está tratando de establecer la nómina en B17 al mínimo. Las celdas de entrada son B4: B10.
Las restricciones se clasifican en categorías obvias y no tan obvias.
La primera restricción obvia es que D12: J12 tiene que ser> = D14: J14.
Pero, si intentara ejecutar Solver ahora, obtendría resultados extraños donde tiene un número fraccionario de personas y posiblemente un número negativo de personas trabajando en ciertos horarios.
Si bien le parece obvio que no puede contratar a 0.39 personas, debe agregar restricciones para decirle a Solver que B4: B10 son> = 0 y que B4: B10 son números enteros.
Elija Simplex LP como método de resolución y elija Resolver. En unos momentos, Solver presenta una solución óptima.
Solver encontró una manera de cubrir la dotación de personal del parque de diversiones con 30 empleados en lugar de 38. El ahorro por semana es de $ 544, o más de $ 7,000 durante el transcurso del verano.
Observe las cinco estrellas debajo de Empleados necesarios. El programa propuesto por Solver satisface sus necesidades exactas durante cinco de los siete días. El subproducto es que tendrá más empleados los miércoles y jueves de los que realmente necesita.
Puedo entender cómo se le ocurrió a Solver esta solución. Necesita mucha gente los sábados, domingos y viernes. Una forma de hacer que la gente esté allí ese día es dándoles libres los lunes y martes. Es por eso que Solver puso a 18 personas libres los lunes y martes.
Pero el hecho de que Solver haya creado una solución óptima no significa que no haya otras soluciones igualmente óptimas.
Cuando solo estaba adivinando la dotación de personal, realmente no tenía una buena estrategia.
Ahora que Solver me ha dado una de las soluciones óptimas, puedo ponerme mi sombrero de lógica. Tener 28 empleados en edad universitaria los miércoles y jueves cuando solo necesita 15 o 18 empleados va a generar problemas. No habrá suficiente para hacer. Además, con el recuento exacto de personal adecuado en cinco días, tendrá que llamar a alguien para que le hagan horas extras si alguien más llama por enfermedad.
Confío en Solver que necesito tener 30 personas para que esto funcione. Pero apuesto a que puedo reorganizar a esas personas para igualar el horario y proporcionar un pequeño búfer en otros días.
Por ejemplo, dar a alguien el miércoles y el jueves libres también garantiza que la persona esté en el trabajo el viernes, sábado y domingo. Entonces, moví manualmente algunos trabajadores de la fila de lunes, martes a la fila de miércoles y jueves. Seguí conectando manualmente diferentes combinaciones y se me ocurrió esta solución que tiene el mismo gasto de nómina que Solver pero mejores intangibles. La situación de exceso de personal existe ahora en cuatro días en lugar de dos. Eso significa que puede manejar las cancelaciones de lunes a jueves sin tener que llamar a alguien de su fin de semana.
¿Es malo que pude encontrar una solución mejor que Solver? No. El hecho es que no habría podido llegar a esta solución sin usar Solver. Una vez que Solver me dio un modelo que minimizaba los costos, pude usar la lógica sobre intangibles para mantener la misma nómina.
Si necesita resolver problemas más complejos de los que puede manejar Solver, consulte los solucionadores de Excel premium disponibles en Frontline Systems: http://mrx.cl/solver77.
Gracias a Dan Fylstra y Frontline Systems por este ejemplo. Walter Moore ilustró la montaña rusa XL.
Ver video
- Solver ha sido un complemento gratuito desde los días de Lotus 1-2-3
- Solver es un producto del fundador de Visicorp, Dan Fylstra
- Solver en su Excel es una versión más pequeña de los solucionadores de servicio pesado
- Obtenga más información sobre los solucionadores profesionales: http://mrx.cl/solver77
- Para instalar Solver, escriba alt = "" + T y luego I. Compruebe Solver.
- El solucionador se encuentra en el lado derecho de la pestaña Datos
- Desea tener una celda objetivo que está tratando de minimizar o maximizar.
- Puede especificar varias celdas de entrada.
- Puede especificar restricciones, incluidas algunas que no esperaría:
- Sin medias personas: use INT para Integer
- Solver encontrará una solución óptima, pero puede haber otras que sean atadas
- Una vez que obtenga la solución Solver, es posible que pueda modificarla.
Transcripción del video
Aprenda Excel del podcast, episodio 2036 - ¡Introducción a Solver!
Muy bien, estoy publicando un podcast de este libro completo, haga clic en la "i" en la esquina superior derecha para acceder a la lista de reproducción, ¡donde puede reproducir todos los videos!
Bienvenido de nuevo a netcast, soy Bill Jelen. Recientemente hablamos sobre algunos análisis Y si …, como Buscar objetivo, ya sabes, con una celda de entrada que estás cambiando, pero ¿y si tienes algo más complejo? Hay una gran herramienta llamada Solver, Solver ha existido durante mucho tiempo, te garantizo que si tienes Excel y estás ejecutando en Windows, tienes Solver, probablemente no esté activado. Entonces, para activarlo, debe ir a alt = "" T y luego I, entonces T para Tom, I para helado, y marque esta casilla para Solver, haga clic en Aceptar, y después de un par de segundos, tendrá una pestaña de Solver aquí en el lado derecho. Muy bien, y vamos a configurar un modelo aquí que el solucionador podría resolver, tenemos un parque de diversiones, estamos tratando de determinar cuántos empleados programar. Todo el mundo trabaja cinco días consecutivos, así que no 'Realmente hay siete horarios posibles en los que estás fuera, domingo lunes, lunes martes, martes miércoles. Tenemos que averiguar cuántos empleados poner en cada uno de esos horarios.
Y así, simplemente, una pequeña matemática aquí, haciendo algunos SUMPRODUCTs, número de empleados veces el domingo para calcular cuántas personas estaban allí el domingo, lunes, martes, miércoles. Y lo que hemos aprendido al operar este parque de diversiones es que necesitamos mucha gente los sábados y domingos. 30 personas los sábados y domingos, durante la semana lunes, martes, un poco lento, 12 empleados podrán hacerlo. Muy bien, con solo venir aquí y simplemente jugar, ya sabes, tratar de averiguar los números correctos, puedes seguir conectando cosas, pero con siete opciones diferentes, tomaría una eternidad, está bien.
Ahora en Solver, lo que tenemos es, tenemos una serie de celdas de entrada, y en la versión gratuita de Solver creo que puede tener, ¿son cien? No lo sé, hay un número, y si tiene que ir más allá, hay un solucionador premium que puede obtener de Frontline Systems. Muy bien, tenemos algunas celdas de entrada, tenemos algunas celdas de restricción, y luego tienes que reducirlo todo a un número final. Entonces, en mi caso, estoy tratando de minimizar la nómina por semana, de modo que ese número verde es lo que quiero probar y optimizar, está bien, ¡así que esto es lo que vamos a hacer!
Solucionador, aquí está la celda objetivo, es la celda verde, y quiero establecer eso en un valor mínimo, averiguar el personal que me da el valor mínimo, cambiando estas celdas azules. Y luego aquí están las restricciones, de acuerdo, así que la primera restricción es que el total de la programación tiene que ser> = la sección roja, y podemos hacer todo eso como una restricción única. Mira lo genial que es esto, todas estas celdas tienen que ser> = estas celdas correspondientes aquí, genial, haz clic en Agregar, está bien, pero luego hay otras cosas en las que no pensarías. Por ejemplo, Solver en este punto podría decidir que es mejor tener 17 personas en este horario, 43 personas en el horario y -7 personas en este horario. Muy bien, tenemos que decirle a Solver que estas celdas de entrada deben ser un número entero, haga clic en Agregar. Y además, no podemos permitir que alguien no se presente,y nos devolverán el sueldo, ¿no? Entonces vamos a decir que estas celdas tienen que ser> = 0, haga clic en Agregar, regresemos ahora, tenemos nuestras tres restricciones allí.
Hay tres formas diferentes de resolver, y esta sigue las matemáticas lineales, por lo que podemos ir a Simplex LP. Si este no funciona, entonces pruebe los otros dos, he tenido casos en los que Simplex dice que no puede encontrar una solución, y uno de los otros dos funciona. Frontline Systems tiene excelentes tutoriales sobre Solver, solo estoy tratando de ayudarlo a completar el primero aquí hoy, no proclamo ser un experto en Solver. Una vez tuve un Solver que no funcionaba y envié una nota a Frontline Systems, y ¡guau, recibí esta increíble carta de 5 páginas, de parte del mismo Dan Fylstra, el presidente de Solver! Y comenzó: "Querido Bill, ¡es bueno saber de ti!" Y luego continuó por 4.9 páginas, eso estaba casi completamente por encima de mi cabeza, está bien. Pero sabes, sé lo suficiente sobre Solver para superar esto, está bien,así que vamos a hacer clic aquí en Resolver, encontró una solución, "Se cumplen todas las restricciones y condiciones de optimización". Me quedaré con eso, puedo crear algunos informes, no es necesario que lo haga ahora. Oh, realmente puedo guardar un escenario, me burlé de los escenarios ayer, tal vez Solver podría crearme un nuevo escenario, así que haremos clic en Aceptar.
Muy bien, y efectivamente nos ha ahorrado dinero, escribimos 2584 antes, y ahora nos redujo a 2040. Así que necesitamos mucha gente libre el lunes y martes, está bien, algunas personas, 2 personas libres el miércoles jueves y luego viernes sábado. Bueno, esto es increíble, nunca se me hubiera ocurrido este conjunto de respuestas al azar, está bien, pero ¿eso significa que es la mejor respuesta? Bueno, significa que es la nómina mínima, pero probablemente pueda encontrar un conjunto diferente de respuestas que aún tengan esta nómina mínima. Hay otras formas de hacer eso, ese podría ser un horario un poco mejor. Como por ejemplo, ahora mismo tenemos 28 personas el miércoles y jueves, cuando solo necesitamos 15 y 18, eso es mucha gente. Piense en quién trabaja en los parques de atracciones, estos son chicos universitarios en casa para descansar,esto va a ser un problema si tenemos tanta gente extra. Y el lunes martes, estamos muertos, exactamente donde queremos estar. Entonces eso significa que si alguien a quien voy a llamar enfermo, ahora tendremos que, ya sabes, llamar a alguien y pagarle tiempo y medio, porque ya han trabajado otros cinco días.
Muy bien, con un poco de matemática simple aquí, si quito 8 del lunes martes, y lo hago 10, y tomo esos 8 y los agrego al miércoles jueves, está bien. Ahora tengo una solución Solver con la misma respuesta exacta, 2040, tienen la cantidad correcta de personas. Solo equilibro el horario, y ahora tenemos 8 extra, 8 extra, 3 extra y 2 extra, y exactamente lo que necesitamos el fin de semana, que es, ya sabes, el escenario de personal completo. Para mí, esto es un poco mejor de lo que se le ocurrió a Solver, ¿significa eso que el solucionador falló? No, absolutamente no, porque nunca me hubiera acercado tanto sin Solver. Una vez que Solver me dio la respuesta, sí, pude modificarlo un poco y llegar allí, está bien. Tip # 37, "40 mejores consejos de Excel de todos los tiempos", acercándose al final de los primeros 40, una gran introducción a Solver.La guía de todos los podcasts de esta serie está aquí, "MrExcel XL - 40 mejores consejos de Excel de todos los tiempos", puede tener el libro electrónico por solo $ 10, imprimir el libro por $ 25, haga clic en la "i" en la parte superior -¡esquina derecha!
Muy bien, resumen: Solver, si está en versiones de Windows de Excel, Lotus 1-2-3, está ahí, fue creado por el fundador de Visicorp, Dan Fylstra. Es una versión gratuita de los solucionadores de alta resistencia, aquí hay un enlace para ver los solucionadores de tareas pesadas, que estará abajo en los comentarios de YouTube. Es probable que simplemente no estén instalados, alt = "" TI, marque Solver, busque en el lado derecho de la pestaña Datos para encontrar Solver. Muy bien, debe tener una celda objetivo que está tratando de minimizar o maximizar o establecer en un valor, un rango de celdas de entrada. Especifique restricciones, incluyendo algo que no esperaría, como si tuviera que decir "No a medias personas" y "No a personas negativas". Solver encontrará la solución óptima, pero puede haber otras que sean ataduras y es posible que pueda modificarla para obtener una mejor solución.
Muy bien, ahí lo tienen, quiero agradecerles por pasar, ¡nos vemos la próxima vez para otro netcast de!
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2036.xlsx
