Qu’est-ce qu’un tableau croisé dynamique sur Excel ?

Pourquoi faire un tableau croisé dynamique ?

Un tableau croisé dynamique est l’une des fonctionnalités les plus puissantes de Microsoft Excel. Elle permet de synthétiser et d’effectuer des calculs sur un grand nombre de données. Avec un TCD, vous pouvez : 

• Effectuer des calculs par catégorie ou sous-catégorie ;
• Filtrer, trier et agglomérer des catégories présentes dans la liste des champs ;
• Inverser votre tableau en mettant les lignes en colonnes et inversement ;
• Calculer des moyennes, sommes, sous-totaux ;
• Agglomérer plusieurs données sources ;
• Avoir une vue concise des résultats et obtenir facilement le détail ligne à ligne ;
• …

Vous avez beau avoir des dizaines de milliers de lignes, vous êtes en capacité de produire des états synthétiques en quelques secondes. Dans l’exemple ci-dessous, j’ai un onglet qui contient presque 17 000 lignes correspondant aux ventes de jeux vidéo par type et par éditeur lors des 30 dernières années. En quelques secondes, j’ai pu créer un tableau croisé dynamique des ventes chez Nintendo par catégorie.

Si j’avais décidé de me passer du TCD, j’aurais dû utiliser des formules Excel du type SOMME.SI ou SOMME.SI.ENS. La puissance de calcul requise pour générer le tableau dans cette feuille de calcul aurait été plus importante. Le temps consacré pour produire le tableau aurait été beaucoup plus important.

Comment fonctionne un tableau croisé dynamique ?

Un TCD est un tableau puissant de calcul directement relié à une ou plusieurs sources de données. Il sert à analyser des bases de données.

Dans l’onglet où sont présentes les données, chaque colonne, pour autant que les informations soient correctement renseignées, peuvent servir dans votre TCD.

Le tableau est dit dynamique puisque vous pouvez changer les données (catégories en ligne et en colonne, éléments à calculer) par de simples glisser-déposer. A chaque modification du tableau, il sera actualisé. Cela signifie que toutes les données seront recalculées.

Sur Excel, dès lors que vous modifiez la moindre information, toutes les cellules présentes dans vos onglets sont calculées de nouveau. Lorsque votre plage de cellules comporte des calculs puissants, votre fichier peut souffrir de ralentissement.

Avec un tableau croisé dynamique, celui-ci n’est calculé qu’en cas de modification de celui-ci ou lorsque vous faites la manipulation pour l’actualiser. La puissance de calcul exigée par Excel est moins importante.

Comment réaliser un tableau croisé dynamique sur Excel ?

Pour la majorité des personnes, créer un tableau croisé dynamique semble être un exercice insurmontable et exigeant beaucoup de connaissances. Il n’y a rien de plus faux. Autant sur Excel il peut être difficile de trouver la bonne formule à mettre en œuvre, autant il est très facile de produire vos premiers tableaux croisés dynamiques. Téléchargez à présent le jeu de données et suivez pas-à-pas ce tutoriel.

Nettoyer les bases de données

Avant de produire votre TCD, il est important de vérifier la qualité de vos données sources. En effet, comme on dit dans mon métier “Garbage in, garbage out” (entrées défectueuses, sorties défectueuses).

Pour cela, vos données doivent être en format base de données. Chaque colonne doit être correctement nommée et si certaines d’entre elles sont des catégories, veillez à ce qu’il n’y ait pas de faux doublons (ex: “Nintendo” et “Niintendo”).

Les colonnes contenant des données numériques ne doivent pas contenir d’autres types de données telles que du texte.

Le gros avantage des TCD est que lorsque votre plage de données source changera (nouvelles lignes, lignes modifiées,…), il sera très facile d’actualiser votre tableau (par un simple clic-droit). Aucune crainte à avoir sur la mise à jour du tableau.

Contrairement à l’exemple donné, veillez à donner des noms évocateurs à vos colonnes. Par la suite, ce sont ces éléments que vous viendrez manipuler. Si votre base de données comporte plusieurs dizaines de colonnes, vous ne voudriez pas confondre les colonnes entre elles.

Avant de générer le tableau croisé dynamique, vérifiez bien que votre source de données ne comporte pas d’entête vide, de colonne vide, de ligne vide, de sous totaux,… Le format doit être le plus “brut” possible.

