Excel Solver: Otimize decisões complexas com várias variáveis

A análise hipotética permite que você teste diferentes cenários para ver como eles influenciam seus resultados. Ele oferece um espaço seguro para você testar variáveis como custos ou capacidades e descobrir a solução mais eficaz. Essa abordagem é essencial para quem gerencia recursos, planeja cronogramas ou toma decisões estratégicas de negócios.

A tentativa e erro manual leva horas e muitas vezes não chega à resposta ideal. O Solver garante a otimização matemática em segundos. Essa é a diferença entre adivinhar a alocação de recursos e saber que você está tomando a melhor decisão possível.

Este tutorial te mostra como usar o Excel Solver, um recurso avançado feito pra resolver esses problemas de otimização. Vou te mostrar como configurar sua planilha, fazer a análise e interpretar os resultados usando exemplos reais.

Entendendo o Solucionador do Excel: Conceitos básicos

Antes de mostrar como usar o Solver, vou explicar os três pilares de todo problema de otimização. Não são só palavras da moda, são o que faz o Solver funcionar.

Célula objetiva, variáveis de decisão e restrições

Pense na célula objetiva como sua meta. É a fórmula que você quer aumentar ou diminuir, tipo o lucro total ou o custo total. Isso tem que ser uma fórmula, não só um número, porque o Solver precisa ver como ela muda quando você mexe nas coisas.

As variáveis de decisão são o que você está tentando descobrir. Quantas cadeiras devemos fazer? Quanto devemos gastar em cada canal de marketing? Eu sempre começo com zero, o que facilita ver o que mudou depois que o Solver é executado.

Restrições são seus limites e regras. Não dá pra gastar mais do que o seu orçamento. Não dá pra fazer quantidades negativas. Alguns projetos precisam de um investimento mínimo. Essas restrições transformam seu problema matemático em algo realista que reflete as limitações reais dos negócios.

Funciona assim: O Solver ajusta suas variáveis de decisão para otimizar seu objetivo, garantindo que todas as restrições sejam satisfeitas. Se você já fez pesquisa operacional, isso vai parecer familiar. Se não, pense nisso como uma tentativa inteligente com garantias.

Comparando com a busca de metas

As pessoas me perguntam sobre isso. A busca por metas é mais simples. Muda uma célula para atingir um alvo específico. Use isso quando você já sabe a resposta que quer e só precisa achar a entrada que te leva até lá.

O Solver é diferente. Ele encontra a melhor resposta quando você tem várias decisões a tomar e restrições a seguir. EuSolver é usado quandoquando você precisa maximizar ou minimizar algo enquanto lida com várias variáveis.

Minha regra geral: se você consegue dizer exatamente o número que quer, use a função Busca de Objetivo. Se você está se perguntando "qual é a melhor maneira de fazer isso?", use o Solver.

Carregando e ativando o complemento Solver

O Solver vem com o Excel, mas você precisa ativá-lo primeiro. Não se preocupe, leva menos de um minuto.

Passos de instalação para Windows e Mac

Para Windows:

1. Arquivo > Opções > Complementos
2. Selecione Complementos do Excel no menu suspenso Gerenciar e clique em Ir.
3. Verifique o complemento Solver complemento Solver, clique em OK
4. Vá para o Dados e localize Solucionador em Analisar .

Para Mac:

1. Ferramentas > Excel Complementos
2. Verifique o complemento Solver complemento Solver, clique em OK

Se você está usando o Excel Online, tenho más notícias. Não dá suporte ao Solver. Você vai precisar da versão para desktop (2016 ou mais recente).

Configuração

Depois de ativado, você acessa o Solver pela guia Dados > botão Solver. As configurações padrão funcionam bem para a maioria dos problemas, então não se preocupe muito com essa parte.

Dito isso, duas configurações são importantes se você tiver problemas. A precisão controla o quanto o Solver é rigoroso em satisfazer as restrições. Eu aperto isso para modelos financeiros em que cada centavo conta. Convergência diz ao Solver quando parar de tentar melhorar a solução. Eu reduzo isso se o Solver encerrar muito cedo com uma resposta que não é totalmente ideal.

Você pode encontrar ambos na caixa de diálogo Parâmetros do Solucionador > botão Opções, mas, na verdade, eu raramente mexo neles.

Definindo o seu problema: Formulação de modelos no Excel

Uma planilha organizada facilita a configuração do Solver.

Deixa eu te mostrar um exemplo: uma fábrica de móveis que faz cadeiras e tabelas. As cadeiras dão um lucro de $60 (precisa de 4 horas de trabalho e 2 unidades de madeira). As tabelas rendem um lucro de $75 (precisam de 6 horas de trabalho e 2 unidades de madeira). Temos 240 horas de trabalho e 100 unidades de madeira disponíveis. Quantos de cada um devemos fazer?

