Activar Excel SolverPor Moisés Ortíz el 1 de julio del 2011

Solver es un complemento de Excel que nos ayuda a trabajar con modelos de negocio y nos permite resolver problemas lineales y no lineales.  En esta ocasión mostraré cómo activar este complemento en Excel 2010.

Cómo activar Solver en ExcelSolver está incluido dentro de Excel pero se encuentra desactivado de manera predeterminada. Para poder habilitarlo debes ir a la ficha Archivo y elegir Opciones y se mostrará el cuadro de diálogo Opciones de Excel donde deberás seleccionar Complementos.

En el panel derecho encontrarás el complemento llamado Solver. Para activarlo debes hacer clic en el botón Ir de la sección Administrar.

Se mostrará el cuadro de diálogo Complementos y deberás marcar la casilla de verificación de Solver y aceptar los cambios.

Para utilizar el complemento Solver debes ir a la ficha Datos y Excel habrá creado un nuevo grupo llamado Análisis el cual contendrá el comando Solver.

Al hacer clic sobre ese comando se mostrará el cuadro de diálogo Parámetros de Solver el cual nos permitirá configurar y trabajar con el complemento recién instalado.

En el próximo artículo mostraré un ejemplo práctico sobre cómo utilizar este complemento de Excel en nuestro análisis de datos.

Utilizando Excel SolverPor Moisés Ortíz el 2 de julio del 2011

Si tienes la necesidad de realizar un pronóstico que involucra más de una variable, puedes utilizar Solver en Excel. Este complemento ayudará a analizar escenarios de negocio multivariable y de optimización.

Ejemplo de uso de SolverEl ejemplo es el siguiente. Tengo un establecimiento de venta de pizzas que ofrece dos tipos de pizza tradicionales, Pepperoni ($30) y Vegetariana ($35) además de la pizza especial Suprema ($45). No sabemos cuál es el potencial de ingresos del establecimiento y tampoco el énfasis que se debería de dar a cada tipo de pizza para maximizar las ventas.

Antes de realizar el análisis debemos considerar las siguientes condiciones. Dada nuestra capacidad de producción solamente podemos elaborar 150 pizzas al día. Otra condición es que no podemos exceder de 90 pizzas tradicionales (Pepperoni y Vegetariana) y además, al no haber muchos vegetarianos en el área, estimamos vender un máximo de 25 pizzas vegetarianas al día. Otra condición a considerar es que solamente podemos comprar los ingredientes necesarios para producir 60 pizzas Suprema por día.

Con esta información elaboraré la siguiente hoja de Excel:

Observa que en los datos están representadas todas las reglas de negocio del establecimiento. Para cada tipo de pizza he colocado el total de pizzas a vender (por ahora en cero), el subtotal de cada una, así como el total de ventas que esta formado por la suma

de los subtotales. Además bajo el título Restricciones he colocado las condiciones previamente mencionadas.

Algo muy importante es establecer las equivalencias para las restricciones. Por ejemplo, una restricción es que el total de pizzas no puede exceder de 150, pero Excel no necesariamente sabe lo que significa “Total de pizzas”, así que he destinado una celda para especificar que el total de pizzas es la suma de las celdas B2+B6+B10. Lo mismo sucede para explicar lo que significa Pizzas Tradicionales.

Los datos ya están listos para utilizar Solver, así que debes ir a la ficha Datos y hacer clic en el comando Solver donde se mostrará el cuadro de diálogo Parámetros de Solver.

En nuestro ejemplo lo que queremos maximizar son las ventas totales por lo que en el cuadro de texto Establecer objetivo está especificada la celda $E$1 y por supuesto seleccioné la opción Máx. El otro parámetro importante son las celdas de variables que en nuestro ejemplo son las pizzas a vender para cada uno de los diferentes tipos.

Finalmente observa cómo en el cuadro de restricciones están reflejadas las condiciones de venta del establecimiento. Pon especial atención a la manera en que se han utilizado las equivalencias que son las celdas $E$10 y $E$11.

Todo está listo para continuar. Solamente debes hacer clic en el botón Resolver y Excel comenzará a calcular diferentes valores para las celdas variables hasta encontrar el valor máximo para las ventas totales. Al término del cálculo se mostrará el cuadro de diálogo Resultados de Solver.

Solamente haz clic en Aceptar para ver los resultados en la hoja de Excel.

