¿Qué es Excel Solver?

2024 Autor: Abigail Brown | [email protected]. Última modificación: 2023-12-17 06:45

El complemento Excel Solver realiza una optimización matemática. Esto se usa típicamente para ajustar modelos complejos a datos o encontrar soluciones iterativas a problemas. Por ejemplo, es posible que desee ajustar una curva a través de algunos puntos de datos mediante una ecuación. Solver puede encontrar las constantes en la ecuación que dan el mejor ajuste a los datos. Otra aplicación es cuando es difícil reorganizar un modelo para que el resultado requerido sea el sujeto de una ecuación.

¿Dónde está Solver en Excel?

El complemento Solver se incluye con Excel, pero no siempre se carga como parte de una instalación predeterminada. Para comprobar si está cargado, seleccione la pestaña DATA y busque el icono Solver en la sección Análisis.

Si no puede encontrar Solver en la pestaña DATOS, deberá cargar el complemento:

1. Seleccione la pestaña ARCHIVO y luego seleccione Opciones.

   

2. En el cuadro de diálogo Opciones seleccione Complementos en las pestañas del lado izquierdo.

   

3. En la parte inferior de la ventana, seleccione Excel Add-ins del menú desplegable Administrar y seleccione Ir…

   

4. Marque la casilla de verificación junto a Solver Add-in y seleccione OK.

   

5. El comando Solver debería aparecer ahora en la pestaña DATA. Está listo para usar Solver.

   

Uso de Solver en Excel

Empecemos con un ejemplo simple para entender lo que hace el Solver. Imagina que queremos saber qué radio dará un círculo con un área de 50 unidades cuadradas. Conocemos la ecuación del área de un círculo (A=pi r²). Por supuesto, podríamos reorganizar esta ecuación para dar el radio requerido para un área dada, pero por el bien del ejemplo, supongamos que no sabemos cómo hacerlo.

Cree una hoja de cálculo con el radio en B1 y calcule el área en B2 usando la ecuación =pi()B1^2.

Podríamos ajustar manualmente el valor en B1 hasta que B2 muestre un valor lo suficientemente cercano a 50. Según la precisión con la que necesita ser, esto podría ser un enfoque práctico. Sin embargo, si necesitamos ser muy exactos, tomará mucho tiempo hacer los ajustes necesarios. En realidad, esto es esencialmente lo que hace Solver. Hace ajustes a los valores en ciertas celdas y verifica el valor en una celda objetivo:

1. Seleccione la pestaña DATA y Solver, para cargar el cuadro de diálogo Solver Parameters

2. Establezca la celda Objetivo para que sea el Área, B2. Este es el valor que se comprobará, ajustando otras celdas hasta que esta alcance el valor correcto.

   

3. Seleccione el botón para Valor de: y establezca un valor de 50. Este es el valor que debe alcanzar B2.

   

4. En el cuadro titulado Cambiando celdas variables: ingrese la celda que contiene el radio, B1.

   

5. Deje las otras opciones como están por defecto y seleccione Resolver. Se realiza la optimización, se ajusta el valor de B1 hasta que B2 sea 50 y se muestra el diálogo Resultados del Solver.

   

6. Seleccione OK para mantener la solución.

   

Este sencillo ejemplo muestra cómo funciona el solucionador. En este caso, podríamos haber obtenido la solución más fácilmente de otras formas. A continuación, veremos algunos ejemplos en los que Solver brinda soluciones que serían difíciles de encontrar de otra manera.

Ajuste de un modelo complejo con el complemento Excel Solver

Excel tiene una función integrada para realizar una regresión lineal, ajustando una línea recta a través de un conjunto de datos. Muchas funciones no lineales comunes se pueden linealizar, lo que significa que la regresión lineal se puede usar para ajustar funciones como las exponenciales. Para funciones más complejas, el Solver se puede utilizar para realizar una "minimización por mínimos cuadrados". En este ejemplo, consideraremos ajustar una ecuación de la forma ax^b+cx^d a los datos que se muestran a continuación.

Esto implica los siguientes pasos:

1. Organiza el conjunto de datos con los valores x en la columna A y los valores y en la columna B.
2. Cree los 4 valores de los coeficientes (a, b, c y d) en algún lugar de la hoja de cálculo; se les puede dar valores iniciales arbitrarios.

3. Cree una columna de valores Y ajustados, usando una ecuación de forma ax^b+cx^d que haga referencia a los coeficientes creados en el paso 2 y los valores x en la columna A. Tenga en cuenta que para copiar la fórmula hacia abajo la columna, las referencias a los coeficientes deben ser absolutas mientras que las referencias a los valores de x deben ser relativas.

   

