Manual Solver 2i513r

Guía para la Utilización del Solver de Excel :

Utilización de la Herramienta “Solver”

1

Para definir y resolver un problema con Solver, se deben seguir los siguientes pasos: 1. En el menú herramientas, seleccionar Solver, lo cual abrirá la ventana que se muestra en la Ilustración 1 2. Si Solver no está disponible, es necesario instalarlo. Para ello en el menú herramientas se elige complementos. Luego, en el cuadro complementos disponibles se activa la casilla de verificación situada junto a Solver y se selecciona aceptar. Si no aparece Solver, no obstante estar instalado, intente cerrar y volver abrir Excel, o desinstalar y volver a instalar Solver. Si esto no da resultado, reinicie el computador.

Ilustración 1: Parámetros de Solver 3. En el cuadro Celda objetivo se debe introducir la referencia de celda de la función

Comentario [s1]:

objetivo. Ésta debe contener una fórmula que sea función directa o indirecta de las celdas cambiantes. 4. Luego se elige cómo será el Valor de la celda objetivo: 

Para que sea el valor máximo posible, seleccione Máximo.



Para que sea el valor mínimo posible, seleccione Mínimo.

Comentario [s2]:



Para que tenga un valor determinado, seleccione Valores de: y, a continuación, introduzca el valor en el cuadro.

5. En el cuadro Cambiando las celdas se deben introducir el rango o los rangos de las variables, separando con punto y comas (o coma, dependiendo de la configuración) los rangos no adyacentes. Otra forma de ingresar varios rangos es mantener la tecla Ctrl apretada mientras se seleccionan los rangos. El número máximo de variables es 200 y no se pueden introducir más de 22 rangos. Por ello, es una buena práctica agrupar las variables en pocas tablas. 6. En el cuadro Sujetas a las siguientes restricciones se ingresan las restricciones del problema. Para introducir una restricción seleccione Agregar… Esto abrirá la ventana que se muestra en la ¡Error! No se encuentra el origen de la referencia.. Los pasos a seguir a continuación son: 

En el cuadro Referencia de celda se escribe el rango o nombre de las celdas cuyo valor se desea restringir. Estas celdas pueden ser variables o contener fórmulas.



Se elige qué relación debe existir entre Referencia de la celda y Restricción; o sea <=, =, >=, int o bin.

Ilustración 2: Agregar Restricciones Si desea un número entero seleccione int (ent), mientras que si se quiere un 0 ó un 1, seleccione bin. Estas dos relaciones se pueden utilizar sólo para restringir las celdas cambiantes; no se pueden restringir fórmulas. Las restricciones =, y especialmente int y bin, son difíciles de cumplir. Si se utilizan, Solver se demorará más en solucionar el problema y, en muchos casos, puede que no

encuentre solución. Por ello, estas restricciones se deben utilizar sólo si son estrictamente necesarias. 

En el cuadro Restricción se escribe, un número o el rango, o una fórmula.

Si la restricción es un rango, éste debe tener el mismo tamaño que el rango de las celdas a restringir. Si no es así, Solver entregará el siguiente mensaje: “La cantidad de celdas en Referencia de celda y Restricción no coincide”. Si la restricción es int (ent) en el cuadro Restricción, aparecerá “integer” (entero). Si la restricción es bin, en el cuadro Restricción, aparecerá “binario”. 

Para aceptar la restricción y agregar otra, seleccione Agregar, y repita los pasos recién descritos.

Para aceptar la restricción y regresar a la ventana Parámetros de Solver, seleccione aceptar. Para cambiar una restricción, en la ventana Parámetros de Solver debe seleccionar Cambiar… Se abrirá nuevamente la ventana de la ¡Error! No se encuentra el origen de la referencia., donde se pueden realizar los cambios que se deseen. Para eliminar una restricción, seleccione Eliminar… Recuerde que las celdas cambiantes deben estar acotadas. Si no, el problema probablemente no convergerá. Solver ofrece en el botón Opciones… la alternativa Asumir no negativos para celdas cambiantes. Para exigir no-negatividad para fórmulas, debe hacerlo como una restricción mediante el cuadro de la ¡Error! No se encuentra el origen de la referencia.. 7. Una vez definidas la celda objetivo, las variables y las restricciones, seleccione Resolver. El programa iterará hasta encontrar una solución, y abrirá la ventana Resultados de Solver que se muestra en la Ilustración 3.

