Comment utiliser la recherche d’objectifs dans Microsoft Excel pour les scénarios de simulation

L'analyse des hypothèses 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 propose trois types d'outils d'analyse des hypothèses: le gestionnaire de scénarios, la recherche d'objectifs et le tableau de données. Avec la recherche d'objectifs, vous pouvez déterminer de quelle entrée vous avez besoin pour convertir la formule à l'envers en une certaine sortie.

La fonctionnalité de recherche d'objectif dans Excel est un essai et une erreur, donc si elle ne produit pas ce que vous voulez, Excel travaille à l'amélioration de 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. Pour valoriser : la valeur que vous souhaitez 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 de la cellule que vous avez définie dans En changeant de cellule jusqu'à ce que la cellule que vous définissez dans Définir la cellule atteigne la valeur que vous avez déterminée dans la valeur À .

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'objectifs 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 de B. En utilisant la recherche d'objectif, vous pouvez calculer quelle valeur de B produira la moyenne souhaitée.

Connexes: Conseils pour gagner du temps dans Microsoft Excel

Comment utiliser la recherche d'objectifs dans Excel

1. Dans Excel, cliquez sur la cellule C1 .
2. Dans la barre de formule, saisissez le code suivant:

    =AVERAGE(A1:B1)

   Cette formule permettra à Excel de calculer la moyenne des valeurs dans les cellules A1 et B1 et de la sortir dans la cellule C1 . Vous pouvez vous assurer que votre formule fonctionne en saisissant des nombres dans A1 et B1. C1 doit montrer leur moyenne.

3. Pour cet exemple, modifiez la valeur de la cellule A1 sur 16 .
4. Sélectionnez la cellule C1 , puis à partir du ruban, accédez à l'onglet Données .
5. Sous l' onglet Données , sélectionnez Analyse des hypothèses , puis Recherche d'objectifs . Cela fera apparaître la fenêtre de recherche d'objectifs.
6. Dans la fenêtre Recherche d'objectifs, définissez la cellule Définir sur C1 . Ce sera votre cellule de but. Le fait d'avoir mis C1 en surbrillance avant d'ouvrir la fenêtre de recherche d'objectif le placera automatiquement dans la cellule Set .
7. Dans À valeur , insérez la valeur de l'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 la cellule En changeant , sélectionnez la cellule que vous souhaitez modifier pour atteindre l'objectif. Dans cet exemple, ce sera la cellule B2 .
9. Cliquez sur OK pour que la recherche d'objectifs 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 passera à la solution, ce qui écrasera les données précédentes. C'est une bonne idée d'exécuter Goal Seek sur une copie de votre fiche technique pour éviter de perdre vos données d'origine.

Connexes: Formules Excel folles qui font des choses incroyables

Exemple de recherche d'objectifs 2

La recherche d'objectifs 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 mettre en place une formule appropriée.

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.

À l'aide de l'analyse hypothétique et de la recherche d'objectifs, vous pouvez calculer le montant d'argent dont vous avez besoin dans votre compte pour obtenir un certain montant d'intérêt mensuel.

Pour cet exemple, disons que nous voulons obtenir 350 $ chaque mois sur les paiements d'intérêts. Voici comment vous pouvez calculer cela:

1. Dans la cellule A1, tapez Balance .
2. Dans la cellule A2, type Taux annuel .
3. Dans la cellule A4, tapez Bénéfice mensuel .
4. Dans la cellule B2, tapez 4% .
5. Sélectionnez la cellule B4 et dans la barre de formule, entrez la formule ci-dessous:

    =B1*B2/12

   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 dans l'onglet Données , cliquez sur What-If Analysis, puis sélectionnez Goal Seek .
7. Dans la fenêtre Recherche d'objectifs , tapez B4 dans la cellule Définir .
8. Tapez 350 dans la cellule de valeur À . (C'est le bénéfice mensuel que vous souhaitez obtenir)
9. Tapez B1 dans En changeant de cellule . (Cela changera le solde en une valeur qui rapportera 350 $ par mois)
10. Cliquez sur OK . La boîte de dialogue État de la recherche d'objectif apparaîtra.

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

Exigences de recherche d'objectifs

Avec la recherche d'objectif, vous pouvez voir qu'il ne peut modifier qu'une seule cellule pour atteindre la valeur de l'objectif. Cela signifie que la recherche d'objectifs ne peut pas résoudre et atteindre une solution si elle a plus d'une variable.

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

Limitations 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. Dans la cellule A1 , tapez 3 .
2. Sélectionnez la cellule C1 , puis dans la barre de formule, entrez la formule ci-dessous:

    =1/(A1-5)

   Cette formule divisera un par A1-5, et si A1-5 se trouve être 0, le résultat sera une valeur indéfinie.

3. Accédez à l'onglet Données , cliquez sur Analyse des hypothèses , puis sélectionnez Recherche d'objectifs .
4. Dans la cellule Définir , tapez C1 .
5. Dans la valeur À , tapez 1 .
6. Enfin, dans En changeant le type de cellule A1 . (Cela changera la valeur A1 pour atteindre 1 en C1)
7. Cliquez sur OK .

La boîte de dialogue État de la recherche d'objectif vous indiquera qu'elle n'a peut-être pas trouvé de solution et vous pouvez vérifier que la valeur A1 est bien différente.

Maintenant, la solution à cette recherche d'objectif serait simplement d'avoir 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 en A1, ce qui donne 1/0, qui est indéfini, tuant le processus.

Un moyen de contourner ce problème de recherche d'objectifs est d'avoir une valeur de départ différente, une valeur 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.

Rendre les calculs plus efficaces avec la recherche d'objectifs

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