13 règles d’or pour parfaitement gérer vos données sur Excel

Bon­jour à tous,

L’utilisation d’Excel comme ges­tion­naire de don­nées est une sit­u­a­tion extrême­ment fréquente… Rap­pelons tout d’abord qu’à l’origine, Excel n’est pas fait pour stock­er des don­nées, mais pour faire des cal­culs ! Cepen­dant, les usages l’ont très rapi­de­ment trans­for­mé en out­il de ges­tion de bases de don­nées (une ou plusieurs tables/listes/tableaux de don­nées), et les récents développe­ments d’Ex­cel per­me­t­tant le stock­age de plusieurs mil­lions de lignes vont encore accélér­er ce mou­ve­ment. Je pense per­son­nelle­ment qu’Excel est dans de nom­breuses sit­u­a­tions un excel­lent out­il de ges­tion de don­nées, A CONDITION de respecter un cer­tain nom­bre de règles!

Une fois ces règles acquis­es, vous pou­vez aus­si ajouter un for­mu­laire au fichi­er grâce à mon dernier out­il : For­mXL Pro

Je vous présente donc ici ma sélec­tion des 13 règles d’or pour utilis­er Excel comme table de don­nées !

Réca­pit­u­latif des règles
Règle 0 — Utilis­er les tableaux d’Ex­cel
Règle 1 – Une feuille = une table de don­nées et surtout rien d’autre
Règle 2 – Dans la pre­mière ligne : unique­ment et seule­ment les titres de colonnes
Règle 3 – Pas de cel­lules vides dans les titres de colonnes
Règle 4 – Pas de dou­blons dans les titres de colonnes
Règle 5 – Une clef pri­maire dans la pre­mière colonne
Règle 6 – Pas de lignes et colonnes vides
Règle 7 – Ne pas ajouter de totaux, sous-totaux et cal­culs inter­mé­di­aires
Règle 8 – Utilis­er les fil­tres automa­tiques
Règle 9 – Éviter d’avoir plusieurs colonnes pour une même dimen­sion
Règle 10 – Posi­tion­ner les don­nées numériques et les cal­culs dans la par­tie droite de la table
Règle 11 – Utilis­er les tableaux d’Excel
Règle 12 – N’utiliser qu’une seule for­mule par colonne
Règle 13 – Utilis­er l’outil de val­i­da­tion des don­nées

Règle 0 – Utilisez les tableaux d’Ex­cel retour
[Edit du 10/3/2016] — Je remonte cette règle, car réelle­ment, depuis l’in­tro­duc­tion des tableaux en 2007, cela rend oblig­a­toire un cer­tain nom­bre de règles ci-dessus.
Pour ajouter un tableau, se plac­er sur vos don­nées, puis Accueil et Met­tre sous forme de tableau :
mise-forme-tableau 1

