Étiquette : TCD

Le tableau croisé dynamique pour les nuls : c’est fini !

Le tableau croisé dynamique pour les nuls : c’est fini !

Ce nouvel article « le tableau croisé dynamique pour les nuls » vient en complément de l’article « Réussir son premier tableau croisé dynamique« . Après avoir lu l’article, vous saurez faire la différence entre les tableaux d’Excel simples et les Tableaux Croisés Dynamiques (TCD) !

Tableau croisé dynamique pour les nuls : Poser les bases

Excel est un tableur. Au départ, il a été conçu pour manipuler des données sous forme de tableaux : des lignes et des colonnes de valeurs.

Qui dit manipuler, dit faire des opérations avec ces données. On peut les compter, les additionner, les soustraire, etc.

Pour cet objectif, Excel propose toute une panoplie de fonctions (J’en ai dénombré 480 sur la page officielle de Microsoft). Avec toutes celles-ci, on pense qu’il doit bien exister une fonction pour chacun de nos besoins.

C’est justement sur ce dernier mot qu’il faut s’arrêter : nos besoins !

Définir ses besoins avant d’expliquer les tableaux croisés dynamiques (pour les nuls et pour les autres !)

Comme une image vaut mieux qu’un long discours, prenons un exemple de tableau. À partir de là, nous pourrons déterminer les différents besoins.

Voici un fichier exemple que vous pouvez récupérer sur le site de Microsoft.

Fichier exempleTCD – Fichier exemple

Vous avez ici des chiffres de ventes par branche, par pays, par produit et par date. Avec toutes ces données, on peut imaginer un bon nombre d’analyses à réaliser…

Commençons par un besoin « simple »

Votre responsable vient vous voir et vous demande : « Peux-tu me faire le top 5 des pays où nous vendons le plus ? ».

À cet instant, votre cerveau commence à entrer en ébullition…

Alors d’abord, il faudrait que je fasse un tri par pays… Ensuite, il faudra que je fasse le total pour chacun d’entre eux. Après je pourrai le noter dans une liste et faire un tri sur le total.

Entre nous, cela fait déjà pas mal de manipulations à faire pour juste « un top 5 », vous ne trouvez pas ?

Excel ne recèlerait pas une fonction pour faire cela en quelques clics ? Vous me voyez venir ? Mais d’abord, continuons avec un besoin plus élaboré.

Et si la demande était plutôt… celle-ci

« J’aurais besoin de l’évolution des ventes par produit, par pays et par mois »

Là, vous vous dîtes que cela va certainement vous prendre des heures de construire manuellement ce type de tableau. Consolider tous les chiffres manuellement prendra du temps.

Même si des fonctions Excel existent pour vous aider à cela, à chaque fois, il faut un peu de temps pour construire votre tableau.

>> Je vous propose d’ailleurs d’essayer par vous-même de répondre à cette demande sans les tableaux croisés dynamiques. Ainsi même les « nuls » (je préfère quand même l’expression « à qui on n’a jamais expliqué comment ça fonctionne ») auront une solution dans cette situation.

Le tableau croisé dynamique aurait toutes les réponses ? (pour les nuls et pour les autres !)

Contrairement au tableau contenant les chiffres de ventes, un tableau croisé dynamique (TCD) ne permet pas de saisir de valeur. Un TCD a BESOIN d’un simple tableau de valeurs pour fonctionner. Il va puiser toutes ses données de ce dernier.

Un TCD sert à faire toutes les consolidations automatiquement. Le mot « dynamique » vient d’ailleurs de là. Derrière le mot « consolidation », il faut comprendre la liste des opérations ci-dessous :

Tableau croisé dynamique pour les nuls - Opérations de consolidationTableau croisé dynamique pour les nuls – Opérations de consolidation

Un autre nom, plus pompeux certes, mais peut-être plus clair, serait « tableau consolidant automatiquement les valeurs d’un autre tableau selon les axes sélectionnés par l’utilisateur ». Finalement, on va s’en tenir à TCD, OK ?

