Monsieur Excel
Pour tout savoir faire sur Excel !

29 décembre 2010

Restaurer l’environnement VBE

Tous ceux qui ont passé un certain temps avec l’éditeur Visual Basic ont été confrontés tôt ou tard au problème suivant : un beau jour, sans trop que l'on sache pourquoi, on perd la disposition naturelle du VBE (Visual Basic Editor) où l’on a, sur la gauche, la fenêtre des projets en haut et la fenêtre des propriétés en bas et, à droite, le code VBA.

Vous vous trouvez alors par exemple dans la situation suivante :

Vous fiant à ce que disent les livres et l’aide de Microsoft, vous pouvez benoîtement essayer de faire un clic droit dans le corps (pas le titre !) de la fenêtre Projet et cocher « Ancrable ». Dans ce cas, ou bien (1) vous avez de la chance et le bloc se cadre bien à gauche, ou bien (2) vous avez la poisse et le cadre se bloc horizontalement en haut, ou bien (3) la fenêtre se déplace sans se cadrer sur un bord.

Dans le cas n°2, faites un double clic sur le bandeau de la fenêtre pour la rendre non jointive à un bord, comme dans le cas n°3.

Dans les cas n°2 et n°3, réduisez à présent la fenêtre du VBE au cas où elle serait en plein écran. Ensuite, sélectionnez la fenêtre à cadrer en la prenant par son bandeau de titre et déplacez-la vers le bord sur lequel vous voulez l’ancrer. La fenêtre est représentée par un cadre avec un trait gris épais lors du déplacement.

Quand vous déplacerez cette fenêtre vers le bord du haut, vous verrez à un moment donné le liseré épais de la fenêtre se transformer en trait pointillé fin avec une forme de rectangle horizontal. Si vous lâchez le bouton de la souris à ce moment-là, la fenêtre sera ancrée horizontalement.

Si en revanche vous déplacez la fenêtre de la même façon vers le bord gauche, vous aurez à un moment donné un rectangle vertical en trait pointillé fin et, en lâchant le bouton, vous ancrerez alors la fenêtre verticalement. C’est le but que nous voulions atteindre : )

Remarque 1 – Il fallait que la fenêtre du VBE ne soit pas en plein écran pour que vous puissiez dépasser le bord gauche de cette fenêtre d’au moins la moitié de la largeur de la fenêtre à ranger afin de voir apparaître le liseré fin. C’est là le piège dans lequel tous ceux qui ont essayé en vain de recadrer les fenêtres du VBE sont tombés quand – comme moi – ils étaient en plein écran en espérant ainsi « mieux voir » ce qui se passait.

Remarque 2 – Tout ces problèmes auraient été évités si Microsoft avait pensé à créer deux commandes – Ancrage vertical et Ancrage horizontal – à la place d’une seule commande… Ou s’ils avaient tout simplement défini l’ancrage par défaut comme étant vertical, ce qui aurait été plus astucieux encore… Qui en effet pourrait vouloir ancrer les fenêtres horizontalement ?

25 décembre 2010

Joyeux Noël !

Un petit message à tous pour vous souhaiter un Joyeux Noël !

Je n’ai pas trouvé grand-chose sur Internet en cherchant Noël et Excel. Voici cependant une adresse d’où vous pourrez télécharger quelques exemples :

http://chandoo.org/wp/2010/12/15/christmas-cards-in-excel/

Ce qui m’a paru le plus intéressant, c’est l’arbre de Noël ci-dessous, avec les boules qui créent un effet de clignotement en changeant de couleur :