Règle 1 – Une feuille = une table de don­nées et surtout rien d’autre retour
Il est indis­pens­able que chaque table occupe une feuille, et rien qu’une feuille. En effet, avoir deux tables sur la même feuille de cal­cul est poten­tielle­ment très dan­gereux. Par exem­ple, vous cour­rez le risque de sup­primer par inad­ver­tance des lignes appar­tenant aux deux tables.
Règle 1
Règle 2 – Dans la pre­mière ligne : unique­ment et seule­ment les titres de colonnes retour
La pre­mière ligne de la feuille con­tient tou­jours et unique­ment les titres et doit se trou­ver en ligne 1 :
Règle 1
Si vous voulez ajouter des bou­tons, des grands titres ou d’autres infor­ma­tions, aug­mentez la hau­teur de la pre­mière ligne et ajoutez un cadre blanc au dessus (out­ils de dessin) pour amélior­er la lis­i­bil­ité et la nav­i­ga­tion dans le fichi­er. On se trou­ve alors sur un autre plan que les don­nées et ceci fonc­tionne cor­recte­ment :
Règle 1
Encore une fois, l’idée est de n’avoir que les don­nées sur la feuille au niveau des cel­lules, et rien d’autre.
Règle 3 – Pas de cel­lules vides dans les titres de colonnes. retour
En effet, vous perdez l’information con­cer­nant le con­tenu de la colonne et en plus, vous ne pour­rez pas utilis­er cor­recte­ment les tableaux croisés dynamiques.
Un corol­laire de cette règle est de ne JAMAIS utilis­er de cel­lules fusion­nées, que ce soit pour les titres de colonnes ou à l’intérieur de la table.
Règle 1
Règle 4 – Pas de dou­blons dans les titres de colonnes retour
Afin de garan­tir que chaque colonne désigne un élé­ment spé­ci­fique, il faut se garder d’utiliser le même nom de colonnes deux fois.
Règle 1
Règle 5 – Une clef pri­maire dans la pre­mière colonne retour
Lorsqu’on con­stru­it une table dans un ges­tion­naire de don­nées de type Access, on ajoute tou­jours une clef pri­maire à cette table. Cette clef n’existe pas dans Excel, donc il est indis­pens­able de l’ajouter manuelle­ment (ou automa­tique­ment avec VBA). On choisira une clef qui s’auto-incrémentera lors de l’ajout d’une nou­velle ligne.
Règle 1
Aucune cel­lule vide dans cette colonne ne doit exis­ter, en d’autres ter­mes, chaque ligne doit avoir une clef. Ceci per­me­t­tra d’effectuer un comp­tage du nom­bre de lignes (fonc­tion nom­bre dans les tableaux croisés dynamiques), ou encore de retrou­ver rapi­de­ment une fiche papi­er si la base a été saisie à par­tir de don­nées papi­er.
Règle 1
Dans le cas où vous utilisez des noms dynamiques, cela per­met aus­si de définir la hau­teur d’une table de don­nées (plus d’information sur les noms dynamiques ici : http://www.polykromy.com/html/poly_main_cours_decaler.html). Depuis l’in­tro­duc­tion des tableaux en 2007, je n’u­tilise plus du tout cette approche, mais je défi­nis un nom sur chaque colonne du tableau.

Règle 6 – Pas de lignes et colonnes vides retour
En lais­sant une ligne ou une colonne vide, vous risquez de ne tra­vailler que sur une par­tie de la table (recon­nais­sance automa­tique de la table par Excel).
Règle 1
Règle 7 – Ne pas ajouter de totaux, sous-totaux et cal­culs inter­mé­di­aires retour
En ajoutant ces cal­culs dans la feuille con­tenant les don­nées, vous cour­rez le risque que ceux-ci soient compt­abil­isés dans des tableaux croisés dynamiques ou dans d’autres cal­culs effec­tués sur des colonnes entières.
Règle 1
Règle 8 – Utilis­er les fil­tres automa­tiques retour
L’utilisation des fil­tres automa­tiques per­met de tra­vailler directe­ment sur toute la table de don­nées sans se pos­er des ques­tions. Ceci est par­ti­c­ulière­ment utile pour fil­tr­er les don­nées (évidem­ment), mais aus­si pour créer des tableaux croisés dynamiques ou encore pour tri­er les don­nées par ordre alphabé­tique.
Rap­pel : aller dans l’onglet (ou le menu) don­nées, puis choisir fil­tres automa­tiques

Règle 1

Règle 9 – Éviter d’avoir plusieurs colonnes pour une même dimen­sion retour
Vous pou­vez retrou­ver une expli­ca­tion détail­lée et en vidéo ici : http://www.xlerateur.com/?p=496
Un exem­ple clas­sique est la créa­tion de nou­velles colonnes pour chaque nou­veau mois. Ceci indique en général un prob­lème dans la struc­ture des don­nées et surtout, cela va com­plex­i­fi­er les cal­culs et les com­para­isons par mois. Dans de tels cas, il vaut mieux restruc­tur­er la table pour met­tre la date dans une seule colonne (sauf cas par­ti­c­uli­er).
Règle 1

Règle 10 – Posi­tion­ner les don­nées numériques et les cal­culs dans la par­tie droite de la table retour
De manière générale, on met les don­nées numériques et les cal­culs à droite de la table de don­nées. Ceci per­met de localis­er rapi­de­ment les cal­culs, mais aus­si de respecter une cer­taine logique dans la lec­ture des infor­ma­tions. Finale­ment, lors de la con­struc­tion d’un tableau croisé dynamique, cela per­met de plus facile­ment créer des tableaux com­plex­es.
Règle 1
De manière assez logique, on essaiera aus­si de faire en sorte que l’en­chaine­ment des for­mules se fasse de gauche à droite. Ceci per­met d’amélior­er la vitesse d’exé­cu­tion ain­si que la lis­i­bil­ité des for­mules (et donc d’éviter des erreurs).
Règle 11 – Utilis­er les tableaux d’Excel retour
Intro­duit et enri­chit pro­gres­sive­ment au fur et à mesure des ver­sions d’Excel, l’utilisation des tableaux (autre­fois appelés Liste sous 2003) s’avère rel­a­tive­ment effi­cace notam­ment pour saisir des don­nées directe­ment dans le tableau. Voici com­ment les met­tre en place :
— 2003 : Menu don­nées, puis Liste
— 2007 et suiv­ant : Sélec­tion­nez votre plage de don­nées, puis dans l’onglet Accueil, choisir Met­tre sous forme de tableau
Règle 1
Note : Il y a beau­coup de débats sur la séman­tique autour des tables/listes/tableaux de don­nées. Au final, on par­le glob­ale­ment de plus ou moins de la même chose : une struc­ture avec des titres de colonnes et des lignes reprenant les don­nées.
Règle 12 – N’utiliser qu’une seule for­mule par colonne retour
Si la base est bien struc­turée, il est recom­mandé de n’utiliser qu’une seule for­mule pour toute la colonne, for­mule qui sera recopiée sur toute la colonne de la table. Cela per­met entre autres, d’éviter des cor­rup­tions de don­nées.
Règle 1
Règle 13 – Utilis­er l’outil de val­i­da­tion des don­nées retour
La val­i­da­tion des don­nées sous Excel est un out­il très puis­sant qui per­met de véri­fi­er par exem­ple que des dates ont bien été saisies, que les valeurs ren­trées appar­ti­en­nent à une liste, ou encore qu’une valeur se trou­ve entre un max­i­mum et un min­i­mum. Faire un cours com­plet sur cette fonc­tion­nal­ité prendrait une journée, tant elle est puis­sante.
Voici un exem­ple per­me­t­tant d’ ajouter une liste à une colonne. Tout d’abord définis­sez un nom, ici liste_villes (sélec­tion­nez la plage, puis dans la zone de nom située à gauche de la zone de for­mule, tapez liste_villes, validez par entrée) qui fera référence à votre liste de valeurs.
Règle 1
Puis sélec­tion­nez la colonne de la ville dans votre table de don­nées et choi­sis­sez Don­nées – Val­i­da­tion, puis dans le menu déroulant, Liste. Finale­ment, ajouter = liste_villes dans la zone “Source”.
Règle 1
Il ne reste plus qu’à utilis­er la zone de liste déroulante disponible lorsqu’on se trou­ve sur la cel­lule :
Règle 1
En respec­tant ces 13 règles vous pour­rez con­stru­ire des tables/listes/tableaux de don­nées faciles à utilis­er, rigoureux et solides.

Si vous en voyez d’autres n’hésitez pas à laiss­er un com­men­taire ci-dessous…

Pour aller plus loin :
— Ajouter un for­mu­laire de saisie via For­mXL Pro :

— Mon CD de for­ma­tion “Organ­isez et gérez vos don­nées” qui reprend un cours com­plet avec exem­ple, 4h de vidéo sur ces sujets et plusieurs autres règles d’or !

A bien­tôt

Gaë­tan

En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel
Pour marque-pages : permalien.

25 réactions à 13 règles d’or pour parfaitement gérer vos données sur Excel

  1. site admin a écrit:

    Petite mise à jour à 7:03 sute à un com­men­taire de Dominique sur l’en­chaine­ment des for­mules qui doit se faire autant que pos­si­ble de gauche à droite. Mer­ci 😉

  2. Charles a écrit:

    Excel­lent…

  3. J.G.D a écrit:

    superbe­ment bien expliqué !!!

  4. Diaz jean-Luc a écrit:

    Bon­jour,

    Il ya quelques temps vous annon­ciez la sor­tie prochaine d’un cous sur VBA avance voire très avancé. J’avais répon­du à un sondage qui por­tait sur le chois du sup­port (vidéo ou CD) et per­son­nelle­ment j’é­tais favor­able au CD mais cela est un détail.
    Je suis impa­tient de con­naître la date de sor­tie de ce nou­veau cours qui j’ en suis cer­tain sera excel­lent comme pour les autres CD que je pos­sède.
    Mer­ci pour votre réponse.
    Cor­diale­ment
    Jean-Luc Diaz

  5. site admin a écrit:

    Bon­jour,

    Le cours surV­BA avancé n’est pas encore final­isé.

    J’ai du faire face à d’autres impérat­ifs, mais ca reste dans mes pri­or­ités. Donc mal­heureuse­ment, pas de date ferme de prévue.

    Je vous tiendrai au courant de la suite.

    Mer­ci pour votre ques­tion.

    Gae­tan Mour­mant

  6. ARISTIDE YANDO a écrit:

    j’ attend avec impa­tience ce mer­ci de tout vos courage

    ARIS

  7. LJ a écrit:

    Mer­ci Gae­tan de rap­pel­er très claire­ment ces “bonnes pra­tiques” qui au final per­me­t­tent..
    … de gag­n­er du temps
    … aug­menter la fia­bil­ité du traite­ments de don­nées
    … et d’amélior­er l’ex­ploita­tion de celles-ci.

    Cdlt
    LJ

  8. Pingback:XLérateur » Blog Archive » Les outils pour analyser vos données - Xlérer vos connaissances sur Excel

  9. J-F L a écrit:

    franche­ment bra­vo et mer­ci

  10. billy a écrit:

    Bon­jour,

    J’a­jouterai la règle “For­mat min­i­mal­iste et stan­dard­isé des don­nées”, qui rejoint et élar­git la règle 13 Val­i­da­tions.

    Par exem­ple, exprimer les pour­cent­ages en déci­males donc en nom­bre stan­dard, car il est plus facile de mul­ti­pli­er les déci­males par 100 lors de la présen­ta­tion finale que de se deman­der pour chaque nom­bre s’il faut lui appli­quer une opéra­tion.
    Ou encore, tout en minus­cules, pas d’ac­cent (surtout sur les majus­cules !), etc.
    La don­née doit être la plus brute pos­si­ble !

    Un grand mer­ci pour ces 13 règles 😀
    Cor­diale­ment.

  11. c bullet a écrit:

    the best site i have found!!! mer­ci beau­coup de la part d’une per­son­ne très novice mais très motivée!!!

  12. Gaetan Mourmant a écrit:

    Mer­ci à C Bul­let 😉

  13. David a écrit:

    Sim­ple­ment dit : don­nées sim­ple­ment stockées,et bien com­pactées (écologique quoi) si seule­ment les tableaux qu’on nous don­nent à traité pou­vez suiv­re ces règles

  14. Gaetan Mourmant a écrit:

    Oui, c’est bien pour ca que j’en par­le ici 😉 J’en ai fait un CD com­plet si vous cherchez des idées de cadeaux de Noël 😉

  15. Clem a écrit:

    Très juste tous ces con­seils ! Mer­ci
    Depuis l’in­tro­duc­tion des tableaux, il n’est toute­fois plus aus­si cru­cial de plac­er la pre­mière entête en ´A1’
    Mais cela reste une bonne idée si l’on ne com­prend pas toutes les impli­ca­tions d’un tel choix

  16. Gaetan a écrit:

    Bon­jour Clem,

    Oui, je suis d’ac­cord avec vous. Ca reste cepen­dant une bonne pra­tique, ca évite la ten­ta­tion de vouloir avoir autre chose qu’un tableau dans une feuille (et donc de sup­primer par inad­ver­tance des don­nées !).

    Mer­ci pour votre com­men­taire.

    Gae­tan

  17. veronique NICOLE a écrit:

    1ère vis­ite sur votre site. je suis épatée. effi­cience, je crois qu’il n’y a pas d’autre mot pour résumer vos pro­pos. mer­ci beau­coup. vero

  18. Gaetan a écrit:

    Bien­v­enue Véronique et mer­ci pour vos com­men­taires.
    Gae­tan

  19. Daniel a écrit:

    Bon­jour Gaë­tan,
    Mer­ci beau­coup pour ces infor­ma­tions *élé­men­taires* mais qui m’é­taient incon­nues. Je pen­sais avoir quelques bases mais me rends compte que j’en suis loin.
    Super infos bien utiles..
    Mer­ci encore

  20. Gaetan a écrit:

    Bon­jour Daniel,
    Bien con­tent que tout ceci vous soit utile.
    A bien­tôt
    Gae­tan

  21. Pingback:Informatique - clauche3107 | Pearltrees

  22. meftah a écrit:

    qu’est ce qu’un mul­ti colonnes et com­ment le créer !?

  23. Gaetan a écrit:

    Bojour Mef­tah,
    Je ne com­prends pas bien ce que vous voulez dire par mul­ti-colonnes?
    Cor­diale­ment
    Gae­tan

  24. Pingback:Excel | Pearltrees

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.