Niveau : Débutant
Introduction : Comment faire des tableaux croisés dynamiques sous Excel ?
Dans cet article, vous allez voir les fondamentaux d'Excel
Cet article guide le lecteur à travers les bases d'Excel, en expliquant les fonctionnalités essentielles et leur utilisation. Voici les points principaux abordés :
Tableaux croisés dynamiques
Sommaire
1) Définition : Les tableaux croisés dynamiques
Dans ce cours excel, vous allez voir ce qu'est un tableau croisé dynamique ou TCD . C'est un outil d’Excel qui permet de présenter les données d’une base de données de manière synthétique, notamment en effectuant automatiquement des regroupements et des calculs.
Dans un TCD, on va « croiser » au moins un champ en ligne avec au moins un champ en colonne.
Il est relativement simple de modifier la disposition, les filtres et tris du TCD et les calculs sont immédiatement mis à jour (aspect « dynamique »).
De nombreuses options permettent de personnaliser la présentation d’un TCD. Le plus difficile est finalement de savoir ce que l’on veut obtenir et de maîtriser toutes les options disponibles pour parvenir au résultat escompté.
Les TCD sont donc considérés comme un outil d’Excel à la fois puissant et parfois difficile à utiliser.
Les TCD peuvent même etre intégré (et lié) dans le cadre de nos formation bureautique sous un document Word ? le saviez vous ?
A. Prérequis Excel : partir d’un tableau sous forme de base de données
Avant toute chose, les données qui vont être mises sous forme de TCD doivent être issues d’une base de données (Excel ou autre format).
Rappel
Une base de données structurée est un tableau où toutes les informations sont organisées avec précision.
Les en-têtes de colonnes, appelés champs, identifient les catégories d’informations, tandis que chaque ligne correspond à un enregistrement, représentant une fiche unique. Pour garantir la cohérence, chaque champ doit contenir des données homogènes (même type et catégorie, comme du texte ou des nombres).
Pour qu’une base soit exploitable dans Excel, il est crucial qu’il n’y ait ni lignes ni colonnes entièrement vides. Lors de la création d’un tableau croisé dynamique (TCD), au moins deux champs doivent avoir des enregistrements avec des contenus identiques, car le TCD regroupe les données sur la base de ces similitudes.
Enfin, une saisie rigoureuse est indispensable. Si nécessaire, un nettoyage préalable de la base de données permettra d’éliminer les incohérences ou erreurs, rendant ainsi les données prêtes pour une analyse efficace avec Excel et le pack office.
Dans nos formations Excel avancé ou formation excel tableau croisé dynamique, nous apprenons aux participants à crée, organiser, structurer correctement une base de donnée
Une base de données utilisable pour créer un tableau croisé dynamique (TCD) peut être saisie manuellement dans Excel ou importée depuis d'autres sources, comme un fichier Access ou un fichier texte (résultant par exemple d’une extraction depuis un logiciel de gestion). Pour les fichiers texte avec séparateurs, Excel propose un outil dédié dans l’onglet Données pour faciliter leur importation.
L’intérêt d’un TCD augmente avec le volume des données, que ce soit en termes de champs ou d’enregistrements. Plus la quantité d’informations est importante, plus le TCD est capable de synthétiser efficacement et de mettre en évidence des faits remarquables ou des tendances significatives. C’est un outil indispensable pour exploiter de grandes bases de données de manière rapide et pertinente.
Comment créer un tableau croisé dynamique ?
Supposons que l’on dispose d’une base de données prête à l’emploi.
Pour créer un TCD, il faut sélectionner une cellule quelconque de la base de données puis cliquer dans l’onglet INSERTION sur l’outil Tableau croisé dynamique
La boite de dialogue Créer un tableau croisé dynamique s’ouvre.
Dans le premier champ, veiller à ce que la base de données soit correctement sélectionnée, et
Corriger si nécessaire (si l’on ne veut pas tous les champs et/ou tous les enregistrements).
Il est conseillé d’avoir un seul TCD Excel, par feuille de données par souci de clarté et pour éviter le risque de chevauchement de différents TCD. (C'est une réponse à notre test Excel gratuit en ligne)
Dans le second champ, laisser a priori le choix par défaut Nouvelle feuille de calcul.
Cliquer sur le bouton OK.
Une nouvelle feuille est insérée.
Comment mettre à jour les onglets d’un tableau croisé dynamique
Dans le ruban apparaissent 2 nouveaux onglets à droite d’AFFICHAGE lorsque la cellule active fait partie du TCD (ou de l’espace réservé pour le créer) : ANALYSE et CRÉATION sous OUTILS DE TABLEAU CROISÉ DYNAMIQUE.
Ruban de l’onglet ANALYSE
Ruban de l’onglet CRÉATION
Sur le côté gauche de la feuille Excel créée se trouve un espace réservé pour la création du TCD (ou rapport).
Les champs et les zones d’un tableau croisé dynamique
Dans cette partie du cours excel avancé vous pouvez aller sur le côté droit de l’écran, le volet Champs de tableau croisé dynamique apparaît automatiquement lorsque la cellule active se trouve dans un TCD ou dans l’espace dédié à sa création. Ce volet permet de sélectionner et d’organiser les champs à inclure dans le tableau sous Excel.
Si ce volet n’est pas visible, vous pouvez l’afficher en cliquant sur Liste des champs, situé dans l’onglet ANALYSE du ruban. Cela garantit un accès rapide aux outils nécessaires pour personnaliser et manipuler votre TCD.
La disposition représentée ci-dessus est la disposition par défaut (Sections Champs et Zones empilée), mais elle peut être différente.
Le volet se divise en deux sections : Champs en haut et Zones en bas.
La section Champs affiche tous les champs de la base de données, généralement dans leur ordre d’origine.
La section Zones comprend quatre parties : FILTRES, COLONNES, LIGNES, et VALEURS.
Dans les zones LIGNES et COLONNES, les champs sélectionnés servent à créer les en-têtes du tableau croisé dynamique (TCD). Les données identiques sont regroupées pour produire un tableau de synthèse.
La zone VALEURS contient le ou les champs utilisés pour les calculs automatiques. Si les données sont numériques, Excel effectue une somme par défaut. Si elles ne le sont pas, un dénombrement (fonction NBVAL) est appliqué.
La zone FILTRES permet de définir des critères de tri et de filtrage pour affiner les données affichées dans le TCD.
Ajout des champs dans le d’un tableau croisé dynamique
Il y a 2 méthodes pour ajouter ou modifier/supprimer des champs dans un TCD : par cliquer/glisser avec la souris ou en cliquant sur des commandes des menus contextuels.
Les champs en ligne d’un tableau croisé dynamique
Pour ajouter un champ dans la zone LIGNES (ou une autre zone), cliquer sur le nom du champ dans la section Champs et le glisser dans la zone correspondante.
Dans la section Champs, le nom du champ apparaît en gras et la case à gauche du nom est cochée.
Le nom du champ apparaît dans la zone (LIGNES par exemple).
La zone réservée pour le TCD est modifiée. On y voit toutes les valeurs uniques du champ, ainsi que les libellés Étiquettes de lignes et Total général. N'hésitez pas à vous former en direct avec un formateur ! nous proposons des formations Excel en présentiel et distanciel !
Les champs en colonne d’un tableau croisé dynamique
Pour ajouter un champ dans la zone COLONNES (ou une autre zone), cliquer sur le nom du champ dans la section Champs et le glisser dans la zone correspondante.
Dans la section Champs, le nom du champ apparaît en gras et la case à gauche du nom est cochée.
Le nom du champ apparaît dans la zone (COLONNES par exemple).
La zone réservée pour le TCD est modifiée. On y voit toutes les valeurs uniques du champ, ainsi que les libellés Étiquettes de Colonnes et Total général.
Les champs de calcul d’un tableau croisé dynamique
Pour ajouter un champ dans la zone VALEURS (ou une autre zone), cliquer sur le nom du champ dans la section Champs et le glisser dans la zone correspondante.
Le nom du champ apparaît dans la zone VALEURS, précédé par défaut par le libellé Somme de (exemple : Somme de Montant). Une autre opération que la somme peut être effectuée.
La zone du TCD est modifiée. On y voit par défaut la somme des valeurs correspondant au croisement des entêtes des lignes à ceux des colonnes. Les totaux en ligne et en colonne ainsi que le total général sont calculés et affichés. La cellule en haut à gauche du TCD contient le même libellé que celui de la zone VALEURS (exemple : Somme de Montant).
Suppression d’un champ d’une zone d'un TCD sous Excel
Pour retirer un champ d’une zone et donc l’exclure du TCD, il suffit de cliquer-glisser le nom du champ hors de la section Zones dans le volet Champs de tableau croisé dynamique.
Changement d’un champ de zone
Pour déplacer un champ d’une zone à une autre, par exemple pour passer des en-têtes de lignes aux en-têtes de colonnes, cliquez-glissez le nom du champ depuis la zone source vers la zone cible dans le volet.
Autre méthode pour gérer les champs
Une alternative consiste à faire un clic droit sur le nom du champ, puis à sélectionner une commande appropriée dans le menu contextuel. Cette méthode permet d’ajouter, supprimer ou déplacer un champ facilement.
Ajout d’un champ d’un tableau croisé dynamique
Dans la section Champs, faire un clic droit sur le nom du champ puis dans le menu contextuel cliquer sur Ajouter (aux étiquettes de lignes par exemple).
Changement d’un champ de zone d’un tableau croisé dynamique
Dans la zone source, cliquer sur le nom du champ puis dans le menu contextuel cliquer sur Déplacer dans la zone (Étiquettes de colonnes par exemple).
Suppression d’un champ d’une zone
Pour supprimer un champ d’une zone, et donc du TCD, cliquer sur le nom du champ dans la zone puis dans le menu contextuel cliquer sur Supprimer le champ.
Remarque : il existe encore une autre méthode pour ajouter ou supprimer un champ du TCD.
Pour ajouter un champ dans le TCD, on peut aussi cocher la case située à gauche de son nom dans la section Champs. Si le type de contenu du champ n’est pas numérique, le champ est ajouté dans la zone LIGNES. Sinon le champ est ajouté dans la zone VALEURS.
Plusieurs champs de tableau croisé dynamique dans la même zone
S’il y a déjà un champ dans la zone LIGNES et que l’on ajoute un nouveau champ en cliquant sur la case située à gauche de son nom dans la section Champs, le nouveau champ sera ajouté également dans la zone LIGNES, sous le nom du champ déjà présent.
Dans un TCD Excel , lorsqu’un second champ est ajouté dans la zone LIGNES, il apparaît comme un sous-niveau du premier, créant ainsi une hiérarchie dans l’organisation des données.
Pour répartir les champs entre les lignes et les colonnes, il suffit de déplacer l’un d’eux dans la zone COLONNES.
Cela peut être fait en cliquant-glissant le champ de la zone LIGNES vers la zone COLONNES, ou en faisant un clic droit sur le nom du champ, puis en sélectionnant Déplacer dans la zone… dans le menu contextuel et en choisissant la zone souhaitée. Cette manipulation permet de structurer le tableau selon vos besoins d'analyse.
Dans Excel et un TCD : Si un champ est déjà présent dans la zone VALEURS et que vous ajoutez un nouveau champ contenant des données numériques (en cochant sa case dans la section Champs), ce dernier sera également placé dans la zone VALEURS, sous le champ existant. Par exemple, vous obtiendrez "Somme de Montant" suivi de "Somme de [Nom du nouveau champ]".
Lorsque plusieurs champs sont dans une même zone, leur ordre peut être ajusté. Pour cela, vous pouvez cliquer-glisser un champ vers le haut ou le bas de la zone, ou effectuer un clic droit sur le nom du champ et utiliser une commande parmi les 4 premières options du menu contextuel. Cette flexibilité permet d’organiser vos données selon vos besoins d’analyse.
Les opérations sur les champs de calcul de tableau croisé dynamique
Quand on ajoute un champ numérique dans la zone VALEURS, l’opération effectuée par défaut dans le TCD est la somme. Mais cette opération peut être modifiée en cliquant sur le nom du champ dans la zone VALEURS puis en cliquant sur la commande Paramètres des champs de valeurs…
La boite de dialogue Paramètre des champs de valeurs s’ouvre.
Dans la liste Résumer le champ de valeur par (Somme est sélectionné par défaut), cliquer sur l’opération voulue (exemple : Moyenne) puis cliquer sur le bouton OK pour fermer la boite de dialogue et constater les modifications dans le TCD.
La fonction de calcul Nombre dans un TCD correspond à la fonction NBVAL d’Excel, qui compte toutes les cellules non vides (alphanumériques ou numériques). En revanche, la fonction de calcul Chiffres correspond à la fonction NB, qui ne compte que les cellules contenant des données numériques.
Lorsque le type de calcul est modifié dans la zone VALEURS, le libellé est automatiquement mis à jour (par exemple, "Moyenne de Montant" si vous choisissez de calculer une moyenne). Les valeurs affichées dans le TCD sont alors recalculées et reflètent les nouveaux paramètres, offrant une vue dynamique et adaptée à vos besoins d’analyse
D’autre part, le libellé de la cellule en haut à gauche du rapport est mis à jour et affiche le nom de l’opération effectuée suivi du nom du champ de la zone VALEURS (exemple : Moyenne de Montant).
Une autre méthode pour modifier l’opération de calcul dans un TCD consiste à sélectionner une cellule dans la zone VALEURS, puis à effectuer un clic droit. Sous Excel, Dans le menu contextuel, choisissez Synthétiser les valeurs par…, puis sélectionnez l’opération souhaitée dans le sous-menu (par exemple, Somme, Moyenne, NB, etc.). Cette méthode permet de changer rapidement le type de calcul appliqué sans passer par le volet des Champs.
La commande Autres options… ouvre la boite de dialogue Paramètres des champs de valeurs
Filtre et Tri : Champs utilisés dans le tableau croisé dynamique
Les champs placés dans les zones LIGNES ou COLONNES apparaissent dans le TCD avec des libellés par défaut, tels que Étiquettes de lignes ou Étiquettes de colonnes. Chaque libellé est accompagné d’une flèche qui, lorsqu’on clique dessus, ouvre un menu contextuel.
Ce menu offre plusieurs options :
Tri croissant ou décroissant des étiquettes.
Filtrage par occurrence, permettant de cocher ou décocher les étiquettes à afficher ou masquer.
Filtres avancés, accessibles via l’option Filtres dans le menu, qui propose des possibilités supplémentaires pour affiner l’affichage des données.
Lorsqu’un tri est appliqué sur une étiquette dans un TCD, une petite flèche (vers le haut pour un tri croissant ou vers le bas pour un tri décroissant) apparaît sur le bouton ouvrant le menu contextuel de tri et de filtre.
Si un filtre est actif sur une étiquette, un petit entonnoir s’affiche à deux endroits : sur le bouton du menu contextuel de filtre et tri de l’étiquette concernée, ainsi qu’à droite du nom du champ dans la section Champs de TCD. Ces indicateurs visuels permettent de repérer facilement les tris ou filtres en cours dans votre tableau croisé dynamique.
Pour supprimer un filtre, ouvrir le menu contextuel du champ et cliquer sur Effacer le filtre de (nom du champ).
Autre méthode pour filtrer ou trier d'un TCD
Dans la section Champs de TCD, il est possible de cliquer à droite du nom d’un champ pour ouvrir le même menu et sous-menu de filtre et tri.
Remarque : Cette méthode fonctionne uniquement si le champ est déjà utilisé dans le TCD, c’est-à-dire si la case devant son nom est cochée dans la section Champs. Si le champ à filtrer n'est ni dans la zone LIGNES ni dans la zone COLONNES, il doit être déplacé dans la zone FILTRES. Pour cela, faites un cliquer-glisser du champ vers la zone FILTRES, ou utilisez le menu contextuel en cliquant droit sur le nom du champ, puis en sélectionnant Déplacer dans la zone FILTRES.
Une zone de filtrage apparaît alors au-dessus du TCD.
Cliquer sur le bouton pour ouvrir le menu contextuel et cocher ou décocher les noms des occurrences qui doivent apparaître ou pas.
Méthode classique de création d’un TCD
Dans l’onglet ANALYSE, cliquer sur Options après avoir cliqué sur la liste déroulante Options du tableau croisé dynamique (à gauche du ruban)
Dans la boite de dialogue Options du tableau croisé dynamique qui s’ouvre, cocher la case Disposition classique du tableau croisé dynamique (glisser de champs dans la grille) dans l’onglet Affichage puis cliquer sur le bouton OK pour fermer la boite de dialogue.
La zone réservée pour la création du TCD est modifiée
Il suffit ensuite de faire un cliquer/glisser des noms de champs de la section Champs du volet
Du Champs de TCD vers la zone voulue du TCD, comme on le ferait vers les 4 zones de la section Zones de ce volet. Remarque : au lieu d’avoir Étiquettes de lignes et Étiquettes de colonnes, on a à l’affichage les noms des champs.
On peut aussi déplacer ou supprimer les champs du TCD en faisant un cliquer glisser vers une autre zone du TCD ou à l’extérieur du TCD.
Modification de la source de données du tableau croisé dynamique (TCD)
Lorsque vous créez un TCD, Excel sélectionne automatiquement toute la base de données source par défaut. Si vous devez modifier cette source ultérieurement, cliquez sur l’onglet ANALYSE et choisissez l’option Changer la source de données.
Une boîte de dialogue intitulée Modifier la source de données du TCD s’ouvre. Sélectionnez la nouvelle plage de données à utiliser, mettez-la à jour dans le champ de saisie, puis cliquez sur OK.
Le tableau croisé dynamique est alors actualisé automatiquement dès que la boîte de dialogue est fermée, reflétant les modifications apportées à la source des données.
Actualisation des données
Si on modifie seulement des données saisies dans la base de données source (noms de champs et/ou contenus des enregistrements) sans changer la plage de données source, le TCD n’est pas mis à jour. Il faut alors forcer l’actualisation du TCD en cliquant sur l’outil Actualiser de l’onglet ANALYSE.
Déplacement d’un TCD
Dans Excel, pour déplacer un TCD, cliquer sur Déplacer le TCD après avoir cliqué sur Actions dans l’onglet ANALYSE. Dans le champ Emplacement, saisir ou mieux sélectionner la cellule en haut à gauche du nouvel emplacement puis cliquer sur le bouton OK.
Sélection d’un TCD
Il est possible de sélectionner tout ou partie d’un tableau croisé dynamique (TCD). Pour cela, rendez-vous dans l’onglet ANALYSE, cliquez sur Actions, puis sur Sélectionner. L’option TCD complet permet de sélectionner le tableau en entier ou de choisir des éléments spécifiques comme les étiquettes et/ou les valeurs. Vous pouvez également supprimer le TCD en appuyant sur la touche Suppr après l’avoir sélectionné.
Une autre méthode sous Excel consiste à cliquer à gauche d’un en-tête de ligne ou en haut d’un en-tête de colonne. Une petite flèche noire apparaît alors (vers la droite pour les lignes, vers le bas pour les colonnes), permettant de sélectionner rapidement une ligne ou une colonne entière du TCD.
Format des valeurs
Dans Excel, Pour formater les valeurs numériques, on peut sélectionner le TCD ou toutes les valeurs puis appliquer un format aux nombres en utilisant les différentes options du groupe Nombre de l’onglet ACCUEIL.
Il est également possible de formater les nombres d’un TCD en utilisant le menu contextuel. Pour cela, faites un clic droit sur une cellule contenant une valeur dans le TCD ou sur un nom d’opération et de champ dans la zone VALEURS du volet Champs de tableau croisé dynamique. Dans le menu contextuel, sélectionnez l’option Paramètres des champs de valeurs….
Une boîte de dialogue intitulée Paramètres des champs de valeurs s’ouvre. Cliquez sur le bouton Format de nombre pour accéder à la boîte de dialogue Format de cellule, où vous pouvez personnaliser l’affichage des nombres (monétaire, pourcentage, nombre de décimales, etc.) selon vos besoins.
Suppression d’un TCD
Pour supprimer un TCD, cliquer sur Effacer tout après avoir cliqué sur Actions dans l’onglet ANALYSE.
Conclusion tableau croisé dynamique
C'est un cours Excel assez long mais nécéssaire pour vous donner les subtilité des TCD.
Les tableaux croisés dynamiques (TCD) dans Excel sont des outils puissants et flexibles pour analyser, synthétiser et visualiser vos données. Grâce à des fonctionnalités comme le tri, le filtrage, le changement de source de données ou le formatage des valeurs, les TCD permettent de manipuler facilement de grandes bases de données pour en extraire des informations clés.
Avec des compétences acquises lors d’une formation Excel ou une formation Excel avancé, les TCD deviennent un véritable levier pour optimiser vos analyses de données et gagner en efficacité.
Notre Organisme de Formation :
Excel formation est une marque de Kronoscope organisme de formation certifié qualiopi, spécialiste en formation Excel, Excel VBA, Power Query et PowerBI pour entreprise et particulier.
Nous proposons des formations de tous niveaux :
Formation Excel faux débutant
Pour les sociétés les formations ont lieu directement sur votre lieu de travail partout en France
Nous formons pour les particuliers dans le cadre de formation à distance CPF uniquement.
Nous avons des tests Excel gratuit en ligne pour évaluer votre niveau pour vous entrainer à la certification TOSA.
Ce sont des tests réalisés par nos formateurs spécialistes en Excel et sont d'un d'un grand niveau de fiabilité.
Voici l'ensemble des villes où nous pouvons intervenir sans frais de déplacement ni d'hébergement, car nous avons des formateurs locaux.
Formation Excel à Paris, Formation Excel à Marseille, Formation Excel à Lyon, Toulouse, Nice, Nantes, Montpellier, Strasbourg, Bordeaux, Lille, Rennes, Toulon, Reims, Saint-Étienne, Le Havre, Dijon, Grenoble, Angers, Villeurbanne, Nîmes.
Nous formons pour des entreprises dans le cadre de formation formation excel intra entreprise.
Comments