TCD Excel avancé : consolider vos bases et transformer vos tableaux croisés en reporting

Le TCD Excel avancé désigne l’ensemble des techniques qui transforment un tableau croisé dynamique de synthèse en outil de reporting complet : consolidation de plusieurs bases, graphiques croisés dynamiques, styles personnalisés, mise en forme conditionnelle qui suit les données et impression maîtrisée. C’est ce qui sépare un TCD d’exploration d’un rapport que l’on diffuse chaque mois sans retouche.

Ce guide suppose que vous savez déjà créer un tableau croisé dynamique, placer des champs dans les quatre zones et ajouter un segment. Si ce n’est pas encore le cas, commencez par notre tutoriel complet sur le tableau croisé dynamique Excel. Ici, nous partons de ce socle pour construire un reporting propre, automatisé et lisible par votre direction.

Qu’est-ce qu’un TCD Excel avancé ?

Un TCD avancé est un tableau croisé dynamique conçu pour être diffusé et mis à jour régulièrement. Il s’appuie sur des sources consolidées et fiables, utilise des calculs robustes (champs calculés ou mesures DAX), et s’accompagne de graphiques croisés, d’une mise en forme stable et de paramètres d’impression ou de partage définis une fois pour toutes.

La différence n’est pas une question de fonction cachée. Elle tient à la façon de construire le fichier : on pense dès le départ à la mise à jour du mois suivant, à la personne qui lira le rapport et au support (écran, réunion, PDF, papier). Un TCD d’exploration peut être brouillon ; un TCD de reporting doit donner le même résultat, présenté de la même manière, à chaque actualisation.

À quoi sert le reporting Excel avec des TCD ?

Le reporting Excel consiste à produire à intervalles réguliers les mêmes indicateurs pour suivre une activité. Les tableaux croisés dynamiques s’y prêtent particulièrement bien parce qu’ils se recalculent sur de nouvelles données sans reconstruction. Quelques usages typiques :

• Contrôle de gestion : chiffre d’affaires, marge et écarts au budget par centre de coût, consolidés à partir de plusieurs fichiers d’entités.
• Direction commerciale : ventes par commercial, par région et par gamme, comparées à l’année précédente, avec un graphique par équipe.
• Ressources humaines : effectifs, absences et heures supplémentaires par service, imprimés service par service.
• Logistique et achats : volumes par fournisseur, délais moyens, ruptures, suivis chaque semaine.
• Service client : tickets par canal, par priorité et par délai de traitement, présentés en réunion d’équipe.

Dans tous ces cas, le gain vient moins de la création du premier rapport que de la répétition : une fois la chaîne en place, la mise à jour se résume à déposer les nouveaux fichiers et à cliquer sur Actualiser tout.

Comment construire un reporting avec un TCD Excel avancé : les étapes

Prenons un cas concret : une entreprise dispose d’un fichier de ventes par région (Nord, Sud, Est, Ouest), chacun avec les mêmes colonnes Date, Commercial, Produit, Quantité, Chiffre d’affaires et Coût. L’objectif est d’obtenir un reporting mensuel unique.

Étape 1 — Consolider des données Excel issues de plusieurs bases

Pour consolider des données Excel avant un TCD, trois méthodes existent. Elles ne se valent pas.

Méthode 1 : Power Query (recommandée). Mettez chaque base sous forme de tableau (Ctrl+T), puis pour chacune : Données > À partir d’un tableau ou d’une plage. Dans l’éditeur Power Query, choisissez Accueil > Ajouter des requêtes > Ajouter des requêtes comme nouvelles, sélectionnez les quatre tables et validez. Vous obtenez une table unique qui empile toutes les lignes. Ajoutez au besoin une colonne personnalisée « Source » pour savoir de quelle région vient chaque ligne, puis Fermer et charger dans > Tableau croisé dynamique.

Si vos fichiers arrivent chaque mois dans un dossier, utilisez plutôt Données > Obtenir des données > À partir d’un fichier > À partir d’un dossier, puis Combiner et transformer. Chaque nouveau fichier déposé dans le dossier sera intégré à la prochaine actualisation, sans aucune manipulation.

