Organiser ses données dans Excel : la base d'un fichier propre

Les règles d'un bon tableau de données

Avant de parler d'outils ou de formules, il faut poser les fondations, parce que c'est sur elles que tout le reste va s'appuyer. Quatre règles non négociables suffisent à transformer un amas de cellules en une vraie base de données exploitable.
Respecte-les et la quasi-totalité de tes problèmes de tri, de filtre et d'analyse disparaissent d'eux-mêmes, avant même que tu les rencontres.

Une ligne décrit un seul enregistrementChaque ligne porte une seule chose, une vente, un client, une commande, jamais deux ventes sur la même ligne ni une vente étalée sur deux lignes.
Le tri, le filtre, les formules et les tableaux croisés dynamiques raisonnent tous ligne par ligne, si bien que cette première règle conditionne déjà tout le reste.

Une seule ligne d'en-têtes, tout en hautUn titre court et unique par colonne, sans titre sur deux lignes ni en-tête répété au milieu du tableau.
C'est cette ligne qu'Excel lit pour nommer tes colonnes dans les filtres, les formules et les TCD, alors brouille-la et tous ces outils perdent leurs repères.

Aucune ligne ni colonne entièrement vide à l'intérieurUn vide complet coupe ton tableau en deux, parce qu'Excel croit que les données s'arrêtent là, et le tri, le filtre et la détection de plage s'arrêtent avec lui.
Garde un bloc bien compact, puisque les espaces se mettent autour du tableau, jamais dedans.

Zéro cellule fusionnée dans la zone de donnéesLa fusion est la grande ennemie d'un tableau exploitable, puisqu'elle casse le tri et le filtre, bloque les TCD et déclenche des erreurs au moindre traitement.
Pour centrer un titre, passe plutôt par Format de cellule puis « Centré sur plusieurs colonnes », tu obtiens le même rendu sans aucun des dégâts.

Le défi

Place ton curseur dans une cellule de ton tableau et appuie sur Ctrl + * (Ctrl + Maj + 8). Excel sélectionne alors toute la plage de données qu'il détecte. Si la sélection couvre exactement ton tableau, ta structure est propre. Si elle s'arrête trop tôt ou déborde, c'est qu'une ligne vide, une colonne vide ou une fusion casse le bloc.

Un type de données cohérent par colonne

Une colonne ne devrait jamais contenir qu'une seule sorte de donnée, que des dates, ou que des montants, ou que du texte, parce que le mélange est la cause silencieuse de la plupart des tris bizarres et des sommes fausses.
C'est d'autant plus piégeux que rien ne saute aux yeux au premier regard, et que le problème ne se révèle qu'au moment où tu calcules.

Le cas le plus fréquent, ce sont les nombres stockés comme du texte. Une colonne où « 1250 » est en réalité du texte, ce qui arrive très souvent après un import, ne s'additionne pas, trie dans le désordre et fait échouer les RECHERCHEV.
Le signe qui ne trompe pas, c'est un petit triangle vert en haut à gauche de la cellule, ou des nombres alignés à gauche au lieu de la droite. Quand tu le repères, sélectionne la colonne, clique sur l'avertissement et choisis « Convertir en nombre ».

Comment repérer une colonne au mauvais type

• Alignement par défaut : Excel aligne les nombres et dates à droite, le texte à gauche. Un nombre aligné à gauche est probablement du texte.
• Triangle vert : il signale une incohérence détectée par Excel, souvent « nombre stocké en texte ».
• Somme à zéro : si SOMME renvoie 0 sur une colonne de chiffres, ce sont du texte, pas des nombres.
• Tri illogique : « 10 » classé avant « 9 » trahit un tri alphabétique sur du texte au lieu d'un tri numérique.

Le saviez-vous ?

Dans Excel, une date est en réalité un nombre : le 1er janvier 1900 vaut 1, et chaque jour ajoute 1, si bien que le 1er janvier 2000 est stocké comme 36 526. C'est pour ça qu'une colonne de vraies dates se trie et se calcule comme des nombres. Quand des dates refusent de se ranger dans l'ordre, c'est presque toujours qu'elles sont stockées en texte, pas en dates.

Les outils pour nettoyer et naviguer

Une fois la structure posée, le plus dur est fait, mais quelques outils des menus Données et Affichage vont te faire gagner un temps fou sur la mise au propre et la lecture.
Tu en retrouveras trois en permanence dès que ton tableau dépasse quelques dizaines de lignes, alors autant les avoir sous la main tout de suite.