Et une petite distraction comme cadeau de Noël ! Avec Word, que ce soit en 2003 ou en 2007 (je n'ai pas encore testé sur 2010), amusez-vous donc à entrer dans un document vierge, puis à valider :

=rand(200,99)

Avec Word 2003, vous obtiendrez 19.800 fois « Servez à ce monsieur une bière et des kiwis ». Avec Word 2007, ce sera 2.200 fois un texte de 1.332 caractères commençant par « Sous l'onglet Insertion, les galeries... »

On a toujours des surprises avec Microsoft...

En conclusion, vous voilà avec un « œuf de Pâques » pour Noël !

22 décembre 2010

Graphe à deux axes en 2007

Nous avons vu dans les deux articles précédents comment réaliser en Excel 2003 un graphe avec deux axes Y.

La grande surprise, quand on essaye d’insérer un graphique et que l’on passe pour cela par la commande « Tous types de graphiques » qui se trouve en bas de chaque famille de graphiques, c’est qu’il n’y a plus de graphe à deux axes Y qui soit proposé en Excel 2007, à part les deux graphes tout à fait particuliers placés dans la famille « Boursier ».

Prenons donc un simple histogramme pour représenter le tout (cf. image ci-dessous). Bien entendu, cela n’est pas satisfaisant car nous voulons placer la dernière série – Total – sur un axe à droite et la transformer en courbe.

Pas de problème pour cela, il y a deux opérations à effectuer…

Tout d’abord, faire un clic droit sur l’une des quatre barres de la dernière série, puis les commandes « Mettre en forme une série de données » et « Axe secondaire ». La série « Total » est alors représentée par un histogramme épais au premier plan.

Ensuite, re-clic droit puis la commande « Modifier le type de graphique Série de données » (ce qui, entre nous, est un nom de commande totalement débile ! Les trois derniers mots sont de trop…) et sélectionner une courbe.

Il ne reste plus, pour parfaire la représentation, à mettre en rouge à la fois la courbe et les valeurs de l’axe droit. Nous obtenons alors le résultat désiré :



18 décembre 2010

Un graphe à deux axes amélioré

Nous avons vu dans l’article précédent que l’un des problèmes des graphes à deux axes en Excel 2003 – et nous aurons le même problème avec les versions 2007 et 2010 ! – est que, par défaut, la première moitié des séries est associée à l’axe gauche, et la seconde à l’axe droit.

Cela ne convient par aux situations fréquentes où – comme dans l’exemple du dernier article – l’on désire associer les n-1 premières séries à l’axe gauche, et seule la dernière série à l’axe droit.

Supposons que nous ayons 8 séries, 7 séries de produits et 1 série de total. Si l’on procède comme nous l’avons fait il y a quatre jours, nous aurons par défaut 4 séries à gauche et 4 séries à droite.

Il nous faudra alors réaliser 6 opérations consécutives : pour les séries 5 à 7, nous devrons changer leur type de graphique et leur axe.

Il y a alors une solution plus efficace : créez pour commencer un histogramme simple avec les 8 séries. Puis sélectionnez la série 8 (le total), modifier son type de graphique, puis son axe… Bilan : 2 opérations au lieu de 6 : )

Une astuce – Quand on réalise un graphe de ce type (ici, nous avons repris l’exemple de l’article précédent), pensez à rendre le graphe plus lisible encore.

Comment donc ? Eh bien tout simplement comme nous l’avons fait ci-dessous : nous avons mis le total en rouge (un trait rouge épais) et nous avons aussi mis en rouge les valeurs de l’axe droit, pour que l’utilisateur comprenne bien que c’est « Total » qui est rouge, et qu’il correspond bien à l’axe droit. C’est si simple, mais combien de gens y pensent ?




14 décembre 2010

Graphe à deux axes en 2003

Il arrive souvent dans la pratique que l’on cherche à construire un graphe avec deux axes Y, surtout quand l’ordre de grandeur des séries n’est pas homogène par exemple si l’on représente dans le même graphe divers chiffres d’affaires et les marges associées…

Dans l’exemple ci-dessous, nous souhaitons comparer des quantités vendues par produit avec le total des unités vendues, tous produits confondus. Dans cet article, nous analysons la façon d’y parvenir avec Excel 2003 ; nous verrons dans un article ultérieur comment cela se passe avec Excel 2007 (et donc 2010)…
Quand on passe par l’assistant graphique et l’onglet « Types personnalisés », plusieurs types de graphes à deux axes Y sont proposés sur la copie d’écran ci-dessous : Courbe - Histo. 2 axes, Courbes - Histogramme, Courbes à deux axes.

