Excel formation - Page de vente TCd - 11 Découvrez la méthode complète pour analyser les données efficacement et sans effort avec les tableaux croisés dynamiques, en cliquant ici

Les tableaux 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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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

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

Excel formation - les tableaux de donnees d excel

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 »

Excel formation - les tableaux de donnees d excel

Et voilà le résultat :

Excel formation - les tableaux de donnees d excel

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.

Excel formation - les tableaux de donnees d excel
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 :

Excel formation - les tableaux de donnees d excel
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 ».

Excel formation - les tableaux de donnees d excel

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.

Excel formation - les tableaux de donnees d excel
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

Excel formation - les tableaux de donnees d excel

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é.

Excel formation - les tableaux de donnees d excelAttention, 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.
Excel formation - les tableaux de donnees d excel
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.
Excel formation - les tableaux de donnees d excelLa 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.
Excel formation - les tableaux de donnees d excel

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() : 
Excel formation - les tableaux de donnees d excel
Il est ainsi possible de calculer le PIB moyen par habitant :
Excel formation - les tableaux de donnees d excelIl 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.
Excel formation - les tableaux de donnees d excel
Chaque changement apporté au tableau de données sera automatiquement appliqué au graphique
Excel formation - les tableaux de donnees d excel

 

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 :

Excel formation - tri et filtre tableau de données excel - 05

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é) :

Excel formation - tri et filtre tableau de données excel - 06

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

Excel formation - tri et filtre tableau de données excel - 07

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é :

Excel formation - tri et filtre tableau de données excel - 08

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) :

Excel formation - tri et filtre tableau de données excel - 09

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 :

Excel formation - tri et filtre tableau de données excel - 10

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 :

Excel formation - tri et filtre tableau de données excel - 11

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 :

Excel formation - tri et filtre tableau de données excel - 12

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

Excel formation - tri et filtre tableau de données excel - 13

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é :

Excel formation - tri et filtre tableau de données excel - 14

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

Excel formation - tri et filtre tableau de données excel - 15

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 :

Excel formation - tri et filtre tableau de données excel - 16

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 :

Excel formation - tri et filtre tableau de données excel - 17

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 :

Excel formation - tri et filtre tableau de données excel - 18

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

Excel formation - tri et filtre tableau de données excel - 19

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.

Excel formation - tri et filtre tableau de données excel - 20

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

Excel formation - tri et filtre tableau de données excel - 21

  • À 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é… »

Excel formation - tri et filtre tableau de données excel - 22 À 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 :

Excel formation - tri et filtre tableau de données excel - 23

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 :

Excel formation - tri et filtre tableau de données excel - 24

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

Excel formation - tri et filtre tableau de données excel - 25

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

Excel formation - tri et filtre tableau de données excel - 26

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 :

Excel formation - tri et filtre tableau de données excel - 27

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 :

Excel formation - tri et filtre tableau de données excel - 28

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

Excel formation - tri et filtre tableau de données excel - 29

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 :

Excel formation - tri et filtre tableau de données excel - 30

  • 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 :

Excel formation - tri et filtre tableau de données excel - 31

 

 

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



Articles qui pourraient vous intéresser

Les 6 bonnes habitudes qui facilitent le développement en VBA sur Excel
Les tableaux de données d'Excel
Comment créer nos propres objets VBA avec les classes personnalisées [#30 FORMATION EXCEL VBA COMPLETE]
Comment lancer des macros automatiquement avec les évènements VBA : Les objets Workbook [#31 FORMATION EXCEL VBA COMPLETE]
Comprendre et corriger les erreurs de VBA [#29 FORMATION EXCEL VBA COMPLETE]
Comment créer des commentaires de pro sur Excel
Créons nos propres types de variables en VBA [#11 FORMATION EXCEL VBA COMPLETE]
Les tableaux de VBA [#27 FORMATION EXCEL VBA COMPLETE]
COMMENT UTILISER LES FONCTIONS HEURE, MINUTE, SECONDE ET TEMPS D’EXCEL
Les tableaux croisés dynamiques, c’est facile !
Manipuler les feuilles de calculs en VBA avec l’objet Worksheet [#26 FORMATION EXCEL VBA COMPLETE]
Découvrons les commentaires de VBA [#20 FORMATION EXCEL VBA COMPLETE]

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.