Monsieur Excel
Pour tout savoir faire sur Excel !

20 avril 2018

Du cinéma avec Excel 2016

Dans le dernier article, nous avons soulevé un problème : la macro « Movie » ne fonctionne que pour les versions d’Excel antérieures à 2016.

Remarque – En fait, il manquait dans la rédaction originale de l’article précédent une commande essentielle pour que le film fonctionne, même avec les versions d’Excel antérieures à 20126, la commande Application.ScreenUpdating=True. Cela a été corrigé a posteriori dans cet article.

Mon collègue MVP Jon Peltier a trouvé une solution pour ce type de situation :  il faut utiliser la commande DoEvents qui, si vous misez sur Internet les articles la concernant, n’est citée nulle part comme ayant cet effet bénéfique dans l’animation cinématographique…

Le piège, c’est que cette commande seule ne suffit pas : il faut exécuter au moins deux fois la commande pour que le cinéma fonctionne. Si un lecteur trouve une explication cohérente à ce phénomène, je suis preneur de son explication…

Voici donc la macro définitive, que nous associons au bouton Cinéma :

Sub Movie()
For i = 1 To 400
    Range("C2") = i
    For j = 1 To 50
        x = x + 12 ^ 2
    Next j
    DoEvents : DoEvents
Next i
End Sub

Exemples de films en Excel

J’ai réalisé de nombreux modèles Excel dans lequel je faisais du cinéma. En voici trois exemples pris dans les 20 dernières années, dans l’ordre chronologique…

1. Simulation des tapis roulants portant les bagages pour Aéroports de Paris. A titre indicatif, il s’agissait alors d’un appel d’offres lancé par AdP, que j’ai gagné et qui m’a valu ensuite des commandes de modèles de la part d’AdP durant plus de 10 ans.  Pour info, mon devis était de 300 KF (c’était la dernière année avant les euros) et le second concurrent le moins cher demandait 1,5 MF, soit cinq fois plus… J’étais le seul à proposer une solution avec Excel…

2. Simulation du fonctionnement d’une gare de péage en fonction des portes ouvertes et du trafic attendu. Ce modèle a été vendu à plusieurs sociétés de péage. Le film montrait dans le temps, pour les portes de péage ouvertes, le nombre de véhicules en attente et le débit en une minute.

3. Simulation du trafic routier dans une zone limitée. A titre indicatif, ce modèle de 50 K€ a été amorti par le client en moins de 6 mois.

4. Simulation du déplacement des piétons dans une aérogare.

Voici ce que donne l'exemple n°3 :




14 avril 2018

Faites votre cinéma avec Excel

Le modèle que nous avons mis au point dans le dernier article permet de modifier facilement le point de départ d’une série graphique.

Pour montrer cette évolution en continu, en un mot pour faire du cinéma avec Excel, il suffit d’écrire une macro qui modifiera la valeur de C1 entre ses limites actuelles 0 et 350.

Pour commencer, nous modifions la définition des cellules C1 et C2. En C1, nous entrons la formule =index(A:A;C2), et nous entrons la valeur 2 en C2.

Nous créons la macro suivante, que nous associons au bouton Cinéma en bas et à gauche du graphe.

Sub Movie()
For i = 1 To 400
    Range("C2") = i
    For j = 1 To 50
        x = x + 12 ^ 2
    Next j
    Application.ScreenUpdating=True
Next i
End Sub

Dès que l’on clique fans le bouton, on observe le film attendu, à une condition : il faut pour cela avoir utilisé une version d’Excel antérieure à Excel 2016.


La boucle For..Next sert à ralentir le film qui, autrement, tourne trop vite. S’il tourne trop vite pour vous ou trop lentement, modifiez le 50.

Si vous utilisez Excel 2016, voyez si vous trouvez le moyen de faire ce qu’il faut pour obtenir le film désiré. Je vous donnerai la réponse dans mon prochain article mais je vous préviens : ce n’est ni naturel ni simple à trouver…

07 avril 2018

Zoom sur une partie d’un graphe

On dispose parfois de données numériques sur de longues périodes et l’on souhaite afficher un graphe à partir d’un certain point dans cette chronique.

Cela est utile par exemple pour la comparaison de cours d’actions quand on veut comparer deux séries, par exemple le CAC 40 et le cours d’une action, en choisissant le point de départ à une date donnée.

