VBA Goal Seek

Atingir meta no Excel VBA

Goal Seek é a ferramenta disponível no Excel VBA que nos ajuda a encontrar o número necessário a ser alcançado para chegar à meta definida.

Por exemplo, você é um estudante e tem como objetivo uma pontuação média de 90% em seis disciplinas disponíveis. A partir de agora, você concluiu 5 exames e ficou com apenas uma matéria. Suas pontuações antecipadas em cinco matérias concluídas são 89, 88, 91, 87, 89 e 90. Agora você quer saber quanto precisa para pontuar em o exame final para atingir a meta percentual média geral de 90%.

Isso pode ser feito usando GOAL SEEK na planilha do Excel, bem como na codificação VBA. Vamos ver como funciona com o VBA.

Sintaxe de busca de metas VBA

No VBA Goal Seek precisamos especificar o valor que estamos alterando e chegar ao resultado final almejado, então forneça a referência da célula usando o objeto VBA RANGE, depois podemos acessar a opção GOAL SEEK.

Abaixo está a sintaxe de busca de meta no VBA.

  • Range (): Neste, precisamos fornecer a referência de célula onde precisamos alcançar o valor desejado.
  • Meta: Neste argumento, precisamos inserir qual é a meta que estamos tentando alcançar.
  • Mudando a célula: neste argumento, precisamos fornecer alterando o valor da célula que precisamos para atingir o objetivo.

Exemplos de Excel VBA Goal Seek

A seguir estão os exemplos de busca de metas no Excel VBA.

Você pode baixar este modelo VBA Goal Seek Excel aqui - Modelo Excel VBA Goal Seek

VBA Goal Seek - Exemplo # 1

Tomemos o exemplo da pontuação média do exame apenas. Abaixo está a pontuação prevista de 5 disciplinas no exame concluído.

Primeiro, precisamos chegar a qual é a pontuação média das 5 disciplinas concluídas. Aplica a função AVERAGE na célula B8.

Neste exemplo, nosso objetivo é 90, a célula em mudança será B7 . Portanto, o Goal Seek nos ajudará a encontrar a pontuação desejada do assunto final para atingir a média geral de 90.

Inicie o subprocedimento no módulo de classe VBA.

Código:

 Sub Goal_Seek_Example1 () End Sub

Agora precisamos do resultado na célula B8, então forneça essa referência de intervalo usando o objeto RANGE.

Código:

 Sub Goal_Seek_Example1 () Range ("B8") End Sub

Agora coloque um ponto e entre na opção “Goal Seek”.

O primeiro argumento é "Meta", para isso precisamos inserir nossa meta final para chegar ao RANGE B8. Neste exemplo, estamos tentando atingir a meta de 90.

Código:

 Sub Goal_Seek_Example1 () Intervalo ("B8"). Objetivo GoalSeek: = 90 End Sub

O próximo argumento é “Changing Cell” para isso, precisamos fornecer em qual célula precisamos do novo valor para atingir o objetivo.

Código:

 Sub Goal_Seek_Example1 () Range ("B8"). GoalSeek Goal: = 90, ChangingCell: = Range ("B7") End Sub

Neste exemplo, nossa célula em mudança é célula Sub 6, ou seja, célula B7.

Ok, vamos executar o código para ver o que precisa ser feito no assunto final para atingir a porcentagem média geral de 90.

Portanto, na disciplina final, 95 devem ser pontuados para se obter a média geral de 90.

VBA Goal Seek - Exemplo # 2

Aprendemos como aplicar GOAL SEEK para encontrar o número necessário para atingir a meta. Agora veremos alguns exemplos avançados de como encontrar a pontuação do exame final para mais de um aluno.

Abaixo estão as pontuações antecipadas de 5 disciplinas após o exame.

Como estamos encontrando a meta para mais de um aluno, precisamos usar loops, abaixo está o código para você.

Código:

 Sub Goal_Seek_Example2 () Dim k As Long Dim ResultCell As Range Dim ChangingCell As Range Dim TargetScore As Integer TargetScore = 90 For k = 2 a 5 Set ResultCell = Cells (8, k) Set ChangingCell = Cells (7, k) ResultCell.GoalSeek TargetScore, ChangingCell Next k End Sub

Este código percorrerá todas as notas dos alunos e chegará à nota final do exame necessária para atingir a média geral de 90.

Então, temos o resultado final agora como,

O aluno A precisa marcar apenas 83 para garantir a porcentagem geral de 90 e o aluno D precisa marcar 93.

Mas olhe para o aluno B e C, eles precisam de uma pontuação de 104 cada no exame final, o que não é possível de forma alguma.

Assim, usando a análise GOAL SEEK, podemos encontrar o número necessário para atingir o número desejado no meio do projeto ou processo.

Coisas para lembrar

  • O Goal Seek está disponível tanto com a ferramenta de planilha quanto com a ferramenta VBA.
  • A célula de resultado deve sempre conter uma fórmula.
  • Precisamos inserir o valor da meta e alterar a referência da célula para a ferramenta de busca de metas.