Tableur EXCEL/Analyse de scénario

Début de la boite de navigation du chapitre

Qui n’a jamais rêvé de résoudre des équations mathématiques de manière automatique ? Solveur vous permet de réaliser ce rêve.

fin de la boite de navigation du chapitre

Présentation de la fonction Solveur

Le solveur d'Excel est un outil de calcul mathématique qui permet de résoudre des équations du second degré. On lui fixe un objectif de résultat, en lui stipulant les contraintes et variables de l'énoncé. Il est alors chargé de calculer de façon automatique les possibilités existantes et d'en faire émerger la solution exacte (voire la plus proche) du problème posé.

Mise en place du Solveur d'Excel

Pour mettre en place la fonction solveur dans Excel il faut aller dans "option d'Excel" puis dans "compléments". Ensuite il faut sélectionner "complément solver" et cliquer sur "atteindre".

Les éléments nécessaires à l'exécution de la fonction

Afin de fonctionner correctement, la fonction solveur doit prendre en compte différents facteurs indispensables à sa réalisation. Parmi ces facteurs, on retrouve notamment :

• les variables de décision (les inconnus de l’inéquation)
• les contraintes (ex : capacité de production maximale)
• la fonction économique (minimiser ou maximiser)
• l’objectif (le résultat à atteindre avec la prise en compte de toutes les variables)

Les variables

Il est important de déterminer le nombre de variables à identifier. Excel peut calculer jusqu'à 200 variables. On notera alors dans une feuille d'excel toutes les variables à définir et on notera "0" dans les cellules à droite.

Les contraintes

Il faut déterminer les contraintes de notre problème. Elles peuvent aussi aller jusqu'à 200. Notre contrainte se notera sous forme de tableau comme pour les variables. Les contraintes devront être identifiées comme supérieures ou égales (≥) à un certain nombre, inférieures ou égales (≤), entre deux valeurs (ent), égales (=) à une valeur ou binaires (bin).

Les contraintes peuvent être soit des contraintes de limite c'est-à-dire liées aux éléments fixant un problème, soit des contraintes de disponibilité c'est-à-dire liées aux variables. En prenant notre exemple ci-dessous, les contraintes de limites sont les trois premières contraintes et la contrainte de disponibilité est la quatrième contrainte. Il y aura autant de contraintes de disponibilité qu'il y aura de variables.

La fonction économique

La fonction économique permet de déterminer une valeur optimum de notre problème. C'est la maximisation, la minimisation ou l'égalisation de l'objectif.

L'objectif

L'objectif est la valeur cible à définir dans les paramètres de la fonction solveur d'Excel. Cette cible doit soit minimiser une dépense, maximiser un profit ou être égale à une autre valeur. La cellule doit obligatoirement contenir une formule incluant les variables du problème.

Exemple de résolution d'un problème linéaire avec le solveur

L'entreprise Durty fabrique des chaussures de danse. Elle souhaite modifier son programme de production actuel pour les produits A (600 unités) et les produits B (200 unités). Lors de leur fabrication les produits passent par différents ateliers. Le tableau ci-dessous donne les informations concernant la durée de leur fabrication dans les ateliers.

	Produit A	Produit B	Heures disponibles
Assemblage (phase 1)	3	4	4200
Assemblage (phase 2)	1	3	2250
Vérification/Empaquetage	2	2	2600

Au vu de la concurrence, l'entreprise ne veut pas fabriquer plus de 1100 unités du produit A.

La marge du produit A est de 66 € l'unité alors que celle du produit B est de 84 €.

On veut déterminer le programme optimal de fabrication à mettre en œuvre c'est-à-dire celui qui maximiserait les bénéfices.

Variables de décision :

x : le nombre d'unités à fabriquer du produit A

y : le nombre d'unités à fabriquer du produit B

Les contraintes sont :

