Comment utiliser la recherche d'objectif dans Microsoft Excel pour des scénarios de simulation

Comment utiliser la recherche d'objectif dans Microsoft Excel pour des scénarios de simulation

L'analyse de simulation d'Excel vous permet de voir comment la modification d'une cellule peut affecter la sortie d'une formule. Vous pouvez utiliser les outils d'Excel à cet effet pour calculer l'effet de la modification de la valeur d'une cellule dans une formule.





Excel dispose de trois types d'outils d'analyse de simulation : Gestionnaire de scénarios, Recherche d'objectifs et Tableau de données. Avec Goal Seek, vous pouvez déterminer de quelle entrée vous avez besoin pour convertir la formule en arrière vers une certaine sortie.





La fonction de recherche d'objectif dans Excel est un essai et une erreur, donc si elle ne produit pas ce que vous voulez, Excel travaille à améliorer cette valeur jusqu'à ce qu'elle le fasse.





Que sont les formules de recherche d'objectifs Excel ?

La recherche d'objectifs dans Excel se décompose essentiellement en trois composants principaux :

  1. Définir la cellule : la cellule que vous souhaitez utiliser comme objectif.
  2. Évaluer : La valeur que vous voulez comme objectif.
  3. En changeant de cellule : La cellule que vous souhaitez modifier pour atteindre la valeur de votre objectif.

Avec ces trois paramètres définis, Excel tentera d'améliorer la valeur dans la cellule que vous avez définie dans En changeant de cellule jusqu'à ce que la cellule que vous avez définie Définir la cellule atteint la valeur que vous avez déterminée dans Évaluer .



Si tout cela semble déroutant, les exemples ci-dessous vous donneront une bonne idée du fonctionnement de Goal Seek.

Exemple de recherche d'objectif 1

Par exemple, disons que vous avez deux cellules (A et B) et une troisième cellule qui calcule la moyenne de ces deux.





Supposons maintenant que vous ayez une valeur statique pour A et que vous souhaitiez augmenter le niveau moyen en modifiant la valeur pour B. En utilisant Goal Seek, vous pouvez calculer quelle valeur de B produira la moyenne souhaitée.

Connexe : Conseils pour gagner du temps dans Microsoft Excel





comment débloquer iphone avec apple watch

Comment utiliser la recherche d'objectif dans Excel

  1. Dans Excel, cliquez sur la cellule C1 .
  2. Dans la barre de formule, saisissez le code suivant : |_+_| Cette formule demandera à Excel de calculer la moyenne des valeurs dans les cellules A1 et B1 et afficher cela dans la cellule C1 . Vous pouvez vous assurer que votre formule fonctionne en entrant des nombres dans A1 et B1. C1 devrait afficher sa moyenne.
  3. Pour cet exemple, modifiez la valeur de A1 cellule à 16 .
  4. Sélectionnez la cellule C1 , puis à partir du ruban, allez à la Données languette.
  5. Sous le Onglet Données , sélectionnez Analyse de simulation puis Recherche d'objectif . Cela fera apparaître la fenêtre de recherche d'objectif.
  6. Dans la fenêtre Recherche d'objectif, définissez le Définir la cellule à C1 . Ce sera votre cellule cible. Après avoir mis en surbrillance C1 avant d'ouvrir la fenêtre de recherche d'objectif, le définira automatiquement dans Définir la cellule .
  7. Dans Évaluer , insérez la valeur d'objectif souhaitée. Pour cet exemple, nous voulons que notre moyenne soit de 26, donc la valeur de l'objectif sera également de 26.
  8. Enfin, dans le En changeant de cellule , sélectionnez la cellule que vous souhaitez modifier pour atteindre l'objectif. Dans cet exemple, ce sera la cellule B2 .
  9. Cliquez sur d'accord pour que Goal Seek opère sa magie.

Une fois que vous avez cliqué sur OK, une boîte de dialogue apparaîtra pour vous informer que Goal Seek a trouvé une solution.

La valeur de la cellule A1 changera pour la solution, et cela écrasera les données précédentes. C'est une bonne idée d'exécuter Goal Seek sur une copie de votre feuille de données pour éviter de perdre vos données d'origine.

En rapport: Des formules Excel folles qui font des choses incroyables

Exemple de recherche d'objectif 2

Goal Seek peut être utile lorsque vous l'utilisez dans des scénarios réels. Cependant, pour utiliser Goal Seek à son plein potentiel, vous devez d'abord avoir une formule appropriée en place.

Pour cet exemple, prenons un petit cas bancaire. Supposons que vous ayez un compte bancaire qui vous donne un intérêt annuel de 4% sur l'argent que vous avez sur votre compte.

En utilisant What-If Analysis et Goal Seek, vous pouvez calculer combien d'argent vous devez avoir sur votre compte pour obtenir un certain montant d'intérêts mensuels.

