Monsieur Excel
Pour tout savoir faire sur Excel !

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…

04 avril 2017

PDF Converter Elite 5 (a)

J’ai eu récemment l’occasion de tester un logiciel permettant de lire un document PDF et d’en sortir, au choix, une représentation en Excel,, en Word, en Powerpoint,…

Bien entendu, comme nous sommes dans ce blog des fêlés d’Excel, c’est bien avec Excel que j’ai testé le produit.

Le document PDF était en pièce jointe d’un mail. Je l’ai ouvert en demandant qu’il soit directement ouvert par PDF Converter

J’ai alors eu le même affichage qu’avec n’importe quel logiciel capable d’ouvrir un PDF. Voici une partie de ce que je voyais :


J’ai alors demandé à PDF Converter de sélectionner la page de compte et de la convertir dans Excel.

Voici le résultat obtenu :


Comme vous pouvez le constater, c’est du beau boulot…

28 mars 2017

Incorporation d’un calendrier

Parfois,dans un dialogue Excel, on a besoin d’incorporer un calendrier dans lequel l’utilisateur peut choisir une date.

Si vous voulez ne pas devoir réinventer la roue, vous pouvez utiliser le splendide travail effectué par trois brillants développeurs Excel, dont deux que nous avons déjà souvent cités, Christina Szabό et Roberto Mensa.

Vous pouvez alors lire l’article « Calendar Control for All Office versions - including Office 2016 64 bit », à l’adresse suivante : https://sites.google.com/site/e90e50/calendar-control-class.

Il est possible d’y télécharger plusieurs versions historiques, de 2011 à maintenant.

Voici ce que donne la version Calendar_Class_v1.2.0.xlsm :


Nous avons fait un double clic dans la cellule C12.  Le dialogue « Date picker » apparaît aussitôt et il suffit de faire alors un double clic sur le jour désiré pour que sa date apparaisse en C12.

23 mars 2017

Quelle est ma version d’Excel ?

Parfois, nous avons besoin de savoir quelle version d’Excel nous utilisons, surtout quand nous utilisons un ordinateur non familier.

Une solution consiste à utiliser le menu Fichier, commande Aide. Nous obtenons alors le tableau représenté dans la partie droite de la copie d’écran ci-dessous.


Une autre solution revient à utiliser la macro ci-dessous, que nous avons associé (cf. copie d’écran) au bouton macro placé dans le coin supérieur gauche qui engendre le dialogue affiché en dessous et à droite du bouton :

Voici la macro en question :

Sub MyVersion()
    MsgBox "Version : " & Application.Version & vbLf & "Build : " & Application.Build
End Sub

Remarque – On peut d’ailleurs se demander pourquoi le « build » ne correspond pas exactement à ce qui est indiqué par la commande « Aide » du menu « Fichier ».

14 mars 2017

D’autres articles sur equiv()

La fonction equiv() est une véritable mine d’or et peut être utilisée pour résoudre un grand nombre de problèmes concrets. Voici quelques articles du blog qui vous permettront d’en savoir plus sur les possibilités offertes par cette fonction géniale.

Liste dynamique sans répétition
Le 16 juin 2013.

Dans cet exemple, la fonction equiv() sert à constituer de façon dynamique  une liste sans répétition de toutes les valeurs différentes observées dans un vecteur.

Un equiv() sur une matrice (a)

La fonction equiv() ne peut utiliser qu’un vecteur comme premier argument. Comment faut-il procéder quand on souhaite trouver une valeur dans une matrice au lieu d’un vecteur ?

La série d’articles suivante répond à cette question délicate. Depuis le 27 janvier 2014, avec Un élément dans une matrice jusqu’au 16 février 2014 avec Un equiv() sur une matrice (d) .

Recherche sur >255 caractères
Le 28 février 2015.

Récupérer une valeur d'un TCD
Le 15 novembre 2005.

Calcul du nombre de modalités
Le 6 octobre 2016.

Nous utilisons là la fonction equiv(), en association avec la fonction frequence(), pour calculer – sans formule matricielle ! – le nombre de modalités (de valeurs différentes) dans un vecteur.

Une utilisation originale d’equiv()

Comme je l’ai indiqué dans le premier article du 27 février, le premier de cette petite série, Excel suppose – quand on utilise les troisièmes arguments +1 et -1, que les valeurs sont respectivement en ordre croissant et décroissant.  Mais ce n’est pas une obligation !

Ainsi, =equiv(10^6;D:D;1) trouve la position de la dernière valeur entrée en colonne D, sous réserve que toutes les valeurs soient inférieures à 10^6. Sinon, il suffit d’augmenter ce nombre…

De même, =equiv(-10^6;D:D;-1) trouve la position de la dernière valeur entrée en colonne D, sous réserve que toutes les valeurs soient supérieures à -10^6.