Passer au tableau croisé dynamique pour tous

Récapitulons ce que vous devez avoir appris jusqu’ici :

  1. Un TCD a besoin d’un tableau normal avec des données pour fonctionner.
  2. Un TCD n’est pas modifiable. Son contenu n’est que le résultat d’une consolidation des données du tableau source.
  3. Il faut établir la question à laquelle on souhaite répondre avant de construire un TCD.

Sur ce dernier point, il faut être clair. On ne peut avec un seul tableau répondre à deux questions différentes.

Un tableau croisé dynamique répond à une question. Deux questions = Deux TCD.

Charles Exceller

Déterminer le top 5 des pays où il y a eu le plus de ventse et présenter l’évolution des ventes par produit, par pays et par mois n’est pas la même chose.

Voici donc un nouvel apprentissage : vous devrez construire autant de TCD que vous avez de questions ! Mais, rassurez-vous, Excel sait faire cela parfaitement ! C’est la mémoire de votre ordinateur qui fera défaut avant Excel, je vous l’assure !

Passer de l’idée à l’action : Dessinez votre tableau croisé dynamique !

Avant de commencer à jouer avec l’objet dans Excel, prenez une feuille à côté de vous et représentez la réponse à votre question.

PaysVentes
pays 11’234 €
pays 22’345 €
etc.x’xxx €
Représentation cible de notre futur tableau croisé dynamique

À partir de ce moment, vous êtes prêt à construire votre premier tableau croisé dynamique.

On pourra noter qu’en ligne se trouvent les pays, et les valeurs qui nous intéressent sont les sommes des ventes.

Deuxième question, deuxième représentation :

ProduitPaysMois 1Mois 2Total
Article 1
Pays 112 €23 €35 €
Pays 245 €56 €101 €
Article 2
Représentation cible du futur 2e tableau croisé dynamique

Première observation, ce tableau n’a rien à voir avec le premier. Vous comprenez donc aisément qu’un deuxième TCD est nécessaire pour le représenter.

Vous noterez les éléments suivants :

  1. Le nombre de lignes est déterminé par le nombre de produits puis par le nombre de pays.
  2. Les colonnes contiennent les mois.
  3. Les valeurs représentées sont les sommes des ventes (correspondantes au mois, au produit et au pays donnés).

En représentant ce tableau, vous pourriez vous rendre compte qu’il manque l’indication de l’année. Si vos données contiennent des valeurs sur plusieurs années, les ventes pourraient concerner tous les mois de janvier (toutes années confondues). Ce point doit rester à votre esprit. Attendons de voir comment Excel se comporte.

Créez votre tableau croisé dynamique

Maintenant que vous avez les idées claires sur votre tableau (ou vos tableaux) cible, vous pouvez prendre les rênes des TCDs.

Cliquez sur « Insertion » dans le ruban, puis cliquez sur « Tableau croisé dynamique ».

Tableau croisé dynamique pour les nuls - Insérer votre TCDTableau croisé dynamique pour les nuls – Insérer votre TCD

L’écran suivant apparaît alors :

Tableau croisé dynamique pour les nuls - Sélection des donnéesTCD – Sélection des données

Plusieurs observations sur cet écran :

  1. Excel essaie de détecter automatiquement le tableau qui sera la source de votre TCD. Il peut être juste comme faux. Toujours vérifier.
  2. Sur votre feuille de données, la source de données se trouve encadrée par des pointillés verts (cf. zoom de la capture d’écran). C’est un autre moyen de vérifier que toutes vos données sont correctement sélectionnées.
  3. Par défaut, Excel insérera votre TCD sur une nouvelle feuille de calcul. Ne soyez pas surpris.

Tableau croisé dynamique pour les nuls - Composition de votre feuilleTCD – Composition de votre feuille

