Solver e VBA no Excel – Automatizar a solução de problemas

Soma contar média pela cor no Excel capa
Contar, Somar e Média pela cor da célula no Excel
9 de novembro de 2017
Como calcular combinação no Excel – Função Combin
20 de novembro de 2017

Solver e VBA no Excel – Automatizar a solução de problemas

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:

Solver e VBA Excel

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:

Solver e VBA Excel 2

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.

Solver e VBA Excel 3

  • 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:

Solver e VBA Excel 4

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.

Solver e VBA Excel 6

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:

Solver VBA Excel

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 🙂

Download “Solver VBA e Excel - Automação com VBA e Solver no Excel” Solver-com-VBA-Excel.zip – Baixado 92 vezes – 50 KB

Abraço

Marcos Rieper

1 Comentário

  1. ANDREY SARTORI disse:

    Show de bola como sempre…Parabéns!!

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.