Goal Seek, qui fait partie de la boîte à outils d'analyse de simulation d'Excel, est une fonctionnalité puissante utilisée pour les calculs rétroactifs. Essentiellement, si vous connaissez le résultat souhaité d'une formule mais que vous n'êtes pas sûr de la valeur d'entrée qui produira ce résultat, vous pouvez utiliser la recherche d'objectif pour le trouver. Que vous soyez analyste financier, étudiant ou propriétaire d'entreprise, comprendre comment utiliser Goal Seek peut considérablement rationaliser votre processus de résolution de problèmes. Dans ce guide, nous explorerons ce qu'est Goal Seek, comment y accéder et démontrerons ses applications pratiques à travers divers exemples, allant de l'ajustement des plans de remboursement du prêt au calcul des notes minimales pour les examens et plus encore.

• Exemple 1 : Ajustement d'un plan de remboursement de prêt
• Exemple 2 : Détermination de la note minimale pour réussir l'examen
• Exemple 3 : Calcul des votes requis pour la victoire électorale
• Exemple 4 : Atteindre le profit cible dans une entreprise

Qu’est-ce que la recherche d’objectifs dans Excel ?

Recherche d'objectifs est une fonctionnalité intégrale de Microsoft Excel, qui fait partie de sa suite d'outils d'analyse de simulation. Il permet aux utilisateurs d'effectuer des calculs inverses : au lieu de calculer le résultat d'entrées données, vous spécifiez le résultat souhaité et Excel ajuste une valeur d'entrée pour atteindre cet objectif. Cette fonctionnalité est particulièrement utile dans les scénarios où vous devez trouver la valeur d'entrée qui produira un résultat spécifique.

• Ajustement variable unique:
  La recherche d'objectif ne peut modifier qu'une seule valeur de cellule d'entrée à la fois.
• Nécessite une formule:
  Lors de l'application de la fonctionnalité de recherche d'objectif, la cellule cible doit contenir une formule.
• Maintenir l'intégrité de la formule:
  La recherche d'objectif ne modifie pas la formule dans la cellule cible ; cela modifie la valeur dans la cellule d'entrée dont dépend la formule.

Accéder à la recherche d'objectifs dans Excel

Pour accéder à la fonctionnalité de recherche d'objectifs, accédez à Données onglet, cliquez sur What-If Analysis > Recherche d'objectifs. Voir la capture d'écran:

Puis le Recherche d'objectifs La boîte de dialogue apparaît. Voici une explication des trois options de la boîte de dialogue Recherche d'objectif d'Excel :

• Définir la cellule: La cellule cible qui contient la formule que vous souhaitez obtenir. Cette cellule doit contenir une formule et Goal Seek ajustera les valeurs dans une autre cellule associée pour que la valeur de cette cellule atteigne votre objectif défini.
• Évaluer: La valeur cible souhaitée pour le "Définir la cellule" atteindre.
• En changeant de cellule : La cellule d'entrée que vous souhaitez que la recherche d'objectif ajuste. La valeur dans cette cellule sera modifiée pour garantir la valeur dans le "Définir la cellule" Rencontre le "Évaluer" cible.
  
  

Dans la section suivante, nous explorerons comment utiliser la fonctionnalité de recherche d'objectifs d'Excel à travers des exemples détaillés.

Utiliser la recherche d'objectifs avec des exemples

Dans cette section, nous passerons en revue quatre exemples courants qui démontrent l’utilité de Goal Seek. Ces exemples iront des ajustements de finances personnelles aux décisions commerciales stratégiques, offrant un aperçu de la façon dont cet outil peut être appliqué dans divers scénarios du monde réel. Chaque exemple est conçu pour vous permettre de mieux comprendre comment utiliser efficacement Goal Seek pour résoudre différents types de problèmes.

Exemple 1 : Ajustement d'un plan de remboursement de prêt

