Como Utilizar o Solver Excel – Álgebra linear no Excel
Hoje vou dar uma explicação sobre a funcionalidade e a utilização do Solver.
O Solver ajuda na solução de problemas: Simples não lineares, problemas lineares e programas complexos. Neste post veremos a solução de um problema linear.
Quem quiser ter uma explicação melhor sobre programação linear pode recorrer ao wikipedia neste link, neste artigo nos ateremos ao uso do Excel para soluções destes problemas.
Definir o problema:
- Defina um problema, de preferência o escreva para ficar claro:
- Exemplo: Uma empresa que produz três produtos, com parâmetros e restrições diferentes, assim como valores de vendas diferentes. Qual deve ser a quantidade produzida de cada item para que o aproveitamento seja ótimo assim como o faturamento, sendo que deve ser produzido no mínimo uma tonelada de cada produto?
- Sendo este o problema, temos que definir campos para as quantidades, e preços de venda para cada produto, como na imagem:
- Os campos da coluna Qtde. serão alterados conforme a solução do problema a fim de otimizar a produção e chegar no máximo Faturamento possível.
Criar Restrições:
- No nosso caso temos um problema de indústria, que como qualquer empresa, tem restrições quanto a tempo de linha de produção, devemos elencar estas restrições para o problema, conforme a tabela abaixo:
- O resultado da coluna Total deve ser sempre menor ou igual a coluna Limite que é o máximo de tempo que aquela linha possui.
Criação da equação:
- Faltou ainda criar uma equação que identifique qual é o resultado do faturamento, neste caso uma equação muito simples, que é a soma da multiplicação do valor de cada produto pela sua quantidade, como na figura.
- Pronto, agora temos a planilha base para a aplicação do Solver.
Solver:
- O Solver é um suplemento do Excel que apesar de já estar instalado, estará desabilitado, para ativá-lo clique no ícone do Excel->Opções->Suplementos e no botão Ir.
- Marque a opção Solver nesta lista e ele ficará disponível.
- Com o Solver habilitado clique na aba Dados e no grupo Análise clique no botão Solver.
- Na tela que segue faça:
- No campo Definir Objetivo selecione a equação.
- No campo Alterando Células Variáveis selecione os campos da coluna Qtde. que serão alterados pelo Solver para solucionar o problema.
- No campo Sujeito às Restrições clique no botão Adicionar que fica ao lado e insira as restrições:
- Campo Qtde., célula B8>1
- Campo Qtde., célula B9>1
- Campo Qtde., célula B10>1
- Campo Qtde., célula B8 int
- Campo Qtde., célula B9 int
- Campo Qtde., célula B10 int
- Campo Total <= Limite G13<=F13
- Campo Total <= Limite G14<=F14
- Campo Total <= Limite G15<=F15
- Altere o método da solução para LP Simplex, apesar de eu ter deixado como estava e ter funcionado igual
- Clique no botão Resolver
- O Solver irá mostrar uma tela aonde é possível manter a solução do Solver, restaurar os valores originais, criar relatórios e salvar o cenário. A tela abaixo é do Excel 2010.
- Clique em Ok, e note que o Solver alterou os campos referentes a quantidade produzida de forma a otimizar o faturamento em detrimento das restrições de produção.
Muito obrigado pela leitura do post, e aguardo vocês na leitura do próximo.
Baixe a planilha