Definição do problema e configuração da planilha

Eu organizo todos os modelos do Solver da mesma maneira. Sempre quatro seções:

1. Dados de entrada (informações sobre o produto, limites de recursos)
2. Variáveis de decisão (quantidades a produzir)
3. Função objetivo (lucro total)
4. Cálculos de restrições (recursos usados vs. disponíveis)

Modelo Solver completo com todas as quatro seções. Imagem do autor.

Esse layout deixa tudo bem claro. Qualquer pessoa que olhar para o seu modelo pode ver imediatamente o que você está otimizando e quais são as limitações com as quais você está trabalhando.

Agora, eu sempre crio o que é conhecido como intervalos nomeados para células importantes. Em vez de escrever =B8*B3+B9*B4, posso escrever =SUMPRODUCT(QuantityToProduce,ProfitPerUnit). Muito mais fácil de entender seis meses depois, quando você já esqueceu o que essas referências de célula significam.

Os intervalos nomeados tornam as fórmulas mais fáceis de ler. Imagem do autor.

Para criá-los: selecione as células, clique na caixa Nome, digite um nome e pressione Enter. Leva só dois segundos e evita dores de cabeça depois.

Variáveis de decisão e função objetivo

Começo as variáveis de decisão em zero. Isso ajuda nas comparações. Algumas pessoas gostam de começar com valores estimados, o que é legal, mas zero funciona pra mim.

A função objetivo precisa ser uma fórmula. Para o nosso exemplo de móveis, eu uso:

=SUMPRODUCT(B8:B9,B3:B4)

Isso calcula: (Cadeiras × $60) + (Tabelas × $75)

Multiplicação e adição simples. É tudo o que você precisa para a maioria dos problemas empresariais.

Restrições

Restrições são onde você transforma limites reais dos negócios em matemática. Nosso fabricante de móveis tem duas limitações de recursos:

• Trabalho: =SUMPRODUCT(B8:B9,C3:C4) <= 240

• Madeira: =SUMPRODUCT(B8:B9,D3:D4) <= 100

E aqui está algo que aprendi da maneira mais difícil: sempre adicione restrições de não negatividade (B8:B9 >= 0). Sem eles, o Solver pode sugerir a criação de cadeiras negativas, o que obviamente não funciona no mundo real.

Também adiciono uma coluna de status com fórmulas d =IF(), como =IF(B16<=C16,"OK","EXCEEDED") . Isso me dá um feedback visual instantâneo antes mesmo de eu executar o Solver. Luzes verdes significam que está tudo certo, luzes vermelhas significam que tem algo errado com a sua configuração.

Passo a passo: Como usar o Solver no Excel

Tudo bem, o modelo está pronto. Agora vou te mostrar como usar o Solver. Essa parte leva uns 30 segundos, depois que você pega o jeito.

Usando a caixa de diálogo Parâmetros do Solver

Abra o Solver clicando na guia Dados > botão Solver.

Caixa de diálogo Solver antes da configuração. Imagem do autor.

Aqui está o que você deve preencher:

Definir objetivo: Clique nesse campo e, em seguida, clique na célula B12 (nossa célula Lucro Total).

To: Escolhao Max porque a gente quer aumentar o lucro.

Alterando células variáveis: Selecione B8:B9 (as quantidades que estamos decidindo).

Sujeito às restrições: Cliqueem “ ” (Adicionar restrição) para cada restrição:

• Trabalho: $B$16 <= $C$16

• Madeira: $B$17 <= $C$17

• Não-negatividade: $B$8:$B$9 >= 0

Escolha um método de resolução: Escolha Simplex LP. Vou explicar os outros métodos mais tarde, mas para problemas lineares como este, o Simplex LP é o que você precisa.

Solucionador configurado e pronto para resolver. Imagem do autor.

Resolvendo o modelo

Antes de clicar em Resolver, dá uma olhada rápida nos seus valores atuais. Tudo deve estar em zero.

Estado inicial com zeros em todos os lugares. Imagem do autor.

Agora clique em Resolver. Para problemas simples como o nosso, isso leva alguns segundos.

Sucesso! O Solver achou a melhor solução. Imagem do autor.

Quando a caixa de diálogo Resultados do Solver aparecer, deve dizer “O Solver encontrou uma solução”. Marque as caixas para“Resposta e sensibilidadede ” (Resposta e sensibilidade de ) em “Relatórios” ( ). Isso te dá uma análise detalhada que vamos ver mais tarde. Depois, clica em OK.

A melhor solução: 30 cadeiras e 20 tabelas. Imagem do autor.