Dans l’exemple ci-dessous, nous avons entré des dates consécutives en colonne A. En B1, histoire d’avoir une jolie courbe, nous avons entré la formule =sin(pi()*(ligne()-1)/22,5)*(1-cos(pi()*(ligne()-1)/17)) que nous avons tirée vers le bas jusqu’en A500 (mars 2018).

En C1, nous avons entré une validation par liste alimentée par A1:A500.

En C2, avec la formule =equiv(C1;A:A;0), nous calculons le numéro de la ligne où se trouve la date sélectionnée en C1.

En D1, nous avons mis la formule =index(B:B;$C$2+LIGNE()-1), reproduite ensuite jusqu’en D100. Le graphe représente les valeurs D1:D100.

En F1, cellule cachée par le graphe, nous avons entré la formule ="Début en "&texte(C1;"mmmm aaaa"). Pour le titre du graphe, nous avons entré la formule =F1.


Remarque – On n’a pas le droit d’entrer la formule de F1 directement dans le titre du graphe, mais on a le droit de faire ainsi référence à une cellule contenant la formule du titre. Cela permet, comme nous le faisons ici, d’avoir un titre dynamique.


31 mars 2018

Une assistance en ligne

Nous avons vu dans les derniers articles qu’il était possible d’améliorer de façon très significative des modèles Excel, et ce de multiples façons : en les développant bien plus vite, en réduisant leur taille – parfois même de façon drastique –, en réduisant sensiblement le nombre des onglets, en rendant ces modèles bien plus flexibles, et enfin en les rendant plus facilement auditables.

Notons que nombre de ces améliorations vous amènent dans des situations de « win-win » (gagnant/gagnant en français). Ainsi, quand un développeur crée un tableau de 5 colonnes  avec une seule formule au lieu de 5, il va 5 fois plus vite mais aussi 5 fois plus sûrement : 1 formule représente 1 risque d’erreur alors que 5 formules en représentent 5 !

Si vous utilisez Excel dans votre entreprise, et que vous avez un modèle trop gros, trop lourd, trop lent, pas assez flexible ou difficilement auditable, je vous propose donc de me le mailer pour que je vous aide à l’améliorer. 

Vous pouvez aussi me l'envoyer si vous souhaitez que je le rende bien plus flexible, à condition de bien expliquer comment vous souhaitez atteindre cet objectif...

Mail : thiriez@hec.fr

Je suis tellement convaincu que je parviendrai à améliorer votre modèle que je vous propose de l’analyser gratuitement. Je vous indiquerai alors le genre d’amélioration que je parviens à lui apporter et je ne vous facturerai que si cela vous convient. Vous n’aurez donc rien à perdre.

Confidentialité

Vous n’avez aucun souci à vous faire en matière de confidentialité. J’ai développé des modèles Excel pour plus de 100 entreprises dans plus de 10 pays. J’ai donc l’habitude de signer des NDA (Non Disclosure Agreements). Je réalise des modèles professionnels depuis 1969 et n’ai jamais eu le moindre contentieux, pas plus que ma société de conseil Logma, créée en 1980, qui fait un chiffre d’affaires supérieur à 1 M€ chaque année.

Références

Quelques-unes de mes références auprès de grandes entreprises : Aéroports de Paris, Arianespace, Artemis, Aventis, Avis, Banques Populaires, Bouygues, Caisse des dépôts, CCF, Cegelec, CNES, Dassault Systèmes, EdF, EADS, Elf, Ernst & Young, Eulia, Finacor, France Telecom, Johnson & Johnson, MBDA, NASA, Quaker Oats, Sacilor, Sanofi, SKF et Total.

Remarque - J'ai été consultant pour au moins 10 de ces entreprises durant plus de 10 ans chacune.

24 mars 2018

Auditabilité – Un exemple

Dans le dernier article, j’ai abordé le thème de l’auditabilité d’un modèle. C’est un critère important et qu’il ne fait en aucun cas sous-estimer. Plus d’une fois, j’ai été appelé au secours – comme consultant – par des clients qui avaient perdu tout contrôle sur un modèle important.

Une fois, avec la Caisse des Dépôts, je me suis retrouvé face à un modèle qui possédait des liens avec plusieurs fichiers Excel dont personne n’avait plus la trace et dont un certain nombre de cellules aboutissaient à des erreurs non traitées par le modèle. Le modèle était devenu totalement inexploitable.

