Como automatizar o Excel Solver

Em Como obter mais do Excel Solver, abordei técnicas para aprimorar seu modelo e capacitar sua análise. Um dos tópicos sobre os quais recebo mais perguntas é a automação do solucionador para resolver vários valores.

Houve uma breve explicação no artigo anterior, mas nada dedicado a como implementá-la do início ao fim. Neste artigo, iremos em profundidade do início ao fim, cobrindo os conceitos e métodos passo a passo. No final, você será capaz de automatizar qualquer um de seus próprios modelos pessoais e dimensionar sua análise.

Como faremos isso

Usaremos o gravador de macro no Excel para lidar com a maior parte do código real. A ferramenta de gravação de macro permitirá que o Excel converta nossas etapas manuais em código VBA.

Em seguida, identificaremos as peças-chave do código a serem alteradas. Em seguida, criaremos um loop para selecionar e substituir dinamicamente esses valores e executar o solucionador novamente.

Isso será semitécnico, mas por meio desse estilo de desenvolvimento podemos nos concentrar no processo e nos conceitos, em vez de nos concentrar em como escrever código.

Configurando o gravador de macro

A funcionalidade de gravação de macro é encontrada na guia do desenvolvedor na faixa de opções. Isso não está disponível por padrão, mas é fácil de implementar. Se você navegar até as opções da faixa de opções, verá uma caixa de seleção para a guia do desenvolvedor.

A imagem abaixo é para Mac, mas o Excel baseado em PC é semelhante.

Se você não instalou o complemento do solver, pode instalá-lo por meio do menu Suplementos. O site de suporte da Microsoft tem instruções para todas as plataformas, Carregar o suplemento Solver no Excel.

Gravando a macro

Agora que a configuração está concluída, estamos prontos para gravar! Configure seu espaço de trabalho apropriadamente com todas as suas fórmulas e referências. Você vai querer configurar tudo até o ponto antes de construir o modelo do solucionador.

Volte para a guia do desenvolvedor na faixa de opções. Você verá um pedaço de papel com um ponto vermelho. Clique nesse botão para abrir o gravador.

Dê um nome à sua macro e preencha a descrição, se desejar.

Assim que você clicar em Ok, o gravador começará.

Siga as etapas de configuração do solucionador e resolva-o. Quando terminar, volte para a guia do desenvolvedor e pare de gravar. Será o mesmo local que o botão que você usou para iniciar a gravação.

Agora que temos o código base, podemos limpá-lo e prepará-lo para as próximas etapas.

Limpe o código

Agora podemos editar o código, remover todas as partes desnecessárias e ter certeza de que estamos usando apenas as partes essenciais da gravação da macro.

Você pode editar o código por meio do editor VBA do Excel. Para acessar o código gravado, clique no botão Macros.

Navegue até sua macro no novo menu e clique em editar.

Agora que temos nosso editor de código aberto, podemos começar a fazer ajustes.

Removendo código desnecessário

Se por acaso você clicar em um monte de outras coisas durante a gravação, você pode limpá-las agora e ficar com algo semelhante a seguir. Se houver muito código extra e você estiver preocupado em quebrá-lo, poderá regravar a qualquer momento.

Configurando as referências do solver

Uma última etapa antes de prosseguirmos, vá para Ferramentas> Referências e selecione Solver. Se você não fizer isso, o pacote não será carregado na macro e você receberá um erro.

Agora você pode alterar seus parâmetros, abrir o menu de macros, selecionar sua macro, clicar em executar e os resultados serão atualizados.

Vamos alinhar o código ao nosso modelo de solucionador. Você verá como ele se alinha. A linguagem é diferente, mas você pode entender o que tudo isso significa.

Em seguida, isolaremos as partes que precisamos alterar e encerrar nossa automação.

Enxágüe e repita - construindo o loop

Agora que temos um solucionador funcionando. Precisamos repetir isso várias vezes. Vamos colocar um exemplo e consolidar o plano.

Começamos com um orçamento de $ 1.000 no primeiro modelo, mas queremos fazer a mesma coisa para incrementos de $ 100 até $ 2.500.

Vamos mergulhar! Temos uma lista de valores na coluna B e nossa saída na coluna C. Se escrevêssemos o processo, ficaria

1. Defina o gasto total como o valor em B10.
2. Resolva o modelo.
3. Coloque a saída em C10.
4. Escolha o próximo valor B.
5. Defina a saída na linha C.
6. Repita as etapas 2 a 5 para todos os valores em B.

Iniciando o loop

Os loops são construções de codificação que repetem uma ação até atingir um ponto final lógico. Um loop será útil aqui para repetir o mesmo processo, executando o solucionador, em cada valor até chegarmos ao final do valor.

Para este exemplo, definiremos o ponto final pelo número de linhas. B10: B25 tem 16 valores.

Vamos iniciar nosso loop e envolvê-lo em nosso código do solver. Vamos configurar uma variável para conter o número do loop (i) e passar por cada valor, resolvendo o loop a cada vez.

Reiniciando o solucionador

Adicionaremos mais uma condição no início do nosso código. Como queremos executar novamente o solucionador a cada iteração, vamos redefini-lo a cada loop. Isso limpará as configurações e iniciará um novo modelo.

Podemos fazer isso por meio do SolverReset .

Atualizando referências de células

Agora resolvemos dezesseis vezes, mas precisamos atualizar nossas referências de células. Embora tenhamos feito referência a B10 originalmente, precisamos mover uma célula para baixo a cada execução para atualizar os limites. Podemos fazer isso por meio da função de deslocamento. Podemos atualizar “$ B $ 10” para fazer referência à célula e então deslocar por i linhas (0 na primeira execução, 1 linha na segunda e assim por diante). Cada vez que o loop é executado, i aumenta em um.

Coletando a saída

A saída mudará a cada vez, então vamos querer salvá-la. Podemos seguir o mesmo processo acima, mas deslocar de C10 e defini-lo igual ao valor do modelo resolvido. Em seguida, o loop recomeça.

Chamaremos C10 de deslocamento pelo número apropriado de linhas e, em seguida, colar o valor de nossa saída.

Evitando as notificações do solver

Vamos nos poupar de uma dor de cabeça e não aprender por um momento por exemplo. Lembra quando você executou o solucionador e aquele menu apareceu perguntando se você gostaria de aceitar a solução? Isso vai aparecer toda vez se você não desativá-lo. Imagine se você configurar um loop com 100 valores, terá que clicar em aceitar todas as vezes!

Vá em frente e adicione as seguintes linhas após o código do solver e adicione (TRUE) ao SolverSolve. Isso irá imitar você aceitando as alterações.

Agora temos o código concluído!

Funcionando

Agora podemos executar o código selecionando-o na lista de macros e executando. Se quiser usar sua pasta de trabalho como modelo, você pode atribuir a macro a um botão para facilitar o uso. Você pode encontrá-los na guia do desenvolvedor.

Você também pode adicionar qualquer gráfico de comparação. Nesse caso, queremos comparar a taxa de crescimento dos gastos com o crescimento do volume de conversão. Este exemplo não é interessante, mas dados reais podem ajudar a revelar pontos de interrupção de eficiência

Conclusão

Nós cobrimos um pouco! Parabéns por colocar seu solucionador automatizado em funcionamento. O exemplo que cobrimos era excessivamente simplista, mas você pode pegar os mesmos conceitos e aplicá-los a qualquer modelo de solucionador.

Isso pode economizar muito tempo para modelos que você precisa resolver várias vezes e incentivar os usuários a experimentar diferentes cenários.

Data de publicação original: 30/07/2019