Resolver función en Excel. Habilitar, caso de uso con capturas de pantalla

“Buscar una solución” es un complemento de Excel, a través del cual es posible elegir la mejor solución a los problemas en función de las restricciones especificadas. La función permite programar empleados, distribuir costos o inversiones. Saber cómo funciona esta característica le ahorrará tiempo y esfuerzo.

¿Qué es la búsqueda de soluciones?

En combinación con varias otras opciones en Excel, hay una función menos popular, pero extremadamente necesaria, "Buscar una solución". A pesar de que encontrarlo no es fácil, conocerlo y usarlo ayuda a resolver muchos problemas. La opción procesa los datos y da la solución óptima de las permitidas. El artículo describe cómo funciona directamente la búsqueda de una solución.

Cómo activar la función "Buscar una solución"

A pesar de la efectividad, la opción en cuestión no se encuentra en un lugar destacado en la barra de herramientas o el menú contextual. La mayoría de los usuarios que trabajan en Excel no son conscientes de su presencia. Por defecto, esta función está deshabilitada, para mostrarla, realice las siguientes acciones:

1. Abra el "Archivo" haciendo clic en el nombre apropiado.
2. Haga clic en la sección "Configuración".
3. Luego seleccione la subsección "Complementos". Todos los complementos del programa se mostrarán aquí, la inscripción "Administración" aparecerá debajo. En el lado derecho habrá un menú emergente donde debe seleccionar "Complementos de Excel". Luego haga clic en "Ir".
   

   

4. Se mostrará una ventana adicional "Complementos" en el monitor. Marque la casilla junto a la función deseada y haga clic en Aceptar.
5. La función deseada aparecerá en la cinta a la derecha de la sección "Datos".

Acerca de los modelos

Esta información será de gran utilidad para aquellos que recién se están familiarizando con el concepto de “modelo de optimización”. Antes de utilizar la "Búsqueda de una solución", se recomienda estudiar los materiales sobre los métodos de construcción de modelos:

• la opción en consideración permitirá identificar el mejor método para asignar fondos para inversiones, carga de locales, suministro de bienes u otras acciones donde sea necesario encontrar la mejor solución.
• El “método óptimo” en tal situación sería: aumentar los ingresos, reducir los costos, mejorar la calidad, etc.

Tareas típicas de optimización:

• Determinación de un plan de producción, durante el cual el beneficio por la venta de mercancías liberadas será máximo.
• Determinación de mapas de transporte, durante los cuales se minimizan los costos de transporte.
• Buscar la distribución de varias máquinas para varios tipos de trabajo, de forma que se reduzcan los costes de producción.
• Determinación del tiempo más breve para la realización de los trabajos.

¡Importante! Para formalizar la tarea, es necesario crear un modelo que refleje los principales parámetros del área temática. En Excel, un modelo es un conjunto de fórmulas que utilizan variables. La opción considerada busca indicadores tales que la función objetivo sea mayor (menor) o igual al valor especificado.

Etapa preparatoria

Antes de colocar una función en la cinta, debe comprender cómo funciona la opción. Por ejemplo, hay información sobre la venta de bienes indicados en la tabla. La tarea es asignar un descuento para cada artículo, que sería de 4.5 millones de rublos. El parámetro se muestra dentro de una celda llamada objetivo. En base a ello, se calculan otros parámetros.

Nuestra tarea será calcular el descuento por el cual se multiplican las sumas por la venta de varios productos. Estos 2 elementos están conectados por una fórmula escrita así: =D13*$G$2. Donde en D13 se escribe la cantidad total para la implementación, y $G$2 es la dirección del elemento deseado.

Uso de la función y configuración

Cuando la fórmula esté lista, debe usar la función directamente:

1. Debe cambiar a la sección "Datos" y hacer clic en "Buscar una solución".

2. Se abrirán las "Opciones", donde se establecen los ajustes necesarios. En la línea "Optimizar la función objetivo:" debe especificar la celda donde se muestra la suma de los descuentos. Es posible prescribir las coordenadas usted mismo o elegir del documento.

3. A continuación, debe ir a la configuración de otros parámetros. En la sección “Hasta:”, es posible establecer los límites máximos y mínimos o un número exacto.

4. Luego se llena el campo “Cambiando los valores de las variables:”. Aquí se ingresan los datos de la celda deseada, la cual contiene un valor específico. Las coordenadas se registran de forma independiente o se hace clic en la celda correspondiente del documento.

5. Luego se edita la pestaña “Según restricciones:”, donde se establecen las restricciones sobre los datos aplicados. Por ejemplo, se excluyen las fracciones decimales o los números negativos.

6. Después de eso, se abre una ventana que le permite agregar restricciones en los cálculos. La línea inicial contiene las coordenadas de una celda o un rango completo. Siguiendo las condiciones de la tarea, se indican los datos de la celda deseada, donde se muestra el indicador de descuento. Luego se determina el signo de comparación. Se establece en “mayor o igual que” para que el valor final no sea con signo menos. El “Límite” establecido en la línea 3 es 0 en esta situación. También es posible establecer un límite con “Agregar”. Los siguientes pasos son los mismos.

7. Cuando se completan los pasos anteriores, el límite establecido aparece en la línea más grande. La lista puede ser grande y dependerá de la complejidad de los cálculos, sin embargo, en una situación particular, 1 condición es suficiente.

8. Además, es posible seleccionar otras configuraciones avanzadas. En la parte inferior derecha hay una opción "Opciones" que le permite hacer esto.

9. En la configuración, puede establecer la "Precisión de limitación" y los "Límites de solución". En nuestra situación, no hay necesidad de usar estas opciones.

10. Cuando se completa la configuración, la función en sí comienza: haga clic en "Buscar una solución".

