Excel : Exercice sur le Solveur
Introduction
Activer le Solveur
Ajouter des contraintes
Options du Solveur
Résoudre le problème
Références
Introduction
Le solveur est un outil complémentaire que vous pouvez
utiliser pour optimiser le modèle que vous avez déjà
préparé. Vous pouvez essayer de maximiser vos profits,
minimiser vos pertes ou atteindre un objectif avec le moins
de ressources possibles. Il ne faut surtout pas oublier
d’ajouter les contraintes qui limitent votre modèle. Sinon,
votre résultat ira vers l’infini !
Sélectionnez la feuille de calcul tx de croissance.
Page 1 sur 5
Activer le Solveur
Puisqu’il s’agit d’un modèle complémentaire, il n’est pas disponible dès l’ouverture
d’Excel. Suivez les instructions ci-dessous pour activer le Solveur.
Appuyez sur l’onglet Fichier.
Aller dans Options / Compléments.
De la zone Gérer, sélectionnez l’option
Compléments Excel et appuyez sur le
bouton Atteindre.
De la liste des macros complémentaires,
activez l’option Complément Solver.
Appuyez sur le bouton OK.
C’est à la fin de l’onglet Données que vous allez maintenant retrouver l’outil Solveur.
L’entreprise désire optimiser son profit trimestriel. Mais elle a aussi certaines contraintes
qu’elle doit respecter. Le taux de croissance mensuel de l’entreprise (B21) doit être entre
15% et 150%. La capacité de vente initiale du produit 100 (B3) doit être entre 45 000 $
et 250 000 $. La capacité de vente initiale du produit 200 (B4) doit être entre 10 000 $
et 125 000 $. La capacité de vente initiale du produit 300 (B5) doit être entre 5 000 $ et
75 000 $. Avec ces informations, utilisez le Solveur pour trouver la solution optimale.
Allez à l’onglet Données.
Appuyez sur le nouveau bouton Solveur.
Page 2 sur 5
La cellule à définir est celle que vous
désirez optimiser. Pour cet exemple,
il s’agit de la cellule D19 qui contient
le profit cumulatif pour le trimestre.
Les cellules variables seront B21
(taux de croissance) ainsi que B3,
B4 et B5 (ventes du premier mois).
Veillez noter que les cellules variables
doivent toujours être des cellules
ayant que des chiffres; jamais de
formules. Il faut ensuite entrer les
contraintes.
Appuyez sur le bouton Ajouter et
ajoutez les contraintes suivantes.
Ces dernières vont s’assurer que le
modèle n’aura pas une tendance à se
diriger vers l’infini.
Ajouter des contraintes
Sans contraintes pour chaque variable que vous avez sélectionnez, le Solveur donnerait
une solution allant vers l'infiniment grand ou l'infiniment petit selon votre modèle. Les
contraintes forcent le Solveur à prendre en considération les limites de vos cellules
variables. Le Solveur peut gérer jusqu'à 200 contraintes.
Pour accélérer le processus, toutes les contraintes ont déjà été ajoutées sauf celles
concernant la cellule B3. «il faut déterminer la limite supérieure et inférieure de celle-ci.
Appuyez sur le bouton
Ajouter et ajoutez les
contraintes suivantes.
Dans la case Cellule, entrez
B3.
Dans la case Contrainte,
entrez B29.
Le Solveur vous permet d'utiliser l'une des cinq contraintes suivantes :
Égale à Le contenu de la cellule doit être égal à un montant déterminé.
Supérieur Le contenu de la cellule soit être supérieur ou égale à un montant
ou égale à déterminé. Cela veut dire qu'on détermine le plancher de la cellule puisque
la valeur ne peut être en dessous d'un seuil.
Inférieur ou Le contenu doit être inférieur ou égal à un montant déterminé de la case
égale à contrainte. Cette contrainte détermine la plafond possible puisque rien ne
peut aller au dessus.
Entier Le contenu de la cellule doit donner un chiffre entier; sans aucune fraction.
Bin
Page 3 sur 5
Pour cette exercice, sélectionnez la contrainte >= .
Le contenu de la cellule B29 est la limite supérieure déterminé pour ce modèle. Il est
possible de mettre une valeur dans la cellule. Mais cela n'est pas recommandé. Il est plus
facile de changer une limite dans une cellule que d'avoir à changer une contrainte dans le
Solveur.
Appuyez sur le bouton Ajouter et
ajoutez les contraintes suivantes.
Dans la case Cellule, entrez B3.
Sélectionnez la contrainte <=.
Dans la case Contrainte, entrez
C29.
Options du Solveur
Appuyez sur le bouton Options.
Le solveur vous offre plusieurs options
pour vous aider à le gérer. Vous pouvez
placer un temps maximum pour trouver la
solution optimale. Vous pouvez aussi
déterminer le nombre d’essais, ou
d’itérations, que le solveur peut prendre
pour trouver la solution. Le degré de
précision, de tolérance et convergence
vous aide à déterminer à quel point vous
serez proche de la solution optimale selon
le type de modèle que vous avez. Le
modèle supposé linéaire optimise le solveur
pour des modèles simples. Vous pouvez
aussi choisir parmi plusieurs types
d’estimations, de dérivées et de types de
recherches pour retrouver la solution
optimale qui varie d’efficacité selon les
équations à résoudre.
Activez l'option Échelle automatique.
Pour sortir des options du Solveur, appuyez sur le bouton OK.
Résoudre le problème
Appuyez sur le bouton Résoudre.
Page 4 sur 5
Sélectionnez l’option Rétablir les valeurs d’origines ainsi que tous les rapports.
Appuyez sur le bouton OK.
Excel va générer trois nouvelles feuilles de calcul avec les résultats d’analyse du degré de
réponse et de sensibilité des variables ainsi que leurs limites. Cela vous aidera à
déterminer quelles sont les variables les plus importantes selon leur impact sur votre
modèle.
Page 5 sur 5