Você já se deparou com um problema em que queira otimizar algo, que existiam algumas restrições a serem consideradas? 

Já quis aumentar sua capacidade produtiva, sem investir mais, utilizando apenas uma realocação dos seus ativos?

Talvez já tenha passado por uma situação em que você gostaria de minimizar suas despesas, gastos e custos mantendo alguns aspectos específicos, mas não sabia como fazer isso?

A boa notícia é que o MS Excel irá nos auxiliar. Existe uma forma de otimizar utilizando a função solver. Mas e aí, você sabe o que é essa função?

 

Assista gratuitamente nossa vídeo-aula relacionada

 

 

O que é solver?

 

O Solver é um software para programação matemática integrada à planilha eletrônica que resolve problemas de programação linear. Ele é um suplemento do Excel que você pode usar para teste de hipóteses, por exemplo.

É uma ferramenta complexa e poderosa do Excel que nos permite fazer vários tipos de simulações, sendo utilizada especialmente para análise de sensibilidade com mais de uma variável e com restrições de parâmetros.

Ou seja, o Solver é uma ferramenta que lhe permite resolver problemas de pequeno e médio porte, visando chegar a uma otimização no resultado.

Em outras palavras, você pode usar o solver para determinar o valor máximo ou mínimo de uma célula, com base em alguns parâmetros.

 

Como o solver funciona?

 

O solver trabalha com um grupo de células relacionadas direta ou indiretamente com a fórmula na célula de destino.

Todas as células que influenciam no resultado da célula destino poderão ser alteradas pelo próprio Excel, desde que sejam fórmulas inter-relacionadas e atinjam a meta desejada, avaliando todas as restrições e atingindo o resultado mais otimizado possível.

Este recurso auxilia a resolver problemas de modelagem matemática. Desta forma, o solver é composto de três elementos principais:

  • Variáveis de decisão: São as incógnitas a serem determinadas pela solução do problema.
  • Restrições: Limitam as variáveis de decisão a certos valores possíveis.
  • Função-Objetivo: É a função a ser maximizada ou minimizada, a qual depende dos valores das variáveis de decisão.

A utilização do Solver é simples. A grande questão se deve à correta modelagem e interpretação do problema. Por esse motivo, resolvi expor um exemplo bem detalhado da modelagem utilizada para resolução de um problema.

 

Exemplo

 

Com o intuito de economizar, um produtor rural deseja calcular a quantidade de cada tipo de grão, que traria um menor custo, sem prejudicar as quantidades mínimas de cada nutriente de que seu gado necessita.

Para isso, ele montou a tabela a seguir com a quantidade de nutrientes que cada kg de grão contém, com suas respectivas necessidades mínimas e preço/kg.

 

 

Vamos ajudá-lo otimizando o processo de forma a obter o menor custo e atender a todas as restrições de necessidades mínimas.

 

Formulação matemática

 

Para realizar a formulação matemática, é necessária uma boa compreensão do problema apresentado, para que assim se identifique quais são as variáveis de decisão, qual é a função objetivo e quais as restrições do problema. Tudo isso servirá de input para a função solver.

Variáveis de decisão: Denominei como X1, X2 e X3 a quantidade de grãos da ração 1, 2 e 3, respectivamente. Essas variáveis serão a resposta do nosso problema, ou seja, será a quantidade que deve ser utilizada de cada grão.

Montei também uma tabela para ficar ainda mais claro, e essa tabela será iniciada com valores zerados. E assim que utilizarmos a função solver, esses valores serão alterados, obtendo assim nossa solução.

 

 

Restrições: Devemos escolher os grãos a serem misturados na ração de forma a atender as necessidades mínimas de nutrientes para o gado. Ou seja, temos restrições para essa mistura.

Essas restrições nada mais são do que a quantidade de cada grão multiplicado pela quantidade de nutriente que oferece. E isso tem que ser maior ou igual a necessidade mínima, veja na tabela abaixo:

 

 

Como na tabela anterior iniciamos com zero, os valores dessa tabela, por consequência também estarão zerados.

Existem inúmeras formas de se misturar os grãos para se atender a essas restrições, certo? Porém, não queremos qualquer combinação, queremos a combinação mais barata possível, e para isso que definimos a função-objetivo.

Função-Objetivo: Essa função representa o custo da mistura, ou seja, o preço de cada grão multiplicado pela quantidade usada na ração.

 

Ativando o solver

 

A primeira coisa a ser feita é ativar o suplemento solver. Para isso, no Excel vá em Opções > Suplementos > Gerenciar suplementos do Excel e clique em Ir, como representado na figura a seguir.

 

 

Depois disso, acione a opção Solver e pronto, está ativado.

 

 

Agora que o solver está ativado, está tudo pronto e você poderá aplicar. Vamos lá?

 

Utilizando o solver

 

Vá na parte superior da planilha, em Dados > Solver. Abrirá uma caixa de diálogo “Parâmetros do Solver”, no campo Definir Objetivo, escolha a célula na qual se inseriu a função-objetivo.

Como queremos minimizar o custo da mistura, marque a opção Mín.

Essa função, não serve apenas para minimizar funções, mas também para maximizar, nessa situação a opção Max seria utilizada para determinar o valor máximo de uma função, para obter o lucro de cada um dos produtos em uma determinada situação.

Retomando nosso exemplo, em Células Variáveis, como o próprio nome já diz, devemos selecionar as células que contém as nossas incógnitas (X1, X2, X3).

Clique em Adicionar para abrir uma caixa de diálogo para inserir as restrições.

Para preenchê-la, basta selecionar a célula em que se inseriu a equação de restrição de quantidade de nutriente A, por na opção maior ou igual (>=) e posteriormente selecionar o valor da restrição (necessidade mínima), como mostrado a seguir.

 

 

Dê OK e pronto, nossa primeira restrição já está setada. As outras restrições das necessidades mínimas de nutrientes, temos que os totais dos nutrientes B, C, D não podem ser inferiores a 250, 900 e 232,5, respectivamente.

Então, repita o passo anterior para os outros três nutrientes.

Feito tudo isso, sua caixa de diálogo “Parâmetros do Solver” deverá estar similar a da figura a seguir:

 

 

Ao clicar em resolver, o solver retornará uma mensagem avisando que encontrou uma solução, clique em ok e observe o resultado obtido.  

 

 

Interpretando o resultado

 

Observando a última imagem, temos, como situação ótima, 200 kg de grão do tipo 1, 50 kg de grão do tipo 2 e 100 kg de grão do tipo 3.

Nosso custo total da ração será de R$ 19.550,00 e pode-se verificar na coluna “Quantidade Total” que todas as quantidades mínimas de nutrientes foram obedecidas.

 

E aí, aprendeu tudo sobre o Solver?

 

Se chegou até aqui, você já sabe o que é solver, para que ele serve e como poderá utilizar o solver para resolver problemas de otimização no Excel, certo?

O conhecimento de Excel é, cada vez mais, essencial para qualquer profissional e pode abrir portas no mercado de trabalho.

Eu te convido a aprender ainda mais sobre essa incrível ferramenta que é o MS Excel no nosso curso de Formação Master em Excel.

Com ele você irá aprender sobre inúmeras outras funções do Excel, que podem te ajudar muito no seu dia a dia.

 

 

Não deixe de nos presentear com seu comentário. Conte para mim o que você achou desse post e me ajude a melhorar ainda mais nosso conteúdo. Seu feedback é importante para nós!