Monsieur Excel
Pour tout savoir faire sur Excel !

29 septembre 2011

Fonction économique modulable

Pour terminer en beauté la série d’articles que nous venons de publier sur la programmation linéaire, et pour assister ceux parmi les lecteurs qui enseignent la modélisation, il ne reste plus qu’à créer un ascenseur grâce auquel nous pourrons déplacer la fonction économique parallèlement à elle-même.

La première étape pour cela consiste à s’assurer que les déplacements de la droite resteront parallèles. C’est une simple affaire : il suffit d’entrer en H14 la formule =G13*300/350.

La seconde étape revient à créer l’ascenseur.

Avec Excel 2007 et suivants, activons l’onglet « Développeur », commande «Insérer », bloc « Contrôles de formulaire », outil « Barre de défilement ».

Avec Excel 2003, sélectionnons l’outil « Barre de défilement » de la barre d’outils « Formulaire ».

Traçons un ascenseur vertical. Faisons un clic droit dedans puis actionnons la commande « Format de contrôle ». définissons G13 comme cellule liée et faisons varier la barre entre un minimum de 0 et un maximum de 3500, avec par exemple un pas de 5 et une page de 100.

Et voilà ! En déplaçant le curseur, vous faites évoluer la fonction économique. Vous avez là un excellent outil pédagogique pour expliquer la programmation linéaire. Comme vous le voyez ci-dessus, où nous avons ôté – pour mieux voir la fonction économique – le remplissage bleu du domaine des solutions réalisables.

25 septembre 2011

Tracé de la fonction économique

La fonction économique est la fonction à optimiser, ici il s'agit de la fonction 300*BR1+350*BR2.

Pour la tracer, nous sommes confrontés à un problème : sa formulation indique la pente de la fonction économique mais ne nous permet pas de tracer une droite. Pour cela, il nous faut associer une constante, sous la forme 300 BR1+350 BR2 = Constante.

Prenons, pour simplifier les calculs, la formulation 300*BR1+350*BR2 = 10*300*350, ce qui nous donne les coordonnées en G13:H14 pour l’intersection de cette droite avec l’axe des X et l’axe des Y.

Sélectionnons le bloc G13:H14, copions-le, sélectionnons la fenêtre contenant le graphe, puis utilisons la commande « Collage spécial » en prenant les options « Nouvelle série » et « Abscisses dans la première colonne ». Nous voyons le résultat ci-dessous, avec la fonction économique que nous avons décorée en rouge.

Et la résolution du programme linéaire dans tout cela ? Eh bien, il suffit de trouver quel est le point de l’hexagone par lequel on peut faire passer la droite parallèle à la droite rouge qui soit la plus haute possible (on souhaite maximiser la fonction économique).

Sur l’image ci-dessous, on constate aisément qu’il s’agit bien du point x=2000 et y=750 qui avait effectivement été identifié par le solveur (cf. article du 13 septembre).

21 septembre 2011

Représentation graphique du PL

Le programme linéaire (PL) que nous étudions depuis quelques articles possède une particularité tout à fait intéressante : il ne possède que deux variables, ce qui nous permet de représenter le problème graphiquement dans un plan.

Dans cet article, nous verrons comment représenter graphiquement le domaine réalisable, c’est-à-dire le tracer dans un plan. Ce domaine regroupe l'ensemble des solutions possibles.

Il faut pour cela entrer dans les coordonnées X (colonne G) et Y (colonne H) de deux points de chaque contrainte, en laissant une ligne blanche entre les contraintes. Nous sélectionnons ensuite G1:H11 puis insérons un graphe en X/Y (nuages de points) avec des segments.

Comme on le voit dans le graphe ci-dessous, on obtient le graphe représentant toutes les contraintes, où j’ai ensuite identifié le domaine réalisable en lui superposant une « forme libre » – c’est ainsi qu’Excel appelle une séquence de segments connectés – que j’ai remplie de bleu.

Le domaine réalisable est donc l’hexagone peint en bleu : chaque point à l’intérieur de l’hexagone respecte l’ensemble des contraintes, et chaque point en dehors viole au moins une des contraintes.

17 septembre 2011

Les trois rapports du solveur

Les trois rapports du solveur correspondent à des onglets qui viennent s’insérer juste avant l’onglet où se trouve le modèle linéaire que l’on a optimisé.

