Le solveur d’Excel est un outil de calcul d’une valeur cible en fonction de variables et de contraintes que l’utilisateur peut paramétrer.
Dans l’exemple ci dessous, on pourra définir quelle quantité de produits A et B on devra recommander auprès de notre fournisseur en fonction de nos stocks déjà établis afin d’atteindre une marge souhaitée. On part du principe qu’on va vendre tout notre stock sans perte.
Vous pouvez télécharger le fichier de l’exemple ci-dessous :
Activer le complément solveur
Le solveur est un complément optionnel d’Excel. Pour l’activer :
Fichier > Compléments > Atteindre
Attention, les compléments sollicitent plus de ressources processeur et RAM que la configuration classique d’Excel au démarrage du logiciel. Il est conseillé de désactiver le solveur quand vous ne l’utilisez plus.
Résoudre un problème avec le solveur
- Définissez une valeur cible (dans notre cas, la marge souhaitée en D2)
La valeur cible est obligatoirement une formule !
On peut définir de l’atteindre au minimum, au maximum ou lui donner une valeur. Dans notre cas, on recherche à atteindre au minimum cette marge.
- Définissez les valeurs variables (celles que le solveur pourra manipuler pour atteindre le résultat souhaité).
Les valeurs D10 et E10 indiquent les quantités à recommander à notre fournisseur pour atteindre un stock suffisant pour réaliser la marge souhaitée.
- Indiquez vos contraintes en cliquant sur « ajouter »
le tableau ci-dessous liste toutes les contraintes à imposer au solveur :
| Contrainte | codage | remarques | ||||||||||||||||||||||||||||||||||||||||||||||||||
| la quantité recommandée doit être inférieure ou égale à la quantité disponible chez le fournisseur | d10<=d9 e10<=e9 | la quantité déjà en stock et les commandes déjà enregistrées n’ont pas d’incidence. | ||||||||||||||||||||||||||||||||||||||||||||||||||
| les quantités commandées doivent être des nombres entier | d10 = entier e10 = entier | le solveur pourrait indiquer des quantités décimales pour s’approcher au mieux de la cible | ||||||||||||||||||||||||||||||||||||||||||||||||||
| la marge à estimer doit être supérieure ou égale à la marge souhaitée. | d2 >= d3 | |||||||||||||||||||||||||||||||||||||||||||||||||||
dans l’aperçu du solveur, cela doit donner ceci :
- Conserver la méthode « GRG non linéaire » par défaut
Il existe trois méthodes de résolution du solveur selon la complexité et le nombre d’itérations (séries de calculs) nécessaires pour atteindre la valeur cible.
Un message apparait :
- conservez le résultat s’il correspond à l’objectif que vous vous étiez fixé :
Ici, le solveur commande 203 produits A et 345 produits B pour atteindre une marge de 150 055 €. Il respecte toutes nos contraintes.
Vous pouvez :
- Rétablir les valeurs d’origine si la solution du solveur ne vous convient pas. Cochez alors « retourner à la boite de dialogue … » pour corriger vos paramètres
- demander un rapport de plan pour obtenir une feuille synthèse de résolution du solveur
- enregistrer le scénario si vous voulez l’utiliser pour d’autres tableaux semblables (ou seules quelques contraintes doivent être corrigées, ou bien encore ou vous souhaitez tester l’atteinte se faisant à minimum au lieu de maximum)
Le solveur est donc un outil idéal pour gérer un problème ou les critères deviennent trop complexes pour une simple fonction conditionnelle.
Autres articles de ce blog sur le tableur :