Les tableaux structurés de données d'Excel

Un tableau de données (ou table de données) sur Excel permet de mettre en forme facilement les données et de pouvoir les exploiter très simplement en ajoutant un grand nombre de fonctionnalités :

• Mise-en-forme enrichie (en-tête, couleur de ligne alternée,…),
• Trie automatique des données,
• Filtre automatique des données,
• Ajout de totaux,
• Création de plages de cellules nommées,…

Partie 1 :

Partie 2 :

Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier en suivant le lien disponible ci-dessous : 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

1. Comment créer un tableau de données

Imaginons un tableau basic, avec une série de données comme ci-dessous :

Pour rendre ce tableau attrayant et pouvoir en tirer facilement des informations, il faudrait :

• Sélectionner l’en-tête du tableau
• Mettre en forme cet en-tête
• Sélectionner tout le tableau
• Mettre un cadre
• Mettre un filtre automatique
• …

Heureusement, grâce aux tableaux de données, il est possible d’arriver au même résultat en trois clics :

• Sélectionnez une cellule du tableau
• Cliquez sur « Insérer » puis « Tableau »
• Dans la fenêtre qui s’affiche, laissez cochée la case « Mon tableau comporte des en-têtes » et cliquez sur le bouton « OK »

Et voilà le résultat :

2. Personnalisation du tableau de données

Pour personnaliser ce tableau, cliquez sur le nouveau menu « Création » qui apparaît maintenant dans le ruban lorsqu’une cellule du tableau est sélectionnée. Dans la zone « Styles de tableau » tout à droite, sélectionnez le style de tableau qui vous plaît. Ces modifications ne sont qu’esthétiques et n’influent en rien sur son fonctionnement.

Il est également possible de mettre la première colonne en valeur, en cliquant simplement sur « Première colonne » dans les « Options de style de tableau ».

3. Exploiter le tableau de données

Maintenant, imaginons que vous vouliez connaître le PIB moyen par habitant. Il suffit pour cela de créer la formule dans la première colonne juste à droite du tableau (D4 dans notre exemple), puis validez :

Vous constaterez au passage tous les changements effectués automatiquement par Excel :

• Les cellules du tableau sont désormais nommées, en fonction de l’en-tête de leur colonne. « =C4/B4 » a ainsi été remplacé par « =[@[PIB (Md d€)]]/[@[Habitants (millions)]] », ce qui est quand même plus explicite. Le « @ » signifie que l’on fait référence à une cellule de la même ligne, sans ce symbole la référence concerne alors toute la colonne (par exemple =SOMME([PIB (Md d€)]) pour connaître la somme des PIBs)
• La formule a été appliquée à l’ensemble des lignes du tableau ! 
• Un en-tête de colonne a été ajouté. Remplacez le « Colonne1 » par « PIB par habitant ».

Pour classer les données en fonction du PIB par habitant, cliquez sur le petit triangle dans l’en-tête puis sur « Trier du plus grand au plus petit ».

Pour modifier l’ensemble des cellules d’une ligne, passez le curseur juste au-dessus de l’en-tête de la colonne pour afficher la flèche noire qui pointe vers le bas, puis cliquez pour sélectionner l’ensemble de la colonne.

Les modifications concerneront ainsi l’ensemble des cellules de la colonne.

4. Ajouter des données au tableau

Pour ajouter une nouvelle ligne de données, saisissez simplement sur la ligne juste en dessous du tableau

Une nouvelle ligne est ainsi créée à la suite du tableau, en reprenant les réglages déjà utilisés, les formules, et les formats des colonnes.

Pour ajouter un total en bas de tableaux, revenez sur l’onglet « Création » du ruban, puis cochez « Ligne Total ». Une ligne est ajoutée tout en bas. Pour modifier le type de total, cliquez sur une cellule de cette ligne, puis sélectionnez le type de total désiré.

