Tutoriel

Création de tableaux à l'aide de Microsoft Excel : Les bonnes pratiques

Selon comment vous construirez votre tableau à l'aide du tableur, soit vous, vous faciliterez la vie et ouvrirez des horizons, soit vous irez dans des impasses et manipulations à n'en plus finir.

Pour Microsoft Excel, un tableau répond à des règles précises. Connaître et respecter ces règles est un gage de données cohérentes, justes et facile à maintenir.

C'est quoi un tableau ?

Un tableau est un ensemble de données organisées en lignes et colonnes.

Sens d'un tableau

Il y a deux façons de présenter un tableau.

Les données en colonnes

C'est à bannir, Excel ne comprend pas cette façon de présenter les données.

C'est se priver de beaucoup de fonctionnalités ou du moins de les compliquer.

tableau présentant des données en colonne

Ce n'est pas naturel et même un humain a plus de mal à lire l'information.

Les données en ligne

C'est la façon la plus intuitive de conserver l'information.

Tout comme Microsoft Excel, c'est celle que nous comprenons le mieux.

tableau présentant des données en ligne

Où commencer un tableau ?

Il n'y a aucune règle précise.

Si le tableau sert uniquement à stocker des données, il vaut mieux commencer en cellule "A1".
Si vous devez connecter le tableau à d'autres applications, ce sera plus simple.

S’il présente aussi des éléments d'analyse, alors il faut se laisser une zone suffisamment haute.
Dans ce cas, je le démarre généralement en ligne 9.

Le numéro de colonne n'a lui, aucune importance (mais j'ai pour habitude de débuter en colonne "C").

Structurer un tableau

Les étiquettes

Pour Microsoft Excel, la première ligne du tableau comporte des libellés de colonnes.

Il appelle ces données des "étiquettes", mais sur certaines fonctions, il emploiera le terme de "nom de champ".

La premiere ligne du tableau excel comporte le nom des colonnes

Bien sûr, deux colonnes ne peuvent porter la même étiquette.

Ruptures au sein du tableau

Lignes et colonnes vierges

Pour Microsoft Excel, un tableau se termine dès qu'il rencontre une colonne ou ligne vierge.

tableau excel dont les données sont séparées par des lignes vide

Vous pouvez le constater en vous positionnant dans une cellule du tableau et utiliser la combinaison de touches Ctrl + *.

Le but est de prendre toutes les données de la plage.

Chaque fois que vous insérez une ligne vierge, pour EXCEL vous construisez un nouveau tableau.

Les lignes vierges, sont difficiles à gérer

Pour trier le tableau, il faudra tout d'abord le sélectionner. Cette action est inutile avec un tableau homogène, où, il suffit de se positionner dans une cellule de la colonne sur laquelle le tri doit s'effectuer.

tri d'un tableau excel dont les données sont séparées par des lignes vide
  • Les lignes vierges vont se regrouper.
  • Il faudra les supprimer et les refaire.
regroupement des lignes vierges après application du tri

Le cas des calculs intermédiaires

Je rencontre des tableaux où chaque fois que l'on change de pays, ville, client, article, etc.., on souhaite réaliser un calcul.

La tentation est fréquente d'insérer une ligne pour réaliser ces calculs intermédiaires.

On se retrouve avec la même problématique que les lignes vierges.
Lors d'un tri, il faudra reconstruire les formules

tri d'un tableau excel dont les données sont séparées par des calculs intermédiaires

On mélange des données avec l'exploitation des données.

L'analyse des données devient plus aléatoire.

Il existe des outils

La fonction "Sous Total" du menu "Données" est particulièrement adapté pour répondre à cette demande.

Il existe des fonctions de regroupement comme "SOMME.SI" ou "SOMME.SI.ENS".

Bien sûr les "Tableaux Croisés Dynamiques" répondront également à votre besoin.

Les fusions de cellules

Les fusions de cellules posent déjà des soucis en dehors des tableaux.

Elles sont à bannir au sein d'un tableau.

