Réussir son premier tableau croisé dynamique avec Excel !

Réussir son premier tableau croisé dynamique avec Excel !

Tous les débutants ont entendu parler du fameux “Tableau croisé dynamique” dans Excel (TCD pour les intimes, et Pivot Table pour les anglosaxons). Je vous propose dans ce tutoriel de vous initier et vous accompagner dans vos premiers pas. D’autres articles suivront pour aller le plus loin possible.

Menu Insertion - Tableau croisé dynamique

Introduction

Excel est connu pour permettre de stocker des listes sans fin (enfin si, 1’048’576 lignes et 16’384 colonnes exactement). Une fois ces données saisies ou importées, on peut les mettre dans des tableaux, les filtrer, les mettre en forme, faire toutes sortes de calculs. Et c’est déjà pas mal.

Jusque là, Excel fait un travail de tableur tout à fait honorable. Mais il n’aurait sans doute pas eu le même succès sans les fonctions d’analyse comme la fonction « tableau croisé dynamique » (par la suite, j’utiliserai son petit nom : TCD).

Une fois que vous y avez goûté, que vous avez compris la puissance que vous pouviez en tirer, il n’y a rien à faire, dès que vous voyez un tableau de données, vous ne pensez qu’à ça… Alors, allons-y !

Étape 0 : À quoi ça sert un TCD (Tableau Croisé Dynamique) dans Excel ?

J’aurais bien envie de vous dire “A croiser les données !”, mais ce serait un peu léger comme explication…

Cela sert à faire des résumés consolidés de vos données. Qui dit résumé, dit qu’il faut de la matière au départ. Et ici, notre matière, ce sont les lignes de données d’un tableau. Selon votre contexte et votre usage, cela peut être des listes de factures ou de commandes, des listes d’articles, de projets, de résultats sportifs ou toute autre liste.

En général, chacune des lignes de valeur dispose d’une information du type « catégorie”, “lieu”, “date”, etc. Et on se pose quelques questions sur cette masse d’information, par exemple :

  1. Quelle est la somme des factures par pays ?
  2. Combien d’articles ont été vendus par mois ?

Sans le tableau croisé dynamique d’Excel, vous êtes condamnés à faire de nombreuses manipulations !

Et vous devrez faire ceci pour chacun des résultats recherchés, et, selon votre activité et vos besoins, recommencer tous les mois, ou pire, toutes les semaines ou même tous les jours !

Alors qu’un TCD vous donnera l’information en quelques secondes, le temps de quelques clics. En plus, vous pourrez simplement rafraîchir le résultat pour que toutes les valeurs se mettent à jour automatiquement.

Désormais, vous êtes prêt à aller plus loin, passons à la première étape.

Si vous pensez que vous avez besoin de plus d’explications, j’ai rédigé un second article qui détaille encore plus les objectifs d’un tableau croisé dynamique. Je l’ai humblement nommé le tableau croisé dynamique pour les nuls.

Vous pourrez ensuite reprendre le fil de cet article.

Étape 1 : Préparez vos données

Excel est un outil qui demande de la rigueur ! Il y a des règles à respecter afin que l’outil vous donne tout ce qu’il a dans le ventre. Je vous rassure, cette préparation n’a rien de très surprenant, et par la suite, vous transformerez ces bonnes habitudes en réflexes.

Les personnes qui ont cet article ont aussi lu :  Le tableau croisé dynamique pour les nuls : c’est fini !

Voici la liste des actions minimales et obligatoires de préparation :

  1. Chaque colonne de votre tableau doit posséder un intitulé
  2. Chaque intitulé doit être unique
  3. Votre tableau ne contient pas de lignes ou de colonnes fusionnées
  4. Des données sont présentes à toutes les lignes (pas de saut de ligne pour aérer ou “faire joli”)

La liste des actions supplémentaires suivantes n’est pas obligatoire pour débuter avec la fonction tableau croisé dynamique d’Excel.

Toutefois, pour exploiter les TCD au mieux, je vous les recommande :

  1. Pour les valeurs vides, laissez le champ réellement vide et n’écrivez pas N/A par exemple
  2. Respectez un formatage homogène des dates et reconnu par Excel en tant que date
  3. Donner des noms de colonnes qui sont parlants pour vous sur la nature des contenus
  4. Soignez la saisie des valeurs textuelles en restant homogène (“Paris”, “paris”, “PARIS” ne sont pas homogènes)

Étape 2 : Vous êtes prêt à créer votre Tableau Croisé Dynamique

Je vous propose d’utiliser les données publiques de la liste des localisations des musées de France. Nous chercherons à trouver le nombre de musées par région et par département.

