Monsieur Excel
Pour tout savoir faire sur Excel !

21 mai 2017

Localiser un nom élastique

Quand on a défini un nom élastique, on peut avoir envie de le sélectionner pour vérifier qu’il a été défini sans erreur. Hélas, quand on déroule la « Zone Nom », à gauche de a barre de formule, on découvre avec dépit que les noms élastiques ne sont pas complètement reconnus comme des noms par Excel et n’appartiennent donc pas à la liste.

Heureusement, Excel possède une commande géniale pour atteindre directement une zone nommée : [Ctrl]-t (pensez à Atteindre). Certes, quand vous l’activez, les noms dynamiques ne sont pas dans la liste des noms proposés, mais vous pouvez alors taper le nom concerné et cliquer dans le bouton « OK ».

Ceci dit, dans certaines circonstances, Excel reconnaît les noms élastiques comme des noms. Par exemple, quand vous créez une formule, la commande [F3] vous permet de coller un nom dans la formule. Eh bien, dans la liste des noms qui apparaît alors, les noms élastiques sont présents. Allez comprendre...

Pour ma part, je suis un fervent utilisateur de la commande « Atteindre ». Outre la possibilité que nous venons d’indiquer, cette commande vous donne accès, via son bouton « Cellules » – parmi la sélection de cellules en cours – aux cellules qui possèdent telle ou telle caractéristique :


13 mai 2017

Un bug dans les graphes

Il y a depuis Excel 2007, je crois – sinon, c’est depuis Excel 2010 – un bug dans les graphes d’Excel dont j’ai déjà d’ailleurs eu l’occasion de parler dans le passé dans ce blog. C’est qu’il n’est plus possible d’entrer directement dans la formule serie() un nom de série débutant par « L » ou « C », si celui-ci est un nom élastique (on dit aussi « dynamique »).

Supposons que vous ayez créé un second nom élastique, Ces_Y, avec exactement la même définition que dans l’article précédent. De bonne foi, vous sélectionnez la série, vous cliquez dans la barre de formule et vous remplacez le « M » de Mes_Y par « C ». Rien ne se passe, pas même un message d’erreur !

Vous cliquez alors dans « Filtres du graphique », la dernière icône à droite, vous activez la commande « Sélectionnez les données », vous cliquez sur le bouton « Modifier » et vous remplacez le « M » de Mes_Y par « C ». Et là, tout baigne, il n’y a aucun problème !

J’ai découvert ce problème chez un client auprès duquel j’avais ainsi rendu dynamique toute une série de graphes. Eh bien, ce problème survient chaque fois que le nom élastique débute par « C » ou « L » : Excel fait alors une confusion gigantesque avec Colonne et Ligne. La preuve, c’est que dans un Excel en anglais, c’est le « C » et le « R » qui sont touchés.

J’ai signalé ce bug à Microsoft depuis plusieurs années déjà, mais il n’est évidemment pas dans la liste des priorités…

08 mai 2017

Création d’un graphe élastique

Quand on crée un graphe dans Excel, on a parfois besoin que ce graphe soit « élastique », c’est-à-dire qu’il s’adapte automatiquement à l’apparition de nouvelles données. Prenons un exemple simple, nous avons saisi la formule =alea() en A1, nous l’avons recopiée vers le bas jusqu'en A10, et nous avons enfin tracé un graphe.


En ce qui nous concerne, le graphe a été défini par les valeurs de A1:A10, ce qui entraîne deux conséquences…

Si nous tirons A10 jusqu’en A13, le graphe reste bloqué sur A1:A10 et n’inclut pas les trois nouvelles valeurs.

Pire encore, si nous effaçons A8:A10 pour ne garder que les 7 premières valeurs, le graphe continue à afficher l’axe des X avec des valeurs de 1 à 7, laissant supposer que les trois dernières valeurs sont à 0.

Remarque – Certes, un observateur perspicace pourra dire que, si ces trois valeurs étaient à 0, cela serait marqué par un segment bleu sur l’axe des X. Mais, vous le savez comme moi, les gens perspicaces sont minoritaires…

Quand vous cliquez sur la série, la barre de formule affiche : =SERIE(;;Graphe!$A$1:$A$10;1).

En utilisant la technique présentée dans le dernier article, définissez le nom élastique Mes_Y par :
=Graphe!$A$1:decaler(Graphe!$A$1;nbval(Graphe!$A:$A)-1;0)

Allez alors dans la barre de formule et remplacez $A$1:$A$10  par mes_y.

Vous constatez alors que le graphe s’adapte immédiatement aux 7 valeurs restantes.

Resélectionnez le graphe et consultez la barre de formule. Vous pouvez voir que le nom de l’onglet a été remplacé par celui du classeur. Pourquoi ? Tout simplement parce qu’un nom est une propriété du classeur alors qu’une adresse (A1:A10) est une propriété de l’onglet !