Comment en était-on arrivé là ? Il s’agissait en fait d’un ensemble de projets routiers et autoroutiers financés ensemble par la Caisse des Dépôts, une banque allemande et une banque italienne. Suite aux manipulations sauvages dont il avait été l’objet de la part de cadres mal coordonnés de ces trois pays, le modèle était devenu totalement incontrôlable.

La mission que m’a donnée le client était double. En premier lieu, je devais entièrement nettoyer le modèle et le rendre de nouveau opérationnel. De plus, je devais garantir que toutes les formules du modèles respectaient bien le dossier contractuel de plus de 15 pages expliquant comment les trois banques se répartiraient les bénéfices ou pertes des opérations.

Dans d’autres entreprises, j’ai été parfois appelé à la rescousse parce qu’un modèle fonctionnait encore mais que son créateur avait quitté la société. Personne ne maîtrisant plus le modèle, ma tâche était de tout comprendre, puis de documenter et d’expliquer le modèle aux cadres de l’entreprise.

Le plus farfelu que j’aie eu à faire en matière d’audit de modèle – en 1970, donc bien avant Excel – était, pour une société de conseil américaine, de comprendre et documenter un logiciel d’optimisation des équipages dans les compagnies aériennes. L’auteur de ce programme avait été renvoyé par la société de conseil pour laquelle il l’avait réalisé en Fortran. Il s’est vengé en partant avec toute la documentation et en ne leur laissant que le code brut, des dizaines de pages de listing sans un seul commentaire.

Il réclamait 100.000 $ de compensation pour fournir la documentation. Comme je venais de faire une thèse de doctorat sur ce sujet et que j’avais moi-même réalisé un programme appliquant ma propre méthode, la société de conseil m’a fait appel pour débloquer cette situation. Ce qui a été fait en une semaine à raison de 12 heures de travail par jour, pour bien moins cher que la somme demandée…

Un exemple

Le petit exemple ci-dessous illustre bien le trade-off que l’on doit faire quelquefois entre l’auditabilité des formules et leur rapidité de calcul ou leur complexité.

Il s’agit d’afficher une étoile quand il manque 1 à 5 unités par rapport au stock minimum, 2 étoiles quand il en manque de 6 à 10, 3 étoiles de 11 à 15, et ainsi de suite.


La formule de C2 est la plus naturelle et la plupart des gens la comprennent sans difficulté.

La formule de D2 est plus compacte. Je constate souvent chez mes clients des cas de ce genre où l’on simplifie une formule en ôtant un si().

La formule de E2 est la plus courte et la plus performante, mais il y a de nombreux utilisateurs qui ne vont pas comprendre ce que le « +4 » fait dans la formule…

En évoluant de C2 à E2, on gagne progressivement en compacité et en efficacité de la formule, mais en perdant au niveau de l’auditabilité. C’est un dilemme fréquent en matière de modélisation.

Remarque – Sans connaître la fonction rept(), on aurait dû faire plus lourd encore avec une table ayant en colonne 1 des valeurs allant de 5 en 5 et en colonne 2 de 1 à n étoiles selon la ligne, le tout assaisonné d’un simple recherche() – dans le meilleur des cas – ou, pire encore, d’un index(equiv…)).


17 mars 2018

La modélisation - Bilan

Les articles précédents

Le premier article de notre dernière série, le 28 février, parlait du temps de développement des modèles Excel. Nous y avons appris qu’il y avait fréquemment des écarts sensibles entre les temps de développement requis par tel ou tel prestataire.

Dans le second article, le 5 mars, nous avons traité du temps de calcul et de la taille des modèles Excel. Là encore, nous avons souligné le fait qu’il était souvent possible d’obtenir – avec l’aide d’un consultant Excel bien rôdé à ce genre de choses – des économies sensibles aussi bien pour la taille des modèles que pour leur temps de calcul.

Le troisième article a été pour nous l’occasion de faire un certain nombre de remarques générales relatives à la modélisation, en s’intéressant bien évidemment en particulier à la modélisation sous Excel.

L’auditabilité des modèles

Il y a encore un thème qui joue un rôle important dans la modélisation, un rôle au moins aussi important que celui des autres thèmes, et que je n’ai pas abordé dans cette série d’articles : l’auditabilité des modèles. En effet, quand je crée ou quand j’améliore des modèles pour une entreprise, j’ai le souci que mon client puisse utiliser ce modèle au mieux sans dépendre de moi, qu’il puisse continuer à faire vivre tous les modèles que j’ai créés ou améliorés pour lui.

