Comment utiliser Excel comme un pro avec 7 principes simples ?

Comment faire un fichier Excel qui fonctionne efficacement sur la durée ? Pas comme je l’ai fait au début en tout cas !

Lorsque je faisais un stage en amélioration continue chez L’Oréal, en grande partie du fait de mon inexpérience, je me suis lancé dans la création excessivement laborieuse d’un fichier Excel pour aider ma manageuse à suivre des références de produit. Finalement, cela n’a abouti à … rien du tout. Par là j’entends que le fichier était tellement compliqué et fouillis qu’il était inexploitable : un mois de travail plus ou moins avec un résultat plus décevant.

Pourquoi ça ? J’avais créé une usine à gaz :

• des fonctions trop nombreuses et sans réelle utilité,
• un codage pas toujours efficience car ne prenant pas en compte l’optimisation d’Excel,
• une personnalisation possible du fichier minime,
• pas de « charte » claire pour comprendre le fonctionnement du fichier aisément,
• etc.

Rajoutez à cela le fait que des bugs dans le programme devenaient de plus en plus nombreux et que le temps de traitement des données prenait un temps EXTRÊMEMENT long.

Bref, ce fut un échec.

Le positif est toutefois que j’ai appris de cet échec, et que celui-ci a influencé ma méthodologie de de construction et de développement sur Excel !

Je vais donc vous présenter ma logique de développement que j’ai appliqué sur les fichiers élaborés par la suite et qui, eux, ont réellement apporté de la valeur.

Pour rendre cela plus ludique, cet article prendra la forme d’une série de 7 principes à suivre.

Mais tout d’abord, un point de vocabulaire s’impose afin de comprendre de quoi il retourne.

Une notion importante : introduction rapide aux « macros »

Une « macro » est une procédure ou une fonction codée dans un langage qui se nomme le VBA (Visual Basic for Applications) et qui s’exécute manuellement ou automatiquement dans Excel.

Rien qu’avec cette phrase, vous êtes peut être en train de vous dire : « Whaaaaaaaat ? J’ai rien compris ! »

Plus simplement, cela permet d’automatiser des tâches sur Excel pour vous faciliter la vie sur le long terme.

Pour que ce soit plus simple à comprendre, on peut comparer cela aux fameuses « formules Excel ». Voici un tableau récapitulant les différences principales entre les deux :

En lien avec ces deux éléments vont se trouver les « fonctions ». Vous avez les fonctions par défaut de Excel que vous connaissez déjà sans doute : SI, SOMME, DIVISION, ESTNUM, etc ; et vous avec les fonctions personnalisées que vous pourrez créer. Et pour terminer, je vous retourne le cerveau en vous disant qu’en faite, une formule … c’est une macro !

Bon j’arrête de vous prendre la tête avec ça ! Si vous ne saviez pas ce qu’il se cacher sous le terme « macro », vous avez compris l’essentiel.

1er principe : Des tests tu réaliseras et du feedback tu demanderas

Cette partie-là est primordiale et doit être particulièrement bien soignée. L’idée est de proposer des versions préliminaires du fichier sur lesquelles vous allez faire des incrémentations.
Vous allez faire une liste des besoins que doit remplir votre fichier Excel et C’est finalement l’idée de la méthode Scrum, sans forcément devoir l’appliquer rigoureusement, de proposer des versions fonctionnelles mais ne remplissant qu’une partie du besoin. De là je recueillais les feedbacks qui permettaient d’orienter et d’adapter le fichier pour coller au mieux aux besoins qui pouvaient potentiellement évoluer par rapport à ceux initialement énoncés.
Le mieux est de faire tester directement le fichier par la personne qui devra l’utiliser ou au moins de se mettre à sa place et d’effectuer TOUTES les étapes nécessaires au fonctionnement du fichier afin de pouvoir en faire une analyse cohérente.

2ème principe : Une « maquette » du fichier tu créeras

Dans mon premier fichier, j’ai eu la mauvaise idée de me hâter à créer des fonctions, des variables, etc avant d’avoir élaboré une maquette complète de ce qui m’était demandé.

Qu’est-ce que j’entends par une « maquette » ? C’est une création du fichier avec tous les onglets, toutes les colonnes, tous les intitulés qui seront sensés s’y trouver avec un bref descriptif de ce qu’on devra trouver dedans et une poignée de lignes pour avoir un exemple. Cela permet d’être sûr d’arriver bien au résultat escompté qui va souvent être sous cette forme :