Supprimer les doublons nettoie la liste en un clicDonnées > Supprimer les doublons garde la première occurrence de chaque enregistrement et retire les suivantes.
Choisis les colonnes qui définissent un doublon, l'e-mail seul ou la combinaison nom + date, mais travaille toujours sur une copie de façon à pouvoir comparer ou revenir en arrière.

Convertir texte en colonnes éclate une colonne trop chargéeDonnées > Convertir découpe le contenu d'une colonne en plusieurs, selon un séparateur (virgule, espace, point-virgule) ou une largeur fixe.
C'est l'outil idéal pour séparer « Nom Prénom » en deux colonnes, éclater une adresse collée, ou forcer le bon format d'une colonne mal interprétée à l'import.

Figer les volets garde les en-têtes sous les yeuxAffichage > Figer les volets immobilise la ligne d'en-têtes, et au besoin les premières colonnes, quand tu fais défiler un grand tableau.
Tu sais ainsi toujours quelle colonne tu lis, même arrivé à la ligne 4 000, et c'est un outil qui mérite qu'on s'y arrête un instant.

Figer les volets, en détail

Où le trouver :Affichage›Figer les volets

Sur un tableau de 2 000 lignes, perdre ses en-têtes de vue dès qu'on défile, c'est se tromper de colonne une fois sur deux, et Affichage > Figer les volets règle exactement ce problème.
« Figer la ligne supérieure » garde la première ligne visible, et pour figer à la fois plusieurs lignes et colonnes, il suffit de placer le curseur juste sous et à droite de la zone à immobiliser puis de choisir « Figer les volets », ce qui colle à l'écran tout ce qui se trouve au-dessus et à gauche.

L’erreur fatale

Supprimer les doublons efface des lignes définitivement, et un Ctrl + Z ne te rendra pas toujours ce que tu croyais garder. Avant de lancer Données > Supprimer les doublons, copie ta plage sur une nouvelle feuille et travaille sur la copie. Tu gardes ainsi l'original intact pour comparer ou revenir en arrière.

Préparer ses données pour un TCD ou des formules

Toutes les règles vues jusqu'ici convergent vers un même but, des données prêtes à analyser, parce qu'un tableau croisé dynamique et les formules d'agrégation (SOMME.SI, NB.SI, RECHERCHEV) réclament exactement le même format « à plat ».
Avant de lancer une analyse, garde donc cette petite checklist en tête, elle te fera repérer en quelques secondes ce qui empêcherait ton tableau de tourner.

La dernière étape est de loin la plus rentable, puisqu'elle consiste à transformer ta plage en tableau structuré (Ctrl + L). Ton TCD et tes formules pointent alors vers une zone qui s'étend toute seule à chaque nouvelle ligne, et tu n'as plus jamais à redéfinir la source de tes analyses.

Les erreurs fréquentes à éviter

La plupart des règles vues plus haut se retournent en mauvaises habitudes quand on les oublie, et il y en a une qui mérite vraiment qu'on s'y arrête, parce qu'elle paraît anodine alors qu'elle casse tout en silence.
C'est l'habitude de glisser des sous-totaux au milieu des données, une ligne « Total trimestre » insérée entre deux ventes, par exemple. Ces totaux intermédiaires faussent les tris, sont comptés à tort dans les formules et bloquent les tableaux croisés dynamiques, sans jamais déclencher d'alerte visible.

Le réflexe à garder est simple, puisqu'il suffit de laisser la zone de données pure et de placer tes totaux en bas, bien à l'écart, ou de laisser un tableau structuré gérer sa propre ligne de total.
Les autres pièges classiques découlent directement des règles précédentes, qu'il s'agisse d'une information collée dans une seule cellule, d'une colonne aux formats incohérents ou d'un tableau à double entrée impossible à exploiter, et tous se résolvent de la même façon, en revenant à des données bien à plat.

Ce qu’il faut retenir

Sépare toujours la saisie de l'affichage. La zone de données reste brute, plate et compacte, une ligne par fait, une info par cellule. La mise en forme jolie, les totaux et les vues croisées se construisent ailleurs, avec la mise en forme conditionnelle, un tableau structuré ou un tableau croisé dynamique. Données propres d'un côté, présentation de l'autre, et tout devient simple.

Tu as maintenant un tableau sain, plat et compact, prêt à être exploité. L'étape suivante consiste à le rendre vivant en le transformant en tableau structuré, cette plage intelligente qui s'étend toute seule, nomme ses colonnes et alimente tes formules sans que tu aies à redéfinir quoi que ce soit. C'est tout l'objet de la prochaine leçon !