Méthode 2 : le modèle de données. Si vos bases ne sont pas identiques mais complémentaires (une table Ventes, une table Produits, une table Commerciaux), cochez Ajouter ces données au modèle de données lors de l’insertion du TCD, puis créez les relations dans Données > Relations ou dans la vue diagramme de Power Pivot. Le TCD peut alors croiser des champs de plusieurs tables sans RECHERCHEV.

Méthode 3 : l’assistant de consolidation de plages multiples. L’ancien assistant Tableau croisé dynamique reste accessible avec la séquence de touches Alt, D, P (tapées l’une après l’autre). L’option Plages de feuilles de calcul avec étiquettes permet de consolider plusieurs plages. Le résultat fonctionne, mais les champs s’appellent Ligne, Colonne, Valeur et Page1, et seule la première colonne sert d’étiquette : c’est une solution de dépannage, pas une base de reporting.

Il existe aussi Données > Consolider, qui additionne plusieurs plages par position ou par catégorie. Ce n’est pas un TCD : le résultat est un tableau figé, utile pour une synthèse ponctuelle, beaucoup moins pour un rapport mensuel.

Le détail des manipulations dans l’éditeur (types de données, fusion de requêtes, colonnes calculées) est traité dans notre guide Power Query et Power Pivot dans Excel.

Étape 2 — Structurer le TCD pour la lecture

Un TCD de reporting doit pouvoir être lu sans explication. Dans l’onglet Création :

1. Disposition du rapport > Afficher sous forme tabulaire, puis Répéter toutes les étiquettes d’éléments.
2. Sous-totaux > Afficher tous les sous-totaux en bas du groupe, ou les désactiver si le graphique suffit.
3. Totaux généraux : ne conservez que ceux qui ont un sens (un total de pourcentages n’en a pas toujours).

Renommez ensuite chaque champ de valeur : cliquez sur l’en-tête « Somme de Chiffre d’affaires » et tapez « CA ». Excel refuse un nom identique à celui d’un champ source ; ajoutez simplement une espace à la fin (« Chiffre d’affaires ») si vous tenez au libellé exact.

Enfin, dans Options du tableau croisé dynamique > Disposition et mise en forme, cochez Pour les cellules vides, afficher et saisissez 0 ou un tiret, et décochez Ajuster automatiquement la largeur des colonnes lors de la mise à jour.

Étape 3 — Ajouter des indicateurs : champ calculé ou mesure DAX

Pour un TCD classique, le champ calculé (Analyse du tableau croisé dynamique > Champs, éléments et jeux > Champ calculé) suffit pour une marge ou un taux de marge. Mais si votre TCD est construit sur le modèle de données, cette commande est grisée : il faut créer une mesure.

Clic droit sur le nom de la table dans le volet des champs > Ajouter une mesure (ou Power Pivot > Mesures > Nouvelle mesure). Exemples de mesures utiles en reporting :