Ilustración 3: Resultados Para mantener los valores de la solución en la hoja de cálculo, seleccione Utilizar solución de Solver. Para restaurar los datos originales, seleccione Restaurar valores originales. 8. Para controlar las características del proceso de solución, seleccione Opciones… en el cuadro de diálogo Parámetros de Solver, lo cual abrirá la ventana de la Ilustración 4. Cada opción tiene una configuración predeterminada, idónea para a la mayoría de los problemas. Sin embargo, en algunas ocasiones se deben reconfigurar para encontrar una solución al problema.

Ilustración 4: Opciones de Solver



Tiempo Máximo: limita el tiempo que tarda el proceso de solución. Puede introducirse un valor tan grande como 32.367 (segundos).



Iteraciones: limita el número de cálculos provisionales.



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.



Tolerancia: el porcentaje mediante el cual la celda objetivo de una solución satisface las restricciones externas puede diferir del valor óptimo verdadero y todavía considerarse aceptable. Esta opción sólo se aplica a los problemas que tengan restricciones enteras. Una tolerancia mayor tiende a acelerar el proceso de solución.



Convergencia: si el valor del cambio relativo en la celda objetivo es menor que el número introducido en el cuadro Convergencia para las últimas cinco iteraciones, Solver se detendrá. La convergencia se aplica únicamente a los problemas no lineales y debe indicarse mediante una fracción entre 0 (cero) y 1. Cuantas más posiciones decimales tenga el número que se escriba, menor será la convergencia; por ejemplo, 0,0001 indica un cambio relativo menor que 0,01. Cuanto menor sea el valor de convergencia, más tiempo se tardará en encontrar una solución.



Adoptar modelo lineal: selecciónelo para acelerar el proceso de solución cuando todas las relaciones en el modelo sean lineales y desee resolver un problema de optimización lineal.



Mostrar resultado de iteraciones: selecciónelo para que Solver deje de mostrar temporalmente los resultados de cada iteración. No es aconsejable seleccionarlo ya que demora el proceso de resolución del problema.



Usar escala automática: selecciónelo para utilizar la escala automática 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.



Adoptar no-negativo: se discutió en el punto 6.

9. Finalmente si se desea volver a la configuración predeterminada, el botón Restablecer todo de la Ilustración 1 anula cualquier cambio realizado en el recuadro de opciones y también borra la celda objetivo, las variables y las restricciones.

2

Problemas y Soluciones

Esta sección está orientada a resolver problemas que se puedan originar con la utilización de la herramienta Solver.

2.1 Solver se detiene antes de encontrar una solución óptima En algunas ocasiones Solver se detiene antes de encontrar una solución óptima; esto puede originarse por cualquiera de los siguientes motivos: 

Se ha interrumpido el proceso de solución: se puede interrumpir el proceso de solución presionando ESC. Microsoft Excel vuelve a realizar los cálculos de la hoja de cálculo con el último valor encontrado para las celdas ajustables.



Se ha activado Mostrar resultado de iteraciones en el cuadro de diálogo Opciones de Solver antes de hacer clic en Resolver. No es aconsejable seleccionarlo ya que el programa se detendrá en cada iteración y llevará mucho tiempo encontrar el óptimo.



Se ha hecho clic en Detener mientras se hacía un recorrido a través de las iteraciones o cuando se ha alcanzado el tiempo máximo o el número máximo de iteraciones.



Se ha activado la casilla de verificación Adoptar modelo lineal en el cuadro de diálogo Opciones de Solver, pero es un problema no lineal.