Excel ha hecho los cálculos para saber que, con las restricciones establecidas, tendremos un valor máximo de venta total  de $5,525. Ahora fácilmente podrías cambiar los valores de las restricciones y volver a efectuar el cálculo con Solver para observar el comportamiento en las ventas.

SolverMostrar todo

Solver es parte de una serie de comandos a veces denominados herramientas de análisis Y si. Con Solver, puede buscarse el valor óptimo para una fórmula de celda, denominada celda objetivo, en una hoja de cálculo. Solver funciona en un grupo de celdas que estén relacionadas, directa o indirectamente, con la fórmula de la celda objetivo. Solver ajusta los valores en las celdas cambiantes que se especifiquen, denominadas celdas ajustables, para generar el resultado especificado en la fórmula de la celda objetivo. Pueden aplicarse restricciones para restringir los valores que puede utilizar Solver en el modelo y las restricciones pueden hacer referencia a otras celdas a las que afecte la fórmula de la celda objetivo.

Utilice Solver para determinar el valor máximo o mínimo de una celda cambiando otras celdas, por ejemplo, puede cambiar el importe del presupuesto previsto para publicidad y ver el efecto sobre el margen de beneficio.

Ejemplo de una evaluación de Solver

En el siguiente ejemplo, el nivel de publicidad de cada trimestre afecta al número de unidades vendidas, determinando indirectamente el importe de los ingresos por ventas, los gastos asociados y los beneficios. Solver puede modificar los presupuestos trimestrales de publicidad (celdas B5:C5), con una restricción total máxima de 20.000 $ (celda F5), hasta que el valor total de beneficios alcance el importe máximo posible. Los valores en las celdas ajustables se utilizan para calcular los beneficios de cada trimestre, por tanto están relacionados con la fórmula en la celda objetivo F7, =SUMA(Q1 Beneficios:Q2 Beneficios).

  Celdas ajustables

  Celda restringida

  Celda objetivo

Una vez ejecutado Solver, los nuevos valores son los siguientes:

Hojas de cálculo de ejemplo de Solver

Microsoft Excel incluye un libro, Solvsamp.xls en la carpeta Office\Samples, que demuestra los tipos de problemas que pueden resolverse.

Pueden utilizarse las hojas de cálculo de muestra en Solvsamp.xls como ayuda para resolver los problemas. Para utilizar cualquiera de las seis hojas de cálculo: Productos varios, Rutas de distribución, Organización de personal, Optimización de ingresos, Cartera de Valores y Diseño Técnico, abra el libro, cambie a la hoja de cálculo que desee utilizar y, a continuación, haga clic en la opción Solver del menú Herramientas. La celda objetivo, las celdas ajustables y las restricciones de la hoja de cálculo ya están especificadas.

Algoritmo y métodos utilizados por Solver

La herramienta Microsoft Excel Solver utiliza el código de optimización no lineal (GRG2) desarrollado por la Universidad Leon Lasdon de Austin (Texas) y la Universidad Allan Waren (Cleveland ).

Los problemas lineales y enteros utilizan el método más simple con límites en las variables y el método de ramificación y límite, implantado por John Watson y Dan Fylstra de Frontline Systems, Inc. Para obtener más información sobre el proceso de solución interno que utiliza Solver,

Definir y resolver un problema con SolverMostrar todo

Solver forma parte de una serie de comandos a veces denominados herramientas de análisis Y si. Con Solver, puede encontrar un valor óptimo (mínimo o máximo) para una fórmula en una celda, denominada la celda objetivo, sujeta a restricciones o limitaciones en los valores de otras celdas de fórmula en una hoja de cálculo. Solver trabaja con un grupo de celdas llamadas celdas de variables de decisión, o simplemente celdas de variables, que participan en el cómputo de fórmulas en las celdas objetivo y de restricción. Solver ajusta los valores en las celdas de variables de decisión para cumplir con los límites en las celdas de restricción y producir el resultado deseado para la celda objetivo.

Nota    En las versiones anteriores de Solver, la celda objetivo se denominaba "celda de destino", y las celdas de variables de decisión, "celdas cambiantes" o "celdas ajustables".

En este artículo

Información general Definir y solucionar un problema Recorrer las soluciones de prueba de Solver Cambiar el modo en que Solver busca soluciones Guardar o cargar un modelo de problema Métodos de resolución usados por Solver Más ayuda para usar Solver

