Monsieur Excel
Pour tout savoir faire sur Excel !

23 juillet 2018

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 !

17 juillet 2018

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.


11 juillet 2018

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 x2, 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.


04 juillet 2018

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…