Les tableaux croisés dynamiques, c’est facile !

Le tableau croisé dynamique, souvent appelés TCD (ou Pivot Table en anglais) est une des fonctionnalités les plus puissantes d’Excel. Elle permet de synthétiser des données brutes sous la forme d’un tableau élaboré et personnalisé. Nous allons voir dans cet article que son utilisation est beaucoup plus simple qu’il n’y paraît, et qu’elle s’avère vite indispensable lorsqu’il s’agit d’analyser des masses importantes d’informations avant de prendre une décision.

 

Téléchargement

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

 

 

Tutoriel Vidéo

Partie 1 : Mon TCD en 6 clics (voir le tutoriel)

Partie 2 : Mon TCD enrichi : Champs calculés et Tri de colonne (voir le tutoriel)

Partie 3 : Regrouper des données dans mon TCD (voir le tutoriel)

Partie 4 : Séléctionner les données à afficher dans le TCD (voir le tutoriel)

Partie 5 : Comment utiliser les segments pour filtrer le TCD d'un seul clic (voir le tutoriel)

Partie 6 : Comment utiliser le filtre chronologique pour filtrer une date sur un TCD d'un seul clic (voir le tutoriel)

Partie 7 : Comment regrouper des données de même nature au sein d'un TCD (voir le tutoriel)

Partie 8 : Comment modifier ou ajouter des informations dans un TCD (voir le tutoriel)


1 – Les tableaux croisés dynamiques, pour quoi faire ?

 

Comme nous venons de le voir en préambule, un tableau croisé dynamique permet de représenter sous une forme synthétique et claire une table de données. Les bénéfices apportés par le tableau croisé dynamique sont nombreux :

  • Regroupement de données suivant des catégories,
  • Faire des opérations sur les données (sommes, comptages, …)
  • Automatisation de totaux et de sous-totaux,
  • Mises-en-forme élaborée,
  • Actualisation simplifiée des données sources,
  • Connexion à une base de données externe

 

2 – Votre premier TCD en six clics seulement

TÉLÉCHARGER LE SUPPORT DE CE COURS

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

La création et l’utilisation d’un tableau croisé dynamique suppose que vous disposiez au préalable d’une table de données dans laquelle Excel puisera les données sources qui vont lui permettre d’alimenter le tableau croisé dynamique. La logique des tableaux croisés dynamiques est donc de séparer d’un côté les données en elles-mêmes et d’un autre côté leur présentation :

  • Les données sont saisies sous la forme d’une table des données, c’est-à-dire un simple tableau en une seule dimension dans lequel chaque ligne représente un enregistrement. Pour être correctement utilisée comme source de données du TCD, chaque colonne doit posséder un en-tête unique (titre de colonne), et il ne doit pas y avoir de ligne ou colonne vide pour qu’Excel puisse identifier simplement les limites du tableau de données. Enfin, il ne faut pas mélanger les types de données contenues dans une même colonne, en d’autres termes, si une colonne composée de données numériques contient également des données textuelles, Excel va considérer que l’ensemble de la colonne est de type textuel et de pourra pas effectuer de calcul sur celle-ci.
  • Le TCD qui peut prendre un grand nombre de dimensions et qui ordonne les données puisées dans la table de données.

Pour notre exemple, nous allons partir d’une liste simple qui reprend l’ensemble des ventes réalisés par les commerciaux (nombre de ventes, Chiffre d’affaires réalisé et montant des commissions) :

Excel formation - les tableaux croiss dynamiques c est facile

Nous allons maintenant construire notre premier TCD en six clics seulement :

  • Clic 1 : Sélectionner n’importe quelle cellule du tableau

Excel formation - les tableaux croiss dynamiques c est facile

  • Clics 2 et 3 : Cliquer sur l’onglet « Insérer » dans le Ruban, puis sur « Tableau croisé dynamique » 

Excel formation - les tableaux croiss dynamiques c est facile

  • Clic 4 : Dans la fenêtre de création de tableau dynamique qui s’affiche à l’écran, cliquer simplement sur le bouton « OK ». Si votre table de donnée est correctement construite, c’est-à-dire avec un titre pour chaque colonne, et sans ligne vide, Excel détermine automatiquement la plage de données qui doit lui servir de source pour la construction du TCD (ici la plage $A$1:$E$19). Si ce n’est pas le cas, cliquer sur « Annuler », sélectionner la bonne plage puis cliquer à nouveau sur le bouton « Tableau croisé dynamique » du menu « Insérer ».

Excel formation - les tableaux croiss dynamiques c est facile