1. Base de données :
   1. Une colonne identifiant relative aux données sur lesquelles on souhaite avoir une information (ex : des codes de produit, le nom/prénom,etc).
   2. Des colonnes informatives venant préciser, catégoriser et expliciter la donnée (ex : pays, marque, taille, âge, etc). Elles servent notamment de filtres pour cibler un certain type de donnée (ex : je veux regarder uniquement une marque de produit). On se servira souvent de liste de choix prédéfinies pour ces colonnes.
2. Traitement de données :
   1. Des colonnes de comparaison et d’analyse. Ces colonnes vont venir confronter les informations concernant le produit pour en retirer un message clair et littéral. C’est réellement la
   2. Une colonne bilan qui synthétise le contenu des autres colonnes. Pour cela, on pourra faire des colonnes de sous-bilan qui seront elles-mêmes comparées entre elles pour aboutir à la colonne bilan.
3. Tableau de bord :
   1. Un ou plusieurs Tableaux Croisé Dynamiques (ou TCD pour les intimes). Ce sont en gros des « vues » différentes de votre base de données. Vous pouvez décider des lignes, colonnes, valeurs ou filtres à considérer sur la base d’une plage de données choisie. Si celle-ci change, on pourra actualiser le TCD avec les nouvelles valeurs. L’avantage de cet outil est qu’il offre beaucoup plus de liberté pour la visualisation de données tout en permettant d’éviter de ne pas corrompre les données initiales par mégarde (suppression d’une colonne, mauvaise référence, etc)
   2. Des graphiques croisés dynamiques pour afficher visuellement les données contenues dans le TCD.

Dans cet exemple, voici par exemple les 3 types de colonnes

3ème principe : Un onglet de personnalisation et des variables tu auras

Un onglet de personnalisation pour l’élaboration d’un fichier complexe (ou simple d’ailleurs) aide énormément aussi bien le « codeur » ou créateur du fichier que l’utilisateur.

Pourquoi ça ? Imaginons que vous vouliez changer le message afficher par une colonne, ou bien déterminer quels sont les produits à traiter selon tel ou tel critère, ou encore capter des données d’un autre fichier où le nom et l’emplacement des colonnes aura pu changer entre temps. Dans ces cas-là, si vous avez codé les données « en dur », vous devrez changer jusqu’à la dernière ligne de code et les formules contenant ce qui est à modifier.

A la place, vous allez prévoir une cellule dans votre onglet de personnalisation pouvant s’actualiser manuellement et dans laquelle vous allez venir changer une valeur au besoin afin d’adapter l’ensemble des formules ou des macros de votre fichier. Cela va constituer votre variable qui, comme son nom l’indique, pourra varier.

Cela va commencer à devenir compliquer de changer toutes les données relatives à ces informations, sans compter le nombre d’erreurs potentielles et bon courage pour retrouver ça dans plusieurs dizaines (voir centaines) de lignes de code. En incluant l’utilisation d’un onglet de personnalisation, on évite cela :

Plus intuitif non ? Mais aussi utile que peut être cette manière de raisonner et de structurer un fichier Excel, elle a quand même quelques défauts.

Le premier est la lisibilité. Dans les macros, il n’y a pas trop de problèmes étant donné qu’il suffira de définir des variables pour rendre le tout plus digeste comme ceci :

Dans les formules néanmoins, ça l’est un peu moins :

Le deuxième est tout simplement le temps. Utiliser cette manière de construire le fichier nécessite un minimum de réflexion, de rigueur et de temps pour être bien exploitée.

4ème principe : Des indications visuelles tu définiras

Pour les fichiers de suivi élaborés, on peut au bout d’un certain temps oublier quelles colonnes sont à remplier, lesquelles se remplissement automatiquement avec un formule ou une macro, lesquelles sont remplies par une personne spécifique, lesquelles contiennent une indication, etc.

Les indications visuels sont importantes pour avoir un fichier clair dont l’utilisation sera fluide.

Pour cela, la première chose est de définir une charte graphique. Plusieurs choix s’offrent à vous : il n’y a pas qu’une seule charte graphique qui peut convenir ! Elle devra toutefois rester la même et cohérente au sein du même fichier.

Par exemple :

La mise en forme conditionnelle est également très importante. Elle traduit le fait qu’une cellule ou une plage de cellules adoptent un format particulier lorsque la condition voulue est remplie.

