HERRAMIENTA SOLVER
Los complementos son una característica de Microsoft Excel que proporciona 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.
En este tema se incluyen los procedimientos que muestran cómo instalar y activar Herramientas para análisis y Solver, y cómo comenzar a analizar los datos con cada una de ellas. 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.
INSTALAR SOLVER
Haga clic en la pestaña Archivo.
Haga clic en Opciones y, a continuación, elija la categoría Complementos.
Casi al final del cuadro de diálogo Opciones de Excel, asegúrese de que la opción Complementos de Excel esté seleccionada en el cuadro Administrar y luego haga clic en Ir.
En el cuadro de diálogo Complementos, seleccione las casillas de verificación Herramientas para análisis y Solver Add-in. A continuación, haga clic en Aceptar.
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 otra vez para Solver.
Observe que en la pestaña Datos, se ha agregado un grupo Análisis. Este grupo contiene botones de comando para Análisis de datos y para Solver.
Ya está listo para comenzar a usar estos complementos.
CARGAR UN MODELO DE PROBLEMA CON SOLVER
El 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 Solverdonde 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 textoEstablecer 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.
AGREGAR UNA RESTRICCIÓN E SOLVER
Hemos hablado en alguna ocasión de la herramienta Solver, y quizá hayamos mencionado la enorme potencia que Excel le ha otorgado. Recordemos que su forma de trabajar se basa en un método iterativo, de prueba y error, hasta encontrar una solución que cumpla tanto un problema principal como todas aquellas restricciones dadas.
Como ejemplo propondremos la optimización de sencillo problema matemático. ¿Cuál es el área máxima de un paralelogramo conociendo el perímetro de éste?.
Es decir, debemos maximizar el área sabiendo que tenemos un perímetro de, por ejemplo, 4 unidades. Por facilitar la interpretación supondremos que hablamos de un rectángulo o un cuadrado.
Tenemos la siguiente figura:
Como ejemplo propondremos la optimización de sencillo problema matemático. ¿Cuál es el área máxima de un paralelogramo conociendo el perímetro de éste?.
Es decir, debemos maximizar el área sabiendo que tenemos un perímetro de, por ejemplo, 4 unidades. Por facilitar la interpretación supondremos que hablamos de un rectángulo o un cuadrado.
Tenemos la siguiente figura:
Sabemos que el perímetro se define como la suma de los lados, es decir
P = 2·(a + b)
Por tanto nuestra función a maximizar será:
A = b· a
sujeto a
2·(a + b) = 4
debiendo maximizar A (el área de nuestra figura).
Recuerdo, vagamente, de mi tiempos universitarios algunas formas de solucionar y encontrar el resultado óptimo a esta pregunta (mediante el cálculo diferencial, o con la programación lineal - método simplex- etc). Nosotros, sin embargo, aprovecharemos la herramienta de Excel Solver.
Para ello, en primer lugar, nombramos dos celdas una como 'base' y otra como 'altura', son nuestras variables a determinar, y las que nos devolverán una área máxima para nuestro paralelogramo.
Asignamos también el nombre 'area' a la celda C9, formulada como 'base' por 'altura'.
Debemos recordar lo explicado en el post .
Quizá lo más importante de este planteamiento sea tener formulado el perímetro como
= 2 · (base + altura), lo que hemos hecho en la celda C11.
Tenemos por tanto:
P = 2·(a + b)
Por tanto nuestra función a maximizar será:
A = b· a
sujeto a
2·(a + b) = 4
debiendo maximizar A (el área de nuestra figura).
Recuerdo, vagamente, de mi tiempos universitarios algunas formas de solucionar y encontrar el resultado óptimo a esta pregunta (mediante el cálculo diferencial, o con la programación lineal - método simplex- etc). Nosotros, sin embargo, aprovecharemos la herramienta de Excel Solver.
Para ello, en primer lugar, nombramos dos celdas una como 'base' y otra como 'altura', son nuestras variables a determinar, y las que nos devolverán una área máxima para nuestro paralelogramo.
Asignamos también el nombre 'area' a la celda C9, formulada como 'base' por 'altura'.
Debemos recordar lo explicado en el post .
Quizá lo más importante de este planteamiento sea tener formulado el perímetro como
= 2 · (base + altura), lo que hemos hecho en la celda C11.
Tenemos por tanto:
CAMBIAR O ELIMINAR UNA RESTRICCIÓN EN SOLVER
Aplicamos Solver, desde Excel 2007 dirigiéndonos al menú Datos > Análisis > Solver, y en la ventana de la herramienta seleccionamos como celda objetivo el nombre 'area', y como celdas cambiantes las celdas 'base' y 'altura'; sin olvidar lo más importante en este caso, y es agregar la restricción del perímetro celda C11 sea igual a 4.
La introducción de un modelo de optimización, un programa lineal en nuestro ejemplo, se puede sintetizar en cuatro fases: 1. Organizar los datos del modelo en la hoja de trabajo. Si bien son múltiples las posibles formas de diseñar el formato y colocación de los datos de entrada, es recomendable seguir los mismos principios que en toda aplicación con hoja de cálculo: pensar en la hoja como un informe que explique el problema, identificar los datos introducidos, colocar comentarios, introducir todos los datos iniciales del problema y construir a partir de los mismos el modelo de optimización con el objeto de facilitar el análisis de sensibilidad, utilizar técnicas de diseño para presentar el modelo, etc. Por otra parte, interesa organizar el programa según el formato del gráfico I con el objeto de ilustrar la propia estructura del modelo. 2. Reservar una celda para cada variable de decisión. Siguiendo el esquema de un programa matemático, es recomendable que inicien la hoja de trabajo. Deberán estar vacías o con datos numéricos, nunca fórmulas, y a ser posible con notas o comentarios. 3. Crear una celda para la función objetivo próxima a las que recogen las variables. La fórmula que incorpora deberá crearse a partir de las celdas descritas en el punto anterior. 2 4. Para cada restricción, crear una celda que recoja la fórmula de su parte izquierda, y a la derecha de dicha celda colocar el término independiente. La estructura recomendable es la que se recoge en el gráfico I dado que permite reducir el trabajo en la fase de introducción del problema, facilita la detección de errores y simplifica su resolución con el «solver».
CUADRO DE DIÁLOGO OPCIONES DE SOLVER
1. Haga clic en la pestaña Archivo y, a continuación, haga clic en Opciones debajo de la pestaña Excel.
2. En el cuadro de diálogo Opciones de Excel, haga clic en Complementos.
3. En el cuadro desplegable Administrar, seleccione Complementos de Excel y, a continuación, haga clic en Ir.
4. En el cuadro de diálogo Complementos, seleccione Solver Add-in y, a continuación, haga clic en Aceptar.
Ejemplo de cómo usar "SOLVER". En estos tiempos donde se habla de la tecnología, información, sociedad del conocimiento, etc., aprovecho la oportunidad de describir lo poderosa que es la hoja de cálculo de excel, pero voy a referirme en particular a una de las herramientas la cual se denomina Solver, y se puede ubicar en el menú principal en la opción Herramientas, al pulsar este icono aparecerán varias opciones y ahí encontraran dicha instrucción, ella resuelve problemas lineales y enteros utilizando 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
RECOMENDACIONES
1.Es aconsejable que comience por formular el problema primero en papel. Si puede redactar primero en un papel un modelo claro y comprensible valiéndose de símbolos, el traspaso de este modelo a Excel se convierte en una tarea mucho más simple y con menos posibilidades de error.
2. Asegúrese de introducir correctamente las restricciones. Sobre todo, verifique que los signos de las restricciones son coherentes con la formulación o el modelo basado en símbolos que ha creado en papel.
3. Algunas veces, surgen errores con Solver cuando hay una función o una referencia de celda a una función introducida en el lado derecho de una restricción. En otras palabras, al añadir una restricción y especificar su lado derecho, debería hacerse mediante una referencia a una celda de Excel que contenga un valor numérico y no una función. Incluso si Solver genera una solución, tal vez aparezcan problemas en los informes de sensibilidad debido a esta práctica.
4. Para modelos lineales, que serán los que trataremos principalmente, no olvide seleccionar la casilla “Assume Linear Model” en la ventana de opciones y especificar restricciones no negativas en las celdas cambiantes.
CONCLUSIONES
Este procedimiento utilizando la opción SOLVER deExcel parece ser un poco largo en comparación conotros paquetes de programación lineal. Laconveniencia, sin embargo, consiste en que se harásólo una vez y para los siguientes casos de análisis sepodrá utilizar la misma hoja cambiando loscoeficientes. Entonces, como se puede notar, laflexibilidad de modelar con Solver es muy grande,pudiéndose introducir directamente en una hoja dondese haga el análisis de Planeación Agregada,Sensibilidad, Transporte, Inventario, Proyectos,Riesgos, Secuencias, Balanceo, etc., fundamentalesen todo estudio de factibilidad.El complemento Solver de Excel nos permite resolver modelos de Programación Linealde forma muy sencilla. Para ello necesitamos tener previamente instalado elcomplemento de Solver en Excel.
APRECIACIÓN
En estos tiempos donde se habla de la tecnología, información, sociedad del conocimiento, etc., aprovecho la oportunidad de describir lo poderosa que es la hoja de cálculo de excel, pero voy a referirme en particular a una de las herramientas la cual se denomina Solver, y se puede ubicar en el menú
GLOSARIO
área de trazado
Gráfico completo y todos sus elementos.
área de valores
Parte de un informe de tabla dinámica que contiene datos de resumen. Los valores de cada celda del área de valores representan un resumen de los datos de los registros o las filas de origen.
área dinámica
Área de la hoja de cálculo a la que se arrastran campos de tabla dinámica o gráfico dinámico a fin de cambiar el diseño del informe. En un informe nuevo, las líneas azules discontinuas indican el área dinámica de la hoja de cálculo.
https://support.office.com/es-es/article/Definir-y-resolver-un-problema-con-Solver-9ed03c9f-7caf-4d99-bb6d-078f96d1652c
https://exceltotal.com/utilizando-excel-solver/
VÍDEO