4. Aunque no es esencial, puede obtener una indicación visual de qué tan bien se ajusta la ecuación trazando ambas columnas y contra los valores x en un solo gráfico de dispersión XY. Tiene sentido usar marcadores para los puntos de datos originales, ya que estos son valores discretos con ruido, y usar una línea para la ecuación ajustada.

   

5. A continuación, necesitamos una forma de cuantificar la diferencia entre los datos y nuestra ecuación ajustada. La forma estándar de hacer esto es calcular la suma de las diferencias al cuadrado. En una tercera columna, para cada fila, el valor de los datos originales para Y se resta del valor de la ecuación ajustado y el resultado se eleva al cuadrado. Entonces, en D2, el valor viene dado por =(C2-B2)^2 Luego se calcula la suma de todos estos valores al cuadrado. Como los valores están elevados al cuadrado, solo pueden ser positivos.

   

6. Ahora está listo para realizar la optimización usando Solver. Hay cuatro coeficientes que deben ajustarse (a, b, c y d). También tiene un único valor objetivo para minimizar, la suma de las diferencias al cuadrado. Inicie el solucionador, como se indicó anteriormente, y configure los parámetros del solucionador para hacer referencia a estos valores, como se muestra a continuación.

   

7. Desmarque la opción para Hacer que las variables sin restricciones no sean negativas, esto obligaría a todos los coeficientes a tomar valores positivos.

   

8. Seleccione Resolver y revise los resultados. El gráfico se actualizará dando una buena indicación de la bondad del ajuste. Si el solucionador no produce un buen ajuste en el primer intento, puede intentar ejecutarlo de nuevo. Si el ajuste ha mejorado, intente resolver a partir de los valores actuales. De lo contrario, podría intentar mejorar manualmente el ajuste antes de resolverlo.

   

9. Una vez que se ha obtenido un buen ajuste, puede salir del solucionador.

Resolver un modelo iterativamente

A veces hay una ecuación relativamente simple que da una salida en términos de alguna entrada. Sin embargo, cuando tratamos de invertir el problema no es posible encontrar una solución simple. Por ejemplo, la potencia consumida por un vehículo está dada aproximadamente por P=av + bv^3 donde v es la velocidad, a es un coeficiente para la resistencia a la rodadura y b es un coeficiente para resistencia aerodinámica. Aunque esta es una ecuación bastante simple, no es fácil de reorganizar para dar una ecuación de la velocidad que alcanzará el vehículo para una entrada de energía dada. Sin embargo, podemos usar Solver para encontrar iterativamente esta velocidad. Por ejemplo, encuentre la velocidad alcanzada con una potencia de entrada de 740 W.

1. Configure una hoja de cálculo simple con la velocidad, los coeficientes a y b, y la potencia calculada a partir de ellos.

   

2. Inicie el solucionador e ingrese el poder, B5, como objetivo. Establezca un valor objetivo de 740 y seleccione la velocidad, B2, como celdas variables para cambiar. Seleccione resolver para iniciar la solución.

   

3. El solucionador ajusta el valor de la velocidad hasta que la potencia está muy cerca de 740, proporcionando la velocidad que necesitamos.

   

4. Resolver modelos de esta manera a menudo puede ser más rápido y menos propenso a errores que invertir modelos complejos.

Comprender las diferentes opciones disponibles en el solucionador puede ser bastante difícil. Si tiene dificultades para obtener una solución sensata, suele ser útil aplicar condiciones de contorno a las celdas modificables. Estos son valores límite más allá de los cuales no deben ajustarse. Por ejemplo, en el ejemplo anterior, la velocidad no debe ser inferior a cero y también sería posible establecer un límite superior. Esta sería una velocidad a la que está bastante seguro de que el vehículo no puede ir más rápido. Si puede establecer límites para las celdas de variables modificables, también hace que otras opciones más avanzadas funcionen mejor, como el inicio múltiple. Esto ejecutará varias soluciones diferentes, comenzando con diferentes valores iniciales para las variables.

Elegir el método de resolución también puede ser difícil. Simplex LP solo es adecuado para modelos lineales, si el problema no es lineal, fallará con un mensaje de que no se cumplió esta condición. Los otros dos métodos son adecuados para métodos no lineales. GRG Nonlinear es el más rápido, pero su solución puede depender en gran medida de las condiciones iniciales de arranque. Tiene la flexibilidad de que no requiere variables para tener límites establecidos. El solucionador evolutivo suele ser el más confiable, pero requiere que todas las variables tengan límites superiores e inferiores, lo que puede ser difícil de calcular por adelantado.

El complemento Excel Solver es una herramienta muy poderosa que se puede aplicar a muchos problemas prácticos. Para acceder completamente al poder de Excel, intente combinar Solver con macros de Excel.