Par défaut, Excel va proposer de créer le TCD dans une nouvelle feuille, afin que le TCD n’influe pas avec des données déjà existantes. Pour utiliser la même feuille, cliquer sur « Feuille de calcul existante », puis choisir une cellule à partir de laquelle Excel va venir intégrer le TCD.

  • Clics 5 et 6 : le TCD est maintenant créé, mais il est vide de toute information. Pour le remplir de manière automatique, il suffit simplement de cliquer sur les champs à ajouter, dans la zone « Champs de tableau croisé dynamique » située à droite de l’espace de travail.

Excel formation - les tableaux croiss dynamiques c est facile

De manière totalement transparente, Excel a analysé les données, et a compris que le champs « Commercial » correspond à un libellé de champs, et que le « CA » est l’élément de données à synthétiser. Lors de cette analyse, les valeurs textuelles sont automatiquement insérées dans la zone « Ligne », les dates et les heures dans la zone « Colonne » et enfin, les valeurs numériques sont insérées dans « Valeurs ».

Excel formation - les tableaux croiss dynamiques c est facile

Il est également possible d’aller encore plus vite en utilisant la suggestion de tableaux croisés dynamiques d’Excel. Pour cela, procédez comme nous venons de le voir ci-dessus, mais au niveau du Clic 3, choisissez plutôt le bouton « Tableaux croisés dynamiques ». Excel va alors analyser les données pour proposer une série de tableaux croisés dynamiques prêts à l’emploi. Choisissez celui qui convient le mieux à vos données, puis cliquez sur « OK ».

Excel formation - les tableaux croiss dynamiques c est facile

 

Si vous regardez attentivement le TCD crée, vous constaterez que l’intitulé de la colonne représentant le montant des ventes est « Somme des CA », et non pas simplement « CA ». Cela permet de savoir d’un seul coup d’œil l’opération qu’Excel effectue sur les données. Nous verrons plus bas dans cet article l’ensemble de ces opérations.

 

3 – Pour aller plus loin

TÉLÉCHARGER LE SUPPORT DE CE COURS

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

Le TCD très sommaire que nous venons de construire en seulement quelques clics nous affiche désormais le montant des ventes réalisées par chaque commercial avec une ligne de totalisation. Nous n’avons ici qu’à peine effleuré la puissance de l’outil de création de tableaux croisés dynamiques.

Nous allons maintenant voire comment aller encore plus loin.

Pour commencer, nous désirons pouvoir consulter les ventes réalisées par chaque commercial (comme c’est déjà le cas ici), mais également par pays. Pour cela, il est nécessaire d’utiliser un tableau à deux dimensions (les commerciaux sur les lignes, les pays sur les colonnes).

Pour cela, il convient de procéder de la manière suivante :

  • Mettre le curseur sur le champs « Pays »,
  • Le glisser dans la zone « Colonnes »

Excel formation - les tableaux croiss dynamiques c est facile

Notre TCD présente désormais les ventes réalisées par chaque commercial et par pays, avec les totaux respectifs.

Excel formation - les tableaux croiss dynamiques c est facile

L’objectif à présent est de connaître la marge nette dégagée par chaque commercial. Pour ce faire, il convient de procéder de la manière suivante :

  • Décocher la case « Pays » si elle est encore sélectionnée après l’exemple précédent,
  • Faire glisser le champs « Commission » dans la zone « Valeurs »

Excel formation - les tableaux croiss dynamiques c est facile

  • Une nouvelle colonne apparaît dans le TCD avec le montant des commissions prélevées par chaque commercial 

Excel formation - les tableaux croiss dynamiques c est facile

  • Maintenant nous voulons connaître ce que chaque commercial dégage comme bénéfice en calculant la différence (c’est-à-dire en effectuant la soustraction) entre le montant des ventes réalisées et le montant des commissions que nous allons lui reverser. Pour cela, sélectionner le menu spécial « Analyse » qui apparaît lorsqu’un TCD est sélectionné, puis dans le groupe « Outils », cliquer sur « Calculs » / « Champs, éléments et jeux » / « Champ calculé ». 

Excel formation - les tableaux croiss dynamiques c est facile

  • Dans la nouvelle fenêtre qui apparaît à l’écran, remplir les champs comme montré ci-dessous :
    • Zone Nom : saisir le nom à donner au champs calculé (« Bénéfice »)
    • Zone Formule : saisir la formule désirée, en l’occurrence ici nous voulons connaître la différence entre les ventes réalisées par les commerciaux, et le montant des commissions que nous allons devoir leur reverser (donc la formule « =CA-Commission »). Pour saisir les champs sans erreur, il est possible de double-cliquer sur l’un d’entre eux pour l’insérer automatiquement. Si un champ est constitué de plusieurs mots, il est à encadrer par des apostrophes.
    • Cliquer sur le bouton « OK » 

