Editions ENI Livres | Vidéos | e-Formations
Les tableaux croisés dynamiques
Utilité et fonctionnement des tableaux croisés dynamiques
Un tableau croisé dynamique, ou TCD, est un outil d’Excel qui permet de synthétiser, croiser et analyser rapidement de grandes quantités de données. À partir d’une base structurée, il permet de regrouper les informations selon différents critères, sans avoir à écrire de formules complexes.
Il est particulièrement utile lorsque l’on souhaite obtenir rapidement des synthèses, comparer plusieurs dimensions d’analyse ou modifier l’angle de lecture des résultats en fonction des besoins. Les TCD permettent ainsi de travailler sur des quantités importantes de données en passant rapidement d’une vue simple à des analyses plus détaillées, tout en conservant la même source de données.
Dans cette première section, nous commencerons par un aperçu de ce que permet un tableau croisé dynamique, afin de mieux comprendre à quoi il sert et ce qu’il peut produire comme synthèses. Nous verrons ensuite les repères théoriques nécessaires pour comprendre la suite du chapitre : vocabulaire essentiel, organisation d’un TCD, et situations dans lesquelles son usage est particulièrement pertinent.
Les sections suivantes montreront comment préparer correctement les données sources, créer un premier tableau croisé dynamique, en modifier la présentation, enrichir les calculs affichés et interpréter différents types d’analyses. Nous aborderons enfin la mise à jour des TCD, ainsi que quelques fonctionnalités complémentaires comme les TCD recommandés.
Exemples de synthèses possibles avec un TCD
Avant d’entrer dans la construction détaillée d’un TCD, voyons le type de synthèses qu’il permet d’obtenir à partir d’une même base de données. Les exemples qui suivent montrent comment un TCD peut faire apparaître des analyses de plus en plus détaillées en croisant plusieurs champs.
Ouvrez le fichier TCD.xlsx dans lequel nous allons réaliser notre premier tableau croisé dynamique.
Activez la feuille ListeVentes, dont voici un extrait. Elle contient plus de 1 100...
Préparer les données sources
La qualité d’un tableau croisé dynamique dépend directement de la qualité des données qui l’alimentent. Avant de créer un TCD, il est donc essentiel de vérifier que la source est correctement structurée et que les valeurs qu’elle contient sont cohérentes.
Règles à respecter
Comme pour les tableaux structurés, une base de données comporte toujours, en première ligne, les titres des champs. Chaque champ doit porter un nom et les en-têtes vides ne sont pas autorisés.
La base de données ne doit pas contenir de cellules fusionnées. Chaque champ doit avoir un nom unique et les lignes entièrement vides ne sont pas autorisées. Un champ correspond à une même information pour tous les enregistrements, tandis qu’une ligne doit regrouper tous les éléments permettant de décrire un seul enregistrement.
La dernière ligne de la base de données ne doit pas être une ligne de synthèse des données des lignes supérieures. Enfin, une base de données ne pourra alimenter un tableau croisé dynamique que si elle comporte au moins trois champs dont un de valeurs numériques.
Ce rappel est valable pour tous les types de tableaux, quelles que soient leurs finalités.
Vérifier et fiabiliser...
Créer un tableau croisé dynamique
Réaliser une première synthèse avec un TCD
Votre supérieur vous demande de vérifier que les commerciaux ont bien atteint leurs objectifs de 100 000 € chacun, et souhaite connaître quels sont les départements dans lesquels la société réalise le plus de vente.
Cette question en comporte, en fait, deux. Pour y répondre vous allez construire deux tableaux totalement différents.
Pour répondre à la première partie de la question, un affichage classique des totaux des ventes enregistrées par chaque commercial pour chaque département est la meilleure solution.
Ouvrez si besoin le fichier TCD.xlsx.
Pour réaliser ce tableau, cliquez dans le tableau de données Tableau1.
Cliquez dans l’onglet Insertion et, dans le groupe Tableaux, cliquez sur la liste déroulante du bouton Tableau croisé dynamique puis sélectionnez l’option À partir d’un tableau ou d’une plage.
La création du TCD
Dans la zone Tableau/Plage, vérifiez que le tableau Tableau1 est bien sélectionné.
Conservez l’option Nouvelle feuille de calcul cochée, puis cliquez sur OK.
La feuille du TCD est créée et activée et le volet Champs de tableau croisé dynamique s’affiche....
Mettre en forme le TCD
Dans un tableau croisé dynamique, la présentation joue un rôle essentiel dans la lisibilité des résultats. Il est souvent préférable de placer en lignes les intitulés les plus nombreux ou les plus longs, et de réserver les colonnes aux catégories plus limitées, comme les périodes. Les TCD permettent de tester facilement plusieurs dispositions en déplaçant simplement les champs.
Modifier la disposition du tableau croisé dynamique
La position relative des étiquettes dans les zones de colonnes ou de lignes n’affecte en aucun cas les résultats. Comparez ces deux dispositions :
La version Département par Commercial
La version Commercial par Département
N’oubliez pas de renommer les étiquettes dans le TCD.
Reprenez la disposition la plus lisible, soit la première, pour pouvoir modifier la présentation des sous-totaux.
Dans le volet Champs de tableau croisé dynamique, dans la zone Lignes, faites glisser l’étiquette Secteur sous l’étiquette Numéro de département, pour obtenir le nouveau tableau suivant.
Vous obtenez un nouveau tableau dans lequel le détail des chiffres d’affaires par Secteur est affiché pour chaque Département.
Dans le volet Champs de tableau croisé dynamique, faites un clic...
Exploiter et enrichir les TCD
Appliquer des fonctions de synthèse dans un TCD
Vous aurez probablement besoin, de quantifier l’étendue des ventes des commerciaux.
Vous pouvez afficher pour chaque élément les valeurs mini, les moyennes et les valeurs maxi.
Procédez ainsi :
Créez un nouveau tableau croisé dynamique en plaçant le champ Numéro de département dans la zone Lignes et le champ Commercial dans la zone Colonnes du volet Champs de tableau croisé dynamique.
Déplacez trois fois l’étiquette Montant HT dans la zone Valeurs pour obtenir cette disposition :
La disposition des champs
Chaque étiquette a son propre indice : Somme de Montant HT, Somme de Montant HT2, Somme de Montant HT3.
Cliquez sur chaque étiquette, puis sélectionnez Paramètres des champs de valeurs et dans la boîte de dialogue Paramètres des champs de valeurs, modifiez le type de calcul et nom des champs pour obtenir la disposition suivante. Cliquez sur OK après chaque modification.
Modification de la fonction de calcul appliquée aux valeurs
Nouvelles fonctions appliquées aux valeurs
Le TCD affiche les nouvelles valeurs.
Le nouveau TCD : une amélioration visuelle est possible
Sélectionnez toute la feuille, faites un clic droit sur un en-tête de colonne et sélectionnez Largeur de colonne, puis appliquez une largeur de colonne de 11.
Sélectionnez les lignes 4 et 5 et faites un clic droit, puis sélectionnez l’option Hauteur de ligne. Appliquez-leur une hauteur de 45, dans l’onglet Accueil, cliquez sur le bouton Renvoyer à la ligne automatiquement.
Sélectionnez les cellules de B4 à M5, et dans l’onglet Accueil - groupe Police, dans la liste déroulante Bordures, sélectionnez Toutes les bordures.
Faites un clic droit dans le tableau et sélectionnez sur Options du Tableau croisé dynamique.
Sélectionnez l’onglet Disposition et mise en forme, cochez la case Fusionner et centrer les cellules avec les étiquettes, et décochez Ajuster automatiquement la largeur des colonnes lors de la mise à jour.
Fusion des cellules avec les étiquettes
Le tableau devient plus lisible :
La nouvelle disposition du tableau...
Mettre à jour les données
Un tableau croisé dynamique n’intègre pas automatiquement les modifications apportées aux données source. Lorsqu’un enregistrement est ajouté, supprimé ou modifié, il est donc nécessaire d’actualiser le TCD pour que les résultats affichés correspondent bien à l’état réel de la base. Cette section montre d’abord comment effectuer cette mise à jour manuellement, puis comment en faciliter l’automatisation lorsque les données évoluent fréquemment.
Actualiser un TCD après modification des données sources
Pour que votre tableau reflète le contenu exact des données sources, chaque fois que vous l’ouvrez, faites un clic droit dessus.
Un sous-menu s’affiche, cliquez sur Actualiser.
L’actualisation du TCD
Le tableau croisé dynamique est mis à jour.
Utiliser un tableau structuré pour faciliter la mise à jour
L’actualisation d’un tableau croisé dynamique ne suffit pas toujours à garantir un résultat propre si la plage de données source a été définie trop largement. Lorsque l’on prévoit des lignes vides en bas du tableau pour de futurs ajouts, celles-ci peuvent être prises en compte par le TCD et faire apparaître des éléments inutiles, comme la mention « (vide) » dans certaines listes ou certains champs.
Pour éviter cela, il est préférable d’utiliser comme source un tableau structuré correspondant exactement à la zone de données utile. Les nouveaux enregistrements saisis à la suite du tableau y seront intégrés automatiquement, sans introduire de lignes vides dans l’analyse.
Pour créer un tableau structuré, sélectionnez une cellule de la plage de données et cliquez dans l’onglet Insertion et le groupe Tableaux, sur le bouton Tableau.
La boîte de dialogue Créer...
Création de TCD à partir des suggestions d’Excel
Les tableaux croisés dynamiques recommandés permettent d’obtenir rapidement des propositions de synthèse à partir d’une base de données, sans construire soi-même le TCD dès le départ. Cette fonctionnalité peut être utile aussi bien pour les utilisateurs débutants, qui souhaitent être guidés, que pour les utilisateurs plus expérimentés, qui veulent explorer rapidement plusieurs possibilités de présentation et de croisement des données. Il suffit ensuite de sélectionner la proposition la plus pertinente et de l’adapter si nécessaire.
Ouvrez à nouveau le fichier Salariés.
Cliquez dans la plage de données de la feuille Liste, puis dans l’onglet Insertion et le groupe Tableaux, cliquez sur Tableaux croisés recommandés.
La boîte de dialogue Suggestions de tableaux croisés dynamiques s’ouvre.
Commencez par l’agrandir en l’étirant par le coin inférieur droit. Six petits points gris foncé s’y trouvent.
Cette boîte de dialogue est composée à gauche d’un bandeau pouvant défiler verticalement avec plusieurs propositions de tableaux croisés dynamiques et sur la droite une plage affichant, en plus détaillé...
Les graphiques croisés dynamiques, les GCD
Les graphiques croisés dynamiques ne sont en fait que des outils permettant de générer non pas des tableaux, pas toujours parlants, mais des graphiques.
La création d’un tel dispositif est de même niveau de difficulté qu’un tableau, ses possibilités sont identiques. Démonstration :
Ouvrez le fichier GCD Salariés.xlsx. Il possède une feuille Liste contenant un tableau structuré.
Sélectionnez une cellule de ce tableau puis, dans l’onglet Insertion et le groupe Graphiques, cliquez sur Graphique croisé dynamique.
La boîte de dialogue Créer un graphique croisé dynamique
Confirmez la sélection de la source ou modifiez-la. Utilisez une nouvelle feuille de calcul.
La feuille de Graphique qu’Excel vous propose d’alimenter
C’est une feuille que vous connaissez bien, mais elle comporte en plus une zone de graphique qui va refléter le tableau que vous allez construire. Toutes les modifications apportées au tableau seront reportées dans le graphique, et inversement, automatiquement.
Combien y a-t-il de personnes en CDD et en CDI par Service ?
La réponse est instantanée, sous deux formes :
Le couple Graphique / TCD
Combien y a-t-il de Contrats par Service et par Sexe ?
Le tableau et le graphique vous le disent...