Cómo utilizar Excel 2019 Solver

Aunque los comandos Tabla de datos y Búsqueda de objetivos de Excel funcionan bien para problemas simples que requieren determinar la relación directa entre las entradas y los resultados en una fórmula, debe usar el complemento Solver cuando se trata de problemas más complejos. Por ejemplo, utilice Solver para encontrar la mejor solución cuando necesite cambiar varios valores de entrada en su modelo de Excel y necesite imponer restricciones a estos valores y / o al valor de salida.

El complemento Solver funciona aplicando métodos iterativos para encontrar la "mejor" solución dadas las entradas, la solución deseada y las restricciones que impone. Con cada iteración, el programa aplica un método de prueba y error (basado en el uso de ecuaciones y desigualdades lineales o no lineales) que intenta acercarse a la solución óptima.

Cuando utilice el complemento Solver, tenga en cuenta que muchos problemas, especialmente los más complicados, tienen muchas soluciones. Aunque Solver devuelve la solución óptima, dados los valores iniciales, las variables que pueden cambiar y las restricciones que define, esta solución a menudo no es la única posible y, de hecho, puede que no sea la mejor solución para usted. Para asegurarse de que está encontrando la mejor solución, es posible que desee ejecutar Solver más de una vez, ajustando los valores iniciales cada vez que resuelva el problema.

Al configurar el problema para el complemento Solver en su hoja de cálculo de Excel, defina los siguientes elementos:

• Celda de objetivo: la celda de destino en su hoja de trabajo cuyo valor se maximizará, minimizará o se hará para alcanzar un valor particular. Tenga en cuenta que esta celda debe contener una fórmula.
• Celdas variables: las celdas cambiantes en su hoja de trabajo cuyos valores se ajustarán hasta que se encuentre la respuesta.
• Celdas de restricción: las celdas que contienen los límites que impone a los valores cambiantes en las celdas variables y / o la celda objetivo en la celda objetivo.

Una vez que termine de definir el problema con estos parámetros y haga que el complemento Solver resuelva el problema, el programa devuelve la solución óptima modificando los valores en su hoja de trabajo. En este punto, puede optar por conservar los cambios en la hoja de trabajo o restaurar los valores originales en la hoja de trabajo. También puede guardar la solución como un escenario para ver más tarde antes de restaurar los valores originales.

Puede usar el complemento Solver con el Administrador de escenarios para ayudar a configurar un problema para resolver o para guardar una solución para que pueda verlo en una fecha posterior. Las celdas cambiantes que defina para el Administrador de escenarios son automáticamente recogidas y utilizadas por Solver cuando selecciona este comando, y viceversa. Además, puede guardar la solución de Solver a un problema como un escenario (haciendo clic en el botón Guardar escenario en el cuadro de diálogo Solver) que luego puede ver con el Administrador de escenarios.

Configurar y definir el problema en Excel 2019

El primer paso para configurar un problema para que el Solver trabaje es crear el modelo de hoja de trabajo para el cual definirá la celda objetivo, las celdas de variables y las celdas de restricción.

Tenga en cuenta que Solver es una utilidad complementaria. Esto significa que, antes de poder usarlo, debe asegurarse de que el programa de complemento Solver todavía esté cargado, como lo indica la aparición del botón Solver en el grupo Análisis al final de la pestaña Datos en la cinta. Si falta este botón, puede cargar Solver abriendo la pestaña Complementos del cuadro de diálogo Opciones de Excel (Alt + FTAA) y luego haciendo clic en el botón Ir después de asegurarse de que los Complementos de Excel se muestren en el menú desplegable Administrar. cuadro de lista a su izquierda inmediata. Luego, seleccione la casilla de verificación Complemento Solver en el cuadro de diálogo Complementos para poner una marca de verificación antes de hacer clic en Aceptar para cerrar el cuadro de diálogo y volver a cargar el complemento.

Para definir y resolver un problema con el complemento Solver después de que haya cargado el complemento y haya creado su modelo de hoja de trabajo, siga estos pasos:

Haga clic en el botón de comando Solver en el grupo Analizar al final de la pestaña Datos de la cinta.

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

Especificar los parámetros que se aplicarán al modelo en el cuadro de diálogo Parámetros de Solver.

Haga clic en la celda de destino en la hoja de trabajo o ingrese su referencia de celda o nombre de rango en el cuadro de texto Establecer objetivo.

A continuación, debe seleccionar la configuración Para. Haga clic en el botón de opción Máx cuando desee que el valor de la celda de destino sea lo más grande posible. Haga clic en el botón de opción Mínimo cuando desee que el valor de la celda objetivo sea lo más pequeño posible. Haga clic en el botón de opción Valor de y luego ingrese un valor en el cuadro de texto asociado cuando desee que el valor de la celda de destino alcance un valor particular.

