- Afficher les valeurs en % du total, en différence ou en cumul transforme un TCD de simples sommes en un tableau d’analyse : part de chaque catégorie, évolution d’un mois à l’autre.
- Les segments et la chronologie ajoutent des boutons de filtre visuels, reliables à plusieurs TCD ; les dates se regroupent par mois, trimestre ou année.
- Un champ calculé travaille sur des sommes de colonnes : appliquer 20 % à la somme des HT donne 38 514 alors que la TVA réelle ligne à ligne vaut 34 094 quand certains achats sont à 10 %.
- Règle : tout calcul qui dépend d’un taux ou d’une condition ligne par ligne se fait dans la source (colonne ajoutée au tableau), pas dans le TCD.
Le premier TCD répond à « combien ? ». L’analyse demande davantage : quelle part de chaque catégorie, quelle évolution d’un mois à l’autre, quel périmètre (payé ou non, tel fournisseur) ? Cet épisode utilise les mêmes 28 factures que l’épisode 25 pour transformer le TCD en tableau d’analyse, et montre le piège le plus fréquent des champs calculés.
Ce que vous saurez faire à la fin de l’épisode
- afficher les valeurs en pourcentage, en écart et en cumul ;
- trier un TCD et en extraire les premiers éléments ;
- regrouper les dates par mois, trimestre, année ;
- ajouter des segments et une chronologie ;
- savoir quand un champ calculé est faux, et ce qu’il faut faire à la place.
Afficher les valeurs autrement
Clic droit sur un chiffre de la zone Valeurs › Afficher les valeurs. Les options les plus utiles :
| Option | Ce qu’elle montre | Usage |
|---|---|---|
| % du total général | Part de chaque ligne dans l’ensemble | Poids des catégories |
| % du total de la colonne / ligne | Part dans la colonne ou la ligne | Répartition par mois |
| Différence par rapport à… | Écart avec l’élément précédent | Variation mensuelle |
| % de différence par rapport à… | Écart relatif | Croissance |
| Résultat cumulé dans | Total cumulé | Courbe de cumul annuel |
| Rang du plus grand au plus petit | Classement | Top fournisseurs |
La part de chaque catégorie
Reprenons le TCD des achats, avec la catégorie en lignes. On glisse Montant HT deux fois en Valeurs : la première pour le montant, la seconde réglée en % du total général.
Figure 1 : les catégories, triées de la plus lourde à la plus légère. Repère 1 : la colonne en % du total général.
| Catégorie | Montant HT | % du total |
|---|---|---|
| Marchandises | 74 100 | 38,5 % |
| Services | 39 340 | 20,4 % |
| Transport | 36 800 | 19,1 % |
| Entretien | 19 000 | 9,9 % |
| Fournitures | 15 930 | 8,3 % |
| Restauration | 7 400 | 3,8 % |
| Total | 192 570 | 100,0 % |
Pour trier : clic droit sur un montant › Trier › du plus grand au plus petit. Lecture : les trois premières catégories pèsent 150 240 sur 192 570, soit 78,0 % des achats : la concentration est forte, ce qui oriente la négociation fournisseurs et le suivi des risques. Pour n’afficher que les trois premières, utilisez Filtres de valeurs › 10 premiers… et changez 10 en 3.
L’évolution d’un mois à l’autre
Avec les mois en lignes, on règle la valeur sur Différence par rapport à, champ Date (mois), élément précédent, puis on ajoute un second champ en % de différence.
Figure 2 : les achats mensuels et leur variation.
Janvier est à 45 480 (pas de mois précédent, donc pas d’écart). Février passe à 74 610, soit +29 130 (+64,1 %) ; mars retombe à 72 480, soit −2 130 (−2,9 %). Un TCD ne dit pas pourquoi : ici, l’explication se trouve dans le détail (la facture de 22 000 de Cabinet Conseil Nord en février, la hausse des achats de marchandises). C’est le rôle du double-clic sur le chiffre (épisode 25).
Regrouper les dates
Les dates d’un TCD se regroupent par clic droit › Grouper… : secondes, minutes, heures, jours, mois, trimestres, années. On peut cocher plusieurs niveaux en même temps (années et mois). Pour une analyse sur plusieurs exercices, cochez Années ; sinon, Excel regroupe les mois de toutes les années sous le même mois (janvier 2025 + janvier 2026 dans « janvier »), erreur classique. Pour regrouper par période spéciale (semaines de 7 jours), choisissez Jours et un nombre de jours égal à 7.
Si Grouper est grisé : la colonne contient du texte ou des cases vides. Revenez à l’épisode 2.
Segments et chronologie
Analyse du tableau croisé dynamique › Insérer un segment ajoute une boîte de boutons par champ choisi (par exemple Statut et Catégorie). Chaque clic filtre le TCD, et la boîte indique d’un coup d’œil quelles valeurs sont retenues. Insérer une chronologie fait de même pour une colonne de dates, avec un curseur de période.
Deux avantages sur les filtres classiques :
- Lisibilité : on voit le filtre actif sans ouvrir de menu, ce qui évite les erreurs de lecture d’un TCD « filtré sans le savoir ».
- Pilotage de plusieurs TCD : clic droit sur le segment › Connexions de rapport permet de relier un segment à plusieurs TCD d’un même classeur : un tableau de bord entier réagit à un seul clic (épisode 30).
Pour effacer un segment : l’icône d’entonnoir barré en haut à droite de la boîte.
Les champs calculés, et leur piège
Analyse du tableau croisé dynamique › Champs, éléments et jeux › Champ calculé permet d’ajouter un champ défini par une formule sur les autres champs. Par exemple, la TVA : = 'Montant HT' * 0,2.
Figure 3 : la TVA réelle (ligne à ligne) et celle du champ calculé divergent.
Le champ calculé ne calcule pas ligne par ligne : il applique sa formule aux sommes de chaque champ. Sur nos 28 factures :
- la TVA réelle vaut, en appliquant à chaque ligne son taux (20 % pour les marchandises, fournitures, services et entretien, 10 % pour le transport et la restauration) : 20 % de 148 370 + 10 % de 44 200 = 29 674 + 4 420 = 34 094 ;
- le champ calculé, qui applique 20 % à la somme globale de 192 570, donne 38 514.
L’écart (4 420) correspond à la différence de taux sur les 44 200 d’achats à 10 %. Aucun message d’erreur n’avertit : le TCD affiche un total plausible mais faux. La règle est simple : si le calcul dépend d’un taux, d’un test ou d’une condition qui varie d’une ligne à l’autre, il se fait dans la source, par une colonne ajoutée au tableau :
=[@[Montant HT]]*[@Taux TVA]et on additionne cette colonne dans le TCD. Les champs calculés ne conviennent qu’aux rapports de sommes qui ont un sens global (taux de marge = somme des marges ÷ somme des ventes, mais pas une moyenne de taux).
Les autres réglages utiles
- Disposition du rapport › Format tabulaire et Répéter toutes les étiquettes d’élément : un TCD prêt à être copié ou transformé en base de données.
- Afficher les éléments sans données : pour ne pas voir disparaître un mois sans facture.
- Actualiser et conserver la mise en forme : Options du TCD › Mise en forme › Conserver la mise en forme à la mise à jour.
- Plusieurs TCD, un seul cache : créer un deuxième TCD à partir du même tableau réutilise le cache (fichier plus léger).
- Graphique croisé dynamique :
Analyse › Graphique croisé dynamiquecrée un graphique relié au TCD (épisode 27).
À vous de jouer
Dans le classeur d’exercice (les trois tableaux sont donnés ; reproduisez-les à partir du journal de l’épisode 25) :
- TCD par catégorie : montant et % du total général, trié du plus grand au plus petit. Retrouvez 38,5 % pour Marchandises.
- TCD par mois : Différence par rapport à l’élément précédent et en %. Retrouvez +29 130 et −2 130.
- Ajoutez un segment Statut : que devient le total de mars avec « Non payé » ?
- Ajoutez à la source la colonne
TVA = Montant HT × Taux(10 % pour Transport et Restauration, 20 % pour les autres catégories) et sommez-la dans le TCD. Que trouvez-vous ?
Correction. Les trois catégories de tête pèsent 78,0 % des achats. Avec le segment Statut = Non payé, le total de mars vaut 54 680. La colonne TVA ligne à ligne donne 34 094, soit 4 420 de moins que le champ calculé à 20 % : la colonne dans la source est la bonne méthode.
À retenir
- Afficher les valeurs : % du total, écart, cumul, rang transforment un TCD en outil d’analyse.
- Les dates se regroupent (mois, trimestre, année) ; cochez l’année pour plusieurs exercices.
- Segments et chronologie : filtres visibles et partageables entre plusieurs TCD.
- Un champ calculé applique la formule aux totaux, pas aux lignes : les calculs avec un taux variable se font dans la source.
- Un TCD se lit avec méfiance : contrôlez toujours un total par un calcul indépendant.
Épisode 27 : les graphiques, pour choisir le bon type et éviter ceux qui trompent.
Questions fréquentes
Comment afficher un pourcentage du total dans un TCD ?
Clic droit sur un chiffre › Afficher les valeurs › % du total général (ou % du total de la colonne, de la ligne). Pour garder à la fois le montant et le pourcentage, glissez une seconde fois Montant HT dans Valeurs et changez l’affichage de ce second champ.
Qu’est-ce qu’un segment et en quoi diffère-t-il d’un filtre ?
Un segment est un filtre sous forme de boutons toujours visibles, qui montrent aussi quelles valeurs sont sélectionnées. Il peut piloter plusieurs TCD à la fois (Connexions de rapport), ce que ne fait pas un filtre classique. La chronologie est son équivalent pour les dates.
Pourquoi mon champ calculé donne-t-il un mauvais total ?
Parce qu’il applique la formule aux sommes des champs, pas ligne par ligne : un taux de TVA ou une marge qui varie d’une ligne à l’autre ne peut pas s’y exprimer. Ajoutez la colonne calculée dans la source et sommez-la.
Peut-on avoir les dates par trimestre ?
Oui : clic droit sur une date du TCD › Grouper, sélectionnez Trimestres (et Années si les données couvrent plusieurs années). Si Grouper est grisé, la colonne contient des dates en texte ou des cellules vides.
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.