Fonction Valeur cible dans Excel : guide complet et exemples

La Fonction Valeur cible dans Excel C'est un de ces outils qu'on ne remarque pas avant de l'avoir découvert, et ensuite on ne peut plus s'en passer. Il permet d'indiquer à Excel : « Voici le résultat que je souhaite obtenir », et le programme calcule automatiquement la valeur que doit avoir une cellule spécifique pour que les calculs soient corrects.

Au lieu d'essayer frénétiquement différents chiffres jusqu'à trouver le bon, vous pouvez utiliser Définir l'objectif pour qu'Excel puisse faire le gros du travailIl est extrêmement utile dans le domaine de la finance (prêts, investissements, budgets personnels), dans les questions académiques (notes, moyennes, bourses) et dans tout calcul où vous avez établi une formule et où il vous manque une inconnue.

Qu'est-ce que la fonction Valeur cible dans Excel ?

Rechercher un objet Cela fait partie des outils de Analyse Et si… Dans Excel, au lieu de vous demander « si je modifie ces données, que va-t-il arriver au résultat ? », vous inversez l'approche : vous indiquez à Excel le résultat que vous souhaitez obtenir et vous lui demandez de calculer la valeur que doit prendre l'une des cellules impliquées dans la formule.

Autrement dit, l'outil fonctionne « Le contraire » de ce que vous feriez normalementVous connaissez déjà la valeur finale souhaitée, et vous laissez Excel trouver la valeur d'entrée qui permet à votre formule de renvoyer ce résultat. Mathématiquement, c'est comme résoudre une équation à une seule inconnue sans avoir à l'isoler manuellement.

Cette fonction est disponible dans Excel et également dans d'autres suites bureautiques telles que Documents ONLYOFFICEoù il a été intégré à leurs feuilles de calcul pour proposer le même type d'analyse de scénarios. Dans tous les cas, la philosophie reste la même : Définissez un objectif, choisissez la variable et laissez le programme itérer. jusqu'à ce qu'une valeur appropriée soit trouvée.

Voici quelques situations typiques où l'utilisation de la fonction Valeur cible est très utile : calculs d'amortissement d'un prêt, planification de l'épargne-retraiteÉlaboration de budgets personnels ou professionnels et analyse des ventes nécessaires pour atteindre un objectif de profit ou un seuil de rentabilité.

Concepts clés avant d'utiliser la fonction Valeur cible

Avant de commencer à utiliser l'outil, il est important de bien comprendre trois idées : la cellule cible, la valeur souhaitée et la cellule qui changeSi vous identifiez correctement ces éléments, le reste est un jeu d'enfant.

La cellule cible Cette cellule contient le résultat d'une formule dont vous souhaitez obtenir une valeur précise. Par exemple, la moyenne de vos notes finales, le montant d'un remboursement mensuel ou le bénéfice total d'un produit. Important : cette cellule doit contenir une formule, et non un nombre écrit à la main.

El Valeur souhaitée Il s'agit du chiffre que vous souhaitez que la cellule cible atteigne. Cela peut correspondre à une moyenne minimale pour maintenir une bourse d'études, au montant maximal du versement mensuel autorisé par votre budget, ou encore au profit que vous vous êtes fixé comme objectif pour votre entreprise. Vous déterminez cette valeur en fonction de votre objectif..

La cellule qui change Il s'agit de l'inconnue du problème, la donnée que vous souhaitez qu'Excel calcule. Cette donnée doit figurer dans la formule de la cellule cible. Il peut s'agir du solde restant dû, du montant de l'emprunt, de la durée de remboursement (en années), du prix de vente, du nombre d'unités vendues, etc.

Il est essentiel que La cellule cible dépend, directement ou indirectement, de la cellule qui se modifie.S'il n'existe aucune relation entre les variables de la formule, la fonction Valeur cible ne trouvera aucun résultat. Concrètement, cet outil remplace la méthode classique par tâtonnement par un processus itératif automatisé.

Comment fonctionne la fonction Objectif de remboursement, étape par étape : exemple de prêt

Un exemple classique pour comprendre la fonction Recherche d'objectif est celui d'un prêt avec remboursements périodiquesImaginez que vous sachiez combien d'argent vous voulez emprunter, en combien de mois vous voulez le rembourser et quel est le montant maximal de remboursement que vous pouvez vous permettre, mais que vous n'ayez aucune idée du taux d'intérêt nécessaire pour que l'opération soit rentable.

Ce type de calcul utilise la fonction PAIEMENT (PMT) La fonction Excel renvoie le montant à payer à chaque période. L'astuce consiste à connaître déjà le montant de la mensualité ; vous utiliserez donc la fonction « Valeur cible » pour qu'Excel ajuste le taux d'intérêt jusqu'à atteindre ce montant.

