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.
Comentarios
Publicar un comentario