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

"Buscar unha solución" é un complemento de Excel, a través do cal é posible escoller a mellor solución aos problemas en función das restricións especificadas. A función permite programar empregados, distribuír custos ou investimentos. Coñecer como funciona esta función aforrarache tempo e esforzo.

Que é a busca de solucións

En combinación con outras opcións en Excel, hai unha función menos popular, pero moi necesaria, "Buscar unha solución". A pesar de que atopalo non é doado, coñecelo e utilizalo axuda a resolver moitos problemas. A opción procesa os datos e dá a solución óptima das permitidas. O artigo describe como funciona directamente a Busca dunha solución.

Como activar a función "Buscar unha solución".

A pesar da eficacia, a opción en cuestión non está nun lugar destacado na barra de ferramentas ou no menú contextual. A maioría dos usuarios que traballan en Excel non son conscientes da súa presenza. Por defecto, esta función está desactivada, para visualizala, realice as seguintes accións:

1. Abre o "Ficheiro" facendo clic no nome apropiado.
2. Fai clic na sección "Configuración".
3. A continuación, seleccione a subsección "Complementos". Todos os complementos do programa mostraranse aquí, a inscrición "Xestión" aparecerá a continuación. No lado dereito aparecerá un menú emerxente onde debes seleccionar "Complementos de Excel". A continuación, fai clic en "Ir".
   

   

4. Aparecerá unha xanela adicional "Complementos" no monitor. Marque a caixa xunto á función desexada e prema en Aceptar.
5. A función desexada aparecerá na cinta á dereita da sección "Datos".

Sobre os modelos

Esta información será moi útil para aqueles que se están familiarizando co concepto de "modelo de optimización". Antes de usar "Buscar unha solución", recoméndase estudar os materiais sobre os métodos de construción de modelos:

• a opción que se considere permitirá identificar o mellor método para destinar os fondos para investimentos, carga de local, subministración de bens ou outras actuacións onde sexa necesario buscar a mellor solución.
• O “método óptimo” en tal situación significaría: aumentar os ingresos, reducir custos, mellorar a calidade, etc.

Tarefas típicas de optimización:

• Determinación dun plan de produción, durante o cal o beneficio da venda de mercadorías liberadas será máximo.
• Determinación de mapas de transporte, durante os cales se minimizan os custos de transporte.
• Procura da distribución de varias máquinas para diversos tipos de traballo, de xeito que se reduzan os custos de produción.
• Determinación do prazo máis breve para a realización dos traballos.

Importante! Para formalizar a tarefa é necesario crear un modelo que reflicta os principais parámetros da materia. En Excel, un modelo é un conxunto de fórmulas que usan variables. A opción considerada busca tales indicadores que a función obxectivo sexa maior (menor) ou igual ao valor especificado.

Etapa preparatoria

Antes de colocar unha función na cinta, cómpre comprender como funciona a opción. Por exemplo, hai información sobre a venda de bens indicada na táboa. A tarefa é asignar un desconto por cada artigo, que sería de 4.5 millóns de rublos. O parámetro móstrase dentro dunha cela chamada destino. En base a el calcúlanse outros parámetros.

A nosa tarefa será calcular o desconto polo que se multiplican as cantidades para a venda de diversos produtos. Estes 2 elementos están conectados por unha fórmula escrita como esta: =D13*$G$2. Onde en D13 está escrita a cantidade total para a implementación e $G$2 é o enderezo do elemento desexado.

Usando a función e configurala

Cando a fórmula estea lista, cómpre usar a propia función directamente:

1. Debe cambiar á sección "Datos" e facer clic en "Buscar unha solución".

2. Abrirase as "Opcións", onde se establecen os axustes necesarios. Na liña "Optimizar a función obxectivo:" debes especificar a cela onde se mostra a suma dos descontos. É posible prescribir as coordenadas vostede mesmo ou escoller entre o documento.

3. A continuación, cómpre ir á configuración doutros parámetros. Na sección "Para:", é posible establecer os límites máximo e mínimo ou un número exacto.

4. A continuación, énchese o campo "Cambiar os valores das variables:". Aquí introdúcense os datos da cela desexada, que contén un valor específico. As coordenadas rexístrase de forma independente ou se fai clic na cela correspondente do documento.

5. Despois edítase a pestana "Segundo restricións:", onde se definen as restricións aos datos aplicados. Por exemplo, exclúense as fraccións decimais ou os números negativos.

6. Despois diso, ábrese unha xanela que che permite engadir restricións nos cálculos. A liña inicial contén as coordenadas dunha cela ou dun rango enteiro. Seguindo as condicións da tarefa, indícanse os datos da cela desexada, onde se mostra o indicador de desconto. Despois determínase o signo de comparación. Establécese como "maior ou igual a" para que o valor final non estea cun signo menos. O "Límite" establecido na liña 3 é 0 nesta situación. Tamén é posible establecer un límite con "Engadir". Os seguintes pasos son os mesmos.

7. Cando se completen os pasos anteriores, o límite establecido aparece na liña máis grande. A lista pode ser grande e dependerá da complexidade dos cálculos, non obstante, nunha situación particular, 1 condición é suficiente.

8. Ademais, é posible seleccionar outras opcións avanzadas. Na parte inferior dereita hai unha opción "Opcións" que che permite facelo.

9. Na configuración, pode configurar a "Precisión de limitación" e "Límites de solución". Na nosa situación, non hai necesidade de utilizar estas opcións.

10. Cando se completen os axustes, a función en si comeza: fai clic en "Buscar unha solución".

