Université FERHAT Abbas Sétif -1- Institut
1ére année Master : Métrologie Module : Modélisation et analyse des résultats sous Excel
Chapitre 3 : L et la simulation sous Excel
Introduction : Solver » proposé par Excel
« Solver » peut être installé comme suite :
Clique sur le bouton rond dans le haut-gauche de la fenêtre Excel et clique sur le bouton « Options Excel »
:
o Compléments »
o Ensuite, choisir dans la liste « Gérer Compléments Excel »
o Enfin, clique sur le bouton « »
Complément Solver » et on clique sur « OK »
optimisation Données ».
1. Le
L'application solveur, intégrée à Microsoft Excel, permet d'effectuer des simulations. Il faut cependant modéliser
préalablement le problème sur une feuille de calcul, est la partie la plus ardue ; si le problème est bien posé,
l'utilisation du solveur est très simple. Donc, il faut consacrer le temps nécessaire à une analyse approfondie du
problème avant de commencer à travailler
Le Solveur permet de trouver une valeur optimale pour une formule dans une cellule, appelée cellule cible, d'une
feuille de calcul. Il fonctionne avec un groupe de cellules associées, soit directement, soit indirectement, à la
formule de la cellule cible. Il adapte les valeurs des cellules à modifier, appelées cellules variables, pour fournir le
résultat spécifié à partir de la formule de la cellule cible.
2. Les paramètres du solveur
Après avoir ouvrir le Solver, sa fenêtre
sera identique à l'image à coté :
2.1. Cellule cible à définir
Spécifie la cellule cible à laquelle on
veut attribuer (par la partie « égale à »
de la fenêtre à coté) une valeur
spécifique (tapez cette valeur), ou
minimiser (un cout), ou maximiser (un
gain). Cette cellule doit contenir une
formule. Pour le faire, il faut cliquer sur
le petit-bouton à sa droite et rechercher
la cellule contenant cette formule dans
la feuille de calcul. Après la sélection,
il faut cliquer sur ce petit-bouton pour
revenir à la fenêtre principale.
2.2. Cellules variables
Spécifie les cellules susceptibles d'être modifiées jusqu'à ce que les contraintes d'un problème soient satisfaites et
que la cellule indiquée dans la zone « Cellule cible à définir » atteigne sa valeur-cible. Les cellules variables
doivent être liées directement ou indirectement à la cellule cible par la formule inscrite dans celle-ci. Pour ce faire,
il faut cliquer sur le petit-bouton à droite pour rechercher cette formule dans la feuille de calcul. Après la sélection,
il faut cliquer sur ce petit-bouton pour revenir à la fenêtre du Solveur.
Le bouton « Proposer » aide à trouver les cellules variables en déterminant toutes les cellules liées aux formules
auxquelles fait référence la formule figurant dans la zone « Cellule cible à définir » et place leurs références dans la
zone « Cellules variables ».
1/8
2.3. Contraintes
Répertorie les restrictions courantes imposées au problème
mathématique auquel doit se pencher le solveur.
Pour ajouter une contrainte il faut cliquer sur le bouton
« Ajouter ». La fenêtre illustrée à coté devrait s'afficher :
Dans la zone « Cellule », il faut entrer la référence de la cellule ou le nom de la plage de cellules dont la valeur doit
être soumise à une contrainte. Pour le faire, il faut cliquer sur le petit-bouton à droite pour rechercher cette cellule
dans la feuille de calcul. Après la sélection, on clique sur ce petit-bouton pour revenir à la fenêtre du solveur.
Ensuite, par le menu déroulant situé au centre de la fenêtre, on choisit la relation désirée (plus petit ou égal ( <= ),
égal ( = ), plus grand ou égal ( >= ), ent ( nombre entier ) ou bin ( binaire (vrai ou faux))) qui sera définie entre la
cellule référencée et la contrainte. Si on clique sur ent, « entier » s'affiche dans la zone Contrainte. Si l'on clique
sur bin, « binaire » s'affiche dans la zone Contrainte.
Dans la zone Contrainte, il faut entrer un nombre, une référence ou un nom de cellule ou bien une formule. Pour le
faire, il faut cliquer sur le petit-bouton à droite pour rechercher cette formule dans la feuille de calcul. Après la
sélection, il faut cliquer sur ce petit-bouton pour revenir à la fenêtre du solveur.
Pour valider la contrainte ou en ajouter une autre, il faut cliquer sur le bouton « Ajouter ». Pour valider la
contrainte et revenir à la fenêtre du solveur, il faut cliquer sur « OK ».
Pour modifier ou supprimer une contrainte, on clique sur la contrainte concernée et on clique sur le bouton
« Modifier » pour apporter des modifications ou sur « Supprimer » pour l'éliminer du calcul.
2.4. Résoudre
Cliquez sur ce bouton pour démarrer le processus de résolution du problème défini. Une fenêtre s'affichera,
effectuant des itérations jusqu'à ce que le solveur trouve une solution logique. Pour conserver les valeurs de la
solution dans la feuille de calcul, il faut cliquer dans la boîte de dialogue « Résultat du solveur » sur « Garder la
solution du solveur ». Dans le cas contraire, pour rétablir les données d'origine, il faut cliquer sur « Rétablir les
valeurs d'origine ».
2.5. Options
Affiche la boîte de dialogue
« Options du Solveur » dans laquelle
vous pouvez charger et enregistrer
des modèles de problème, ainsi que
contrôler des fonctionnalités
avancées du processus de résolution
affichées et expliquées ci-dessous :
Durée de résolution et nombre d'itérations : dans la zone Temps max, il est possible de choisir la durée
maximale en secondes autorisée pour le processus de résolution. Dans la zone Itérations, il est possible de
choisir le nombre maximal d'itérations autorisées.
Remarque : si le processus de résolution atteint la durée maximale ou le nombre d'itérations maximal avant
que le Solveur ait trouvé la solution, la boîte de dialogue « Affichage d'une solution intermédiaire » s'affiche.
Degré de précision : dans la zone Précision, il est possible d'ajuster le degré de précision souhaité : plus le
nombre est petit, plus la précision est élevée.
Tolérance des nombres entiers : dans la zone Tolérance, il est possible d'ajuster le pourcentage d'erreur
autorisé dans la solution (si elle est exigée en nombre entier).
2/8
Degré de convergence : dans la zone Convergence, il est possible de choisir le niveau de modifications
relatives autorisé dans les cinq dernières itérations avant que le Solveur s'arrête pour proposer une solution :
plus le nombre est petit, plus le niveau de modifications relatives permis est faible.
Il est aussi possible d'utiliser le bouton « Aide » dans la boîte de dialogue pour obtenir plus d'informations
sur les autres options.
prix de vente comme suite :
Produit A Produit B Produit C
10 DA 16 DA 20 DA
Prix de vente 15 DA 24 DA 30 DA
Le commerçant veut savoir la quantité optimale produit afin de maximiser le bénéfice,
cela dans la limite d'une contrainte budgétaire totale de 2000 DA.
Solution :
1- Les données du problème à résoudre et sa modélisation préalable :
2- Paramétrage du Solveur :
3- La solution optimale trouvée est :
3/8
Introduction
Les simulations par : les fonctions « Valeur cible », les tables et les scénarios consistent à faire varier des
paramètres, puis à examiner les résultats obtenus en fonction des différentes valeurs testées.
1. La fonction « valeur cible »
Elle permet de connaître quelle doit être la valeur contenue dans une cellule pour atteindre une
valeur définie dans une autre cellule (cellule résultat, contenant généralement une formule).
Exemple cellule à modifier) pour obtenir un bénéfice
donné (valeur cible, contenue dans la cellule à définir).
Autrement dit, en utilisant les expressions de la fenêtre « Valeur cible », la fonction « Valeur cible » indique, en
fonction de la « valeur cible » contenue dans la « cellule à définir », quelle doit être la valeur de la « cellule à
modifier ».
La cellule à modifier ne doit pas contenir de formule, juste une valeur.
La cellule à définir contient une formule dépendante, directement ou indirectement, de la valeur de la cellule à
modifier.
Modélisez ces données sur une feuille de calcul :
La cellule B3 contient la formule =B1*B2
La cellule B5 contient la formule =B4*B2
La cellule B7 contient la formule =B3-B5-B6
On a donc, en valeurs : B7 = (400 * B2) (80 * B2) 150
Cette expression est équivalente à : B2 = (B7 + 150) / 320
A une valeur « cible » de bénéfice (cellule B7), la fonction « Valeur cible » renvoie en résultat la valeur du
nombre de toiles à vendre (cellule B2).
La cellule à définir est B7, la valeur à atteindre est 2000, la cellule à modifier est B2.
Application de la fonction « Valeur cible »
Affichez la fenêtre « Valeur cible » : Onglet « Données » groupe « Outils de données » bouton «Analyse
de scénarios » choisir dans la liste « Valeur cible ».
Cellule à définir : cliquez sur son petit-bouton et sélectionnez la cellule à définir :B7)
Valeur à atteindre : saisissez la valeur souhaitée .
Cellule à modifier : cliquez sur son petit-bouton et sélectionnez la cellule à modifier (dans ce cas:B2)
À la fin, Validez par le bouton « OK ».
La fenêtre « Etat de la recherche
Si la recherche a abouti à une solution, le
tableau de simulation est rempli.
Dans , on obtient le tableau suivant :
Pour obtenir un bénéfice
Autres simulations :
4/8
2. Les tables de données
Lorsque vous utilisez des tables de données, vous effectuez une analyse de simulation
montre comment la modification des valeurs dans les cellules variables dans vos formules peut affecter le résultat
de ces formules (sans avoir à modifier ou recopier les formules), et ainsi de comparer les résultats des différentes
variations de ces données entrantes.
Calcul du salaire net suivant : un salaire de base (fixe), une commission et un C Affaires
A- Table de simulation à 1 entrée :
1- Dans une nouvelle feuille de calcul, entrez les
valeurs du tableau à coté, les valeurs à calculer sont :
Le montant de la commission : D5=C5*D2/100
Le salaire Total : E5=B5+D5, recopier le vers le bas.
2- Sélectionnez les cellules C5 à D10 c'est-à-dire la
simulation (Taux %), la formule à simuler (D5) et
les cellules résultantes.
3- Passez la commande : Données Analyse de
scénarios
4-
5- Cliquez sur OK
6- Si vous analyser la formule contenue dans une
cellule résultante, elle devrait se présenter sous la
: {=TABLE(;C5)}
B- Table de simulation à 2 entrées
exemple précédent mais
cette fois avec deux variables, une en ligne et une
donné.
1- Ouvrez une nouvelle feuille de calcul
dans Excel et entrez les valeurs suivantes dans les
cellules A1 à F15. (La valeur en rouge est
obtenue par la formule de calcul décrite ci après).
2- Le salaire Total en A5 = B2 + (B1* B3)
3- Sélectionnez les cellules A5 à F15 c'est-à-dire
la plage contenant
simulation (Les CA et les fixes) la formule à
simuler (A5) et les cellules résultantes.
4- Passez la commande Données / Analyse de
5-
$B$2) et la
6- Cliquez sur OK
5/8
7- Si vous analyser la
formule contenue dans une
cellule résultante (par
exemple D10), elle devrait se
présenter sous la forme
{=TABLE(B2;B1)}
3. Les scenarios
Vous avez créé un modèle qui répond à vos
besoins. On vous offre plusieurs possibilités
pour améliorer votre performance. Mais
laquelle est la meilleure ? Vous pourriez les
essayer dans votre modèle l'un après l'autre.
Cependant, le gestionnaire de scénarios
vous permet de comparer rapidement
plusieurs hypothèses, ou scénarios pour
utiliser le terme d'Excel, et de générer un
tableau de synthèse avec les résultats.
La méthode des scénarios permet de faire
varier de nombreux paramètres (
paramètres).
Attention : Cette commande est seulement
utile que si vous avez déjà un modèle
complet et opérationnel. Aussi, toutes les
variables doivent se retrouver sur une même
feuille de calcul.
On vous offre ci-dessous trois propositions pour augmenter la rentabilité de l'entreprise pour le trimestre.
Scénario 1 : Taux de croissance de 20%, mais les ventes du produit 300 commencent à 50 000 $.
Scénario 2 : Taux de croissance de 30%, mais les ventes du produit 200 commencent à 50 000 $.
Scénario 3 : Taux de croissance de 40%, mais les ventes du produit 100 commencent à 95 000 $.
Voici les adresses des cellules nécessaires pour réaliser l'exercice sur le gestionnaire de scénarios.
Le taux de croissance: B21
Produit 100: B3
Produit 200: B4
Produit 300: B5
Laquelle est la plus intéressante pour
l'entreprise ? Dans ce cas, on veut
savoir quel scénario va donner le
produit cumulatif le plus élevé. Ce
chiffre se trouve dans la cellule D19.
nglet Données groupe «
Outils de données » bouton
Analyse de scénarios option
Gestionnaire de scénarios.
De la fenêtre du gestionnaire de
scénarios, clic sur « Ajouter »
Les premières étapes de création d'un scénario consistent à donner un nom au scénario et de déterminer quelles
seront les cellules variables. Ce sont les cellules dont vous désirez changer les valeurs. Pour le premier scénario, il
s'agit des cellules B21 et B5.
6/8
Dans la case Nom du scénario, entrez le texte : Scénario 1.
Dans la case Cellules variables, entrez les cellules B21; B5.
Saisissez éventuellement un commentaire au scénario.
Les deux options « Changements interdits » et « Masquer » ne seront modifiées que si la feuille est
A la fin, Validez. La fenêtre « Valeurs de scénarios
Validez, ou ajoutez un autre scénario (ce qui validera le scénario précédent).
Il y a deux façons de choisir plusieurs cellules pour un scénario.
Dans la case Cellules variables, entrez chaque adresse de cellule en les séparant avec un ";". OU
Cliquez dans la case Cellules variables + cliquez sur la première cellule à sélectionner + pression sur la
touche Ctrl et cliquez sur les autres cellules que vous avez besoin pour ce scénario + cliquez sur OK.
Attention :
des cellules ayant des chiffres; jamais des formules. Sinon, le gestionnaire de scénarios va écraser vos formules lors
de la préparation du rapport de synthèse. Votre modèle ne sera plus valide.
Il faut maintenant entrer les valeurs appropriées de
chaque scénario dans les cases :
Dans la case B21, entrez le chiffre 0,2 ou 20%.
Dans la case B5, entrez le chiffre 50000.
Appuyez sur le bouton Ajouter.
Entrez les informations sur le second scénario : Entrez les informations sur le troisième scénario :
Nom du scénario : Scénario 2. Nom du scénario : Scénario 3.
Cellules variables : B21; B4. Cellules variables : B21; B3.
Appuyez sur le bouton OK. Appuyez sur le bouton OK.
Dans la case B21, entrez le chiffre 0,3 ou 30%. Dans la case B21, entrez le chiffre 0,4 ou 40%.
Dans la case B4, entrez le chiffre 50000. Dans la case B3, entrez le chiffre 95000.
Appuyez sur le bouton Ajouter. Appuyez sur le bouton OK.
En changeant les quantités ou les prix (valeurs de cellules B2:C7), on obtiendra .
Afficher le tableau de synthèse
Les trois scénarios ont été entrés. Il faut maintenant déterminer
un tableau de synthèse. Ce tableau va vous montrer un résumé
des cellules variables ainsi que des cellules dont vous désirez
voir le résultat.
Appuyez sur le bouton Synthèse.
Il faut maintenant déterminer quelles sont les cellules dont
vous désirez voir le résultat dans le rapport de synthèse. Ces
cellules doivent toujours être des cellules ayant des formules.
Pour les besoins de cet exercice, sélectionnez les
cellules D19, B25, B26 et B27.
Ces cellules contiennent le profit cumulatif pour le trimestre (D19), le minimum (B25), le maximum (B26) et la
moyenne (B27) des bénéfices pour le trimestre.
Assurez- Synthèse de scénario + Appuyez sur le bouton OK.
Une nouvelle feuille de calcul va être générée avec les résultats des scénarios. La première colonne indique les
cellules que vous avez utilisées pour les scénarios ainsi que les cellules dont vous vouliez voir le résultat en
synthèse. Les cellules grises sont celles dont les valeurs sont différentes que les valeurs actuelles du modèle. Selon
L'option Afficher
Cette option a l'avantage de vous permettre de voir l'impact d'un scénario sur tout votre modèle.
Mais il ne faut surtout pas oublier d'annuler l'affichage à la fin. Sinon, vous aller rester avec les chiffres du scénario
au lieu de ceux de votre modèle.
Données.
Sous le bouton Analyse de scénarios Gestionnaire de scénarios.
Sélectionnez l'option Afficher (aussi, Modifier ou Supprimer )
Vous pouvez naviguer à travers votre modèle pour voir les changements apportés par ce scénario. Il ne faut surtout
pas modifier des chiffres ou créer des formules. Vous pouvez seulement voir le scénario.
Pour annuler l'affichage du scénario et retourner à votre modèle, appuyez sur le bouton Annuler ou Ctrl+Z
7/8