1. Préparer le tableau de prêt

Premièrement, c'est une bonne idée organiser les données dans un petit tableau Pour que tout reste bien organisé, vous pouvez créer quelque chose comme ceci sur une nouvelle feuille :

• En format A1: « Montant du prêt ». Dans B1: le capital que vous souhaitez demander, par exemple 100000.
• En format A2: « Durée en mois ». B2: le nombre de mois, par exemple 180 (ce qui correspondrait à 15 ans).
• En format A3: « Taux d'intérêt ». Dans B3Pour l'instant, laissez-le vide, car il représentera l'inconnu.
• En format A4: « Paiement ». Dans B4: voici la formule avec PAIEMENT.

Saisissez maintenant la formule dans la cellule B4. =PMT(B3/12;B2;B1)Vous divisez la cellule B3 par 12 car le taux est généralement exprimé en taux annuel, et vous calculez le paiement mensuel. Comme la cellule B3 est vide, Excel considère un taux d'intérêt de 0 % et calcule le paiement mensuel en fonction uniquement de la durée et du montant.

Dans cet exemple précis, vous constaterez peut-être que les frais calculés sont d'environ 555,56Mais cette valeur ne vous concerne pas encore. L'important, c'est que vous ayez déjà… structure de la formule Prêt à l'emploi. Trouver la cible.

2. Utilisez la fonction Valeur cible pour trouver le taux d'intérêt.

La formule étant en place, vous pouvez maintenant ouvrir l'outil d'analyse. Accédez à l'onglet Données et au sein du groupe de commandes Pour les outils de prévision ou de données, sélectionnez Analyse d'hypothèses > Recherche d'objectifsUne boîte de dialogue comportant trois champs s'ouvrira.

Dans le cadre « Définir la cellule » Vous spécifiez la cellule contenant la formule dont vous souhaitez corriger le résultat, ici B4. Il s'agit de votre cellule cible, où se trouve le calcul du paiement mensuel à l'aide de la fonction PMT.

À la campagne « Le courage de » Vous indiquez le montant que vous souhaitez payer. Si vous prévoyez de payer 900 par mois, inscrivez-le ici. - 900Le signe négatif est important car, dans Excel, les paiements effectués de votre poche sont considérés comme des montants négatifs.

Dans le cadre « Cellule changeante » Vous saisissez la référence de la cellule qu'Excel doit ajuster pour obtenir ce paiement. Dans cet exemple, il s'agit de la cellule B3, où sera placé le taux d'intérêt. Lorsque vous cliquez sur OK, la fonction Valeur cible itère et modifie le taux jusqu'à trouver celui qui fixe le paiement mensuel à -900.

Une fois terminé, l'outil vous affichera un fenêtre d'état avec le résultatVous verrez le taux d'intérêt calculé en B3 et le nouveau paiement en B4. Excel trouve généralement une solution assez précise si la formule est correcte et si le problème a un sens numérique.

Pour couronner le tout, vous pouvez formatear cellule B3 en pourcentage Dans l'onglet Accueil, sélectionnez le format pourcentage et ajustez le nombre de décimales à l'aide des icônes d'augmentation et de diminution des décimales. Le taux d'intérêt s'affichera ainsi de manière plus lisible.

Autres exemples d'utilisation : billets à ordre, placements et crédits

La puissance de Goal Seek ne se limite pas aux prêts. Elle peut être appliquée à une multitude de situations quotidiennes où Vous avez un objectif numérique, mais il vous manque une variable. qui répond à cet objectif. Nous allons examiner plusieurs cas pratiques très courants.

1. Savoir quelle note obtenir au dernier examen

L'un des exemples les plus intuitifs est celui de évaluationsImaginez que vous ayez déjà saisi plusieurs notes et qu'il ne vous reste plus qu'un examen. Vous connaissez la moyenne minimale requise pour réussir le cours ou conserver votre bourse, mais vous ignorez quelle note obtenir pour ce dernier examen.

Supposons que vous ayez besoin d'une moyenne de 70 pour pouvoir passer l'examen final ou conserver votre bourse. Vous avez déjà passé 3 examens, il vous en reste un, et vous souhaitez qu'Excel vous indique la moyenne à obtenir. Quelle note dois-tu obtenir à ce quatrième examen ? de sorte que la moyenne générale soit d'au moins 70.

Commencez par saisir les notes connues dans une colonne, par exemple B2:B4. Laissez la cellule B5 vide car elle contiendra la note de l'examen en attente, et dans une autre cellule, disons B7, saisissez la formule de la moyenne : =PROMÉDIO(B2:B5)Ce sera votre cellule cible.

