Exercice Corrigé Solveur Excel TD solveur Excel

Objectif : Utilisation du solveur Excel pour résoudre des problèmes linéaires.


1.  Formuler le problème

Il faut avant tout exprimer la fonction objectif et les contraintes.
Par exemple, reprenons l’exercice 3.2. :
Variables de décision :
X1 = le nombre de téléviseurs GE 45 produits
X2 = le nombre de téléviseurs GE 60 produits
Fonction objectif : maximiser le profit
Profit = 50 X1 + 75 X2
Contraintes :
Heures de production : 2 X1 + 2 X2 <= 300
Heures d’assemblage : X1 + 3 X2 <= 240
Non-négativité : X1 >= 0 et X2 >= 0

2.  Encoder les valeurs

A la première ligne, nous écrivons le titre du problème et à la seconde ligne, le nom des variables de décision, ici GE45 et GE60.

Les trois lignes suivantes concernent la fonction objectif.

Nous introduisons à la ligne 3 les profits unitaires, c’est-à-dire les coefficients des variables de décision dans la fonction objectif.

La ligne 4 correspond dans ce cas-ci aux quantités, c’est-à-dire ce que l’on cherche.  La cellule C4 pour les quantités de GE 45 et la cellule D4 pour les quantités de GE60.  Le solveur remplira ces cases après avoir résolu le problème, elles sont laissées vides en attendant, ce qui équivaut à 0.

La ligne 5 nous donnera le profit dégagé par la vente des quantités de GE45 et GE60 produites.  Nous introduisons dans la cellule C5 la formule = C3*C4 (où C3=50) et dans la cellule D5, la formule = D3*D4 (où D3=75). 

Dans la cellule E5, nous introduisons la formule qui permettra au solveur de calculer la valeur optimale de la fonction objectif, le profit total (donc, l’équation de la fonction objectif = 50X1 + 75X2.  La formule à taper est =50*C4 + 75*D4 ou, plus simplement, C5 + D5).

En attendant, les cellules C4 et D4 étant vides, les cellules C5, D5 et E5 sont vides elles aussi, donc égales à 0.

Les deux lignes suivantes concernent les contraintes. 

Nous écrivons les coefficients de X1 et de X2 dans les contraintes dans les cellules qui y correspondent, C7 et D7 pour la contrainte d’heures de production, C8 et D8 pour la contrainte d’heures d’assemblge. 

Pour chacune des contraintes, la colonne « total » donnera la valeur de la contrainte à l’optimum, nous introduisons donc l’équation de chaque contrainte à la ligne qui lui correspond, dans cette colonne.  Par exemple, la cellule C7 = C7*C4 + D7* D4.

Dans la colonne « limites », nous introduisons la valeur du membre de droite pour chaque contrainte.

Il n’est pas nécessaire d’introduire les contraintes de non-négativité, on le fera au moyen d’une option du solveur.

3.  Résoudre le problème

Quand le problème a été formulé et introduit dans une feuille Excel, on choisit l’option « solveur » dans le menu « outils ».  Une boîte de dialogue apparaît.

Dans la case « cellule cible à définir », on sélectionne la cellule comprenant la formule donnant la valeur de la fonction objectif (E5).

On choisit Max dans l’option « égale à ».

Dans la case « cellules variables » on choisit les cellules correspondant aux valeurs des variables X1 et X2, ce que l’on cherche (cellules C4 et D4).

Pour intégrer les contraintes, on choisit « ajouter » et on sélectionne la cellule « totaux » correspondant à la valeur du membre de gauche de la contrainte (E7 pour la contrainte d’heures de production et E8 pour la contrainte d’heures d’assemblage), le signe de comparaison (ici <=) et la valeur du membre de droite (respectivement F7 et F8 pour chacune des contraintes).

Dans le menu « options », cocher « modèle supposé linéaire » et « supposé non-négatif » (ceci revient à introduire les contraintes de non-négativité.  Si on a choisit de les introduire comme contraintes standard dans la feuille de calcul, il n’est plus nécessaire de cocher cette case).

Pour lancer l’optimisation, on appuie ensuite sur « résoudre ».  On nous propose ensuite de conserver les valeurs initiales ou de prendre les valeurs du solveur.  Si on prend les valeurs du solveur, il remplir les cellules des valeurs des variables de décision (ici C4 et D4), la cellule de la valeur optimale de la fonction objectif (ici E5) et des valeurs des membres de gauche des contraintes à la solution optimale (ici E7 et E8).

Trois rapports sont également disponibles (réponses, sensibilité et limites).  Nous nous intéresserons aux rapports des réponses et de sensibilité.

Il est à noter que ces explications sont données pour le logiciel en français.

4. Rapport des réponses

La cellule cible est E5, la cellule de profit total, c’est-à-dire la valeur optimale de la fonction objectif.  Nous l’avions initialisée à 0 et le solveur a trouvé une solution optimale égale à 8625.

Les cellules variables, C4 et D4 correspondent à la solution optimale pour chacune des variables de décision.  Elles étaient également initialisées à 0 et le solveur nous fournit un optimum = (105,45).

Le solveur nous donne aussi la valeur du membre de gauche de chacune des contraintes, dans les cellules E7 et E8.  Ces valeurs sont égales à 300 et 240 respectivement.  Les deux contraintes sont liantes, elles sont satisfaites avec égalité, la marge est donc égale à 0.

5. Rapport de la sensibilité

Le rapport de la sensibilité consiste en des analyses post-optimales, d’une part suite à des variations des coefficients de la fonction objectif (tableau du haut « cellules variables »), d’autre part suite à des changements du membre de droite des contraintes (tableau du bas « contraintes »).

Le tableau « cellules variables » donne la valeur finale de chaque variable, son coût réduit (ici = 0 puisque aucune des variables n’est à sa borne inférieure), la valeur du coefficient de chaque variable dans la fonction objectif (50 et 75 que nous avions introduits dans la feuille de calcul), l’augmentation et la réduction admissible de chaque coefficient.  Ces dernières valeurs permettent de trouver le ‘range of optimality’ des coefficients.  Le range of optimality du coefficient de x1 est [50+25 ; 50-25], c’est-à-dire [75 ; 25] et il est de [75+75 ; 75-25], soit [150 ; 50] pour x2.  Il nous faut donc calculer le range of optimality à partir des valeurs d’augmentation et de diminution possibles alors qu’il nous est donné directement dans les sorties WINQSB.

Le tableau « contraintes » fournit la valeur finale du membre de gauche de chacune des contraintes à la solution optimale.  « L’ombre coût » est la traduction qu’Excel nous offre de la notion de « shadow cost ».  La colonne « contrainte à droite » correspond à la valeur du membre de droite de la contrainte, que nous avions préalablement introduite.  L’augmentation et la réduction admissibles permettent de calculer le « range of feasibility » des ressources, de la même manière que le « range of optimality » ci-dessus.