Attention, le calcul ajouté automatiquement lors de la création de la ligne des totaux est une somme des cellules de la colonne. Or dans notre exemple, la colonne correspond au calcul de la moyenne, il faut donc aller modifier le total pour que la formule soit la bonne.

Lorsque la ligne des totaux est affichée, la méthode pour créer une nouvelle ligne est légèrement différente. En effet, si vous vous placez sous la ligne des totaux pour créer une nouvelle entrée, cela n’aura pas d’impact sur le tableau. Vous pouvez alors agrandir le tableau manuellement, en tirant le symbole de fin de tableau vers le bas.
La seconde possibilité pour ajouter une ligne est de vous placer sur la dernière ligne de données, (juste au-dessus la ligne des totaux) et d’appuyer sur la touche « Tabulation ». Arrivé en bout de tableau, cela ajoutera une ligne à la suite.

5. Exploiter les données

Imaginons maintenant que nous voulions exploiter ces données pour connaître le PIB moyen des trois pays pour lesquels il est le plus élevé.
Commencez par ajouter une colonne qui fera ressortir le rang de chaque pays, en utilisant la fonction RANG() : 

Il est ainsi possible de calculer le PIB moyen par habitant :
Il suffira ensuite de modifier ou ajouter les données au tableau de données pour que les calculs se mettent automatiquement à jour. Pour créer un graphique à partir des données du tableau, sélectionnez simplement une cellule du tableau de données, puis dans « Insérer », choisissez le type de graphique. Vous constaterez alors qu’automatiquement les données saisies seront utilisées comme source pour la création du graphique.

Chaque changement apporté au tableau de données sera automatiquement appliqué au graphique

6. Trier les données d’un tableau Excel

Maintenant que le tableau de données est créé, nous pouvons constater que chaque en-tête de colonne est orné d’un bouton permettant de mettre en place un tri ou un filtre rapidement :

Nous pouvons alors cliquer sur celui-ci pour voir toutes les options disponibles.

De cette manière, nous pouvons très simplement organiser les données par ordre alphabétique (ou par ordre alphabétique inversé) :

Il suffit alors de cliquer sur l’une des deux options correspondantes pour réorganiser toutes les données :

Comme nous pouvons alors le constater, le bouton a changé d’apparence afin de pouvoir identifier rapidement qu’un tri a été mis en place.

Celui-ci indique également le sens du tri, la flèche est en effet inversée si le tri est inversé :

Si la colonne contient des valeurs numériques, le tri se fera en suivant un ordre croissant (du plus petit au plus grand), ou décroissant (du plus grand au plus petit) :

Si celle-ci contient des dates, le tri se fera cette fois-ci de la date la plus ancienne vers la plus récente, ou à l’inverses de la date la plus récente vers la plus ancienne :

Enfin, comme nous pouvons le voir sur les captures précédentes, nous pouvons également mettre en place un tri en fonction de la couleur des cellules.

Pour cela, nous commençons par sélectionner quelques cellules au hasard dans une colonne pour en modifier la couleur de fond :

Cela étant fait, nous pouvons déployer le menu de tri et filtre et choisir l’option Trier par couleur et choisir la couleur correspondante :

Les cellules dont la couleur de fond correspond au rouge seront ainsi affichées en début de liste :

Nous pouvons également sélectionner Aucun remplissage pour que les cellules sans couleur de fond soient insérées en début de liste.

Si nous souhaitons que ce soit non pas la couleur de fond de la cellule qui soit pris en compte, mais par exemple la couleur de police, nous choisiront plutôt Tri personnalisé :

De cette manière, nous pourrons ainsi choisir d’utiliser la couleur de la police comme critère de tri :

Pour information, sachez que depuis cette fenêtre il est possible de mettre en place un tri assez complexe (tri sur plusieurs niveaux, …). Pour en savoir plus sur les options de tri complexes, vous pouvez consulter ce tutoriel.

7. Filtrer les données

Maintenant que nous avons vu la notion de tri, découvrons la notion de filtre, qui va permettre de sélectionner les lignes du tableau afficher et celles à masquer.

