HERRAMIENTA SOLVER
Activar Solver en Excel:
Solver 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ónIr 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.
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.
Cargar un modelo de problema con SOLVER:
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.
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).
1. Celdas variables
2. Celda restringida
3. Celda objetivo
Una vez ejecutado Solver, los nuevos valores son los siguientes:
Agregar una restricción en 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 dialogo opciones en solver:
Pueden controlarse las características avanzadas del proceso de solución, cargarse o guardarse definiciones de problemas y definirse parámetros para los problemas lineales y no lineales. Cada opción tiene una configuración predeterminada adecuada a la mayoría de los problemas.
Precisión: Controla la precisión de las soluciones utilizando el número que se introduce para averiguar si el valor de una restricción cumple un objetivo o satisface un límite inferior o superior.
Tangente, utiliza la extrapolación lineal de un vector tangente.
Cuadrática, utiliza la extrapolación cuadrática, que puede mejorar los resultados de problemas no lineales en gran medida. Derivadas: Especifica la diferencia que se utiliza para estimar las derivadas parciales del objetivo y las funciones de la restricción.
Progresivas, se utilizan en la mayor parte de los problemas, en que los valores de restricción cambien relativamente poco.
Centrales, se utiliza en los problemas en que las restricciones cambian rápidamente, especialmente cerca de los límites. Aunque esta opción necesita más cálculos, puede ser útil cuando Solver devuelve un mensaje diciendo que no puede mejorarse la solución. Hallar por: Especifica el algoritmo que se utiliza en cada iteración para determinar la dirección en que se hace la búsqueda.
Newton, es un método casi Newton, normalmente necesita más memoria pero menos iteraciones que el método de gradiente conjugada.
Conjugada, necesita menos memoria que el método Newton, pero normalmente necesita más iteraciones para alcanzar un determinado nivel de precisión.
2.RESUMEN:
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
4. 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.
5. CONCLUSIONES:
Este procedimiento utilizando la opción SOLVER de Excel parece ser un poco largo en comparación con otros paquetes de programación lineal. La conveniencia, sin embargo, consiste en que se hará sólo una vez y para los siguientes casos de análisis se podrá utilizar la misma hoja cambiando los coeficientes. Entonces, como se puede notar, la flexibilidad de modelar con Solver es muy grande, pudiéndose introducir directamente en una hoja donde se haga el análisis de Planeación Agregada, Sensibilidad, Transporte, Inventario, Proyectos, Riesgos, Secuencias, Balanceo, etc., fundamentales en todo estudio de factibilidad.
6. APRECIACION DE EQUIPO:
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ú
7. GLOSARIO DE TERMINOS:
Á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.
