Bonjour à tous,
L’utilisation d’Excel comme gestionnaire de données est une situation extrêmement fréquente… Rappelons tout d’abord qu’à l’origine, Excel n’est pas fait pour stocker des données, mais pour faire des calculs ! Cependant, les usages l’ont très rapidement transformé en outil de gestion de bases de données (une ou plusieurs tables/listes/tableaux de données), et les récents développements d'Excel permettant le stockage de plusieurs millions de lignes vont encore accélérer ce mouvement. Je pense personnellement qu’Excel est dans de nombreuses situations un excellent outil de gestion de données, A CONDITION de respecter un certain nombre de règles!
Une fois ces règles acquises, vous pouvez aussi ajouter un formulaire au fichier grâce à mon dernier outil : FormXL Pro
Je vous présente donc ici ma sélection des 13 règles d’or pour utiliser Excel comme table de données !
Récapitulatif des règles
Règle 0 - Utiliser les tableaux d'Excel
Règle 1 – Une feuille = une table de données et surtout rien d’autre
Règle 2 – Dans la première ligne : uniquement et seulement les titres de colonnes
Règle 3 – Pas de cellules vides dans les titres de colonnes
Règle 4 – Pas de doublons dans les titres de colonnes
Règle 5 – Une clef primaire dans la première colonne
Règle 6 – Pas de lignes et colonnes vides
Règle 7 – Ne pas ajouter de totaux, sous-totaux et calculs intermédiaires
Règle 8 – Utiliser les filtres automatiques
Règle 9 – Éviter d’avoir plusieurs colonnes pour une même dimension
Règle 10 – Positionner les données numériques et les calculs dans la partie droite de la table
Règle 11 – Utiliser les tableaux d’Excel
Règle 12 – N’utiliser qu’une seule formule par colonne
Règle 13 – Utiliser l’outil de validation des données
Règle 0 – Utilisez les tableaux d'Excel retour
[Edit du 10/3/2016] - Je remonte cette règle, car réellement, depuis l'introduction des tableaux en 2007, cela rend obligatoire un certain nombre de règles ci-dessus.
Pour ajouter un tableau, se placer sur vos données, puis Accueil et Mettre sous forme de tableau :
Règle 1 – Une feuille = une table de données et surtout rien d’autre retour
Il est indispensable que chaque table occupe une feuille, et rien qu’une feuille. En effet, avoir deux tables sur la même feuille de calcul est potentiellement très dangereux. Par exemple, vous courrez le risque de supprimer par inadvertance des lignes appartenant aux deux tables.
Règle 2 – Dans la première ligne : uniquement et seulement les titres de colonnes retour
La première ligne de la feuille contient toujours et uniquement les titres et doit se trouver en ligne 1 :
Si vous voulez ajouter des boutons, des grands titres ou d’autres informations, augmentez la hauteur de la première ligne et ajoutez un cadre blanc au dessus (outils de dessin) pour améliorer la lisibilité et la navigation dans le fichier. On se trouve alors sur un autre plan que les données et ceci fonctionne correctement :
Encore une fois, l’idée est de n’avoir que les données sur la feuille au niveau des cellules, et rien d’autre.
Règle 3 – Pas de cellules vides dans les titres de colonnes. retour
En effet, vous perdez l’information concernant le contenu de la colonne et en plus, vous ne pourrez pas utiliser correctement les tableaux croisés dynamiques.
Un corollaire de cette règle est de ne JAMAIS utiliser de cellules fusionnées, que ce soit pour les titres de colonnes ou à l’intérieur de la table.
Règle 4 – Pas de doublons dans les titres de colonnes retour
Afin de garantir que chaque colonne désigne un élément spécifique, il faut se garder d’utiliser le même nom de colonnes deux fois.
Règle 5 – Une clef primaire dans la première colonne retour
Lorsqu’on construit une table dans un gestionnaire de données de type Access, on ajoute toujours une clef primaire à cette table. Cette clef n’existe pas dans Excel, donc il est indispensable de l’ajouter manuellement (ou automatiquement avec VBA). On choisira une clef qui s’auto-incrémentera lors de l’ajout d’une nouvelle ligne.
Aucune cellule vide dans cette colonne ne doit exister, en d’autres termes, chaque ligne doit avoir une clef. Ceci permettra d’effectuer un comptage du nombre de lignes (fonction nombre dans les tableaux croisés dynamiques), ou encore de retrouver rapidement une fiche papier si la base a été saisie à partir de données papier.
Dans le cas où vous utilisez des noms dynamiques, cela permet aussi de définir la hauteur d’une table de données (plus d’information sur les noms dynamiques ici : https://www.polykromy.com/html/poly_main_cours_decaler.html). Depuis l'introduction des tableaux en 2007, je n'utilise plus du tout cette approche, mais je définis un nom sur chaque colonne du tableau.
Règle 6 – Pas de lignes et colonnes vides retour
En laissant une ligne ou une colonne vide, vous risquez de ne travailler que sur une partie de la table (reconnaissance automatique de la table par Excel).
Règle 7 – Ne pas ajouter de totaux, sous-totaux et calculs intermédiaires retour
En ajoutant ces calculs dans la feuille contenant les données, vous courrez le risque que ceux-ci soient comptabilisés dans des tableaux croisés dynamiques ou dans d’autres calculs effectués sur des colonnes entières.
Règle 8 – Utiliser les filtres automatiques retour
L’utilisation des filtres automatiques permet de travailler directement sur toute la table de données sans se poser des questions. Ceci est particulièrement utile pour filtrer les données (évidemment), mais aussi pour créer des tableaux croisés dynamiques ou encore pour trier les données par ordre alphabétique.
Rappel : aller dans l’onglet (ou le menu) données, puis choisir filtres automatiques
Règle 9 – Éviter d’avoir plusieurs colonnes pour une même dimension retour
Vous pouvez retrouver une explication détaillée et en vidéo ici : https://www.xlerateur.com/?p=496
Un exemple classique est la création de nouvelles colonnes pour chaque nouveau mois. Ceci indique en général un problème dans la structure des données et surtout, cela va complexifier les calculs et les comparaisons par mois. Dans de tels cas, il vaut mieux restructurer la table pour mettre la date dans une seule colonne (sauf cas particulier).
Règle 10 – Positionner les données numériques et les calculs dans la partie droite de la table retour
De manière générale, on met les données numériques et les calculs à droite de la table de données. Ceci permet de localiser rapidement les calculs, mais aussi de respecter une certaine logique dans la lecture des informations. Finalement, lors de la construction d’un tableau croisé dynamique, cela permet de plus facilement créer des tableaux complexes.
De manière assez logique, on essaiera aussi de faire en sorte que l'enchainement des formules se fasse de gauche à droite. Ceci permet d'améliorer la vitesse d'exécution ainsi que la lisibilité des formules (et donc d'éviter des erreurs).
Règle 11 – Utiliser les tableaux d’Excel retour
Introduit et enrichit progressivement au fur et à mesure des versions d’Excel, l’utilisation des tableaux (autrefois appelés Liste sous 2003) s’avère relativement efficace notamment pour saisir des données directement dans le tableau. Voici comment les mettre en place :
- 2003 : Menu données, puis Liste
- 2007 et suivant : Sélectionnez votre plage de données, puis dans l’onglet Accueil, choisir Mettre sous forme de tableau
Note : Il y a beaucoup de débats sur la sémantique autour des tables/listes/tableaux de données. Au final, on parle globalement de plus ou moins de la même chose : une structure avec des titres de colonnes et des lignes reprenant les données.
Règle 12 – N’utiliser qu’une seule formule par colonne retour
Si la base est bien structurée, il est recommandé de n’utiliser qu’une seule formule pour toute la colonne, formule qui sera recopiée sur toute la colonne de la table. Cela permet entre autres, d’éviter des corruptions de données.
Règle 13 – Utiliser l’outil de validation des données retour
La validation des données sous Excel est un outil très puissant qui permet de vérifier par exemple que des dates ont bien été saisies, que les valeurs rentrées appartiennent à une liste, ou encore qu’une valeur se trouve entre un maximum et un minimum. Faire un cours complet sur cette fonctionnalité prendrait une journée, tant elle est puissante.
Voici un exemple permettant d’ ajouter une liste à une colonne. Tout d’abord définissez un nom, ici liste_villes (sélectionnez la plage, puis dans la zone de nom située à gauche de la zone de formule, tapez liste_villes, validez par entrée) qui fera référence à votre liste de valeurs.
Puis sélectionnez la colonne de la ville dans votre table de données et choisissez Données – Validation, puis dans le menu déroulant, Liste. Finalement, ajouter = liste_villes dans la zone "Source".
Il ne reste plus qu’à utiliser la zone de liste déroulante disponible lorsqu’on se trouve sur la cellule :
En respectant ces 13 règles vous pourrez construire des tables/listes/tableaux de données faciles à utiliser, rigoureux et solides.
Si vous en voyez d'autres n'hésitez pas à laisser un commentaire ci-dessous...
Pour aller plus loin :
- Ajouter un formulaire de saisie via FormXL Pro :
- Mon CD de formation "Organisez et gérez vos données" qui reprend un cours complet avec exemple, 4h de vidéo sur ces sujets et plusieurs autres règles d'or !
A bientôt
Gaëtan