El valor en Definir celda objetivo que se ha especificado en el cuadro de diálogo Parámetros de Solver aumenta o disminuye sin límite.



Es necesario permitir más tiempo a Solver para que encuentre una solución. Ajuste el valor de Tiempo máximo o de Iteraciones en el cuadro de diálogo Opciones de Solver.



En problemas con restricciones enteras, es necesario disminuir el valor de Tolerancia en el cuadro de diálogo Opciones de Solver para que Solver pueda encontrar una solución entera mejor.



En problemas no lineales, es necesario disminuir el valor de Convergencia en el cuadro de diálogo Opciones de Solver, para que Solver pueda buscar una solución cuando el valor de la celda objetivo cambie lentamente.



Es necesario activar la casilla de verificación Usar escala automática en el cuadro de diálogo Opciones de Solver, ya que algunos valores de entrada tienen diferentes órdenes de magnitud, o bien los valores de entrada y los resultados difieren en varios órdenes de magnitud. Si los valores típicos de las celdas ajustables y las celdas restringidas o celdas objetivo difieren entre sí más de unos pocos órdenes de magnitud, active la casilla de verificación Usar escala automática en el cuadro de diálogo Opciones de Solver. En los problemas no lineales, antes de seleccionar Resolver en el cuadro de diálogo Parámetros de Solver, asegúrese de que los valores iniciales de las celdas ajustables tengan el mismo orden de magnitud que el deseado en los valores finales.

2.2 No se obtiene la solución prevista En los problemas no lineales, puede ser útil probar diferentes valores iniciales para las celdas ajustables o cambiantes, especialmente si Solver ha encontrado una solución significativamente diferente de la que se esperaba. También puede reducirse el tiempo de solución definiendo las celdas ajustables con valores que se crean cercanos al valor óptimo. En los modelos lineales (es decir, si se ha activado la casilla de verificación Adoptar modelo lineal en el cuadro de diálogo Opciones de Solver), los valores iniciales de las celdas ajustables no afectarán a los valores finales ni al tiempo de solución.

2.3 La solución que Solver ha encontrado es distinta del resultado anterior Solver muestra el siguiente mensaje, "Solver ha llegado a la solución actual. Todas las restricciones se han satisfecho". El cambio relativo en la celda objetivo es menor que el valor de Convergencia en el cuadro de diálogo Opciones de Solver. Si se introduce un valor menor que el valor de Convergencia, Solver puede buscar una solución mejor pero tardará más tiempo en encontrarla.

2.4 Solver no encuentra una solución óptima A continuación se muestra la lista de mensajes de finalización de Solver: 

Solver no puede mejorar la solución actual. Todas las restricciones se han satisfecho: solamente se ha encontrado una solución aproximada; no se alcanzó mayor precisión o el valor de precisión es demasiado bajo. Pruebe cambiando el valor de precisión a un número mayor en el cuadro de diálogo Opciones de Solver y ejecute otra vez el programa.



Los valores de la celda objetivo no convergen: el valor de la celda objetivo aumenta (o disminuye) sin límites, aunque se hayan satisfecho todas las restricciones. Puede haberse omitido una o varias restricciones al definir el problema. Compruebe los valores actuales de la hoja de cálculo para ver la divergencia en la solución, compruebe las restricciones y ejecute otra vez el programa.



Solver no ha podido encontrar una solución factible: Solver no ha podido encontrar una solución de prueba que satisfaga todas las restricciones dentro de los valores de precisión. Es probable que las restricciones no sean coherentes. Examine la hoja de cálculo por si hubiera algún error en las fórmulas de restricción o en la opción de las restricciones.



No se han satisfecho las condiciones para adoptar modelo lineal: se ha activado la casilla de verificación Adoptar modelo lineal, pero los cálculos finales en los valores máximos de Solver no están de acuerdo con el modelo lineal. La solución no es válida para las fórmulas de la hoja de cálculo real. Para comprobar si se trata de un problema no lineal, active la casilla de verificación Usar escala automática y ejecute otra vez el programa. Si aparece nuevamente este mensaje, desactive la casilla de verificación Adoptar modelo lineal y ejecute otra vez el programa.



