IntroductionLe solveur d'EXCEL est un outil puissance d'optimisation et d'allocation de ressources. Il peut vous aider dterminer comment utiliser au mieux des ressources limites pour maximiser les objectifs souhaits (tellela ralisation de bnfices) et minimiser une perte donne (tel un cot de production). En rsum, il permetde trouver le minimum, le maximum ou la valeur au plus prs d'une donne tout en respectant lescontraintes qu'on lui soumet. Plutt que de vous contenter d'approximations, vous pouvez faire appel ausolveur pour trouver la meilleur solution.

Quand utiliser le solveurUtilisez le solveur lorsque vous recherchez la valeur optimale d'une cellule donne (la fonctionconomique) par ajustement des valeurs de plusieurs autres cellules (les variables) respectant desconditions limites suprieurement ou infrieurement par des valeurs numriques (cest dire lescontraintes).Etudions un exemple avec le fichier proglin.xlsReprenons lexercice du dernier TE:

Nous devions maximiser la fonction conomique f(x1

, , x3) = 10 x1 + 15 x2 + 25 x3Sous les contraintes suivantes:

Le problme peut tre synthtis sur cette feuille de calcul EXCEL:

Les variables sont les quantits respectives des diffrents investissements (cellules jaunes). Les contraintes sont les valeurs imposes dans la donne (cellules rouges). La cellule cible est celle contenant la formule exprimant la valeur optimiser (cellules bleues).Afin doptimiser la fonction conomique, nous allons utiliser la commande Solveur du menu Outil

Utilisation dEXCEL pour rsoudre des problmes de programmation linairePROGRAMMATION LINEAIRE ET EXCEL Annexe 1

Jt - 2MSPM - 2004

[ ; ]

x x x

x x x

x x x

x pour ii

1 2 3

1 2 3

1 2 3

2 4 20 0003 16 000

3 5 3 48 0000 1 3

+ +

+ +

+ +

Formule:=10*B3+15*B4+25*B5

Programme initialToutes les variablessont poses = 0

Formule:=B3+2*B4+4*B5

Formule:=3*B3+5*B4+3*B5

Formule:=B3+B4+3*B5

PROGRAMMATION LINEAIRE ET EXCEL Annexe 2

Jt - 2MSPM - 2004

Premire tape : Configurer loutil SolveurIl est fort probable que les commandes du solveur napparaissent pas encore dans le menu Outils.Ainsi droulez le menu Outils puis:

Deuxime tape : Spcifications de la cellule cibleDans la zone Cellule cible dfinir, tapez la rfrence de la cellule que vous voulez minimiser, maximiser (cest dire la fonction conomique).

Si vous dsirez maximiser la cellule cible, choisissez le bouton Max.Si vous dsirez minimiser la cellule cible, choisissez le bouton Min.Si vous dsirez que la cellule cible se rapproche d'une valeur donne, choisissez le bouton Valeur et indiquela valeur souhaite dans la zone droite du bouton.

Remarques

Allez plus vite en cliquant directement sur la cellule spcifier plutt que de taper sa rfrence au clavier. La cellule cible doit contenir une formule dpendant directement ou indirectement des cellules variables

spcifies dans la zone Cellules variables.

La valeur de la fonctionconomique se situe dans la

case B17

Quatrime tape : Spcifications des contraintes

A l'aide des boutons Ajouter, Modifier et Supprimer de la bote de dialogue, tablissez votre liste de contraintes dans la zone Contraintes.

Remarques

Aprs avoir cliqu dans chaque case complter, il suffit de cliquer dans les cellules correspondantesdirectement sur la feuille Excel. Puis pour confirmer

Une contrainte peut tre une limit infrieurement (), suprieurement () ou limit aux nombres entiers(oprateur ent).

La cellule laquelle l'tiquette Cellule fait rfrence contient habituellement une formule qui dpend descellules variables.

Le solveur gre jusqu' 200 contraintes.

PROGRAMMATION LINEAIRE ET EXCEL Annexe 3

Jt - 2MSPM - 2004

Troisime tape : Spcifications des cellules variablesTapez dans la zone Cellule variables les rfrences des cellules devant tre modifies par le solveurjusqu' ce que les contraintes du problme soient respectes et que la cellule cible atteigne le rsultatrecherch.

Remarques

Allez plus vite en cliquant-glissant directement sur les cellules spcifier plutt que de taper leurs rfrences au clavier.

Il est probable que le solveur vous propose automatiquement les cellules variables en fonction de lacellule cible. Controlez que sa proposition nest pas trop exotique.