L’inconvénient est que, dans chacun de ces cas, Excel place la moitié des séries sur l’axe gauche et la moitié sur l’axe droit. Nous le voyons clairement dans le graphe ci-dessous, où deux séries sont en histogramme avec l’axe gauche et deux en courbe avec l’axe droit.

Remarque – Dans les premières versions d’Excel, une simple case dans le dialogue de création de graphe permettait de choisir le nombre de séries associées à l’axe gauche. Hélas, cette fonctionnalité géniale a disparu un beau jour, car elle troublait les utilisateurs trop « lambda ».

C’est bien regrettable car, souvent, on souhaite n’avoir qu’une série sur l’axe droit. Dans le cas présent, pour remettre la troisième série, Pentium C, en histogramme et sur l’axe gauche, nous sommes obligés de passer par deux opérations pour cette série : d’abord changement de type de graphe, puis changement d’axe…

10 décembre 2010

Les limites du solveur d’Excel

Le solveur d’Excel a été déveoppé par « Frontline Solvers » qui, sur son site http://www.solver.com/ ne se prive pas d’ailleurs de vous dire qu’il peut vous vendre mieux en direct !

Le solveur d’Excel est limité à 200 variables, les autres solveurs – qu’ils soient des add-ins d’Excel ou non – peuvent aller jusqu’à des milliers de variables. Une autre limite du solveur d’Excel, que Frontline oublie comme par hasard de préciser, est bien plus grave : le solveur d’Excel n’est réellement fiable que pour des modèles linéaires continus (la programmation linéaire).

Certes, le solveur d’Excel vous donne le droit de spécifier que telle ou telle variable est entière ou booléenne. Certes, il vous permet de définir des modèles comportant des contraintes non linéaires, avec des formules comportant par exemple des fonctions si() ou max(), des variables au carré, …

Mais, dans la résolution de problèmes contenant de telles variables ou de telles formules, nous avons observé (et la liste n’est pas exhaustive) les divers phénomènes suivants :

● le solveur déclare que l’une solution est optimale alors qu’elle ne l’est pas

● le solveur affirme qu’il n’y a pas de solution réalisable alors qu’il y en a

● le solveur trouve, ou ne trouve pas, la solution optimale selon l’ordre des contraintes

Le résultat de mes diverses expériences est donc le suivant : le solveur d’Excel n’est fiable, je le répète, que pour des modèles linéaires continus.

Pour clore cette série sur le solveur par là où nous avons commencé, voici une énigme que vous pouvez vous amuser à résoudre. Si quelqu’un connaît l’auteur de cette énigme (que l’on trouve en anglais sur Internet, à de multiples endroits), je serai ravi de pouvoir lui rendre honneur.

Dans 10 ans, Tim sera deux fois aussi vieux que Jane ne l'était quand Mary avait 9 fois l'âge de Tim. Il y a 8 ans, Mary avait la moitié de l'âge que Jane aura quand elle aura un an de plus que l'âge que Tim aura quand Mary sera 5 fois plus vieille que Tim dans 2 ans. Quand Tim avait 1 an, Mary était de 3 ans plus vieille que Tim ne le sera quand Jane sera 3 fois aussi vieille que Mary l'était 6 ans avant l'année où Jane a eu la moitié de l'âge que Tim aura quand Mary aura 10 ans de plus qu'elle ne l'était quand Jane avait le tiers de l'âge que Tim aura quand Mary aura 3 fois l'âge qu'elle avait quand Jane est née. Quels sont leurs âges aujourd'hui ?

05 décembre 2010

Les rapports du solveur

Quand on lance le solveur, trois réponses sont possibles. Dans le meilleur des cas (cf. Figure 1), le solveur trouve la solution et propose trois rapports. Deux autres cas sont possibles : le solveur ne trouve aucune solution réalisable – vous avez alors mis des contraintes incompatibles – ou alors il trouve une solution infinie – vous n’avez pas mis assez de contraintes…