Excel formation - les tableaux croiss dynamiques c est facile

Voici le résultat auquel vous devriez arriver :

Excel formation - les tableaux croiss dynamiques c est facile

Pour trier les commerciaux en fonction de leur performance, cliquer sur le menu déroulant de l’en-tête « Etiquettes de lignes », puis choisissez « Options de tri supplémentaires… ».

Excel formation - les tableaux croiss dynamiques c est facile

Dans la fenêtre qui s’affiche, choisir l’Option « Descendant (de Z à A) par : », puis dans le menu déroulant sélectionner le champs « Bénéfice » que nous venons juste d’ajouter au TCD, et valider en cliquant sur le bouton « OK »         :

Excel formation - les tableaux croiss dynamiques c est facile

 

4 – Les différents types d’opérations

TÉLÉCHARGER LE SUPPORT DE CE COURS

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

Comme nous l’avons déjà abordé un peu plus haut, par défaut lorsqu’Excel détecte qu’une colonne de données est de type numérique, il va présenter ses données en effectuant une somme. Pour les besoins d’une analyse plus poussée, il peut être utile de réaliser d’autres types d’opérations. Pour cela, il faut effectuer un double-clic sur une en-tête de colonne du TCD pour afficher la fenêtre suivante :

Excel formation - les tableaux croiss dynamiques c est facile

Voici le détail des opérations disponibles :

  • Somme : consiste à additionner toutes les valeurs de la colonne qui répondent aux différents critères du TCD. C’est l’opération la plus courante et celle que nous utilisons dans nos exemples depuis le début,
  • Nombre : consiste à compter le nombre d’enregistrements de la colonne qui répondent aux différents critères du TCD (permet par exemple de connaître le nombre de pays dans lesquels un commercial réalise des affaires),

Excel formation - les tableaux croiss dynamiques c est facile

  • Moyenne : consiste à présenter la moyenne des enregistrements de la colonne qui répondent aux différents critères du TCD (par exemple le CA moyen réalisé par chaque commercial),