Une fois le fichier téléchargé, enregistré sur votre ordinateur et ouvert dans Excel, vous pouvez poursuivre.

Dans l’onglet Insertion, cliquez sur “Tableau croisé dynamique” :

Menu Insertion - Tableau croisé dynamique

La fenêtre suivante apparaît :

Créer un tableau croisé dynamique

Si vous curseur est positionné sur une des valeurs de votre tableau, Excel le détecte et présélectionne l’ensemble des colonnes et lignes de votre tableau. C’est à ce moment que l’absence de lignes vides est essentielle. Car Excel peut identifier cette ligne vide comme une indication de fin du tableau.

Pour vos premiers pas, je vous invite à vérifier que l’ensemble du tableau est correctement présélectionné en déplaçant les ascenseurs verticaux et horizontaux avec la fenêtre de création du TCD toujours ouverte.

Si tout est correctement sélectionné, vous pouvez valider en cliquant sur OK.

Et si vous avez bien suivi le pas-à-pas, vous vous serez rendu compte de la présence d’une ligne blanche dans le fichier des musées !

Menu Insertion - TCD - Plage de sélection

Ce qui aurait totalement induit en erreur les résultats d’analyse ! Ce qui m’a également interpelé, c’est le nombre de lignes de la sélection par défaut ($A$1:$O624) qui indique une sélection de la ligne 1 à la ligne 624, hors le fichier dispose de 1254 lignes ! Il faut donc supprimer la ligne vide et recommencer.

Cette fois-ci, la sélection est correcte :

Menu Insertion - TCD - Plage de sélection ajustée

Et voilà ! C’est fait ! “Mais, il n’y a rien !” me direz-vous. Eh oui, c’est maintenant que tout commence.

Tableau croisé dynamique - vide

Étape 3 : Petite explication de ce que vous avez devant vous

Vous avez devant vous un tableau qui est prêt à consolider les données de votre tableau et à répondre à toutes vos questions.

Sur chacun des emplacements du tableau qui est devant vous, vous aller pouvoir placer les colonnes du tableau, et selon le lieu de dépôt, les conséquences seront différentes.

Déterminer les LIGNES

Commençons par “LIGNES”, en bas à gauche. Lors du dépôt d’une des colonnes, le TCD va analyser le contenu du tableau initial de valeur et présenter une liste de valeurs textuelles. Celle-ci contiendra très exactement toutes les valeurs du tableau initial de manière unique.

TCD - lignes

L’objectif est de faire des regroupements. Ainsi, pour le champ “NEW REGIONS”, le nom de la région ne sera indiqué qu’une seule fois, même si celui-ci est répété sur 100 lignes différentes dans la source de données.

Déterminer les VALEURS

Continuons en bas à droite : “VALEURS”. Lors du dépôt d’une colonne à cet emplacement, le TCD va chercher à présenter un résultat numérique, et ce, qu’elle que soit la nature des données. Même avec le champ “NEW REGIONS”, un TCD cherchera un nombre.

Les personnes qui ont cet article ont aussi lu :  [SÉCURITÉ] Méfiez-vous des fichiers au format XLS !

TCD - valeurs

Pour les champs contenant des nombres, par défaut, il calculera une somme. Pour tous les autres types de champ (texte, date, …), il calculera le nombre de valeurs total.

Ainsi, si nous combinons ces deux premiers axes, nous pourrons disposer du nombre de musées par région. Il faut ici “glisser-déposer” le champ “NOM DU MUSEE” dans le bloc « VALEURS ».

TCD - valeurs complétées

Déterminer les COLONNES

Ensuite, remontons en haut à droite sur “COLONNES”. La finalité est identique à “LIGNES”, mais à partir de colonnes. Soigneusement combinées à “LIGNES”, vous pourrez découper vos résultats selon un autre axe d’analyse (à condition que l’attribut sélectionné soit pertinent). Dans l’exemple présent, il n’y aurait aucun intérêt à ventiler le résultat par département. En effet, chaque département n’appartient qu’à une seule région, et donc aucune consolidation de chiffres possibles.

Si nous avions eu accès au tarif d’entrée des musées, alors, on aurait pu mettre le prix en colonne. Ainsi, nous aurions vu le nombre de musée à un prix donné, et ce, région par région.

Déterminer les FILTRES

Enfin, en haut à gauche, nous avons “FILTRES”. Le dépôt d’une colonne ici permettra, comme le nom le sous-entend, de filtrer selon les valeurs à analyser du tableau initial.

Ainsi, si on ne souhaite pas voir certaines catégories de produit polluer les résultats, nous n’avons pas besoin de les supprimer du tableau initial.

Étape 4 : Construisez le Tableau Croisé Dynamique qui vous correspond