Scénario: Une personne envisage de contracter un emprunt de 20,000 5 $ et vise à le rembourser en 5 ans à un taux d'intérêt annuel de 377.42 %. Le remboursement mensuel requis est de 500 $. Plus tard, il se rend compte qu’il peut augmenter son remboursement mensuel de XNUMX $. Étant donné que le montant total du prêt et le taux d’intérêt annuel restent les mêmes, combien d’années faudra-t-il désormais pour rembourser le prêt ?

Comme le montre le tableau suivant :

• B1 contient le montant du prêt de 20000 XNUMX $.
• B2 contient la durée du prêt 5 (années).
• B3 contient le taux d'intérêt annuel de 5 %.
• B5 contient le paiement mensuel calculé selon la formule =PMT(B3/12,B2*12,B1).
  
  

Ici, je vais vous montrer comment utiliser la fonction de recherche d'objectif pour accomplir cette tâche.

Étape 1 : Activez la fonction de recherche d'objectifs

Allez à Données onglet, cliquez sur What-If Analysis > Recherche d'objectifs.

Étape 2 : Configurez les paramètres de recherche d'objectifs

1. Dans le Définir la cellule , sélectionnez la formule B5.
2. Dans le Évaluer Dans la case, saisissez le montant du remboursement mensuel prévu -500 (un nombre négatif représente un paiement).
3. Dans le En changeant de cellule , sélectionnez la cellule B2 que vous souhaitez ajuster.
4. Cliquez OK.
   
   

Résultat

Les Statut de recherche d'objectif Une boîte de dialogue apparaîtra alors, indiquant qu'une solution a été trouvée. Dans le même temps, la valeur de la cellule B2 que vous avez spécifiée dans la case « En modifiant la cellule » sera remplacée par la nouvelle valeur et la cellule de formule obtiendra la valeur cible en fonction du changement de variable. Cliquez sur OK pour appliquer les changements

Il lui faudra désormais environ 3.7 ans pour rembourser son prêt.

Exemple 2 : Détermination de la note minimale pour réussir l'examen

Scénario: Un étudiant doit passer 5 examens, chacun de poids égal. Pour réussir, l'étudiant doit obtenir une note moyenne de 70 % à tous les examens. Après avoir réussi les 4 premiers examens et connaissant leurs notes, l'étudiant doit maintenant calculer la note minimale requise au cinquième examen pour s'assurer que la moyenne globale atteint ou dépasse 70 %.

Comme le montre la capture d'écran ci-dessous :

• B1 contient la note du premier examen.
• B2 contient la note du deuxième examen.
• B3 contient la note du troisième examen.
• B4 contient la note du quatrième examen.
• B5 contiendra la note du cinquième examen.
• B7 est la note moyenne de passage calculée par la formule =(B1+B2+B3+B4+B5)/5.
  
  

Pour atteindre cet objectif, vous pouvez appliquer la fonctionnalité de recherche d'objectif comme suit :

Étape 1 : Activez la fonction de recherche d'objectifs

Allez à Données onglet, cliquez sur What-If Analysis > Recherche d'objectifs.

Étape 2 : Configurez les paramètres de recherche d'objectifs

1. Dans le Définir la cellule , sélectionnez la cellule de formule B7.
2. Dans le Évaluer Dans la case, entrez la moyenne de passage de 70 %.
3. Dans le En changeant de cellule , sélectionnez la cellule (B5) que vous souhaitez ajuster.
4. Cliquez OK.
   
   

Résultat

Les Statut de recherche d'objectif Une boîte de dialogue apparaîtra alors, indiquant qu'une solution a été trouvée. Dans le même temps, la cellule B7 que vous avez spécifiée dans la case « En modifiant la cellule » sera automatiquement saisie avec une valeur correspondante et la cellule de formule obtiendra la valeur cible en fonction du changement de variable. Cliquez sur OK pour accepter la solution.

Ainsi, pour atteindre la moyenne globale requise et réussir tous les examens, l’étudiant doit obtenir au moins 71 % au dernier examen.

