Solver no Excel

O Solver é um poderoso comando do Excel que permite resolver problemas com teste de hipóteses. É utilizado principalmente para análise de sensibilidade com mais de uma variável e com parâmetros de restrições. Através do recurso Solver é possível fazer inúmeras simulações na sua planilha, e automatizar seus cálculos na hora de tomar decisões.

O Solver é um suplemento do Excel que apesar de já estar instalado, estará desabilitado.

Quando utilizar Solver no Excel?

Quando temos um problema com múltiplas variáveis, limites e restrições, o Atingir Meta não será suficiente, pois possui limites de parâmetros para simulação. Para isso, devemos utilizar o recurso Solver.

Como ativar (instalar) o recurso Solver no Excel?

Para ativar o solver, é bem simples!

Para habilita-lo:

1. Clique no Menu Arquivos.
   
2. Clique em Opções.
   
3. Escolha a opção Suplementos.
   

   
   

4. Na opção Gerenciar: Suplementos do Excel, clique no botão Ir.
   
5. Na janela Suplementos, marque à opção Solver
   
6. Clique no botão OK
   

   
   

Resolvendo problemas com Solver no Excel!

Agora que já ativamos o Solver, utilizaremos um exemplo bem simples para melhor entendermos o objetivo dessa ferramenta.

Imagine o cenário de uma microempreendedora chamada Lúcia, ela trabalha no ramo de vestuário masculino, e baseada em seu faturamento dos últimos seis meses, dispõe da quantia de R$ 15.000,00 para investir no próximo semestre de vendas.

Na planilha acima temos as quantidades que podem ser compradas, os itens, o valor por item, a margem de vendas, o valor das compras e o total em vendas.

Com base nisso, vamos ajudar Lúcia a analisar qual a melhor forma de investir esse capital, para que ela aproveite da melhor maneira possível o lucro obtido no último semestre.

O comando Solver nos permite realizar as simulações e alterações automaticamente, a partir do momento em que definimos o valor total a ser gasto.

Definindo o Solver no Excel

No solver você trabalhará basicamente com os seguintes conjuntos de dados: Definir objetivo, Valor de, Max ou Min, Alterando células variáveis e restrições.

1 – Definir objetivo: digite uma referência de célula ou um nome para a célula de objetivo, a qual deve conter uma fórmula.

2 – Valor de: Selecione essa opção se você deseja que a célula de objetivo tenha um determinado valor; para isso digite o valor desejado dentro da caixa

3 – Max: Selecione essa opção se você deseja que o valor da célula de objetivo seja o maior
possível.

4 – Min: Selecione essa opção se você deseja que o valor da célula de objetivo seja o menor
possível.

5 – Alterando células variáveis:  insira um nome ou a referência para cada intervalo de células variáveis de decisão. Separe as referências não adjacentes com vírgulas. As células variáveis devem estar relacionadas direta ou indiretamente à célula de objetivo. Você pode especificar até 200 células variáveis. :

Fonte: Microsoft

Em nosso exemplo, procederemos da seguinte maneira:

Para obter grande parte dos dados em nossa planilha usaremos valores estáticos e fórmulas básicas do Excel:

De F2 até F6 calcularemos o valor total de compras aplicando a seguinte fórmula: =E2*B2 (digite a fórmula na célula F2, depois clique e arraste até a célula F6)

De H2 até H6 calcularemos o valor estimado para a venda de cada produto em função de sua margem de vendas, aplicando a seguinte fórmula: =(E2*B2)+( E2*B2)*G2 (digite a fórmula na célula H2, depois clique e arraste até a célula H6)

A célula F8 armazenará o total de compras que será dado por: =SOMA(F2:F6)

F9: digitaremos manualmente o valor disponível para investir que é de R$ 15.000,00

Assim como os dados das colunas A, B, C e D que
serão inseridos manualmente

F10: o saldo que restará do valor investido será dado pela fórmula: =F9-F8

F1: A estimativa do total vendido será dado por: =SOMA(H2:H6)

F12: o lucro será dado pelo total vendido menos o total de compras: =F11-F8

Solver na prática

Com a tabela de dados que você vai usar aberta no Excel, o suplemento Solver habilitado, e as informações acima bem definidas, vá a guia DADOS no grupo ANÁLISE clique sobre SOLVER

Será aberta a seguinte caixa de diálogo para definição dos parâmetros de análise do Solver:

Parâmetros do Solver no Excel

Na caixa Definir Objetivo, digite
ou selecione a célula F12 que contém a informação do Lucro obtido;