Et voilà ! Vous avez à présent un graphe élastique.
Tirez A7 vers le bas et les nouvelles valeurs sont aussitôt intégrées dans le graphe.

01 mai 2017

Définition d’un nom élastique

Les noms élastiques ne sont pas souvent utilisés dans les modèles que je vois chez mes clients. J’avais déjà traité de ce sujet dans le passé, mais je pense qu’il mérite d’être revu car ces noms sont très utiles.

Un nom élastique, c’est un nom qui s’adapte automatiquement au nombre de données présentes. Supposons par exemple que nous disposions en colonne C d’une liste de pays. Nous utilisons la commande « Validation » du menu ou de l’onglet « Données » pour définir une liste de validation en A2, comme on peut le voir dans la copie d’écran ci-dessous :


Cela fonctionne très bien mais la liste est figée aux six pays du bloc C1:C6. Qu’arrive-t-il si nous souhaitons enrichir cette liste an ajoutant des pays ? Cela ne sera hélas pas répercuté dans le menu déroulant, sauf si l’on redéfinit l’adresse de la liste.

Pour éviter ce problème, il suffit de définir un nom dynamique identifiant les pays, à l’aide de la formule : =Elastique!$C$1:decaler(Elastique!$C$1; nbval(Elastique!$C:$C)-1;0) où « Elastique » est le nom de la feuille. Après avoir défini ce nom élastique, il ne vous reste plus qu’à remplacer dans la définition de la liste de validation =$C$1:$C$6 par =Pays.

Remarque 1 – Si vous oubliez le signe « = », la liste de validation ne vous donnera que « Pays » comme possibilité !

Et voilà ! Le tour est joué ! Ajoutez deux ou trois pays, déroulez le menu en A2, et constatez que les nouveaux pays sont automatiquement intégrés à la liste…

Remarque 2 – On pourrait se passer de la fonction decaler() en utilisant des tables. Mais l’utilisation de tables pose certains problèmes par ailleurs : je préfère donc les éviter dans ce type de situation. Si vous vouylez en savoir plus à ce sujet, lisez mon article du 9 juillet 2011.

26 avril 2017

Le pouvoir immense d’Excel...

Dans la réunion des MVPs Excel présentée dans l’article précédent, j’ai animé une conférence baptisée « The immense power of Excel without BI ». La Business Intelligence (BI) revient à utiliser Excel, Power Query et Power BI pour gérer intelligemment des bases de données parfois très importantes.


Loin de moi l’idée, par le titre de ma présentation, de dire que cette voie de recherche n’était pas intéressante. Je voulais simplement souligner que, même sans ces outils, Excel demeure un outil permettant de construire de puissants modèles bien plus rapidement et bien moins cher qu’avec d’autres outils.

Certes, tout le monde connaît la puissance d’Excel dans le monde de la finance, du marketing, de la comptabilité et du contrôle de gestion. Ce qui est moins connu, et qui pourtant a représenté une part non négligeable de mon activité de consultant depuis des années, c’est que l’on peut créer avec Excel une variété infinie de modèles de toute sorte. En voici quelques exemples…

Simulation des tapis roulants portant les bagages à Roissy

Air France louait à AdP (Aéroports de Paris) un système de tapis roulants portant les bagages, et n’était pas du tout content du service. Air France réclamait à AdP des millions d’euros de dommages et intérêts. AdP a lancé un appel d’offres pour simuler le système et, d’une part, voir comment se défendre contre Air France et, d’autre part, éventuellement modifier le système pour le rendre plus performant. J’ai gagné cet appel d’offres avec un modèle vendu 300.000 F (45.732 €). Le second concurrent le moins cher dans l’appel d’offres avait un devis de 1,5 MF (228.659 €), soit 5 fois plus cher ! J’étais bien entendu le seul à utiliser Excel pour cette simulation.

Grâce aux résultats de mon modèle, Air France a été obligé de retirer ses réclamations…

Système expert : diagnostic des tuyaux dans les centrales nucléaires

EdF avait besoin d’un système expert pour diagnostiquer les tuyaux dans les centrales nucléaires afin de les remplacer au bon moment, en maintenance préventive, avant qu’ils n’aient des problèmes dont les conséquences pouvaient être très graves.

EdF avait prévu à cet effet un développement en C++ de 6 mois avec un coût de 80.000 €. Je l’ai fait en 6 semaines pour 23.000 €.

Le plus intéressant à ce sujet est que, plus de 10 ans plus tard, après que j’ai montré ce modèle à un de mes clients dans le monde pharmaceutique, ce client m’a demandé de construire, sur le même principe, un système expert de diagnostic médical.

En effet, qu’est-ce qu’un être humain sinon un ensemble de tuyaux qui peuvent, eux aussi, éclater avec alors des conséquences très graves ?

