O Excel é muito eficiente quando você tem todos os dados necessários para seus cálculos.
Mas não seria legal se pudesse resolver variáveis desconhecidas ?
Com o objetivo de procurar e o suplemento Solver, pode. E vamos mostrar como. Leia para um guia completo sobre como resolver para uma única célula com meta de busca ou uma equação mais complicada com o Solver.
Como usar a meta no Excel
O objetivo da busca já está embutido no Excel. Está sob a aba Data, no menu What-If Analysis :
Para este exemplo, usaremos um conjunto muito simples de números. Temos três quartos de números de vendas e uma meta anual. Podemos usar o Objetivo Buscar para descobrir o que os números precisam estar no quarto trimestre para fazer o objetivo.
Como você pode ver, o total de vendas atual é de 114.706 unidades. Se quisermos vender 250.000 até o final do ano, quantos precisamos vender no quarto trimestre? A meta do Excel nos dirá.
Veja como usar a Meta de meta, passo a passo:
- Clique em Dados> What-If Analysis> Goal Seek . Você verá esta janela:
- Coloque a parte “equals” da sua equação no campo Set Cell . Esse é o número que o Excel tentará otimizar. No nosso caso, é o total de nossos números de vendas na célula B5.
- Digite seu valor de meta no campo Para o valor . Estamos à procura de um total de 250.000 unidades vendidas, por isso vamos colocar “250.000” neste campo.
- Diga ao Excel qual variável resolver no campo Alterando Célula . Queremos ver o que nossas vendas no quarto trimestre precisam ser. Então, vamos dizer ao Excel para resolver a célula D2. Ficará assim quando estiver pronto:
- Clique em OK para resolver seu objetivo. Quando parece bom, basta clicar em OK . O Excel informará quando o recurso Busca encontrou uma solução.
- Clique em OK novamente e você verá o valor que resolve sua equação na célula escolhida. Ao alterar a célula .
No nosso caso, a solução é 135.294 unidades. Claro, poderíamos ter acabado de descobrir isso subtraindo o total da meta anual. Mas o Goal Seek também pode ser usado em uma célula que já tenha dados nele . E isso é mais útil.
Observe que o Excel substitui nossos dados anteriores. É uma boa ideia executar o Goal Seek em uma cópia dos seus dados . Também é uma boa ideia fazer uma anotação nos dados copiados de que ela foi gerada usando a Meta de meta. Você não quer confundi-lo com dados atuais e precisos.
Então Goal Seek é um recurso útil do Excel 16 Excel fórmulas que ajudarão você a resolver problemas da vida real 16 Excel fórmulas que ajudarão você a resolver problemas da vida real A ferramenta certa é metade do trabalho. O Excel pode resolver cálculos e processar dados mais rapidamente do que você pode encontrar sua calculadora. Mostramos as principais fórmulas do Excel e demonstramos como usá-las. Leia mais, mas não é tão impressionante. Vamos dar uma olhada em uma ferramenta muito mais interessante: o suplemento do Solver.
O que o Solver do Excel faz?
Resumindo, o Solver é como uma versão multivariada de Meta de Objetivo . Leva uma variável de meta e ajusta várias outras variáveis até obter a resposta desejada.
Pode resolver um valor máximo de um número, um valor mínimo de um número ou um número exato.
E isso funciona dentro de restrições, portanto, se uma variável não puder ser alterada ou puder variar apenas dentro de um intervalo especificado, o Solver levará isso em consideração.
É uma ótima maneira de resolver várias variáveis desconhecidas no Excel. Mas encontrar e usá-lo não é simples.
Vamos dar uma olhada no carregamento do suplemento do Solver e, em seguida, entrar em como usar o Solver no Excel 2016.
Como carregar o suplemento Solver
O Excel não tem o Solver por padrão. É um add-in, assim como outros poderosos recursos do Excel Power Up Excel com 10 Add-Ins para processar, analisar e visualizar dados como um Pro Power Up Excel com 10 Add-Ins para processar, analisar e visualizar dados como um Pro Vanilla Excel é incrível, mas você pode torná-lo ainda mais poderoso com add-ins. Quaisquer que sejam os dados que você precise processar, é provável que alguém tenha criado um aplicativo do Excel para isso. Aqui está uma seleção. Leia mais, você tem que carregá-lo primeiro. Felizmente, já está no seu computador.
Vá para Arquivo> Opções> Suplementos . Em seguida, clique em Ir ao lado de Gerenciar: Suplementos do Excel .
Se esse menu suspenso disser algo diferente de "Suplementos do Excel", você precisará alterá-lo:
Na janela resultante, você verá algumas opções. Verifique se a caixa ao lado de Solver Add-In está marcada e clique em OK .
Agora você verá o botão Solver no grupo Análise da guia Dados :
Se você já estiver usando o Data Analysis Toolpak Como fazer o Basic Data Analysis no Excel Como fazer o Basic Data Analysis no Excel O Excel não se destina à análise de dados, mas ainda pode manipular estatísticas. Mostraremos como usar o suplemento Data Analysis Toolpak para executar as estatísticas do Excel. Leia mais, você verá o botão Análise de dados. Caso contrário, o Solver aparecerá sozinho.
Agora que você carregou o add-in, vamos dar uma olhada em como usá-lo.
Como usar o Solver no Excel
Existem três partes em qualquer ação do Solver: o objetivo, as células variáveis e as restrições. Vamos percorrer cada um dos passos.
- Clique em Dados> Solver . Você verá a janela Parâmetros do Solver abaixo. (Se você não vir o botão solver, consulte a seção anterior sobre como carregar o suplemento Solver.)
- Defina seu objetivo de célula e diga ao Excel seu objetivo. O objetivo está no topo da janela do Solver e tem duas partes: a célula objetiva e uma opção de maximizar, minimizar ou um valor específico.
Se você selecionar Max, o Excel ajustará suas variáveis para obter o maior número possível em sua célula objetiva. Min é o oposto: o Solver minimiza o número objetivo. Valor De permite especificar um número específico para o Solver procurar. - Escolha as células variáveis que o Excel pode alterar. As células variáveis são definidas com o campo Alterando Células Variáveis . Clique na seta ao lado do campo e, em seguida, clique e arraste para selecionar as células com as quais o Solver deve trabalhar. Observe que essas são todas as células que podem variar. Se você não quiser que um celular seja alterado, não o selecione.
- Definir restrições em variáveis múltiplas ou individuais. Finalmente, chegamos às restrições. É aqui que o Solver é realmente poderoso. Em vez de alterar qualquer uma das células variáveis para qualquer número desejado, você pode especificar restrições que precisam ser atendidas. Para detalhes, veja a seção sobre como definir restrições abaixo.
- Quando todas essas informações estiverem em vigor, clique em Resolver para obter sua resposta. O Excel atualizará seus dados para incluir as novas variáveis (por isso, recomendamos que você crie uma cópia dos seus dados primeiro).
Você também pode gerar relatórios, que analisaremos brevemente no nosso exemplo do Solver abaixo.
Como definir restrições no Solver
Você pode informar ao Excel que uma variável deve ser maior que 200. Ao tentar valores de variáveis diferentes, o Excel não ficará abaixo de 201 com essa variável específica.
Para adicionar uma restrição, clique no botão Adicionar ao lado da lista de restrições. Você terá uma nova janela. Selecione a célula (ou células) a ser restringida no campo Referência de célula e escolha um operador.
Aqui estão os operadores disponíveis:
- <= (menor ou igual a)
- = (igual a)
- => (maior que ou igual a)
- int (deve ser um inteiro)
- bin (deve ser 1 ou 0)
- AllDifferent
AllDifferent é um pouco confuso. Ele especifica que cada célula no intervalo selecionado para Referência de Célula deve ser um número diferente. Mas também especifica que eles devem estar entre 1 e o número de células. Então, se você tiver três células, você terminará com os números 1, 2 e 3 (mas não necessariamente nessa ordem)
Por fim, adicione o valor da restrição.
É importante lembrar que você pode selecionar várias células para referência de célula. Se você quiser que seis variáveis tenham valores acima de 10, por exemplo, você pode selecionar todas e dizer ao Solver que elas devem ser maiores ou iguais a 11. Você não precisa adicionar uma restrição para cada célula.
Você também pode usar a caixa de seleção na janela principal do Solver para certificar-se de que todos os valores para os quais você não especificou restrições sejam não-negativos. Se você quiser que suas variáveis fiquem negativas, desmarque essa caixa.
Um exemplo do solver
Para ver como tudo isso funciona, usaremos o suplemento Solver para fazer um cálculo rápido. Aqui estão os dados que estamos começando:
Nele, temos cinco tarefas diferentes, cada uma paga uma taxa diferente. Também temos o número de horas que um trabalhador teórico trabalhou em cada um desses trabalhos em uma determinada semana. Podemos usar o suplemento Solver para descobrir como maximizar o pagamento total, mantendo certas variáveis dentro de algumas restrições.
Aqui estão as restrições que usaremos:
- Nenhum trabalho pode ficar abaixo de quatro horas.
- O trabalho 2 deve ser maior que oito horas .
- O trabalho 5 deve ser menor que onze horas .
- O total de horas trabalhadas deve ser igual a 40 .
Pode ser útil escrever suas restrições assim antes de usar o Solver.
Veja como nós configuramos isso no Solver:
Primeiro, observe que criei uma cópia da tabela para não sobrescrever a original, que contém nossas horas de trabalho atuais.
Em segundo lugar, veja que os valores nas restrições maior que e menor que são um valor maior ou menor do que o que mencionei acima. Isso porque não há opções maior que ou menor que. Há apenas maior que ou igual a e menor que ou igual a.
Vamos acertar o Solve e ver o que acontece.
O Solver encontrou uma solução! Como você pode ver à esquerda da janela acima, nossos ganhos aumentaram em $ 130. E todas as restrições foram cumpridas.
Para manter os novos valores, verifique se a solução do Keep Solver está marcada e clique em OK .
Se você quiser mais informações, no entanto, você pode selecionar um relatório do lado direito da janela. Selecione todos os relatórios desejados, informe ao Excel se deseja que eles sejam descritos (recomendo) e clique em OK .
Os relatórios são gerados em novas planilhas em sua pasta de trabalho e fornecem informações sobre o processo que o suplemento Solver passou para obter sua resposta.
No nosso caso, os relatórios não são muito empolgantes e não há muita informação interessante. Mas se você executar uma equação do Solver mais complicada, poderá encontrar algumas informações úteis sobre relatórios nessas novas planilhas. Basta clicar no botão + ao lado de qualquer relatório para obter mais informações:
Opções Avançadas do Solver
Se você não sabe muito sobre estatísticas, pode ignorar as opções avançadas do Solver e executá-las como estão. Mas se você estiver executando cálculos grandes e complexos, talvez queira investigá-los.
O mais óbvio é o método de solução:
Você pode escolher entre GRG Nonlinear, Simplex LP e Evolutionary. O Excel fornece uma explicação simples sobre quando você deve usar cada um deles. Uma explicação melhor requer algum conhecimento de estatística Aprender estatísticas de graça com esses 6 recursos Aprender estatísticas de graça com esses recursos A estatística tem a reputação de um assunto que é difícil de entender. Mas aprender com o recurso certo o ajudará a entender os resultados da pesquisa, os relatórios eleitorais e suas atribuições de classe de estatísticas em nenhum momento. Leia mais e regressão.
Para ajustar configurações adicionais, basta clicar no botão Opções . Você pode informar ao Excel sobre a otimização de números inteiros, definir restrições de tempo de cálculo (útil para conjuntos de dados em massa) e ajustar como os métodos de resolução GRG e Evolucionária realizam seus cálculos.
Novamente, se você não sabe o que isso significa, não se preocupe com isso. Se você quiser saber mais sobre qual método de solução usar, o Engineer Excel tem um bom artigo que define isso para você. Se você quer precisão máxima, Evolutionary é provavelmente um bom caminho a percorrer. Apenas esteja ciente de que levará muito tempo.
Meta e Solver de Objetivo: Levando o Excel para o Próximo Nível
Agora que você está familiarizado com os conceitos básicos de solução de variáveis desconhecidas no Excel, um novo mundo de cálculos de planilhas está aberto para você.
Busca de meta pode ajudá-lo a economizar tempo fazendo alguns cálculos mais rápidos, e o Solver adiciona uma enorme quantidade de poder às habilidades de cálculo do Excel Como calcular estatísticas básicas no Excel: Guia para iniciantes Como calcular estatísticas básicas no Excel: Guia do iniciante fazer estatísticas! Você pode calcular porcentagens, médias, desvio padrão, erro padrão e testes T do aluno. Consulte Mais informação .
É só uma questão de ficar confortável com eles. Quanto mais você os usar, mais úteis eles se tornarão.
Você usa o Meta ou o Solver em suas planilhas? Que outras dicas você pode fornecer para obter as melhores respostas? Compartilhe seus pensamentos nos comentários abaixo!