NOTA TÉCNICA SOBRE EL USO DEL SOLVER EXCEL Versión 2.0. Gutiérrez Villaverde, Herberth E. 1


Save this PDF as:
 WORD  PNG  TXT  JPG

Tamaño: px
Comenzar la demostración a partir de la página:

Download "NOTA TÉCNICA SOBRE EL USO DEL SOLVER EXCEL Versión 2.0. Gutiérrez Villaverde, Herberth E. 1"

Transcripción

1 UNIVERSIDAD DE LIMA FACULTAD DE CIENCIAS EMPRESARIALES Y ECONÓMICAS CARRERA DE NEGOCIOS INTERNACIONALES NOTA TÉCNICA SOBRE EL USO DEL SOLVER EXCEL Versión 2.0 Gutiérrez Villaverde, Herberth E. 1 1 Docente de la Universidad de Lima:

2 Contenido Introducción. 3 Instalación del Solver en Excel 2010/ Solución con Solver de un problema de programación lineal de mezclas. 5 Descripción de las opciones de cálculo del Solver. 11 Fuentes de información 16

3 Introducción Existen varios programas de optimización lineal, no lineal y los que usan otros algoritmos como los genéticos. Sin embargo, en las empresas es muy importante el uso del programa Solver del Excel puesto que prácticamente cada microcomputadora de escritorio o portátil tiene Excel y cualquier empleado la sabe usar. Instalación del Solver en Excel 2010/2016. Normalmente el Solver no está activo cuando se carga el Excel por que ocupa memoria. Por lo tanto, lo primero que tenemos que hacer es revisar si tenemos activo el Solver, seleccionando la pestaña Datos. Si el Solver está cargado veremos en el extremo derecho de la cinta: Fig. 1. Pestaña Datos mostrando el Solver activado. Indica que el Solver está activo Si el Solver no está activo, hay que cargarlo, para lo que es necesario hacer lo siguiente: en la pestaña Archivo seleccionar Opciones y en esta ventana seleccionar Complementos: Fig 2. Localizando la opción Complementos para activar el Solver.

4 Después de seleccionar Complementos, en la parte inferior de la ventana aparece la opción de Complementos de Excel y para acceder a ellos presionar el botón Ir Fig. 3. Activación de los complementos disponibles en Excel. Finalmente, aparece la ventana con los complementos de Excel disponibles, entre ellos el Solver. Para cargarlo hacemos click en el recuadro Solver y presionamos el botón Aceptar. Fig4. Activación del complemento Solver.

5 Después de esto veremos en la pestaña Datos el Solver ya cargado. Solución con Solver de un problema de programación lineal de mezclas. Para aprender el uso del Solver, usaremos el siguiente problema de mezclas. Una empresa al sur de Lima que se dedica a la comercialización de pollos requiere preparar una mezcla diaria de 2,000 Kg. de alimentos balanceados, con requerimientos nutricionales en proteínas, vitaminas y calcio. Para la preparación de la mezcla la empresa usa como materia prima: harina de pescado, soya y productos carbonatados. En la siguiente tabla se proporcionan los requerimientos de la mezcla alimenticia para los pollos así como los costos de la materia prima y los contenidos de los nutrientes respectivos: Materia Prima Costo S/. Kg. Proteínas Unidades/Kg. Vitaminas Unidades/Kg. Calcio Unidades/Kg. Harina de Pescado Soya Productos carbonatados Requerimiento Mínimo Requerimiento Máximo Formular un problema lineal para encontrar la mezcla más económica. En esta oportunidad no discutiremos el proceso de formulación del modelo, puesto que nuestro interés es aprender a usar el Solver. Así que proporcionamos la formulación del modelo: Sea: X1 = Kg de harina de pescado a usar X2 = Kg de soya a usar X3 = kg de productos carbonatados a usar MIN Z =10X1 + 5X2 + 15X3 Minimizar el costo de la mezcla s.a.: X1 + X2 + X3 = 2000 Peso requerido en la mezcla 0.6 X X X3 >= 900 Requerimiento mínimo de proteínas 3 X X X3 >= 4000 Requerimiento mínimo de vitaminas 1.5 X X2 + 4 X3 >= 2000 Requerimiento mínimo de calcio 1.5 X X2 + 4 X3 <= 6000 Requerimiento máximo de calcio X1, X2, X3 >= 0 La solución de un problema en Excel tiene los siguientes pasos: a. Desarrollo de una representación del modelo en la hoja electrónica. b. Indicación de la celda que contiene la Función Objetivo. c. Especificación de las variables de decisión, que se conocen como celdas cambiantes d. Especificación de las celdas que contienen las restricciones e. Solución del modelo e interpretación de los resultados

