Ferramenta SOLVER no Excel: como usar?

Quer aprender a usar a ferramenta Solver no Excel? Então você veio ao lugar certo!

Não é novidade que o Excel fornece uma infinidade de funções e ferramentas que visam auxiliar e automatizar o nosso trabalho. E dentre essas ferramentas está o SOLVER.

E se você ainda não conhece ou não sabe o que é o Solver no Excel, então acompanhe este artigo para conhecer essa poderosa ferramenta!

Já está interessado(a) no que vem por aí? Então compartilhe com sua rede de amigos para que mais pessoas possam aprender e conhecer a ferramenta Solver no Excel:

Facebook

Twitter

LinkedIn

O que é e como usar o SOLVER no Excel

O Solver é uma ferramenta do Excel que tem como finalidade realizar vários tipos de simulações, a fim de encontrar o valor ideal. E para que isto aconteça, ele testa as hipóteses possíveis dentro de determinados parâmetros que estipulamos.

Esta ferramenta é muito útil para resolver problemas de programação linear. Ela auxilia no processo de tomada de decisão como, por exemplo, se você deseja tomar a decisão sobre qual o orçamento ideal para determinada campanha de marketing, ou como maximizar o retorno de determinado investimento, entre outros.

Para melhor compreensão, suponha que você tem uma lista de compras com os valores e quantidades de cada item, e possui apenas um valor x que poderá gastar. Com a ferramenta Solver, é possível encontrar a melhor forma de conseguir comprar tudo da lista com apenas a quantidade de dinheiro disponível.

A princípio pode parecer um pouco confuso entender como esta ferramenta funciona. No entanto, a seguir apresentaremos alguns exemplos práticos de como podemos utilizar a ferramenta Solver no Excel.


Como habilitar o Solver ao Excel

Por padrão, a ferramenta Solver não vem habilitada no Excel. Neste caso, é necessário habilitá-la para que você tenha acesso a ela. Veja a seguir o passo a passo de como ativá-lo.

1. Vá até a guia Arquivo > Opções da sua pasta de trabalho:

habilitar o Solver

2. Ao clicar em Opções, a caixa de diálogo Opções do Excel irá aparecer, então selecione Suplementos e, em seguida, clique em Solver > Ir…

Suplementos do Excel

3. Em seguida, a tela mostrando os suplementos irá se abrir. Selecione a opção Solver e clique em Ok:

Suplementos do Excel

Feito isso, o Office vai começar a instalação do suplemento na pasta de trabalho do Excel.

4. Ao final da instalação é possível ver a ferramenta Solver, dentro da guia Dados conforme mostra a imagem abaixo:

Onde ficar o Solver na planilha

Pronto! Agora é só começar a utilizar a ferramenta.

Com dissemos no início do artigo, a ferramenta Solver é muito útil para a resolução de problemas.

Então para que você possa entender na prática como ela funciona, confira abaixo alguns exemplos de uso desta ferramenta.

Exemplo 1: Calcular o custo mínimo a ser cobrado por um novo serviço

Neste primeiro exemplo, suponha que você tenha uma oficina mecânica e queira ampliar os serviços oferecidos. Neste caso, você deseja começar a oferecer o serviço de lavagem, mas para isto, será necessário comprar a máquina automática para fazer a lavagem dos carros.

Este novo equipamento tem o custo de R$10.000,00 e deve ser parcelado em 12 vezes. Sendo assim, queremos saber qual será o custo mínimo a ser cobrado pelo serviço, para que seja possível pagar pelo novo equipamento dentro do prazo de 12 meses.

Para sabermos qual será este valor, o primeiro passo será organizar as informações no Excel, da seguinte maneira:

Analisar dados no Excel

Feito isso, agora vamos inserir essas informações na ferramenta Solver, ela irá nos auxiliar a encontrar este valor mínimo (B5) que deverá ser cobrado por cliente. Como ainda não temos este valor e não podemos deixá-lo em branco, vamos inserir na célula B5 o valor 1, apenas para a fórmula não retornar erro. Veja a imagem:

Analise de dados no Excel

Agora vamos inserir as informações na ferramenta Solver, confira o passo a passo abaixo:


1. Vá até a guia Dados e clique em Solver:

Definir objetivo no Solver

2. Na caixa de diálogo Parâmetros do Solver, você deverá inserir as informações nos 3 componentes principais, que são:

  • Definir o objetivo
  • Alterando Células variáveis
  • Sujeito às Restrições

Quais são os parâmetros do Solver

Por meio destes parâmetros o Solver irá encontrar o valor ideal , ou seja, o custo mínimo do serviço a ser oferecido, presente na célula B5.

3. Em seguida, precisamos inserir as informações nos 3 campos mostrados acima. O primeiro será o parâmetro Definir Objetivo. O Objetivo será a célula que contém a fórmula que representa o objetivo ou meta do problema. No exemplo está na célula B7.

Como podemos observar, a célula B7 contém a fórmula usada para calcular o prazo de pagamento, onde dividimos o valor do equipamento pela projeção de clientes multiplicado pelo custo mínimo do serviço. O prazo deve ser igual a 12, pois o equipamento deverá ser pago em 12 meses. Sendo assim, esta é nossa meta.

