Monsieur Excel
Pour tout savoir faire sur Excel !

28 octobre 2010

Réflexions sur la modélisation

Je vais être dans l’impossibilité d’alimenter ce blog durant 15 jours. A titre de compensation, je vous propose aujourd’hui un article sensiblement plus long que mon format habituel d’une à deux pages. Et ce seulement un jour après ma dernière contribution :)

J’ai commencé à développer des modèles pour les entreprises au début 1970, donc il y a plus de 40 ans. Diplômé de l’ENSIMAG, j’étais alors étudiant au MIT, pour un Ph.D. (doctorat) en recherche opérationnelle. J’ai créé ma première société de conseil, Flight Transportation Associates, quand j’étais encore étudiant. Toutes ces années de conseil représentent une certaine expérience :)

En plus de 40 ans, j’ai développé plus de 1.000 modèles, pour plus de 100 entreprises, dans plus de 10 pays. Au début en Fortran et en assembleur, puis sur Visicalc (le premier tableur, en 1979), Multiplan (à partir de 1982) et enfin – depuis 1985 – exclusivement sur Excel.

Ce qui m’a toujours frappé, c’est l’écart phénoménal que l’on peut constater entre des modèles créés par monsieur tout-le-monde et des modèles réellement performants : un modèle efficace est développé bien plus rapidement, se calcule sensiblement plus vite et occupe beaucoup moins de place en mémoire. Un modèle efficace représente donc une situation de win-win-win par rapport à un modèle lambda !

Temps de développement du modèle

Le tableau ci-dessous illustre quelques écarts entre les temps demandés pour un développement en interne et ceux que j’ai proposés en devis forfaitaire (et que j’ai tenus).

Notez l’excellente performance d’EdF : avec son écart représentant seulement un facteur 4, EdF est la société où j’ai trouvé le plus petit écart avec mon propre temps de développement.

Chaque fois que je me suis trouvé en concurrence avec d’autres sociétés de conseil dans le cadre d’un appel d’offres, mon devis a toujours été entre 3 et 10 fois moins cher que celui du second moins-disant (je ne parle même pas des autres).

Quand un modèle est développé plus rapidement, il est non seulement plus économique mais aussi « meilleur » ! En effet, si un modèle est développé en 3 jours, il a bien plus de chances de coller à la réalité et aux besoins que s’il avait été développé en 6 mois, temps durant lequel la situation que l’on modélise va évoluer et rendre par conséquent le modèle plus ou moins obsolète dès sa sortie.

Il m’est même arrivé une fois qu’un client – l’Institut Géographique National (IGN) en la matière – vienne me voir après que deux des plus grandes sociétés de conseil françaises aient déclaré que leur problème ne pouvait pas être modélisé. Il s’agissait d’un modèle devant proposer le matin, en fonction de la carte météo, quels avions devaient partir faire quelles missions, à quels endroits, en se ravitaillant si nécessaire dans quels aéroports, en embarquant quels appareils d’enregistrement, et ce avec quel équipage…

Pour ceux qui aimeraient savoir comment j’ai modélisé cela (ou qui voudraient tout simplement apprendre plein d’autres choses sur la vraie modélisation, celle qui fait gagner de l’argent aux entreprises), il y a un chapitre de mon livre
« Comprendre et utiliser les modèles », aux Editions d’Organisation, qui est entièrement consacré à ce modèle. Pour acquérir un exemplaire de cet ouvrage, adressez un chèque de 35 € à mon ordre, en adressant la lettre à mon intention, à Logma SA – 12 rue d’Anjou – 78000 Versailles. Si vous commandez de l’étranger ou avez besoin d’une facture, contactez-moi par mail : thiriez@hec.fr.

Temps de calcul du modèle

On observe des écarts tout aussi significatifs entre le temps de calcul d’un modèle développé chez un client – que ce soit en interne ou par un autre prestataire – et le temps de calcul après que j’aie audité et amélioré le modèle.

Ce ne sont que trois exemples, mais il m’est souvent arrivé de diviser les temps de calcul des modèles que j’auditais par un facteur allant de 50 à 100.