Simulation routière

J’ai construit aussi un modèle permettant de simuler le trafic routier sur un ensemble de quelques kms carrés avec des ronds-points, des feux,…

La particularité de ce modèle, construit avec un budget de 75.000 €, est qu’il a été amorti en moins de 3 mois. Il était prévu en effet, pour résoudre un problème d’embouteillage chronique à une intersection, de remplacer un rond-point par un système de feux. Le modèle a apporté la preuve que, si l’on faisait cela, l’embouteillage aurait certes été résolu au point critique mais que, par voie de conséquence, il serait réapparu quelques centaines de mètres plus loin.

Analyse des risques liés au programme Ariane 5

EADS m’a demandé de les aider à construire un modèle permettant d’analyser tous les risques liés au programme Ariane 5 avec ses 30 lanceurs et de calculer les conséquences financières liées à ces risques. L’idée était de mettre en place une procédure de suivi mensuel de ces risques afin de voir comment l’on pouvait les réduire ou les éliminer – grâce à la recherche et du développement – ou, si ce n’était pas possible, les réassurer afin de réduire leur impact potentiel.

Ce modèle m’a valu un prix de la modélisation financière. En faisant varier un curseur, pour n’importe quel risque, on pouvait voir quels étaient les facteurs clefs de ce risque selon que l’on voulait se couvrir à 80%, à 90%, à 95%...

En guise de conclusion…

Ces quatre exemples montrent que les applications possibles d’Excel sont sans limites. Pour ne citer que les 15 dernières années, chaque fois que j’ai proposé une solution avec Excel, ma solution était au moins 3 à 10 fois plus rapide (en temps de développement) que les autres, et 3 à 10 fois moins chère. Vous en trouverez des exemples dans l’article Réflexions sur la modélisation du 28 octobre 2010.

Si plusieurs lecteurs souhaitent que je présente l’un de ces modèles plus en détail dans un article ultérieur, je le ferai volontiers.

22 avril 2017

Excel MVP Meeting à Amsterdam

Depuis plusieurs années, Jan Karel Pieterse organise à Amsterdam, avec l’assistance de Microsoft et dans ses locaux près de l’aéroport, un congrès de deux jours réunissant des MVPs Excel européens. Nous nous retrouvons à une vingtaine et nous échangeons entre nous et avec quelques représentants des équipes de développement Excel de Microsoft.

Comme chaque année, deux ou trois MVPs américains ou canadiens sont venus participer à ces échanges. Cette année, il y avait deux habitués, Jon Peltier et Ken Puls, et un nouveau-venu, Oz du Soleil. La France était – hors notre hôte les Pays-Bas, bien sûr – le pays européen le mieux représenté avec ses trois participants : Cathy Monier, Frédéric le Guen et votre serviteur.

Etant tenu – comme tous les MVPs – par des accords de NDA (non-disclosure agreement), je ne peux pas vous raconter ce qui s’y est passé mais je peux en tout cas vous dire que je trouve pour ma part ces échanges entre experts Excel très enrichissants. Ils servent aussi à aider Microsoft à mieux cadrer l’évolution de notre logiciel favori : notre avis a été demandé à plusieurs reprises sur telle ou telle évolution possible des fonctionnalités d’Excel, ainsi que sur la meilleure façon de contrer tel ou tel dysfonctionnement.

Pour terminer, je tiens à partager avec vous cette photo que j’ai prise lors d’une visite à Keukenhof – le paradis des amateurs de fleurs et de beaux parterres – le jour précédant la rencontre des MVPs :


Si vous voulez voir plein de photos sur Keukenhof :


10 avril 2017

PDF Converter Elite 5 (b)

Dans l’article précédent, nous vous avons présenté  PDF Converter Elite 5.

Nous pouvons voir ci-dessous à quoi cela ressemble quand on a chargé un PDF.

Nous avons cliqué sur l’icône d’Excel, dans la rangée du haut. La partie droite de l’écran affiche alors ce qui concerne « PDF to Excel »


Il est alors possible, dans la partir droite de l’écran, de sélectionner une zone particulière, de prendre la totalité des pages, ou un sous-ensemble de pages. La partie gauche de l’écran permet de faire défiler les pages pour alors faciliter le choix. On peut aussi masquer cette partie, ce qui permet de mieux voir le contenu du PDF.

Quand on sélectionne une zone particulière, ou l’ensemble des pages, on a alors accès à des options avancées :


Ce logiciel est bien conçu et son travail – sur Excel, puisque je ne l’ai utilisé que dans ce cadre – donne entière satisfaction.

Mon seul regret et la décoration tristounette de l’ensemble. Quand je travaille sur ce logiciel, j’ai l’impression d’être en train de rédiger un faire-part de décès…