Neste artigo você verá como usar o Solver e VBA no Excel para automatizar a solução de problemas.
O Solver é um suplemento do Excel de grande poder, ele serve para resolver problemas como:
- Maximizar resultados, como o lucro de uma indústria
- Minimizar valores, por exemplo a quantidade mínima de pessoas para atender uma escala de trabalho
- Atingir valores, como é o caso do nosso exemplo, em que iremos demonstrar como encontrar a solução para problemas combinatórios
Mas há situações em que nem mesmo o Solver sozinho não consegue resolver. Como no caso que iremos apresentar.
Pense na seguinte situação, a sua empresa possui uma lista de cupons fiscais, alguns deles estão cancelados, e você só tem o valor total do cancelamento. Descubra quais em conjunto dão estes valores.
Para se ter uma ideia da dificuldade, veja esta lista com 10 itens de uma empresa:
O objetivo é encontrar os valores que somados atingem o objetivo de R$ 233.798,93, e para localizar estes valores manualmente, poderiam haver até, 1.023 tentativas, ou seja, 1023 combinações diferentes e esta quantidade pode ser muito maior, dependendo da quantidade de números que existem para a combinação.
Para esta solução utilizamos o Solver, aonde criamos a seguinte estrutura:
Temos então:
- Loja: Apenas o número da loja;
- Valor: O valor de cada cupom fiscal;
- Teste: 1 ou 0 é ele que define se o número será usado no cálculo ou não;
- Valor calculado: Multiplicação do campo Valor pelo campo Teste;
- Objetivo: Valor total que se deseja atingir;
- Fórmula: Soma simples do campo Valor Calculado.
IMPORTANTE: Se você não está familiarizado com o Solver, veja este artigo: Como utilizar o Solver Excel – Álgebra linear no Excel, pois neste artigo não iremos detalhar o seu funcionamento do início.
Veja a solução no Solver como ficou, lembrando que você pode realizar o download da planilha ao final e também pode ver na íntegra como criar esta solução no vídeo neste mesmo artigo. Ps.: Aproveite para assinar o nosso canal, sempre temos novidades.
- Vermelho: Célula objetivo, o valor que queremos atingir.
- Azul: A fórmula que realiza a soma do valor calculado, apresentará o resultado dos cálculos.
- Verde: Variáveis que serão manipuladas e também as restrições de que estes números serão binários, ou seja, 1 ou 0.
Após a execução destes cálculos, que leva cerca de 4 segundos o Excel apresenta os valores que somados atingem o resultado desejado:
Veja que apenas estes valore são somados para que o total seja atingido.
Solução para automatizar Solver com VBA no Excel
Agora, pense fazer esta mesma solução para centenas de equipamentos que tiveram o mesmo problema ao gravar os dados no banco. O que fazer?
No nosso caso, que fizemos com apenas 10 lojas, mas que poderia ser para quantas quisesse, fizemos uma lista com a meta por cada uma das filiais e os valores dos cupons para cada uma.
Acima, nós temos então ao lado esquerdo a lista de cupons fiscais com a mesma estrutura que demonstramos anteriormente, e á direita o valor meta de cada uma das filiais, um somase que realiza a soma por loja na tabela de cupons fiscais e na coluna situação, temos uma fórmula SE que identifica se o valor da meta é semelhante ao valor encontrado.
Então foi criado um código VBA que realiza um loop para colocar todos estes valores na lista, loja por loja, fazer os cálculos e depois retornar os mesmos para esta planilha base, demonstrando assim os resultados. Veja o código VBA abaixo:
Sub lsAutoSolver()
Dim iTotalLinhas As Long
'Seta a planilha
Calculos.Select
'Limpar configurações do Solver
SolverReset
'Verificar a quantidade de linhas
iTotalLinhas = Cells(Rows.Count, 1).End(xlUp).Row
'Incluir as regras de cálculo
SolverOk SetCell:="$G$8", MaxMinVal:=3, ValueOf:=Range("G7").Value, ByChange:="$C$8:$C$" & iTotalLinhas _
, Engine:=1, EngineDesc:="GRG Nonlinear"
'Incluir restrições
SolverAdd CellRef:="$C$8:$C$" & iTotalLinhas, Relation:=5, FormulaText:="binary"
'Realizar os cálculos
SolverSolve True
End Sub
Sub lsCalcular()
Dim lContadorMeta As Integer
Dim lTotalMeta As Long
Dim lLinhaInicial As Long
Dim lUltimaLinha As Long
Dim lTotalBase As Long
Dim lTotalCalculos As Long
Base.Select
lTotalMeta = Cells(Rows.Count, 6).End(xlUp).Row
lTotalBase = Cells(Rows.Count, 1).End(xlUp).Row
lLinhaInicial = 1
For lContadorMeta = 2 To lTotalMeta
lLinhaInicial = Range("A" & lLinhaInicial & ":A" & lTotalBase).Find(Range("F" & lContadorMeta).Value).Row
lUltimaLinha = lLinhaInicial + Application.CountIf(Range("A:A"), Range("F" & lContadorMeta).Value) - 1
Calculos.Range("A8:B1048576").Clear
Base.Range("A" & lLinhaInicial & ":B" & lUltimaLinha).Copy Destination:=Calculos.Range("A8")
Base.Range("G" & lContadorMeta).Copy Destination:=Calculos.Range("G7")
lsAutoSolver
lTotalCalculos = 8 + Application.CountIf(Range("A:A"), Base.Range("F" & lContadorMeta).Value) - 1
Base.Select
Calculos.Range("C8:D" & lTotalCalculos).Copy Destination:=Base.Range("C" & lLinhaInicial)
Next lContadorMeta
MsgBox "Cálculos concluídos!"
End Sub
O procedimento lsCalcular realiza o loop mencionado e o procedimento lsAutoSolver aplica o Solver sobre os valores, usando os mesmos parâmetros e restrições que definimos no nosso problema no início. Veja o funcionamento dele:
Download da Planilha Gratuita
Faça o download clicando do exemplo clicando no botão abaixo, e não se esqueça de se inscrever nas nossas redes sociais e no nosso canal do Youtube 🙂
