Solver e VBA no Excel 2010

Números Aleatórios (Estatística), Aleatórioentre, aleatório, arred
Números Aleatórios (Estatística), Aleatórioentre, aleatório, arred
23 de abril de 2011
Criar pastas automaticamente com VBA
Criar pastas automaticamente com VBA
27 de abril de 2011

Solver e VBA no Excel 2010

Objetivo: Demonstrar com um exemplo simples como automatizar a aplicação do Solver utilizando VBA.

 

//

Lomadee, uma nova espécie na web. A maior plataforma de afiliados da América Latina.

 

Solver é uma poderosa ferramenta de análise, utilizada para a otimização e simulação de modelos de negócios e engenharia.Pode ser ainda mais poderosa, se usado em conjunto com VBA, para automatizar a solução de vários modelos que utilizam parâmetros de entrada diferentes e restrições.

Este suplemento do Excel serve para a solução de problemas lineares, simples não-lineares e complexos.

No exemplo que faremos neste artigo nós resolveremos o seguinte problema:

Em uma lista de valores com casas decimais precisamos encontrar quais que somados chegam a um determinado valor.

Para isso:

  • Ativar o suplemento Solver, clique em Arquivo->Opções->Suplementos e selecione o Suplemento Solver na tela que segue.

  • Crie uma lista na coluna A com valores aleatórios utilizando a fórmula: =ARRED(ALEATÓRIOENTRE(1000;100000)+ALEATÓRIO();2), esta lista não deve ter tantos números, mais ou menos uns 15.
  • Copie e cole somente valores estes números nesta coluna, desta forma teremos uma lista de valores fixos para trabalharmos.
  • Some alguns destes valores aleatoriamente e salve na célula E1, este será nosso objetivo.
  • Na célula B1 digite o valor 1, e na célula C1 digite a fórmula =A1*B1.
  • Na célula E2 digite =SOMA(C1:C38).
  • Teremos a seguinte planilha:
  • Agora já temos a planilha preparada para aplicarmos o Solver e conseguirmos o nosso objetivo.

Automatizando o Solver

  • Clique na guia Desenvolvedor, clique em Gravar Macro e configure a tela conforme abaixo:

  • Clique na guia Dados e no botão Solver, ele ficará no canto direito superior da tela.

  • No campo Set Objective selecione a célula $E$2, esta fórmula é a que irá ser alterada conforme os parâmetros passados.
  • No campo Value Of digite o valor da célula E1, você não conseguirá digitar.
  • No campo By Changing Variable Cells selecione o intervalo: $B$1:$B$19.
  • Clique no botão Add e na tela que segue você deve selecionar a Cell Reference a célula $B$1 no campo que segue selecione Bin que no caso significa que este campo apenas aceitará valores binários (1 ou 0).

  • Clique no botão Solve e ao mostrar a mensagem clique em OK.
  • Selecione a guia Desenvolvedor e clique em Parar Gravação.
  • Clique no botão Visual Basic e selecione o módulo 1 que foi criado pela gravação da macro.
  • Aqui você já terá a macro gravada para gerar a mesma solução automaticamente, mas vamos imaginar que a lista de valores muda, desta forma você terá que alterar a macro com algumas adaptações.
  • O suplemento Solver precisa ser ativado também para o VBA, então a primeira coisa que você deve fazer é ativar esta referência.
  • Para isso selecione no VBA o menu Ferramentas->Referências e na lista selecione Solver.

        • SolverOk SetCell:=”$E$2″, MaxMinVal:=3, ValueOf:=Range(“E1″).Value, ByChange:=”$B$1:$B$” & iTotalLinhas _

       

Agora já fiz as devidas alterações no código para que tudo que fizemos e gravamos na Gravação de Macros seja adaptado para qualquer quantidade de números que se tenha na lista de valores.

Sub lsAutoSolver()
    Dim i               As Long
    Dim iTotalLinhas    As Long

    SolverReset

    iTotalLinhas = Cells(Rows.Count, 1).End(xlUp).Row

    Range("B1").Select
    Selection.Copy
    Range("B2:B" & iTotalLinhas).Select
    ActiveSheet.Paste
    Range("C1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C2:C" & iTotalLinhas).Select
    ActiveSheet.Paste
    Range("E2").Select

    SolverOk SetCell:="$E$2", MaxMinVal:=3, ValueOf:=Range("E1").Value, ByChange:="$B$1:$B$" & iTotalLinhas _
        , Engine:=1, EngineDesc:="GRG Nonlinear"

    While i <= iTotalLinhas
        SolverAdd CellRef:="$B$" & i, Relation:=5, FormulaText:="binary"
        i = i + 1
    Wend

    SolverSolve
End Sub

Sobre os comandos do Solver:

SolverReset: Limpa todas as configurações do Solver

SolverOk: Cria um novo cálculo do Solver, passando as configurações como os campos de fórmula, o tipo de cálculo e a lista de células variáveis.

SolverAdd: Adiciona restrições para o cálculo do Solver.

SolverSolve: Efetua o cálculo do Solver. Variação SolverSolve True, desta forma não mostrará a tela, apenas serão alteradas as variáveis.

Você pode fazer o download da planilha com o código fonte e o exemplo completo do Solver no botão abaixo.

Os cálculos para este exemplo podem demorar bastante, dado a complexidade do cálculo, sugiro até você tentar encontrar sozinho os números que somados chegam ao valor.

Para o cálculo feito pelo Solver o tempo irá variar conforme a capacidade do processador do seu computador.

Também pode ver mais sobre o solver em: http://guiadoexcel.com.br/como-utilizar-o-solver-3

http://www.linhadecodigo.com.br/artigo/803/Tudo-o-que-voc%C3%AA-precisa-saber-sobre-o-Solver-no-Excel.aspx

 

 

Abraço
Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/

5 Comentários

  1. Wellington disse:

    O Solver é uma das melhores ferramentas do Excel. Especial pra quem precisa resolver pepinos rsr

  2. Marcelo disse:

    Exatamente o que precisava!
    Sozinho eu nunca iria adivinhar que teríamos que ativar o suplemento dentro do VBA também!
    Valeu Marcos!

  3. Daniela disse:

    Olá tentei fazer o teste mais a macro da erro em:

    Engine:=1, EngineDesc:=”GRG Nonlinear”

    Ele informa que o objeto não foi encontrado, como posso solucionar?

  4. Maria Eduarda disse:

    Eu sou espantado, Tеnho que admitir. Raramente еu fazer
    deparar um blog é tanto iguaⅼmente educativa e envolvente ,
    e deixe-mе dizer , tem bateu o prego na cabeçɑ. O problema é algo que muito poucos pessoal é falando inteligentemente ѕobre.
    Agora еu sou muito feliz І encontrado ist᧐ durante
    meu caçar alɡo respeito deste .

  5. Pedro Elias Barbosa disse:

    Muito bom! Estive muito por muito tempo procurando uma forma de solucionar o meu problema, e você fez exatamente o que eu precisava. Excelente tutorial, muito obrigado.

Deixe uma resposta

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

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

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.