6 A continuación, desarrollaremos cada uno de estos pasos. En la hoja de la Fig. 5 se ha plasmado una representación del modelo descrito arriba con los coeficientes de la función objetivo en las celdas C25:E25, los coeficientes de las restricciones en las celdas C26:E30 y los valores de los lados derechos en las celdas H26:H30. En las celdas C31:E31 aparecerán los valores de X1, X2 y X3 una vez resuelto el problema. Estas celdas pintadas de amarillo (solo por conveniencia), se conocen como celdas cambiantes, porque en el proceso de encontrar la solución irán cambiando hasta tener un valor final. Fig. 5. Representación del modelo en hoja electrónica. Observemos que hasta el momento solo hemos colocado los coeficientes de la función objetivo y de las restricciones, pero no hemos expresado como son las expresiones matemáticas. Tomemos como ejemplo la restricción del Peso requerido en la mezcla: X1 + X2 + X3 = 2000 Lado izquierdo (LI) de la restricción Lado derecho (LD) de la restricción En la hoja electrónica en las celdas C26:E26 solo se han colocado los coeficientes de X1, X2 y X3, pero no hemos establecido que la suma de 1 x X1 más 1 x X2 más 1 x X3, es decir, X1 +X2 + X3, es el lado izquierdo de la restricción. Para construir el lado izquierdo de la restricción del peso requerido de la mezcla tenemos que incluir en la celda F26 la siguiente fórmula: =C31*C26+D31*D26+E31*E26 De igual manera debemos construir los lados izquierdos del resto de restricciones en las celdas F27:F30:

7 =C31*C27+D31*D27+E31*E27 (Lado izquierdo de restricción de proteínas) =C31*C28+D31*D28+E31*E28 (Lado izquierdo de restricción de vitaminas) =C31*C29+D31*D29+E31*E29 (Lado izquierdo de restricción del mínimo de Calcio) =C31*C30+D31*D30+E31*E30 (Lado izquierdo de restricción de máximo de Calcio) De manera análoga debemos construir la expresión de la función objetivo en la celda F25: =C25*D31+D25*D31+E25*E31 En este momento ya estamos listos para ingresar el modelo al Solver. Para ello, hagamos un click sobre la celda que tiene la función objetivo: F25. Luego seleccionamos la opción Solver en la pestaña Datos e inmediatamente aparecerá la ventana de Parámetros del Solver: Fig. 6. Activación del Solver y carga de la ventana de Parámetros del Solver. Como se puede observar, el recuadro Establecer objetivo: ya tiene la celda que corresponde a la función objetivo, en este caso la celda $F$25. En el campo Para: marcar la opción Min porque vamos a minimizar el costo de la mezcla. En el recuadro Cambiando las celdas de variables: hay que indicar las celdas que contiene a las variables de decisión o celdas cambiantes, en este problema las celdas C31:E31 (las celdas en amarillo). Hasta el momento la ventana de parámetros del Solver debe verse como en la Fig. 7.

8 Fig. 7. Ingreso de la celda objetivo, tipo de optimización y las variables de decisión. Ahora tenemos que indicar al Solver como están conformadas las restricciones, para ello usamos el recuadro Sujeto a las restricciones:. Iniciamos el ingreso de las restricciones presionando el botón Agregar, después de lo cual aparecerá la ventana Agregar restricción con tres campos para ingresar la dirección del lado izquierdo, indicar el tipo de restricción =, > o < y poner la dirección del lado derecho de cada restricción, como se muestra el caso de la restricción del peso de la mezcla en la Fig. 8. Fig. 8. Ingreso de las restricciones del problema.

