Représentation graphique (a)

Nous allons à présent montrer comment utiliser Excel pour une représentation graphique en 2D. Nous prendrons comme exemple le problème analysé dans la série d’articles du 11 au 23 juillet. Mais vous pouvez lire cet article – et ceux qui suivront – sans même les consulter. Cette nouvelle série d’articles est en effet totalement indépendante.

Reprenons simplement la formulation du problème à résoudre :

Max 300 BR1 + 350 BR2

3 BR1 + 2 BR2 ≤ 7.500

BR1 + 2 BR2 ≤ 3.500

BR1 ≤ 2.275

BR2 ≤ 1.500

BR1 ≥ 0 & BR2 ≥ 0

Sachant qu’une droite est identifiée par deux points, il est facile – pour les deux premières contraintes – d’obtenir ces deux points en fixant successivement BR1 à 0 puis BR2 à 0. Nous obtenons ainsi les valeurs en G2:H3 et G4:H5.

Pour la troisième contrainte, BR1 est fixé à 2.275. Pour Y, nous avons pris Y=0 et Y=3.750, la plus grande valeur de Y obtenue précédemment. Nous avons fait de même avec la quatrième contrainte.

Il n’est pas nécessaire de tracer les deux dernières contraintes qui spécifient  que les solutions se trouvent à droite de l’axe des Y et au-dessus de l’axe des X.

La fonction économique

Le plus délicat est le tracé de la fonction économique, la formule 300 BR1 + 350 BR2 que l’on cherche à optimiser. Si l’on donne à cette formule différentes valeurs, on obtiendra des parallèles. Il suffit donc de tracer l’un d’entre elles pour illustrer graphiquement la droite de profit que l’on cherche à optimiser.

En H14, nous avons entré la valeur 1.000 et, pour garder la pente de la droite dans le rapport requis, nous avons entré en G15 la formule =7*H14/6. Ainsi donc, dès que l’on entrera une nouvelle valeur en H14, on obtiendra sur le graphe une droite parallèle…

Représentation graphique

Il ne reste plus qu’à représenter graphiquement notre problème d’optimisation, ce qui peut se faire en deux étapes.

La première étape consiste à tracer le domaine des solutions réalisables, c’est-à-dire l’ensemble des points correspondant à des solutions respectant toutes les contraintes.

Pour cela, sélectionnons le bloc G2:H12 et insérons un graphe en nuage de points avec des lignes droites. Nous obtenons aussitôt la représentation graphique de l’ensemble des contraintes.

Remarque – Il suffisait donc de séparer les blocs de deux points par des lignes vides pour faire tracer à Excel des segments indépendants !

La seconde étape consiste à tracer la droite de la fonction économique. 

Pour cela, sélectionnons le bloc G14:H15, copions-le avec [Ctrl]-c, sélectionnons le graphe en cliquant dedans puis demandons un collage spécial en nouvelle série. Et voilà ! Il ne vous reste plus qu’à faire un petit travail de décoration pour obtenir le même résultat que dans la copie d’écran ci-dessous.

Les solutions réalisables sont tous les points de l’hexagone placé sous l’ensemble des droites bleues. La solution optimale est le point de l’hexagone par lequel on peut faire passer la parallèle à la droite rouge qui soit la plus haute possible. On retrouve ainsi aisément le point (2000 ; 750) calculé par le solveur d’Excel…

Optimisation (PL) – Rapports

Les rapports engendrés par le solveur ne sont pas géniaux et les termes utilisés sont souvent inadéquats. C’est au point que j’ai développé pour mes clients une macro qui corrige les erreurs les plus graves en remplaçant les termes erronés par des termes plus appropriés.

Cette macro modifie aussi certains résultats en les remplaçant par les valeurs plus intéressantes à lire.

On peut aussi reprocher à Microsoft les noms inutilement longs des onglets. Ainsi, ma macro remplace automatiquement le nom de feuille « Rapport de solution 1 » par « Solution », et de même pour les deux autres onglets ajoutés par le solveur. Il est en effet souvent inefficace dans Excel d’avoir des noms d’onglets longs, car cela réduit le nombre d’onglets affichés en bas de l’écran.

Voici ce que donne le premier rapport, le rapport de solution, en omettant du texte au-dessus du tableau : 

Un « Etat lié », dans le langage du solveur, signifie que la contrainte associée est saturée. Il n’y a aucun renseignement dans ce tableau que nous n’ayons pas déjà dans le tableau Excel affichant le résultat.

Voici ce que donnent les deux autres onglets, en omettant aussi les lignes de texte qui les précèdent : 

Les seules informations originales que l’on peut trouver se trouvent dans le rapport de sensibilité. Ce sont les valeurs marginales des contraintes saturées, ici baptisées à tort « de Lagrange multiplicateur ». Quand on voit des titres tels que « Inférieure Limite » ou « Variable Nom », on comprend bien que la traduction a, au mieux, été réalisée par un automate et, au pire, par un débile incompétent.

La valeur marginale de 62,5 pour la Tôlerie-Peinture signifie que – jusqu’à un certain point non indiqué dans le rapport – chaque unité en plus ou en moins par rapport aux 7.500 unités actuelles, a une valeur marginale de 62,5 €. On gagnerait 62,5 € de plus en passant à 7.501 !

