LeCompagnon.info

Excel

Excel - Exercice sur le Solveur

Introduction
Avant de commencer
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 une objectif avec le moindre 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 !

Avant de commencer

Pour suivre cet exercice, vous pouvez aller chercher le classeur demos-excel-3.xlsx qui se trouve sur la page des fichiers de démonstrations.

*Sélectionnez la feuille de calcul Modèle.

 

Introduction à Excel
Menu Excel
Fichier
Accueil
Insertion
Mise en page
Formules
Données
Révision
Affichage
Thèmes
Opérations de base
Présentation
Mise en page et Impression
Fonctions
Fonctions matricielles
Copier, coller
Référence relative et absolue
Graphiques
Formules 3D
Outils d'analyse
Commentaires
Liste de données
Macro commandes
Tableau croisé dynamique
Exercices : création de modèle
Graphique: Insérer un second axe
Graphique: créer un graphique demi-lune
Graphique: Créer une pyramide d'âge
Gestionnaire de scénarios
Mise en forme conditionnelle
Remplissage instantané et analyse rapide
Solveur
Somme automatique
Sous-total
Tableau croisé dynamique
Table de données (1 et 2 variables)
Valeur cible
Validation - Liste de choix
Quiz
Navigation

Word 2003
Word 2007
Excel
Excel
VBA pour Excel
PowerPoint 2003
PowerPoint 2007
Access
Access 2010
Internet
Général
Intelligence financière

Navigation
Précédent
Suivant
Page Principale
FAQ
Nouveautés
Plan du site
Références
Motivations
Manuels de formation
Fichiers de démonstration
Nous joindre
Par courriel
Abonnez-vous à
l'infolettre LCI
Partager


Abonnez-vous à l'infolettre LeCompagnon.info

Excel 2007 : Modèle pour appliquer le solveur

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.

*Sélectionnez l'onglet Fichier.
*Sélectionnez l'option Options.

Excel - Fichier - Options - Complements

*De la colonne de gauche, sélectionnez la catégorie Compléments.
*De la liste des types de compléments au bas de la fenêtre, sélectionnez Complément Excel et appuyez sur le bouton Atteindre.

Excel - Solveur Activer le complément Solveur

*De la liste des macros complémentaires, activez l’option Complément Solver.
*Appuyez sur le bouton OK.

Excel - Solveur - Bouton Solveur

 C’est à la fin de l’onglet Données que vous allez maintenant retrouver le bouton pour 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.

Excel - Solveur - fenêtre solveur

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). Veuillez 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 et de votre modèle. Le Solveur peut gérer jusqu'à 200 contraintes.

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

Excel - Solveur - minimum pour cellule B3

*Appuyez sur le bouton Ajouter et ajoutez les contraintes suivantes.

*Dans la case Cellule, entrez B3.
*Dans la case Contrainte, entrez B32.

Ne prenez pas en considération les "$" dans les références de cellules.

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 ou égale à

Le contenu de la cellule soit être supérieur ou égale à un montant déterminé. Cela veut dire qu'on détermine le "plancher" de la cellule puisque la valeur ne peut être en dessous du seuil déterminé.

Inférieur ou égale à

Le contenu doit être inférieur ou égal à un montant déterminé de la case contrainte. Cette contrainte détermine le "plafond" possible puisque rien ne peut aller au-dessus.

Entier

Le contenu de la cellule doit donner un chiffre entier; sans aucune fraction.

Bin

Le contenu peut être que VRAI ou FAUX

*Pour cette exercice, sélectionnez la contrainte >= .

Le contenu de la cellule B32 est la limite inférieure déterminée pour ce modèle ou son "plancher". 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.

Excel - Solveur - maximum de la cellule B3

*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 C32.

L'opération <= détermine le "plafond" ou le maximum permit selon les conditions de ce modèle.

Options du Solveur

*Appuyez sur le bouton Options.

Excel - Solveur - fenêtre Options Toutes les méthodes

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.

L'échelle automatique est nécessaire lorsque vous avez plusieurs variables. Sinon, le Solveur va optimiser modèle seulement selon la première variable au leiu d'optimiser chaque variable individuellement. TRÈS IMPORTANT.

Vous pouvez aussi déterminer le nombre d'itérations (essaies) et le temps maximum en secondes. Certains modèles plus complexes peuvent prendre plusieurs heures même sur un ordinateur performant pour compléter. Une limite de teps peut être un avantage.

Excel - Solveur - fenêtre des options  GRG non-linéraire

Le complément Solveur offre trois méthodes pour optimiser votre modèle. Une apporche peut atteindre la solution optimale plus rapidement selon le type de modèle que vous avez créé. Le second onglet vous offre des options pour des modèles non-linéaires dont l'option Multistart.

 

Excel - Solveur - fenêtre options Évolutionnaire

Le dernier onglet offre les options pour une approche évolutionnaire dont le degré de convergence et le taux de mutation.

 


*Pour sortir des options du Solveur, appuyez sur le bouton OK.

Résoudre le problème

Avant de demander au Solveur de "résoudre" ou d'optimiser votre modèle, vous dever réaliser les étapes suivantes.

Déterminer la cellule à optimiser.
Choisir si vous désiez maximiser ou minimiser le modèle.
Choisir les cellules que vous permetez au Solveur de modifier afin de l'optimiser.
Inclure les critères pour chaque variables choisie.
Déterminer les options du Solveur.

*Appuyez sur le bouton Résoudre.

Le Solveur va prendre quelques instranst pour Optimiser votre modèle. Cet exemple est très simple et rapide.

Excel - Solveur - fenêtre Résoudre

*Sélectionnez l’option Rétablir les valeurs d’origines ainsi que choisir tous les rapports (Réponses, Sensibilité, Limites).
*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.

Excel - Solveur - Rapport de réponses

La nouvelle feuille de calcul avec le rapport de réponses montre l'approche qui a été utilisée, la solution optimale selon les critères, les valeurs initiales et finales pour les variables que vous aviez choisies ainsi que de la liste des contraintes que vous avez déterminé. Cela vous permettra de voir les résultats et d'apporter des changements si requis.

Excel - Solveur - Rapport de sensibilité

La feuille de calcul du rapport de sensibilité vous montre l'impact de chaque variables choisies dans votre modèle. On constate ici que la cellule B21, le taux de croissance, a un impact très important par rapports aux autres cellules (1 154 999 vs 6,825 )

Excel - Solveur - rapport des limites

La dernière feuille de calcul indique les limites de chaque variable utilisées dans le modèle.

Conclusion

Le Solveur est un excellent outil pour optimier votre modèle ainsi que pour déterminer l'importance de chaque variable de votre modèle. Vous saurez ainsi sur quel élément vous devez mettre plus d'importance.

 

Références

Excel Solver instructions: http://www.business.latech.edu/~jcochran/QA525/Excel%20Solver%20Instructions/Using%20Excel%20Solver.htm

Initiation au Solveur d'Excel : http://www.emse.fr/~beaune/solveur/novice.html



 

Abonnez-vous à l'infolettre

Abonnez-vous à l'infolettre du site pour recevoir les dernières nouvelles et aussi des formations à votre courriel. Vous pouvez voir la liste des dernières infolettres ici.

Cliquez ici pour vous abonner à l'infolettre





Ce site est hébergé par 1&1.com