La valeur cible dans Excel
D’habitude, on saisit des données et Excel calcule le résultat. La valeur cible fait l’inverse : on fixe le résultat voulu et Excel cherche la donnée qui permet de l’atteindre. Combien de stagiaires pour dégager 1 000 € de marge ? Quel montant emprunter pour une mensualité de 350 € ? Quelle note obtenir à l’examen pour avoir 12 de moyenne ?
CommandeDonnées › Analyse de scénarios
ClavierAlt, S, W, G
Variables1 seule cellule
Plusieurs variablesSolveur
1
Le principe de la valeur cible
La boîte de dialogue Valeur cible demande trois informations :
| Champ | Ce qu’il faut indiquer | Condition |
|---|---|---|
| Cellule à définir | Le résultat à atteindre (marge, mensualité, moyenne…) | Doit contenir une formule |
| Valeur à atteindre | Le nombre souhaité (1000, 350, 12…) | Une valeur tapée, pas une référence |
| Cellule à modifier | La donnée qu’Excel a le droit de changer | Doit contenir une valeur et intervenir, directement ou non, dans la formule |
Excel essaie alors des valeurs successives dans la cellule à modifier (jusqu’à 100 essais) jusqu’à ce que la formule donne le résultat voulu, à 0,001 près.
Analyse « à rebours »La valeur cible évite de réécrire l’équation à la main : inutile de savoir isoler x, Excel procède par approximations successives.
Préparer le modèleLe calcul doit déjà fonctionner dans la feuille, avec les données dans des cellules séparées : jamais de nombres « en dur » dans les formules.
Simulateur : Données › Analyse de scénarios › Valeur cible
Valeur cible
État de la recherche d’objectif
Valeur cible :Valeur actuelle :
Voir les essais successifs d’Excel
2
Utiliser la valeur cible pas à pas
- Construisez le modèle : données dans des cellules, résultat calculé par une formule qui en dépend.
- Cliquez sur la cellule résultat (elle sera proposée dans Cellule à définir).
- Données › Prévision › Analyse de scénarios › Valeur cible.
- Tapez la valeur à atteindre, cliquez dans Cellule à modifier puis sur la cellule de donnée, et validez par OK.
- La boîte État de la recherche d’objectif affiche le résultat. OK conserve la nouvelle valeur dans la feuille, Annuler rétablit l’ancienne.
OK remplace la donnéeAprès OK, la valeur d’origine est écrasée. Notez-la ou faites Ctrl + Z pour revenir en arrière. Pour comparer plusieurs hypothèses, utilisez plutôt le Gestionnaire de scénarios.
Arrondir le résultatExcel trouve souvent une valeur décimale (7,18 stagiaires). Arrondissez selon le bon sens : il faut 8 stagiaires pour atteindre au moins 1 000 € de marge.
3
Trois cas pratiques
| Question | Cellule à définir | Valeur | Cellule à modifier | Réponse |
|---|---|---|---|---|
| Combien de stagiaires pour 1 000 € de marge ? | Marge =B5-B6 | 1000 | Nombre de stagiaires | 7,18 → 8 stagiaires |
| Et à 6 stagiaires, quel prix demander ? | Marge | 1000 | Prix par stagiaire | 526,67 € |
| Quel montant emprunter pour 350 € par mois ? | Mensualité =VPM(B2/12;B3;-B1) | 350 | Montant emprunté | 15 501,09 € |
| Sur combien de mois pour 20 000 € ? | Mensualité | 350 | Durée (mois) | 63,5 → 64 mois |
| Quelle note à l’examen (coef. 2) pour 12 de moyenne ? | Moyenne | 12 | Note d’examen | 14 |
Relation non linéaireLa mensualité ne varie pas proportionnellement à la durée : Excel procède alors par plusieurs essais, visibles dans le simulateur (« Voir les essais successifs »).
Vérifier la vraisemblanceDemandez 18 de moyenne : Excel trouve 26 à l’examen, une note impossible. La valeur cible ne connaît pas les contraintes du réel : le Solveur permet d’en ajouter.
4
Limites et messages d’erreur
| Message ou situation | Cause | Solution |
|---|---|---|
| « La cellule doit contenir une formule. » | La cellule à définir est une simple valeur. | Choisissez la cellule qui calcule le résultat. |
| « La cellule doit contenir une valeur. » | La cellule à modifier contient une formule. | Choisissez une donnée saisie, en amont du calcul. |
| « …n’a peut-être pas trouvé de solution. » | Objectif impossible (mensualité inférieure aux seuls intérêts) ou résultat qui ne dépend pas de la cellule modifiée. | Vérifiez le lien entre les cellules et le réalisme de l’objectif. |
| Solution approchée (999,9996) | Excel s’arrête à 0,001 près ou après 100 itérations. | Fichier › Options › Formules : augmentez les itérations, réduisez l’écart maximal. |
Une seule variableLa valeur cible ne modifie qu’une cellule. Pour faire varier plusieurs données sous contraintes (prix entre 400 et 600 €, 12 stagiaires maximum), utilisez le Solveur (complément à activer).
Dans le simulateurEssayez « Cellule à définir » = une donnée, ou « Cellule à modifier » = une formule, puis l’exemple « Prêt » avec 60 € par mois sur la durée : vous obtenez les trois messages.
Exercice de synthèse : trois recherches d’objectif
Télécharger le fichier d’exercice (Énoncé et Corrigé)
- Feuille Rentabilité : nombre de stagiaires pour une marge de 1 000 € (7,18, donc 8). Annulez, puis cherchez le prix pour 6 stagiaires (526,67 €).
- Feuille Prêt : montant empruntable pour une mensualité de 350 € sur 48 mois (15 501,09 €), puis durée nécessaire pour 20 000 € (63,5 mois).
- Feuille Examen : note à obtenir pour 12 de moyenne (14). Essayez 18 et commentez le résultat.
- Comparez vos réponses avec la feuille Corrigé, où la formule inverse permet de vérifier chaque résultat.