11. Después de que el programa realice los cálculos requeridos y emita los cálculos finales en las celdas requeridas. Luego se abre una ventana con los resultados, donde se guardan/cancelan los resultados, o se configuran los parámetros de búsqueda según uno nuevo. Cuando los datos cumplen los requisitos, se guarda la solución encontrada. Si marca la casilla "Volver al cuadro de diálogo de opciones de búsqueda de soluciones" de antemano, se abrirá una ventana con la configuración de la función.

12. Existe la posibilidad de que los cálculos resulten erróneos o sea necesario cambiar los datos iniciales para obtener otros indicadores. En tal situación, debe volver a abrir la ventana de configuración y verificar la información.
13. Cuando los datos son precisos, se puede utilizar un método alternativo. Para estos efectos, debe hacer clic en la opción actual y seleccionar el método más adecuado de la lista que aparece:

• Encontrar una solución usando un gradiente generalizado para problemas no lineales. Por defecto se utiliza esta opción, pero es posible utilizar otras.
• Búsqueda de soluciones a problemas lineales basados ​​en el método símplex.
• Usar la búsqueda evolutiva para completar una tarea.

¡Atención! Cuando las opciones anteriores no lograron hacer frente a la tarea, debe verificar los datos en la configuración nuevamente, ya que este suele ser el principal error en tales tareas.

14. Cuando se recibe el descuento deseado, queda aplicarlo para calcular la cantidad de descuentos para cada artículo. Para este propósito, se resalta el elemento inicial de la columna "Importe de descuento", se escribe la fórmula «=D2*$G$2» y presione “Entrar”. Los signos de dólar se colocan para que cuando la fórmula se extienda a líneas adyacentes, G2 no cambie.

15. Ahora se obtendrá el importe del descuento del artículo inicial. Luego, debe mover el cursor sobre la esquina de la celda, cuando se convierte en un "más", se presiona el LMB y la fórmula se estira a las líneas requeridas.
16. Después de eso, la mesa estará finalmente lista.

Cargar/Guardar opciones de búsqueda

Esta opción es útil cuando se aplican varias opciones de restricción.

1. En el menú Opciones del buscador de soluciones, haga clic en Cargar/Guardar.
2. Ingrese el rango para el área del modelo y haga clic en Guardar o Cargar.

Al guardar el modelo, se ingresa una referencia a 1 celda de una columna vacía donde se colocará el modelo de optimización. Durante la carga del modelo, se ingresa una referencia a todo el rango que contiene el modelo de optimización.

¡Importante! Para guardar la última configuración en el menú Opciones de solución, se guarda un libro de trabajo. Cada hoja tiene sus propias opciones de complemento de Solver. Además, es posible configurar más de 1 tarea para una hoja haciendo clic en el botón "Cargar o Guardar" para guardar tareas individuales.

Un ejemplo simple del uso de Solver

Es necesario cargar el contenedor con contenedores para que su masa sea máxima. El tanque tiene un volumen de 32 metros cúbicos. metro. Una caja llena tiene un peso de 20 kg, su volumen es de 0,15 metros cúbicos. metro. Caja – 80 kg y 0,5 cu. metro. Se requiere que el número total de contenedores sea de al menos 110 uds. Los datos están organizados así:

Las variables del modelo están marcadas en verde. La función objetivo está resaltada en rojo. Restricciones: por el menor número de envases (mayor o igual a 110) y por peso (=SUMAPRODUCTO(B8:C8,B6:C6) – peso de tara total en el contenedor.

Por analogía, consideramos el volumen total: =SUMAPRODUCTO(B7:C7,B8:C8). Esta fórmula es necesaria para establecer un límite en el volumen total de los contenedores. Luego, a través de la “Búsqueda de solución”, se ingresan enlaces a elementos con variables, fórmulas y los propios indicadores (o enlaces a celdas específicas). Por supuesto, el número de contenedores es un número entero (también es una limitación). Presionamos "Buscar una solución", como resultado de lo cual encontramos tal cantidad de contenedores cuando la masa total es máxima y se tienen en cuenta todas las restricciones.

Buscar solución no pudo encontrar soluciones

Dicha notificación aparece cuando la función en cuestión no ha encontrado combinaciones de puntajes variables que satisfagan cada restricción. Al usar el método Simplex, es muy posible que no haya solución.

Cuando se utiliza un método de resolución de problemas no lineales, partiendo en todos los casos de los indicadores iniciales de las variables, esto indica que la posible solución está lejos de dichos parámetros. Si ejecuta la función con otros indicadores iniciales de las variables, probablemente haya una solución.

Por ejemplo, al usar el método no lineal, los elementos de la tabla con variables no se llenaron y la función no encontró soluciones. Esto no quiere decir que no haya solución. Ahora bien, teniendo en cuenta los resultados de una determinada valoración, se introducen en los elementos otros datos con variables próximas a las recibidas.

En cualquier situación, inicialmente debe examinar el modelo para detectar la ausencia de un conflicto de restricciones. A menudo, esto está interconectado con una selección incorrecta de la relación o el indicador límite.

En el ejemplo anterior, el indicador de volumen máximo es de 16 metros cúbicos. m en lugar de 32, porque tal restricción contradice los indicadores para el número mínimo de asientos, ya que corresponderá al número de 16,5 metros cúbicos. metro.

Conclusión

En base a esto, la opción "Buscar una solución" en Excel ayudará a resolver problemas específicos que son bastante difíciles o imposibles de resolver de la manera habitual. La dificultad de aplicar este método es que inicialmente esta opción está oculta, por lo que la mayoría de los usuarios no son conscientes de su presencia. Además, la función es bastante difícil de aprender y usar, pero con la investigación adecuada, traerá grandes beneficios y facilitará los cálculos.