Pour cet exemple, disons que nous voulons obtenir 350 $ chaque mois grâce aux paiements d'intérêts. Voici comment vous pouvez le calculer :

thème aero glass pour windows 10
  1. En cellule A1 taper Équilibre .
  2. En cellule A2 taper Taux annuel .
  3. En cellule A4 taper Bénéfice mensuel .
  4. En cellule B2 taper 4% .
  5. Sélectionnez la cellule B4 et dans la barre de formule, saisissez la formule ci-dessous : |_+_| Le bénéfice mensuel sera égal au solde du compte multiplié par le taux annuel, puis divisé par le nombre de mois dans une année, 12 mois.
  6. Allez au Données onglet, cliquez sur Analyse de simulation, puis sélectionnez Recherche d'objectif .
  7. Dans le Recherche d'objectif fenêtre, tapez B4 dans Définir la cellule .
  8. Tapez 350 dans le Évaluer cellule. (C'est le bénéfice mensuel que vous souhaitez obtenir)
  9. Taper B1 dans En changeant de cellule . (Cela changera le solde à une valeur qui rapportera 350 $ par mois)
  10. Cliquez sur d'accord . Les Statut de recherche d'objectif le dialogue s'ouvrira.

Dans la boîte de dialogue Statut de la recherche d'objectif, vous verrez que la recherche d'objectif a trouvé une solution dans la cellule B4. Dans la cellule B1, vous pouvez voir la solution, qui devrait être 105 000.

Exigences de recherche d'objectif

Avec Goal Seek, vous pouvez voir qu'il ne peut changer qu'une seule cellule pour atteindre la valeur de l'objectif. Cela signifie que Goal Seek ne peut pas résoudre et atteindre une solution s'il a plus d'une variable.

Dans Excel, vous pouvez toujours résoudre plusieurs variables, mais vous devrez utiliser un autre outil appelé Solveur. Vous pouvez en savoir plus sur Excel's Solver en lisant notre article sur Comment utiliser le solveur d'Excel .

Limites de la recherche d'objectifs

Une formule de recherche d'objectif utilise le processus d'essai et d'amélioration pour atteindre la valeur optimale, ce qui peut poser des problèmes lorsque la formule produit une valeur indéfinie. Voici comment vous pouvez le tester vous-même :

  1. En cellule A1 , taper 3 .
  2. Sélectionnez la cellule C1 , puis dans la barre de formule, saisissez la formule ci-dessous : |_+_| Cette formule divisera un par A1-5, et si A1-5 est égal à 0, le résultat sera une valeur indéfinie.
  3. Allez au Données onglet, cliquez sur Analyse de simulation puis sélectionnez Recherche d'objectif .
  4. Dans Définir la cellule , taper C1 .
  5. Dans Évaluer , taper 1 .
  6. Enfin, dans En changeant de cellule taper A1 . (Cela changera la valeur A1 pour atteindre 1 en C1)
  7. Cliquez sur d'accord .

La boîte de dialogue Goal Seek Status vous dira qu'elle n'a peut-être pas trouvé de solution et vous pouvez vérifier que la valeur A1 est loin.

Maintenant, la solution à cette recherche de but serait d'avoir simplement 6 dans A1. Cela donnera 1/1, ce qui équivaut à 1. Mais à un moment donné au cours du processus d'essai et d'amélioration, Excel essaie 5 dans A1, ce qui donne 1/0, ce qui n'est pas défini, tuant le processus.

Un moyen de contourner ce problème de recherche d'objectif est d'avoir une valeur de départ différente, qui peut échapper à l'indéfini dans le processus d'essai et d'amélioration.

Par exemple, si vous remplacez A1 par un nombre supérieur à 5, puis répétez les mêmes étapes pour la recherche d'objectif pour 1, vous devriez obtenir le résultat correct.

comment activer flash sur mac

Rendez les calculs plus efficaces avec la recherche d'objectifs

What-If Analysis et Goal Seek peuvent accélérer considérablement vos calculs avec vos formules. Apprendre les bonnes formules est une autre étape importante pour devenir un maître Excel.

Partager Partager Tweeter E-mail 15 formules Excel qui vous aideront à résoudre les problèmes de la vie réelle

Excel n'est pas seulement pour les entreprises. Voici plusieurs formules Microsoft Excel qui vous aideront à résoudre des problèmes quotidiens complexes.

Lire la suite
Rubriques connexes
  • Productivité
  • Tableur
  • Microsoft Excel
  • Conseils Microsoft Office
  • L'analyse des données
A propos de l'auteur Amir M. Intelligence(39 articles publiés)

Amir est un étudiant en pharmacie passionné par la technologie et les jeux. Il aime jouer de la musique, conduire des voitures et écrire des mots.

Plus de Amir M. Bohlooli

Abonnez-vous à notre newsletter

Rejoignez notre newsletter pour des conseils techniques, des critiques, des ebooks gratuits et des offres exclusives !

Cliquez ici pour vous abonner