Ils portent les noms « Rapport des réponses 1 », « Rapport de la sensibilité 1 » et « Rapport des limites 1 ».

Remarque – On peut déplorer que les noms de ces trois onglets soient si longs : plus des noms d’onglets sont longs, moins on arrive à en voir à la fois dans la barre des onglets. Les noms « Réponses », « Sensibilité » et « Limites », par exemple, auraient été bien préférables ! Je conseille en règle générale d’utiliser des noms d’onglets aussi courts que possible, sans bien entendu en arriver à ce qu’ils ne soient pas significatifs.

En fait, des trois rapports, le premier et le troisième sont totalement inutiles si l’on a choisi de garder dans la feuille du modèle la solution optimale. Le seul rapport qui fournisse des informations utiles est le rapport de sensibilité.

Si ce rapport ne comporte pas toutes les colonnes ci-dessous, c’est qu’il faut recommencer l’optimisation en cochant vie la bouton « Options » l’option
« Modèle supposé linéaire », si vous utilisez Excel 2003 ou2007. Avec 2010, il faut choisir « Simplex PL » dans la zone « Sélectionner une résolution ».

La traduction de cet onglet en français a été un véritable massacre, effectué par un imbécile congénital. C’est du mot à mot, par exemple « finale Valeur » parce qu’en anglais on dit « Final value »… Le plus surréaliste est le « Shadow cost » qui – comme de bien entendu – est devenu « Ombre Coût » - ce qui ne signifie absolument rien en français, alors qu’il aurait fallu utiliser « Valeur marginale » ou « Valeur duale ». Notez que cela fait plus de 10 ans que j’en ai informé Microsoft…

Ceci dit, les renseignements fournis par cet onglet sont précieux. On apprend ainsi en E16 que tout relâchement de la contrainte d’assemblage nous ferait gagner 112,50 € par unité, jusqu’à une augmentation de 1.000 unités maximum (cf. G16). Au-delà on gagnerait soit plus rien, soit moins.

Bon, j’arrête là, je n’ai ni le temps ni la place ici de vous faire tout un cours sur la programmation linéaire, mais sachez que c’est un domaine très riche et que cela permet de bien résoudre de multiples problèmes d’optimisation des ressources dans les entreprises.

13 septembre 2011

Résolution avec le solveur

Pour résoudre le problème d’optimisation présenté il y a quatre jours, il faut utiliser le solveur d’Excel, un complément disponible dans l’onglet « Données ». S’il n’y apparaît pas, à droite de l’onglet, utilisez les « Options Excel », section
« Compléments », et activez-le.

Nous réglons alors le solveur en définissant ses paramètres comme dans la seconde image ci-dessous et en cochant l’option « Modèle supposé linéaire » via le bouton « Options » de ce dialogue.

Remarque 1 – Si vous oubliez de spécifier que le modèle est linéaire, les rapports que vous pourrez obtenir après l’optimisation seront moins détaillés.

Remarque 2 – Notez que vous gagnez du temps en écrivant les contraintes sous forme matricielle : ainsi, $D$4:$D$7<=$E$4:$E$7 signifie en une seule formule que vous voulez respecter les quatre contraintes $D$4<=$E$4, $D$5<=$E$5, $D$6<=$E$6 et $D$7<=$E$7 !

Excel affiche alors le dernier dialogue ci-dessus pour vous avertir que la solution optimale (cf. première image ci-dessus) a été trouvée. Les deux autres issues possibles sont « Solution infinie » – auquel cas vous avez oublié de formuler certaines contraintes – ou « Pas de solution réalisable », qui correspond au cas où il n’y a aucune solution satisfaisant à toutes vos contraintes…

Si vous êtes satisfait de la solution, sélectionnez éventuellement un ou plusieurs rapports et quittez ce dialogue en cliquant dans « OK » pour garder la solution optimale. Nous parlerons dans le prochain article de l’intérêt de chacun des trois rapports.

09 septembre 2011

Programmation linéaire – Intro

La programmation linéaire est la technique d’optimisation la plus utilisée au monde. Elle consiste à résoudre des problèmes formulés à l’aide d’équations ou d’inéquations à plusieurs variables, chaque formule étant exprimée au premier degré – d’où le terme « linéaire ».