Tout comme pour les tris, nous allons retrouver plusieurs types de filtres, directement au niveau de la fenêtre de paramétrage dédiée, en cliquant sur l’icone présente au niveau des en-têtes :

En premier lieu, nous pouvons par exemple filtrer les données en sélectionnant celles que nous souhaitons conserver en cliquant sur les cases de la liste des champs :

Lorsque nous cliquons sur « (Sélectionner tout) », les options qui était alors sélectionnées vont se désélectionner automatiquement, et il suffira alors de cliquer un nouveau sur les champs que nous voulons conserver à l'écran :

Nous pouvons également effectuer une recherche pour retrouver rapidement une valeur dans la liste :

Lorsque nous avons sélectionné les valeurs à afficher, nous pouvons valider le filtre en appuyant sur le bouton OK.

De cette manière toutes les options qui n'ont pas été cochée vont être masquées automatiquement, afin de laisser uniquement celles que nous avons sélectionnées.

Nous pouvons également utiliser les options de filtres avancé, en choisissant par exemple que les lignes affichées soient égales :

• À un texte que nous allons définir (« Est égal à… »),
• Au contraire, qu'elle soit absolument différente de ce texte (« Est différent de… »),
• Les valeurs doivent commencer par le texte que nous allons saisir (« Commence par… »),
• À l’inverse qu’elles se terminent par ce texte (« Ne contient pas… »),
• Encore qu'elles contiennent ce texte (« Contient… »),
• Ou alors enfin qu’elle ne la contienne pas (« Ne contient pas… »)

Si nous voulons aller plus loin dans la notion de filtre nous pouvons cliquer sur la dernière option de ce menu « Filtre personnalisé… »

À ce moment-là nous allons retrouver le filtre que nous avions mis en place juste avant, à savoir que nous allons sélectionner uniquement les cellules qui sont égales à Auclair ou alors Audibert.

Ici, l’option « Ou » est sélectionnée, ce qui signifie que seule l’une des deux options doit être validée pour être affichée.

Au contraire de l’option « Et » qui signifie que seules les lignes qui valident en même temp les deux options seront conservées à l’affichage. (Dans ce cas-là, les deux filtres sont définis sur « est égal à », nous n'aurons par conséquent aucune ligne d’affichée, les valeurs ne pouvant pas être égales à deux valeurs différentes).

Si nous déroulons les différentes options de filtrage, nous allons retrouver les options que nous avons vu encore une fois juste avant :

Comme pour le tri, nous allons pouvoir sélectionner des données à afficher en fonction de critères numériques.

Par exemple en choisissant que les données à afficher à l'écran soient supérieures à 50000 :

De de cette manière nous n'aurons plus que les codes postaux dont le numéro est supérieur à 50000 :

Si les données comportent des dates, nous pouvons également mettre en place un filtre sur ces dernières :

Nous allons par exemple pouvoir filtrer uniquement les dates de naissance des personnes nées dans les années 80, en choisissant le filtre « Entre », puis en définissant une date qui est « postérieure ou égale » au 1er janvier 1980 et qui en plus de cela soit « antérieure ou égale » au 31 décembre 1989 :

Ensuite nous validons en appuyant sur OK.

Excel affiche alors uniquement les dates qui sont comprises entre les deux dates que nous avons spécifiées.

Pour finir nous pouvons également mettre en place un filtre en fonction d'une couleur :

Ainsi, seules les cellules dont le fond correspond à la couleur sélectionnée seront affichées, les autres cellules seront en effet masquées :

Deux petites astuces avant de nous quitter :

• il est facile de savoir si un filtre est activé au sein du tableau, car dans ce cas là les numéros des lignes filtrées sont affichés en bleu au lieu du noir traditionnel :

• Pour désactiver tous les filtres présents sur une feuille en un clic, nous pouvons nous rendre dans le menu Données, puis en cliquant sur le bouton Effacer :

Article initialement publié le 1er décembre 2017 et actualisé le 8 février 2021