9 De manera análoga podemos ingresar las demás restricciones, después de lo cual tendremos: Fig. 9. Modelo ingresado en la ventana de paramentros de Solver. Adicionalmente, notar que se tiene que marcar el cajón de Convertir variables sin restricciones en no negativas, que es el equivalente a decir que los Xj >= 0. Así mismo, puesto que estamos en programación lineal, el Método de resolución: debe ser el Simplex LP. En este punto ya estamos en posición de presionar el botón Resolver para que el Solver inicie los cálculos internos y nos comunique si el problema tiene solución si se ha presentado algún inconveniente. Como vemos en la Fig. 10, después de presionar Resolver el Solver nos muestra la ventana Resultados del Solver, con el mensaje: Solver encontró una solución. Se cumplen todas las restricciones y condiciones óptimas y en la hoja del modelo podemos apreciar que los valores de las variables de decision X1, X2 y X3, tienen los valores de 1200, 800 y 0 respectivamente en las celdas en amarillo.

10 Fig. 10. Ventana de resultados del Solver. Descripción de las opciones de cálculo del Solver Método Simplex LP LP significa programación Lineal. Este método se usa para modelos con ecuaciones de primer orden. Las ecuaciones de primer orden son aquellas en las que las variables de decisión están elevadas a la primera potencia y su gráfico es una línea recta. El Método Simplex LP producirá siempre soluciones óptimas globales para los problemas de optimización que puede resolver. Método GRG Nonlinear Se debe seleccionar este método cuando las ecuaciones formadas con las variables de decisión son no lineales pero continuas. Un problema de optimización no lineal (NLP, smooth nonlinear programming) es uno en el que la función objetivo, o al menos una de las restricciones, es una función no lineal diferenciable de las variables de decisión. Por ejemplo: 4 X12 +3 X23 + log X3 Es una función no lineal diferenciable, donde X1, X2 y X3 son variables de decisión. Las funciones no lineales, a diferencia de las lineales, involucran variables que están elevadas a una potencia, pueden estar multiplicadas o divididas por otras variables. Adicionalmente pueden usar funciones trascendentales como la logarítmica, la exponencial, seno y coseno. Los problemas no lineales y sus métodos de solución requieren que las funciones no lineales sean continuas y que generalmente sean diferenciables con respecto a cada variable de decisión, es decir, que las gradientes de la función sean continuas. Una función es continua si no tiene quiebres cuando es graficada. Por ejemplo, la función del Excel =IF(C1>40, D1, 5*D1) es discontinua, asumiendo que C1 es una variable de decisión, porque su valor salta de D1 a 5*D1. De otro lado, la función Excel ABS(C1) es continua pero no diferenciable, su gráfico es una V continua, pero su derivada es discontinua, ya que salta de -1 a +1 en C1 = 0.

11 Método Evolutivo (Evolutionary) Este método se debe usar si cualquiera de las funciones del modelo son discontinuas o no diferenciables. Se llama método evolutivo por que utiliza algoritmos evolutivos. Los problemas no diferenciables o discontinuos son los más complejos de resolver y le pueden tomar mucho tiempo al Solver Excel. Adicionalmente, el método evolutivo solo puede encontrar buenas soluciones y no soluciones locales o globales. Las funciones de Excel que son no diferenciables son MIN, MAX y ABS. Las funciones discontinuas del Excel son: INDICE, CONSULTAH, CONSULTAV, BUSCAR, ENTERO, REDONDEAR, CONTAR, BDMAX, BDMIN, SI, ELEGIR, NO, Y, O, MAYOR O IGUAL. Si cualquiera de estas funciones forma parte del modelo en Excel se debe usar el Método Evolutivo. Establecimiento de Opciones del Solver A continuación, se muestra las opciones generales para todos los métodos antes de ejecutar el Solver para la mayoría de problemas. Esta ventana aparece cuando seleccionamos el botón de Opciones en el diálogo Solver. Establecimiento de Opciones Todos los Métodos Precisión de restricciones. Este valor establece la máxima diferencia entre la celda restringida y el valor real de la restricción. Se considera que la restricción es satisfecha si la diferencia entre ambos valores es menor o igual al valor considerado ( en el caso mostrado). Usar escala automática. Esta opción hace que el Solver re-escale las variables, restricciones y la función objetivo. Esto es necesario si en el problema existen parámetros de entrada que tienen diferencias de magnitud importantes entre sus valores (por ejemplo, existen valores pequeños como y a la vez grandes como millones). Esto hará que en los cálculos se pierda precisión y se produzcan errores inesperados o condiciones para que el algoritmo se detenga. Consideramos una buena práctica tener activada siempre esta opción.

