Práctica 5: Resolución de problemas con Excel y Solver - Prof. Valdés, Ejercicios de Matemáticas

¡Descarga Práctica 5: Resolución de problemas con Excel y Solver - Prof. Valdés y más Ejercicios en PDF de Matemáticas solo en Docsity! Práctica 5 Resolución de problemas con Excel El conocido programa Excel de Microsoft incorpora un módulo de optimización llamado Solver para resolver problemas de Programación Lineal. En esta práctica vamos a describir cómo introducir los datos del problema, determinar las opciones de resolución e interpretar los resultados. Si ha sido instalado, dentro del menú Herramientas podemos encontrar la opción Solver. Si no está, basta con que seleccionemos en el mismo menú la opción Complementos, y en la ventana que aparece pongamos una marca en la casilla Solver para que lo instale. El programa Excel es una hoja de cálculo, lo que quiere decir que su propósito es precisamente calcular expresiones matemáticas. En las casillas o celdas de la ventana de Excel podemos introducir tanto números como expresiones. Así por ejemplo, si en la casilla A1 hemos introducido el número 5, y en la casilla A2 el número 7 y queremos calcular su suma, podemos introducir en la casilla A3 la expresión “=A1+A2” obteniendo el valor de dicha suma. Notad que el símbolo “=” indica que el programa ha de calcular la expresión que viene a continuación y no se trata de un mero texto a insertar. En ocasiones podemos ver que se intercala el símbolo “$” junto a la referencia de una celda, por ejemplo “$A$2”. Esto indica que la referencia de la celda es absoluta y no relativa; es decir, que si copiáramos la expresión en otra celda, al ser absoluta la fórmula quedará tal cual está, pero si es relativa (no lleva los símbolos $) modificará la fórmula que copiamos. Dado que no vamos a hacer copias de celdas, a efectos de esta práctica consideraremos equivalentes las expresiones “A2” y “$A$2”. Consideremos el siguiente problema: Max 2x+3y s.a.: 2x+4y ≤ 12,5 x ≥ 0 y ≥ 0 Para poder resolverlo con Excel hemos de comenzar por establecer unas celdas para el valor de las variables, la función objetivo y el de las restricciones. Vamos a considerar que la variable x la ubicamos en la celda B7 y la y en la B8. Habitualmente le damos un valor inicial a las variables por comodidad para saber la celda en la que las hemos situado. Consideremos por ejemplo que les damos a ambas el valor de 1 (no es necesario que sea un valor factible del problema lineal). Para que la pantalla resulte fácilmente comprensible, introducimos el texto “x=” en la celda A7 e “y=” en la A8. Notad que estas expresiones no van precedidas del símbolo “=” y son meros textos que nosotros vemos pero el motor de cálculo ignora. El valor de la función objetivo es sencillamente una celda calculada en la que pondremos su expresión. Así, en la celda B4 hemos introducido “=2*B7+3*B8”. Al igual que con las variables, podemos poner en la celda contigua A4 el texto “z=” que nos ayude a identificar el valor de B4. Finalmente, para introducir las restricciones necesitamos dos celdas por cada una de ellas; una para la expresión y otra para el término de la derecha (rhs). Con el fin de tener una presentación comprensible del problema, vamos a añadir una celda entre ambas con el símbolo “<=”, “>=” o “=” según corresponda. Realmente no es aquí donde le definimos al Solver el tipo de restricción, y sólo hacemos esto para hacer más inteligible la ventana de datos. Así, la restricción 2x+4y ≤ 12,5 la introducimos poniendo en la celda B12 “=2*B7+4*B8”, en la celda D12 “12,5” y para visualizar el tipo de restricción, introducimos en la celda C12 la expresión “<=”. Además, en las celdas A12, A13 y A14 hemos introducido un nombre para cada restricción y también hemos puesto unos letreros para la “Función objetivo”, “Variables” y “Restricciones”, coloreando las celdas correspondientes. La figura 1 muestra la ventana de datos completa con todos los elementos del problema. Figura 1. Ventana de datos Notad que Excel no asume la no negatividad de las variables y hemos de ponerle explícitamente la restricción “mayor o igual que 0” cuando corresponda. Hasta ahora únicamente hemos introducido una serie de datos y expresiones en la ventana de Excel. Veamos a continuación cómo definir en el solver el modelo lineal a partir de estos datos. Comenzamos por seleccionar la opción Solver dentro del menú Herramientas, obteniendo la ventana de la figura 2. Iteraciones Limita el tiempo que tarda el proceso de solución, limitando el número de cálculos provisionales. Aunque puede introducirse un valor tan grande como 32.367, el valor predeterminado 100 es adecuado para la mayor parte de los problemas pequeños. 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. Debe indicarse la precisión mediante una fracción entre 0 (cero) y 1. Cuantas más posiciones decimales tenga el número que se escriba, mayor será la precisión; por ejemplo, 0,0001 indica una precisión mayor que 0,01. Usar escala automática Seleccionarlo para que Excel modifique internamente los valores cuando haya grandes diferencias de magnitud entre las entradas y los resultados; por ejemplo, cuando se maximiza el porcentaje de beneficios basándose en inversiones de millones de dólares. EJERCICIO Eliminar las restricciones de no negatividad del modelo y seleccionar la opción avanzada Asumir no negativos. Volved a resolver el ejercicio comprobando que la solución es la misma. En general, al resolver el problema, en la ventana que aparece podemos pedirle tres tipos de informes: respuestas, sensibilidad y límites. Este último tiene poco interés pues nos dice para cada variable los valores que puede tomar para obtener una solución posible, manteniendo el valor de las restantes fijo e igual al de la solución obtenida. Respuesta. Muestra una lista con la celda objetivo y las celdas ajustables con sus valores originales y sus valores finales, las restricciones y la información acerca de las mismas. Las restricciones aparecen etiquetadas como Obligatorias, si son activas en la solución obtenida, u Opcionales si no son activas. Para estas últimas nos da la holgura entre el valor de la expresión en la solución actual y el rhs. Sensibilidad. Facilita información acerca de la sensibilidad de la solución a que se realicen pequeños cambios del valor de las variables o de las restricciones. En modelos no lineales, el informe facilita los valores para las gradientes y los multiplicadores de Lagrange. En los modelos lineales, el informe incluye los costes reducidos de las variables (Gradiente reducido) y el valor de las variables duales (Multiplicador de Lagrange). EJERCICIO Resolver el siguiente problema, obteniendo y comentando los informes de respuesta y sensibilidad: Min -2x + y - z s.a.: x + y+ z ≤ 6 -x +2y ≤ 4 x, y, z ≥ 0 INFORME RESPUESTA Celda objetivo (Mínimo) Celda Nombre Valor original Valor final $B$2 z 0 -12 Celdas cambiantes Celda Nombre Valor original Valor final $B$4 x1 0 6 $B$5 x2 0 0 $B$6 x3 0 0 Restricciones Celda Nombre Valor de la celda fórmula Estado Divergencia $B$8 r1 6 $B$8<=$C$8 Obligatorio 0 $B$9 r2 -6 $B$9<=$C$9 Opcional 10 INFORME DE SENSIBILIDAD Celdas cambiantes Valor Gradiente Celda Nombre Igual reducido $B$4 x1 6 0 $B$5 x2 0 3 $B$6 x3 0 1 Restricciones Valor Multiplicador Celda Nombre Igual de Lagrange $B$8 r1 6 -2 $B$9 r2 -6 0