Como usar a meta de busca no Microsoft Excel para cenários de hipóteses

gurinho

A análise de variações hipotéticas do Excel permite que você veja como a alteração de uma célula pode afetar o resultado de uma fórmula. Você pode usar as ferramentas do Excel para esse fim para calcular o efeito da alteração do valor de uma célula em uma fórmula.

O Excel possui três tipos de ferramentas de análise de variações hipotéticas: Gerenciador de cenários, Atingir metas e Tabela de dados. Com o Goal Seek, você pode determinar qual input você precisa para converter a fórmula de trás para frente em um determinado output.

O recurso Goal Seek no Excel é tentativa e erro, portanto, se ele não produzir o que você deseja, o Excel trabalha para melhorar esse valor até que isso aconteça

O que são fórmulas do Excel Goal Seek?

A busca de metas no Excel se divide essencialmente em três componentes principais:

  1. Definir célula : a célula que você deseja usar como objetivo.
  2. Valorizar : o valor que você deseja como meta.
  3. Ao alterar a célula : a célula que você gostaria de alterar para atingir o valor do seu objetivo.

Com essas três configurações definidas, o Excel tentará melhorar o valor na célula definida em Alterando a célula até que a célula definida em Definir célula atinja o valor determinado no valor Para .

Se tudo isso parecer confuso, os exemplos abaixo darão uma boa ideia de como funciona a busca de metas.

Exemplo 1 de busca de meta

Por exemplo, digamos que você tenha duas células (A e B) e uma terceira célula que calcula a média dessas duas.

Agora, suponha que você tenha um valor estático para A e deseja aumentar o nível médio alterando o valor para B. Usando o Atingir Meta, você pode calcular qual valor de B produzirá a média desejada.

Relacionado: Dicas para economizar tempo no Microsoft Excel

Como usar a meta de busca no Excel

  1. No Excel, clique na célula C1 .
  2. Na barra de fórmulas, insira o seguinte código:
     =AVERAGE(A1:B1)

    Essa fórmula fará com que o Excel calcule a média dos valores nas células A1 e B1 e apresente na célula C1 . Você pode garantir que sua fórmula funcione inserindo números em A1 e B1. C1 deve mostrar sua média.

  3. Para este exemplo, altere o valor da célula A1 para 16 .
  4. Selecione a célula C1 e, a seguir, na faixa de opções, vá para a guia Dados .
  5. Na guia Dados , selecione Análise de variações hipotéticas e, em seguida, Atingir meta . Isso abrirá a janela Goal Seek.
  6. Na janela Atingir Meta, defina a célula Definir como C1 . Esta será sua célula-objetivo. Ter destacado C1 antes de abrir a janela Goal Seek irá automaticamente defini-lo na célula Set .
  7. Em Para valor , insira o valor da meta que você deseja. Para este exemplo, queremos que nossa média seja 26, então o valor da meta também será 26.
  8. Por fim, na célula Ao alterar , selecione a célula que deseja alterar para atingir a meta. Neste exemplo, será a célula B2 .
  9. Clique em OK para que o Goal Seek faça sua mágica.

Depois de clicar em OK, uma caixa de diálogo será exibida informando que o Goal Seek encontrou uma solução.

O valor da célula A1 mudará para a solução e isso substituirá os dados anteriores. É uma boa ideia executar o Goal Seek em uma cópia de sua folha de dados para evitar a perda de seus dados originais.

Relacionado: Fórmulas malucas do Excel que fazem coisas incríveis

Exemplo 2 de busca de meta

O Goal Seek pode ser útil quando você o utiliza em cenários da vida real. No entanto, para usar o Goal Seek em todo o seu potencial, você precisa ter uma fórmula adequada, para começar.

Para este exemplo, vamos pegar um pequeno caso bancário. Suponha que você tenha uma conta bancária com juros de 4% ao ano sobre o dinheiro que possui.

Usando a análise de variações hipotéticas e a busca de metas, você pode calcular quanto dinheiro precisa ter em sua conta para obter uma determinada quantia de pagamento de juros mensal.

Para este exemplo, digamos que queremos receber $ 350 todos os meses com o pagamento de juros. Veja como você pode calcular isso:

  1. Na célula A1, digite Equilíbrio .
  2. Na célula A2, digite Taxa anual .
  3. Na célula A4, digite Lucro Mensal .
  4. Na célula B2 digite 4% .
  5. Selecione a célula B4 e, na barra de fórmulas, insira a fórmula abaixo:
     =B1*B2/12

    O lucro mensal será igual ao saldo da conta multiplicado pela taxa anual e depois dividido pelo número de meses em um ano, 12 meses.

  6. Vá para a guia Dados , clique em Análise de variações hipotéticas e selecione Atingir meta .
  7. Na janela Atingir Meta , digite B4 na célula Definir .
  8. Digite 350 na célula de valor Para . (Este é o lucro mensal que você deseja obter)
  9. Digite B1 em Ao alterar a célula . (Isso mudará o saldo para um valor que renderá $ 350 mensais)
  10. Clique OK . A caixa de diálogo Status de busca de meta aparecerá.

Na caixa de diálogo Status da Busca de Meta, você verá que a Busca de Meta encontrou uma solução na célula B4. Na célula B1, você pode ver a solução, que deve ser 105.000.

Requisitos de busca de meta

Com o Goal Seek, você pode ver que ele pode alterar apenas uma célula para atingir o valor do objetivo. Isso significa que o Goal Seek não pode resolver e chegar a uma solução se tiver mais de uma variável.

No Excel, você ainda pode resolver mais de uma variável, mas precisará usar outra ferramenta chamada Solver. Você pode ler mais sobre o Solver do Excel lendo nosso artigo sobre como usar o Solver do Excel .

Limitações de busca de meta

Uma fórmula de Atingir Objetivo usa o processo de tentativa e melhoria para atingir o valor ideal, o que pode representar alguns problemas quando a fórmula produz um valor indefinido. Veja como você pode testá-lo:

  1. Na célula A1 , digite 3 .
  2. Selecione a célula C1 e , na barra de fórmulas, insira a fórmula abaixo:
     =1/(A1-5)

    Esta fórmula dividirá um por A1-5 e, se A1-5 for 0, o resultado será um valor indefinido.

  3. Vá para a guia Dados , clique em Análise de variações hipotéticas e selecione Atingir meta .
  4. Em Definir célula , digite C1 .
  5. Em Para o valor , digite 1 .
  6. Finalmente, em Alterando o tipo de célula A1 . (Isso mudará o valor A1 para chegar a 1 em C1)
  7. Clique OK .

A caixa de diálogo Status de busca de meta informará que pode não ter encontrado uma solução e você pode verificar se o valor A1 está muito errado.

Agora, a solução para essa meta de busca seria simplesmente ter 6 em A1. Isso dará 1/1, que é igual a 1. Mas em um ponto durante o processo de teste e melhoria, o Excel tenta 5 em A1, que dá 1/0, que é indefinido, matando o processo.

Uma maneira de contornar esse problema de busca de metas é ter um valor inicial diferente, que possa escapar do indefinido no processo de teste e melhoria.

Por exemplo, se você alterar A1 para qualquer número maior que 5 e, em seguida, repetir as mesmas etapas para Atingir meta para 1, deverá obter o resultado correto.

Faça os cálculos mais eficientes com a busca de metas

A análise de variações hipotéticas e a busca de metas podem acelerar seus cálculos com fórmulas em grande medida. Aprender as fórmulas certas é outro passo importante para se tornar um mestre do Excel.