Excel Solver: optimización de resultados, adición de restricciones y ahorro de soluciones como escenarios

Configure su modelo de Excel Solver mediante el cuadro de diálogo Parámetros de Solver. Utilice el cuadro Establecer objetivo para especificar la celda del objetivo y utilice el grupo Para para decirle a Excel Solver lo que desea de la celda del objetivo: el valor máximo posible; el valor mínimo posible; o un valor específico. Finalmente, use el cuadro Cambiando celdas variables para especificar las celdas que Solver puede usar para insertar valores y optimizar el resultado.

Optimización de los resultados de Excel Solver

Cuando Solver encuentra una solución, puede elegir Conservar la solución de Solver o Restaurar valores originales. Si elige Keep Solver Solution, Excel cambia permanentemente la hoja de trabajo. No puede deshacer los cambios.

Con su modelo de hoja de trabajo listo para Solver listo para usar, estos son los pasos a seguir para encontrar un resultado óptimo para su modelo usando Solver:

Elija Datos → Solver.
Excel abre el cuadro de diálogo Parámetros de Solver.

En el cuadro Establecer objetivo, ingrese la dirección de la celda objetivo de su modelo.
Tenga en cuenta que si hace clic en la celda para ingresarla, Solver ingresa automáticamente una dirección de celda absoluta (por ejemplo, $ B $ 14 en lugar de B14). Solver funciona bien de cualquier manera.

En el grupo Para, seleccione una opción:

• Max: devuelve el valor máximo posible.
• Mín .: Devuelve el valor mínimo posible.
• Valor de: Ingrese un número para establecer la celda objetivo en ese número.

  Para el modelo de ejemplo, se ha seleccionado Valor de y se ingresó 0 en el cuadro de texto.

En el cuadro Cambiando celdas variables, ingrese las direcciones de las celdas que desea que Solver cambie mientras busca una solución.
En el ejemplo, las celdas cambiantes son B4 y C4. La siguiente imagen muestra el cuadro de diálogo Parámetros de Solver completado.

El cuadro de diálogo Parámetros de Solver completado.

Haga clic en Resolver.
Solver se pone manos a la obra. A medida que Solver trabaja en el problema, es posible que vea los cuadros de diálogo Mostrar solución de prueba aparecer una o más veces.

En cualquier cuadro de diálogo Mostrar solución de prueba que aparezca, haga clic en Continuar para mover las cosas.
Cuando se completa la optimización, Excel muestra el cuadro de diálogo Resultados de Solver.

El cuadro de diálogo Solver Results y la solución al problema de equilibrio.

Seleccione la opción Keep Solver Solution.
Si no desea aceptar el resultado, seleccione la opción Restaurar valores originales.

Haga clic en Aceptar.

Puede pedirle a Solver que muestre uno o más informes que le brinden información adicional sobre los resultados. En el cuadro de diálogo Resultados de Solver, utilice la lista Informes para seleccionar cada informe que desee ver:

• Respuesta: muestra información sobre la celda objetivo, las celdas variables y las restricciones del modelo. Para la celda objetivo y las celdas variables, Solver muestra los valores original y final.
• Sensibilidad: intenta mostrar qué tan sensible es una solución a los cambios en las fórmulas del modelo. El diseño del informe de sensibilidad depende del tipo de modelo que esté utilizando.
• Límites: muestra la celda objetivo y su valor, así como las celdas variables y sus direcciones, nombres y valores.

Excel Solver puede utilizar uno de varios métodos de resolución. En el cuadro de diálogo Parámetros de Solver, use la lista Seleccionar un método de solución para seleccionar uno de los siguientes:

• LP simplex: utilícelo si su modelo de hoja de trabajo es lineal. En los términos más simples posibles, un modelo lineal es aquel en el que las variables no se elevan a ninguna potencia y no se utiliza ninguna de las llamadas funciones trascendentes, como SIN y COS.
• GRG Nonlinear: Úselo si su modelo de hoja de trabajo es no lineal y suave. En términos generales, un modelo suave es aquel en el que una gráfica de la ecuación utilizada no muestra bordes afilados o roturas.
• Evolutivo: utilícelo si su modelo de hoja de trabajo no es lineal ni uniforme.

¿Tienes que preocuparte por algo de esto? Es casi seguro que no. Excel Solver utiliza de forma predeterminada GRG Nonlinear, y eso debería funcionar para casi cualquier cosa que haga con Solver.

Agregar restricciones a Excel Solver

El mundo real pone restricciones y condiciones a las fórmulas. Una fábrica puede tener una capacidad máxima de 10,000 unidades por día, el número de empleados en una empresa no puede ser un número negativo y sus costos de publicidad pueden estar restringidos al 10 por ciento de los gastos totales.

