Goal Seek est l'un des outils d'analyse de simulation d'Excel qui vous aide à trouver la valeur d'entrée correcte d'une formule pour obtenir la sortie souhaitée. Il montre comment une valeur dans une formule affecte une autre. En d'autres termes, si vous avez une valeur cible que vous souhaitez atteindre, vous pouvez utiliser la recherche d'objectifs pour trouver la bonne valeur d'entrée pour l'obtenir.
Par exemple, disons que vous avez obtenu un total de 75 points dans une matière et que vous avez besoin d'au moins 90 pour obtenir une note S dans cette matière. Heureusement, vous avez un dernier test qui pourrait vous aider à augmenter votre score moyen. Vous pouvez utiliser Goal Seek pour déterminer le score dont vous avez besoin sur ce test final pour obtenir une note S.
Goal Seek utilise une méthode d'essais et d'erreurs pour revenir en arrière sur le problème en entrant des suppositions jusqu'à ce qu'il arrive au bon résultat. Goal Seek peut trouver la meilleure valeur d'entrée pour la formule en quelques secondes, ce qui aurait pris beaucoup de temps à déterminer manuellement. Dans cet article, nous allons vous montrer comment utiliser l'outil Goal Seek dans Excel avec quelques exemples.
Composants de la fonction de recherche d'objectif
La fonction de recherche d'objectif se compose de trois paramètres, à savoir :
- Définir la cellule – Il s'agit de la cellule dans laquelle la formule est saisie. Il spécifie la cellule dans laquelle vous voulez la sortie souhaitée.
- Évaluer – Il s'agit de la valeur cible/désirée que vous souhaitez à la suite de l'opération de recherche d'objectif.
- En changeant de cellule – Ceci spécifie la cellule dont la valeur doit être ajustée pour obtenir la sortie souhaitée.
Comment utiliser la recherche d'objectifs dans Excel : exemple 1
Pour montrer comment cela fonctionne dans un exemple simple, imaginez que vous dirigez un étal de fruits. Dans la capture d'écran ci-dessous, la cellule B11 (ci-dessous) affiche combien il vous a coûté d'acheter des fruits pour votre stand et la cellule B12 montre vos revenus totaux sur la vente de ces fruits. Et la cellule B13 montre le pourcentage de votre profit (20%).
Si vous souhaitez augmenter vos bénéfices à « 30 % », mais que vous ne pouvez pas augmenter votre investissement, vous devez donc augmenter vos revenus pour réaliser des bénéfices. Mais jusqu'à combien ? La recherche d'objectif vous aidera à le trouver.
Vous utilisez la formule suivante dans la cellule B13 pour calculer le pourcentage de profit :
=(B12-B11)/B12Maintenant, vous voulez calculer la marge bénéficiaire de sorte que votre pourcentage de profit soit de 30 %. Vous pouvez le faire avec Goal Seek dans Excel.
La première chose à faire est de sélectionner la cellule dont vous souhaitez ajuster la valeur. Chaque fois que vous utilisez Goal Seek, vous devez sélectionner une cellule qui contient une formule ou une fonction. Dans notre cas, nous sélectionnerons la cellule B13 car elle contient la formule pour calculer le pourcentage.
Ensuite, allez dans l'onglet « Données », cliquez sur le bouton « Et si l'analyse » dans le groupe Prévision et sélectionnez « Recherche d'objectif ».
La boîte de dialogue Recherche d'objectif apparaîtra avec 3 champs :
- Définir la cellule – Saisissez la référence de cellule qui contient la formule (B13). C'est la cellule qui va avoir la sortie souhaitée.
- Évaluer – Entrez le résultat souhaité que vous essayez d'atteindre (30%).
- En changeant de cellule – Insérez la référence de cellule pour la valeur d'entrée que vous souhaitez modifier (B12) afin d'arriver au résultat souhaité. Cliquez simplement sur la cellule ou entrez manuellement la référence de la cellule. Lorsque vous sélectionnez la cellule, Excel ajoute le signe « $ » avant la lettre de la colonne et le numéro de ligne pour en faire une cellule absolue.
Lorsque vous avez terminé, cliquez sur « OK » pour le tester.
Ensuite, une boîte de dialogue « Statut de la recherche d'objectif » apparaîtra et vous informera si elle a trouvé une solution, comme indiqué ci-dessous. Si une solution a été trouvée, la valeur d'entrée dans la « cellule évolutive (B12) » sera ajustée à une nouvelle valeur. C'est ce que nous voulons. Ainsi, dans cet exemple, l'analyse a déterminé que, pour que vous atteigniez votre objectif de profit de 30 %, vous devez réaliser un revenu de 1635,5 $ (B12).
Cliquez sur « OK » et Excel modifiera les valeurs des cellules ou cliquez sur « Annuler » pour supprimer la solution et restaurer la valeur d'origine.
La valeur d'entrée (1635,5) dans la cellule B12 est ce que nous avons découvert en utilisant Goal Seek afin d'atteindre notre objectif (30%).
Choses à retenir lors de l'utilisation de la recherche d'objectifs
- La cellule définie doit toujours contenir une formule qui dépend de la cellule « En changeant de cellule ».
- Vous ne pouvez utiliser la recherche d'objectif que sur une seule valeur d'entrée de cellule à la fois
- Le « En changeant de cellule » doit contenir une valeur et non une formule.
- Si Goal Seek ne peut pas trouver la bonne solution, il affiche la valeur la plus proche qu'il peut produire et vous indique que le message « Goal-Seeking n'a peut-être pas trouvé de solution ».
Que faire lorsque Excel Goal Seek ne fonctionne pas
Cependant, si vous êtes certain qu'il existe une solution, vous pouvez essayer de résoudre ce problème de plusieurs manières.
Vérifier les paramètres et les valeurs de la recherche d'objectif
Il y a deux choses que vous devez vérifier : d'abord, vérifiez si le paramètre « Définir la cellule » fait référence à la cellule de formule. Deuxièmement, assurez-vous que la cellule de formule (Set cell) dépend, directement ou indirectement, de la cellule changeante.
Ajustement des paramètres d'itération
Dans les paramètres d'Excel, vous pouvez modifier le nombre de tentatives possibles qu'Excel peut effectuer pour trouver la bonne solution ainsi que sa précision.
Pour modifier les paramètres de calcul d'itération, cliquez sur « Fichier » dans la liste des onglets. Ensuite, cliquez sur « Options » en bas.
Dans la fenêtre Options Excel, cliquez sur « Formules » dans le volet de gauche.
Dans la section « Options de calcul », modifiez ces paramètres :
- Itérations maximales – Il indique le nombre de solutions possibles qu'Excel calculera ; plus le nombre est élevé, plus il peut effectuer d'itérations. Par exemple, si vous définissez « Maximum Iterations » sur 150, Excel teste 150 solutions possibles.
- Changement maximum - Il indique l'exactitude des résultats ; le plus petit nombre donne une plus grande précision. Par exemple, si la valeur de votre cellule d'entrée est égale à « 0 » mais que la recherche d'objectif arrête de calculer à « 0,001 », le changer en « 0,0001 » devrait résoudre le problème.
Augmentez la valeur 'Maximum Iterations' si vous voulez qu'Excel teste plus de solutions possibles et diminuez la valeur 'Maximum Change' si vous voulez un résultat plus précis.
La capture d'écran ci-dessous montre la valeur par défaut :
Aucune référence circulaire
Lorsqu'une formule renvoie à sa propre cellule, cela s'appelle une référence circulaire. Si vous voulez que Excel Goal Seek fonctionne correctement, les formules ne doivent pas utiliser de référence circulaire.
Exemple de recherche d'objectif Excel 2
Disons que vous empruntez de l'argent de « 25 000 $ » à quelqu'un. Ils vous prêtent l'argent à un taux d'intérêt de 7 % par mois pour une période de 20 mois, ce qui fait le remboursement de « 1327 $ » par mois. Mais vous ne pouvez vous permettre de payer que « 1 000 $ » par mois pendant 20 mois avec un intérêt de 7 %. Goal Seek peut vous aider à trouver le montant du prêt qui produit un paiement mensuel (EMI) de « 1000 $ ».
Entrez les trois variables d'entrée dont vous aurez besoin pour calculer votre calcul PMT, c'est-à-dire un taux d'intérêt de 7 %, une durée de 20 mois et un montant en principal de 25 000 $.
Entrez la formule PMT suivante dans la cellule B5 qui vous donnera un montant EMI de 1 327,97 $.
La syntaxe de la fonction PMT :
=PMT(Taux d'intérêt/12, Terme, Principal)La formule:
=PMT(B3/12,B4,-B2)
Sélectionnez la cellule B5 (cellule de formule) et accédez à Données -> Analyse de simulation -> Recherche d'objectif.
Utilisez ces paramètres dans la fenêtre « Recherche d'objectif » :
- Définir la cellule – B5 (la cellule qui contient la formule qui calcule l'EMI)
- Évaluer – 1000 (la formule résultat/objectif que vous recherchez)
- En changeant de cellule – B2 (il s'agit du montant du prêt que vous souhaitez modifier pour atteindre la valeur de l'objectif)
Ensuite, appuyez sur « OK » pour conserver la solution trouvée ou « Annuler » pour la supprimer.
L'analyse vous indique que le montant maximum que vous pouvez emprunter est de 18825 $ si vous souhaitez dépasser votre budget.
C'est ainsi que vous utilisez Goal Seek dans Excel.