Créer le tableau croisé dynamique

Placez sur une cellule de votre base de données puis rendez-vous dans l’onglet Insertion du ruban. Sur l’extrême gauche, il doit être indiqué tableau croisé dynamique. Cliquez dessus.

Par défaut, si vos données sources sont correctement saisies, Excel reconnaîtra l’étendue de votre plage de données. 

Il va ouvrir une page Créer un tableau croisé dynamique en y indiquant la source de données. Si vous pensez ajouter des lignes par la suite, je vous invite à enlever les nombres dans les coordonnées de la plage de données. Ainsi vous indiquez à Excel que vous voulez des colonnes entières

Ex : “vgsales!$A:$K” vs “vgsales!$A$1:$K$16599”

Vous avez la possibilité de créer le TCD dans un nouvel onglet ou de le mettre dans un onglet existant. Si c’est la première fois que vous faites un tableau de ce type, je vous invite à l’intégrer dans une nouvelle feuille de calcul.

Une fois que vous aurez cliqué sur Ok vous obtiendrez la création d’un tableau dans un nouvel onglet. Le résultat devrait ressembler à la capture suivante.

Dans le même temps, si vous sélectionnez l’une des cellules du tableau, vous devriez voir apparaître sur la droite de votre écran la liste des champs de données. 

Si ça n’est pas le cas, je vous invite à faire un clic-droit sur votre TCD et sélectionner Afficher la liste des champs.

A noter qu’il est parfaitement possible de créer un tableau croisé dynamique à partir de données disponibles dans un autre classeur. Cependant, je le déconseille. Si le chemin d’accès à votre document venait à changer, il serait plus difficile de mettre à jour votre tableau et les nouveaux enregistrements (nouvelles données) ne seraient pas intégrés.

Au moment de la création du tableau croisé dynamique, il vous était possible de créer un graphique croisé dynamique. Les principes sont les mêmes que ceux présentés dans ce tutoriel. Même si cette fonctionnalité permet de produire rapidement des graphiques, je dois vous avouer que je m’en suis peu servi dans mon quotidien de contrôleur de gestion. Les représentations étant moins esthétiques qu’un graphique fait par d’autres moyens, je les évite.

Mise en forme du TCD

A présent, nous allons pouvoir sélectionner les données qui nous intéresse dans notre tableau croisé dynamique. Nous allons sélectionner les étiquettes de lignes et les étiquettes de colonnes ainsi que les champs numériques que nous souhaitons calculer.

Pour cela, dans la partie droite de l’écran, nous allons sélectionner les données souhaitées. En Lignes, nous souhaitons avoir l’éditeur de jeux vidéos (publisher) ainsi que le genre. En entêtes de colonnes, nous voulons les années. Je souhaite ici avoir l’information des ventes en Europe. Je sélectionne le champ EU_Sales et je le dépose dans l’encart valeur.

Il peut arriver qu’au lieu d’afficher Somme de EU_Sales, Excel indique Nombre de EU_Sales. Le résultat étant qu’il affichera le nombre de lignes présentes ce qui n’a pas d’intérêt pour nous. 

Pour changer, faites un clic droit sur le champ dans la section Valeurs puis sélectionnez Paramètres de champ. La fenêtre suivante apparaît. Sélectionnez alors Somme pour que le TCD additionne les valeurs du champ. 

Vous devriez obtenir le résultat suivant.

Comme vous avez pu le constater, il existe plusieurs fonctions possibles : 

• somme
• nombre
• moyenne
• max
• min
• …

Ajouter un champ de données

Tout comme vous venez d’ajouter des étiquettes de lignes et des étiquettes de colonnes ainsi que des données numériques, vous pouvez sélectionner d’autres éléments.

Si vous cochez directement les champs dans la partie supérieure de la section champs de tableau croisé dynamique (encart sur la droite de votre écran), Excel effectuera des opérations différentes suivant le type de données.

• Si vous sélectionnez un champ au format texte, il l’ajoutera en tant qu’étiquette de lignes ;
• Si vous sélectionnez un champ dans lequel sont des données texte de type catégorie (ex: éditeur de logiciel ou plateformes), il l’ajoutera dans la section valeurs en indiquant le nombre de valeurs ;
• Si vous sélectionnez un champ numérique (ex: ventes au Japon), il l’ajoutera à la section valeur et effectuera la même opération que les autres champs numériques (somme dans notre exemple).