Información general

Utilice Solver para determinar el valor máximo o mínimo de una celda cambiando otras celdas. Por ejemplo, puede cambiar el importe del presupuesto previsto para publicidad y ver el efecto sobre el margen de beneficio.

Ejemplo de una evaluación de Solver

En el siguiente ejemplo, el nivel de publicidad de cada trimestre afecta al número de unidades vendidas, determinando indirectamente el monto de los ingresos por ventas, los gastos asociados y los beneficios. Solver puede modificar los presupuestos trimestrales de publicidad (celdas variables de decisión B5:C5), con una restricción total máxima de $20.000 (celda F5), hasta que el valor total de beneficios (celda objetivo F7) alcance el monto máximo posible. Los valores en las celdas variables se usan para calcular los beneficios para cada trimestre, por tanto, están relacionados con la fórmula en la celda objetivo F7, =SUMA(Q1 Beneficios:Q2 Beneficios).

Celdas variables

Celda restringida

Celda objetivo

Una vez ejecutado Solver, los nuevos valores son los siguientes:

Volver al principio

Definir y solucionar un problema1. En el grupo Análisis de la ficha Datos, haga clic en Solver.

Si el comando Solver o el grupo Análisis no está disponible, deberá cargar el programa de complemento Solver.

Cómo cargar el programa de complemento Solver

1. Haga clic en la pestaña Archivo, elija Opciones y, a continuación, haga clic en la categoría Complementos.

2. En el cuadro Administrar, haga clic en Complementos de Excel y, a continuación, en Ir. 3. En el cuadro Complementos disponibles, active la casilla de verificación Complemento

Solver y, a continuación, haga clic en Aceptar.

2. En el cuadro Establecer objetivo, escriba una referencia de celda o un nombre para la celda objetivo. La celda objetivo debe contener una fórmula.

3. Siga uno de los procedimientos siguientes:

Si desea que el valor de la celda objetivo sea el valor máximo posible, haga clic en Máx. Si desea que el valor de la celda objetivo sea el valor mínimo posible, haga clic en Mín. Si desea que la celda objetivo tenga un valor determinado, haga clic en Valor de y luego

escriba el valor en el cuadro.

1. En el cuadro Cambiando las celdas de variables, escriba un nombre o una referencia para cada rango de celda de variable de decisión. Separe con comas las referencias no adyacentes. Las celdas de variables deben estar directa o indirectamente relacionadas con la celda objetivo. Se puede especificar un máximo de 200 celdas de variables.

2. En el cuadro Sujeto a las restricciones, realice lo siguiente para especificar todas las restricciones que desee aplicar.

1. En el cuadro de diálogo Parámetros de Solver, haga clic en Agregar.2. En el cuadro Referencia de la celda, escriba la referencia de celda o el nombre del rango

de celdas para los que desea restringir el valor.3. Haga clic en la relación (<=, =, >=, int, bin o dif ) que desea establecer entre la celda a la

cual se hace referencia y la restricción.

Si hace clic en int, aparece integer en el cuadro Restricción. Si hace clic en bin, aparece binary en el cuadro Restricción. Si hace clic en dif, aparece alldifferent en el cuadro de diálogo Restricción.

4. Si elige <=, =, o >= para la relación en el cuadro Restricción, escriba un número, una referencia de celda o nombre o una fórmula.

5. Siga uno de los procedimientos siguientes:

Para aceptar una restricción y agregar otra, haga clic en Agregar. Para aceptar la restricción y volver al cuadro de diálogo Parámetros de Solver,

haga clic en Aceptar.

Nota    Puede aplicar las relaciones int, bin y dif solamente en restricciones en celdas de variables de decisión.

Puede cambiar o eliminar cualquier restricción existente haciendo lo siguiente:

1. En el cuadro de diálogo Parámetros de Solver, haga clic en la restricción que desee cambiar o eliminar.

2. Haga clic en Cambiar y realice los cambios que desee, o haga clic en Eliminar.

1. Haga clic en Resolver y siga uno de los procedimientos siguientes:

Para mantener los valores de la solución en la hoja de cálculo, en el cuadro de diálogo Resultados de Solver, haga clic en Conservar solución de Solver.

Para restaurar los valores originales tal como estaban antes de hacer clic en Resolver, haga clic en Restaurar valores originales.

Notas    