11. Despois de que o programa realice os cálculos necesarios e emita os cálculos finais nas celas requiridas. Despois ábrese unha xanela cos resultados, onde se gardan/cancelan os resultados ou se configuran os parámetros de busca segundo un novo. Cando os datos cumpren os requisitos, gárdase a solución atopada. Se marca a caixa de diálogo "Volver á caixa de diálogo de opcións de busca de solucións" con antelación, abrirase unha xanela coa configuración das funcións.

12. Existe a posibilidade de que os cálculos resulten erróneos ou sexa necesario modificar os datos iniciais para obter outros indicadores. Nesta situación, cómpre volver abrir a xanela de configuración e comprobar a información dúas veces.
13. Cando os datos son precisos, pódese utilizar un método alternativo. Para estes efectos, cómpre facer clic na opción actual e seleccionar o método máis axeitado da lista que aparece:

• Buscar unha solución usando un gradiente xeneralizado para problemas non lineais. Por defecto, utilízase esta opción, pero é posible usar outras.
• Buscar solucións para problemas lineais baseados no método simplex.
• Usar a busca evolutiva para completar unha tarefa.

Atención! Cando as opcións anteriores non puideron facer fronte á tarefa, debes comprobar de novo os datos na configuración, xa que este é a miúdo o principal erro en tales tarefas.

14. Cando se recibe o desconto desexado, queda aplicalo para calcular o importe dos descontos de cada artigo. Para este fin, resáltase o elemento inicial da columna "Importe de desconto", escríbese a fórmula «=D2*$G$2» e prema "Intro". Os signos do dólar colócanse para que cando a fórmula se estenda ás liñas adxacentes, G2 non cambie.

15. Agora obterase o importe do desconto para o artigo inicial. A continuación, debes mover o cursor sobre a esquina da cela, cando se converta nun "máis", preme o LMB e a fórmula esténdese ata as liñas necesarias.
16. Despois diso, a mesa estará finalmente lista.

Cargar/Gardar opcións de busca

Esta opción é útil cando se aplican varias opcións de restrición.

1. No menú Opcións do buscador de solucións, faga clic en Cargar/Gardar.
2. Introduza o intervalo para a área do modelo e prema Gardar ou Cargar.

Ao gardar o modelo, introdúcese unha referencia a 1 cela dunha columna baleira onde se colocará o modelo de optimización. Durante a carga do modelo, introdúcese unha referencia a todo o intervalo que contén o modelo de optimización.

Importante! Para gardar a última configuración no menú Opcións de solución, gárdase un libro de traballo. Cada folla ten as súas propias opcións de complemento Solver. Ademais, é posible establecer máis dunha tarefa para unha folla facendo clic no botón "Cargar ou Gardar" para gardar tarefas individuais.

Un exemplo sinxelo de uso de Solver

É necesario cargar o recipiente con recipientes para que a súa masa sexa máxima. O depósito ten un volume de 32 metros cúbicos. m. Unha caixa chea ten un peso de 20 kg, o seu volume é de 0,15 metros cúbicos. m. Caixa – 80 kg e 0,5 cu. m. Requírese que o número total de envases sexa de polo menos 110 unidades. Os datos organízanse así:

As variables do modelo están marcadas en verde. A función obxectivo está resaltada en vermello. Restricións: polo menor número de envases (maior ou igual a 110) e polo peso (=SUMAPRODUTO(B8:C8;B6:C6) – peso total da tara no recipiente.

Por analoxía, consideramos o volume total: =SUMAPRODUTO(B7:C7;B8:C8). Tal fórmula é necesaria para establecer un límite no volume total de envases. Despois, a través da "Buscar unha solución", introdúcense ligazóns a elementos con variables, fórmulas e os propios indicadores (ou ligazóns a celas específicas). Por suposto, o número de contedores é un número enteiro (tamén é unha limitación). Prememos "Buscar unha solución", polo que atopamos tal número de recipientes cando a masa total é máxima e se teñen en conta todas as restricións.

A busca de solución non puido atopar solucións

Tal notificación aparece cando a función en cuestión non atopou combinacións de puntuacións variables que satisfagan cada restrición. Cando se usa o método Simplex, é moi posible que non haxa solución.

Cando se utiliza un método de resolución de problemas non lineais, partindo en todos os casos dos indicadores iniciais das variables, isto indica que a posible solución está lonxe de tales parámetros. Se executas a función con outros indicadores iniciais das variables, probablemente haxa unha solución.

Por exemplo, ao usar o método non lineal, os elementos da táboa con variables non se encheron e a función non atopou solucións. Isto non significa que non haxa solución. Agora, tendo en conta os resultados dunha determinada valoración, introdúcense outros datos nos elementos con variables próximas ás recibidas.

En calquera situación, debes examinar inicialmente o modelo para detectar a ausencia dun conflito de restricións. Moitas veces, isto está interconectado coa selección inadecuada da proporción ou do indicador limitante.

No exemplo anterior, o indicador de volume máximo é de 16 metros cúbicos. m en lugar de 32, porque tal restrición contradí os indicadores do número mínimo de asentos, xa que corresponderá ao número de 16,5 metros cúbicos. m.

Conclusión

En base a isto, a opción "Buscar unha solución" en Excel axudará a resolver problemas específicos que son bastante difíciles ou imposibles de resolver dos xeitos habituais. A dificultade para aplicar este método é que inicialmente esta opción está oculta, polo que a maioría dos usuarios non son conscientes da súa presenza. Ademais, a función é bastante difícil de aprender e usar, pero cunha investigación adecuada, traerá grandes beneficios e facilitará os cálculos.