Haga clic en la opción del botón de opción correspondiente en la sección Para del cuadro de diálogo. Si selecciona el botón de opción Valor de, ingrese el valor para que coincida en el cuadro de texto asociado.

A continuación, designe las celdas variables, es decir, las que Solver puede cambiar para alcanzar su objetivo Igual a.

Haga clic en el cuadro de texto Cambiando celdas variables y luego seleccione las celdas para cambiar en la hoja de trabajo o ingrese sus referencias de celda o nombre de rango en el cuadro de texto.

Recuerde que para seleccionar celdas no adyacentes en la hoja de trabajo, debe mantener presionada la tecla Ctrl mientras hace clic en cada celda de la selección. Para que Excel elija las celdas cambiantes según la celda de destino que seleccionó, haga clic en el botón Adivinar a la derecha de este cuadro de texto.

Antes de que Solver ajuste su modelo, puede agregar restricciones para la celda objetivo o cualquiera de las celdas cambiantes que determinan sus límites al ajustar los valores.

(Opcional) Haga clic en el botón Agregar a la derecha del cuadro de lista Sujeto a las restricciones en el cuadro de diálogo Parámetros de Solver.

Esta acción abre el cuadro de diálogo Agregar restricción. Al definir una restricción, elija la celda cuyo valor desea restringir o seleccione la celda en la hoja de trabajo o ingrese su referencia de celda en el cuadro de texto Referencia de celda. Luego seleccione la relación (=, <=,> =, o int para integer o bin para binary) del cuadro de lista desplegable a la derecha y (a menos que elija int o bin ) ingrese el valor apropiado o la referencia de celda en el Cuadro de texto de restricción.

Para continuar agregando restricciones para otras celdas utilizadas por Solver, haga clic en el botón Agregar para agregar la restricción y borre los cuadros de texto en el cuadro de diálogo Agregar restricción. Luego, repita el Paso 5 para agregar una nueva restricción. Una vez que haya terminado de definir las restricciones para la celda de destino y de cambiar los valores en el modelo, haga clic en Aceptar para cerrar el cuadro de diálogo Agregar restricción y volver al cuadro de diálogo Parámetros de Solver (que ahora enumera sus restricciones en el cuadro de lista Sujeto a las restricciones).

(Optional) Deselect the Make Unconstrained Variables Non-Negative check box if you want to allow negative values when the variable cells are not subject to constraints.
By default, the Solver Add-in employs the GRG (Generalized Reduced Gradient) Nonlinear method in solving the model whose parameters you’re setting known as a very efficient way to solve smooth nonlinear problems. To use the LP Simplex method (for Linear Programming following the Simplex algorithm) or Evolutionary engine for solving non-smooth problems, you need to follow Step 7.

(Optional) Select LP Simplex or Evolutionary from the Select a Solving Method drop-down list to use either one of these methods solving nonsmooth problems.

Click the Solve button to have the Solver solve the problem as you’ve defined it in the Solver Parameters dialog box.

Solving the problem with Excel’s Solver

When you click the Solve button, the Solver Parameters dialog box disappears, and the status bar indicates that the Solver is setting up the problem and then keeps you informed of the progress in solving the problem by showing the number of the intermediate (or trial) solutions as they are tried. To interrupt the solution process at any time before Excel calculates the last iteration, press the Esc key. Excel then displays the Show Trial Solution dialog box, informing you that the solution process has been paused. To continue the solution process, click the Continue button. To abort the solution process, click the Stop button.

When Excel finishes the solution process, the Solver Results dialog box appears. This dialog box informs you whether the Solver was able to find a solution, given the target cell, changing cells, and constraints defined for the problem. To retain the changes that the Solver makes in your worksheet model, leave the Keep Solver Solution option button selected and click OK to close the Solver Results dialog box. To return the original values to the worksheet, click the Restore Original Values option button instead. To save the changes as a scenario before you restore the original values, click the Save Scenario button and assign a name to the current scenario before you click the Restore Original Values option and OK button.

The Solver Results dialog box showing that Solver found a solution to the problem.

Unlike when using the Goal Seek command, after clicking the Keep Solver Solution option button in the Solver Results dialog box, you can’t use the Undo command button on the Quick Access toolbar to restore the original values to your worksheet. If you want to be able to switch between the “before” and “after” views of your worksheet, you must save the changes with the Save Scenario button and then select the Restore Original Values option button. That way, you can retain the “before” view in the original worksheet and use the Scenario Manager to display the “after” view created by the Solver.

Changing Excel’s Solver options

For most of the problems, the default options used by the Solver are adequate. In some situations, however, you may want to change some of the Solver options before you begin the solution process. To change the solution options, click the Options button in the Solver Parameters dialog box. Excel then opens the Options dialog box with the All Methods tab selected where you can make all necessary changes.