Vous pouvez spcifier jusqu' 200 cellules variables. Dans le programme initial, on dfinit les cellules variables par des zros.

x1 + x2 + 3x3 16000

3x1 + 5x2 + 3x3 48000

xi 0 pour i [1 ; 4]

x1 + 2x2 + 4x3 20000

Cinquime tape : Les options du solveur

Cette bote de dialogue permet de contrler les caractristique avances de rsolution et de prcision dursultat. En gnral, la plupart des paramtres par dfaut sont adapts la majorit des problmesd'optimisation. Concentrons-nous sur quelques options plus spcifiques:Modle suppos linaireA cocher seulement si le systme d'quations est linaire. Si la case est active alors que le problme n'estpas linaire, EXCEL affichera un message d'erreur pendant la rsolution.En revanche, si le problme est linaire et que la case est active, la rsolution est plus rapide.Diffrence entre problme linaire et non linaireSur un graphe, un problme linaire serait reprsent par une droite. On trouve donc dans un problmelinaire des oprations arithmtiques simples comme : l'addition et la soustraction.Sur un graphe, un problme non linaire serait reprsent par une courbe, traduisant une relation nonproportionnelle entre les variables du systme. Le cas le plus courant est quand 2 variables du systmesont multiplies l'une avec l'autre.Afficher le rsultat des itrationsInterrompt le solveur et affiche les rsultats produits par chaque itration. Cette option permet de suivretape aprs tape les diffrents programmes de base.

Sixime tape : Rsolution et rsultatUne fois tous les paramtres du problme mis en place, le choix du bouton amorce le processus de rsolution du problme. Vous obtenez alors une de ces rponses :

Que faire des rsultats du solveur Garder la solution trouve par le solveur ou rtablir les valeurs d'origine dans votre feuille de calcul. Crer un des rapports intgrs du solveur en slectionnant celui qui nous concernera.

PROGRAMMATION LINEAIRE ET EXCEL Annexe 4

Jt - 2MSPM - 2004

Cinquime tape : Rapport des rponses

Ce rapport donne l'volution des cellules variables et de la cellule cible. On remarque donc bien qu'il y a eu une maximisation du bnfice.

Le rapport rappelle les diffrentes valeurs des contraintes, leurs formules, et dans quelle mesure ellesont t respectes.

Li : La valeur finale de la cellule contenant une contrainte atteint effectivement la valeur maximum.Exemple: $B$10 devait-tre

Appliquons ceci aux donnes suivantes:

Exercice 1: Reprenons le problme des crabes (ex. 6.4) dont le modle tait le suivant:Maximiser f(x1, x2, x3) = 80%12,5x1 + 95%8,42x2 + 90%7,78x3avec les contraintes:

Exercice 2:

Dans sa basse-cour, un fermier peut tenir 600 volatiles: oies, canard et poules. Il veut avoir au moins 20canards et 20 oies, mais pas plus de 100 canards, ni plus de 80 oies, ni plus de 140 des deux.Acheter et lever une poule cote Fr. 3.-, un canard Fr. 6.- et une oie Fr. 8.-. Ils peuvent tre vendusFr. 8.-, Fr. 13.- et Fr. 20.- respectivement.

Comment ce fermier peut-il raliser un bnfice maximum ?

Exercice 3:

Dans une entreprise de nettoyage, chaque personne travaille cinq jours conscutifs suivis de deux joursde cong. Il existe 4 catgories demploys selon leurs jours de cong. Le salaire dun employ varieselon la catgorie laquelle il appartient:

Les demandes quotidiennes en employs dpendent du jour de la semaine, suivant le tableau ci-dessous:

Combien de personnes de chaque catgorie doit-on faire travailler de faon satisfaire la demande et minimiser le cot du personnel ?

Exercice 4:

Prsenter la feuille de Calcul EXCEL de lexercice prcdent de manire attrayante et interprtable pourun tiers.

PROGRAMMATION LINEAIRE ET EXCEL Annexe 6

[ , ]

x x x

x x x

x x x

x i i

1 2 3

1 2 3

1 2 3

1000

16 19 18 18000

100

0 1 3

+ +

+ +

- -

Catgorie

Cong Vendredi,samedi

samedi,dimanche

dimanche,lundi

lundi,mardi

Salaire Fr. 5200.- Fr. 4800.- Fr. 5200.- Fr. 5600.-

Jour lundi mardi mercredi jeudi vendredi samedi dimancheDemande 25 18 41 41 30 18 24

Jt - 2MSPM - 2004