Confira a imagem abaixo:

Usando a ferramenta solver

3. Agora precisamos preencher o campo Alterando as Células Variáveis. Neste campo vamos inserir as células que contêm os dados variáveis, ou seja, aqueles que podem ser alterados para que o objetivo seja atingido. No exemplo, estes dados estão presentes nas células B4 e B5. Veja a imagem:

analise de dados no solver

Dica Extra: Se o intervalo das variáveis não forem adjacentes, selecione a primeira célula, pressione Ctrl e selecione as demais células.

4. O último passo será inserir as restrições. Estas são restrições ou limites das possíveis soluções para o problema. No exemplo, elas são o valor do investimento e o número de clientes por mês.

Para inserir estas restrições clique no botão Adicionar ao lado da janela Sujeito às Restrições:

Parâmetros

Na janela Adicionar Restrição, no campo Referência de Célula, insira a primeira restrição que é o valor do investimento. No item Referência de célula, selecione a célula B3, no campo do sinal, selecione o = e no campo Restrição insira o valor do investimento 10000, feito isso, clique em Adicionar, conforme imagem abaixo:

Passo a passo da análise de hipótese do Excel

Agora vamos inserir a segunda restrição, que é o número de clientes. Confira:

Restrições do Solver

Note que no campo do sinal, escolhemos o <=, pois o número de clientes não poderá ser menor que 50.

Ao finalizar clique em Ok. Note que as restrições foram inseridas no campo Sujeito às Restrições:

Teste de hipótese no Excel

5. Agora clique em Resolver, o Solver irá encontrar a solução ideal para o problema. Dependendo da complexidade do modelo, o Excel poderá levar algum tempo. Quando ele terminar o processo então a janela Resultados do Solver irá se abrir. Clique em Ok para finalizar.

Resultados do solver

A janela Resultados do Solver será fechada e a solução aparecerá na planilha, conforme a imagem abaixo:

Resultados

Neste exemplo, o valor do custo mínimo por serviço deverá ser de R$16,67, este valor permitirá que você pague pelo novo equipamento em 12 meses, desde que haja pelo menos 50 clientes por mês.


Exemplo 2: Otimizar as compras do mercado

Neste segundo exemplo, suponha que temos uma pequena lista de compras e um orçamento de R$500,00 para realizar as compras do mercado, e não podemos ultrapassar este valor. Veja abaixo, a lista com os valores dos itens e o nosso orçamento:

Como otimizar compras do mercado

Note que na planilha temos uma lista de compras com vários itens, seu valor médio, a quantidade em kg e o valor total.

Observe também que na parte superior encontra-se o valor que temos para gastar no mercado e o valor total dos itens que estão na lista.

Deste modo, é possível observar que esta lista de compra ficou mais cara do que o orçamento para o mercado. O total ficou R$659,00, no entanto, precisamos comprar todos os itens da lista com R$ 500,00.

Para resolver este problema, podemos utilizar o Solver e analisar a melhor opção para que isto ocorra. Confira o passo a passo:

1. Vá até a guia Dados > Solver.

2. Na caixa de diálogo Parâmetros do Solver, insira o objetivo, neste exemplo é a célula B4, que contém o valor total da compra, e no item Valor de, insira o valor do orçamento para o mercado, que é R$500,00. Confira a imagem abaixo:

Resolver problema de preço no excel

3. Agora selecione as células variáveis, esta poderá ser alterada para que o problema seja solucionado. Neste exemplo será a coluna de quantidade (Qnt – Kg).

otimizar gasto com supermercado no excel

Neste exemplo não vamos inserir restrições, então basta clicar em Resolver. Na caixa de diálogo Resultados do Solver, clique em Ok e confira o resultado.

Como otimizar gastos com supermercado no excel

Note que o Solver otimizou o valor da lista de compras, diminuindo a quantidade (kg) de cada item, assim o valor total da compra passou de R$659, 00 para R$ 500,00.

A ferramenta Solver, também fornece relatórios detalhados sobre o processo que foi efetuado. Para isto, na caixa de diálogo Resultado do Solver, no campo Relatórios, selecione os relatórios desejados e clique em Ok, conforme imagem abaixo:

Relatórios do solver

Os relatórios serão criados em uma nova planilha, conforme imagem abaixo:

Relatório

Note que no relatório é possível verificar todo o processo que foi realizado.

Informações importantes sobre o Solver

  • A ferramenta Solver permite selecionar três tipos de métodos de solução, são eles: GRG não linear, LP Simplex e Evolucionário.
  • Se o Solver estiver processando um determinado problema por muito tempo, você poderá interromper o processo pressionando a tecla Esc. Neste caso, o Excel irá recalcular a planilha com os últimos valores encontrados para as células Variáveis.

Baixe aqui a planilha contendo os exemplos apresentados neste tutorial.

Confira também as seguintes dicas de Excel:

E então, o que achou da dica para usar a ferramenta Solver no Excel? Caso tenha ficado com alguma dúvida, basta deixar um comentário abaixo. Continue acompanhando mais dicas de Excel em nosso site!