Optimisation (PL) – Solveur

Nous sommes enfin prêts à utiliser le solveur d’Excel pour résoudre le problème posé dans l’article précédent. Vous devez trouver l’icône du solveur tout à fait à droite dans le ruban Données. Si ce n’est pas le cas, c’est que vous ne l’avez pas encore installé dans votre Excel. Pour cela, il faut passer par Fichier – Options – Compléments, trouver le solveur et l’installer. Dès que ceci est fait, l’icône devrait apparaître à la droite du ruban Données.

Formulation du problème

Pour gagner du temps, je vous conseille de cliquer dans la cellule E3 avant d’activer le solveur. Cela vous permettra – cf. la copie d’écran – d’avoir cette cellule par défaut dans le bloc « Objectif à définir : ».

En dessous, vous avez le choix entre trois options : maximisation, minimisation et valeur cible. Dans notre cas précis, c’est le premier bouton qui doit être coché.

Il faut ensuite identifier les cellules variables et ajouter les contraintes comme vous pouvez en voir le résultat dans la copie d’écran.

Je vous conseille ensuite de ne rien toucher aux réglages par défaut et de cliquer aussitôt dans le bouton Résoudre en bas de la feuille.

La fenêtre de résultat apparaît alors (cf. copie d’écran ci-dessous). Nous nous trouvons ici dans le meilleur des trois cas : « Le solveur a trouvé une solution… ». Les deux autres réponses possibles sont qu’il n’y a aucune solution réalisable (les contraintes interdisent toute solution), ou que la solution obtenue est infinie (on n’a pas mis asses de contraintes).

Je vous conseille à présent de sélectionner les trois rapports à droite puis de cliquer dans OK. Vous aurez alors les résultats en A1:E7 ainsi que trois nouveaux onglets à gauche pour les rapports demandés. Nous commenterons ces rapports dans le prochain article.

Optimisation (PL) – Enoncé

Nous débutons avec cet article une petite série sur l’optimisation en programmation linéaire. La programmation linéaire est le terme utilisé pour la modélisation d’un problème à résoudre quand l’objectif à atteindre et toutes les contraintes à respecter peuvent être formulés avec uniquement des variables au premier degré : pas de x², pas de sin(x), ….

C’est dire qu’il n’est pas possible de trouver plus simple mathématiquement car, quand on écrit x=1, c’est déjà une équation du premier degré.

La première bonne nouvelle, c’est que de très nombreux problèmes de gestion d’entreprise (finance, production, marketing, comptabilité, contrôle, stockage, …) peuvent être formulés en programmes linéaires.

La seconde bonne nouvelle, c’est que l’on sait résoudre ces problèmes par ordinateur depuis la découverte en 1947 de l’algorithme du simplexe. Et que le solveur, un add-in livré avec Excel, permet de mettre en œuvre cet algorithme.

Enoncé du problème

Notre entreprise produit des tondeuses appelées BR1 et BR2. On gagne 300 € par unité de BR1 et 350 € par unité de BR2. On entre en B3 et B4 les quantités fabriquées.  Pour une unité de BR1, il faut 3 unités de temps en tôlerie-peinture, 1 en assemblage, et 1 en montage 1. Les valeurs de E4 à E7 indiquent le nombre d’unités de temps disponibles dans le mois pour chacune des quatre contraintes.

Les formules de D4 à D7 calculent le montant de chaque contrainte utilisée. Il faudra bien entendu que ce montant ne dépasse pas les quantités disponibles indiquées en E4-E7.

Formulation mathématique du problème

La formulation mathématique du problème est indiquée dans la zone de texte en A10:C15. Il s’agit bien d’un problème de programmation linéaire car la fonction à optimiser (on appelle cela la fonction économique) est bien du premier degré, ainsi que chacune des contraintes qui suivent.

Nous verrons dans le prochain article comment, à l’aide du solveur d’Excel, résoudre ce problème de façon optimale.

Faire parler Excel (b)

Dans l’article précédent, nous avons vous comment Excel pouvait lire, à voix haute, quelle était la valeur de la cellule que l’on venait de définir ou de modifier.

En fait, on peut aussi faire « lire » à Excel ce que l’on veut, par exemple le contenu d’un ensemble de cellules sélectionnées. Voici la macro qui fait le travail :

Sub Cell_talk()

        For Each cell In Selection

            Application.Speech.Speak (cell.Value)

        Next

End Sub

Remarque – Ces cellules n’ont pas besoin d’être voisines : il est possible de sélectionner, la touche [Ctrl] restant enfoncée, des cellules disjointes avant de lancer la macro.

L’argument de la commande peut aussi être un texte que l’on a soi-même prédéfini, par exemple avec la commande Application.Speech.Speak ("The professor is always right")…

Je n’ai pas trouvé le moyen – peut-être n’existe-t-il d’ailleurs pas – de sélectionner la langue à utiliser dans la prononciation du texte sélectionné. Ce pourrait être amusant de choisir le français, l’anglais, l’allemand ou – pourquoi pas – des langues plus exotiques…