Exemple 3 : Calcul des votes requis pour la victoire électorale

Scénario: Lors d'une élection, un candidat doit calculer le nombre minimum de voix requis pour obtenir la majorité. Le nombre total de suffrages exprimés étant connu, quel est le nombre minimum de voix que le candidat doit recueillir pour obtenir plus de 50 % et gagner ?

Comme le montre la capture d'écran ci-dessous :

• La cellule B2 contient le nombre total de votes exprimés lors de l'élection.
• La cellule B3 indique le nombre actuel de votes reçus par le candidat.
• La cellule B4 contient la majorité souhaitée (%) des voix que le candidat vise à obtenir, qui est calculée par la formule =B2/B1.
  
  

Pour atteindre cet objectif, vous pouvez appliquer la fonctionnalité de recherche d'objectif comme suit :

Étape 1 : Activez la fonction de recherche d'objectifs

Allez à Données onglet, cliquez sur What-If Analysis > Recherche d'objectifs.

Étape 2 : Configurez les paramètres de recherche d'objectifs

1. Dans le Définir la cellule , sélectionnez la cellule de formule B4.
2. Dans le Évaluer Dans la case, entrez le pourcentage souhaité de votes majoritaires. Dans ce cas, pour remporter l'élection, le candidat doit obtenir plus de la moitié (c'est-à-dire plus de 50 %) du total des votes valides, j'inscris donc 51 %.
3. Dans le En changeant de cellule , sélectionnez la cellule (B2) que vous souhaitez ajuster.
4. Cliquez OK.
   
   

Résultat

Les Statut de recherche d'objectif Une boîte de dialogue apparaîtra alors, indiquant qu'une solution a été trouvée. Dans le même temps, la cellule B2 que vous avez spécifiée dans la case « En modifiant la cellule » sera automatiquement remplacée par la nouvelle valeur et la cellule de formule obtiendra la valeur cible en fonction du changement de variable. Cliquez sur OK appliquer les changements.

Ainsi, le candidat a besoin d’au moins 5100 XNUMX voix pour remporter l’élection.

Exemple 4 : Atteindre le bénéfice cible dans une entreprise

Scénario: Une entreprise vise à atteindre un objectif de bénéfice spécifique de 150,000 XNUMX $ pour l'année en cours. En tenant compte à la fois des coûts fixes et variables, ils doivent déterminer le chiffre d’affaires nécessaire. Combien d’unités de ventes sont nécessaires pour atteindre cet objectif de profit ?

Comme le montre la capture d'écran ci-dessous :

• B1 contient les coûts fixes.
• B2 contient les coûts variables par unité.
• B3 contient le prix unitaire.
• B4 contient les unités vendues.
• B6 est le revenu total, calculé par la formule =B3*B4.
• B7 est le coût total, calculé par la formule =B1+(B2*B4).
• B9 est le bénéfice actuel sur les ventes, calculé par la formule =B6-B7.
  
  

Pour obtenir le total des unités de ventes en fonction du bénéfice cible, vous pouvez appliquer la fonction de recherche d'objectifs comme suit.

Étape 1 : Activez la fonction de recherche d'objectifs

Allez à Données onglet, cliquez sur What-If Analysis > Recherche d'objectifs.

Étape 2 : Configurez les paramètres de recherche d'objectifs

1. Dans le Définir la cellule Dans la zone , sélectionnez la cellule de formule qui renvoie le profit. Voici la cellule B9.
2. Dans le Évaluer Dans la case, entrez le bénéfice cible 150000 XNUMX.
3. Dans le En changeant de cellule , sélectionnez la cellule (B4) que vous souhaitez ajuster.
4. Cliquez OK.
   
   

Résultat

La boîte de dialogue Statut de recherche d'objectif apparaîtra alors, indiquant qu'une solution a été trouvée. Dans le même temps, la cellule B4 que vous avez spécifiée dans la case "En modifiant la cellule" sera automatiquement remplacée par une nouvelle valeur et la cellule de formule obtiendra la valeur cible en fonction du changement de variable. Cliquez sur OK pour accepter les modifications.

