Excel | Le Solveur

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 :

Sous Windows :

• Fichier > Compléments > Atteindre

Sous Mac Os :

1. Dans le menu Outils , sélectionnez Compléments Excel.
2. Dans la zone Macros complémentaires disponibles, activez la case à cocher Complément Solveur, puis cliquez sur OK.
   • Si le Complément Solver ne figure pas dans la zone Macros complémentaires disponibles, cliquez sur Parcourir pour le localiser.
   • Si vous recevez une invite indiquant que le complément Solver n’est pas installé sur votre ordinateur, cliquez sur Oui dans la boîte de dialogue pour l’installer.

Les informations ci-dessus proviennent du site Microsoft (je n’ai pas de Mac )

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

Valeur cible

• 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.

Variables et contraintes

• 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 :

Méthode de résolution

Il existe trois méthodes de résolution du solveur selon la complexité, les règles de calcul et le nombre d’itérations (séries de calculs) nécessaires pour atteindre la valeur cible.

Pour choisir une méthode linéaire, il faut être sûr que le calcul respectera des principes de proportionnalité (ce qui n’est pas notre cas, notamment parce qu’on veut un nombre entier de chaque produit), idem pour la méthode simplex.

• Conserver la méthode « GRG non linéaire » par défaut

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 :