Tableau Excel avec des cellules fusionnées

Les tris et insertions deviennent compliqués.

Message d'erreur de Microsoft Excel lors du tri d'un tableau avec des fusion de cellules

Il vaut mieux répéter l'information sur chaque ligne que de déstructurer le tableau.

Isoler le tableau

Si les données doivent représenter une zone homogène sans rupture, on prendra soin d'isoler le tableau de toute autre information.

Ainsi aucune information ne sera collée au tableau de données.

On laissera toujours une ligne et une colonne vierge autour du tableau.

Si vous ajoutez un filtre automatique et que les flèches ne sont pas positionnées sur les étiquettes, c'est qu'une information est collée au tableau.
Excel ne parvient plus à déterminer où commence et où fini le tableau.

Le tableur ne sait plus où commence le tableau et pose les filtres au mauvais endroit

Le cas des rubriques

Souvent, on rajoute une ligne d'informations au-dessus des étiquettes.

Tableau Excel avec une ligne de catégories

Dans certain cas, cette ligne est problématique.

L'astuce consiste à laisser une ligne vierge entre celle-ci et les étiquettes.

Ligne séparatrice entre le tableau Excel et les catégories

Il ne reste plus qu'à masquer cette ligne vierge pour avoir l'effet visuel attendu.

Masquer la Ligne séparatrice entre le tableau Excel et les catégories

Plusieurs tableaux pour une feuilles

Le mieux si vous avez plusieurs tableaux, c'est quand même de les mettre sur des feuilles différentes.

S'ils doivent être positionnés sur la même feuille, prenez garde de ne pas toucher à la structure de l'un en manipulant l'autre.
Si les tableaux sont positionnés l'un à côté de l'autre, en supprimant une ligne d'un tableau, vous allez aussi supprimer une ligne de l'autre tableau.

Deux tableau Excel cote à cote

Il existe bien sûr, la possibilité d'utiliser les commandes "Décaler les cellules vers le bas" ou "Décaler les cellules vers le haut".
Mais on ne se rappelle pas toujours qu'il y a un autre tableau qui traine au fond de la feuille de calcul.

Deux tableau Excel décalés

Les mises à jour

Quand on fait « grandir » un tableau, deux écueils se présentent.

  • Il faut en général refaire les bordures.
  • Les formules de calculs peuvent ne plus faire référence à la bonne zone.

Microsoft Excel sait régler le problème.

Il faut se rendre dans le menu "Accueil" => "Mettre sous forme de tableau" et transformer la plage.
Mais ce fonctionnement peut être déroutant à bien des égards.

Où insérer ?

Si vous insérer entre deux lignes de données existantes, il n'y a aucun souci.

Insérer une ligne au sein d'un tableau excel

C'est la solution que je recommande.

  • La mise en forme sera reprise.
  • Les formules seront recalculées.

Insérer à la fin du tableau

Il n'y a aucun intérêt à insérer à la fin du tableau.