Modifying the solution options in the Options dialog box.

Excel 2019 Solver Option Settings

After changing the options, click OK to return to the Solver Parameters dialog box; from here, you can then click the Solve button to begin the solution process with the new solution settings that you just changed.

When you use the default GRG (Generalized Reduced Gradient) Nonlinear or Evolutionary method, you can set additional Solver settings using the options on the GRG Nonlinear and Evolutionary tabs of the Options dialog box. These options include changing the Converge, Population Size, and Random Seed settings for either of these particular methods.

Saving and loading a model problem in Excel 2019

The objective cell, variable cells, constraint cells, and Solver options that you most recently used are saved as part of the Excel worksheet when you click the Save button on the Quick Access toolbar (Ctrl+S). When you define other problems for the same worksheet that you want to save, you must click the Save Model button in the Solver Options dialog box and indicate the cell reference or name of the range in the active worksheet where you want the problem’s parameters to be inserted.

Cuando hace clic en el botón Cargar / Guardar, Excel abre el cuadro de diálogo Cargar / Guardar modelo, que contiene un cuadro de texto Seleccionar área del modelo. Este cuadro de texto contiene las referencias de celda para un rango lo suficientemente grande como para contener todos los parámetros del problema, comenzando con la celda activa. Para guardar los parámetros del problema en este rango, haga clic en Aceptar. Si este rango incluye celdas con datos existentes, debe modificar la referencia de celda en este cuadro de texto antes de hacer clic en Aceptar para evitar que Excel reemplace los datos existentes.

Después de hacer clic en Aceptar, Excel copia los parámetros del problema en el rango especificado. Estos valores se guardan como parte de la hoja de trabajo la próxima vez que guarde el libro. Para reutilizar estos parámetros del problema al resolver un problema, simplemente necesita abrir el cuadro de diálogo Opciones de Solver, hacer clic en el botón Cargar / Guardar para abrir el cuadro de diálogo Cargar / Guardar modelo, hacer clic en el botón Cargar y luego seleccionar el rango que contiene el archivo guardado. parámetros del problema. Cuando hace clic en Aceptar en el cuadro de diálogo Cargar modelo, Excel carga los parámetros de este rango de celdas en los cuadros de texto apropiados en el cuadro de diálogo Parámetros de Solver. Luego puede cerrar el cuadro de diálogo Opciones de Solver haciendo clic en Aceptar, y puede resolver el problema usando estos parámetros haciendo clic en el botón de comando Resolver.

Recuerde que puede usar el botón Restablecer todo siempre que desee borrar todos los parámetros definidos para el problema anterior y devolver las opciones de Solver a sus valores predeterminados.

Creación de informes de Solver en Excel 2019

Puede crear tres tipos diferentes de informes con Solver:

• Informe de respuesta: enumera la celda de destino y las celdas cambiantes con sus valores originales y finales, junto con las restricciones utilizadas para resolver el problema.
• Informe de sensibilidad: indica qué tan sensible es una solución óptima a los cambios en las fórmulas que calculan la celda de destino y las restricciones. El informe muestra las celdas cambiantes con sus valores finales y el gradiente reducido para cada celda. (El gradiente reducido mide el objetivo por unidad de aumento en la celda cambiante). Si definió restricciones, el informe de sensibilidad las enumera con sus valores finales y el multiplicador de Lagrange para cada restricción. (El multiplicador de Lagrange mide el objetivo por unidad de aumento que aparece en el lado derecho de la ecuación de restricción).
• Informe de límites: muestra la celda objetivo y las celdas cambiantes con sus valores, límites superior e inferior y resultados objetivo. El límite inferior representa el valor más bajo que puede tener una celda cambiante mientras fija los valores de todas las demás celdas y sigue cumpliendo las restricciones. El límite superior representa el valor más alto que hará esto.

Excel coloca cada informe que genera para un problema de Solver en una hoja de trabajo separada en el libro de trabajo. Para generar uno (o todos) de estos informes, seleccione el tipo de informe (Respuesta, Sensibilidad o Límites) en el cuadro de lista Informes del cuadro de diálogo Resultados de Solver. Para seleccionar más de un informe, simplemente haga clic en el nombre del informe.

Cuando hace clic en Aceptar para cerrar el cuadro de diálogo Resultados de Solver (después de elegir entre las opciones Mantener solución de Solver y Restaurar valores originales), Excel genera el informe (o informes) que seleccionó en una nueva hoja de trabajo que agrega al principio del libro. . (Las pestañas de la hoja de informe se nombran por tipo de informe, como en Informe de respuesta 1, Informe de sensibilidad 1 e Informe de límites 1 ).