Tal vez te ha tocado resolver problemas de optimización de valores, o incluso maximizar producción, minimizar costos o simplemente llegar a un valor específico. A veces optamos por llenar hojas y hojas con formulas para hacer estos procedimientos, sin embargo, Excel te ofrece una opción ideal para llegar a este tipo de problemas y es a través del complemento SOLVER.
Contenido del artículo
SOLVER es un complemento de análisis y de hipótesis que te va a ayudar a llegar al mejor resultado definiendo una celda que tiene una fórmula (la celda objetivo), esta celda esta sujeta a restricciones o limitaciones en los valores. Es decir, que valores vamos a cambiar con qué restricciones para que pueda dar un resultado óptimo.
El complemento Solver trabaja con un grupo de celdas denominadas celdas de variables que se utilizan para calcular fórmulas en las celdas objetivo o de restricción. Por lo tanto, puedes determinar el valor mínimo o máximo de una celda haciendo modificaciones en otras.
Solver es una herramienta que te ayuda a resolver y optimizar ecuaciones mediante el uso de métodos matemáticos.
Hay 3 formas de activar el complemento Solver en Excel.
La primera, es buscando en el menú de tu libro de Excel la opción “Programador”, luego en el área de complementos hacer clic en “Complementos de Excel”. Automáticamente se abrirá un cuadro de diálogo con los complementos disponibles, escoge la opción “Solver” y por último haz clic en “Aceptar”.
Para verificar su activación, ve al menú principal en la opción “Datos” y en el área de “Análisis” podrás visualizar la opción “Solver”.
La segunda, es accediendo a “Archivo”, luego “Opciones”, seguido “Complementos”, seleccionar la opción “Solver” y por último hacer clic en “Aceptar”, y para verificar la activación seguir el mismo procedimiento anterior.
La tercera es haciendo clic directamente en el buscador, elegir la opción “Complementos” y se mostrará un cuadro de diálogo como en la primera opción, seleccionas “Solver” y luego aceptar.
Supongamos que tienes un presupuesto de 1200 euros para comprar productos o alimentos de la empresa “Feria de Hortalizas C.A”. Entonces, debes utilizar la totalidad del presupuesto siempre y cuando se cumplan las restricciones mencionadas.
En tu libro de Excel, tienes una tabla especificada con los productos y su respectivo precio, así como, una columna con la cantidad que queremos comprar y el total, el cual se refleja con una fórmula matemática simple de multiplicación, que sería, multiplicar el precio por la cantidad, quedando la fórmula en la columna D2 de esta manera: =B2*C2
Y al final de la columna, tener una fórmula con la sumatoria total de los gastos que sería: =SUMA(D2:D13)
Pues, con el complemento Solver queremos optimizar el resultado de esa fórmula cambiando valores que en este caso serían las cantidades.
Como se tiene un presupuesto de 1200 y se debe utilizar la totalidad del mismo, entonces tienes que elegir o insertar las cantidades hasta completar ese monto, sin embargo, con Solver vamos a buscar una solución óptima.
Entonces, vamos a utilizar Solver sin restricciones, recuerda que, para utilizar el complemento, lo tendrías que haber activado previamente. Por lo tanto, ve al menú principal en la opción “Datos”, busca en el área de “Análisis” y haz clic en “Solver”.
En el cuadro de diálogo “Parámetros de Solver” te aparecerán una serie de opciones que deberás completar.
Por ejemplo, en la casilla “Establecer objetivo”, agregarás la fórmula que deseas que cumpla una condición. En este caso como la condición es el presupuesto de 1200 euros y debes utilizarlo, entonces tu “Celda Objetivo” será la sumatoria del total de gastos o D14 que es la multiplicación de precio por cantidad.
Con Solver en la opción “Para”, cómo utilizamos el método simplex o ecuaciones de programación lineal aquí puedes elegir el máximo, el mínimo o simplemente definirle un valor, que, para este ejemplo, el valor definido es 1200, así que ese valor de 1200 es el que buscamos que resulte en la celda objetivo.
Es fundamental que no olvides que la celda objetivo debe ser una fórmula. Ahora, si tuvieras algún planteamiento en donde se te pide maximiza la producción, baja costos, entonces tú eliges máximo o mínimo para ese caso.
Bien, para llegar al valor de 1200 que es el presupuesto, tenemos que cambiar ciertas celdas. En la casilla “Cambiando las celdas de variables” deberás incluir las celdas de “Cantidad” que son las variables, es decir, de: C2 a C13 (C2:C13).
En la parte inferior aparece la opción “Método de resolución”, esto como es temas matemáticos de programación lineal y el método más utilizado es el método simplex que son simplemente ecuaciones lineales para por ejemplo maximizar minimizar o simplemente buscar un objetivo entonces utilizaremos este.
Una vez seleccionado entonces haces clic en “Resolver”. Automáticamente aparece un cuadro de diálogo llamado “Resultados de Solver” en el que expresa que “Solver encontró una solución. Se cumplen todas las restricciones y condiciones óptimas”.
Entonces, deberás elegir la opción “Conservar solución de Solver” y opcionalmente escoger la casilla “Volver al cuadro de diálogo de parámetros de Solver” y hacer clic en “Aceptar”.
Si visualizas en la parte izquierda el complemento expresó fácilmente compra 150 especies y con eso llegas a los 1200 euros, pero, ¿Qué sucede aquí?
Es donde entran ya las condiciones y las restricciones, es decir, se quiere llegar a los 1200 pero eligiendo cierta cantidad de productos dependiendo del artículo.
Ahora bien, en el cuadro de diálogo, se incluirán las restricciones.
Es decir, se quiere cumplir con el presupuesto de 1200 pero comprando 15kg de cebollas, para ello, selecciona “Agregar” e incluye en la referencia de celda, la celda de cantidad del producto “Cebollas”, elige el signo igual y en la restricción el número “15”, ya que, se quiere comprar si o si 15kg de cebollas. Luego, haz clic en “Agregar”.
Otras de las restricciones que se quieren incluir es que en las cantidades existan valores enteros no con decimales, es decir, no 15.5 kg de cebollas por ejemplo (imagen izquierda); y además que todos los alimentos tengan al menos 1 producto (imagen derecha).
Para las últimas restricciones se seleccionan toda la columna de cantidad, esto a fin de que aplique para todas. Entonces quedaría de esta manera:
Al final, en el cuadro de parámetros de Solver, en la sección de "Sujeto a las restricciones" aparecerán todas las que hayas incluido.
Abajo del cuadro de restricciones dice “Convertir variables sin restricciones en no negativos”, esto qué significa que algunas soluciones de Solver te pueden decir o te sugiere que tú tengas valores en negativos y aquí como no aplicaría los valores negativos porque se quiere al menos un artículo de cada categoría, entonces, es por eso que se le insertó “mayor o igual a 1”. De todos modos, se dejar marcada esta opción para que no permita valores negativos.
Ya verificado los datos, haz clic en “Resolver”. Ahora podrás visualizar en el cuadro de diálogo “Resultados de Solver” donde expresa que Solver encontró una solución en donde dice que las cebollas cumplen con 15 y las piñas con 40, por su parte, los limones reflejan 1, como en la restricción expresaba menor a 20, podía haberse expresado, 1, 2 3 4 etc. pero menos que 20.
Entonces cumpliéndose esas condiciones el resto ya mostró varias opciones para llegar a los 1200 y ahora en la opción de “informes” elige “responder”. En la parte inferior dice “Crea el tipo de informe que se especifique y coloca cada informe en una hoja separada del libro”. Haz clic en “Informe de esquema” y luego en “Aceptar”.
Seguido, se inserta una nueva hoja que dice “Informe de respuestas 1” en donde ya muestra al detalle en donde está la celda, el total de gastos que es de 1200 y también muestra detalladamente todo lo contenido en ese informe.
Con Solver pudiste visualizar que se puede llegar a un objetivo definiendo qué celdas queremos cambiar y depende por supuesto de tu planteamiento, y tal vez debas cumplir ciertas restricciones.
