Le tableau croisé dynamique Excel !!! Ce mot barbare pour certains fait peur à nombre de mes apprenants, mais en réalité, c'est un outil fantastique et facile à utiliser.
Les tableaux croisés dynamiques te permettent d'analyser et d'identifier les anomalies d'une base de données. C'est donc un réel outil d'aide à la prise de décision.
Nicolas t'explique dans la vidéo comment ils fonctionnent avec un cas pratique de 35 minutes !
Dans tous les cas, dans cet article, tu vas avoir toutes les informations essentielles sur les tableaux croisés dynamiques.
Le fichier d'exercice se situe à la fin de l'article.
Le tableau croisé dynamique est un outil dans Excel qui permet de synthétiser les données pour ensuite les analyser.
Ces tableaux facilitent la prise de décision, car ils ont la capacité de présenter, de manière rapide et simple, les données d'une base de données.
Tu peux filtrer et trier les données synthétisées, et également les analyser dans un graphique croisé dynamique.
Les tableaux croisés dynamiques te permettent ainsi de traiter un tableau qui contient beaucoup de données et d'informations. En un seul coup d’œil, tu peux obtenir les informations dont tu as besoin dans la base de données.
Comment insérer un tableau croisé dynamique ?
Il n’y a rien de plus simple pour générer un tableau croisé dynamique !
D’abord, tu dois avoir une source de données dans une feuille Excel (une source ou base de données est un tableau avec plein de données).
La base de données comprend les données que tu veux analyser : ces données vont donc te servir à construire ton tableau croisé dynamique.
Voici un exemple d'une base de données :
Quelles sont les étapes pour construire un tableau croisé dynamique sur Excel ?
- Utiliser le raccourci [CTRL] + [A] (Windows) ou [⌘] + [A] (Mac) pour sélectionner tout ton tableau Excel (ta base de données).
- Aller dans l'onglet "Insertion".
- Cliquer sur "Tableau croisé dynamique".
- Cliquer sur "Ok" pour insérer le tableau croisé dynamique dans une nouvelle feuille de calcul.
- (Si tu veux insérer ton tableau croisé dynamique dans une feuille de calcul existante, tu dois cocher "Feuille de calcul existante" et saisir l'endroit où tu veux l'insérer. Puis, tu peux cliquer sur "Ok".)
Pour gagner du temps et mieux te repérer dans ton classeur Excel, tu peux renommer la nouvelle feuille de calcul que tu viens de créer.
Il s'agit donc de la feuille qui contient ton tableau croisé dynamique. Lorsque tu sélectionnes la zone du tableau croisé dynamique (voir image ci-après), le volet "Champs de tableau croisé dynamique" apparait à droite.
Si ce volet ne s'affiche pas, alors tu dois faire un clic droit sur la zone du tableau croisé dynamique, puis sélectionner "Afficher la liste des champs". Comme tu peux le remarquer, le volet "Champs de tableau croisé dynamique"contient les titres de colonnes de ta source de données.
En dessous des étiquettes de données, c'est-à-dire en dessous des titres des colonnes de la base de données, tu peux apercevoir 4 paramètres de champs disponibles pour créer ton tableau croisé dynamique :
- Filtres : champs que tu veux filtrer (tu peux aussi insérer un segment ou une chronologie).
- Colonnes : champs que tu veux présenter en colonnes (généralement, tu utilises cette case lorsque tu as trop de champs dans les lignes).
- Lignes : champs que tu veux présenter en lignes.
- Valeurs : champs que tu veux calculer.
Nous allons maintenant parler en détails de ces 4 champs.
Mon conseil : si tu es débutant, commence par utiliser uniquement le champ Valeur et le champ Lignes.
Les paramètres Lignes et Colonnes
Les paramètres Lignes et Colonnes s’adressent aux données du tableau que tu veux présenter. Il suffit d’y glisser-déposer l’étiquette de données que tu souhaites afin qu’elle s’affiche dans le tableau croisé dynamique. Tu peux en mettre plusieurs si tu le souhaites.
Toutefois, pour une meilleure organisation, il est mieux de convenir d’une certaine hiérarchie. Place les champs avec le moins d’issues au-dessus (par exemple, le champ Sexe contient généralement moins d'issues que le champ Ville).
Ensuite, si tu as vraiment beaucoup de données, il vaut mieux glisser une des étiquettes de données dans le champ Colonnes. Les données du tableau croisé dynamique seront plus visibles et mieux présentées.
D'ailleurs, si tu souhaites mieux naviguer dans tes bases de données, tu devrais apprendre à figer les lignes et/ou les colonnes des tableaux Excel. Nous avons écrit un article sur le sujet ici.
Le paramètre Valeurs
Ensuite, tu as le paramètre Valeurs. C’est ici que tu dois intégrer les valeurs à calculer. Il s’agira donc des sommes, des moyennes, des valeurs minimales et maximales... Tu peux y ajouter autant d’étiquettes de données que tu le souhaites, et même plusieurs fois la même étiquette (pour analyser le salaire minimum et le salaire maximum par exemple). Attention à ne pas trop en mettre non plus au risque de rendre ton tableau croisé dynamique illisible, car il contiendra trop de données.
Il faut aussi préciser qu'il vaut mieux glisser les étiquettes de données dans les champs que de les cocher. En effet, Excel présente automatiquement les données numériques dans le champ des Valeurs si tu les coches, donc il vaut mieux les glisser manuellement dans le champ souhaité.
Le paramètre Filtres
Le paramètre Filtres permet de filtrer les informations que tu veux faire apparaître, outre les données déjà présentées. Par exemple, tu as mis respectivement "Sexe" et "Qualification" dans les Lignes et Colonnes. Dans les valeurs, il y a la Somme des salaires (le champ Salaires donc). Tu peux alors ajouter le champ Site dans le paramètre Filtres. De cette manière, tu peux faire apparaître les Valeurs en fonction du Site choisi dans les Filtres.
Il est important de noter que si tu modifies des informations de la base de données, tu dois absolument actualiser le tableau croisé dynamique pour effectuer la mise à jour des données. Pour actualiser les données, tu dois utiliser le raccourci [Alt] + [F5] (Windows et Mac) ou aller dans "Analyse" puis "Actualiser".
Exemples de tableaux croisés dynamiques
Dans cette partie, tu vas découvrir deux exemples de tableaux croisés dynamiques !
Masse salariale par sexe, par site et par qualification
Nombre de salariés par sexe et par qualification
Comment paramétrer les Valeurs du tableau croisé dynamique ?
Par défaut, le paramètre des Valeurs définit automatiquement la Somme du champ. Tu peux changer le paramètre à ta guise :
- Commence par cliquer sur l'étiquette dans le champ Valeurs,
- Puis sur "Paramètres des champs de valeurs"
- Et enfin, sélectionner ce que tu veux calculer.
Calculer la moyenne dans les Valeurs
Tu peux aussi calculer la moyenne des Valeurs.
Comment faire ?
- Pour ce faire, tu dois d’abord glisser le champ que tu veux calculer.
- Ensuite, tu dois faire un clic droit et aller dans "p.
- Tu peux changer le paramètre à ta guise en cliquant sur l'étiquette dans le champ Valeurs, puis sur "Paramètres des champs de valeurs".
- Tu peux maintenant sélectionner la moyenne.
Afficher les valeurs minimales et maximales d'une même étiquette de données dans les Valeurs
Tu peux afficher les valeurs minimales et maximales d'une même étiquette de données dans le tableau croisé dynamique Excel.
Comment faire ? Un exemple pour te l'illustrer.
- Tu peux glisser les "Salaires" dans le paramètre des Valeurs.
- Tu dois ensuite sélectionner "Min." dans le "Paramètres des champs de valeurs".
- Puis, tu dois insérer une nouvelle fois les "Salaires" dans le paramètre des Valeurs et sélectionner "Max." dans le "Paramètres des champs de valeurs".
- Tu as maintenant le salaire minimum et maximum présentés dans ton tableau croisé dynamique.
Le format monétaire dans les Valeurs
Tu peux modifier le format d'une étiquette de données en format monétaire ou en format comptabilité.
Comment faire ?
- Pour cela, tu dois sur l’étiquette de données dans le paramètre Valeurs
- Puis va dans "Paramètres des champs de valeurs".
- Là, tu peux choisir le format qui te convient.
Maîtrisez Excel de A à Z
Base du logiciel, détail des fonctions, gestion et analyse des données, tableaux et graphiques...
Comment affiner la lecture d’un tableau croisé dynamique des données dans un tableur Excel ?
Parfois, il y a beaucoup d’informations donc le tableau croisé dynamique est illisible. Comment rendre le tableau croisé dynamique agréable à analyser ?
Insérer des tranches
Pour simplifier la présentation de tes statistiques, tu peux regrouper les données de certains champs pour les présenter par tranche. Le tableau croisé dynamique Excel te permet de le faire automatiquement.
Par exemple, prenons les tranches d’âge :
- Glisser l’étiquette "Âge" dans le paramètre "Lignes".
- Sélectionner les âges dans le tableau croisé dynamique.
- Faire un clic droit ou bien aller dans l’onglet "Analyse du tableau croisé dynamique".
- Cliquer sur "Grouper..." ou "Grouper la sélection".
- Saisir les bornes minimales et maximales des tranches d'âge (si tu les coches, Excel prend les âges minimum et maximum de la base de données).
- Saisir un écart de tranche et cliquer sur "Ok".
- (Pour retirer les tranches, tu dois faire un clic droit sur une des tranches et cliquer sur "Dissocier").
Insérer un segment
Tu peux aussi insérer un ou plusieurs segments. Il s'agit de filtres plus visuels.
Par exemple, tu veux filtrer les données du tableau croisé par Site :
- Sélectionner une cellule du tableau croisé et aller dans l'onglet "Insertion"
- Cliquer sur "Segment".
- Sélectionner "Site" et cliquer sur "Ok" : le segment apparait.
- Filtrer les sites souhaités.
- Tu peux filtrer plusieurs données en maintenant la touche [Ctrl] (Windows) ou [⌘] (Mac).
Insérer une chronologie
Tu peux aussi insérer une chronologie si ta base de données contient un champ au format Date.
Par exemple, tu veux filtrer les données du tableau croisé par Date d'intégration :
- Sélectionner une cellule du tableau croisé et aller dans l'onglet "Insertion".
- Cliquer sur "Chronologie".
- Sélectionner "Date d'intégration" et cliquer sur "Ok" : la chronologie apparait.
- Filtrer en fonction des Dates d'intégration souhaités.
Insérer un graphique croisé dynamique
Tu peux présenter ton tableau croisé dynamique dans un graphique croisé dynamique :
- Sélectionner une cellule du tableau croisé et aller dans l'onglet "Insertion".
- Cliquer sur "Graphique croisé dynamique" : les données s'affichent dynamiquement dans un graphique en fonction des champs insérés dans ton tableau croisé dynamique.
- Tu peux modifier le style du graphique croisé dynamique dans les menus qui s'affichent : "Analyse des graphiques pivotants", "Création" et "Format".
Insérer un nouveau champ dans le tableau croisé dynamique
Tu peux également insérer un nouveau champ (une nouvelle étiquette de données) dans ton tableau croisé dynamique. Cela te permet par exemple d'effectuer des calculs ou des simulations.
Comment faire ?
- Tu dois aller sur le tableau croisé dynamique Excel.
- Puis dans l’onglet "Analyse du tableau croisé dynamique".
- Puis dans "Champs, éléments et jeux".
Je certifie mes compétences Excel
Découvrez comment passer la Certification Excel de référence : TOSA EXCEL.
Nous t'avons enfin concocté une petite FAQ...
FAQ : faire un tableau croisé dynamique sur Excel
- Comment créer un tableau croisé dynamique sur Excel ?
- Comment analyser les données d'un tableau croisé dynamique sur Excel ?
- Que signifient les paramètres des champs dans un tableau croisé dynamique sur Excel ?
Comment créer un tableau croisé dynamique sur Excel ?
Pour créer un tableau croisé dynamique sur Excel, tu dois sélectionner ta base de données puis aller dans l'onglet "Insertion" et sélectionner "Tableau croisé dynamique".
Tu peux aussi insérer un "Tableau croisé dynamique recommandé". Dans ce cas, Excel te fera des propositions de tableaux croisés dynamiques déjà paramétrés.
Comment analyser les données d'un tableau croisé dynamique sur Excel ?
Pour analyser les données d'un tableau croisé dynamique sur Excel, tu peux utiliser les segments, les chronologies, les filtres, les graphiques croisés dynamiques ou les champs calculés.
Que signifient les paramètres des champs dans un tableau croisé dynamique sur Excel ?
Il existe 4 paramètres de champs dans un tableau croisé dynamique :
- Valeurs : champs que tu veux calculer.
- Lignes : champs que tu veux présenter en lignes.
- Colonnes : champs que tu veux présenter en colonnes (généralement, tu utilises cette case lorsque tu as trop de champs dans les lignes).
- Filtres : champs que tu veux filtrer (tu peux aussi insérer un segment ou une chronologie).
Pour aller plus loin
Des ressources inédites et accessibles ! Pour progresser sur la fonction d'Excel, nous mettons à ta disposition des ressources exceptionnelles qui sont toutes abordables simplement !
S'entraîner est la meilleure façon de progresser !
Besoin d'une aide supplémentaire ? Tu peux toujours consulter notre expert de notre équipe de formateur afin d'obtenir une assistance personnalisée gratuitement.
Tu souhaites des tutos sur d'autres fonctionnalités d'Excel ? C'est par ici :
Formation de A à Z et finançable
Une progression rapide sur Excel ! Tu seras en mesure d'automatiser de nombreuses tâches répétitives que tu effectues actuellement de manière manuelle, ce qui te permettra de gagner un temps précieux. Notre formation est finançable. Pour découvrir le programme et nous contacter.