Dans le dernier cas (EdF), où il s’agissait de consolider une quarantaine de classeurs Excel, il faut noter aussi que l’application originale comportait 400 pages de code Visual Basic – et était donc pratiquement impossible à auditer – alors que notre solution finale se contente de 8 pages de code, soit 50 fois moins…

Quand nous avons rapproché notre consolidation de celle effectuée avec l’ancien modèle sur les comptes de juin 2009 et de décembre 2009, nous avons trouvé un seul écart (de 600 K€ quand même !). Après analyse, il s’est avéré que l’erreur provenait de l’ancien modèle…

Taille du modèle

En ce qui concerne la taille des modèles, on observe une fois encore des écarts significatifs, comme on peut le constater dans le tableau ci-dessous :

Dans le cas de sanofi, le modèle concernait le « plan clinique », c’est-à-dire l’analyse et le suivi des médicaments en cours d’expérimentation. Au moment où j’ai réalisé le modèle, il y avait un classeur Excel par médicament, et il était quasiment impossible de consolider tout cela. J’ai mis en place un système permettant de tout regrouper en un seul classeur et – par conséquent – de rendre possibles toutes sortes de consolidations.

En ce qui concerne la taille du modèle, il m’est très souvent arrivé de parvenir à des économies de l’ordre d’un facteur 5 à 10.

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 dans le premier tableau) : 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 qu’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 40 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 ci-dessus.

En conclusion, sachez donc que vos modèles existants peuvent probablement être améliorés de façon sensible, 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…

27 octobre 2010

Du texte dans une forme

On peut intégrer du texte dans n’importe quelle forme. Il suffit pour cela de faire un clic droit sur la forme puis d’utiliser la commande « Modifier le texte ».

Là où cela devient vraiment puissant, c’est quand le texte provient du contenu d’une cellule et peut donc alors dépendre des résultats obtenus dans le classeur.
C’est ce que nous avons fait ci-dessus en affichant dans la « pensée » le texte de la cellule G20. Pour cela, l’objet étant sélectionné, il suffit de taper dans la barre de formule la référence à la cellule, comme nous le voyons en haut de l’image ci-dessus.

Remarque – Quand une cellule est ainsi liée à la forme, la commande « Modifier le texte » se grise automatiquement. Elle ne se réactive que si l’on supprime la formule.

24 octobre 2010

Des poignées pour les formes

Dans l'article précédent, nous avons vu comment sélectionner une forme pour l'insérer dans une feuille de calcul.

Une particularité intéressante d’Excel concernant les formes est la présence – pour certaines formes – de poignées jaunes de modification. Nous en voyons des exemples ci-dessous pour deux formes, dont la seconde n’existait pas dans Excel 2003.
En sélectionnant la poignée jaune, nous avons, dans le premier cas, transformé le sourire en grimace et, dans le second, augmenté la partie du cercle utilisée.

Remarque – N’oublions pas non plus (nous en avions déjà parlé lors d’articles antérieurs) l’importance, lors du tracé de formes, des touches suivantes :
● la touche [Alt], pour aligner la forme sur des bords de cellule, aussi bien lors du placement de l’objet que lors de son redimensionnement ;
● la touche [Maj], pour rendre la figure symétrique : un rectangle devient alors un carré, une ellipse devient un cercle,…

Enfin, si l’on déplace un objet avec la touche [Maj] enfoncée, il se déplace automatiquement dans la même ligne ou dans la même colonne, selon la première direction de la souris.

20 octobre 2010

Utilisation des formes Excel 2007

En passant par l’onglet « Insertion », le bloc « Illustrations » et la commande
« Formes », on a accès en Excel 2007 à une grande variété de formes, comme nous pouvons le voir ci-dessous.

Ce sont pratiquement toutes des formes que l’on trouvait dans Excel 2003, via la commande « Formes automatiques » de la barre d’outils « Dessins ». Deux rares nouveautés, au premier coup d’œil, la famille « Formes d’équation » et les objets en ¾ et en partie de cercle.

