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 ExcelSur 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 !
Une fois ces manipulations maîtrisées, rendez-vous au chapitre suivant !
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 !
Une fois ces manipulations maîtrisées, rendez-vous au chapitre suivant !
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.