E aí está. A solução mostra que devemos fazer 30 cadeiras e 20 tabelas para obter um lucro de 3.300 dólares. Observe que ambas as restrições mostram “OK”, o que significa que estamos usando nossos recursos de forma eficiente, sem ultrapassar os limites.

Resumo do fluxo de trabalho

Aqui está o processo que sigo sempre:

1. Crie o modelo (entradas, variáveis, objetivo, restrições)
2. Configurar o Solver (definir o que otimizar e o que ajustar)
3. Clique em Resolver e espere
4. Verifique se a solução faz sentido do ponto de vista comercial.
5. Dá uma olhada nos relatórios pra ter uma visão mais detalhada.

Esse último passo é importante. Só porque o Solver encontrou uma resposta, não quer dizer que seja a resposta certa para o seu negócio. Sempre verifique se os resultados estão certos.

Métodos do Solver, parâmetros e opções avançadas

O Solver oferece três algoritmos diferentes. Escolher o certo faz uma grande diferença na velocidade e precisão.

Visão geral dos métodos de resolução

Três métodos de resolução para diferentes tipos de problemas. Imagem do autor.

Simplex LP

Eu uso o Simplex LP 80% do tempo. É para problemas lineares em que tudo se resume a adição e multiplicação. Mix de produtos? Alocação do orçamento? Planejamento de recursos? Simplex LP. É rápido e garante que você obtenha a melhor resposta.

GRG Não Linear

O GRG Não Linear é pra quando você tem curvas, quadrados ou exponenciais nas suas fórmulas. Pense em modelos de preços em que a demanda cai conforme o preço sobe, mas não de forma linear. Ou otimização de portfólio com cálculos de variância. Ele encontra os ótimos locais, que podem não ser os melhores de todos, mas geralmente são bons o suficiente.

Evolutivo

Evolutivo é a opção da força bruta. Use isso quando tiver restrições inteiras (só pode fazer unidades inteiras), decisões binárias (sim ou não) ou instruções “ =IF() ” nas suas fórmulas. É mais lento porque basicamente tenta várias soluções aleatórias e fica com as boas. Masfunciona quando os outros métodos falham.

Minha regra de decisão é simples: comece com Simplex LP. Se suas fórmulas tiverem expoentes ou produtos de variáveis, mude para GRG Não Linear. Se você precisa de variáveis inteiras ou binárias, use o Evolutionary.

Opções e configurações do Solver

Clique em Opções ar na caixa de diálogo Solver para ver as configurações avançadas. Eu raramente me meto com isso, mas o que importa é o seguinte:

Limite o tempo de execução do Solver com o tempo máximo. O padrão é 100 segundos, o que é suficiente para a maioria dos problemas. Eu aumentei pra modelos bem grandes que precisam de mais tempo pra pensar.

Limite o número de tentativas que o Solver faz com Iterações. Se chegar nesse limite sem resolver, aumente.

Controle o rigor do Solver em relação às restrições com a opçãoPrecisão. Eu aperto isso para modelos financeiros em que cada centavo conta, mas, fora isso, deixo como está.

Geração de relatórios e análise de sensibilidade

Lembra quando eu te disse pra dar uma olhada nessas caixas de relatórios? Aqui está o que você ganha.

O Relatório de Respostas mostra a solução. Imagem do autor.

O Relatório de Respostas é o seu registro em papel. Isso mostra o que mudou: as variáveis de decisão passaram de 0 para 30 cadeiras e 20 tabelas, e o lucro passou de $0 para $3.300. Ele também mostra quais restrições são vinculativas (totalmente utilizadas) e quais não são (têm folga). No nosso caso, tanto a mão de obra quanto a madeira são limitadas, o que significa que estamos usando toda a capacidade disponível.

Quanto os insumos podem mudar antes que a solução mude? Imagem do autor.

O Relatório de Sensibilidade é onde as coisas ficam interessantes. As colunas“Aumento/Diminuição Permitida” ( ) do“ ” mostram o quanto suas entradas podem mudar antes que você precise de uma solução diferente. Intervalos amplos significam que sua resposta é robusta. Intervalos estreitos significam que pequenas mudanças podem mudar tudo.

Os preços-sombra mostram o valor dos recursos. Imagem do autor.

Mas o verdadeiro ouro é o preço-sombra. Isso mostra quanto vale mais uma unidade de um recurso. Se a mão de obra tem um preço sombra de $7,5, conseguir mais uma hora de trabalho acrescenta $7,5 ao lucro. Então, se você puder contratar mão de obra temporária por US$ 5/hora, faça isso. Por US$ 10/hora, dispenso.

Importância da análise de sensibilidade