Supprimer un champ du TCD

Pour supprimer un champ de données du TCD, rien de plus simple : 

• Dans la partie droite Champs de tableau croisé dynamique, sélectionnez le champ indésirable et glissez le vers la liste des champs.
• Vous pouvez également, identifiez le champ dans la liste et le décocher.
• Dernière solution, vous pouvez faire un clic-droit sur votre champ dans le TCD et cliquer sur Supprimer “nom du champ”.

Réorganiser le TCD

Il vous est parfaitement possible de réorganiser le TCD de façon à obtenir la mise en forme souhaitée. Pour cela, vous pouvez déplacer les lignes en colonnes et inversement.

Vous avez également la possibilité de déplacer un champ de données dans la section Filtres. Vous pourrez filtrer le tableau sur une catégorie donnée.

Dans l’exemple ci-dessus, j’ai placé le type de jeu vidéo en tant que filtre du tableau et j’ai sélectionné le genre Shooter. 

A titre d’exemple, je vais déplacer le Champ Year directement afin de filtrer les données sur les années de 2010 à 2016.

Synthétiser les valeurs

Voici une étape optionnelle mais qui peut être fort pratique suivant le type de tableau que vous souhaitez produire.

Vous avez la possibilité d’afficher des valeurs en tant que pourcentage du total général, du total de la colonne, du total de la ligne,…

Pour cela, faites un clic-droit sur le champ sur lequel vous souhaitez effectuer l’opération puis rendez-vous dans Afficher les valeurs. Vous aurez la possibilité de sélectionner l’une des opérations voulues.

Le résultat est intéressant puisqu’on se rend compte facilement du poids de chaque éditeur de jeux vidéos dans le total des ventes sur le genre et les années sélectionnées.

Mettre en forme un TCD

Il existe des possibilités de personnalisation du tableau croisé dynamique. Lorsque vous sélectionnez l’une des cellules du tableau, deux nouveaux onglets vont apparaître dans la barre d’outils. 

L’onglet Création va vous permettre d’accéder à plusieurs options très intéressantes.

Afficher et masquer les sous-totaux

La première option qui vous est laissée par Excel est d’afficher ou non les sous totaux dans votre tableau. Vous avez notamment la possibilité d’afficher le sous total au-dessous ou en-dessous de la catégorie comme dans les exemples ci-dessous.

Cette option n’est valable et intéressante que si vous avez placé plusieurs catégories en tant qu’étiquettes de ligne.

Afficher et masquer les totaux généraux

Grâce au menu Totaux généraux, vous avez la possibilité d’afficher et de masquer les totaux généraux par ligne et/ou par colonne.

Je vous invite à le faire si vous avez sélectionné plusieurs champs numériques dont la somme n’a aucun intérêt. En effet, si vous avez sélectionné la somme des véhicules par foyer ainsi que la somme des revenus par loyer, additionner ces deux valeurs n’a aucun intérêt.

Modifier la disposition du TCD

Par défaut, lorsque vous générez un tableau croisé dynamique, il se met sous forme compactée.

Il existe d’autres options d’affichage. Le mode plan va placer les différentes catégories sélectionnées en étiquettes de ligne dans des colonnes différentes. C’est très appréciable dès que vous avez plus de 2 champs.

Le mode tabulaire est proche du mode plan à la différence près que le sous-total est affiché en bas.

Développer et réduire les champs

Lorsque vous travaillez avec un tableau croisé dynamique, lorsque vous avez au moins 2 étiquettes en ligne ou en colonne, vous pouvez vouloir masquer un champ.

Dans votre TCD, dès lorsque que vous avez plusieurs niveaux d’étiquettes de données, sur les niveaux supérieur est affiché un “-/+” vous laissant la possibilité de masquer ou afficher le détail.

Si vous souhaitez effectuer la même opération sur l’ensemble des lignes, vous pouvez le faire en effectuant un clic droit sur la catégorie à réduire puis sélectionner réduire le champ entier (ou Développer le champ entier pour l’opération inverse).

Travailler avec un TCD

Lorsque vous produisez un TCD, vous allez vouloir effectuer certaines opérations. De temps en temps, vous voudrez actualiser les résultats affichés en fonction des nouvelles données.

Il vous arrivera aussi de vouloir consulter les données source pour comprendre les valeurs affichées.

Plus rarement, vous serez amené à déplacer le TCD et à le supprimer.