De manera similar, suponga que está ejecutando un análisis de equilibrio en dos productos. Si ejecuta la optimización sin ninguna restricción, Solver podría alcanzar una ganancia total de 0 estableciendo un producto con una ligera pérdida y el otro con una ligera ganancia, donde la pérdida y la ganancia se anulan entre sí. De hecho, si observa de cerca la imagen anterior, esto es exactamente lo que hizo Solver. Para obtener una verdadera solución de equilibrio, es posible que prefiera ver ambos valores de beneficio del producto como 0.

Estas restricciones y condiciones son ejemplos de lo que Solver llama restricciones. Agregar restricciones le dice a Solver que encuentre una solución para que estas condiciones no se violen.

A continuación, se explica cómo ejecutar Solver con restricciones agregadas a la optimización:

Elija Datos → Solver.
Excel abre el cuadro de diálogo Parámetros de Solver.

Utilice el cuadro Establecer objetivo, el grupo Para y el cuadro Cambiando celdas variables para configurar Solver como se describe anteriormente.

Haga clic en Agregar.
Excel muestra el cuadro de diálogo Agregar restricción.

En el cuadro Referencia de celda, ingrese la dirección de la celda que desea restringir.
Puede escribir la dirección o seleccionar la celda en la hoja de trabajo.

En la lista desplegable, seleccione el operador que desea utilizar.
La mayoría de las veces, utiliza un operador de comparación, como igual a (=) o mayor que (>). Utilice el operador int (entero) cuando necesite que una restricción, como el total de empleados, sea un valor entero en lugar de un número real (es decir, un número con un componente decimal; ¡no puede tener 10,5 empleados!). Utilice el operador bin (binario) cuando tenga una restricción que deba ser VERDADERA o FALSA (o 1 o 0).

If you chose a comparison operator in Step 5, in the Constraint box, enter the value by which you want to restrict the cell.
This image shows an example of a completed Add Constraint dialog box. In the example model, this constraint tells Solver to find a solution such that the product profit of the Inflatable Dartboard (cell B12) is equal to 0.

The completed Add Constraint dialog box.

To specify more constraints, click Add and repeat Steps 4 through 6, as needed.

For the example, you add a constraint that asks for the Dog Polisher product profit (cell C12) to be 0.

Click OK.

Excel returns to the Solver Parameters dialog box and displays your constraints in the Subject to the Constraints list box.

Click Solve.

En cualquier cuadro de diálogo Mostrar solución de prueba que aparezca, haga clic en Continuar para mover las cosas.
La siguiente imagen muestra el ejemplo de solución de equilibrio con las restricciones agregadas. Observe que no solo la celda de Beneficio total (B14) se establece en 0, sino también las dos celdas de Beneficio del producto (B12 y C12).

El cuadro de diálogo Solver Results y la solución final al problema de equilibrio.

Seleccione la opción Keep Solver Solution.
Si no desea aceptar el resultado, seleccione la opción Restaurar valores originales.

Haga clic en Aceptar.

Puede agregar un máximo de 100 restricciones. Además, si necesita realizar un cambio en una restricción antes de comenzar a resolver, seleccione la restricción en el cuadro de lista Sujeto a las restricciones, haga clic en Cambiar y luego realice los ajustes en el cuadro de diálogo Cambiar restricción que aparece. Si desea eliminar una restricción que ya no necesita, seleccione la restricción y luego haga clic en Eliminar.

Guarde una solución de Excel Solver como escenario

Siempre que tenga un modelo de hoja de cálculo que utilice un conjunto coherente de valores de entrada, conocido como celdas cambiantes , tendrá lo que Excel llama escenario. Con Solver, estas celdas cambiantes son sus celdas variables, por lo que una solución de Solver equivale a una especie de escenario en Excel . Sin embargo, Solver no le ofrece una forma sencilla de guardar y volver a ejecutar una solución en particular. Para solucionar este problema, puede guardar una solución como un escenario que luego podrá recuperar utilizando la función Administrador de escenarios de Excel.

Siga estos pasos para guardar una solución de Solver como escenario:

Elija Datos → Solver.
Excel abre el cuadro de diálogo Parámetros de Solver.

Utilice el cuadro Establecer objetivo, el grupo Para, el cuadro Cambiando celdas variables y la lista Sujeto a las restricciones para configurar Solver como se describe anteriormente.

Haga clic en Resolver.

Siempre que aparezca el cuadro de diálogo Mostrar solución de prueba, elija Continuar.
Cuando se completa la optimización, Excel muestra el cuadro de diálogo Resultados de Solver.

Haga clic en Guardar escenario.
Excel muestra el cuadro de diálogo Guardar escenario.

En el cuadro de diálogo Nombre del escenario, escriba un nombre para el escenario y luego haga clic en Aceptar.
Excel lo regresa al cuadro de diálogo Resultados de Solver.

Seleccione la opción Keep Solver Solution.
Si no desea aceptar el resultado, seleccione la opción Restaurar valores originales.

Haga clic en Aceptar.