Votre nouvelle feuille se composera de trois zones :

  1. À cet emplacement, se trouvera votre tableau résultat. Cette zone ne servira qu’à quelques actions limitées (filtrer et trier).
  2. Ici, vous verrez l’ensemble des colonnes de votre tableau source. Ce sont celles que vous pourrez utiliser pour construire votre TCD. Si une colonne manque à l’appel, vérifier à nouveau votre source, contient-elle bien toutes les colonnes ?
  3. La clé de voûte de votre TCD se trouve ici. Vous construirez ici le contenu des lignes, des colonnes ou des valeurs de votre TCD.

Répondre à notre première question : le top 5 des pays par volume de ventes

Si vous remontez de quelques paragraphes, lorsque je vous ai demandé de dessiner votre tableau cible, j’ai décrit ce tableau d’une manière particulière. J’ai mis l’accent sur les données qui se trouvaient en lignes, en colonnes ou en valeurs.

C’était une manière d’introduire ce qu’il vous reste à faire. Glissez-déposez simplement les colonnes de votre tableau source (en 2) vers les emplacements correspondants (en 3).

Vous obtiendrez pour la première question (le top 5 des pays) le tableau suivant :

Tableau croisé dynamique pour les nuls - Première réponseTCD – Première réponse

Nous avons une petite chance ici, car il n’y a que 5 pays dans la source de données, sinon, quelques clics supplémentaires auraient été nécessaires… 😉

Par ailleurs, j’ai dû mettre en forme les valeurs sous forme monétaires (onglet Accueil, format nombre comptabilité). Souvent, Excel saura reconnaître le bon format des données.

Répondre à notre deuxième question : Les chiffres de ventes des produits, par pays et par mois.

Comme annoncé, vous aurez à insérer un nouveau TCD. Après l’avoir fait, vous aurez à réaliser les opérations suivantes :

  1. Glissez-déposez d’abord les produits, puis les pays en lignes.
  2. Passez ensuite aux ventes vers les valeurs (Sales)
  3. En dernier, placez la date en colonnes.

Vous verrez alors le résultat suivant :

Deuxième réponseTCD – Deuxième réponse

Notez qu’en 4, Excel a reconnu automatiquement que la colonne « Date » contenait effectivement des données de dates (Excel ne se fie qu’au contenu, pas au nom…). Et réalise automatiquement des regroupements par année et par trimestre !

En plus, votre tableau contient des sous-totaux intermédiaires et les totaux par lignes et par colonnes ! Que demander de plus ! Tous ces résultats consolidés en quelques clics…

Merci les tableaux croisés dynamiques ! Pour ceux qui le souhaitent, vous pouvez télécharger le fichier résultat ou encore continuer votre lecture avec « Réussir son premier tableau croisé dynamique« .

Je vous présente également dans cette vidéo un autre exemple d’utilisation des tableaux croisés dynamiques avec les données sur le Covid-19 mis à disposition par le gouvernement via https://data.gouv.fr.

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

[Tuto] Comment créer un graphique croisé dynamique ?

[Tuto] Comment créer un graphique croisé dynamique ?

Un graphique croisé dynamique (ou GCD de son petit nom) est un graphique lié à un tableau croisé dynamique (ou TCD). Il est donc impératif de maîtriser (si ce n’est exceller…!) les TCD. Vous avez deux manières de débuter votre premier graphique croisé dynamique, soit vous partez de vos données brutes, soit vous partez d’un TCD existant. Je vous présenterai dans cet article deux fonctions additionnelles que seuls les GCD et TCD possèdent, mais que je trouve plus intéressantes visuellement avec un graphique croisé dynamique.

Tout comme les TCD, une fois que vous avez goûté aux graphiques croisés dynamiques, vous ne reviendrez plus jamais en arrière !

Introduction