Sur le plan pratique, notons la présence d’un plus et d’un moins avec la version 2007 : le plus est l’affichage de tous les objets en une seule fenêtre, le moins est la disparition de la possibilité – et cela touche de nombreuses barres d’outils en plus des barres de dessin – qu’il y avait en 2003 de sortir une barre d’outils dans la feuille en la tirant par sa bande grisée.

Cette fonctionnalité, hélas disparue avec la version 2007 d'Excel, était particulièrement utile pour effectuer du travail répétitif, par exemple tracer toute une série d’objets d’un coup ou – pour l’outil d’encadrement – effectuer en série plusieurs encadrements.

14 octobre 2010

Trois façons de « trouver Charlie »

Vous connaissez peut-être ces albums d’images pour enfant où il s'agit de
« trouver Charlie ». Un peu de la même façon, dans l’exemple ci-dessous, nous avons des codes en colonne A et, en colonne E, nous désirons obtenir le total des quantités pour les codes contenant « IBM ».

Nous voyons ci-dessus les trois formules entrées à cet effet en colonne E. Une fois de plus, nous constatons la grande variété des formules possibles dans Excel pour atteindre un même objectif… Seule la troisième formule doit être matricielle, c’est-à-dire validée avec la combinaison [Ctrl]-[Maj]-[Entrée]

Remarque 1 – On peut regretter à cet égard que les accolades des formules matricielles ne soient visibles ni lors de l’affichage global des formules, ni quand on fait un double clic dans une cellule pour y afficher sa formule.

Remarque 2 – Il est intéressant aussi de noter l’utilisation du joker « * » dans la fonction somme.si(). Le joker « ? » pourrait lui aussi être utilisé dans une telle situation.

09 octobre 2010

Protection de feuille (suite et fin)

La protection de la feuille permet d’activer ou de désactiver sélectivement la protection de l’un ou l’autre des 15 éléments présents dans la fenêtre ci-dessous. Nous avons un peu triché sur le copie d’écran pour tout vous présenter dans une fenêtre unique…
Le fait de protéger une feuille ne représente qu’un niveau assez faible de sécurité : on trouve aisément sur Internet les moyens de déprotéger une feuille de calcul, un classeur ou le code VBA d’un classeur.

Sachant cela, on peut aussi recourir à d’autres moyens pour obtenir une certaine protection. Nous avons ainsi vu, dans l’article « Une protection par validation » du 22 janvier 2007, comment un simple contrôle de validation – dont les principes ont été présentés dans les articles du 13 et du 18 janvier 2007 – peut aussi jouer un rôle de protection.

05 octobre 2010

Protection d’une feuille de calcul

Pour protéger des cellules dans une feuille de calcul Excel, il faut agir un peu à l’envers : il faut préalablement indiquer ce que l’on ne veut pas protéger, puis lancer la procédure de protection…

Nous devons d’abord sélectionner les cellules modifiables par l’utilisateur (ici, B2:E4), puis utiliser la commande « Format de cellule » du bloc « Cellules » de l’onglet « Accueil » (ou faire un clic droit), activer l’onglet « Protection », et désactiver l’option « Verrouillée », comme ci-dessous :

Il faut ensuite utiliser la commande « Protéger la feuille », onglet « Révision », pour protéger la feuille, en ayant bien soin de désactiver la toute première commande, « Sélectionner les cellules verrouillées », comme dans la copie de dialogue ci-dessous :

Excel vous demande de confirmer le mot de passe après la validation de cette fenêtre. Ensuite, la feuille est protégée et seules les cellules déverrouillées sont alors accessibles.

Remarque 1 – Je ne sais pas pourquoi le titre de mon premier dialogue est
« Liste pers. ». Cela n’a aucun rapport… Est-ce un bug dans Excel 2007 ?

Remarque 2 – Pour se déplacer d’une cellule non protégée à la suivante, il suffit d’utiliser la touche [Tab] ; vous pouvez aussi utiliser [Maj]-[Tab] pour aller à la précédente.

Remarque 3 – On peut regretter qu’il n’y ait pas de moyen simple et rapide pour sélectionner d’un coup toutes les cellules non verrouillées et voir ainsi où elles se trouvent. Pour cela, on est obligé d’en « faire le tour » via la touche [Tab]