Como queremos obter o máximo de lucro possível, para que o dinheiro seja bem investido, selecione a opção Máx. em próximos exemplos, vamos explorar as opções Min. e Valor de:

Feito isso, só precisamos agora determinar as restrições do nosso problema, que são apenas três.

Analisando a situação de Lúcia percebemos as seguintes restrições:

Primeira: ela não pode comprar menos do que a quantidade mínima de estoque estabelecida, que em nosso exemplo é 15. Logo precisamos adicionar essa restrição da seguinte maneira:

Em Sujeito às restrições clique no botão Adicionar na caixa Adicionar Restrição

Insira: E2:E6 >= C2:C6 que significa dizer que a quantidade comprada deve ser maior ou igual, a quantidade mínima de estoque.

Para a segunda condição, que é: a quantidade comprada deve ser menor ou igual a quantidade máxima permitida para o estoque, em nosso exemplo 30 unidades. Repita o procedimento acima trocando apenas a condição, que agora será: E2:E6 <= D2:D6

E na terceira e última condição, precisamos determinar que o Saldo, valor armazenado pela célula F10 deve ser
maior ou igual a zero, pois ela não pode gastar mais do que tem disponível para investir. Última condição: F10 >= 0

Por último selecione um modelo de solução do Solver, nesse exemplo utilizaremos o GRG não Linear

Nota: O Solver possui três algoritmos ou métodos de solução na caixa de diálogo Parâmetros do Solver:

Gradação Reduzida Generalizada (GRG) Não Linear: Use para problemas simples não lineares.

LP Simplex: Use para problemas lineares.

Evolucionário: Use para problemas complexos.

Depois de definir os parâmetros necessários, clique em RESOLVER

Será exibida a tela RESULTADO DO SOLVER

Esta tela oferece as opções: Manter solução do Solver e Restaurar Valores Originais, como queremos analisar os resultados que o solver oferece para o nosso problema, mantenha a primeira opção selecionada e clique em OK.

Pronto, o resultado obtido a partir dos cálculos realizados pelo solver.

Nosso próximo exemplo, também será bem simples!

Pedrinho foi a uma festa com um grupo de oito pessoas, depois de determinado tempo o grupo se reuniu, e juntou uma certa quantia para lanchar. O valor reunido foi de R$ 70,00, e foi entregue a Pedrinho, que ficou responsável por comprar os itens determinados pelo grupo.

Na planilha acima pode-se observar o cardápio disponível, as quantidades solicitadas, e os valores unitário e total respectivamente.

O que o grupo não percebeu é que juntaram menos dinheiro que o necessário, para cumprir todas as exigências. Como Pedrinho não quer dar viagem perdida, resolveu comprar os lanches mesmo assim, por isso, vamos ajuda-lo com a ferramenta Solver, para que ele descubra a melhor maneira de redistribuir o valor disponível.

Para isso, ele estipulou algumas condições:

• As quantidades de bebida seriam mantidas, adicionaremos essa restrição da seguinte maneira: B13 = 3 e B14 = 5
  
• Com exceção aos doces, deveriam ter uma ou mais unidades dos outros lanches, pois dessa maneira cada pessoa comeria pelo menos um lanche. Logo B2:B9 >= 1
  
• A coluna quantidade (Qtde) só deve conter números inteiros, pois ninguém vende um pastel e meio. Logo B2:B14 >= números inteiros
  

De D2 até D14 calcularemos o valor total aplicando a seguinte fórmula: =B2*C2 (digite a fórmula na célula D2, depois clique e arraste até a célula D14)

Como utilizar o Solver no Excel?

Com o suplemento Solver já habilitado, vá a guia Dados, grupo Análise e clique sobre Solver

Agora que já vimos a definição dos parâmetros do Solver, conseguimos facilmente entender o que foi feito.

Estipulamos no campo Valor de a quantia máxima que poderia ser gasta, no nosso caso R$ 70,00.

Adicionamos as células da coluna quantidade (B2:B14) no campo Alterando células variáveis;

Inserimos as restrições necessárias e clicamos em Resolver depois clicamos em Manter solução do Solver

Acima a tabela com resultado proposto pelos cálculos do solver.

É isso pessoal! Então tem alguma dúvida relacionada ao Excel? Algum recurso que você gostaria de aprender através do nosso blog?

Veja também: Como utilizar Atingir Meta no Excel

Deixe seu comentário, com certeza vamos fazer o máximo para atende-lo!