Solver ha encontrado un valor de error en una celda objetivo o restringida: una o varias fórmulas han producido un valor de error en el último cálculo. Busque la celda objetivo o la celda restringida que contiene el error y cambie la fórmula para que produzca un valor numérico adecuado.

El problema también puede deberse a el ingreso de un nombre o una fórmula incorrecta en el cuadro de diálogo Agregar restricción o Cambiar restricción, o bien se ha introducido "entero" o "binario" en el cuadro Restricción. 

Memoria disponible insuficiente para resolver el problema: Microsoft Excel no ha podido asignar la memoria necesaria para Solver. Cierre algunos archivos o programas e inténtelo otra vez.



Otro elemento de Microsoft Excel está utilizando SOLVER.DLL: se está ejecutando más de una sesión de Microsoft Excel y otra sesión ya está utilizando Solver.dll. Solamente puede utilizarse en una sesión cada vez.



El problema es demasiado grande para Solver: ha excedido el número máximo de variables de decisión (200) o rangos con variables (22). La solución implica agrupar algunas variables de manera astuta o simplemente replantear el ejercicio.

3

Recomendaciones y Comentarios

A continuación se presenta una lista de recomendaciones y comentarios útiles para operar Microsoft Excel y Solver de la manera más eficiente y con la menor cantidad de problemas: 

No utilice funciones lógicas para realizar cálculos que después serán incorporados a Solver ya que éste no podrá resolver el problema de optimización. Sin embargo sí se pueden utilizar las funciones Min y Max.



Es conveniente trabajar con celdas y grupos de celdas definidas con un nombre familiar. Para realizar esto, seleccione la celda o el rango de celdas que desea definir y en el menú Insertar, seleccione Nombre y luego Definir. Escriba el nombre (sin espacios) que desea otorgarle a esta(s) celdas y haga clic en Ok. Si el rango seleccionado ya estaba definido pero igual se realizó este procedimiento, queda con ambos nombres. La ventaja de esta técnica es que en Solver se podrá ver la información con las definiciones otorgadas facilitando las revisiones y posibles correcciones. Si se ve la planilla a un Zoom del 25%, se podrán ver los nombres sobre las celdas.



Es recomendable ir incorporando las restricciones una a una en Solver. Esto es, luego de que se agrega la primera restricción, se corre Solver. Con esta técnica será posible identificar qué restricción me está originando un problema.



Cuando la función objetivo comprende la sumatoria de muchas celdas distintas, por ejemplo la suma de los ingresos menos los costos, es conveniente realizar sumas parciales de ingresos y costos y luego sumar. Esto facilita la escritura y permite comprobar que no existen errores en las fórmulas.



Siempre es conveniente ir guardando versiones antes de realizar cambios. Esto permitirá volver a una versión antigua si los cambios realizados no satisfacen las expectativas.



Trate de utilizar colores para identificar los distintos componentes del problema. De esta manera le será más fácil identificar la información que busca en la planilla.



Una vez que obtenga un resultado que lo deja relativamente conforme, corra Solver varias veces para comprobar de que efectivamente ese es el resultado. Si éste cambia, revise las soluciones sugeridas en la sección de Problemas y Soluciones.



Si a pesar de todas las recomendaciones sugeridas en la sección Problemas y Soluciones, Solver sigue arrojando problemas de no-linealidad, pruebe replanteando el problema.



Utilice el teclado numérico, o fíjese bien en la configuración de decimales para no cometer el error de usar punto cuando debe ser coma (o coma cuando debe ser punto).



Utilice la función Sumaproducto cuando deba calcular la suma del producto de dos matrices ya que se disminuye la cantidad de trabajo realizado.



Es aconsejable utilizar la auditoría de fórmulas en el menú Herramientas. Permite rastrear los precedentes o dependientes de un resultado.