Le tableau croisé dynamique est l’un des outils les plus utiles d’Excel pour résumer et analyser une liste de données et faire des statistiques. Il permet d’obtenir rapidement des totaux, des moyennes, des nombres ou des pourcentages, sans construire une succession de formules.
Dans l’exemple utilisé dans les captures d’écran, la base contient notamment les champs Équipe, Produit, Division, CA Vente et Charges patronales. Nous allons construire un tableau croisé dynamique qui présente la somme du chiffre d’affaires par équipe et par division, puis utiliser ces mêmes données pour découvrir les principales possibilités d’analyse et de filtrage.
Qu’est-ce qu’un tableau croisé dynamique Excel ?
Un tableau croisé dynamique, souvent abrégé en TCD, est un tableau de synthèse construit à partir d’une base de données. Il ne modifie pas les données d’origine : il les regroupe pour répondre à une question précise.
À partir de cette même base, vous pouvez par exemple obtenir :
- le chiffre d’affaires total par équipe
- le chiffre d’affaires par division
- le détail des résultats par produit
- la moyenne ou le maximum des charges patronales
- la part de chaque équipe dans le total général
- une sélection limitée à certaines divisions
L’intérêt principal du TCD réside dans sa souplesse. Les champs peuvent être déplacés à tout moment pour modifier l’analyse. Une même base peut ainsi servir à construire plusieurs synthèses sans être modifiée.
Dans les captures de ce tutoriel, le champ Équipe est placé en lignes, le champ Division en colonnes et le champ CA Vente dans les valeurs. Le tableau affiche ainsi immédiatement le chiffre d’affaires de chaque équipe pour les divisions Asia, Canada, Europe, South America et United States.
Préparer les données avant de créer un tableau croisé dynamique
La qualité du tableau croisé dynamique dépend directement de l’organisation des données sources. Chaque ligne doit correspondre à une opération et chaque colonne doit contenir une information précise.
Nous utiliserons une base comportant notamment les colonnes suivantes :
| Équipe | Produit | Division | CA Vente | Charges patronales |
|---|---|---|---|---|
| 0 | ASH-1001 | Asia | 40 224,26 € | 800,16 € |
| 1 | ASH-1115 | Canada | 43 962,37 € | 556,31 € |
| 2 | ASH-1116 | Europe | 141 064,20 € | 1 630,34 € |
| 3 | ASH-1117 | South America | 32 664,24 € | 403,52 € |
Avant de créer le tableau croisé dynamique, vérifiez les points suivants :
- chaque colonne possède un titre
- tous les en-têtes sont différents
- aucune ligne vide ne coupe la liste
- la base ne contient pas de cellules fusionnées
- chaque colonne contient un type de données cohérent
- les montants sont de véritables nombres et non des textes
- les dates sont reconnues comme des dates par Excel
Créer un tableau croisé dynamique dans Excel
Sélectionner la source de données
Cliquez dans une cellule de la base, puis ouvrez l’onglet Insertion et sélectionnez Tableau croisé dynamique.
Excel détecte normalement l’ensemble de la plage. Dans la capture, la zone Tableau/Plage contient la source nommée Ventes par produits. Vérifiez que cette zone correspond bien à toutes les données à analyser avant de choisir l’emplacement du rapport.
Choisir l’emplacement du tableau
Excel propose deux emplacements :
- Nouvelle feuille de calcul pour placer le TCD sur une feuille séparée
- Feuille de calcul existante pour choisir une cellule précise dans une feuille déjà utilisée
La nouvelle feuille de calcul est généralement le choix le plus pratique. Le tableau reste séparé de la base et dispose de suffisamment d’espace pour évoluer.
Cliquez ensuite sur OK. Excel crée un rapport vide et affiche le volet Champs de tableau croisé dynamique.
Comprendre la liste des champs
La partie supérieure du volet reprend les en-têtes de la base. Dans les captures, elle contient notamment les champs Produit, Équipe, Division, CA Vente et Charges patronales.
La partie inférieure comporte quatre zones :
| Zone | Utilisation | Exemple |
|---|---|---|
| Lignes | Présente les catégories verticalement. | Équipe |
| Colonnes | Présente les catégories horizontalement. | Division |
| Valeurs | Effectue le calcul statistique. | Somme de CA Vente |
| Filtres | Filtre l’ensemble du rapport. | Date d’embauche ou autre champ |
Si la liste des champs disparaît, cliquez dans le tableau croisé dynamique. Vous pouvez également utiliser la commande Liste de champs disponible dans l’onglet Analyse du tableau croisé dynamique.
Construire un tableau croisé dynamique
La construction du tableau consiste à placer les champs dans les quatre zones. Vous pouvez les faire glisser depuis la liste ou utiliser le menu accessible par un clic droit sur leur nom.
Pour reproduire le tableau visible dans les captures, placez :
- Équipe dans la zone Lignes
- Division dans la zone Colonnes
- CA Vente dans la zone Valeurs
Le champ Produit sera ensuite ajouté sous Équipe pour créer un deuxième niveau de lignes.
Excel calcule immédiatement la Somme de CA Vente pour chaque croisement entre une équipe et une division.
Ajouter, déplacer ou supprimer un champ
Pour modifier l’analyse, faites glisser un champ d’une zone vers une autre. Le menu associé à chaque champ permet également de le déplacer vers les étiquettes de lignes, les étiquettes de colonnes, la zone Valeurs ou la zone Filtres. Dans l’exemple, le champ Produit peut être ajouté sous Équipe afin d’obtenir le détail des ventes par produit.
Pour supprimer un champ du TCD, vous pouvez :
- décocher sa case dans la liste des champs
- le faire glisser en dehors des zones
- ouvrir son menu puis choisir Supprimer le champ
Modifier l’ordre des champs
Lorsqu’une zone contient plusieurs champs, leur ordre détermine la façon dont les données sont organisées. Dans la capture, Équipe constitue le premier niveau de lignes et Produit le second. Chaque équipe peut ainsi être développée pour afficher les produits qui lui sont associés.
Faites glisser les champs vers le haut ou vers le bas dans la zone concernée pour inverser ou modifier cette hiérarchie. Lorsque plusieurs champs sont placés dans la même zone, vous pouvez ensuite modifier la disposition d’un tableau croisé dynamique pour choisir une présentation compacte, en mode plan ou sous forme tabulaire.
Choisir les statistiques affichées
Lorsqu’un champ numérique est placé dans la zone Valeurs, Excel utilise généralement la fonction Somme. La capture montre les paramètres du champ Charges patronales, nommé automatiquement Somme de Charges patronales. Pour des données textuelles ou une colonne contenant des valeurs non numériques, Excel peut utiliser la fonction Nombre.
Modifier la fonction de calcul
Pour choisir une autre statistique :
- cliquez sur la flèche du champ présent dans la zone Valeurs
- sélectionnez Paramètres des champs de valeurs
- choisissez la fonction souhaitée
- cliquez sur OK
Les principales fonctions proposées sont les suivantes :
| Fonction | Résultat obtenu | Exemple |
|---|---|---|
| Somme | Additionne les valeurs. | Chiffre d’affaires total |
| Nombre | Compte les valeurs. | Nombre de ventes |
| Moyenne | Calcule la moyenne des valeurs. | Montant moyen des charges patronales |
| Maximum | Affiche la valeur la plus élevée. | Meilleure vente |
| Minimum | Affiche la valeur la plus faible. | Vente la moins élevée |
Pourquoi Excel affiche-t-il Nombre au lieu de Somme ?
La colonne source contient probablement au moins une cellule vide, un texte, une erreur ou une valeur numérique enregistrée sous forme de texte. Corrigez la source, actualisez le TCD puis sélectionnez Somme dans les paramètres du champ.
Renommer un champ de valeurs
Excel utilise automatiquement des titres comme Somme de Charges patronales ou Moyenne de Charges patronales. Ces intitulés peuvent être remplacés par des noms plus explicites.
Dans la fenêtre Paramètres des champs de valeurs, modifiez la zone Nom personnalisé. Vous pouvez par exemple remplacer Somme de Charges patronales par Chiffre d’affaires.
Le nouveau nom doit être différent du nom exact de la colonne source. Si la colonne s’appelle Charges patronales, Excel n’acceptera pas directement ce même intitulé pour le champ de valeurs.
Modifier le format des nombres
Le format doit être défini dans les paramètres du champ de valeurs, plutôt que directement sur quelques cellules du TCD. De cette manière, il est conservé lorsque le tableau est actualisé ou réorganisé.
Depuis la fenêtre Paramètres des champs de valeurs, cliquez sur Format de nombre, puis choisissez notamment :
- un format monétaire pour les montants
- un séparateur de milliers
- le nombre de décimales
- un format en pourcentage
- un format de date lorsque le contenu du champ le permet
Afficher plusieurs statistiques à partir du même champ
Un même champ peut être placé plusieurs fois dans la zone Valeurs. Cette possibilité permet par exemple d’afficher simultanément :
- la somme des charges patronales
- le nombre de valeurs enregistrées
- la moyenne des charges patronales
Faites simplement glisser une nouvelle fois le champ Charges patronales dans la zone Valeurs, puis choisissez une fonction différente pour chaque occurrence.
Lorsque l’indicateur recherché nécessite une formule qui n’existe pas dans la base, une autre méthode est possible. Elle est présentée dans le tutoriel consacré à l’ajout d’un champ calculé dans un tableau croisé dynamique.
Afficher les valeurs sous différentes formes
La fenêtre Paramètres des champs de valeurs ne sert pas uniquement à choisir une somme ou une moyenne. Dans la capture, l’onglet Afficher les valeurs du champ Charges patronales donne accès à des calculs comme % du total général, % du total de la colonne, % du total de la ligne, Différence par rapport ou Résultat cumulé par.
Afficher le pourcentage du total général
Pour connaître la part des charges patronales représentée par chaque élément du tableau :
- ajoutez une seconde fois le champ Charges patronales dans la zone Valeurs
- ouvrez les paramètres de cette seconde occurrence
- activez l’onglet Afficher les valeurs
- choisissez % du total général
- appliquez un format en pourcentage
- renommez le champ, par exemple Part des charges patronales
Le tableau affiche alors deux occurrences du même indicateur : la première conserve les montants, tandis que la seconde présente la part de chaque valeur dans le total général. Cette méthode permet de comparer directement les valeurs absolues et leur poids relatif sans ajouter de formule dans la base.
Le même menu contient d’autres possibilités de comparaison. Pour suivre une évolution entre deux périodes ou deux catégories, consultez la méthode consacrée à calculer une variation dans un tableau croisé dynamique.
Filtrer un tableau croisé dynamique
Le filtrage permet de limiter l’analyse à une partie des données sans modifier la base source. Les filtres peuvent être appliqués à l’ensemble du rapport, aux lignes ou aux colonnes.
Utiliser la zone Filtres
Un champ qui ne fait pas partie des lignes ou des colonnes peut être placé dans la zone Filtres. Une liste déroulante apparaît alors au-dessus du tableau.
Ouvrez cette liste pour choisir une valeur. Pour en conserver plusieurs, cochez l’option Sélectionner plusieurs éléments, puis activez les valeurs souhaitées. Le filtre s’applique à l’ensemble du rapport.
Filtrer les étiquettes de lignes
La flèche placée à côté d’un champ de lignes permet de choisir les éléments à afficher. Dans le tableau de l’exemple, ce principe peut être utilisé sur le champ Équipe pour ne conserver que certaines équipes.
Il est également possible d’utiliser :
- les filtres d’étiquettes pour rechercher un texte précis
- les filtres de valeurs pour afficher les meilleurs ou les moins bons résultats
- la zone de recherche pour retrouver rapidement une valeur dans une longue liste
Filtrer les étiquettes de colonnes
Le même principe s’applique aux champs placés dans la zone Colonnes. La capture ouvre le filtre du champ Division et permet de conserver ou de masquer les colonnes Asia, Canada, Europe, South America et United States.
Effacer un filtre
Pour supprimer un filtre, ouvrez la liste correspondante puis sélectionnez Effacer le filtre de…. Vous pouvez également réactiver l’option Sélectionner tout.
Lorsqu’un résultat semble manquer, vérifiez toujours les filtres appliqués au rapport, aux lignes et aux colonnes. Un ancien filtre peut rester actif après l’actualisation des données.
Trier les résultats
Les éléments du tableau peuvent être triés :
- par ordre alphabétique
- du plus petit au plus grand
- du plus grand au plus petit
- en fonction d’un champ de valeurs précis
Pour classer les équipes selon leur chiffre d’affaires, cliquez avec le bouton droit sur une valeur de Somme de CA Vente, puis choisissez Trier et Du plus grand au plus petit.
Lorsque le tableau contient de nombreuses dates, celles-ci peuvent également être réunies par période : grouper les données d’un tableau croisé dynamique.
Actualiser un tableau croisé dynamique
Lorsque la base source est modifiée, les changements ne sont JAMAIS reportés immédiatement dans le TCD. Il faut actualiser le tableau pour intégrer les nouvelles valeurs.
Actualiser un seul tableau
Cliquez dans le tableau croisé dynamique, puis utilisez l’une des méthodes suivantes :
- ouvrez l’onglet Analyse du tableau croisé dynamique et cliquez sur Actualiser
- effectuez un clic droit dans le TCD et choisissez Actualiser
Actualiser tous les tableaux du classeur
La commande Actualiser tout met à jour l’ensemble des tableaux croisés dynamiques et des connexions de données présents dans le classeur.
Cette commande est utile lorsque plusieurs TCD ont été construits à partir de la même source ou lorsque le classeur contient plusieurs sources de données.
Actualiser le tableau à l’ouverture du fichier
Pour demander une actualisation à chaque ouverture :
- cliquez dans le tableau croisé dynamique
- ouvrez les options du tableau croisé dynamique
- activez l’onglet Données
- cochez Actualiser les données lors de l’ouverture du fichier
- validez avec OK
Pourquoi les nouvelles lignes n’apparaissent-elles pas ?
Si le TCD a été créé à partir d’une plage de cellules classique, les nouvelles lignes ajoutées sous la plage peuvent ne pas appartenir à la source initiale.
Cliquez dans le TCD puis sélectionnez Changer la source de données. Vérifiez que la nouvelle plage inclut toutes les lignes et toutes les colonnes nécessaires.
L’utilisation d’un tableau Excel comme source réduit fortement ce problème, car la plage s’étend automatiquement lorsque de nouvelles lignes sont ajoutées.
Une synthèse peut aussi être représentée visuellement
Lorsque les résultats doivent être intégrés à un tableau de bord ou présentés à d’autres utilisateurs, vous pouvez également créer un graphique croisé dynamique.
Questions fréquentes
Pourquoi le tableau croisé dynamique affiche-t-il Nombre au lieu de Somme ?
Excel utilise généralement Nombre lorsque la colonne source contient du texte, des valeurs non numériques ou certaines cellules vides. Corrigez les données, actualisez le tableau puis sélectionnez Somme dans les paramètres du champ de valeurs.
Pourquoi certaines données n’apparaissent-elles pas ?
Vérifiez les filtres du rapport, des lignes et des colonnes. Contrôlez également que toutes les lignes appartiennent bien à la source du TCD, puis actualisez le tableau.
Comment afficher un pourcentage dans un tableau croisé dynamique ?
Ajoutez une seconde fois le champ Charges patronales dans la zone Valeurs, ouvrez ses paramètres puis choisissez une présentation comme % du total général. Appliquez ensuite un format en pourcentage.
Peut-on utiliser plusieurs fois le même champ dans la zone Valeurs ?
Oui. Un même champ peut être ajouté plusieurs fois afin d’afficher simultanément une somme, une moyenne, un nombre ou un pourcentage.
Comment modifier le format des nombres ?
Ouvrez les paramètres du champ de valeurs puis cliquez sur Format de nombre. Le format choisi sera conservé lorsque le tableau sera réorganisé ou actualisé.
Comment actualiser un tableau croisé dynamique ?
Cliquez dans le TCD puis choisissez Actualiser dans l’onglet Analyse du tableau croisé dynamique. La commande Actualiser tout permet de mettre à jour tous les tableaux et toutes les connexions du classeur.
Vous souhaitez mieux exploiter les tableaux croisés dynamiques ?
La création et l’utilisation des TCD peuvent être intégrées à une formation Excel en entreprise.