C1 => 3x+4y ≤ 4200 heures (heures disponibles à l'assemblage: phase 1)

C2 => x+3y ≤ 2250 heures (heures disponibles à l'assemblage : phase 2)

C3 => 2x+2y ≤ 2600 heures (heures disponibles: vérification/empaquetage)

C4 => x ≤ 1100 unités (quantité maximale pour A)

La fonction économique à maximiser est :

Z = 66x + 84y où Z correspond au bénéfice total

Résolution avec EXCEL

Il y a trois principales parties à fournir au solveur :

• La cellule à maximiser/minimiser
• La plage de variables de décision (x et y)
• Les contraintes

1) Les cellules B2 et C2 sont les variables du problème (x et y)

2) Les coefficients reliés aux variables sont inscrits de B5:C8

3) La quantité de ressources est indiquée et le sens de la contrainte (facultatif, seulement pour mémo)

4) Il faut indiquer le bénéfice/unité pour chaque variable (B11:C11)

5) Première contrainte : 3x +4y ≤ 4200. Il faut calculer l'expression de la partie gauche de l'équation avant d'activer le solveur. Exemple dans la cellule D5 la formule =$B$2*B5+$C$2*C5 est inscrite, équivalente à 3x +4y

6) Copiez cette formule pour les autres contraintes

7) La formule =B11*B2+C11*C2 est inscrite dans la cellule F12. C'est cette cellule qu'on maximisera car elle correspond à la fonction objectif 66x+84y

8) Menu : Outils/Solveur

9) Entrez les paramètres du solveur

• Cellule cible à définir : Ceci correspond à l'adresse de la fonction à optimiser
• Égale à : Cochez le type d'optimisation voulu. Le Max est coché car dans cet exemple nous voulons maximiser le bénéfice total.
• Cellules variables: Sélectionnez l'endroit dans le tableur où les variables se trouvent. Il ne doit pas avoir de cellules vides entre les variables. Les cellules B2:C2 représentent les variables de notre problème, c'est-à-dire celles qu'on désire déterminer.
• Contraintes : Vous devez spécifier chacune des contraintes de votre problème

Il ne faut pas oublier d'entrer les contraintes de non-négativité x≥0 , y≥0

1) Cliquez sur "Ajouter"

2) Cellule: Sélectionnez toutes vos variables : B2:C2

3) Inscrivez le sens >=

4) Contrainte : 0

x, y doivent être des entiers afin de ne pas produire des fractions d'unités.

1) Cliquez sur "Ajouter"

2) Cellule: Sélectionnez toutes vos variables

3) Choisissez "ent"

Pour enregistrer les autres contraintes

Cliquez sur "Ajouter"

Exemple : Pour la première contrainte : 3x +4y ≤ 4200 heures

1) Entrez l'adresse de la cellule contenant la formule : 3x +4y équivalente à (=B5*B2+C5*C2). On doit donc entrer D5

2) Le sens de l'équation <=

3) Le nombre de ressource 4200 ou son adresse F5

La première contrainte correspond à D5≤F5

Exemple : pour la dernière contrainte x ≤ 1100

Le solveur sépare l'équation en trois.

1) Le membre gauche de l'équation: c'est-à-dire l'adresse de la cellule contenant la formule B2*B8+C2*C8 donc D8

2) Le sens de l'équation : <=

3) Le membre gauche de l'équation : c'est-à-dire le nombre de ressources 1100 ou son adresse F8

Cliquez sur OK lorsque vous avez terminé d'entrer toutes vos contraintes.

Etant donné que nous voulons résoudre un programme linéaire, il est possible de le spécifier au solveur afin qu'il utilise la méthode adéquate pour résoudre le problème.

Cliquez sur "option", cochez "Modèle supposé linéaire" et cliquez sur OK.

10) Cliquez sur "Résoudre"

11) Le solveur a trouvé la solution optimale selon les contraintes.

Production de 1000 A et de 300 B et un bénéfice total de 91 200 €.

12) Le solveur vous demande si vous voulez garder cette solution à l'écran ou revenir à celle de départ. Choisissez garder la solution du solveur.

13) Appuyer sur OK