Dans cet article, je vais vous partager ma méthode de travail pour créer un outil de travail efficace et évolutif sur Excel. Je tiens à préciser que c’est surtout destiné à celles ou ceux qui n’ont pas les moyens d’utiliser un logiciel métier ou qui doivent saisir leurs données directement sur Excel. Néanmoins, une partie de cette méthode est quand même utile pour celles ou ceux qui gèrent des extractions de données.
Pour information, j’ai appris cette façon de travailler durant mon BTS, il y a une vingtaine d’années. Puis dans la société d’édition de logiciels où je travaillais, elle était appliquée. Et en fait, vous verrez que c’est simplement du bon sens. Bon sens qu’on a trop souvent oublié car on nous impose des limites de temps, des résultats immédiats… bref !
Nous allons partir de la problématique suivante :
Gérer le nombre de boîtes de chocolats commandées à l'année et par secteur.
1ère étape : que fait-on ?
Vous avez lu la problématique et votre réflexe premier est d’ouvrir Excel. Vous pensez qu’il est facile de créer le bon tableau et il n’y a pas besoin de réfléchir pour résoudre le problème.
Tout d’abord, vous n’ouvrez pas Excel mais Word. Oui, vous avez bien lu ! Vous allez d’abord rédiger le contexte actuel de l’entreprise, du service, de comment ça se passe… Ainsi vous avez une trace écrite de ce que vous aviez comme information à la création de l’outil Excel. N’oubliez pas que votre fichier va exister plusieurs années et passer entre les mains de plusieurs utilisateurs. En posant par écrit ce qui existe, cela vous permet déjà de réfléchir aux évolutions du fichier dans un avenir à moyen terme.
Exemple par rapport à la problématique des boîtes de chocolat :
Actuellement l’entreprise a 3 secteurs commerciaux : NORD, EST, OUEST. Mais elle souhaite développer géographiquement son activité.
De plus, l’entreprise ne vend que 3 types de chocolat : LAIT, BLANC, NOIR. Cependant, d’autres types de chocolat vont être mis en vente dans les semaines et mois à venir.
2ème étape : et ensuite ?
Vous vous dites que là c’est bon, j’ouvre Excel ! Toujours pas !
Vous restez sur Word ou alors vous prenez un brouillon puis vous réfléchissez aux objectifs de votre problématique. En effet, il est important de rédiger pourquoi vous faites ce fichier. Quelles sont les analyses que vous voulez en faire. Car, oui, aujourd’hui, en presque 2025, tous les tableaux de données sur Excel sont étudiés. Il n’y a pas de prise de décision dans une entreprise sans une observation des faits.
La deuxième étape consiste donc à rédiger les objectifs, les analyses à faire sur le fichier Excel.
Exemple par rapport à la problématique des boîtes de chocolat :
- connaître la quantité totale de boîte vendues par secteur
- connaître la quantité totale de boîte vendues par type de chocolat
- connaître la quantité totale de boîtes vendues par année et mois
- connaître la répartition en % des secteurs
- connaître la répartition en % des types de chocolat
- connaître l'évolution en % d'une année sur une autre à la même période
- connaître le résultat cumulé par mois
Petite pause dans mon article !
Vous avez l’énoncé du problème à résoudre, le contexte de l’entreprise et les objectifs à atteindre. Essayez par vous-même de créer sur une feuille de brouillon, le tableau de données pour répondre aux besoins exprimés.
Est-ce que votre tableau ressemble à celui-là ?
Ou ressemble-t-il à celui-là ?
Vous aurez la bonne réponse dans la suite de cet article.
3ème étape : Excel arrive enfin !
Par rapport à votre rédaction de problématique et particulièrement les objectifs, il faut recenser les données servant aux calculs. Pour ma part, j’aime bien les mettre en surbrillance ou en couleur pour identifier rapidement les futures entêtes de colonnes de mon tableau.
Reprenons quelques objectifs par rapport à la problématique des boîtes de chocolat :
- connaître la quantité totale de boîte vendues par secteur
- connaître la quantité totale de boîte vendues par type de chocolat
- connaître la quantité totale de boîtes vendues par année et mois
Il y a donc 4 données : quantité, secteur, type de chocolat et date. En effet, dès que vous croisez la notion de mois, d’année, de trimestre, de jour, de numéro de semaine… il est nécessaire d’avoir une colonne date dans votre tableau listant les données.
Vous pouvez donc maintenant ouvrir EXCEL (enfin !) et saisir les entêtes de colonnes que vous avez trouvé. La bonne réponse était donc le tableau numéro 2.
4ème étape : définir un cadre de saisie !
Comme dans tout cahier des charges, on rédige les consignes à suivre. Il ne vous viendrait pas à l’idée de construire une maison sans plan ou encore d’usiner une pièce sans les contraintes à respecter. Il en va de même pour la saisie de vos données.
Reprenez donc votre document Word et écrivez pour chaque donnée, le cadre de saisie.
Exemple par rapport à la problématique des boîtes de chocolat :
Date : supérieure ou égale à la date du jour qui doit changer avec la fonction AUJOURDHUI()
Secteur : liste déroulante évolutive à prédéfinir en onglet Nomenclature (NORD, EST, OUEST)
Type chocolat : liste déroulante évolutive à prédéfinir en onglet Nomenclature (LAIT, NOIR, BLANC)
Quantité de boîte : nombre entier compris entre 1 et 25 avec possibilité de modifier si besoin
Puis retournez sur Excel et créez votre cadre de saisie avec la validation des données (dans l’onglet Données). Si vous souhaitez avoir plus d’informations sur cette partie, je vous conseille de lire les autres articles de blog notamment celui-là Les Validation des données : L'outil essentiel pour éviter les erreurs sur Excel.
5ème étape : définir les formules de calcul si besoin !
Contrairement aux idées reçues, il n’est pas OBLIGATOIREMENT nécessaire que votre tableau de données contienne des formules de calcul. Cependant, si cela est nécessaire, il est très important de les écrire également dans votre document WORD. Ainsi, la personne qui reprendra le fichier dans le futur, aura déjà un état des lieux des formules qui existent dans le fichier.
Imaginez que dans mon exemple de gestion de boîtes de chocolat, je rajoute une donnée PRIX UNITAIRE. On va donc ajouter une colonne supplémentaire nommée TOTAL.
PS : n’oubliez pas de revenir à la 4ème étape si vous faites l’exercice pour ajouter le cadre de saisie du prix unitaire.
Dans mon document WORD, il faut rédiger ainsi :
TOTAL = QUANTITE * PRIX UNITAIRE
N’hésitez pas à expliquer les formules complexes. Pensez qu’on ne créé pas des formules complexes tous les jours et qu’il faut pouvoir avoir accès facilement à une explication de ce qu’on voulait faire au moment où on l’a fait.
6ème étape : saisir des données !
Le fichier EXCEL partie données est prêt ! Vous pouvez saisir des données pour préparer la suite de votre fichier. En effet, à ce stade-là, vous n’avez toujours pas créé les tableaux ou indicateurs qui vous permettent d’analyser vos données.
3 possibilités pour saisir vos données :
- Saisir des données fictives mais variées.
- Saisir des données réellement passées pour avoir déjà une analyse de ce qui a été réalisées. En revanche, il peut y avoir des problèmes avec les contraintes de saisie, notamment avec la gestion des dates.
- Saisir au fur et à mesure des données réelles.
L’objectif est d’avoir une grande quantité de lignes (entre 50 et 100), variées, permettant de créer ensuite les tableaux croisés dynamiques.
7ème étape : la construction de vos tableaux de bord !
Cette étape peut se faire bien plus tard dans la construction de votre fichier. En effet, comme dit précédemment, il faut une certaine quantité de données pour créer vos indicateurs.
Lorsque vous êtes prêt pour cette étape, reprenez la liste des objectifs rédigés en début de document Word puis construisez vos tableaux de bord avec des TCD ou des formules propagées.
A retenir !
Ecrire un document pour la conception de votre fichier Excel est identique à écrire un cahier des charges pour usiner un produit, créer un logiciel, construire une maison… Très souvent, des normes ou certifications vous imposent de tout tracer pour une notion de « qualité ». Votre fichier Excel ne doit pas faire exception à la règle. N’oubliez pas que souvent il est un outil décisionnel central dans le fonctionnement de votre service ou entreprise.