Avant d’aller plus loin, vous devez être au clair avec la question à laquelle vous souhaitez répondre. Vous recherchez le nombre de musées “par région et par département” (1) ou bien, “par département et par région” (2) ?

Si vous ne voyez pas la différence entre ces deux questions, voici une illustration :

TCD - musées par région puis département

Tableau croisé dynamique - musées par département puis région

Vous pouvez voir que c’est légèrement différent. Le total général est identique, c’est normal, ce sont les mêmes données. En revanche, les valeurs n’ont pas été regroupées de la même manière.

Bravo ! Vous avez parfaitement franchi toutes les étapes qui vous mène à la maîtrise des TCD. Dans de futurs articles, je développerai bien d’autres possibilités offertes par les TCD.

Je vous propose également cette vidéo pour illustrer l’utilisation des tableaux croisés dynamiques avec l’analyse des données publiquement disponibles sur le Covid via le site https://data.gouv.fr :

https://www.youtube.com/watch?v=H0iao5mAtcs

Étape 5 : Aller plus loin avec les GCD : Les graphiques croisés dynamiques

Maintenant que vous maîtrisez les TCD, vous pouvez aller explorer encore plus de possibilités avec les GCD ! Les graphiques croisés dynamiques. Ces derniers sont les graphiques liés aux TCD. Pour tout savoir sur les graphiques croisés dynamiques, suivez le guide !

Étape 6 : Avez-vous apprécié cet article ?

Votre avis m’aide à continuer ! Je vous remercie de commenter cet article si cela a été le cas, d’ailleurs, si cela ne l’a pas été aussi. Sinon, je ne le saurai jamais… !

Partager l'article
  • Ménager dit :

    Merci pour votre aide et retour

    Je vais essayer

    Bien à vous

    Laurence ménager

  • Bonjour Laurence,
    La plus grande probabilité, c’est que la source de votre TCD ne s’est pas adaptée à l’ensemble de vos données.
    Le plus simple est d’utiliser l’objet tableau dans Excel (Accueil > Mettre sous forme de tableau) et d’utiliser ce tableau comme source de votre TCD. Ainsi, l’ajout de vos données sera automatiquement pris en compte par le TCD.
    Sinon, vous pouvez toujours étendre la source de votre TCD :
    Onglet « Analyse du tableau croisé dynamique > Changer la source de données ».

  • Laurence Ménager dit :

    Bonjour, merci pour ces explications très claires, j’ai une question : j’ai fait un TCD par rapport à un tableau source que je met tous les jours à jours ( je rajoute des lignes) comment faire pour actualiser mon TCD il ne s’actualise pas même si je le fais avec l’onglet « actualiser tout » merci pour votre aide et retour.

  • Méthode dit :

    Merci beaucoup pour votre cours maintenant je parviens à réussir facilement le TCD il me reste à pratiquer le GCD

  • Clair et applicable rapidement, merci

  • Merci Marie ! Content que cela vous ai servi à ce point !

  • Testé et adopté! Merci ! Au vu du nombre de TCD que je fais, je viens de gagner en efficacité. TOP !

  • Lorsqu’on insère un TCD (et que notre curseur se trouve dans une des cellules), un onglet « Création » apparaît dans les menus.
    Là, se trouvent plusieurs propositions par défaut pour mettre en couleur.

    Il faut bien penser à utiliser la petite flèche vers le bas pour voir la liste des propositions.
    Je n’utilise que les options déjà existante.

    De plus, si besoin, il y a tout en bas l’option « Nouveau style de tableau croisé dynamique ». C’est cette option qu’il faut utiliser et non les mises en formes conditionnelles (MFC). En effet, avec des MFC, on ne s’en sortirait pas… entre les lignes de totaux, les bandes, etc.

    Dans les options du menu création, il y a également plusieurs options à explorer sur l’ajout de bandes, des sous-totaux, de la disposition en mode tabulaire ou plan, etc… En écrivant ces lignes, je vois là un article complet qui pourrait être dédié à ce sujet !

    Tenez-moi informé si ces premiers éléments ont déjà résolus votre demande !

  • C’est très clair. Pour ma part, j’en fais énormément. Ma seule question … mes TCD sont très moches et je les « relooke » manuellement. Les vôtres sont automatiquement remaniés… vous utilisez des mises en forme conditionnelles?

  • Très clair. Merci

  • Merci, j’y vois plus clair, je pense que la meilleure compréhension va passer dans l’utilisation quotidienne …

  • Merci beaucoup pour ce tuto qui m’a été très utile !

  • >

    TÉLÉCHARGEZ le Livre PDF - Et si vous arrêtiez de ne pas gagner du temps ?

    "Les 12 fonctions Excel à maîtriser absolument !"