Le seul revers de cette médaille – pour un consultant – est que, si je lui permets à mon client de bien s’approprier son modèle, il n’a plus forcément besoin de moi pour l’améliorer. J’en ai une une illustration il y a deux ans avec un modèle que j’avais construit pour Sanofi il y a près de 15 ans. J’ai été recontacté par eux car ils voulaient le compléter par de nouvelles fonctionnalités et j’ai ainsi appris que, durant plus de 10 ans, ils avaient utilisé ce modèle et apporté des modifications sans avoir – jusqu’à ce jour – été obligés de faire appel à mes services !

Heureusement pour moi, durant toutes ces années, j’ai quand même développé d’autres modèles pour Sanofi, mais dans d’autres services.

11 mars 2018

Modélisation – Remarques finales

Les outils décisionnels

De nombreux traitements, de nombreuses fonctionnalités, que l’on effectue aujourd’hui de façon coûteuse (il faut une licence par poste) avec des outils décisionnels peuvent parfaitement bien être réalisés avec Excel.

Nombreux sont mes clients qui, après être passés à un décisionnel, reviennent vers moi un ou deux ans plus tard pour me redemander des outils performants et personnalisés…

Une des raisons pour lesquels ces outils se vendent si bien malgré tout est qu’ils reçoivent l’adhésion totale de votre informatique maison (dont nous avons pu apprécier le niveau de performance potentiel dans le premier tableau du premier article de cette série) : quand vous utilisez ces outils, vos informaticiens sont utiles et donc valorisés ; quand vous utilisez Excel, vous parvenez à vous passer de leurs services, et ils n’aiment pas cela du tout. Pas étonnant donc s’ils déclarent qu’Excel, « C’est du bricolage »…

En guise de conclusion…

On peut se demander comment il peut y avoir de tels écarts entre une modélisation classique et une modélisation performante. Il y a en fait plusieurs explications…

Tout d’abord, quand une société de conseil réalise un modèle, plusieurs personnes interviennent : le consultant qui analyse le problème et propose un devis, le chef de projet qui va coordonner la réalisation du produit, les développeurs qui vont programmer et enfin le formateur qui va assurer l’installation du modèle (parfois, c’est même quelqu’un d’autre qui fait cela !) et la formation. La coordination et le contrôle de tout ce joli monde représente du temps et pompe de l’énergie. Quand je développe un modèle, j’assure tout cela de A à Z, donc sans la moindre déperdition.

Une autre raison est que, dans toutes les écoles d’informatique, on enseigne la programmation structurée, en « top-down ». L’avantage de cette approche est qu’elle est organisée et systématique, et réduit donc le risque d’erreur.

Depuis plus de 45 ans, je pratique la programmation en « bottom-up », dans laquelle on développe d’abord un premier noyau, autour duquel on ajoute ensuite diverses fonctionnalités, puis d’autres encore, puis d’autres encore… et ce jusqu’à la fin du projet.

La raison pour laquelle cette approche n’est pas conseillée d’habitude est que, si le développeur n’a pas dès le début une vue d’ensemble de ce qu’il réalise et des possibles extensions futures, on aboutit rapidement à une « usine à gaz ».

En revanche, quand cela est bien fait, cela permet de développer bien plus vite qu’avec une approche « top-down ». De plus, contrairement au « top-down », on dispose à tout moment d’un modèle qui fonctionne, sur lequel on peut faire réagir le client, et qui permet donc d’améliorer le modèle au cours de son développement, ce qui est beaucoup plus difficile, sinon impossible, avec une approche « top-down ».

C’est grâce à cette approche « bottom-up » – que j’ai toujours pratiquée – et au fait que je réalise tout moi-même de A à Z, que j’ai pu aboutir aux écarts décrits dans cette série d’articles.

C’est assez amusant de constater que les méthodes « agiles », créées en 2001 et à la mode depuis quelques années, reposent en bonne partie sur ces principes qui m’ont toujours guidé.

En conclusion, sachez donc que vos modèles Excel existants peuvent probablement être améliorés de façon notable, et que vos futurs modèles pourraient être bien plus efficaces et bien moins chers, à condition toutefois de vous adresser pour les concevoir à des prestataires bien choisis…