En conséquence, l’entreprise doit vendre au moins 6666 150,000 unités pour atteindre son objectif de bénéfice de XNUMX XNUMX $.

Problèmes courants et solutions pour la recherche d’objectifs

Cette section répertorie certains problèmes courants et les solutions correspondantes pour Goal Seek.

1. La recherche d'objectif ne parvient pas à trouver une solution

• Raison: Cela se produit généralement lorsque la valeur cible n'est pas réalisable avec une valeur d'entrée possible, ou si la supposition initiale est trop éloignée de toute solution potentielle.
• Solution: Ajustez la valeur cible pour vous assurer qu’elle se situe dans une plage réalisable. Essayez également différentes suppositions initiales plus proches de la solution attendue. Assurez-vous que la formule dans la « Cellule définie » est correcte et peut logiquement produire la « Valeur à » souhaitée.

2. Vitesse de calcul lente

• Raison: La recherche d'objectifs peut être lente avec des ensembles de données volumineux, des formules complexes ou lorsque la supposition initiale est loin de la solution, nécessitant plus d'itérations.
• Solution: Simplifiez les formules lorsque cela est possible et réduisez la taille des données. Assurez-vous que la proposition initiale est aussi proche que possible de la solution attendue afin de réduire le nombre d'itérations nécessaires.

3. Problèmes de précision

• Raison: Lorsque vous utilisez la recherche d'objectif, vous remarquerez peut-être que parfois Excel indique qu'il a trouvé une solution, mais ce n'est pas exactement ce que vous voulez - proche mais pas assez proche. La recherche d'objectifs ne fournit pas toujours un résultat très précis en raison de la précision des calculs et des arrondis d'Excel.
  
  Par exemple, pour calculer une exponentiation spécifique d'un nombre et utiliser la recherche d'objectif pour trouver la base qui permet d'obtenir le résultat souhaité. Ici, je vais modifier la valeur d'entrée dans la cellule A1 (Base) afin que la cellule A3 (résultat de l'exponentiation) corresponde au résultat cible de 25.
  
  Comme vous pouvez le voir dans la capture d'écran ci-dessous, Goal Seek fournit une valeur approximative plutôt que la racine exacte.
  
• Solution: Augmentez le nombre de décimales affichées dans les options de calcul d'Excel pour plus de précision, puis réexécutez la fonction de recherche d'objectif. Veuillez procéder comme suit.
  
  1. Cliquez Déposez votre dernière attestation > Options ouvrir le Options Excel fenêtre.
  2. Sélectionnez Formules dans le volet gauche et dans le Options de calcul section, augmentez le nombre de décimales dans la Modification maximale boîte. Ici je change 0.001 à 0.000000001 et cliquez sur OK.
     
  3. Réexécutez la recherche d'objectif et vous obtiendrez la racine exacte, comme indiqué dans la capture d'écran ci-dessous.
     

4. Limites et méthodes alternatives

• Raison: La recherche d'objectif ne peut ajuster qu'une seule valeur d'entrée et peut ne pas convenir aux équations nécessitant des ajustements simultanés de plusieurs variables.
• Solution: Pour les scénarios nécessitant des ajustements de plusieurs variables, utilisez le Solveur d'Excel, qui est plus puissant et permet de définir des contraintes.

Goal Seek est un outil puissant pour effectuer des calculs inverses, vous épargnant de la tâche fastidieuse consistant à tester manuellement différentes valeurs pour se rapprocher de votre objectif. Grâce aux informations contenues dans cet article, vous pouvez désormais appliquer Goal Seek en toute confiance, propulsant ainsi votre analyse de données et votre efficacité vers de nouveaux sommets. Pour ceux qui souhaitent approfondir les fonctionnalités d’Excel, notre site Web propose une multitude de didacticiels. Découvrez plus de trucs et astuces Excel ici.