- Un tableau croisé dynamique (TCD) résume une longue liste de lignes en un tableau à double entrée, sans écrire de formule.
- Il se construit en glissant des champs vers quatre zones : Lignes, Colonnes, Valeurs, Filtres.
- Les dates se regroupent par mois, trimestre ou année ; les montants se totalisent par compte, par tiers, par journal.
- Après modification des données sources, il faut actualiser le TCD (Alt+F5) ; la source doit être un tableau Excel pour que les nouvelles lignes soient prises en compte.
Quand les écritures se comptent par milliers, les formules cèdent la place à un outil plus rapide : le tableau croisé dynamique (TCD). Il transforme une liste en synthèse en quelques clics, et se modifie en glissant les champs. Voici un guide pour un usage comptable, avec les écrans-clés.
Ce que fait un TCD
Un TCD regroupe les lignes d’une source selon une ou plusieurs catégories (compte, trimestre, tiers, journal) et calcule une valeur (somme, nombre, moyenne). Il remplace des dizaines de SOMME.SI.ENS ; il se met à jour d’un clic.
Étape 1 : préparer la source
La source doit être une liste propre : une ligne d’en-têtes, une écriture par ligne, pas de lignes de total, pas de cellules fusionnées, pas de colonnes vides. Colonnes types : Date, Journal, Compte, Libellé, Tiers, Débit, Crédit.
Astuce : sélectionnez une cellule de la liste et appuyez sur Ctrl+T pour la convertir en tableau Excel. Les lignes ajoutées plus tard seront automatiquement incluses dans le TCD.
Étape 2 : insérer le TCD
- Cliquez dans la liste.
- Onglet Insertion > Tableau croisé dynamique.
- Choisissez « Nouvelle feuille de calcul » > OK.
- Le volet Champs de tableau croisé dynamique s’ouvre à droite avec quatre zones : Filtres, Colonnes, Lignes, Valeurs.
Étape 3 : construire le tableau
Pour obtenir les charges par compte et par trimestre :
- Glissez Compte (ou un champ combinant numéro et intitulé) dans Lignes.
- Glissez Date dans Colonnes : Excel crée souvent automatiquement les champs Mois ou Trimestres.
- Glissez Débit dans Valeurs (« Somme de Débit »).
- Glissez Journal dans Filtres pour pouvoir isoler le journal d’achats.
Figure 1 : le repère 1 montre la zone Lignes (comptes), le repère 2 la zone Colonnes (trimestres), le repère 3 une valeur de la zone Valeurs.
Résultat : chaque charge apparaît par trimestre, avec les totaux par ligne et par colonne. Le total général de 845 400 MAD se compare directement à la somme du grand livre pour contrôle.
Étape 4 : regrouper les dates
Si les colonnes affichent des dates au jour : clic droit sur une date > Grouper, sélectionnez Trimestres (et Années si l’exercice dépasse 12 mois). Excel les regroupe en T1, T2, T3, T4.
Étape 5 : trier, filtrer, mettre en forme
- Trier : clic droit > Trier > du plus grand au plus petit (classer les charges par montant).
- Filtrer : le champ en zone Filtres, ou l’entonnoir sur les étiquettes de lignes.
- Segments : Analyse du tableau croisé dynamique > Insérer un segment (boutons de filtre visuels).
- Format des nombres : clic droit sur une valeur > Format de cellule : séparateur de milliers, aucune décimale.
- Style : onglet Création du TCD (bandes, totaux).
Étape 6 : calculs avancés
| Besoin | Méthode |
|---|---|
| Part de chaque compte dans le total | Paramètres des champs de valeurs > Afficher les valeurs > % du total de la colonne |
| Évolution d’un trimestre sur l’autre | Afficher les valeurs > Différence par rapport à > T1 (ou % de différence) |
| Solde (débit − crédit) | Ajouter un champ calculé : Analyse > Champs, éléments et jeux > Champ calculé > =Débit-Crédit |
| Analyse par tiers | Remplacer Compte par Tiers dans Lignes |
| Détail d’un chiffre | Double-clic sur la valeur : Excel ouvre les lignes sources dans une nouvelle feuille |
Le double-clic est particulièrement utile pour justifier un total devant l’auditeur.
Un exercice corrigé pas à pas
Huit lignes de charges d’exploitation (extrait d’un grand livre, en MAD) :
| Date | Compte | Montant |
|---|---|---|
| 12/01 | 6136 | 12 000 |
| 25/01 | 6131 | 8 000 |
| 14/02 | 6136 | 9 500 |
| 02/03 | 6144 | 15 000 |
| 15/04 | 6136 | 11 000 |
| 20/05 | 6144 | 22 000 |
| 08/06 | 6131 | 8 000 |
| 30/06 | 6144 | 7 500 |
Construction du TCD. Insérer > Tableau croisé dynamique ; placer Compte dans Lignes, Date dans Colonnes (puis clic droit sur une date > Grouper > Trimestres), Montant dans Valeurs (somme). Résultat attendu :
| Compte | 1er trimestre | 2e trimestre | Total |
|---|---|---|---|
| 6131 Locations | 8 000 | 8 000 | 16 000 |
| 6136 Honoraires | 21 500 | 11 000 | 32 500 |
| 6144 Publicité | 15 000 | 29 500 | 44 500 |
| Total général | 44 500 | 48 500 | 93 000 |
Afficher les parts. Clic droit sur une valeur > Afficher les valeurs > % du total général : 6131 = 17,2 %, 6136 = 34,9 %, 6144 = 47,8 %. La publicité pèse presque la moitié des charges de la période et croît de 97 % d’un trimestre à l’autre (15 000 → 29 500) : c’est le premier poste à examiner.
Récupérer un chiffre dans une formule. Pour reporter le total des honoraires dans une autre feuille sans copier-coller, la fonction LIREDONNEESTABCROISDYNAMIQUE lit une cellule du TCD de manière stable, même si le tableau change de forme :
=LIREDONNEESTABCROISDYNAMIQUE("Montant";$A$3;"Compte";"6136")
(le deuxième argument désigne une cellule du TCD ; le résultat est 32 500).
Filtrer d’un clic. Analyse du tableau croisé dynamique > Insérer un segment : un segment « Compte » ou « Journal » filtre le tableau à la demande, utile pour une réunion de revue.
Vérifier que le TCD est juste
- Le total général du TCD égale la somme de la colonne source (ici 93 000).
- Aucune cellule vide dans les colonnes de la source : une date manquante crée une ligne « (vide) ».
- Les montants sont des nombres (alignés à droite) : un montant stocké en texte n’est pas additionné.
- Actualiser après toute modification de la source (Analyse > Actualiser, ou Alt + F5).
- En cas de lignes ajoutées, utiliser un tableau Excel (Ctrl + T) comme source : le TCD intègre les nouvelles lignes à l’actualisation.
Cinq analyses utiles à partir d’un même grand livre
Un tableau croisé dynamique bien construit répond à de nombreuses questions sans formule. Avec un grand livre transformé en tableau Excel (colonnes : Date, Compte, Intitulé, Journal, Libellé, Débit, Crédit), voici cinq analyses.
| Question | Lignes | Colonnes | Valeurs | Remarque |
|---|---|---|---|---|
| Quelle est la balance par compte ? | Compte, Intitulé | Somme du Débit, Somme du Crédit | Contrôle : total débit = total crédit | |
| Comment les charges évoluent-elles mois par mois ? | Compte (filtré sur la classe 6) | Date regroupée par mois | Somme du Débit − Crédit | Colonne calculée « Solde » dans la source |
| Quelles charges ont le plus augmenté ? | Compte | Exercice | Somme du Solde ; afficher la variation en % | « Afficher les valeurs » > « Différence en % de » |
| Quels fournisseurs pèsent le plus ? | Tiers (comptes 4411) | Somme du Crédit ; classement | Trier décroissant, filtre « 10 premiers » | |
| Quelle part représente chaque charge ? | Compte | Somme du Solde en « % du total général » | Comparaison à la structure des produits |
Astuce : ajoutez à la source une colonne Solde = Débit − Crédit et une colonne Classe = GAUCHE(Compte;1). Elles permettent de filtrer par classe et de calculer sans champ calculé.
Regrouper par mois, trimestre, exercice
Sur un champ de dates, faites un clic droit dans le tableau, Grouper, et sélectionnez Mois, Trimestres et Années. Un grand livre sur deux ans donne alors, en quelques secondes, une lecture par exercice puis par trimestre et par mois. Pour un exercice décalé (clôture au 30 juin), construisez dans la source une colonne « Exercice » avec la formule =SI(MOIS([@Date])>6;ANNEE([@Date])+1;ANNEE([@Date])) et utilisez-la comme champ de regroupement.
Un champ calculé : la marge
Dans un tableau croisé construit sur les ventes et les coûts, ajoutez un champ calculé : Analyse du tableau croisé dynamique > Champs, éléments et jeux > Champ calculé. Nom : Marge, formule : =Ventes - Coûts. Un second champ Taux de marge : =(Ventes - Coûts) / Ventes. Le résultat se calcule au niveau agrégé (par famille, par client) et non ligne à ligne, ce qui est correct pour une marge sur ensembles. Attention : un champ calculé ne peut pas utiliser de fonctions de recherche, de condition sur d’autres lignes ou de références de cellules.
Des segments pour un tableau de bord interactif
Les segments sont des boutons de filtrage visuels. Sur un TCD de chiffre d’affaires, ajoutez des segments sur Famille de produits, Commercial et Exercice. Reliez-les à plusieurs tableaux (clic droit sur le segment > Connexions de rapports) pour qu’un seul clic mette à jour les tableaux et les graphiques de la feuille. Une chronologie permet de filtrer par période en faisant glisser un curseur. Cette présentation convient à un tableau de bord mensuel de direction.
Vérifier un tableau croisé avec trois contrôles
- Total général du TCD = total de la balance : rapprochez le total des débits et des crédits avec ceux de la source (
=SOMME(Source[Débit])). - Nombre de lignes : comparez le décompte du TCD (valeurs en « Nombre ») avec le nombre de lignes de la source.
- Test aléatoire : choisissez un compte et vérifiez son solde avec
SOMME.SI.ENS.
Si un contrôle échoue, la cause la plus fréquente est une plage de source qui n’inclut pas les nouvelles lignes : utilisez un tableau Excel (Ctrl+T) pour que le TCD se mette à jour avec Actualiser tout.
Les erreurs courantes et leurs remèdes
| Symptôme | Cause | Remède |
|---|---|---|
| Les montants s’affichent en « Nombre » | Colonne contenant du texte (cellules vides, montants importés en texte) | Convertir en nombres, supprimer les espaces |
| Dates non regroupables | Dates au format texte | Convertir avec DATEVAL ou Données > Convertir |
| Nouvelles lignes absentes | Plage fixe | Source sous forme de tableau Excel |
| Lignes « (vide) » | Cellules vides dans la source | Remplir ou filtrer |
| Totaux faux après modification d’une cellule de la source | TCD non actualisé | Actualiser tout (Alt+F5) |
| TCD qui change de mise en forme à l’actualisation | Largeurs automatiques | Désactiver « Ajuster automatiquement la largeur » |
Actualiser
Après toute modification des données : clic droit sur le TCD > Actualiser, ou Alt+F5. Pour que l’actualisation soit automatique à l’ouverture du fichier : Analyse > Options > Données > « Actualiser les données lors de l’ouverture du fichier ».
Les erreurs courantes
- Cellules vides ou texte dans une colonne de montants : Excel fait un « Nombre de » au lieu d’une somme.
- Dates en texte : le regroupement est impossible.
- En-tête manquant dans une colonne : message d’erreur à la création.
- Source fixe : les nouvelles écritures n’apparaissent pas ; convertissez en tableau Excel.
- Total faux à cause de doublons d’écritures : contrôlez la source avant l’analyse.
- TCD copié-collé en valeurs : il perd son dynamisme et vieillit.
Quand préférer des formules ?
Les TCD conviennent à l’exploration et aux synthèses ponctuelles. Pour un reporting à format fixe (tableau de bord envoyé chaque mois), les formules (SOMME.SI.ENS) ou Power Query conviennent mieux : mise en page maîtrisée, aucune manipulation d’actualisation.
Cas particuliers et situations limites
La source contient des milliers de lignes et le classeur est lent. Utiliser le modèle de données (Power Pivot) au lieu d’une plage classique, ou prétraiter les données dans Power Query pour réduire les colonnes.
Le TCD ne reconnaît pas les nouvelles lignes ajoutées. Convertir la source en tableau Excel et actualiser ; ou modifier la source de données du TCD pour inclure toute la plage.
Les mois s’affichent dans le désordre. Les dates sont du texte : convertir en vraies dates, puis regrouper par mois.
On veut afficher des pourcentages du total. Clic droit sur la valeur, Afficher les valeurs puis % du total général, % du total de la colonne ou Différence en % de.
Le TCD doit être diffusé sans les données. Données puis Options du TCD : décocher « Enregistrer les données sources avec le fichier » ; le destinataire pourra consulter le tableau sans pouvoir détailler les lignes.
À retenir
Un tableau croisé dynamique construit sur une source en tableau Excel analyse un grand livre en quelques clics. Les contrôles sont indispensables : total du TCD égal au total de la source, nombre de lignes, test sur un compte. Les segments et chronologies en font un tableau de bord interactif.
Questions fréquentes
Pourquoi mon TCD ne s’actualise-t-il pas ?
Un TCD n’est pas recalculé automatiquement : faites un clic droit puis Actualiser, ou Données > Actualiser tout (Alt+F5). Pour qu’il détecte les lignes ajoutées, formatez la source en tableau Excel (Ctrl+T) ou utilisez une plage dynamique.
Les dates apparaissent par jour, comment les regrouper ?
Clic droit sur une date du TCD > Grouper, puis choisissez Mois, Trimestres, Années. Si le bouton est grisé, c’est que certaines dates sont du texte ou que des cellules sont vides dans la colonne source.
Que signifie « Nombre de » au lieu de « Somme de » ?
Si la colonne contient des cellules vides ou du texte, Excel compte les lignes au lieu d’additionner. Convertissez la colonne en nombres, puis cliquez sur le champ dans Valeurs > Paramètres des champs de valeurs > Somme.
Peut-on obtenir des pourcentages ?
Oui : Paramètres des champs de valeurs > Afficher les valeurs > % du total général (ou de la colonne, de la ligne). C’est utile pour la structure des charges ou la part de chaque client.
Articles rédigés par l’équipe du cabinet à partir des textes officiels et de la pratique des missions. Chaque article indique sa date de publication et, le cas échéant, de dernière revue.