Ouvrir Recherche d'objectif à partir des données > Analyse de scénarios > Recherche d'objectif et Vous le configurez comme ceci:

• Cellule définie: B7 (la cellule contenant la formule de la moyenne).
• Valeur pour: 70 (la moyenne que vous souhaitez atteindre).
• Cellule de changement: B5 (la note pour l'examen en attente).

Après confirmation, Excel calculera la valeur que doit avoir la cellule B5 pour que la moyenne de la cellule B7 soit égale à 70. Par exemple, il pourrait vous indiquer que vous avez besoin de… 85 lors du dernier examen pour atteindre la moyenne requise. Cette même idée peut être utilisée avec les moyennes pondérées à l'aide de fonctions telles que : SOMMEPROD y SUMA, si chaque test a une pondération différente.

2. Moyennes pondérées et notes avec des pondérations différentes

Dans de nombreux cours, les évaluations n'ont pas toutes la même pondération. Par exemple, les devoirs peuvent compter pour 30 %, les quiz pour 20 % et l'examen final pour 50 %. Dans ce cas, au lieu d'une simple moyenne, on utilise généralement une formule de moyenne pondérée.

Une méthode courante consiste à présenter les notes dans une colonne et les pondérations (sous forme décimale ou en pourcentage) dans une autre. Vous pouvez utiliser cette combinaison. SOMMEPROD(notes;poids)/SOMME(poids) pour obtenir la note finale.

La cellule contenant la formule de la moyenne pondérée sera votre cellule cible. Ensuite, comme précédemment, utilisez la fonction Valeur cible pour qu'Excel calcule la moyenne. ajuster la note d'un examen spécifique jusqu'à ce que la note globale atteigne, disons, 50 % ou la valeur requise pour réussir.

En suivant cette approche, vous pourriez découvrir que, par exemple, Vous devez obtenir 57,5 ​​% à l'examen final de sorte que, compte tenu de la pondération de chaque composante, la note du cours atteigne les 50 % requis.

3. Planification des investissements et de l'épargne

La recherche ciblée est également un allié précieux lors de la préparation. plans d'investissement ou d'épargneSi vous connaissez déjà le rendement attendu de vos investissements et le calendrier, vous pouvez vous poser des questions comme : « Combien dois-je cotiser chaque mois pour atteindre un montant cumulé de X ? » ou « Quel montant initial dois-je investir si je veux obtenir un certain rendement ? »

des fonctions financières telles que FV (valeur future), VA (valeur actuelle), TAUX ou le PAIEMENT lui-même. Vous définissez la formule qui décrit votre situation (par exemple, la valeur future d'une série de versements périodiques) et vous laissez le versement mensuel ou le capital initial comme variable.

Une fois le modèle assemblé, utilisez la fonction Recherche de cible pour verrouiller votre position. objectif d'économies ou de rentabilité Dans la cellule de résultat, Excel ajuste ensuite la contribution périodique ou le montant de l'investissement initial. Le principe est le même que pour les prêts, mais appliqué à l'épargne ou aux investissements.

4. Crédits et limitations budgétaires

Pour en revenir aux prêts, on peut aller plus loin. Imaginez que vous ayez calculé avec PAYMENT le montant annuel à payer pour un prêt de 12 000 € sur 20 ans à un taux d'intérêt de 5 %. Le résultat est un paiement d'environ 962,91 € par an, mais… Votre budget maximum est de 900.

Avec la fonction Valeur cible, vous pouvez formuler le problème à l'envers : Vous souhaitez que le paiement annuel soit de -900 Vous souhaitez ajuster le montant du prêt. Configurez l'outil de sorte que la cellule cible soit celle contenant le résultat du PAIEMENT, la valeur souhaitée soit -900 et la cellule à modifier soit le capital emprunté.

Le résultat pourrait être, par exemple, un prêt d'environ 11.216Ainsi, vous savez exactement combien vous pouvez emprunter sans dépasser la limite annuelle de 900 €. Si, au lieu de fixer le capital, vous souhaitez conserver le même montant d'emprunt et le modifier le temps Pour le retour, il vous suffit de spécifier la cellule contenant le nombre d'années ou de mois comme étant celle qui change.

5. Analyse des avantages et des ventes nécessaires

Dans le monde des affaires, la fonction Valeur cible est utile pour répondre à des questions comme :Combien d'unités dois-je vendre pour gagner 50 000 ?« Ou encore, à quel prix dois-je vendre pour atteindre une certaine marge, compte tenu des coûts et de la capacité de production ? »

Vous pouvez élaborer une formule de profit comme celle-ci : (Prix – Coût) × Unités vendues Utilisez-la comme cellule cible. Selon vos besoins, définissez le profit souhaité et laissez la cellule qui change indiquer le nombre d'unités à vendre ou le prix unitaire.

Par exemple, si vous définissez un objectif de profit de 50 000, vous pouvez demander à la fonction « Valeur cible » de calculer le nombre d’unités à vendre. maintenir le prix et le coût fixesOu inversement : fixer un volume de ventes limité par la capacité de production et laisser le prix s’ajuster pour atteindre le profit souhaité.

Différences entre la recherche de but et le solveur

Il est important de comprendre où s'arrête l'utilité. Rechercher un objet Et où commence-t-il ? SolverUn autre module complémentaire Excel très puissant. Tous deux servent à l'analyse de scénarios, mais ils ne résolvent pas les mêmes types de problèmes.

L'objectif de recherche est conçu pour problèmes liés à une seule variableAutrement dit, vous ne pouvez modifier qu'une seule cellule à la fois pour tenter d'atteindre la valeur cible. S'il y a plusieurs inconnues, cette méthode s'avère insuffisante ; c'est là qu'intervient le solveur.

Solver vous permet de travailler avec plusieurs cellules variables simultanémentVous pouvez définir des restrictions (par exemple, interdire les valeurs négatives dans certaines cellules, interdire le dépassement d'une certaine valeur maximale, etc.) et choisir si votre objectif est de maximiser, de minimiser ou d'égaler une valeur spécifique. C'est un outil d'optimisation beaucoup plus complet.

Par exemple, si vous voulez savoir quelle note vous devez obtenir en anglais et en chimie pour que la moyenne de ces deux matières, plus celle des autres, soit de 84, vous êtes confronté à un problème avec deux variablesLa fonction Valeur cible ne fonctionnera pas, mais le solveur, si. Dans ce cas, définissez la cellule cible sur la moyenne, attribuez-lui la valeur 84 et indiquez que seules les notes d'anglais et de chimie peuvent varier.

Un autre exemple typique avec Solver est le maximisation du profit sous contraintes, par exemple lorsqu'une entreprise vend plusieurs services, chacun avec son propre profit, et souhaite trouver la combinaison d'unités à vendre qui maximise le profit total tout en respectant certaines conditions (ventes minimales d'un service, ventes maximales d'un autre, limite globale d'unités, etc.).

Problèmes courants liés à l'utilisation de la fonction Valeur cible et comment les éviter

Bien que la fonction Valeur cible soit très facile à utiliser, il arrive qu'elle ne propose pas de solution ou qu'elle renvoie des résultats étranges. Dans la plupart des cas, Le problème réside dans la configuration ou les propriétés numériques du problème.pas dans l'outil lui-même.

Une cause fréquente est que le La cellule cible ne dépend pas de la cellule changeanteSi la cellule que vous avez indiquée comme variable n'est pas impliquée dans la formule de la cellule cible (directement ou indirectement), Excel n'a rien à ajuster et la recherche de la valeur cible échouera de toute façon.

Ils peuvent aussi vous causer des problèmes références circulairesAutrement dit, il s'agit de formules où une cellule dépend d'elle-même. Cela peut empêcher la convergence des calculs ou désactiver certaines options dans Excel. Il est donc conseillé de vérifier la cohérence de vos formules et d'éviter les boucles récursives incontrôlées.

Un autre point à examiner est le options d'itération Dans Excel, si le nombre maximal d'itérations ou la variation maximale autorisée entre les itérations est trop faible, la fonction Valeur cible risque d'abandonner prématurément ou de fournir une approximation inexacte. Ces paramètres se règlent dans Fichier > Options > Formules, en augmentant le nombre maximal d'itérations ou en ajustant la variation maximale.

Enfin, il convient de vérifier que le L'objectif que vous proposez doit avoir un sens numérique.Si vous demandez quelque chose d'impossible compte tenu des limites de votre modèle (par exemple, un paiement mensuel irréaliste pour un prêt énorme à un taux d'intérêt très élevé), il est normal qu'Excel ne puisse trouver aucune valeur adaptée.

L'outil Valeur cible d'Excel, utilisé à bon escient, devient un raccourci puissant pour répondre à toutes sortes de questions numériques : il permet de passer de « que se passe-t-il si je change ceci ? » à « voici ce que je veux obtenir, dites-moi ce que je dois changer », et ainsi de suite. Cela vous évite de perdre beaucoup de temps en essais et erreurs. aussi bien pour gérer vos finances personnelles que pour analyser votre entreprise ou vos études.