C’est ce que nous allons voir tout de suite.

Comment actualiser un TCD

Votre TCD est connecté à votre base de données. Comme expliqué précédemment, les résultats affichés ne s’actualisent pas automatiquement contrairement aux calculs affichés dans les autres onglets.

Ce qui peut sembler être un défaut est en fait une très grande qualité. En effet, le recalcul demande de la puissance de calcul pour votre tableur préféré. Le recalcul périodique a le gros avantage de rendre vos fichiers plus légers et plus rapides.

Pour actualiser votre tableau croisé dynamique, vous avez plusieurs possibilités.

La première possibilité est de provoquer une actualisation manuellement. Pour cela, cliquez sur une cellule du TCD et sélectionnez Actualiser. Le raccourci clavier Alt+F5 aura le même effet. Pratique !

Si vous avez plusieurs TCD dans votre document Excel, effectuer cette opération manuellement peut être long. Rendez-vous dans l’onglet Données de votre ruban et cliquez sur Actualiser tout. Tous votre TCD seront mis à jour sur la base des nouvelles données.

A noter que le format de votre tableau peut varier après son actualisation. Excel va adapter la taille des colonnes aux nouvelles données. Vous pouvez éviter ça faites un clic-droit sur n’importe quelle cellule de votre tableau. Dans Options du tableau croisé dynamique, trouvez la section Disposition et décochez l’option Ajuster automatiquement la largeur des colonnes lors de la mise à jour.

Toujours dans les options du tableau croisé dynamique, vous avez la possibilité de demander à Excel d’actualiser le TCD dès l’ouverture du fichier. L’option se situe dans le menu Données et cochez Actualisez les données lors de l’ouverture du fichier.

Explorer le détail des calculs

Au quotidien, lorsque vous travaillez avec un tableau croisé dynamique, il peut arriver de ne pas comprendre les résultats affichés. C’est peut être parfaitement logique. Toujours est il que vous voulez pouvoir vérifier les données source.

Pour ça rien de plus simple, il vous suffit de double-cliquer sur la valeur suscitant votre incompréhension. Excel va alors créer un nouvel onglet avec un extrait des données sources entrant dans le calcul.

Vous pourrez par la suite supprimer cet onglet. Il n’a d’autre intérêt que la lecture et l’analyse que vous lui portez.

A l’inverse, vous pouvez vouloir empêcher l’exploration des détails. Si vous travaillez dans la paie, vous pouvez vouloir transmettre la somme des rémunérations par service sans vouloir pour autant autoriser à ce que les individus consultent le détail pour chaque salarié. Dans la section Données des Options du tableau croisé dynamique, décochez l’option Activer l’affichage des détails.

Comment déplacer un TCD

Si vous souhaitez changer l’emplacement du tableau croisé dynamique dans un nouvel onglet ou dans un nouveau classeur, vous avez la possibilité de le faire.

Pour cela sélectionnez l’ensemble de la plage de cellules composant le TCD. Ensuite, tapez en même temps ctrl+X (couper). Sélectionnez la cellule où vous souhaitez déposer le tableau et tapez sur ctrl+V (coller).

Supprimer un TCD

Pour supprimer le TCD, le plus simple est de sélectionner les lignes ou les colonnes qui l’accueillent. Ensuite, faites un clic droit sur la partie mise en exergue et cliquez sur Supprimer. Votre tableau a été effacé.

Résumé de qu’est ce qu’un tableau croisé dynamique dans Excel

Dans cet article, je vous ai présenté ce qu’est un tableau croisé dynamique et ce qu’il permettait de réaliser au quotidien. Il vous permet de créer des analyses succinctes et puissantes grâce à une base de données préalablement alimentée.

Vous avez la possibilité d’ajouter des champs, d’en supprimer, d’intégrer une ou plusieurs étiquettes de lignes et de colonnes.

Nous avons vu ensemble comment mettre en forme votre tableau croisé dynamique et procéder à des calculs intéressants de nos données.

Il reste bien d’autres choses à découvrir sur l’utilisation des TCD comme la création de champs calculés et d’éléments calculés, la personnalisation des regroupements de données, la possibilité d’intégrer un segment ou une chronologie,…

Cependant, je pense que vous avez là une bonne base de travail et que vous avez enfin la réponse à la question “Qu’est-ce qu’un tableau croisé dynamique”.