CA Total := SUM ( Ventes[Chiffre d'affaires] )
Marge := SUM ( Ventes[Chiffre d'affaires] ) - SUM ( Ventes[Coût] )
Taux de marge := DIVIDE ( [Marge] ; [CA Total] )
CA N-1 := CALCULATE ( [CA Total] ; SAMEPERIODLASTYEAR ( Calendrier[Date] ) )

Selon vos paramètres régionaux, le séparateur d’arguments est le point-virgule ou la virgule. La mesure CA N-1 nécessite une table de dates marquée comme table de dates et reliée à la table des ventes. Autre avantage du modèle de données : l’option Nombre distinct devient disponible dans les paramètres des champs de valeurs, pratique pour compter des clients uniques.

Étape 4 — Créer un graphique croisé dynamique

Un graphique croisé dynamique est un graphique lié à un TCD : il affiche exactement ce que le TCD affiche. Cliquez dans le TCD, puis Analyse du tableau croisé dynamique > Graphique croisé dynamique, choisissez le type et validez.

Quelques réglages rendent le graphique présentable :

• Masquer les boutons de champ : onglet Analyse du graphique croisé dynamique > Boutons de champ > Masquer tout. Ils encombrent un graphique destiné à une réunion.
• Choisir le bon type : histogramme groupé pour comparer des catégories, courbes pour une évolution mensuelle, barres horizontales pour un classement, secteurs uniquement pour 3 à 5 parts.
• Trier dans le TCD : le graphique suit l’ordre du TCD. Un tri décroissant sur les valeurs donne un classement lisible.
• Graphique combiné : pour afficher le CA en histogramme et le taux de marge en courbe, choisissez Modifier le type de graphique > Graphique combiné et placez le taux sur un axe secondaire.

Attention : tous les types de graphiques ne sont pas disponibles pour un graphique croisé dynamique. Les nuages de points, les bulles, les graphiques boursiers et plusieurs types récents (compartimentage, cascade, entonnoir, par exemple) ne sont pas proposés. Si vous en avez besoin, construisez un graphique classique à partir de cellules alimentées par LIREDONNEESTABCROISDYNAMIQUE.

Étape 5 — Relier segments, chronologies et plusieurs TCD

Un reporting contient rarement un seul TCD. Pour que tous réagissent au même filtre, ils doivent partager le même cache (même source). Insérez un segment, puis clic droit > Connexions de rapport et cochez chaque TCD. Les graphiques croisés suivent automatiquement leur TCD.

Pour gagner de la place, réglez le segment dans l’onglet Segment : nombre de Colonnes (3 ou 4 boutons par ligne), hauteur et largeur des boutons. Dans Paramètres des segments, cochez Masquer les éléments sans données pour éviter les boutons inutiles.

Étape 6 — Appliquer une mise en forme conditionnelle au TCD

La mise en forme conditionnelle TCD a une particularité que beaucoup ignorent. Sélectionnez une valeur du champ, puis Accueil > Mise en forme conditionnelle et choisissez une règle (barres de données, jeu d’icônes, règle des valeurs supérieures). Une petite icône d’options apparaît alors à côté de la sélection, avec trois choix :

Option d’application	Comportement	Quand l’utiliser
Cellules sélectionnées	La règle reste sur ces cellules précises	Presque jamais en reporting : elle se décale à la première actualisation
Toutes les cellules affichant les valeurs « CA »	La règle s’applique à toutes les valeurs du champ, y compris les sous-totaux et totaux	Mise en évidence globale
Toutes les cellules affichant les valeurs « CA » pour « Produit »	La règle ne porte que sur le niveau de détail choisi	Comparer les produits entre eux sans que les totaux écrasent l’échelle

La troisième option est la plus utile : les barres de données ou les échelles de couleurs ne comparent alors que des éléments de même niveau. Pour modifier ce choix plus tard : Mise en forme conditionnelle > Gérer les règles > Modifier la règle, section Appliquer la règle à.

Étape 7 — Créer un style de TCD personnalisé

Les styles prédéfinis de l’onglet Création conviennent pour un usage interne, mais un reporting diffusé gagne à respecter la charte de l’entreprise. Ouvrez la galerie des styles et cliquez sur Nouveau style de tableau croisé dynamique. Donnez un nom au style, puis mettez en forme chaque élément : Tableau entier, Ligne d’en-tête, Ligne Total général, Sous-titre de ligne 1, Première bande de lignes, etc.

Cochez Définir comme style de tableau croisé dynamique par défaut pour ce document pour que tous les nouveaux TCD du classeur l’utilisent. Les options de l’onglet Création (Lignes à bandes, En-têtes de ligne) se combinent avec votre style. Les segments ont leur propre galerie : dupliquez un style existant (clic droit > Dupliquer) pour l’adapter à vos couleurs.

Un style personnalisé est enregistré dans le classeur. Pour le réutiliser ailleurs, copiez un TCD qui l’utilise dans le nouveau classeur, ou partez d’un modèle (.xltx) qui le contient.

Étape 8 — Paramétrer l’impression d’un TCD

Un TCD imprimé sans préparation donne souvent des pages coupées et des en-têtes absents. Les réglages utiles se trouvent à trois endroits :

1. Options du tableau croisé dynamique > onglet Impression : cochez Définir les titres d’impression pour répéter les en-têtes du TCD sur chaque page, et décochez l’impression des boutons Développer/Réduire.
2. Paramètres de champ (clic droit sur un élément du champ Région > Paramètres de champ) > onglet Disposition et impression : cochez Insérer un saut de page après chaque élément pour commencer chaque région sur une nouvelle page.
3. Mise en page : orientation paysage, Ajuster à 1 page en largeur, et un pied de page avec la date d’impression et le numéro de page.

Pour un envoi par région, une commande méconnue fait gagner beaucoup de temps : placez le champ Région dans la zone Filtres, puis Analyse du tableau croisé dynamique > Options (flèche) > Afficher les pages de filtre du rapport. Excel crée une feuille par région, chacune avec son TCD filtré, prête à être exportée en PDF.

Automatiser le reporting Excel : les bonnes pratiques

Un rapport qui demande une heure de retouches à chaque mise à jour n’est pas encore un reporting. Voici les réglages qui rendent le processus reproductible.

• Actualiser tout en une fois : Données > Actualiser tout (Ctrl+Alt+F5) met à jour les requêtes Power Query puis les TCD. Dans les propriétés de chaque requête (Données > Requêtes et connexions, clic droit > Propriétés), vous pouvez activer Actualiser les données lors de l’ouverture du fichier.
• Conserver la mise en forme : dans les Options du TCD, laissez cochée Conserver la mise en forme des cellules lors de la mise à jour, et appliquez les formats de nombre via les Paramètres des champs de valeurs plutôt que via l’onglet Accueil.
• Utiliser des listes personnalisées : pour que les régions ou les gammes s’affichent toujours dans l’ordre métier, créez une liste dans Fichier > Options > Options avancées > Modifier les listes personnalisées. Le TCD l’utilise pour trier si l’option Utiliser des listes personnalisées lors du tri est active.
• Construire une page de synthèse avec LIREDONNEESTABCROISDYNAMIQUE : pour un rapport très mis en forme (tableau de bord, note de direction), placez les TCD sur une feuille technique et récupérez les chiffres avec cette fonction. La mise en page reste libre et les chiffres restent justes après actualisation.
• Nommer les objets : TCD (onglet Analyse, zone Nom), segments, requêtes. Un classeur avec « TCD_Ventes_Region » est bien plus simple à maintenir que « Tableau croisé dynamique7 ».
• Documenter : un onglet « Lisez-moi » qui indique la source des données, la fréquence de mise à jour et la procédure d’actualisation évite bien des erreurs quand le fichier change de mains.

Pour aller jusqu’à un écran interactif avec indicateurs clés, graphiques et boutons de navigation, suivez notre méthode pour créer un tableau de bord Excel. Et si certaines manipulations restent répétitives (export PDF de chaque feuille, envoi par e-mail), une macro VBA Excel peut prendre le relais.

Les erreurs fréquentes avec les TCD avancés (et comment les éviter)

En formation, les mêmes problèmes reviennent dès qu’on passe à plusieurs sources et à la diffusion.

Erreur	Cause	Solution
Un segment ne pilote pas tous les TCD	Les TCD n’utilisent pas le même cache ou la même source	Construire tous les TCD sur la même table ou la même requête, puis passer par Connexions de rapport
Le champ calculé est grisé	Le TCD est basé sur le modèle de données	Créer une mesure DAX (clic droit sur la table > Ajouter une mesure)
Impossible de grouper les dates ou les éléments	Même cause : TCD issu du modèle de données	Ajouter les colonnes Année, Mois ou Catégorie dans Power Query ou dans une table de dates
La mise en forme conditionnelle se décale	Règle appliquée aux « Cellules sélectionnées »	Réappliquer la règle à toutes les cellules affichant les valeurs du champ
Les colonnes changent de largeur à chaque actualisation	Option d’ajustement automatique active	La décocher dans Options du TCD > Disposition et mise en forme
Doublons après consolidation	Un même fichier ou une même période importé deux fois	Ajouter une colonne Source et contrôler les lignes avec un TCD de vérification
Le graphique croisé n’offre pas le type voulu	Type non pris en charge par les graphiques croisés	Utiliser un graphique classique alimenté par LIREDONNEESTABCROISDYNAMIQUE
Le fichier devient lourd et lent	Plusieurs caches identiques, données chargées en double	Charger les requêtes en « Connexion uniquement » ou dans le modèle de données

Notre conseil : avant de diffuser un reporting, gardez un petit TCD de contrôle qui compte les lignes par source et par mois. Une région manquante ou un mois en double se voit immédiatement.

TCD classique, modèle de données ou Power BI : comparaison

Le TCD avancé peut reposer sur deux moteurs différents, et Power BI prend le relais au-delà d’un certain niveau de partage.

Critère	TCD classique	TCD sur modèle de données (Power Pivot)	Power BI
Nombre de tables	Une seule	Plusieurs, reliées	Plusieurs, reliées
Calculs	Champs et éléments calculés	Mesures DAX	Mesures DAX
Nombre distinct	Non	Oui	Oui
Regroupement manuel (Grouper)	Oui	Non (colonnes à préparer en amont)	Via groupes ou colonnes
Volume confortable	Quelques centaines de milliers de lignes	Plusieurs millions de lignes	Plusieurs millions de lignes
Diffusion	Fichier Excel, PDF, impression	Fichier Excel, PDF, impression	Rapport en ligne partagé, actualisation planifiée
Courbe d’apprentissage	Faible	Moyenne	Moyenne à élevée

Tant que votre reporting est produit par une ou deux personnes et diffusé en PDF ou en fichier, le TCD avancé dans Excel reste la solution la plus simple. Quand plusieurs dizaines de personnes doivent consulter des visuels interactifs actualisés automatiquement, les visuels Power BI deviennent plus adaptés, avec une logique de mesures identique.

Comment progresser sur les TCD avancés et le reporting ?

La progression la plus efficace suit l’ordre dans lequel un reporting se construit :

1. Consolider le socle (1 heure) : revoir la structure d’un TCD, les tris, les regroupements, les champs calculés et les segments sur une base propre.
2. Présenter (1 heure) : styles personnalisés, mise en forme conditionnelle à la bonne portée, graphiques croisés dynamiques, paramètres d’impression.
3. Automatiser la source (1 à 2 heures) : importer et combiner des fichiers avec Power Query, relier des tables dans Power Pivot, écrire vos premières mesures.
4. Assembler : réunir TCD, graphiques et segments sur une page lisible, puis préparer le fichier au partage.
5. Pratiquer sur un cas réel : reprendre votre reporting mensuel actuel et le reconstruire avec cette méthode. C’est là que les automatismes s’installent.

Pour suivre ce parcours avec des fichiers d’exercice, la formation Excel dédiée aux TCD et au tableau de bord d’EspritAcadémique dure trois heures, est ouverte à tous les niveaux et reprend chaque étape en vidéo. Elle aborde notamment :

• la construction de TCD multicritères, les listes personnalisées, les regroupements et les champs calculés ;
• les segments, les chronologies et la consolidation de plusieurs bases ;
• la personnalisation de l’aspect, la création d’un style maison, la mise en forme conditionnelle et l’impression ;
• les graphiques croisés dynamiques, dont les graphiques complexes pilotés par segments ;
• l’import et la préparation des données avec Power Query, puis les mesures calculées dans Power Pivot ;
• l’assemblage d’un tableau de bord avec boutons de navigation et sa préparation au partage.

Vous pouvez parcourir le programme complet de cette formation sur les TCD avant de vous inscrire.

En résumé

Un TCD Excel avancé repose sur trois piliers : une source consolidée et automatisée (Power Query ou modèle de données), des calculs robustes (champs calculés ou mesures DAX), et une présentation stable (style personnalisé, mise en forme conditionnelle appliquée au champ, graphiques croisés, paramètres d’impression). Une fois cette chaîne en place, votre reporting se met à jour d’un clic et s’imprime ou s’envoie sans retouche.