22 Les tableaux croisés dynamiques
Maintenant que vous savez parfaitement saisir des données, les analyser d’une manière plus que basique, il est temps de s’attaquer à l’outil d’analyse le plus puissant d’Excel : « les tableaux croisés dynamiques ».
Le nom de cette notion est déroutant, nous vous l’accordons. Cette fonctionnalité du tableur est assez peu utilisée, pourtant rudement pratique, notamment sur les longues listes, et est un peu plus compliquée que les autres.
Prérequis fondamental : les listes de données. Car comme bien souvent, on analyse une liste, il faut donc être au point une bonne fois pour toutes dessus.
Les tableaux quoi ?
« Tableaux croisés dynamiques ». En fait, comme son nom l’indique, nous allons « croiser » les données d’une liste.
Mettez-vous dans le contexte : une longue liste de données, très longue. Plusieurs étiquettes de colonnes, des informations alphabétiques, numériques etc. Le tableau croisé dynamique créé à partir de cette longue liste va vous permettre d’obtenir notamment des statistiques, toujours aussi simplement que d’habitude, avec un glisser-déposer.
Mais alors où est le piège si c’est un simple glisser-déposer ?
L’embêtant n’est pas tellement de mettre debout un tableau croisé dynamique (que nous abrégerons désormais TCD), mais bien de l’organiser.
Un outil statistique puissant
Après la construction d’un TCD à partir d’une longue liste, vous devez le mettre en forme. Les TCD combinent format de cellules et listes déroulantes. Une fois la mise en forme effectuée, vous obtenez de belles statistiques.
Par exemple, les moyennes des ventes réalisées par un vendeur au mois de Janvier, puis de Février etc. Avec une liste déroulante, vous pouvez changer de vendeur et observer ses résultats. La moyenne peut être faite avec une formule, c’est vrai, mais il faudra élargir la plage d’entrée à chaque nouvelle saisie dans la liste. Bref, les TCD, c’est puissant car ils combinent plusieurs outils d’analyse en un tableau.
La notion de TCD est encore floue pour vous. Je vous propose de travailler par l’exemple et d’en construire un ensemble. Nous nous occuperons de son organisation ensuite.
Fabriquons un TCD !
Si vous avez bien suivi, vous avez parfaitement compris qu’il nous fallait une liste de données avant de commencer les manipulations, liste qui se voudra de préférence bien longue. Deux choix s’offrent à vous :
- Saisir vous-même la liste. Cela vous fera travailler la saisie mais on se retrouve dans quelques heures
- Télécharger une liste de données idéale toute prête
A vous de voir si vous préférez le masochisme ou saisir le plateau d’argent qui est à votre disposition. Nous allons travailler sur une liste de 51 entrées, qui récapitule l’âge, le sexe, le jeu joué et le score de chaque participant à une soirée Jeux.
Nous souhaitons obtenir diverses statistiques : qui est le vainqueur de chaque jeu, quel est le jeu préféré des femmes, quel est le jeu préféré des hommes, quel est le score moyen à chaque jeu, le score moyen des hommes, le score moyen des femmes, le cumul de points de chaque sexe, de chaque âge etc etc. Sachez que toutes ces statistiques, qu’on pouvait obtenir laborieusement à coup de formules, de graphiques etc. vont apparaître dans le TCD ! Bien évidemment pas toutes à la fois, il faudra modifier l’organisation du TCD pour obtenir chaque statistique souhaitée, mais le gain de temps et de clarté est tout de même énorme.
Pour le moment, nous devons construire notre TCD à partir de la fameuse liste :
Télécharger le fichier tcd.xlsx
La construction du TCD
Sélectionnez toute la liste de données, délimitée par une bordure rouge dans les fichiers proposés. N’oubliez pas de sélectionner les étiquettes de colonnes, c’est très important. Dans l’onglet « Insertion », cliquez sur « Tableau croisé dynamique » :
Une fenêtre demande de confirmer la plage de saisie du tableau et vous demande si vous souhaitez mettre le TCD dans une nouvelle fenêtre Excel ou dans l’actuelle :
Vérifiez la plage du tableau et confirmez la nouvelle feuille. Apparaissent alors deux onglets ainsi qu’un module à droite de l’écran :
Les deux onglets Options et Création du groupe Outils de tableau croisé dynamique
Le module de champs de tableau croisé dynamique
Sur la fenêtre qui s’ouvre, il faut cliquer sur « Disposition », afin de construire le TCD. S’il y a plusieurs fenêtres, cliquez sur « Suivant » jusqu’à arriver à la fenêtre ayant pour intitulé la « Disposition ».
Une fenêtre s’ouvre, vous invitant à bâtir votre TCD à coups de glissés-déposés peu ordinaires :
C’est ici que se fait la construction du TCD.
Pas si simple !
La procédure étant différente sur Windows et sur Mac, je vais vous la présenter pour ces deux systèmes.
Sur Windows
La première fois que l’on arrive ici, tout semble incompréhensible et difficile. A droite, dans le module, vous reconnaissez les étiquettes des colonnes de notre liste ainsi que les zones du TCD où seront déposées les étiquettes. Au milieu, vous avez un schéma du TCD que vous verrez une fois construit dans votre feuille de calcul. Nous croisons donc les données des lignes et des colonnes pour obtenir les résultats du milieu. Qu’attendons-nous ?
Il faut ainsi glisser les noms des étiquettes de colonne sur l’un des quatre champs du TCD.
L’étiquette « Numéro de concours » ne va servir à rien ici. Elle permet juste d’identifier chaque joueur dans la liste. Nous l’oublions donc.
Allons-y pour les autres. Nous souhaitons d’abord voir les totaux des points marqués à chaque jeu pour les hommes et les femmes, ainsi que la somme des deux. Cette première série statistique est un peu « bateau », je vous l’accorde, mais il faut commencer doucement.
Puisque nous voulons visualiser les scores, nous glissons l’étiquette de colonne « Score » sur la zone « Valeurs ». Jusqu’ici, tout devrait aller.
On veut un découpage des scores en fonction du sexe des participants et des jeux joués. Pas de problème, dans le champ « Ligne » nous glissons l’étiquette « Jeu » et dans le champ « Colonne », nous glissions « Sexe ».
Notez que nous aurions pu faire l’inverse, c’est ensuite une question de goût sur le résultat final. Nous venons d’anticiper les croisements des données de notre liste sous forme d’un tableau. Il n’y a plus qu’à fermer ce module complémentaire.
Et d’un ! Votre tableau croisé dynamique apparaît !
Ainsi, sur tous les points marqués dans la soirée, 247 ont été fournis par les joueurs d’Urban Terror. Les femmes ont marqué 33 points à ce jeu et se sont d’ailleurs faîtes bien massacrées par les hommes contre 214 points.
Il est aussi possible de changer le filtre des champs en cliquant sur les flèches à droite de ceux-ci. Nous l’avons déjà vu dans un précédent chapitre.
Nous allons maintenant voir comment modifier simplement ce TCD, sans avoir à tout refaire.
Sur Mac
La première fois que l’on arrive ici, tout semble incompréhensible et difficile. A droite, vous reconnaissez les étiquettes des colonnes de notre liste. Au milieu, vous avez un schéma du TCD que vous verrez une fois construit dans votre feuille de calcul. Nous croisons donc les données des lignes et des colonnes pour obtenir les résultats du milieu. Qu’attendons-nous ?
Il faut ainsi glisser les noms des étiquettes de colonne sur l’un des quatre champs du TCD.
L’étiquette « Numéro de concours » ne va servir à rien ici. Elle permet juste d’identifier chaque joueur dans la liste. Nous l’oublions donc.
Allons-y pour les autres. Nous souhaitons d’abord voir les totaux des points marqués à chaque jeu pour les hommes et les femmes, ainsi que la somme des deux. Cette première série statistique est un peu « bateau », je vous l’accorde, mais il faut commencer doucement.
Puisque nous voulons visualiser les scores, nous glissons l’étiquette de colonne « Score » sur le champ « Données ». Jusqu’ici, tout devrait aller.
On veut un découpage des scores en fonction du sexe des participants et des jeux joués. Pas de problème, dans le champ « Ligne » nous glissons l’étiquette « Jeu » et dans le champ « Colonne », nous glissions « Sexe ».
Notez que nous aurions pu faire l’inverse, c’est ensuite une question de goût sur le résultat final. Nous venons d’anticiper les croisements des données de notre liste sous forme d’un tableau. Il n’y a plus qu’à cliquer sur « Ok », puis sur « Fin ».
Et d’un ! Votre tableau croisé dynamique apparaît !
Ainsi, sur tous les points marqués dans la soirée, 247 ont été fournis par les joueurs d’Urban Terror. Les femmes ont marqué 33 points à ce jeu et se sont d’ailleurs faîtes bien massacrées par les hommes contre 214 points.
Chouette n’est-ce pas ? Reposez-vous, c’est mérité. Nous allons maintenant voir comment modifier simplement ce TCD, sans avoir à tout refaire.
Modification du TCD
Là encore, la façon de faire est différentes sur Windows et Mac. Voyons l’un après l’autre.
Sur Windows
Votre premier TCD est bien basique. Sur les 4 champs du schéma, nous n’en avons rempli que 3. Retournons dans le module permettant la conception du TCD.
Pour information, vous pouvez réafficher le module en cliquant sur « Liste des champs », dans le groupe « Afficher » de l’onglet « Options » des tableaux croisés dynamiques.
Cochez la case de l’étiquette Age » et glissez-la dans la zone « Filtre du rapport ». Comme nous avons glissé l’étiquette Age, nous pourrons trier les scores de chaque jeu de chaque sexe en fonction de l’âge des participants.
Votre TCD a été modifié et vous voyez apparaître en haut du tableau un filtre. Fermez le module. Et admirez le résultat :
Un filtrage a été effectué pour ne garder que les joueuses et les joueurs de 16 ans. Les femmes de 16 ans sont accrocs à Halo.
Voilà. Les données peuvent être ainsi filtrées et interprétées de différentes manières. La liste analysée a suffisamment de champs et d’entrées pour être tournée dans tous les sens. Modifiez le TCD à votre sauce, faites des tests, il n’y a que comme ça que ça rentre. Pourquoi ne pas croiser les données d’âge et de sexe pour visualiser les scores, tout en permettant un filtrage par jeu ? A vous de voir !
Sur Mac
Votre premier TCD est bien basique. Sur les 4 champs du schéma, nous n’en avons rempli que 3. Retournons dans la fenêtre permettant la conception du TCD. Cliquez au hasard dans votre TCD. Dans la barre d’outils qui vient à côté de votre tableau, cliquez sur le bouton « Assistant Tableau Croisé Dynamique ».
Une fenêtre s’ouvre, cliquez sur « Disposition », comme pour la création.
Glissez simplement l’étiquette « Age » sur le champ « Plage ». Le champ Plage permettra d’obtenir des filtres sur son TCD. Comme nous avons glissé l’étiquette Age, nous pourrons trier les scores de chaque jeu de chaque sexe en fonction de l’âge des participants.
Cliquez sur « Ok » puis sur « Fin », comme auparavant. Votre TCD a été modifié et vous voyez apparaître en haut du tableau un filtre.
Un filtrage a été effectué pour ne garder que les joueuses et les joueurs de 16 ans. Les femmes de 16 ans sont accrocs à Halo
Voilà. Les données peuvent être ainsi filtrées et interprétées de différentes manières. La liste analysée a suffisamment de champs et d’entrées pour être tournée dans tous les sens. Modifiez le TCD à votre sauce, faîtes des tests, il n’y a que comme ça que ça rentre. Pourquoi ne pas croiser les données d’âge et de sexe pour visualiser les scores, tout en permettant un filtrage par jeu ? A vous de voir !
Résumons
Un tableau croisé dynamique (TCD) permet d’analyser une liste de données. Cette notion est considérée comme la plus puissante d’Excel ;
Le TCD fournit des statistiques détaillées sur votre liste, selon la manière dont il est construit ;
On construit un TCD en effectuant un glisser-déposer d’étiquettes de colonnes sur l’une des quatre zones du TCD. Le champ « Plage » permet d’obtenir un filtre.
Continuons maintenant sur notre lancée.
Nous continuons notre tour d’exploration des TCD. Nous allons nous concentrer ici sur la mise en forme des données, afin d’obtenir les statistiques désirées dans des formes variées et intéressantes.
Mettre en forme un TCD
Nous allons travailler ici sur un TCD simplifié, toujours créé à partir de notre liste de jeux.
Le tableau sur Windows
Le tableau sur Mac
Un tri effectué sur les jeux (ici Urban Terror) nous permet de visualiser la somme des âges des participants de chaque sexe.
Et là, c’est le problème. Nous voulions savoir la moyenne d’âge des joueuses d’Urban Terror et la moyenne d’âge des joueurs d’Urban Terror. Plutôt raté puisqu’Excel fait la somme des âges des participants…
Nous allons donc corriger ce problème !
Sur Windows
Retournez dans les onglets des TCD et faites apparaitre le module. Cliquez sur l’étiquette de colonne « Somme sur Age » puis sur « Paramètres des champs de valeur ».
Une fenêtre s’ouvre. Dans la liste déroulante de gauche, cliquez sur « Moyenne », puisque nous voulons une moyenne d’âge :
Le bouton « Format de nombre » en dessous vous permet de mettre en forme vos données avec des formats que vous connaissez bien (Standard, Nombre etc.). Allez faire un tour de ce côté là si vous le souhaitez pour par exemple avoir des moyennes avec une ou deux décimales.
Cliquez sur « Ok ». Vous voyez ainsi que les joueuses et les joueurs de Urban Terror ont en moyenne 18 ans.
Avec le filtre des jeux, vous pouvez passer aux Sims 3 par exemple. Comme votre mise en forme ne bouge pas, vous voyez que les joueuses des Sims 3 ont en moyenne 18 ans et les joueurs 21 ans, la moyenne d’âge de tous les joueurs des Sims 3 étant de 19 ans.
En plaçant le filtre sur « Tous », vous avez la moyenne d’âge de tous les participants de la soirée, ainsi que les moyennes d’âge de toutes les femmes et de tous les hommes.
Bien, non ? Mais vous n’êtes pas encore au bout de vos surprises en ce qui concerne la mise en forme d’un TCD.
Des pourcentages !
Les pourcentages sont très pratiques dans l’analyse d’une longue liste car ils vous permettent de visualiser des données en un clin d’œil. Voici la nouvelle disposition du TCD à réaliser :
Nous voulons tout simplement savoir quel est l’âge le plus représenté parmi les joueurs. Faites un double-clic sur « NB sur Jeu ». Dans la fenêtre qui s’ouvre, cliquez sur l’onglet « Afficher les valeurs ». Dans la liste déroulante, choisissez « % du total général ». Cliquez ensuite sur « Format de nombre » puis sélectionnez « Pourcentage ». Vous savez ainsi à combien de % sont représentés les âges à sa soirée Jeux.
Les joueurs de 18 ans sont les plus représentés
Oui mais si 3 fois plus d’âges étaient représentés ? Le TCD aurait été 3 fois plus long et difficilement lisible !
Exact, d’ailleurs, tel quel, c’est difficilement lisible et les filtres auraient pu faire ce travail plus proprement et en moins de temps. Pour pallier ce problème, nous allons utiliser « les groupes ».
Sur Mac
Retournez dans la fenêtre de disposition de votre TCD et faites un double-clic sur l’étiquette de colonne « Somme sur Age ».
Une fenêtre s’ouvre. Dans la liste déroulante de gauche, cliquez sur « Moyenne », puisque nous voulons une moyenne d’âge.
Le bouton « Nombre » à droite vous permet de mettre en forme vos données avec des formats que vous connaissez bien (Standard, Nombre etc.). Allez faire un tour de ce côté là si vous le souhaitez pour par exemple avoir des moyennes avec une ou deux décimales.
Cliquez sur « Ok ». Vous voyez ainsi que les joueuses et les joueurs de Urban Terror ont en moyenne 18 ans.
Avec le filtre des jeux, vous pouvez passer aux Sims 3 par exemple. Comme votre mise en forme ne bouge pas, vous voyez que les joueuses des Sims 3 ont en moyenne 18 ans et les joueurs 21 ans, la moyenne d’âge de tous les joueurs des Sims 3 étant de 19 ans.
En plaçant le filtre sur « Tous », vous avez la moyenne d’âge de tous les participants de la soirée, ainsi que les moyennes d’âge de toutes les femmes et de tous les hommes.
Chouette n’est ce pas ? Mais vous n’êtes pas encore au bout de vos surprises en ce qui concerne la mise en forme d’un TCD.
Des pourcentages !
Les pourcentages sont très pratiques dans l’analyse d’une longue liste car ils vous permettent de visualiser des données en un clin d’œil. Voici la nouvelle disposition du TCD à réaliser :
Nous voulons tout simplement savoir quel est l’âge le plus représenté parmi les joueurs. Faites un double-clic sur « NB sur Jeu ». Dans la fenêtre qui s’ouvre, cliquez sur « Options ». Dans la liste déroulante, choisissez « % du total » et validez les différentes boîtes de dialogue. Vous savez ainsi à combien de % sont représentés les âges à sa soirée Jeux.
Les joueurs de 18 ans sont les plus représentés
Oui mais si 3 fois plus d’âges étaient représentés ? Le TCD aurait été 3 fois plus long et difficilement lisible !
Exact, d’ailleurs, tel quel, c’est difficilement lisible et les filtres auraient pu faire ce travail plus proprement et en moins de temps. Pour pallier ce problème, nous allons utiliser « les groupes ».
Les groupes
Reprenons le TCD précédent :
On souhaite faire des groupes d’âge afin que le tout soit mieux ordonné et lisible. Les 10-15 ans, les 15-20 ans, les 20-30 ans et le plus de 30 ans.
Sélectionnez tous les âges compris entre 10 et 15 ans. Dans l’onglet « Données », dans le groupe « Plan », cliquez sur le bouton « Grouper ».
Une colonne s’est créée dans le TCD, vous n’avez plus qu’à la nommer en tapant directement votre texte (10-15). Faites de même pour les autres âges.
Un double-clic sur chaque groupe vous permet de masquer les détails des âges et d’avoir des statistiques globales :
Les 15-20 ans étaient donc les plus représentés à la soirée Jeux.
Notez que les groupes peuvent également être utilisés sur des dates exactement de la même manière.
Résumons
Un double-clic sur l’étiquette de colonne schématisée dans le menu « Disposition » permet d’accéder à des options de mises en forme (moyenne, pourcentages etc.)
Les groupes, accessibles à partir du menu « Données » permettent de grouper des éléments du TCD en un paquet, et donc de simplifier sa lecture.
Petit exercice
Ayant pour support la liste de données sur laquelle nous avons travaillé, sauriez-vous retrouver le gagnant de chaque jeu ?
Vous savez désormais vous servir des TCD. Notez que c’est désormais à votre tour d’explorer tous les boutons que nous avons évoqués mais pas explorés. A vous de mettre en forme vos données selon vos goûts et vos besoins !
Références
Barrée, B. et Fougeray, B. (s. d.). Analysez des données avec Excel. OpenClassrooms.
