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 données !

Réca­pit­u­latif des règles
Règle 0 — Utilis­er les tableaux d’Excel
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 intermédiaires
Règle 8 – Utilis­er les fil­tres automatiques
Règle 9 – Éviter d’avoir plusieurs colonnes pour une même dimension
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 donné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 correctement :
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 papier.
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 : https://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 automatiques

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 : https://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 particulier).
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 complexes.
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 donné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 donné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 puissante.
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 cellule :
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

Top 10 de mes fonctionnalités préférées, les connaissez-vous ?

Un post réca­pit­u­latif pour mes 10 fonc­tion­nal­ités préférées sur Excel !

On va la faire à la MTV, en com­mençant par la fin !

10 — Les con­necteurs pour dessin­er effi­cace­ment et proprement
— Util­i­sa­tion des con­necteurs pour créer rapi­de­ment des dessins sur Excel :
2000 à 2003 : Affichage de la barre d’outils Dessin, puis :
connecteurs
2007–2013 : dans l’on­glet Inser­tion, sous “formes”.
A not­er que l’outil est beau­coup plus facile à utilis­er sur 2003 que sous 2007–2013 !
top-10-Excel-2

9‑Copier une plage comme une image
Très utile pour copi­er des tableaux ou des graphiques comme des images (par exem­ple pour les coller ensuite dans pow­er­point), voici la procédure :
2000–2003 : touche MAJ appuyée, puis Edi­tion et choisir Copi­er une image…
2007–2013 : Onglet Accueil, puis coller, en tant qu’im­age… et choisir copi­er comme image (au niveau de la logique MS, ca ressem­ble au bou­ton démar­rer pour arrêter win­dows XP 😉 )
A-top-10-Excel-4

8‑Ajuster les pages pour l’impression
Bien que rel­a­tive­ment bien con­nu, cette astuce est néan­moins incon­tourn­able dès lors que l’on veut imprimer des tableaux. On peut ain­si laiss­er vide le nom­bre de pages en hau­teur pour n’avoir qu’un ajuste­ment sur la largeur !
top-10-Excel-3

7a-Créer très rapi­de­ment une série de noms
A par­tir des noms de lignes ou de colonnes d’un tableau, on peut ain­si très rapi­de­ment créer une série de noms.
2000–2003 : Menu Inser­tion, Noms, Créer…
A-top-10-Excel-5
2007–2013 : Onglet For­mules, Créer à par­tir de la sélection:
A-top-10-Excel-6

7b-Tou­jours dans les noms, coller une liste avec leurs adresses
Pour utilis­er cette fonc­tion­nal­ité, il faut bien sur que des noms aient été créés!
2000–2003 : facile d’ac­cès : Inser­tion, nom, coller une liste
A-top-10-Excel-7
2007–2013: c’est très bien caché par rap­port à 2003, car le nom de la fonc­tion­nal­ité a été changé :
A-top-10-Excel-8

6‑validation et zone nommée
C’est une astuce que j’u­tilise fréquem­ment pour faire référence à une liste se trou­vant dans une autre feuille :
1- Nom­mer la liste (2000–2003: inser­tion, noms, définir / 2007–2013: onglet for­mules, ges­tion­naire de noms)
2- Dans don­nées val­i­da­tion, choisir liste et faire référence au nom:
A-top-10-Excel-9

5- Mes fonc­tions de cal­culs préférées
Evidem­ment, on ne peut éviter SOMMEPROD, qui peut com­pléter adéquate­ment SOMME.SI, lorsqu’on doit gér­er plusieurs critères. Sur le même principe, dans Excel 2007–2010, SOMME.SI.ENS est devenu incontournable.
Pour 2003, j’adore DECALER,notam­ment en lien avec des noms dynamiques (voir plus bas).
Finale­ment, les fonc­tions INDEXet EQUIV et leurs com­bi­naisons (à la place de RECHERCHEV que je n’aime pas du tout ! 😉 ).

4‑Les tableaux croisés dynamiques
Si vous ne les con­nais­sez pas et que vous utilisez des listes de don­nées (une table dont la pre­mière ligne con­tient les en-tête pour chaque colonne), c’est un out­il incon­tourn­able pour l’analyse. je n’ai pas le temps de dévelop­per ici (ca prendrait un cd com­plet !), mais voici l’in­struc­tion de base :
2000–2003 : Don­nées — Rap­port de tableaux croisés dynamiques
2007–2013 : Inser­tion — Rap­port de tableaux croisés dynamiques
On fait ensuite gliss­er les champs aux emplace­ments désirés (en lignes, en colonnes, sur les don­nées ou encore dans la zone de filtre).

3- Les noms dynamiques (2000–2003) et les tables (2007–2013)
2000–2003 : Il s’ag­it de créer des noms avec la fonc­tion DECALER qui per­me­t­tent de s’a­juster aux don­nées. Par exem­ple, créer un nom pour faire référence à la table con­tenant les don­nées ali­men­tant un tableau croisés dynamique.
On peut aus­si utilis­er des noms dynamiques pour des images ou encore pour des listes.
Pour un cours com­plet sur ces util­i­sa­tions : cliquez ici
2007–2013 : Il est main­tenant beau­coup plus facile de créer un nom dynamique, il suf­fit sim­ple­ment de met­tre la plage sous la forme d’une table, puis de nom­mer cette plage. On peut aus­si utilis­er le nom de cette table, mais pas dans tous les cas (val­i­da­tions avec liste, par exemple).

2- 2007–2013 seule­ment — la punaise sur le nom du fichier
Unique­ment sur 2007–2013, en cli­quant à droite du nom du fichi­er à par­tir du bou­ton office/menu fichi­er, on garde en per­ma­nence les fichiers qu’on utilise le plus.
Sous 2003, on peut aus­si créer des rac­cour­cis vers les fichiers les plus util­isés (dans l’ex­plo­rateur win­dows), mais c’est moins rapi­de et intuitif.

1‑Les rac­cour­cis-clavier incontournables
Cer­taine­ment l’as­tuce per­me­t­tant de gag­n­er le plus de temps, le raccourci-clavier:
ALT : en dessi­nant, qui per­met de caler l’im­age sur les bor­ds de la cellule
F4 : répéter la dernière action
F4 : dans une for­mule, ajouter/supprimer des $
F3 : dans une for­mule (ou ailleurs), insér­er un nom. Aus­si utile dans val­i­da­tion ou mise en forme conditionnelle
CTRL + G, U, I : respec­tive­ment Gras, Souligné (Under­lined) et Italique
ALT + F11 : affich­er VBE
CTRL + Entrée : en édi­tant une for­mule, ca per­met de recopi­er sur toute la plage sélectionnée
CTRL + ; : : insér­er la date et l’heure
Encore plus sur ce sujet : ici ou

Et voilà, bra­vo si vous avez tout lu !
Si vous avez votre pro­pre liste (de 5 ou 10) ou que vous voulez com­pléter celle-ci, n’hésitez pas !

A bien­tôt.

Gaë­tan

Changer le format d’enregistrement par défaut sous 2007

En lien avec un précé­dent post, voici la procé­dure pour chang­er le type de fichi­er par défaut. Procé­dure que je recom­mande très très forte­ment dès que vous maniez du VBA:
— Bou­ton Office (en haut à gauche)
— puis options excel, en bas à droite
— puis enreg­istrement et choisir le for­mat d’en­reg­istrement par défaut

A bien­tôt

Gaë­tan