Excel : rechercher une valeur pour un résultat attendu (valeur cible)

  • Temps de lecture :4 mins read

Vous vous rappelez des problèmes mathématiques à l’école ?  Avec Excel, plus besoin de se creuser les méninges, le tableur les résout pour vous.

Utilité et fonctionnement

La fonctionnalité d’Excel valeur cible recherche la valeur d’une variable servant à réaliser un objectif par le biais d’une formule de calcul.Son fonctionnement est l’inverse d’une formule « standard ».
  • Formule standard :
Nous avons les variables -> Nous utilisons une formule de calcul -> On obtient un résultat
  • Valeur cible :
Nous avons un résultat attendu -> Nous appliquons la formule de calcul > On obtient la variable à atteindre

Utiliser la valeur cible

Pour mettre en pratique, il vous faut au minimum utiliser 2 cellules sur votre classeur Excel :
  • 1 cellule contenant la formule de calcul
  • 1 cellule contenant la valeur cible à rechercher.
Personnellement, pour être clair, je préfère ajouter autant de cellules que de variables déjà connues et 1 cellule pour la résultat attendu.Ensuite, quand tout est prêt,
  1. Allez dans l’onglet Données du ruban Excel
  2. Dans le groupe Prévision, sélectionnez Analyse scénarios puis Valeur cible…
  3. Dans la nouvelle fenêtre, remplissez les champs nécessaires :
  • Cellule à définir : correspond à la cellule contenant la formule de calcul
  • Valeur à atteindre : résultat attendu. Champ accepte uniquement les nombres
  • Cellule à modifier : correspond à la valeur inconnue (à trouver)

Exemples

Abonnement

Je visite une ville pendant mes vacances. Pour me déplacer, je préfère utiliser une voiture de location. J’ai alloué un budget de 30 € pour le stationnement du véhicule. Or, le coût à la minute est de 0.20 €. Combien de temps je peux laisser le véhicule au parking ?On obtient les variables suivantes :
  • Cellules à définir : formule de calcul située en cellule B3
  • Valeur à atteindre : budget alloué soit 30
  • Cellule à modifier : nombre de minutes en cellule B1
Calcul de la valeur cible

En cliquant sur OK, Excel lance le simulateur d’hypothèses pour trouver la valeur cible.

Dans notre exemple, il trouve 150. Soit, pour 30€ de budget, je peux laisser le véhicule pendant 150 minutes au parking. Si vous acceptez cette valeur cible, cliquez sur OK.

résultat valeur cible

Chiffre d’affaires / Bénéfice

Je veux monter ma propre entreprise individuelle avec la fabrication de produits artisanaux. Pour étudier la viabilité de mon projet, je fais une étude rapide de rentabilité.Voici les données importantes :
  • Chaque produit me coûte 15 € pour sa fabrication
  • Le prix de vente est fixé à 25 €
  • Quoiqu’il arrive, j’ai 15 000 € de charges mensuelles fixes (loyer, salaires du personnel…)
  • Je veux obtenir 2 000 € de bénéfice chaque mois qui sera ma rémunération.
Passons à la saisie sur une feuille Excel. Evidemment, je pourrais simplifier avec le strict nécessaire soit seulement 2 cellules : valeur cible + cellule à définir (formule) mais je préfère vous montrer les détails pour mieux visualiser le cheminement.Tout d’abord, le nombre de produits que je dois vendre pour atteindre le bénéfice attendu Ensuite, je détaille les produits soient le prix de vente unitaire et le chiffre d’affaires. Je continue cette fois avec les différentes charges : variables (selon le nombre de produits vendus) et fixes pour obtenir le coût de revient. Enfin, je souhaite afficher le résultat calculé grâce aux données ci-dessus et le bénéfice attendu.
Exemple calcul d'un bénéfice attendu

On peut maintenant passer à nos simulations avec les variables suivantes :

  • Cellules à définir : formule de calcul située en cellule B10
  • Valeur à atteindre : bénéfice attendu soit 2000
  • Cellule à modifier : nombre de minutes en cellule B1

Avez-vous remarqué pour pour le champ cellules à définir, la formule de calcul ne tient pas compte de tous les calculs (chiffre d’affaires, coût variable, coût de revient) mais uniquement la formule de calcul pour le résultat ?

Il n’est pas nécessaire d’avoir l’intégralité de la formule de calcul dans une cellule unique à partir du moment que l’ensemble des cellules contenant une formule sont liées et que le champ cellule à définir correspond à la « formule finale » et l’objectif attendu.

En lançant Valeur cible d’Excel, le tableur fait sa recherche d’hypothèses et déduit que pour atteindre un bénéfice de 2000 €, il est nécessaire de vendre 1700 produits.