S’il est possible d’utiliser la construction de condition par défaut comme « valeurs en double » ou « Valeur égale à … », on pourra également utiliser une condition personnalisée s’écrivant de la même manière qu’une formule Excel (et d’ailleurs, je vous conseille de l’écrire avant dans votre fichier pour la tester !). Dans ce dernier cas, la donnée de sortie doit être « VRAI » ou « FAUX » avec une fonction SI().

Cela permet de repérer directement les lignes où les données donnent des résultats correctes ou incorrectes, attendus ou à traiter, etc :

5ème principe : Les utilisateurs tu considèreras

Rien ne sert de faire un fichier avancé avec une multitude de fonctionnalités si la personne censée utiliser le fichier n’a pas les compétences techniques pour les utiliser. On adaptera donc les manipulations à effectuer au degré de connaissance de l’utilisateur : du néophyte qui découvre le logiciel au pro qui l’utilise quotidiennement !

Comment ? On pourra choisir de :

• Faire un tutoriel vidéo afin de montrer l’ensemble de la procédure à suivre pour utiliser le fichier. Vous pourrez utiliser un logiciel gratuit comme Loom par exemple. Tout comme découvrir un remède contre le cancer mais que sans que personne ne le sache constitue une importante somme d’efforts pour rien, la clé d’un fichier qui dure dans le temps est la création d’un tutorial ludique et adapté.
• Mettre des commentaires ou des notes dans les entêtes des colonnes ou dans certaines cellules pour expliquer l’utilité et le fonctionnement de celles-ci. Cela rend l’expérience plus fluide grâce au JITL, le Just In Time Learning. En gros, vous apprenez à quoi sert une cellule en même temps que vous devez l’utiliser.
• En VBA, on peut faire s’afficher un pop up explicative lorsque qu’on appuie sur un bouton ou modifie le contenu d’une cellule par exemple. Cela équivaut à écrire cela dans le code d’une feuille ou d’un fichier (Onglet « Développeur » > Visual Basic) :

Sub PopUp()

MsgBox « je suis une PopUp ! Pense à enregistrer « 

End Sub

Ce qui donnera :

A vous d’adapter le message bien entendu ! Vous pouvez également afficher des données variables du fichier en plus d’un texte figé.

6ème principe : Des sauvegardes régulières tu feras

Particulièrement lorsque l’on programme mais également lorsque l’on met à jour ou utilise un fichier, il est important de le sauvegarder (avec un format type « Nomdufichier_Date ») régulièrement pour pouvoir retrouver la dernière version au besoin.

Cela peut prévenir de nombreux problèmes, des colonnes supprimées par mégarde aux bugs qui font planter le fichier dès l’ouverture.

Il est possible de rendre cette action automatique en y ajoutant une macro. Lorsque le fichier est en phase d’être fermé ou sauvegardé et que celui-ci ne l’a pas été à la fréquence choisie, une fenêtre comme celle ci-dessous s’affiche demandant si l’utilisateur veut enregistrer le fichier de cette manière.

7ème principe : Sur-charger le fichier tu ne feras point

Les fonctions sont utiles en termes de lisibilité et de facilité de programmation. Il faut toutefois veiller à ne pas multiplier les petites fonctions au risque d’avoir de grosses difficultés à retrouver une erreur en devant faire des va-et-vient entre elles.

Et en même temps, avoir une seule grosse fonction qui fait toutes les actions voulues dans le fichier et le meilleur moyen de s’y perdre et de ne plus comprendre les liens entre les variables, calculs, attributions de valeurs, etc.

Essayer autant que possible de garder votre fichier au plus simple ! Si quelqu’un d’autre que vous essaye d’utiliser et ne comprends rien ou difficilement avec quelques explications, c’est votre signal d’alarme !

▶️ Conclusion

Nous avons vu ensemble les principes fondamentaux à respecter pour devenir un vrai expert d’Excel :

1. Des tests tu réaliseras et du feedback tu demanderas
2. Une « maquette » du fichier tu créeras
3. Un onglet de personnalisation et des variables tu auras
4. Des indications visuelles tu définiras
5. Les utilisateurs tu considèreras
6. Des sauvegardes régulières tu feras
7. Sur-charger le fichier tu ne feras point

Choisissez-en 1 ou 2 et focalisez-vous dessus sur une période choisie. Ensuite, passez à un autre et ainsi de suite !

Si cet article vous a été utile ou que vous avez d’autres principes en tête, dites-le moi en commentaire 😉