Manual Basico de Excel-Solver

Manual Bsico de MsExcel-Solver

Jos F Giraldo J

MANUAL BSICO DE MS-EXCEL-SOVERF

TEXTO Y EJEMPLO TOMADO Y ADAPTADO DE

http://www.infoab.uclm.es/asignaturas/42560/practicas/Practica2.pdf http://www2.ubu.es/econapli/profesores/jfalegre/archivos/textos/uso %20de%20solver.pdf

Universidad Tecnolgica de Pereira. Facultad de Ingeniera Industrial. Maestra en Investigacin Operativa y Estadstica. 2010


Jos F Giraldo J

La Herramienta Solver de MsExcel Solver es una herramienta para resolver problemas de optimizacin mediante el uso de mtodos numricos. Solver se puede utilizar para optimizar funciones de una o ms variables, con o sin restricciones. Microsoft Excel Solver utiliza diversos mtodos de solucin, dependiendo de las condiciones del problema. Para los problemas lineales utiliza el mtodo Simplex. Para problemas lineales enteros utiliza Branch and Bound y para problemas no lineales utiliza el algoritmo de optimizacin no lineal del Gradiente Reducido Generalizado 2 (GRG2). Con Solver, se puede buscar el valor ptimo (Mximo o Mnimo) para una celda, denominada celda objetivo, en donde se escribe la frmula de la funcin objetivo f(x1, x2,..., xn). Solver cambia los valores de un grupo de celdas, denominadas celdas cambiantes, que estn relacionadas, directa o indirectamente, con la frmula de la celda objetivo. En estas celdas se encuentran los valores de las variables de decisin x1, x2,..., xn. Los modelos ms realistas tienen factores de restriccin que es necesario aplicar a ciertos valores. Estas restricciones se pueden aplicar a las celdas de las variables de decisin (celdas cambiantes) o a cualquier otra celda que tenga una funcin (frmula) de estas celdas. Se puede agregar restricciones a Solver, escribiendo una frmula gj(x1, x2,...,xn) en una celda, y especificando que la celda deber ser mayor o igual, igual, o menor o igual que otra celda que contiene la constante bj. Tambin, si fuese el caso, se puede especificar que los valores solucin sean enteros, para evitar resultados inconvenientes o incompatibles en algunos problemas. Inclusive pueden hallarse soluciones binarias. Instalacin de Solver En el men Herramientas, hay que verificar que aparezca el comando Solver (Fig. 1). Si no aparece, se deber activar el complemento o macro automtica Solver (Fig. 2): en el cuadro de dilogo Complementos, hay que seleccionar la casilla de verificacin Solver. Si Solver no aparece en la lista del cuadro de dilogo Complementos (Fig. 3), se hace clic en Examinar y se localiza la unidad, la carpeta y el nombre de archivo Solver.xla que, normalmente, est ubicado en la carpeta Macros/Solver, o se ejecuta el programa de instalacin de MsOffice si no se puede localizar el archivo.



Jos F Giraldo J

Figura 1.

Figura 2.

Active Solver y Acepte



Jos F Giraldo J

Figura 3.

Solver no aparece en la lista. Examine

La ubicacin del archivo para MsOffice 2003 es: C:\Archivos de programa\Microsoft Office\OFFICE11\Macros\SOLVER. Seleccionelo y Acepte



Jos F Giraldo J

Uso de Solver para resolver problemas de optimizacin Para resolver cualquier problema de optimizacin con Solver, el punto clave esta en construir el modelo apropiadamente usando formulas de Excel. Es indispensable que las celdas cambiante (variables de decisin) afecten mediante formulas tanto a las restricciones como a la funcin objetivo, de otra manera no se hallar una solucin. Adicionalmente hay que considerar que: Si Solver no encuentra los valores de las variables de decisin que hacen ptima a la funcin objetivo hay que tener en cuenta que los mtodos numricos para problemas no lineales encuentran el ptimo slo si: o Existe, y o se parte de una solucin inicial "apropiada".