12 Mostrar resultados de iteraciones. Esta opción hará que el Solver se detenga después de cada iteración mostrando los resultados alcanzados en esa iteración. Generalmente no se solicita esta opción a no ser que se tenga alguna razón particular para hacerlo. A continuación, se muestra el diálogo después de cada parada en un modelo de mezclas. Omitir restricciones de enteros. Esta opción se usa para relajar el problema lineal en enteros, es decir omitir todas las declaraciones en enteros para las variables de decisión. En general es una buena práctica correr un problema en enteros primero como un problema relajado, ya que, si la solución resulta en forma natural en enteros, tendremos el beneficio de los reportes de sensibilidad. Optimalidad de entero (%). Define la máxima diferencia en % entre el valor de la función objetivo de la mejor solución del problema considerando las restricciones en enteros y el problema relajado. El valor por defecto es 1%. Seleccionando 0% se garantiza obtener la mejor solución, pero puede tomar mucho tiempo. Límites para la solución (mostrado como Resolviendo Límites ) Tiempo máximo (segundos). El tiempo máximo de ejecución de un problema permitido al Solver. Sin embargo, presionando la tecla ESC se puede detener la ejecución en cualquier momento. Si hacemos esto el Solver preguntará si queremos detener la ejecución definitivamente o continuar. Iteraciones. El máximo número de iteraciones (ejecuciones de prueba) que se le permite ejecutar la Solver. Restricciones de enteros y Evolutionary. Esta opción solo aplica si se utiliza el Método Evolutivo o si se usa cualquier tipo de restricciones en enteros (entero, binario o todos diferentes ). Máximo de sub problemas. El número máximo de sub problemas permitido al Método evolutivo para evaluar. Máximo de soluciones viables. El número máximo de soluciones factibles de generar permitidas al Solver.

13 Establecimiento de opciones para el Método Evolutivo A continuación, se explicará las opciones para resolver problemas con el Método Evolutivo. Convergencia. La convergencia permite especificar qué tan cerca deseamos que la solución final del Solver esté de la solución óptima. El valor de la convergencia establece la máxima diferencia en % que el 99% de las últimas soluciones encontradas tienen entre ellas, antes de que el Solver presente el siguiente mensaje: Solver no puede mejorar la solución actual. Se cumplen todas las restricciones y presente su solución final. A medida que el valor de la convergencia es más pequeño se requerirán más iteraciones para alcanzar este valor, pero la solución proporcionada por el Solver estará más cercana a la solución óptima. Tasa de Mutación. Es la tasa o frecuencia de cambio, a la cual mutarán (es decir cambiarán) las soluciones (cada solución representa un individuo, y la generación es el conjunto de soluciones consideradas en una iteración) que mantiene el Solver en el espacio de soluciones, a fin de incrementar la probabilidad de no quedarse atrapado en un óptimo local y a la vez explorara otras regiones que pudieran aportar mejores soluciones. La frecuencia de mutación es un número entre 0 y 1. Tamaño de población. Este valor establece cuántos puntos muestreados del espacio de soluciones serán mantenidos como soluciones candidatas en todo momento para cada variable de decisión. Este valor debe ser un número entre 10 y 200. Valor de inicialización aleatorio. El algoritmo evolutivo utiliza un generador de números aleatorios para diferentes elecciones aleatorias, que inicia una serie de números aleatorios a partir de un número

14 semilla. Si se ingresa un número o semilla, el Solver realizará las mismas elecciones cada vez que se ejecute. Si se deja en blanco o cero, el Solver usará una semilla diferente para generar números aleatorios cada vez que se ejecute, lo cual puede llevar a una solución diferente que puede ser mejor o peor que la anterior. Tiempo máximo sin mejora. Este es el tiempo en segundos permitido para que el Solver continúe trabajando sin lograr una mejora significativa en la solución final. Después de transcurrido este límite de tiempo, el Solver emitirá el mensaje Solver no puede mejorar la solución actual. Se cumplen todas las restricciones. Requerir límites en variables. Esto indicará que el Solver trabajará solo si las variables de decisión tienen límites superiores e inferiores establecidos. En general, el algoritmo evolutivo será más eficiente si las variables de decisión tienen límites más estrechos. Fuentes de información 1. Fylstra, D., Lasdon, L., Watson, J., & Waren, A. (1998). Design and use of the Microsoft Excel Solver. Interfaces, 28(5), DOI: /inte Harmon, M. (2012). Step-By-Step Optimization With Excel Solver-The Excel Statistical Master. Excel Master Series.

Sitemap