Para interrumpir el proceso de resolución, puede presionar ESC. Microsoft Excel actualiza la hoja de cálculo con los últimos valores encontrados para las celdas de variable de decisión.

Para crear un informe basado en su solución después de que Solver encuentre una solución, seleccione un tipo de informe en el cuadro Informes y haga clic en Aceptar. El informe se crea en una nueva hoja de cálculo del libro. Si Solver no encuentra una solución, la opción de crear un informe no está disponible.

Para guardar los valores de la celda de variable de decisión como un escenario que pueda mostrar más tarde, haga clic en Guardar escenario en el cuadro de diálogo Resultados de Solver y luego escriba un nombre para el escenario en el cuadro Nombre del escenario.

Volver al principio

Desplazarse por las soluciones de prueba en Solver1. Después de definir un problema, haga clic en Opciones en el cuadro de diálogo

Parámetros de Solver.2. En el cuadro de diálogo Opciones, active la casilla de verificación Mostrar resultado de

iteraciones para ver los valores de cada solución de prueba y, a continuación, haga clic en Aceptar.

3. En el cuadro de diálogo Parámetros de Solver, haga clic en Resolver.4. En el cuadro de diálogo Mostrar solución de prueba, siga uno de los procedimientos

siguientes: Para detener el proceso de solución y ver el cuadro de diálogo Resultados de

Solver, haga clic en Detener.

Para continuar el proceso de solución y ver la siguiente solución de prueba, haga clic en Continuar.

Volver al principio

Cambiar la forma en que Solver encuentra soluciones1. En el cuadro de diálogo Parámetros de Solver, haga clic en Opciones.2. Elija o especifique valores para cualquiera de las opciones en las pestañas Todos los

métodos, GRG Nonlinear y Evolutionary en el cuadro de diálogo.

Volver al principio

Guardar o cargar un modelo de problema1. En el cuadro de diálogo Parámetros de Solver, haga clic en Cargar/Guardar.2. Especifique un rango de celdas para el área modelo y haga clic en Guardar o en Cargar.

Cuando guarde un modelo, especifique la referencia de la primera celda de un rango vertical o de las celdas vacías en que desee colocar el modelo de problema. Cuando cargue un modelo, especifique la referencia de todo el rango de celdas que contenga el modelo de problema.

Sugerencia    Puede guardar las últimas selecciones con una hoja de cálculo en el cuadro de diálogo Parámetros de Solver guardando el libro. Cada hoja de cálculo en un libro puede tener sus propias selecciones de Solver y todas ellas se guardan. También puede definir más de un problema para una hoja de cálculo haciendo clic en Cargar/Guardar para guardar los problemas individualmente.

Volver al principio

Métodos de resolución usados por SolverPuede elegir cualquiera de los tres algoritmos o métodos de resolución siguientes en el cuadro de diálogo Parámetros de Solver:

Generalized Reduced Gradient (GRG) Nonlinear Se usa para problemas que son no lineales suavizados.

LP Simplex Se usa para problemas lineales. Evolutionary Se usa para problemas no suavizados.

Activar y usar complementos en Excel 2010

Los complementos son una característica de Microsoft Excel que proporcionan características y comandos adicionales. Dos de los complementos más conocidos son Herramientas para análisis y Solver, que ofrecen una capacidad ampliada de análisis de datos para la planeación de "Y si". Para usar estos complementos, debe instalarlos y activarlos.

Veremos el procedimiento para activar herramientas para Análisis y Solver. Puede usar este mismo proceso general para instalar y activar muchos otros complementos. Es posible que algunos de estos complementos, como los creados por terceros, dispongan de su propio programa de instalación.

1. Haga clic en la pestaña Archivo.

2. Haga clic en Opciones, y a continuación seleccione la categoría Complementos

3. Casi al final de esa ventana, haga clic en el botón Ir, previamente seleccione la opción Complementos de Excel

4. En la nueva ventana, active la casilla Herramientas para análisis y Solver

5. Si Excel muestra un mensaje que indica que no puede ejecutar este complemento y le pide que lo instale, haga clic en Sí para instalar los complementos.

Nota    En este caso, como está instalando dos complementos, Excel le pide dos veces que instale un complemento; una vez para Herramientas para análisis y una vez para Solver.

Observe que se ha agregado un grupo Análisis en la pestaña Datos. Este grupo contiene botones de comando para Análisis de datos y para Solver.