Il n’y a pas plus simple que le premier degré : quand on écrit x=1, on est déjà au premier degré ! Heureusement pour les entreprises, les modèles linéaires, qui sont donc les plus simples possibles, permettent de formuler – et donc résoudre – une très grande variété de problèmes de production, logistique, finance, marketing… Toutes les branches de la gestion utilisent des modèles linéaires, jusqu’aux ressources humaines !

Le problème ci-dessous est un problème de production. Nous fabriquons deux produits, BR1 et BR2. Les quantités à fabriquer sont entrées en B2 et C2. Pour fabriquer une unité de BR1, il faut trois unités de tôlerie-peinture et une d’assemblage. On dispose de 7.500 unités de tôlerie-peinture dans la période analysée. On a la contribution par unité (B3 et C3). Le but est d’identifier les quantités optimales BR1 et BR2 qui – en respectant toutes les contraintes – permettront d’obtenir la contribution maximale.

Nous avons mis dans une zone de texte la formulation mathématique du problème. Il s’agit bien de programmation linéaire puisque la fonction économique (la fonction à optimiser) et toutes les contraintes sont linéaires.

La formule de D4 (recopiée jusqu’en D7) est =sommeprod($B$2:$C$2;B4:C4) et celle de E3 est =sommeprod(B2:C2;B3:C3). Il n’y a que cinq formules dans le modèle.

Nous verrons dans le prochain article comment utiliser le solveur d'Excel pour trouver la solution optimale.

05 septembre 2011

Création du schéma précédent

Une particularité du schéma publié il y a quatre jours est que les connecteurs reliant les boîtes du schéma sont dynamiques : si donc vous déplacez ou redimensionnez une des boîtes, les connecteurs s’adaptent de façon à préserver les liens antérieurs.

Nous allons prendre l’exemple du connecteur reliant la boîte « Assemblage » à la boîte « Travaux finition ». Nous traçons en premier lieu (première image ci-dessous) un arc dans le vide.

Ensuite (image ci-dessus), nous cliquons sur le connecteur, sélectionnons la poignée de sélection en haut du connecteur, et la tirons à l’intérieur de la boîte
« Assemblage ». Apparaissent alors les quatre marqueurs au milieu des bords de la boîte « Assemblage » : il suffit de lâcher la poignée sur le marqueur voulu – qui affiche aussitôt un gros rond rouge – pour ancrer la connexion à cet endroit.

Si l’on déplace ensuite la boîte « Assemblage », on constate que le connecteur s’adapte au déplacement et préserve son ancrage.

On fait de même ensuite avec la poignée basse du connecteur et la boîte
« Travaux finition » pour ancrer de la même façon le connecteur avec cette boîte. Le connecteur est à présent ancré aux deux boîtes et s’adaptera à tout déplacement ou redimensionnement de l’une ou de l’autre.

01 septembre 2011

PERT – Le schéma

Nous avons vu le 25 août le graphe permettant de suivre l’évolution chronologique des différentes tâches du projet.

Nous aurions aussi pu enrichir notre modèle en faisant apparaître dans le graphe, dans une autre couleur, la marge de temps dont on dispose pour les tâches non critiques avant qu’elles en deviennent critiques à leur tour.

En revanche, ce que l’on ne voit pas bien dans le graphe, c’est le lien de dépendance entre les prédécesseurs et les successeurs dans la séquence des tâches. Pour cela, il est intéressant de disposer d’un véritable schéma.

Nous avons créé le schéma ci-dessus en utilisant à la fois des formes et des connecteurs. Pour le contenu des boîtes, nous avons créé en V2 la formule =B2&car(13)&"Date : "&J2&car(13)&"Durée : "&C2, que nous avons ensuite recopiée vers le bas.

Pour associer le texte résultant de la formule de V2 à la première boîte, il a suffi – après l’avoir sélectionnée – de taper =V2 dans la barre de formule. Comme nous avons obtenu les autres boîtes par clonage, il suffisait pour chaque nouvelle boîte de modifier le numéro de ligne dans la formule.

J’ai enfin mis en fond jaune, à la main, toutes les tâches du chemin critique…

Je n’ai hélas pas trouvé de solution pour associer à chaque boîte un format conditionnel – identifiant le chemin critique – qui soit lié aux dates au plus tôt et au plus tard de chaque boîte, et tel que l'on puisse le recopier automatiquement lors du clonage des boîtes…

Remarque – Ce schéma est bien plus utile, pour visualiser les dépendances entre les tâches, que le graphe du 25 août !