QUELQUES UTILISATIONS DU SOLVEUR DEXCEL
Le solveur d'Excel est un programme macro que l'on trouve dans le menu outils d'Excel. Il permet de maximiser de minimiser ou de définir sous certaines
Installation du solveur
L'extension d'un fichier Excel est .xlsx pour la version 2007 et. 2010 et .xls pour les versions antérieures. Houyam Dehbi
2B La résolution de modèles linéaires par Excel 2010 Nous
Placer le curseur dans la cellule D10 où se trouve la valeur courante z de la fonction- objectif; cliquer sur le menu Données d'Excel puis sur l'option Solveur
2A La résolution de modèles linéaires par Excel Nous reprenons ici
solveur d'Excel 2010 pour résoudre un modèle linéaire (voir pages 30 et 31). Nous procéderons en trois étapes et illustrerons notre propos avec le problème
RÉSOLUTION DÉQUATIONS À LAIDE DEXCEL
Sélectionnez dans Excel la fonction Solveur (menu Outils). La boîte de dialogue suivante vous sera présentée. Page 2. Page 2 sur 6.
Excel : le solveur
Si l'outil est absent il vous faut activer le complément concerné : • Cliquez sur le bouton office (Excel 2007) ou allez dans l'onglet « fichier » (Excel 2010)
Formation du personnel non statisticien des ministères sectoriels
Support cours Microsoft Excel 2010 rt cours Microsoft Excel 2010 rt de formation Microsoft Excel 2010 ... Utilisation de la valeur cible et du solveur .
Optimisation linéaire: Applications
Solveur Excel. Autres solveurs. Approx. linéaires. Jeux matriciels. Références. Exemple 2 : Blue Ridge Hot Tubs (BRHT). ? [Ragsdale 2010]. ? Mod`ele :.
Le Solveur permet de rechercher la valeur optimale valeur optimale
Avant d'utiliser le solveur EXCEL il faut d'abord construire efficacement une feuille de calcul. o Dans cet exemple
Excel 2010 Expert
Valeur cible et solveur : des simulations à rebours • 200. Valeur cible • 200 Correspondances commandes Excel 2003 – Excel 2010 • 485. Fichier • 485.
[PDF] QUELQUES UTILISATIONS DU SOLVEUR DEXCEL - R2MATH
Le solveur d'Excel est un programme macro que l'on trouve dans le menu outils d'Excel Il permet de maximiser de minimiser ou de définir sous certaines
Maitriser le solveur dExcel (solver) - Cours-Gratuit
Document de formation sur Excel un cours gratuit dédié aux débutants sous format PDF pour s'initier apprendre et maitriser le solveur d'Excel à partir de
[PDF] le solveur - Excel - Ph Yvonnet
Cliquez sur le bouton office (Excel 2007) ou allez dans l'onglet « fichier » (Excel 2010) • « options Excel »(2007) ou « options »(2010)
[PDF] Journée passerelle en Excel - École dactuariat - Université Laval
Le Solveur est un exemple de compléments devant être installés et le Développeur un exemple d'options cachées Procédure : ? « Bouton Office » ? « Options
Application du logiciel Excel Utilisation du Solver du logiciel Excel
Application du logiciel Excel Utilisation du Solver du logiciel Excel Download Free PDF paper cover icon Download Free PDF paper cover thumbnail
[PDF] 2B La résolution de modèles linéaires par Excel 2010
Nous reprenons ici de façon plus détaillée la section où est indiqué comment utiliser le solveur d'Excel 2010 pour résoudre un modèle linéaire (voir pages
[PDF] OPTIMISATION À LAIDE DEXCEL
En plus d'effectuer la résolution d'équations le solveur d'Excel permet la résolution de problèmes d'optimisation de tous genres (une ou plusieurs
[PDF] Optimisation linéaire: Applications - GERAD
Optimisation linéaire avec le solveur de Excel 2 Autres solveurs Bonnes pratiques données dans [Ragsdale 2010] MTH8415: Optimisation linéaire:
[PDF] Installation du Solveur et de VBA dans EXCEL 2007/2010 - Pequan
Excel installe les compléments et ferme la fenêtre d'Options Si l'installation s'est bien passée le solveur est disponible dans l'onglet Données ainsi que l'
[PDF] Excel 2010 Expert - fnac-staticcom
Valeur cible et solveur : des simulations à rebours • 200 Valeur cible • 200 Correspondances commandes Excel 2003 – Excel 2010 • 485 Fichier • 485
Comment activer le solveur sur Excel 2010 ?
Dans le menu Outils,sélectionnez Ajouter Excel. Dans la zone Macros complémentaires disponibles, activez la case à cocher Complément Solveur, puis cliquez sur OK.Où se trouve le solveur Excel ?
Sous l'onglet Données, dans le groupe Analyse, cliquez sur Solveur. Remarque : Si la commande Solveur ou le groupe Analyse n'est pas disponible, vous devez activer le macro complémentaire Solveur.Comment utiliser la fonction solveur sur Excel ?
Le solveur d'Excel fonctionne de manière analogue à la valeur cible, tout en offrant des possibilités beaucoup plus importantes. En particulier, il est possible de calculer un objectif à atteindre en fonction de plusieurs cellules variables, et non d'une seule comme avec la valeur cible.- Il se peut que vous ayez omis une ou plusieurs contraintes dans la définition du problème. Vérifiez les valeurs de la feuille de calcul courante pour savoir dans quelle mesure la solution diverge , vérifiez les contraintes , puis executez de nouveau le problème. Le solveur ne peut pas trouver de solution réalisable.
Optimisation lineaire: Applications
MTH8415
S. Le Digabel, Polytechnique Montreal
H2020 (v2)MTH8415: Optimisation lineaire: Applications1/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferences Plan1. Optimisation lineaire avec le solveur deExcel
2. Autres solveurs
3. Application : Approximations lineaires
4. Application : Jeux matriciels
References
MTH8415: Optimisation lineaire: Applications2/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferences1. Optimisation lineaire avec le solveur deExcel
2. Autres solveurs
3. Application : Approximations lineaires
4. Application : Jeux matriciels
References
MTH8415: Optimisation lineaire: Applications3/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesIntroduction
I Outil integre dansExcelpour l'optimisation lineaire, non lineaire, et en nombres entiers IOptimisation lineaire avec le simplexe
IAvantages :
ISimplicite d'utilisation. Base surExcel
IEcace pour des problemes de taille raisonnable
IOutils pour l'analyse de sensibilite
IInconvenients :
IPas adapte aux problemes de grande taille
IDicilement integrable au sein d'autres applications I Bonnes pratiques donnees dans [Ragsdale, 2010]MTH8415: Optimisation lineaire: Applications4/60 SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesPrincipes de base
I Communication :Le chier doit ^etre clair (noms, couleurs, commentaires, etc.) I Fiabilite :Les sorties doivent ^etre correctes et consistantes I Comprehension :On devrait pouvoir comprendre le modele et verier les resultats I Flexibilite :Un modele devrait ^etre facilement modiable le jour ou les donnees changentMTH8415: Optimisation lineaire: Applications5/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesTrucs (1/2)
I Organiser le format des donnees puis construire le modele a partir des donnees I Ne jamais mettre de constante dans une formule mais l'adresse de la cellule contenant cette constante I Les valeurs dont le sens est relie devraient ^etre situees proches les unes des autres I Les formules identiques devraient ^etre copiees/collees I Le total d'une colonne devrait ^etre au bas de la colonne ILe total d'une ligne devrait ^etre a droite de la ligneMTH8415: Optimisation lineaire: Applications6/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesTrucs (2/2)
I On lit habituellement de gauche a droite et de haut en bas.Un modele devrait respecter cet ordre
I Utiliser les caracteristiques deExcelpour distinguer variables, parametres, formules, etc. I Utiliser des zones de textes et des commentaires pour faciliter la lecture du modele ILaisser les parametres en
o rangeMTH8415: Optimisation lineaire: Applications7/60 SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesVariables d'optimisation
IUne cellule par variable
I Les placer sur une m^eme ligne dans des colonnes contigues IAjouter une particularite (couleur
ble ue )p ouridentication rapide I Placer le nom des variables dans les cellules juste au-dessus et a gauche (lecture facile des sorties) IOptionnel : Fournir une valeur initiale aux variablesMTH8415: Optimisation lineaire: Applications8/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesFonction objectif
I Placer les coecients de maniere similaire aux variables I Calculer avec la fonctionExcel SOMMEPROD(SUMPRODUCT) I Placer le nom juste au-dessus ou nommer la cellule IAjouter une particularite (couleur
jaune ) pour identication rapideMTH8415: Optimisation lineaire: Applications9/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesContraintes
IUne contrainte par ligne
I Un seul nombre a droite dans une cellule distincte IToutes les variables a gauche
I Placer les coecients dans colonnes correspondant aux variables I Faire le calcul (avecSOMMEPROD) du membre de gauche et placer le resultat dans une cellule IPlacer le nom de la contrainte a gauche
IAjouter particularite (couleur
verte ) pour identication rapideMTH8415: Optimisation lineaire: Applications10/60 SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesExecution du solveur
I Lancer l'interface du solveur depuis le menuOutilsouDonnees
I Cellule cible a definir: fonction objectif (min/max) ICellules variables: variables de decision
IContraintes: contraintes
IVia les Options du solveur :
I Suppose non-negatif: contraintes de non negativite IIndiquerModele suppose lineaire
ICocherEchelle automatique
I Cliquer surResoudre, puisReponses(Sensibilite) et sur Ok IApres : Bien lire le message pour savoir si ca a marcheMTH8415: Optimisation lineaire: Applications11/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesExemple 1 : Oak Products
I [Weatherford, 1997] I La compagnieOak Productsfabrique 6 types de chaises a partir de 11 composantes I Chaque semaine on regarde l'inventaire des composantes et on etablit le plan de production IChaque type de chaise induit un prot unitaire
I Combien doit on produire de chaises de chaque type?MTH8415: Optimisation lineaire: Applications12/60 SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesOak Products : Donnees
MTH8415: Optimisation lineaire: Applications13/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesOak Products : Variables et objectif
IUne variable de decision par type de chaise :
x= (C;M;H;L;K;Q), avec : IC: nombre de chaisesCaptainproduites
IM(Mate)
IH(American High)
IL(American Low)
IK(Spanish King)
IQ(Spanish Queen)
IProt : Fonction objectif a maximiser :
f(x) = 36C+ 40M+ 45H+ 38L+ 35K+ 25QMTH8415: Optimisation lineaire: Applications14/60 SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesOak Products : Contraintes
I Une condition contrainte d'inventaire a respecter pour chacune des composantes (contraintes) : INombre de grandes chevilles :
c1(x) = 8C+ 12H+ 8K+ 4Q1280
INombre de petites chevilles :
c2(x) = 4C+ 12M+ 12L+ 4K+ 8Q1900
INombre de dossiers type Spanish :c11(x) =K+Q85
I Finalement, il y a des imperatifs de production a respecter : il faut produire des nombres positifs de chaises (contraintes) : C0,M0,H0,:::,Q0MTH8415: Optimisation lineaire: Applications15/60 SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesOak Products : Modele
maxC;M;H;L;K;Q36C+ 40M+ 45H+ 38L+ 35K+ 25Q
s.c. 8 >>>>>>>>>:8C+ 12H+ 8K+ 4Q12804C+ 12M+ 12L+ 4K+ 8Q1900
4C+ 4M+ 4H+ 4L+ 4K+ 4Q1090
C+K+Q190
M+H+L170
K+Q85 C;M;H;L;K;Q0MTH8415: Optimisation lineaire: Applications16/60 SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesOak Products : Resolution
Voir chierEx1-Oak Products.xlsxMTH8415: Optimisation lineaire: Applications17/60 SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesExemple 2 : Blue Ridge Hot Tubs (BRHT)
I [Ragsdale, 2010] IModele :
Max. prot350X1 + 300X2PompesX1 +X2200
Main d'uvre9X1 + 6X21566
Tuyaux12X1 + 16X22880
non-negativiteX1;X20MTH8415: Optimisation lineaire: Applications18/60 SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesBRHT : Rapport de sensibilite
MTH8415: Optimisation lineaire: Applications19/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesBRHT : Sensibilite aux coecients de l'objectif
I Les valeurs appelees \Augmentation admissible" et \Reduction admissible" pour les cellules variables indiquent la taille maximale des variations du coecient de l'objectif qui laissent la solution optimale inchangee (m^eme point extr^eme) en supposant que tous les autres coecients restent inchanges I Un zero pour \Augmentation admissible" ou \Reduction admissible" indique qu'il existe plus d'une solution optimale I L'intervalle admissible de changement decrit dans le rapport de sensibilite n'est valable que si tous les autres coecients restent xes (i.e. seulement un est change) I Si le changement sort de l'intervalle admissible, il faut resoudre le probleme a nouveau pour en conna^tre l'impact sur la solution optimale (i.e. les nouvelles valeurs optimales des variables et de l'objectif)MTH8415: Optimisation lineaire: Applications20/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesBRHT : Interpretation des co^uts reduits des
variables I Pour une variable qui n'est pas a sa borne superieure ou inferieure, le co^ut reduit est de zero I Pour une variable qui est a sa borne sup. ou inf., le co^ut reduit indique l'impact sur la valeur optimale de l'objectif d'une augmentation d'une unite de cette variable I Une variable dont la valeur optimale est a son minimum a un co^ut reduit relie au changement minimum du coecient de l'objectif qui rend une augmentation de cette variable protableMTH8415: Optimisation lineaire: Applications21/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesBRHT : Sensibilite aux membres de droite des
contraintesMTH8415: Optimisation lineaire: Applications22/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesBRHT : Sensibilite aux mdd des contraintes
IChanger le membre de droite d'une contrainte :
IPeut changer la valeur optimale de l'objectif
IPeut changer la solution optimale (un nouveau point extr^eme) ILe rapport de sensibilite associe un
co ^utomb re ( shadow price) a chacune des contraintes. Celui-ci indique de combien l'objectif augmentera par unite d'augmentation du membre de droite, en supposant que tous les autres parametres restent constants I Le co^ut ombre n'est valable que si le mdd reste dans l'intervalle admissible, deni par les valeurs de \Augmentation admissible" et de \Reduction admissible" I Les co^uts ombre correspondent aux opposes des co^uts reduits des variables d'ecart et aux solutions dualesMTH8415: Optimisation lineaire: Applications23/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesBRHT : Sensibilite aux mdd des contraintes
I Si la variation du mdd est dans cet intervalle, la nouvelle valeur optimale de l'objectif se calcule comme suit :Variation de l'obj. = variation du mddco^ut ombre
I Le co^ut ombre des contraintes inactives est toujours zero : Changer la valeur du mdd d'une contrainte inactive n'aecte pas la solution optimale I Ces regles ne s'appliquent que si seulement un parametre (mdd) est modie I Le co^ut ombre indique seulement la variation de la valeur optimale de l'objectif. Si la contrainte est active, changer son mdd aecte l'ensemble des solution admissibles et mene a une nouvelle solution optimale. Pour trouver la nouvelle solution optimale, nous devons resoudre a nouveau le problemeMTH8415: Optimisation lineaire: Applications24/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesBRHT : Autre usage des co^uts ombre
I Supposons qu'un nouveau bain (le Typhoon-Lagoon) peut ^etre produit par BRHT. Son prot unitaire serait de 320$ et requiert : 1 pompe (co^ut ombre = 200$), 8 heures de main d'uvre (co^ut ombre = 16.67$), 13 pieds de tuyaux (co^ut ombre = 0$) IEst-il protable de produire ce bain?
I320200116:678013 =13:33$: Non
I Un produit dont le prot marginal est au dessous du co^ut marginal de sa production (mesure avec les co^uts ombre des ressources) ne peut ^etre produit dans une solution optimale (a moins d'ajouter une contrainte de production minimale)MTH8415: Optimisation lineaire: Applications25/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesBRHT : Solution degeneree
I La solution d'un POL est appelee degeneree si une des variables de base est a sa borne superieure ou a sa borne inferieure I On detecte une solution degeneree si l'augmentation ou la diminution admissible pour le mdd d'une contrainte est a zero I Dans ce cas, le rapport de sensibilite est dicilement interpretableMTH8415: Optimisation lineaire: Applications26/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesExemple 3 : Eastern Steel
I ES achete du minerai provenant de 4 mines et melange ces minerais pour obtenir de l'acier. La qualite de l'acier se mesure en fonction de la teneur du melange, selon 3 types d'element A, B et C. Par tonne d'acier, il faut au moins 5 kilos de A,100 de B, 30 de C. A, B et C sont en quantites dierentes
dans le minerai des 4 mines exploitees et a des prix dierents :Mine 1 Mine 2 Mine 3 Mine 4
A (kg/tonne) 10 3 8 2
B (kg/tonne) 90 150 75 175
C (kg/tonne) 45 25 20 37
$/tonne 800 400 600 500 I Il faut determiner le melange a co^ut minimalMTH8415: Optimisation lineaire: Applications27/60 SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesES : Modele
I Variables :M1;M2;M3;M4: Quantite de minerai des mines1 a 4 dans une tonne d'acier
IModele :
min800M1 + 400M2 + 600M3 + 500M4 s.c.8 >>>:10M1 + 3M2 + 8M3 + 2M45(1)90M1 + 150M2 + 75M3 + 175M4100(2)
45M1 + 25M2 + 20M3 + 37M430(3)
M1 +M2 +M3 +M4 = 1(4)
M1;M2;M3;M40MTH8415: Optimisation lineaire: Applications28/60 SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesES : Rapport de sensibilite
MTH8415: Optimisation lineaire: Applications29/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesES : Questions
I De combien au maximum la mine 2 peut-elle augmenter son prix sans voir ses ventes aupres de ES baisser?Reponse :66.85$
I De combien la mine 4 doit-elle baisser son prix pour reussir a vendre son minerai a ES?Reponse :91.11$
I Sans renegocier le prix des minerais aupres des mines, comment ES peut-elle baisser son co^ut de minerai a 500$ par tonne? Possibilite 1 :Relaxer la contrainte (1) de 5 a 4.75 (511:110:2544:44 = 500) Possibilite 2 :Relaxer la contrainte (3) de 30 a 27.5 (511:112:54:444 = 500)MTH8415: Optimisation lineaire: Applications30/60 SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferences1. Optimisation lineaire avec le solveur deExcel
2. Autres solveurs
3. Application : Approximations lineaires
4. Application : Jeux matriciels
References
MTH8415: Optimisation lineaire: Applications31/60
SolveurExcelAutres solveursApprox. lineairesJeux matricielsReferencesOptimisation lineaire avec Matlab
IPour resoudreminx2Rnf(x) =c>x
s.c.8 :Axb Dx=e `xu IExecuter la commande :
[x f flag output lambda] =linprog(c;A;b;D;e;l;u) I lambda.ineqlinetlambda.eqlinpermettent d'acceder auxquotesdbs_dbs44.pdfusesText_44[PDF] solutions harcèlement scolaire
[PDF] résolution système d'équation non linéaire excel
[PDF] qu'est ce que le harcèlement scolaire ?
[PDF] prévention harcèlement scolaire
[PDF] agir contre le harcèlement ? l'école pdf
[PDF] harcèlement sévère définition
[PDF] le harcèlement entre élèves le reconnaître le prévenir le traiter
[PDF] introduction ? l'informatique cours pdf
[PDF] marché de la confiserie 2015
[PDF] harlan coben innocent pdf
[PDF] harlan coben pdf francais
[PDF] harlan coben livres pdf gratuit
[PDF] harlan coben une chance de trop pdf
[PDF] sans un adieu pdf