Présenter ses données sous forme d’un graphique est toujours plus intéressant que de les présenter sous forme d’un tableau de valeurs. Les différences entre les valeurs seront visuellement remarquables alors qu’entre plusieurs nombres juxtaposés, cela peut devenir un calvaire. En bref, un dessin vaut mieux qu’un long discours.

Les graphiques croisés dynamiques apportent en plus la capacité de consolider des données automatiquement selon le ou les critères de votre choix. C’est très utile (si ce n’est essentiel) lorsque vos données sont décrites de manière détaillée.

Ainsi, constituer un GCD basé sur une liste de valeurs avec seulement deux colonnes (nom, valeur) aura moins d’intérêt que si votre GCD s’appuie sur une liste de valeurs détaillées (n° de facture, n° de ligne de commande, nom client, ville client, montant HT, article commandé, lieu initial stock, fournisseur article, ville fournisseur, etc.).

Étape 1 : Préparez vos données

Cette étape reste essentielle pour tous les traitements dans Excel. Je reprends ici la même liste que dans mon article sur les TCD :

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 graphique croisé dynamique d’Excel.

Toutefois, pour exploiter les GCD 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. Donnez 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 1bis : Suivez l’exemple par vous-même !

Je vous invite à télécharger ce fichier afin de disposer de votre côté des mêmes données d’illustration. Ainsi, vous devriez être en mesure d’arriver aux mêmes résultats que moi.

L’exemple fourni se base sur les données publiques mises en ligne par le gouvernement français sur le site https://www.data.gouv.fr, en particulier, j’ai utilisé ces données : https://www.data.gouv.fr/fr/datasets/base-de-donnees-de-la-pollution-aerienne-aux-abords-des-ecoles-et-creches-dile-de-france/

Comme il m’a été nécessaire de préparer les données, je vous invite à récupérer le fichier prêt à l’emploi.

Étape 2 : Insérer votre graphique croisé dynamique

Cas 1 : Vous souhaitez insérer directement un graphique croisé dynamique

Dans le ruban, vous cliquez sur “Insertion” puis vous disposerez de l’option “Graphique croisé dynamique” comme indiqué ci-dessous.

Excel - Graphique Croise Dynamique - Insertion

Cas 2 : Vous disposez déjà d’un tableau croisé dynamique

Dans ce cas, vous trouverez, comme présenté sur l’illustration ci-dessous, comment insérer un graphique croisé dynamique lié au tableau croisé dynamique.

Pour cela, vous cliquez préalablement sur votre tableau croisé dynamique afin de faire apparaître le ruban associé, puis vous trouverez le bouton “Graphique croisé dynamique” comme ceci :

Excel - Graphique Croise Dynamique - Insertion - Option 2

Dans les deux cas, vous disposerez d’un tableau croisé dynamique et d’un graphique croisé dynamique.

Votre affichage devrait ressembler à celui-ci :

Excel - Graphique croise dynamique vide

Étape 3 : Posez votre question à Excel !

Habituellement, lorsqu’on insère un graphique dans Excel, un graphique apparaît ! Or, ici, il n’y a rien ! Pourquoi ?

Excel - Graphique Croise Dynamique - Champs - ZonesC’est exactement comme pour les TCD, c’est à vous de poser une question et de sélectionner les colonnes/lignes qui vont permettre d’y répondre.

Vous devrez alors glisser-déposer vos champs disponibles dans la bonne case en bas à droite parmi les quatre zones réservées.

Pour les TCD, nous avions FILTRES, COLONNES, LIGNES et VALEURS. Pour un GCD, vous aurez FILTRES, LÉGENDE, AXE et VALEURS.

Comme je vous l’ai indiqué, un GCD sera toujours lié à un TCD, ainsi, la LÉGENDE correspondra aux COLONNES et l’AXE aux LIGNES.

Il ne reste maintenant qu’à faire une première mise en situation.

Cas pratique : Représentez l’évolution de NO2 par département

Pas de panique, je ne vous demande pas de chercher en dehors des données du fichier que je vous ai préparé !