Es conveniente siempre probar con diferentes soluciones iniciales, para confirmar que la solucin de Solver es realmente la mejor, o para evitar que se "atasque" en puntos de inflexin o en ptimos locales. Las inestabilidades (por malas soluciones iniciales) del algoritmo de optimizacin no lineal no se presentan en casos de Programacin Lineal, dado que Solver utiliza el Mtodo Simplex

Veremos ahora la utilizacin de Solver para resolver un caso de Programacin Lineal, aplicndolo a un ejemplo muy elemental, tomado del libro de Eppen, Gould y Schmidt, Investigacin de Operaciones en la Ciencia Administrativa, 3ra edicin, Editorial Prentice Hall. El Modelo de la Protrac La Protrac Inc., fabrica dos tipos de productos qumicos, E y F, cuya utilidad neta es de $5000 y $4000 por tonelada respectivamente. Ambos pasan por operaciones de 2 departamentos de produccin, que tienen una disponibilidad limitada. El departamento A dispone de 150 horas mensuales; cada tonelada de E utiliza 10 horas de este departamento, y cada tonelada de F, 15 horas. El departamento B tiene una disponibilidad de 160 horas mensuales. Cada tonelada de E precisa de 20 horas, y cada tonelada de F precisa de 10 horas para su produccin.Universidad Tecnolgica de Pereira. Facultad de Ingeniera Industrial. Maestra en Investigacin Operativa y Estadstica. 2010


Jos F Giraldo J

Para la produccin global de E y F, se debern utilizar al menos 135 horas de verificacin en el prximo mes; el producto E precisa de 30 horas y F de 10 horas de verificacin por tonelada. La alta gerencia ha decretado que es necesario producir al menos una tonelada de F por cada 3 de E. Un cliente ha solicitado 5 toneladas, cualquiera sea su tipo, de E o F. Por otro lado, es evidente que no pueden producirse cantidades negativas de E ni de F. Se trata de decidir, para el mes prximo, las cantidades a producir de cada uno de los productos para maximizar la utilidad global. 1. El Modelo

Antes de introducir este modelo en la hoja de clculo, conviene preparar una tabla que sirva como gua visual para construir el modelo (Fig. 4): Figura 4. Tabla Gua



Jos F Giraldo J

2. Estructura del Modelo e Introduccin de Datos En una hoja de clculo de Excel estructure el modelo como se muestra a continuacin. (Fig. 5) Figura 5. Formulacin del Modelo

Para poder entender este modelo, debemos comprender primero el modo en que opera la funcin de Excel sumaproducto. Supongamos que tenemos dos vectores fila de forma: A=[a1 a2 a3 a4an] B=[b1 b2 b3 b4bn]

Si quisiramos multiplicar punto a punto los elementos de los vectores y luego sumar estos productos entonces tendramos: SumaProducto= a1*b1 + a2*b2 + a3*b3 + a4*b4+an*bn O expresado de otra manera:n

Suma Pr oducto = ai bii =1

Esta es la operacin que de manera abreviada nos permite realizar funcin sumaproducto de Excel, para la cual los vectores A y corresponden a los 2 rangos que se pasan como argumento a funcin. Esto funciona de igual manera si los vectores fueran columnas.

la B la 2

Volviendo al ejemplo, en la celda D16 escribimos =SUMAPRODUCTO(B19:C19;B20:C20). Esta celda representa la funcin objetivo que involucra las variables de decisin XE y XF (B19: C19) y sus respectivos mrgenes de utilidad (vector de costos, B20:C20).



Jos F Giraldo J

En las celdas D23 a D27 escribimos respectivamente las formulas:

Estas celdas involucran las variables de decisin XE y XF (B19:C19) y los coeficientes tecnolgicos (B23:C27) que junto con el vector de recursos b (F23:F27) conforman las restricciones del modelo. Por el momento no se tienen en cuenta las condiciones de no negatividad las cuales se introducirn al modelo cuando se alimenten los parmetros de Solver. Las celdas (E23:E27) que muestran los signos relacionales (>=,

Documents

Manual Basico de Excel-Solver