Recherche d'objectifs VBA | Comment utiliser la recherche d'objectifs pour trouver de la valeur dans Excel VBA?

Recherche d'objectifs dans Excel VBA

Goal Seek est l'outil disponible dans Excel VBA qui nous aide à trouver le nombre requis à atteindre pour atteindre l'objectif défini.

Par exemple, vous êtes étudiant et vous avez ciblé un score moyen de 90% parmi six matières disponibles. À partir de maintenant, vous avez terminé 5 examens et il ne vous reste plus qu'une seule matière, vos notes attendues pour cinq matières terminées sont 89, 88, 91, 87, 89 et 90. Vous voulez maintenant savoir combien vous devez obtenir l'examen final pour atteindre l'objectif de pourcentage moyen global de 90%.

Cela peut être fait en utilisant GOAL SEEK dans la feuille de calcul Excel ainsi que dans le codage VBA. Voyons comment cela fonctionne avec VBA.

Syntaxe de recherche d'objectifs VBA

Dans VBA Goal Seek, nous devons spécifier la valeur que nous modifions et arriver au résultat final ciblé, alors fournissez la référence de cellule en utilisant l'objet VBA RANGE, plus tard, nous pourrons accéder à l'option GOAL SEEK.

Vous trouverez ci-dessous la syntaxe de la recherche d'objectifs dans VBA.

Range (): Dans ce cas, nous devons fournir la référence de cellule où nous devons atteindre la valeur ciblée.
Objectif: Dans cet argument, nous devons saisir quel est l'objectif que nous essayons d'atteindre.
Changer de cellule: dans cet argument, nous devons fournir en modifiant la valeur de cellule dont nous avons besoin pour atteindre l'objectif.

Exemples de recherche d'objectifs Excel VBA

Voici les exemples de recherche d'objectifs dans Excel VBA.

Vous pouvez télécharger ce modèle Excel de recherche d'objectif VBA ici - Modèle Excel de recherche d'objectif VBA

Recherche d'objectifs VBA - Exemple n ° 1

Prenons l'exemple de la note moyenne à l'examen uniquement. Vous trouverez ci-dessous le score prévu de 5 matières à l'issue de l'examen terminé.

Tout d'abord, nous devons arriver à quel est le score moyen des 5 matières terminées. Appliquer la fonction MOYENNE dans la cellule B8.

Dans cet exemple, notre objectif est 90, la cellule changeante sera B7 . Ainsi, Goal Seek nous aidera à trouver le score ciblé du sujet final pour atteindre la moyenne globale de 90.

Démarrez la sous-procédure dans le module de classe VBA.

Code:

 Sous Goal_Seek_Example1 () End Sub

Nous avons maintenant besoin du résultat dans la cellule B8, alors fournissez cette référence de plage en utilisant l'objet RANGE.

Code:

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

Maintenant, mettez un point et entrez l'option «Recherche d'objectifs».

Le premier argument est «Objectif» pour cela, nous devons entrer notre objectif final pour arriver dans la GAMME B8. Dans cet exemple, nous essayons d'atteindre l'objectif de 90.

Code:

 Sous Goal_Seek_Example1 () Range ("B8"). GoalSeek Goal: = 90 End Sub

L'argument suivant est «Changer de cellule». Pour cela, nous devons indiquer dans quelle cellule nous avons besoin de la nouvelle valeur pour atteindre l' objectif.

Code:

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

Dans cet exemple, notre cellule changeante est la cellule Sub 6, c'est-à-dire la cellule B7.

Ok, exécutons le code pour voir ce qui doit être fait dans le sujet final pour atteindre le pourcentage moyen global de 90.

Ainsi, dans le sujet final, 95 doit être noté pour obtenir la moyenne globale de 90.

Recherche d'objectifs VBA - Exemple n ° 2

Nous avons appris comment appliquer GOAL SEEK pour trouver le nombre requis pour atteindre l'objectif. Nous allons maintenant voir un exemple avancé de recherche de la note finale à l'examen pour plus d'un étudiant.

Vous trouverez ci-dessous les notes attendues de 5 matières après l'examen.

Puisque nous trouvons l'objectif pour plus d'un étudiant, nous devons utiliser des boucles, voici le code pour vous.

Code:

 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 To 5 Set ResultCell = Cells (8, k) Set ChangingCell = Cells (7, k) ResultCell.GoalSeek TargetScore, ChangingCell Next k End Sub

Ce code parcourra en boucle tous les scores des étudiants et arrivera à la note finale à l'examen requise pour atteindre la moyenne globale de 90.

Nous avons donc le résultat final maintenant comme,

L'élève A ne doit obtenir que 83 pour obtenir le pourcentage global de 90 et l'étudiant D doit en obtenir 93.

Mais regardez les étudiants B et C, ils doivent marquer 104 chacun à l'examen final, ce qui n'est pas possible du tout.

De cette manière, en utilisant l'analyse GOAL SEEK, nous pouvons trouver le nombre requis pour atteindre le nombre ciblé au milieu du projet ou du processus.

Choses à retenir

Goal Seek est disponible avec l'outil de feuille de calcul ainsi que l'outil VBA.
La cellule de résultat doit toujours contenir une formule.
Nous devons entrer la valeur de l'objectif et changer la référence de la cellule à l'outil de recherche d'objectif.