Eu uso esses relatórios para responder a três perguntas. Em quais recursos devo investir? Minha solução é frágil? E se minhas previsões estiverem erradas? Os relatórios dão respostas claras para todas as três perguntas.

Primeiro, eu vejo os preços de sombra pra decidir em quais recursos devo investir. Preço de sombra alto significa valor alto.

Depois, eu vejo os intervalos permitidos pra saber se minha solução é frágil. Se o lucro só pode mudar em $1 antes que a solução mude, você está em uma situação delicada. Se puder mudar em 50 dólares, tá tudo certo.

Por fim, pergunto e se minhas previsões estiverem erradas? Os intervalos permitidos indicam até que ponto você pode se afastar antes de precisar recalcular.

Meu conselho: sempre dá uma olhada nos preços antes de aprovar um orçamento. Se um recurso mostrar um preço sombra de zero, isso quer dizer que você tem capacidade excedente. Investir mais nisso é um desperdício. Invista seu dinheiro onde os preços paralelos são mais altos.

Solução de problemas e erros

As mensagens de erro do Solver parecem assustadoras, mas geralmente são fáceis de resolver. Aqui estão os que eu vejo com mais frequência.

Erros e mensagens comuns

O Solver não conseguiu encontrar uma solução viável.

Isso quer dizer que suas restrições estão em conflito umas com as outras.

 Nenhuma solução satisfaz todas as restrições. Imagem do autor.

Isso geralmente rola quando você pede algo impossível, tipo tentar atingir uma meta de lucro que os recursos simplesmente não dão conta. A solução é remover as restrições uma a uma até que o Solver funcione; esse processo identifica exatamente qual restrição está causando o conflito.

Os valores das células definidos não convergem

Isso quer dizer que seu objetivo pode crescer pra sempre. Tá faltando um limite máximo em algum lugar. Negócios de verdade sempre têm limites, então inclua-os.

O Solver não consegue melhorar a solução atual

Isso geralmente rola com problemas não lineares. Tente valores iniciais diferentes ou mude para o método Evolutivo. Às vezes, um ótimo local já é bom o suficiente.

Erros na formulação do modelo

Referências circulares

Isso rola quando a célula A depende da célula B, que depende da célula A. O Excel vai te avisar sobre isso. Use a guia Fórmulas > Rastrear precedentes para encontrar o loop e quebrá-lo.

Restrições inteiras

Adicionando restrições inteiras para números inteiros. Imagem do autor.

Limitações do solucionador e alternativas para problemas em grande escala

O Excel Solver é ótimo, mas tem algumas limitações que você precisa saber.

Entendendo as limitações do Solver

O Solucionador Padrão tem um máximo de 200 variáveis de decisão e 100 restrições. Parece muito até você tentar escalar 200 funcionários em 50 turnos. São 10.000 variáveis, muito além do que o Solver consegue lidar.

Eu uso o Solver para planos de produção mensais, orçamentos trimestrais e seleção de projetos. É perfeito para decisões táticas. Mas não foi feito pra lidar com problemas de empresas grandes, tipo redes de cadeia de suprimentos com milhares de locais.

Alternativas para grandes problemas

OpenSolver

O OpenSolver é grátis e tira esses limites. Ele lida com tantas variáveis quanto a memória do seu computador permitir, usa a mesma interface do Excel Solver e você pode baixá-lo em OpenSolver.org. Recomendo quando você ultrapassar o Solver padrão.

Solucionador Frontline Premium

O Frontline Solver custa dinheiro (US$ 3.00 a US$ 1.500/ano), mas suporta milhares de variáveis com algoritmos melhores. É o que eu usaria se estivesse fazendo isso profissionalmente em tempo integral.

Software independente

Ferramentas como CPLEX ou Gurobi lidam com milhões de variáveis , mas exigem conhecimento de programação e investimento real. Isso é coisa de nível empresarial.

Na minha opinião, para a maioria dos analistas de negócios, o Excel Solver cobre 95% do que você precisa. Se você atingiu os limites, isso é um bom sinal, você está fazendo um trabalho sofisticado o suficiente para justificar ferramentas melhores.

Conclusão

A gente falou de tudo, desde a configuração básica até a interpretação dos preços sombra. 

Agora, em vez de ficar discutindo opiniões numa sala de reuniões, você pode colocar os números na tela. “Aqui está o plano matematicamente ideal.” Isso muda a conversa. Você para de adivinhar e começa a provar.

Vá em frente e tente isso no seu próximo problema complicado, aquele conflito de agenda ou aperto no orçamento que você tem evitado. Você pode descobrir que a resposta perfeita estava escondida na sua planilha o tempo todo.

E se você quiser aprender mais técnicas para impressionar seu chefe, faça nosso curso de  curso de Análise de Dados no Excel para continuar se aperfeiçoando.