Conteúdo
“Procurar uma solução” é um suplemento do Excel, através do qual é possível escolher a melhor solução para os problemas com base nas restrições especificadas. A função possibilita agendar funcionários, distribuir custos ou investimentos. Saber como esse recurso funciona economizará tempo e esforço.
O que é Busca de Soluções
Em combinação com várias outras opções do Excel, existe uma função menos popular, mas extremamente necessária, “Procurar uma solução”. Apesar de não ser fácil encontrá-lo, conhecê-lo e usá-lo ajuda na resolução de muitos problemas. A opção processa os dados e fornece a solução ideal dentre as permitidas. O artigo descreve como o Search for a Solution funciona diretamente.
Como ativar o recurso “Pesquisar uma solução”
Apesar da eficácia, a opção em questão não está em lugar de destaque na barra de ferramentas ou no menu de contexto. A maioria dos usuários que trabalham no Excel não estão cientes de sua presença. Por padrão, esta função está desabilitada, para exibi-la, execute as seguintes ações:
- Abra o “Arquivo” clicando no nome apropriado.
- Clique na seção "Configurações".
- Em seguida, selecione a subseção “Complementos”. Todos os complementos do programa serão exibidos aqui, a inscrição “Gerenciamento” aparecerá abaixo. No lado direito, haverá um menu pop-up onde você deve selecionar “Excel Add-ins”. Em seguida, clique em “Ir”.
1 - Uma janela adicional “Add-ins” será exibida no monitor. Marque a caixa ao lado da função desejada e clique em OK.
- A função desejada aparecerá na faixa à direita da seção “Dados”.
Sobre modelos
Essas informações serão extremamente úteis para quem está começando a se familiarizar com o conceito de “modelo de otimização”. Antes de usar a “Busca de uma solução”, recomenda-se estudar os materiais sobre os métodos de construção de modelos:
- a opção em apreço permitirá identificar a melhor forma de alocação de fundos para investimentos, carregamento das instalações, fornecimento de bens ou outras ações em que seja necessário encontrar a melhor solução.
- O “método ótimo” em tal situação significaria: aumentar a receita, reduzir custos, melhorar a qualidade, etc.
Tarefas típicas de otimização:
- Determinação de um plano de produção, durante o qual o lucro da venda de mercadorias liberadas será máximo.
- Determinação de mapas de transporte, durante os quais os custos de transporte são minimizados.
- Busque a distribuição de várias máquinas para diversos tipos de trabalho, para que os custos de produção sejam reduzidos.
- Determinação do menor tempo para conclusão do trabalho.
Importante! Para formalizar a tarefa, é necessário criar um modelo que reflita os principais parâmetros da área temática. No Excel, um modelo é um conjunto de fórmulas que usam variáveis. A opção considerada procura por tais indicadores que a função objetivo seja maior (menor) ou igual ao valor especificado.
Fase preparatória
Antes de colocar uma função na faixa de opções, você precisa entender como a opção funciona. Por exemplo, há informações sobre a venda de mercadorias indicadas na tabela. A tarefa é atribuir um desconto para cada item, que seria de 4.5 milhões de rublos. O parâmetro é exibido dentro de uma célula chamada target. Com base nele, outros parâmetros são calculados.
Nossa tarefa será calcular o desconto pelo qual são multiplicadas as somas para a venda de vários produtos. Esses 2 elementos estão conectados por uma fórmula escrita assim: =D13*$G$2. Onde em D13 é escrita a quantidade total para a implementação, e $G$2 é o endereço do elemento desejado.
Usando a função e configurando-a
Quando a fórmula estiver pronta, você precisa usar a própria função diretamente:
- Você precisa mudar para a seção "Dados" e clicar em "Pesquisar uma solução".
- As “Opções” serão abertas, onde as configurações necessárias são definidas. Na linha “Otimizar a função objetivo:” você deve especificar a célula onde é exibida a soma dos descontos. É possível prescrever as coordenadas você mesmo ou escolher no documento.
- Em seguida, você precisa ir para as configurações de outros parâmetros. Na seção “Para:” é possível definir os limites máximo e mínimo ou um número exato.
- Em seguida, o campo “Alterando os valores das variáveis:” é preenchido. Aqui são inseridos os dados da célula desejada, que contém um valor específico. As coordenadas são registradas independentemente ou a célula correspondente no documento é clicada.
- Em seguida, é editada a guia “De acordo com as restrições:”, onde são definidas as restrições sobre os dados aplicados. Por exemplo, frações decimais ou números negativos são excluídos.
- Depois disso, abre-se uma janela que permite adicionar restrições nos cálculos. A linha inicial contém as coordenadas de uma célula ou de um intervalo inteiro. Seguindo as condições da tarefa, são indicados os dados da célula desejada, onde é exibido o indicador de desconto. Em seguida, o sinal de comparação é determinado. Ele é definido como “maior que ou igual a” para que o valor final não fique com um sinal de menos. O “Limite” definido na linha 3 é 0 nesta situação. Também é possível definir um limite com “Adicionar”. Os próximos passos são os mesmos.
- Quando as etapas acima forem concluídas, o limite definido aparecerá na linha maior. A lista pode ser grande e dependerá da complexidade dos cálculos, porém, em uma determinada situação, 1 condição é suficiente.
- Além disso, é possível selecionar outras configurações avançadas. No canto inferior direito há uma opção “Opções” que permite fazer isso.
- Nas configurações, você pode definir a “Precisão da limitação” e “Limites da solução”. Em nossa situação, não há necessidade de usar essas opções.
- Quando as configurações estiverem concluídas, a função em si é iniciada – clique em “Encontrar uma solução”.
- Depois que o programa executa os cálculos necessários e emite os cálculos finais nas células necessárias. Em seguida, abre-se uma janela com os resultados, onde os resultados são salvos / cancelados, ou os parâmetros de pesquisa são configurados de acordo com um novo. Quando os dados atendem aos requisitos, a solução encontrada é salva. Se você marcar a caixa “Retornar à caixa de diálogo de opções de pesquisa de solução” com antecedência, uma janela com configurações de função será aberta.
- Existe a possibilidade de que os cálculos estejam errados ou seja necessário alterar os dados iniciais para obter outros indicadores. Em tal situação, você precisa reabrir a janela de configurações e verificar novamente as informações.
- Quando os dados são precisos, um método alternativo pode ser usado. Para isso, você precisa clicar na opção atual e selecionar o método mais adequado na lista que aparece:
- Encontrando uma solução usando um gradiente generalizado para problemas não lineares. Por padrão, esta opção é utilizada, mas é possível utilizar outras.
- Encontrar soluções para problemas lineares com base no método simplex.
- Usando a busca evolutiva para completar uma tarefa.
Atenção! Quando as opções acima falharam em lidar com a tarefa, você deve verificar os dados nas configurações novamente, pois esse geralmente é o principal erro nessas tarefas.
- Quando o desconto desejado é recebido, resta aplicá-lo para calcular o valor dos descontos para cada item. Para isso, destaca-se o elemento inicial da coluna “Valor do desconto”, a fórmula é escrita «=D2*$G$2» e pressione “Entrar”. Os cifrões são colocados para que, quando a fórmula for esticada para linhas adjacentes, G2 não mude.
- O valor do desconto para o item inicial será obtido agora. Então você deve mover o cursor sobre o canto da célula, quando se torna um “mais”, o LMB é pressionado e a fórmula é esticada para as linhas necessárias.
- Depois disso, a mesa estará finalmente pronta.
Carregar/Salvar opções de pesquisa
Esta opção é útil ao aplicar várias opções de restrição.
- No menu Opções do Solution Finder, clique em Carregar/Salvar.
- Insira o intervalo para a área do modelo e clique em Salvar ou Carregar.
Ao salvar o modelo, é inserida uma referência a 1 célula de uma coluna vazia onde será colocado o modelo de otimização. Durante o carregamento do modelo, é inserida uma referência para todo o intervalo que contém o modelo de otimização.
Importante! Para salvar as últimas configurações no menu Opções de solução, uma pasta de trabalho é salva. Cada planilha tem suas próprias opções de complemento do Solver. Além disso, é possível definir mais de 1 tarefa para uma planilha clicando no botão “Carregar ou Salvar” para salvar tarefas individuais.
Um exemplo simples de uso do Solver
É necessário carregar o contêiner com contêineres para que sua massa seja máxima. O tanque tem um volume de 32 metros cúbicos. m. Uma caixa cheia tem um peso de 20 kg, seu volume é de 0,15 metros cúbicos. m. Caixa – 80 kg e 0,5 cu. m. É necessário que o número total de contêineres seja de pelo menos 110 unidades. Os dados estão organizados assim:
As variáveis do modelo estão marcadas em verde. A função objetivo é destacada em vermelho. Restrições: pelo menor número de contêineres (maior ou igual a 110) e pelo peso (=SOMAPRODUTO(B8:C8,B6:C6) – peso total da tara no recipiente.
Por analogia, consideramos o volume total: =SOMAPRODUTO(B7:C7,B8:C8). Essa fórmula é necessária para definir um limite no volume total de contêineres. Em seguida, através do “Buscar uma solução”, são inseridos links para elementos com variáveis, fórmulas e os próprios indicadores (ou links para células específicas). Obviamente, o número de contêineres é um número inteiro (também é uma limitação). Pressionamos "Encontrar uma solução", como resultado, encontramos esse número de contêineres quando a massa total é máxima e todas as restrições são levadas em consideração.
A busca por solução falhou ao encontrar soluções
Essa notificação aparece quando a função em questão não encontrou combinações de pontuações variáveis que satisfaçam cada restrição. Ao usar o método Simplex, é bem possível que não haja solução.
Quando se utiliza um método de resolução de problemas não lineares, em todos os casos a partir dos indicadores iniciais das variáveis, isso indica que a solução possível está longe de tais parâmetros. Se você executar a função com outros indicadores iniciais das variáveis, provavelmente haverá uma solução.
Por exemplo, ao usar o método não linear, os elementos da tabela com variáveis não foram preenchidos e a função não encontrou soluções. Isso não significa que não haja solução. Agora, levando em consideração os resultados de uma determinada avaliação, outros dados são inseridos nos elementos com variáveis próximas às recebidas.
Em qualquer situação, você deve examinar inicialmente o modelo quanto à ausência de um conflito de restrição. Muitas vezes, isso está interligado com a seleção inadequada da razão ou do indicador limitante.
No exemplo acima, o indicador de volume máximo é de 16 metros cúbicos. m em vez de 32, porque tal restrição contraria os indicadores para o número mínimo de lugares, uma vez que corresponderá ao número de 16,5 metros cúbicos. m.
Conclusão
Com base nisso, a opção “Pesquisar uma solução” no Excel ajudará na resolução de problemas específicos que são bastante difíceis ou impossíveis de resolver da maneira usual. A dificuldade em aplicar este método é que inicialmente esta opção está oculta, razão pela qual a maioria dos usuários não está ciente de sua presença. Além disso, a função é bastante difícil de aprender e usar, mas com a devida pesquisa, trará grandes benefícios e facilitará os cálculos.