Valeur cible et Solveur Excel : trouver l'entrée pour atteindre un résultat
D'habitude, on entre des valeurs et Excel calcule un résultat. La valeur cible et le Solveur font l'inverse : vous dites quel résultat vous voulez, et Excel cherche les valeurs d'entrée qui y arrivent. Deux outils, deux niveaux de complexité.
La Valeur cible — une variable, un résultat
La valeur cible résout une équation à une inconnue. Elle modifie une seule cellule variable pour qu'une cellule résultat atteigne exactement la valeur souhaitée.
Exemple : quel CA faut-il pour atteindre 50 000 € de bénéfice ?
Supposons :
- B1 = Chiffre d'affaires (entrée)
- B2 = Charges fixes = 30 000
- B3 = Marge (%) = 40%
- B4 = Bénéfice =
=B1*B3-B2
- Données → Analyse de scénarios → Valeur cible
- Cellule à définir :
B4(le bénéfice) - Valeur à atteindre :
50000 - Cellule à modifier :
B1(le CA) - Cliquez OK → Excel trouve que B1 = 200 000 €
La formule B1*0,4-30000 = 50000 → B1 = 200 000. La valeur cible a résolu ça itérativement en quelques millisecondes.
Autres exemples courants
- Finance : quel taux d'intérêt permet une mensualité donnée ? (VPM → chercher le taux)
- RH : quel volume de ventes pour déclencher un bonus ?
- Prix : à quel prix de vente unitaire atteint-on le seuil de rentabilité ?
Limites de la valeur cible
- Une seule cellule variable à la fois
- Pas de contraintes (la variable peut devenir négative ou absurde)
- La cellule résultat doit dépendre directement ou indirectement de la cellule variable
Le Solveur — plusieurs variables, des contraintes
Le Solveur est un complément (fourni avec Excel, à activer) qui optimise une cellule objectif en jouant sur plusieurs cellules variables, avec des contraintes.
Activer le Solveur
Fichier → Options → Compléments → Compléments Excel → Atteindre → cochez Solveur → OK
Le Solveur apparaît ensuite dans l'onglet Données, tout à droite.
Exemple : optimiser un mix produit
Vous fabriquez 3 produits (A, B, C). Chaque produit consomme des heures machine et génère une marge. Vous avez 120 heures disponibles. Quel mix maximise la marge totale ?
| Produit | Heures/unité | Marge/unité | Quantité (variable) |
|---|---|---|---|
| A | 2h | 15 € | B2 |
| B | 3h | 25 € | B3 |
| C | 1h | 8 € | B4 |
Cellule objectif : =B2*15+B3*25+B4*8 (marge totale, à maximiser)
Contraintes à saisir dans le Solveur :
B2*2 + B3*3 + B4*1 <= 120(heures disponibles)B2, B3, B4 >= 0(pas de quantités négatives)B2, B3, B4 = entier(si les quantités doivent être entières)
Méthode de résolution : Simplex LP (problème linéaire). Cliquez Résoudre.
Choisir la bonne méthode de résolution
| Méthode | Quand l'utiliser |
|---|---|
| Simplex LP | Objectif et contraintes linéaires (la plupart des cas de gestion) |
| GRG Non linéaire | Formules avec exposants, logarithmes, multiplications de variables |
| Évolutionnaire | Problèmes non continus, fonctions SI dans les contraintes |
Enregistrer et comparer les scénarios
Après chaque résolution, cliquez Enregistrer le scénario pour garder la solution en mémoire. Vous pouvez ensuite comparer plusieurs scénarios via Données → Gestionnaire de scénarios.
Valeur cible vs Solveur : résumé
| Valeur cible | Solveur | |
|---|---|---|
| Nombre de variables | 1 | Jusqu'à 200 |
| Contraintes | Non | Oui (illimitées) |
| Objectif | Atteindre une valeur précise | Minimiser / maximiser / atteindre |
| Activation requise | Non (natif) | Oui (complément) |
| Complexité | Simple | Avancé |