Comment utiliser la recherche d’objectifs dans Excel

La recherche d’objectifs est l’un des outils d’analyse hypothétique d’Excel qui vous aide à trouver la valeur d’entrée d’une formule pour obtenir le résultat souhaité. Il montre comment une valeur dans une formule en affecte une autre. En termes simples, si vous souhaitez atteindre une valeur cible, vous pouvez utiliser la recherche d’objectifs pour trouver la meilleure valeur d’entrée pour l’obtenir.

Par exemple, disons que vous avez obtenu un total de 65 points dans une matière et que vous avez besoin d’au moins 80 pour obtenir une note A dans cette matière. Heureusement, vous avez une dernière mission qui pourrait vous aider à augmenter votre moyenne. Vous pouvez utiliser la recherche d’objectifs pour déterminer le score dont vous avez besoin pour ce devoir final pour obtenir une note A.

La recherche d’objectifs utilise essentiellement une méthode d’essai et d’erreur pour suivre le problème en saisissant des suppositions jusqu’à ce qu’il parvienne à la réponse. Il peut trouver la réponse exacte en quelques secondes, ce qui aurait pris beaucoup de temps pour la trouver manuellement. Dans ce didacticiel, nous allons vous montrer comment utiliser l’outil de recherche d’objectifs dans Excel avec quelques exemples.

Composants de la fonction de recherche d’objectifs

La fonction de recherche d’objectif comprend trois paramètres, à savoir:

• Définir la cellule -Il s’agit de la cellule dans laquelle la formule est entrée. Il spécifie la cellule dans laquelle vous souhaitez obtenir le résultat souhaité.
• À valoriser -Il s’agit de la valeur cible/souhaitée que vous souhaitez à la suite de l’outil de recherche d’objectif.
• En changeant de cellule -Ceci spécifie la cellule dont la valeur doit être modifiée pour obtenir le résultat souhaité après l’opération de recherche d’objectif.

Comment utiliser la recherche d’objectifs dans Excel: exemple 1

Pour illustrer son fonctionnement 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 en coûte pour acheter des fruits pour votre étal et la cellule B12 montre votre revenu total sur la vente de ces fruits. Et la cellule B13 montre le pourcentage de vos bénéfices (20%).

Si vous souhaitez augmenter vos bénéfices à”30%”de vos revenus et en supposant que vous ne parvenez pas à augmenter votre investissement, vous réalisez que vous devez augmenter vos revenus. Mais à combien? La recherche d’objectifs vous aidera à le trouver.

Vous utilisez la formule suivante dans la cellule B13 pour calculer le pourcentage de profit:

 =(B12-B11)/B12  

À présent, vous souhaitez calculer la marge bénéficiaire de sorte que votre pourcentage de profit soit de 30%. Vous pouvez le faire avec la recherche d’objectifs dans Excel.

La première chose à faire est de sélectionner la cellule dont vous souhaitez modifier la valeur. Chaque fois que vous utilisez la recherche d’objectifs, vous devez sélectionner une cellule contenant une formule ou une fonction. Dans notre cas, nous allons sélectionner la cellule B13 car elle contient la formule pour calculer le pourcentage.

Accédez ensuite à l’onglet”Données”, cliquez sur le bouton”Et si l’analyse”dans le groupe Prévision, puis sélectionnez”Recherche d’objectifs”.

La boîte de dialogue Recherche d’objectifs apparaîtra avec trois champs:

• Définir la cellule -Saisissez la référence de cellule contenant la formule (B13). C’est la cellule qui contiendra le résultat souhaité.

• À valoriser -Saisissez le résultat souhaité que vous essayez d’obtenir (30%).

• En changeant de cellule -Entrez la référence de la cellule d’entrée que vous voudriez ajuster (B12) afin d’arriver au résultat souhaité. Cliquez simplement sur la cellule ou saisissez 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.

La boîte de dialogue État de la recherche d’objectifs s’affiche et vous informe si une solution a été trouvée. Si une solution a été trouvée, la valeur d’entrée dans la «cellule changeante» sera ajustée à une nouvelle. Ainsi, dans cet exemple, l’analyse a déterminé que, pour que vous puissiez atteindre votre objectif de profit de 30%, vous devez réaliser un revenu de 1 635,5 USD (B12).

