Goal Seek é uma das ferramentas de análise de variações hipotéticas do Excel que ajuda você a encontrar o valor de entrada correto de uma fórmula para obter a saída desejada. Mostra como um valor em uma fórmula afeta outro. Em outras palavras, se você tem um valor-alvo que deseja atingir, pode usar a busca de objetivo para encontrar o valor de entrada correto para obtê-lo.
Por exemplo, digamos que você acertou um total de 75 marcas em uma matéria e precisa de pelo menos 90 para obter uma nota S nessa matéria. Felizmente, você tem um último teste que pode ajudá-lo a aumentar sua pontuação média. Você pode usar o Goal Seek para descobrir de quanta pontuação você precisa naquele teste final para obter uma nota S.
O Goal Seek usa um método de tentativa e erro para rastrear o problema inserindo estimativas até chegar ao resultado certo. O Goal Seek pode encontrar o melhor valor de entrada para a fórmula em questão de segundos, o que levaria muito tempo para descobrir manualmente. Neste artigo, mostraremos como usar a ferramenta Goal Seek no Excel com alguns exemplos.
Componentes da função de busca de metas
A função Goal Seek consiste em três parâmetros, viz .:
- Definir célula - Esta é a célula em que a fórmula é inserida. Ele especifica a célula na qual você deseja a saída desejada.
- Dar valor - Este é o Valor Alvo / Desejado que você deseja como resultado da operação de busca de meta.
- Mudando de célula - Isso especifica a célula cujo valor precisa ser ajustado para obter a saída desejada.
Como usar a busca de metas no Excel: Exemplo 1
Para demonstrar como isso funciona em um exemplo simples, imagine que você dirige uma barraca de frutas. Na captura de tela abaixo, a célula B11 (abaixo) exibe quanto custou para você comprar frutas para sua barraca e a célula B12 mostra sua receita total com a venda dessas frutas. E a célula B13 mostra a porcentagem do seu lucro (20%).
Se você deseja aumentar seu lucro para "30%", mas não consegue aumentar seu investimento, deve aumentar sua receita para obter lucro. Mas para quanto? A busca de metas o ajudará a encontrá-lo.
Use a seguinte fórmula na célula B13 para calcular a porcentagem de lucro:
= (B12-B11) / B12Agora, você deseja calcular a margem de lucro de forma que seu percentual de lucro seja de 30%. Você pode fazer isso com o Goal Seek no Excel.
A primeira coisa a fazer é selecionar a célula cujo valor você deseja ajustar. Cada vez que você usa o Goal Seek, deve selecionar uma célula que contenha uma fórmula ou função. Em nosso caso, selecionaremos a célula B13 porque contém a fórmula para calcular a porcentagem.
Em seguida, vá para a guia ‘Dados’, clique no botão ‘Análise What if’ no grupo Forecast e selecione ‘Goal Seek’.
A caixa de diálogo Atingir Meta aparecerá com 3 campos:
- Definir célula - Insira a referência da célula que contém a fórmula (B13). Esta é a célula que terá a saída desejada.
- Dar valor - Insira o resultado desejado que você está tentando alcançar (30%).
- Mudando de célula - Insira a referência da célula para o valor de entrada que você deseja alterar (B12) para chegar ao resultado desejado. Basta clicar na célula ou inserir manualmente a referência da célula. Quando você seleciona a célula, o Excel adiciona o sinal ‘$’ antes da letra da coluna e do número da linha para torná-la uma célula absoluta.
Quando terminar, clique em ‘OK’ para testá-lo.
Em seguida, uma caixa de diálogo ‘Status de busca de meta’ aparecerá e informará se encontrou alguma solução, conforme mostrado abaixo. Se uma solução foi encontrada, o valor de entrada na 'célula de mudança (B12)' será ajustado para um novo valor. Isto é o que queremos. Portanto, neste exemplo, a análise determinou que, para atingir sua meta de lucro de 30%, você precisa atingir uma receita de $ 1.635,5 (B12).
Clique em ‘OK’ e o Excel mudará os valores das células ou clique em ‘Cancelar’ para descartar a solução e restaurar o valor original.
O valor de entrada (1635,5) na célula B12 é o que descobrimos usando o Goal Seek para atingir nossa meta (30%).
Coisas para lembrar ao usar o Goal Seek
- A célula definida deve sempre conter uma fórmula que é dependente da célula ‘By Changing Cell’.
- Você só pode usar o Goal Seek em um único valor de entrada de célula por vez
- O ‘Ao alterar a célula’ deve conter um valor e não uma fórmula.
- Se o Goal Seek não conseguir encontrar a solução correta, ele mostra o valor mais próximo que pode produzir e diz a você que a mensagem ‘Goal-Getting pode não ter encontrado uma solução’.
O que fazer quando a busca de metas do Excel não está funcionando
No entanto, se você tiver certeza de que há uma solução, existem algumas coisas que você pode tentar para corrigir esse problema.
Verifique os parâmetros e valores do Goal Seek
Há duas coisas que você deve verificar: primeiro, verifique se o parâmetro ‘Definir célula’ se refere à célula da fórmula. Em segundo lugar, certifique-se de que a célula da fórmula (célula definida) depende, direta ou indiretamente, da célula em mudança.
Ajustando as configurações de iteração
Nas configurações do Excel, você pode alterar o número de tentativas possíveis que o Excel pode fazer para encontrar a solução certa, bem como sua precisão.
Para alterar as configurações de cálculo de iteração, clique em ‘Arquivo’ na lista de guias. Em seguida, clique em "Opções" na parte inferior.
Na janela Opções do Excel, clique em "Fórmulas" no painel esquerdo.
Na seção "Opções de cálculo", altere estas configurações:
- Iterações máximas - indica o número de soluções possíveis que o Excel irá calcular; quanto maior o número, mais iterações ele pode realizar. Por exemplo, se você definir 'Iterações máximas' para 150, o Excel testa 150 soluções possíveis.
- Alteração máxima - indica a precisão dos resultados; o número menor fornece maior precisão. Por exemplo, se o valor da sua célula de entrada for igual a '0', mas a meta de busca parar de calcular em '0,001', alterá-lo para '0,0001' deve resolver o problema.
Aumente o valor de ‘Iterações máximas’ se desejar que o Excel teste mais soluções possíveis e diminua o valor de ‘Alteração máxima’ se desejar um resultado mais preciso.
A captura de tela abaixo mostra o valor padrão:
Sem referências circulares
Quando uma fórmula se refere à sua própria célula, é chamada de referência circular. Se você deseja que o Excel Goal Seek funcione corretamente, as fórmulas não devem usar referências circulares.
Exemplo 2 de busca de metas do Excel
Digamos que você esteja pegando um empréstimo de ‘$ 25.000’ de alguém. Eles emprestam o dinheiro a uma taxa de juros de 7% ao mês por um período de 20 meses, o que resulta no reembolso de '$ 1.327' por mês. Mas você só pode pagar "$ 1.000" por mês, durante 20 meses, com juros de 7%. O Goal Seek pode ajudá-lo a encontrar o valor do empréstimo que produz um pagamento mensal (EMI) de '$ 1000'.
Insira as três variáveis de entrada que você precisará para calcular o cálculo do PMT, ou seja, taxa de juros de 7%, prazo de 20 meses e valor principal de $ 25.000.
Insira a seguinte fórmula PMT na célula B5, que resultará em um valor EMI de $ 1.327,97.
A sintaxe da função PMT:
= PMT (taxa de juros / 12, prazo, principal)A fórmula:
= PMT (B3 / 12, B4, -B2)
Selecione a célula B5 (célula de fórmula) e vá para Dados -> Análise de hipóteses -> Atingir meta.
Use estes parâmetros na janela ‘Atingir meta’:
- Definir célula - B5 (a célula que contém a fórmula que calcula a EMI)
- Dar valor - 1000 (o resultado da fórmula / objetivo que você está procurando)
- Mudando de célula - B2 (este é o valor do empréstimo que você deseja alterar para atingir o valor da meta)
Em seguida, clique em 'OK' para manter a solução encontrada ou em 'Cancelar' para descartá-la.
A análise indica que a quantidade máxima de dinheiro que você pode pedir emprestado é de $ 18.825, se quiser exceder o seu orçamento.
É assim que você usa o Goal Seek no Excel.