Il vous faut maintenant sélectionner les champs en les glissant dans la bonne zone réservée.

>>Allez-y, essayez de votre côté !

Petite astuce Excel complémentaire : lorsque vous cochez la case d’un champ de type nombre, Excel vous le place automatiquement dans la zone valeurs et sélectionne également la fonction « Somme ». Si c’est un champ d’un autre type, Excel le placera aussi dans la zone Valeurs, mais utilisera la fonction « Nombre ».

Vous devriez avoir sélectionné les champs suivants :

Excel - GCD - Evolution NO2 par departement

Et vous devriez voir ceci : (j’ai légèrement déplacé le graphique)

Excel - GCD - Evolution NO2 par departement - DATA

Première amélioration à apporter à ce GCD

Les intitulés dans le graphique ne sont pas très lisibles (on ne voit que le texte « Somme de… ». On peut les modifier ! Comme le TCD et le GCD sont totalement liés, tout ce que vous apporterez comme modification à l’un sera automatiquement répercuté sur l’autre.

Je vous propose donc de changer les noms des colonnes qui se présentent dans la table de valeurs. Au lieu de “Somme de NO2_2012”, je saisis simplement “NO2 2012”, et ainsi de suite pour les autres colonnes.

Cela donne maintenant :

Excel - GCD - Evolution NO2 par departement - DATA Amerlioration 1

Tout est à jour automatiquement, sans aucune autre intervention ! Idem pour le graphique et la légende du graphique.

Cas pratique 2 : Représentez l’évolution du NO2 par département et par type d’établissement

Lorsqu’on se met à ajouter des critères de représentation (par xxx et par yyy), il faut bien se poser la question de l’ordre des mots !

Est-ce que vous souhaitez comparer les types d’établissement au sein d’un même département ou bien est-ce que vous souhaitez comparer un même type d’établissement entre les départements ?

C’est cette information qui déterminera l’ordre des champs dans la zone AXE.

La première question donnerait ce résultat :

Excel - GCD - Evolution NO2 par departement et type - DATA

Avec cet ordre des champs :Cas 1 - Ordre type departement

On compare les types d’établissement dans un même département.

La deuxième question donnerait ce résultat :

Excel - GCD - Evolution NO2 par type et departement - DATA

Avec cet ordre des champs :Cas 2 - Ordre type departement

On compare entre les départements le même type d’établissement.

Étape 4 : Triez, filtrez vos graphiques croisés dynamiques

En regardant le graphique ci-dessus, il semble légèrement désordonné. Il faudrait trier par ordre décroissant les résultats selon le taux de NO2 de 2012 et classer les départements selon le même ordre.

Si au passage on retirait les résultats pour les établissements de type “NA”, ce serait mieux (dans le cas présent, cela n’apporte pas d’information significative).

Pour ces actions, selon sa préférence, on pourra faire le tri sur le TCD ou directement sur le graphique croisé dynamique. Les deux étant liés, le résultat sera le même.

Pour le faire depuis le graphique, il faut repérer la petite flèche et explorer ses possibilités.

Découvertes des possibilités offertes

Excel - GCD - Filtrer - Trier depuis graphiquePlusieurs possibilités se présentent à vous :

  1. Filtrer directement les types que vous souhaitez voir ou que vous souhaitez retirer (en cochant ou décochant les choix)
  2. Filtrer sur les étiquettes (comprendre « le type d’établissement »)
  3. Le dernier filtre, celui s’appliquant aux valeurs
    1. Il faudra choisir un des champs valeurs à disposition (NO2_2012 par exemple)
    2. Déterminer un opérateur de comparaison
    3. Déterminer une valeur fixe de comparaison (cela ne peut pas être un champ variable)
  4. Trier simplement par ordre alphabétique (ou alphabétique inverse). Dans le cas présent, ce sera sur l’intitulé du type d’établissement.
  5. Enfin, des “options de tri supplémentaires…”Excel - Graphique Croise Dynamique - Options de tri supplementaires

Allons voir cette dernière option.

On découvre alors qu’il est possible de trier manuellement les types d’établissement ! Si aucun choix automatique ne vous convient, faites celui qui vous arrange !

Tri manuel

Excel - Graphique Croise Dynamique - Tri manuelPour réaliser ce tri manuel, il suffit de sélectionner le champ que l’on souhaite déplacer (ici, le type “NA”). Lorsqu’on le déplace, soudain, Excel montre une barre verte et montre que c’est un ensemble qui va être déplacé et pas uniquement un champ.

On peut alors le positionner après une autre valeur “type” selon votre préférence. Dans l’exemple ci-contre, je le place après “primaire”.

Ascendant par / Descendant par

Dans cette option, vous avez l’ensemble des champs valeurs à disposition. Cela signifie que vous pouvez déterminer l’ordre du tri des types d’établissement selon les valeurs des champs.

Résoudre la question posée

Pour rappel, il s’agit de retirer les “NA” de l’affichage et de trier dans l’ordre descendant selon les valeurs de NO2 de 2012.

Facile !

Il ne faut juste pas oublier de réaliser un filtre sur les types, et le tri sur les départements ! Car oui, ce sont les départements qui doivent être triés à l’intérieur de chaque type…

Excel - Graphique Croise Dynamique - Cas pratique

Étape 5 : Passez aux graphiques croisés dynamiques interactifs !

Ces fonctions supplémentaires qu’apportent les graphiques croisés dynamiques sont de véritables plus qui justifient à eux seuls l’utilisation d’un GCD au lieu d’un graphique standard.

A savoir que ces fonctions sont également disponibles pour les TCD.

Ces deux fonctions sont les segments et la chronologie.

Elles ne sont disponibles que dans les rubans dédiés aux TCD et GCD.

Excel - GCD - Segments et Chronologie

Pour pouvoir insérer une chronologie, il faut disposer d’un champ de type date. Les données sources ne sont pas structurées de manière appropriée pour cela.

Au lieu d’avoir une ligne avec 6 colonnes de données (NO2_2012, NO2_2013, etc.), il aurait fallu disposer de 6 lignes par établissement avec deux colonnes : “Date du relevé” et “Valeur”. Nous aurions pu obtenir tous les graphiques illustrés dans cet article. En prime, nous aurions eu accès à la fonctionnalité “Chronologie”…

La structure de vos données sources est très importante pour pouvoir utiliser Excel à pleine puissance !

Qu’apporte la fonction “Segment” ?

Intrinsèquement, la fonction Segment n’est rien d’autre que l’application d’un filtre sur les données. Toutefois, au lieu d’aller dans les options avancées et de cocher/décocher des cases, vous disposez d’un visuel bien plus explicite.

C’est d’autant plus utile que lorsque vous disposez de plusieurs filtres actifs en même temps, vous pouvez soudain avoir un doute sur les filtres appliqués.

Voyons ce que cela donne de plus près en ajoutant un segment sur les types et les départements :

Excel - GCD - Choix segment

De nouveaux objets font leur apparition à l’écran, les fameux segments.

Excel - GCD - Utilsation segment

Vous pouvez alors filtrer bien plus facilement les données, faire des sélections multiples croisées. Ceci est possible soit en cliquant avec la touche “Ctrl” appuyée, soit en faisant un “Cliqué-glissé-relâché” pour sélectionner directement plusieurs valeurs adjacentes.

Le fichier résultat se trouve ici.

Étape 6 : Donnez-moi votre avis !

Qu’avez-vous pensé de ce tutoriel ? Avez-vous d’autres questions ? Souhaitez-vous en savoir plus ?

Votre avis est important ! Je vous remercie de me laisser un petit mot ci-dessous dans les commentaires.

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.

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.

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… !

>