Avoir l'ordre naturel de saisi ne sert à rien.
Et si vous devez trier des données, il y a forcément une colonne qui le permet (une date, un numéro d'opération, etc.).

Des inconvénients majeurs

Rajout de données à la fin d'un tableau excel
  • Il faut refaire une bonne partie des mises en forme.
  • Les adresses des formules de calculs ne sont pas mises à jour.

Il y a quand même des cas où cela peut s'avérer souhaitable.

  • Vous avez un tableau qui ne possède pas encore de données.
  • Vous souhaitez respecter ce fonctionnement naturel de l'être humain (ergonomie).
  • Vous voulez faire toujours pareil, parce qu'un jour vous allez automatiser la saisie dans les tableaux.

Ligne d'insertion

Prévoir un nombre de lignes

Il est possible de préparer à l'avance un certain nombre de lignes.

J'ai déjà rencontré des limitations avec ce fonctionnement.

  • On arrive à la limite des lignes prévues à l'avance.
  • Au fin fond de la feuille qui sert de trame, il traine de vieilles données que l'on a oublié de supprimer.

Créer une ligne d’insertion

Le plus solide est de ne rajouter qu'une seule ligne vide à la fin du tableau.

Cette ligne est formatée comme les autres.

Elle ne contient aucune données (pas même une formule qui n'affiche rien).

Ligne d'insertion en fin du tableau excel

Cette ligne est incluse chaque fois qu'on utilise une fonctionnalité de Microsoft Excel qui s'adresse à une colonne ou au tableau (comme la validation de données ou les mises en forme conditionnelles).

Elle est aussi sélectionnée dans tous les calculs qui font référence à la plage.
Ainsi quand on insère une ligne à son niveau, les formules se mettent à jour avec la nouvelle adresse.

Rajout de données au niveau de la ligne d'insertion sur le tableur excel

L'inconvénient c'est qu'il faudra insérer à chaque fois une ligne et recopier les formules de calcul.

Structurer l'information

Sur Microsoft Excel, nous gérons des données.
Il se trouve qu'il existe des règles pour les gérer efficacement.

Excel est un logiciel qui génère énormément d'erreurs étant trop permissif.
Elles sont essentiellement dû à des erreurs de saisie et des incohérences d'écriture.

L'atomisation

Une cellule de la feuille contenant trop d'informations

Une cellule ne doit contenir qu'une information et une seule.

La même information répartie en plusieurs cellules
  • Ainsi, il est mieux de réserver une cellule pour le nom et une autre pour le prénom.
  • Le code postal et une ville tirerons bénéfice à être dans deux cellules distinctes.
Réunir l'information est simple, à l'aide de l'opérateur de concaténation

En informatique, il est souvent plus simple, de réunir l'information que de tenter de la dissocier.

Le dédoublonnage

Je rencontre des tableaux où dans une colonne, la même information est écrite plusieurs fois de manière différente.

Il devient impossible d'en tirer des analyses fiables.

Une règle simple

Quand dans une colonne, vous allez saisir plusieurs fois la même valeur, c'est qu'il faut prendre ces données pour en faire un tableau à part où la valeur n'apparait qu'une fois.

Une plage sans doublon

Ensuite, on fait un lien entre les tableaux avec des listes à l'aide du menu "Données" => "Validation de données".

Création d'une liste de validation sur excel

C'est aussi là qu'intervient fréquemment la fonction "RECHERCHEV", pour récupérer des données du tableau source.

Utilisation d'une liste de validation sur excel

Cette organisation a l'avantage d'établir un dialogue entre les deux tableaux.

  • Dans le premier on récupère des informations stockées dans le second.
    Ainsi on s'assure de la cohérence de l'information puisque la même donnée est toujours écrite pareil.
  • Dans le second, on va pouvoir faire des calculs de regroupement avec les informations du premier tableau.

De plus ce second tableau peut être le point de départ à une évolution du projet.
Ainsi dans un tableau des villes, je pourrais rajouter le revenu moyen, la densité de population, etc..
Je m'ouvre la possibilité de faire des analyses plus poussées.

L'unicité

Vos tableaux doivent toujours posséder une colonne avec une information unique (sans doublons).

Si de façon naturelle il n'existe pas une telle colonne, alors créez une colonne ou vous ferez en sorte qu'il n'y a jamais de doublons.
Le plus simple étant un numéro incrémenté de 1 à chaque ligne.

Un tableau avec sa colonne unique

Le but est d'être sûr de faire référence à la bonne ligne quand on lit des tableaux (quand la fonction "RECHERCHEV" trouve deux valeurs identiques, elle fera toujours référence à la première rencontrée).

Du bon usage de Microsoft Excel

En appliquant ces quelques règles qui sont avant tout du bon sens, l’exploitation de vos tableaux s’en trouvera simplifié.

Vous éliminerez les principales causes d’erreurs dans les tableaux Microsoft Excel.

En structurant correctement vos tableaux, l’information sera fiable et cohérente.

>