Excel formation - les tableaux croiss dynamiques c est facile

  • Max : consiste à présenter l’enregistrement le plus élevé de la colonne en fonction des différents critères du TCD,
  • Min : consiste à présenter l’enregistrement le moins élevé de la colonne en fonction des différents critères du TCD,
  • Produit : consiste à multiplier entre eux l’ensemble des enregistrements de la colonne en fonction des différents critères du TCD. Les nombres représentés peuvent très rapidement atteindre des niveaux très élevés, au point qu’Excel puisse être dans l’impossibilité de les afficher (Excel stock les valeurs numériques sur 15 chiffres uniquement, et arrondi au besoin, voir cet article pour plus d’informations https://www.excelformation.fr/comprendre-les-types-de-cellules-d-excel.html). Ce type de calcul n’a pas grand intérêt dans nos exemples,

Excel formation - les tableaux croiss dynamiques c est facile

  • Les autres opérations disponibles permettent de répondre à des besoins plus pointus (« Chiffre » permet de compter le nombre d’enregistrements comme le ferait l’opération « Nombre » mais en ne considérant que les valeurs numériques, « Ecartype » pour calculer l’écart type d’un échantillon, « Ecartypep » l’écart type d’une population, « Var » la variance d’un échantillon, et enfin « Varp » la variance d’une population).

 

5 – Sélectionner les données

 

   5.1 - Les filtres classiques

 

TÉLÉCHARGER LE SUPPORT DE CE COURS

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

Par défaut tous les enregistrements contenus dans la base de données source sont repris dans le TCD, mais il est très simple de filtrer uniquement des données qui répondent à un ou plusieurs critères.  

Pour insérer un filtre sur les données, il convient de procéder de la manière suivante :

  • Cliquer sur le symbole en forme de triangle situé dans le coin inférieur-droit de la cellule contenant l’en-tête du champ.
  • Dans le menu contextuel qui s’affiche, cocher les valeurs de champs à afficher et décocher les valeurs qui n’ont pas à figurer dans le TCD.

Par exemple, dans l’illustration ci-dessous, nous allons choisir de filtrer les pays pour en exclure les ventes réalisées avec la Belgique.

Excel formation - les tableaux croiss dynamiques c est facile

Pour gagner un peu de temps dans la sélection des éléments, il suffit de sélectionner les élements que l'on souhaite conserver à l'écran (ou exclure), puis à l'aide du clic-droit de la souris, nous choisissons Filtrer > Conserver uniquement les élements sélectionnés :

Excel formation - les tableaux croiss dynamiques c est facile

Il est également possible de mettre en place un filtre sur un champ qui ne fait pas parti du tableau croisé dynamique que nous sommes en train de construire. Pour ce faire, il suffit de faire glisser ce champ dans la zone « Filtres » de la fenêtre de création du tableau croisé dynamique. Vous pouvez alors constater l’apparition d’une nouvelle ligne en haut du TCD avec le champ que vous venez de glisser. Procédez ensuite comme nous l’avons vu juste ci-dessus pour sélectionner les champs qui doivent être affichés et ceux qui doivent rester masqués.

Excel formation - les tableaux croiss dynamiques c est facile

Par défaut, lorsqu’un champ est glissé dans la zone des filtres, un seul champ peut être sélectionné. Pour en choisir plusieurs, cochez la case « Sélectionner plusieurs éléments ».

Excel formation - les tableaux croiss dynamiques c est facile

Pour notre exemple, nous pouvons ainsi simplement choisir de n’afficher que les lignes pour lesquelles un minimum de 3000 ventes a été réalisé.

Excel formation - les tableaux croiss dynamiques c est facile

Au besoin, il est également possible de définir des critères de filtre en fonction de critères pointus :

  • Filtres textuels : présence ou non d'un terme, chaîne commençant par un texte,...

Excel formation - les tableaux croiss dynamiques c est facile

  • Filtres numériques : égal à, inférieur à, compris dans une fourchette,...

Excel formation - les tableaux croiss dynamiques c est facile

À titre d'illustration, imaginons que nous ne souhaitions afficher que les ventes dont le total représente au moins un millions d'euros de chiffre d'affaires :

  • Pour commencer, nous déployons le menu contextuel permettant de personnaliser le filtre des étiquettes de lignes :

Excel formation - les tableaux croiss dynamiques c est facile

  • Dans la fenêtre qui s'affiche, nous pouvons choisir directement de n'afficher que les données dont la valeur totale est strictement supérieur à 1000000 :

Excel formation - les tableaux croiss dynamiques c est facile

  • Puis nous validons en appuyant sur le bouton [OK]

Excel formation - les tableaux croiss dynamiques c est facile

 

   5.2 - Les segments

 

TÉLÉCHARGER LE SUPPORT DE CE COURS

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

Les différentes méthodes de filtres de nous venons de voir à l'instant permettent de personnaliser exactement les informations que l’on souhaite afficher ou non au sein d’un tableau croisé dynamique.

Mais nous allons maintenant découvrir une nouvelle méthode, encore plus pratique à utiliser au quotidien, il s’agit des segments !

Les segments (ou Slicer en anglais) sont des éléments graphiques présentés sous la forme de boutons qui permettant de mettre en place très rapidement des filtres sur les tableaux croisés dynamiques d'un simple clic :

Outre le fait de définir des filtres en quelques secondes seulement, un second intérêt que l’on peut attribuer aux segments, c’est que ceux-ci sont toujours présents à l’écran, ce qui permet de visualiser en coups d’œil seulement les filtres activés ou non, sans avoir besoin de dérouler le moindre menu.

Ici, nous allons nous intéresser exclusivement à l'utilisation des segments dans le cadre d'une mise en place de tableau croisé dynamique, mais sachez que ces derniers peuvent également être utilisés sur des tableaux de données.

Pour insérer un segment directement connecté sur un tableau croisé dynamique, nous allons procéder de la manière suivante :

  • Pour commencer, nous sélectionnons n'importe quelle cellule du tableau croisé dynamique :

  • Puis, nous nous rendons dans le menu Insertion du ruban d'Excel afin de cliquer Segments (présent dans le groupe Filtres) :

  • Dans la fenêtre qui s'affiche à l'écran, nous pouvons sélectionner les champs du tableau croisé dynamique pour les lesquels nous souhaitons qu'un segment soit inséré (après avoir sélectionné les champs correspondants, nous validons en appuyant sur le bouton [OK]). Pour notre exemple, nous souhaitons pouvoir sélectionner rapidement le nom du commercial à afficher, ainsi que celui du pays :

  • Et enfin, lorsque nous validons, les champs sont automatiquement insérés sur la feuille de calcul :

Il est évidemment possible de déplacer et de redimensionner les segments comme nous le souhaitons :

Il est alors possible de :

  • D’appliquer un filtre en cliquant simplement sur l’un des champs :

  • Sélectionner plusieurs champs en maintenant la touche [Ctrl] du clavier enfoncée en même temps que nous cliquons sur les champs  :

  • Il est également possible d’activer le mode de sélection multiple en appuyant sur le premier bouton situé en haut à droite de chaque segment (ou en utilisant le raccourci [Alt]+[s]), ainsi il suffit de cliquer sur un champs pour afficher ou masquer ce dernier :

  • Pour sélectionner plusieurs situé les uns en dessous autres, nous commençons par sélectionner le tout premier, puis nous cliquons sur le dernier, tout en appuyant sur la touche [Maj] du clavier :

  • Comme vous pouvez le voir sur les captures ci-dessus, les boutons permettant de filtrer Charles et Nadège sont grisés. En effet, ces derniers n’ont réalisé aucune vente dans les pays pour lesquels le filtre est activé. Il est tout de même possible de les sélectionner en cliquant sur leur bouton, mais évidemment la ligne du tableau croisé dynamique les représentant ne se sera pas affichée :

  • Il est possible de désactiver tous les filtres en cliquant sur le bouton Effacer le filtre (en haut à droite de chaque segment), ou en utilisant le raccourci [Alt]+[c] :

 

Enfin, il est possible d’aller encore plus loin dans la personnalisation des segments en se rendant dans le menu Outils Segments > Options qui ne s’affiche dans le menu ruban que lorsqu’un segment est sélectionné :

À partir de ce menu nous pouvons par exemple :

  • changer le texte du segment (Légende),
  • modifier l’ordre d’apparition des données (Paramètres des segments),
  • choisir de ne pas afficher les boutons sans données (Paramètres des segments),

  • connecter le segment sur d’autres tableaux croisés dynamiques

  • Modifier l’apparence du segment sélectionné (Styles de segments) :

  • Organiser les segments avec les autres objets de la feuille de calcul : placer au dessus, en dessous, aligner les objets entre eux, les grouper, … (Organiser)

  • En enfin nous pouvons modifier la taille occupée par les boutons au sein du segment (Boutons), et du segment (Segment) :

Enfin, pour supprimer un segment, nous le sélectionnons (en cliquant sur l’une de ces bordure), puis appuyons sur la touche [Suppr] du clavier.

 

   5.3 - Le filtre chronologique (filtre de dates sur une frise)

 

TÉLÉCHARGER LE SUPPORT DE CE COURS

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

Dans la partie précédente de cette formation sur la création de tableaux croisés dynamiques, nous avions découvert comment utiliser les segments pour filtrer très rapidement les données affichées dans le TCD. Cette méthode très puissante n’est en revanche pas adapté à la saisie de dates sur lesquelles les critères peuvent être variés (années, mois, jours, intervalles,…). Mais heureusement, un autre outil dédié au filtre dynamique des dates est directement proposé par Excel, il s’agit de l’outil de filtre chronologique !

Pour illustrer l’utilisation du filtre chronologique, nous allons utiliser une nouvelle base de données, que voici :

Excel formation - TCD 12 - 01

Comme vous pouvez le constater, cette base de données reprend les ventes réalisées par Émily au Japon au cours des cinq dernières années.

Base reprend donc un nombre important de lignes, qu’il peut être intéressant d’analyser sur des périodes définies.

Pour cela, nous créons rapidement un tableau croisé dynamique, que nous choisissons d’insérer directement sur la feuille de calcul :

Excel formation - TCD 12 - 02

Pour insérer le tableau croisé dynamique sur la feuille de calcul en cours, choisissez l’option « Feuille de calcul existante », puis cliquez sur « Emplacement » pour choisir une cellule de la feuille de calcul (ici la cellule F7) :

Excel formation - TCD 12 - 03

Ensuite, nous pouvons valider la création du Tableau croisé dynamique en appuyant sur le bouton [OK] :

Excel formation - TCD 12 - 04

Le tableau croisé dynamique est maintenant inséré juste à côté de la base de données qui reprend les ventes réalisées par Émily au Japon.

Pour alimenter ce TCD en données, nous glissons simplement les champs dans les zones correspondantes depuis le menu Champs de tableau croisé dynamique situé sur la droite de la fenêtre Excel :

  • Glissez le champs Date dans la zone Lignes,

Excel formation - TCD 12 - 05

  • Glissez le champs Quantités dans la zone Valeurs :

Excel formation - TCD 12 - 06

  • Glissez le champs CA dans la zone Valeurs, juste en dessous de Quantités :

Excel formation - TCD 12 - 07

Vous pourrez noter au passage que l’insertion du Champs Date dans la zone ligne a eu pour effet de créer deux champs calculés (Trimestres et Années) afin de regrouper les dates. Vous pouvez choisir de conserver ou de retirer ces regroupements en décochant simplement les cases correspondantes (par exemple ici, nous retirons le regroupement trimestre) :

Excel formation - TCD 12 - 08

Il est également possible de ne récupérer que les dates sans aucun regroupement, pour cela, rendez-vous dans Analyse > Dissocier :

Excel formation - TCD 12 - 09

À présent, nous pouvons en venir au sujet qui nous intéresse : l’insertion d’une chronologie reliée à un tableau croisé dynamique.

Celle-ci est analogue à l’insertion des segments que nous venons de découvrir dans la partie précédente : nous commençons par sélectionner une cellule du tableau croisé dynamique, puis nous nous rendons dans le menu Analyse (qui ne s’affiche que lorsqu’une cellule du tableau croisé dynamique est sélectionnée), puis nous choisissons Insérer une chronologie :

Excel formation - TCD 12 - 10

Ici, nous n’avons qu’un seul champ de type date sur lequel Excel peut insérer la chronologie :

Excel formation - TCD 12 - 11

Enfin, il suffit de valider en appuyant sur le bouton [OK] pour que la chronologie soit effectivement insérée et liée automatiquement au tableau croisé dynamique :

Excel formation - TCD 12 - 12

À présent, il suffit de cliquer à un endroit de la frise chronologique pour filtrer dynamiquement les données du tableau croisé dynamique :

Excel formation - TCD 12 - 13

Vous pouvez alors constater que seuls les ventes du mois de janvier 2019 sont maintenant affichées à l’écran !

Par défaut, chaque intervalle de la frise chronologique correspond à un mois, pour utiliser un espace de temps plus important, il est possible de sélectionner plusieurs intervalles en même temps, en déplaçant les curseurs situés aux extrémités :

Excel formation - TCD 12 - 14

Ici nous avons sélectionné l’intervalle qui correspond aux mois de janvier à mars 2019.

Nous pouvons également sélectionner directement plusieurs intervalles en faisant glisser le cureur au moment de la sélection :

Excel formation - TCD 12 - 15

Pour modifier la période (mensuelle) définie par défaut, nous utilisons le menu déroulant situé au-dessus de la frise, sur la droite :

Excel formation - TCD 12 - 16

Cela nous permet d’afficher les données annuelles, trimestrielles, mensuelles ou encore quotidiennes.

Pour effacer un filtre mis en place, cliquez sur le bouton Effacer le filtre :

Excel formation - TCD 12 - 17

Et pour supprimer complètement le filtre chronologique, sélectionnez ce dernier, puis appuyez sur la touche [Suppr] du clavier (ou clic-droit > Supprimer la chronologie)

Excel formation - TCD 12 - 18

Attention toutefois, comme nous l’avons déjà vu pour la suppression d’un segment, lorsque nous supprimons un filtre chronologique, cela n’efface pas pour autant les filtres mis en place. Pensez à les effacer au préalable !

Tout comme pour les segments également, il est possible de personnaliser l’apparence donnée à la frise chronologique :

  • Il est possible de la déplacer :

Excel formation - TCD 12 - 19

  • Il est possible de la redimensionner :

Excel formation - TCD 12 - 20

  • Il est possible de modifier l’apparence de la chronologie depuis le menu Option :

Excel formation - TCD 12 - 21

Sachez également que depuis le menu Options, nous pouvons également choisir les éléments à afficher ou masquer :

  • Afficher ou masquer l’En-tête
  • Afficher ou masquer l’Étiquette de la sélection
  • Afficher ou masquer la Barre de défilement
  • Afficher ou masquer le Niveau des temps

Excel formation - TCD 12 - 22

Pour finir sur cette partie consacrée au filtre des données via la frise chronologique, sachez qu’il est possible de combiner celle-ci avec segment sur un même champs :

Excel formation - TCD 12 - 23

En revanche, comme vous pouvez le constater sur la capture ci-dessus, ces deux éléments ne sont reliés, ni mis à jour en temps réels pour tenir des changements opérés sur l’un ou l’autre.

 

 

 


excelformation.fr, formation excel, didacticiel excel, excel, tuto, cours, gratuit, formation, débutant, apprendre, tuto excel, comment filtrer les dates dans un tableau croisé dynamique avec l’outil de filtre chronologique sur excel, comment, filtrer, dates, dans, tableau, croisé, dynamique, avec, l’outil, filtre, chronologique, formation sur les tableaux croisés dynamiques, tableaux, croisés, dynamiques, filtrer très rapidement, très, rapidement, tcd, filtre dynamique des dates, filtre chronologique, tableau croisé dynamique, regrouper les dates, regrouper, regroupements, insertion d’une chronologie, insertion, d’une, chronologie, insérer une chronologie, insérer, frise chronologique, frise, filtrer dynamiquement, dynamiquement, effacer le filtre, effacer

 

6 – Grouper les données

TÉLÉCHARGER LE SUPPORT DE CE COURS

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

 

Il peut arriver que nous ayons besoin de grouper certaines données de lignes ou de colonnes les unes avec les autres (par exemple pour réunir les pays qui appartiennent à un même continent les unes avec les autres).

Pour ce faire, commencez par sélectionner les données que vous désirez regrouper (si elles ne sont contigües entre elles, maintenez la touche « Ctrl » du clavier enfoncé). Ensuite, à l’aide du bouton droit de la souris, sélectionnez « Grouper ».

Excel formation - les tableaux croiss dynamiques c est facile

Vous pouvez également utiliser le Ruban (« Analyse » / « Groupe » / « Grouper la sélection »).

Excel formation - les tableaux croiss dynamiques c est facileA présent, les données choisies sont regroupées ensembles. Vous pouvez simplement renommer un groupe en sélectionnant son étiquette et en tapant le nom adéquat.

Excel formation - les tableaux croiss dynamiques c est facile

Pour masquer le détail d’un groupe, cliquez sur le signe « - » devant son étiquette. Pour l’afficher à nouveau, il suffit de cliquer le signe « + ».

Excel formation - les tableaux croiss dynamiques c est facile

 

7 – Insérer de nouveaux enregistrements

TÉLÉCHARGER LE SUPPORT DE CE COURS

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

______________________________________________

 

Comme nous venons juste de le voir, le TCD met en forme des données issues d’une base de données source. Mettre à jour le tableau lors de chaque changement dans cette base demanderait trop de ressources à Excel. Pour ne pas multiplier les ralentissements, Excel duplique les données dans une zone de cache qui nécessitera d’être réactualisée manuellement à chaque fois que des nouvelles informations seront saisies, supprimées ou modifiée. Cette actualisation peut s’effectuer de deux manières différentes :

  • Soit en sélectionnant le bouton « Actualiser » présent dans l’onglet « Analyse » du ruban (cet onglet n’est visible que lorsqu’un tableau croisé dynamique n’est sélectionné, il convient donc au préalable de cliquer sur une des cellules de ce TCD),

Excel formation - les tableaux croiss dynamiques c est facile

  • Soit en sélectionnant ce même bouton « Actualiser », mais cette fois-ci au sein du menu contextuel qui s’affiche lorsque l’on effectue un clique-droit avec le bouton droit de la souris sur une cellule du TCD 

Excel formation - les tableaux croiss dynamiques c est facile

Pour ajouter une ligne de données au TCD, il existe trois façons de faire. Les deux premières sont lourdes et pas vraiment intuitives, nous ne ferons que de les citer, tandis que la troisième est bien plus pratique à utiliser et plus puissante :

  • La première façon de faire consiste à insérer une ligne sous le tableau, puis de redéfinir la plage de cellules à utiliser (onglet « Analyse » du ruban / « Changer la source de données » / « Changer la source de données… »).

Excel formation - les tableaux croiss dynamiques c est facile

Excel formation - les tableaux croiss dynamiques c est facile

  • La seconde façon de faire consiste à insérer une nouvelle ligne au milieu du tableau pour insérer des données (clic-droit sur un libellé de ligne, par exemple le numéro de la ligne « 10 », puis cliquer sur « Insertion »). 

Excel formation - les tableaux croiss dynamiques c est facile

Excel formation - les tableaux croiss dynamiques c est facile

Excel formation - les tableaux croiss dynamiques c est facile

Si les données du tableau suivent un ordre défini, il faudra alors les trier manuellement après avoir insérer cet enregistrement.

  • Enfin, la troisième et meilleure façon de faire consiste à transformer la base de données source en un tableau de données Excel. Pour plus d’informations, je vous invite vivement à consulter cet article qui aborde la notion de tableau de données dans le détail.

Pour transformer notre plage de données en tableau de données Excel, procédez de la manière suivante :

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

Avec cette manière de procéder, il vous suffira d’ajouter un enregistrement sous le tableau pour automatiquement redéfinir la plage de cellule utilisée pour alimenter le tableau croisé dynamique.

 

8 – Mettre en forme le TCD

 

Dans la plupart des cas, la mise-en-forme par défaut conviendra largement. Mais il est évidemment possible de personnaliser complètement son apparence à partir du menu « Création » qui s’affiche dans le ruban lorsqu’un TCD est sélectionné.

Voyons quels en sont les principales options de personnalisations :

  • Sous-totaux : permet d’activer ou non la présence de sous-totaux lorsqu’il y a des changements de catégories,
  • Totaux généraux : permet d’activer ou non la présence des totaux généraux du tableau. Les totaux de lignes s’affichent à droite du tableau, tandis que les totaux des colonnes s’affichent en bas, sur la dernière ligne du tableau,
  • Disposition du rapport : permet de personnaliser le type de tableau voulu, c’est-à-dire sous la « forme compactée » (qui est la forme par défaut sur les dernière versions d’Excel) avec les étiquettes de lignes disposées dans une seule et même colonne, en « mode plan » qui ressemble fortement à la forme compactée, à la différence que chaque étiquette de ligne se retrouve dans une colonne différente et enfin la « forme tabulaire » qui est un tableau plus classique. Pour ces deux derniers modes d’affichage uniquement, il est également possible de choisir de répéter les étiquettes d’éléments, lorsque ceux-ci possèdent plusieurs éléments

La forme compactée :

Excel formation - les tableaux croiss dynamiques c est facile

Le mode plan :

Excel formation - les tableaux croiss dynamiques c est facile

La forme tabulaire

Excel formation - les tableaux croiss dynamiques c est facile

La forme tabulaire avec une répétition des étiquettes d’éléments

Excel formation - les tableaux croiss dynamiques c est facile

 

  • Le groupe « Options de style de tableau croisé dynamique » permet d’automatiser la mise en évidence des en-têtes de lignes et de colonnes du TCD et de choisir changer le fond d’une ligne sur deux (ou d’une colonne sur deux) afin d’en faciliter la lecture,

Excel formation - les tableaux croiss dynamiques c est facile

  • Pour changer rapidement l’apparence du TCD, vous pouvez choisir parmi les nombreux styles proposés par défaut par Excel. Il suffit de les survoler avec le curseur de la souris pour afficher en temps réel un aperçu du rendu sur votre tableau croisé dynamique. 

Excel formation - les tableaux croiss dynamiques c est facile

Il est également possible de créer vos propres styles que vous pourrez réutiliser dans vos autres fichiers Excel, en sélectionnant l’option « Nouveau style de tableau croisé dynamique ».

 

9 – Pour résumer

 

Voici quelques rappels importants concernant la construction des tableaux croisés dynamiques :

  • Un TCD permet de synthétiser et de mettre en forme des données issue d’une table de données,
  • Les colonnes de la table des données doivent toutes posséder un titre, et il ne doit pas y avoir de ligne vide, ni de colonne vide,
  • Utiliser les tableaux de données Excel simplifie l’ajout de données (nouvelles lignes ou nouvelles colonnes) en redéfinissant automatiquement la plage des cellules sources. Sinon, cette plage de cellule sera à modifier manuellement après chaque enregistrement

 

Article initialement publié le 19 mars 2018, puis modifié le 18 novembre 2019



Articles qui pourraient vous intéresser

IF...THEN ou Comment effectuer des tests logiques simples en VBA [#11 FORMATION EXCEL VBA COMPLETE]

Automatisez vos saisies d’informations !

Les tableaux croisés dynamiques, c’est facile !

La notion d’objet dans le langage VBA [#10 FORMATION EXCEL VBA COMPLETE]

Commentaires

Sabsab commented

Bonjour à vous, Merci beaucoup pour ce tuto très bien expliqué. J'ai une question concernant la possibilité de filtrer le TCD uniquement sur les commerciaux ayant vendu à un pays ou plus. Le TCD que vous avez donné comme exemple correspond exactement à un TCD sur lequel je souhaite filtrer les prix des clients en commun à plusieurs pays. Avez-vous une solution pour cette problématique? Merci d'avance

Excelformation.fr commented

Bonjour et merci pour votre message. Je ne suis pas sûr de bien comprendre votre demande, pourriez-vous m'en dire davantage ? Bonne journée, et à bientôt !

Poster un commentaire

Pseudo
Commentaire

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.