Un tableau croisé dynamique, ou TCD, est une fonctionnalité d’Excel qui réorganise un jeu de données brutes en tableau synthétique, sans modifier la source. Il permet de regrouper, filtrer et calculer des valeurs selon les axes que l’on choisit, en quelques clics. C’est l’outil central pour passer d’une feuille de calcul volumineuse à une analyse de données lisible.
Modèle de données Excel : relier plusieurs tables sans les fusionner
La plupart des guides expliquent comment créer un TCD à partir d’une seule plage de cellules. Cette approche fonctionne, mais elle oblige souvent à tout rassembler dans une feuille unique, avec des colonnes dupliquées et un risque d’erreurs de copier-coller.
A voir aussi : Puis-je utiliser de l'encre de sublimation dans une imprimante Canon Pixma ?
Excel propose une alternative. En chargeant plusieurs tables dans le Modèle de données, puis en créant des relations entre leurs colonnes-clés, un tableau croisé dynamique peut exploiter des champs issus de tables différentes. La relation repose sur un principe simple : une colonne d’une table (par exemple un identifiant client) correspond à une colonne d’une autre table, selon une logique un-à-plusieurs.
L’intérêt concret : une table contient les commandes, une autre les informations clients, une troisième les produits. Le TCD pioche dans les trois sans qu’on ait eu aux coller côte à côte. La maintenance est plus propre, et les mises à jour ne cassent pas la structure.
A lire également : Comment forcer l'ouverture d'un PDF ?
Comptage distinct dans un tableau croisé dynamique : une subtilité à connaître
Par défaut, la fonction de comptage d’un TCD compte les cellules renseignées. Si un client apparaît dix fois dans la colonne, il sera compté dix fois. Pour obtenir le nombre de valeurs uniques (combien de clients distincts, combien de références produit différentes), il faut activer l’option « Nombre distinct ».
Cette option n’est pas disponible dans tous les cas. Elle nécessite que le TCD soit construit à partir du Modèle de données. Un TCD classique, basé sur une simple plage de cellules, ne la propose pas.
C’est un point à vérifier avant de concevoir un rapport fondé sur des identifiants uniques. Si le comptage distinct est requis, il faut cocher l’option « Ajouter ces données au modèle de données » lors de la création du tableau croisé dynamique.
Power Query et TCD : préparer les données de façon répétable
Le nettoyage des données avant analyse est souvent la partie la plus chronophage. Supprimer des colonnes inutiles, filtrer des lignes incohérentes, reformater des dates : ces opérations manuelles doivent être refaites à chaque nouveau jeu de données.
Power Query enregistre chaque étape de transformation dans une requête. Quand la source de données est mise à jour (un nouveau fichier mensuel, par exemple), il suffit d’actualiser la requête pour que les mêmes traitements s’appliquent automatiquement. Le TCD en aval reçoit alors des données déjà nettoyées, sans intervention manuelle.
En pratique, la combinaison Power Query plus tableau croisé dynamique transforme un processus de reporting ponctuel en chaîne reproductible :
- Power Query importe et nettoie les données brutes selon des règles définies une seule fois (suppression de doublons, filtrage, changement de type de colonnes)
- Le résultat alimente directement le Modèle de données ou une table Excel structurée
- Le TCD, connecté à cette sortie, se met à jour en un clic via le bouton « Actualiser »
Champs calculés et organisation des valeurs dans un TCD Excel
Un tableau croisé dynamique ne se limite pas aux sommes et aux moyennes disponibles par défaut. La zone « Valeurs » accepte des champs calculés, c’est-à-dire des formules personnalisées qui combinent des colonnes existantes. On peut par exemple créer un champ « marge » qui soustrait le coût du prix de vente, directement dans le TCD, sans ajouter de colonne à la source.
L’organisation des champs dans les quatre zones du TCD (Filtres, Colonnes, Lignes, Valeurs) détermine la lecture du résultat. Déplacer un champ de « Lignes » vers « Colonnes » change la perspective d’analyse sans toucher aux données. C’est cette flexibilité qui distingue le TCD d’un tableau figé construit avec des formules SOMME.SI ou NB.SI.
Regroupement par dates dans l’analyse de données
Quand la source contient des dates, Excel peut les regrouper automatiquement par mois, trimestre ou année dans le TCD. Ce regroupement évite de créer des colonnes auxiliaires pour extraire le mois ou l’année. Il suffit de cliquer droit sur une cellule de date dans le tableau croisé dynamique et de sélectionner « Grouper ».
Le regroupement par dates rend visible une tendance qu’une liste de lignes quotidiennes ne permet pas de repérer. Un tableau affichant les ventes par trimestre est plus parlant que le même tableau avec une ligne par jour.
Limites pratiques des tableaux croisés dynamiques sous Excel
Le TCD est un outil d’analyse puissant, mais il fonctionne dans le cadre des contraintes d’Excel. La grille est limitée en nombre de lignes, ce qui peut poser problème avec des jeux de données très volumineux. Dans ce cas, des alternatives comme Power BI ou des bases de données dédiées prennent le relais pour l’analyse.
Un TCD ne se met pas à jour en temps réel. Chaque modification de la source nécessite une actualisation manuelle (ou un rafraîchissement à l’ouverture du fichier, configurable dans les options). Un rapport partagé entre collègues peut donc afficher des données obsolètes si personne n’a cliqué sur « Actualiser ».
- Vérifier le format de la source avant de créer le TCD : chaque colonne doit avoir un en-tête unique, sans lignes vides au milieu des données
- Éviter les cellules fusionnées dans la plage source, qui provoquent des erreurs de lecture
- Préférer le format « Tableau » (Ctrl+T) pour la source, afin que les nouvelles lignes soient automatiquement incluses dans la plage du TCD
Le tableau croisé dynamique reste l’outil le plus direct pour explorer un jeu de données sous Excel sans écrire de formule complexe. Sa vraie force apparaît quand on le combine avec le Modèle de données et Power Query : la préparation devient reproductible, l’analyse couvre plusieurs tables, et le comptage distinct devient accessible. Maîtriser ces trois briques change la façon dont un fichier Excel passe de source brute à rapport exploitable.