Cliquez sur”OK”et Excel modifiera les valeurs de cellule 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 la recherche d’objectif afin d’atteindre notre objectif (30%).

Points à retenir lors de l’utilisation de la recherche d’objectifs

• La cellule d’ensemble 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
• La cellule”En modifiant la cellule”doit contenir une valeur et non une formule.
• Si la recherche d’objectifs ne trouve pas la solution exacte, elle affiche la valeur la plus proche qu’elle a trouvée et vous informe que”La recherche d’objectifs n’a peut-être pas trouvé de solution”.

Que faire lorsque la recherche d’objectifs Excel ne fonctionne pas

Cependant, si vous êtes sûr qu’une solution existe, 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’objectifs

Tout d’abord, assurez-vous que la cellule Set fait référence à la cellule contenant une formule, et vérifiez si 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 ajuster le nombre de solutions possibles qu’Excel calculera ainsi que sa précision.

Pour les modifier, cliquez sur”Fichier”dans la liste des onglets. Cliquez ensuite sur”Options”en bas.

Dans la fenêtre Options Excel, cliquez sur”Formules”dans la barre latérale gauche.

Ici, modifiez ces paramètres sous”Options de calcul”:

• Nombre maximum d’itérations-Il indique le nombre de solutions possibles; plus le nombre est élevé, plus il y a d’itérations. Augmentez ce nombre si vous souhaitez qu’Excel teste davantage de solutions possibles. Par exemple, si vous définissez”Nombre maximal d’itérations”sur 150, Excel teste 150 solutions possibles.
• Changement maximal-Il indique l’exactitude des résultats; plus le nombre est bas, plus la précision est élevée. Diminuez ce nombre si votre formule nécessite plus de précision. Par exemple, si votre cellule d’entrée est égale à 0 mais que la recherche d’objectif s’arrête à 0,001, définir le changement maximal sur 0,0001 devrait résoudre le problème.

La capture d’écran ci-dessous montre la valeur par défaut:

Aucune référence circulaire

Lorsqu’une formule renvoie directement ou indirectement à sa propre cellule, on parle de référence circulaire. Pour que Excel Goal Seek fonctionne correctement, les formules impliquées ne doivent pas être co-dépendantes les unes des autres.

Exemple de recherche d’objectifs Excel 2

Supposons que vous empruntez de l’argent «25 000 USD» à quelqu’un. Ils vous prêtent de l’argent à un taux d’intérêt de 7% par mois pendant une période de 20 mois, ce qui représente un remboursement de «1 327 $» par mois. Mais vous ne pouvez vous permettre de payer que «1 000 USD» par mois pendant 20 mois avec un intérêt de 7%. Utilisez la recherche d’objectifs dans Excel pour trouver le montant du prêt qui produit un paiement mensuel (EMI) de «1 000 USD».

Entrez les 3 variables d’entrée que vous devrez utiliser pour votre formule PMT, c’est-à-dire un taux d’intérêt de 7%, une durée de 20 mois et un montant principal de 25 000 USD.

Entrez la formule PMT suivante dans la cellule B5 qui vous donnera un montant de paiement mensuel de 1 327,97 $.

La syntaxe de la fonction PMT:

 =PMT (taux d'intérêt/12, durée, principal)  

La formule:

 =PMT (B3/12, B4,-B2)  

Sélectionnez la cellule B5 (cellule de formule) et accédez à Données-> Et si l’analyse-> Recherche d’objectifs.

Voici ce que vous devez saisir dans la fenêtre”Recherche d’objectifs”:

• Définir la cellule -B5 (la cellule qui contient la formule qui calcule l’EMI)
• À la valeur -1000 (le résultat de la formule/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, cliquez sur”OK”pour conserver la solution trouvée ou sur”Annuler”pour la supprimer.

L’analyse vous indique que le montant maximal du prêt que vous pouvez emprunter est de 1 825 USD si vous souhaitez respecter le budget.

C’est ainsi que vous utilisez la recherche d’objectifs dans Excel.