Intéressons-nous au cas où la solution a été trouvée. Il vous suffit alors de sélectionner un ou plusieurs rapports dans la fenêtre et de valider pour les obtenir.

Chaque rapport occupe une feuille, placée en amont de la feuille active. Un regret par rapport aux noms de ces feuilles : ils sont bien trop longs, avec
« Rapport des réponses 1 », « Rapport de la sensibilité 1 », « Rapport des limites 1 ». Pour ma part, je les aurai nommés « Réponses », « Sensibilité »
et « Limites » : il est rarement efficace d’avoir des noms d’onglets longs.

Quel que soit votre objectif, maximisation, minimisation ou valeur cible, le seul rapport qui vous donne de réelles informations utiles est le rapport de sensibilité. Il avait bien été traduit dans la première version du solveur d’Excel. Hélas, dans une version ultérieure – et ce depuis plus de 15 ans ! – il est horriblement mal traduit : soit Microsoft a utilisé un logiciel de traduction automatique, soit le travail a été confié à un stagiaire au QI inférieur à 100…

Pour vous dire combien c’est mauvais, imaginez donc que le terme « Shadow cost » a été traduit par « Ombre coût », terme que je vous ne trouverez dans aucun livre d’économie français ! Non seulement la traduction est infecte, mais la présentation des résultats n’est pas homogène avec celle des logiciels d’optimisation usuels : j’ai donc développé pour mes clients une macro qui transforme l’infâme rapport de sensibilité du solveur d’Excel en un rapport utile et efficace.

L’information vraiment utile que l’on trouve dans le rapport de sensibilité est justement la valeur marginale (le shadow cost ou reduced cost, selon le bloc considéré), ainsi que l’intervalle dans lequel elle est valide.

Prenons le cas de la cellule B10 (usine 2 vers entrepôt A). Cette cellule est vierge, on n’y transporte donc rien dans la solution optimale. Si l’on devait y transporter une unité, pour continuer à respecter les contraintes en ligne en colonne, il faudrait opérer un déplacement selon les quatre coins du rectangle rouge (cf. Figure 3) : ajouter en B10, ôter en E10, ajouter en E11 et ôter en B11. Cela représenterait un surcoût de 8 euros : +3, -1, +14, -8. Eh bien, c’est justement le résultat affiché en E14 dans le rapport de sensibilité !

C’est ce genre de renseignement fort utile que l’on trouve dans le rapport de sensibilité !

01 décembre 2010

Optimisation avec le solveur

Après notre première incursion auprès du solveur il y a quatre jours, nous allons découvrir aujourd’hui sa capacité à dénicher la solution d’un problème d’optimisation.

Nous avons trois usines et quatre entrepôts (A, B, C et D). Le tableau en B2:E14 représente les coûts unitaires de transport de chaque usine vers chaque entrepôt. La ligne 5 représente les besoins de chaque entrepôt et la colonne G ce qui est disponible dans chaque usine.

Le problème à résoudre est de trouver les quantités à transporter (bloc B9:F11) qui vont, en respectant les contraintes de besoin et de disponibilité, minimiser le coût total en G14, évalué par la formule =sommeprod(B2:F4;B9:F11). En B12:F12 et G9:G11 enfin, nous avons les sommes verticales et horizontales.

Le problème est résolu aisément à l’aide du solveur comme nous le voyons ci-dessous avec le résultat, puis avec le réglage du solveur qui a permis de l’obtenir :

Remarque – Il ne faut pas vous contenter des réglages ci-dessus ! Vous devez aussi, avant de cliquer dans le bouton « Résoudre », passer par le bouton
« Options » et cocher les options « Modèle supposé linéaire » et « Supposé non-négatif ».

Si vous oubliez la dernière option, le solveur d’Excel ne parvient pas à converger ! En effet, il essaye en vain de transporter des quantités négatives pour réduire le coût total.

Si vous oubliez de spécifier que le modèle est linéaire, il y aura de petites erreurs d’arrondis et, en outre, vous ne bénéficierez pas – après la résolution – de toutes les informations utiles (en particulier, des valeurs marginales).