Monsieur Excel
Pour tout savoir faire sur Excel !

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…

28 juin 2018

Faire parler Excel (a)

Pour faire la suite des quatre articles précédents, j’aurais pu titrer celui-ci « Pour qu’Excel se mette à table ! ».

Il y a deux façons de faire parler Excel. La première consiste à lui faire dire à haute voix quel est le contenu de la cellule qui vient d’être définie. C’est cela dont nous traiterons dans l’article de ce jour. La seconde consiste à choisir ce que l’on veut entendre Excel lire à haute voix : cela sera l’objet du prochain article.

Pour personnaliser la barre d’accès rapide (QAT en anglais pour Quick Access Toolbar), il faut cliquer dans le bouton à droite de cette barre, sélectionner Autres commandes puis Toutes les commandes. Vous voyez alors apparaître la fenêtre dont la copie d’écran ci-dessous représente une partie.


Ce sont les quatre dernières commandes de la copie d’écran qui nous intéressent. La première représente un interrupteur qui active ou désactive la lecture de ce que l’on saisit dans les cellules. Un message sonore d’Excel, quand vous cliquez dans le bouton, vous dit si c’est activé ou non. La seconde, quand elle est activée, vous lit la valeur de toute cellule quand vous appuyez sur Entrée.

Si c’est une formule qui a été saisie, c’est la valeur de son résultat qui est lue.

Remarque – Si vous voulez rigoler, entrez une formule telle que =A12/0

Je n’ai pas vu à quoi servaient les deux dernières commandes.

22 juin 2018

Un autre bug de table (b)

J’ai mis en note dans l’article précédent une quatrième condition à respecter pour que le bug survienne : il faut utiliser une version d’Excel antérieure à 2016. En fait, j’ai vérifié qu’il fonctionne toujours avec Excel 2013.

En résumé, il faut donc respecter 4 conditions :

1. Il faut une table, ici le bloc C1:C4.

2. Il faut une formule faisant référence à la table et au moins à une cellule en dessous, ce qui est ici le cas de la formule =somme(C2:C5) de la cellule B6.

3. Il faut qu’il y ait une zone de filtre logée dans une autre colonne. C’est ce que nous avons fait avec A1 à qui nous avons appliqué la commande « Filtrer » du ruban Données.

4. Il faut utiliser une version d’Excel antérieure à 2016.

Remarque – En fait, des lecteurs m’ont fait remarquer qu’ils ne rencontraient pas le bug sur 2016. J’ai testé sur Excel 2016 et – de fait – le bug ne survenait pas. J’ai ensuite retesté sur Excel 2013 et le bug était toujours là ! Je n’ai pas encore testé cela sur Excel 2010 ou 2007…


Microsoft lui-même dit qu’il faut prendre un certain nombre de précautions quand on travaille avec des tables. Vous pourrez lire l’article « Création ou suppression d’un tableau Excel » à l’adresse :
                       

13 juin 2018

Un autre bug de table (a)

Les deux articles précédents ont inspiré un de nos lecteurs, Philippe Monin, qui a découvert un bug tout à fait original, lié lui aussi aux tables dans Excel. Je n’avais encore jamais rencontré ce bug mais il faut reconnaître qu’il est lié à un concours de circonstances tout à fait improbable.

Il faut en effet que trois conditions soient réunies pour que le bug « fonctionne ». Si tant est que l’on puisse dire qu’un bug peut fonctionner…

Il faut une table, ici le bloc C1:C4.

Il faut une formule faisant référence à la table et au moins à une cellule en dessous, ce qui est ici le cas de la formule =somme(C2:C5) de la cellule B6.

Il faut enfin – troisièmement – qu’il y ait une zone de filtre logée dans une autre colonne. C’est ce que nous avons fait avec A1 à qui nous avons appliqué la commande « Filtrer » du ruban Données.


Maintenant que tout est en place, vous pouvez réaliser l’expérience… Copiez la cellule D9, qui contient la valeur 13, cliquez en C3 et collez. Et, oh miracle, au lieu de prendre la valeur 13, la cellule C3 prend la valeur 7, qui correspond au contenu de la cellule en dessous !!!

Si une seule des trois conditions présentées ci-dessus n’est pas présente, le bug ne survient pas !!!

Mes félicitations à Microsoft, qui a réussi à inventer un bug particulièrement original, et à Philippe dont le travail d’apothicaire a réussi à isoler ce problème totalement original !

Remarque – Nous ferons quelques remarques complémentaires dans le prochain article.

05 juin 2018

Un bug de table (b)

Dans l’article précédent, nous avons montré comment construire un exemple simple illustrant un problème original avec une table de données.

Le problème provient de la formule de la cellule K2 : =$B$3 fait en effet référence à la valeur de départ de 150.

La difficulté, c’est que cette cellule précisément représente le point de départ de la table. Quand le calcul de la table se déroule, cette valeur de départ est en fait remplacée au fur et à mesure par les valeurs de K3 à K6. Excel ne sait donc plus bien où donner de la tête…

La meilleure preuve, c’est que si l’on entre 150 en K2, la table fonctionne parfaitement et les valeurs de le colonne L sont identiques à celles de la colonne N.

Dans la table originale, nous avions constaté que les résultats en L2 et L3 étaient bons. Le résultat en L4 était décalé d’une valeur, celui en L5 de deux valeurs et celui en L6 de trois valeurs !

Il est évident que ces décalages proviennent du fait que, la formule originale de K2 provoquait en fait une sorte de référence circulaire quand on créait la table. Ce que je ne peux pas expliquer, c’est pourquoi l’on observait ces décalages de 0 ligne, 1 ligne, 2 lignes, 3 lignes…