Comment créer un tableau croisé dynamique dans Excel ?

Un tableau croisé dynamique consiste en un résumé d’un ensemble de données, selon divers critères de regroupement, représenté sous la forme d’un tableau à double entrée qui facilite l’interprétation des données. Il est dynamique car il nous permet d’obtenir différents totaux, de filtrer les données, de changer la présentation des données, de visualiser ou non les données sources, etc… Pour ceux d’entre vous qui connaissent Access, c’est ce qui se rapproche le plus d’une requête de références croisées, mais avec plus d’interactivité.

Voyons comment créer un tableau croisé dynamique dans excel à partir des données dont nous disposons déjà.

Créer un tableau croisé dynamique grâce à l’assistant

Pour créer un tableau croisé dynamique, Excel nous fournit l’assistant Tableau croisé dynamique et Graphique croisé dynamique.

Supposons que nous ayons une collection de données sur les articles dans le magasin avec le numéro de référence et le mois d’achat, plus nous connaissons la quantité achetée et le montant de l’achat.

Nous allons créer un tableau dynamique à partir de ces données afin de pouvoir mieux examiner les ventes de chaque article pour chaque mois.

• Pour accéder à l’assistant, allez dans le menu Données – Rapport tableaux croisés dynamiques et graphiques.
• Le premier des trois écrans de l’assistant de tableaux dynamiques apparaît, où il nous est demandé d’indiquer la situation des données à analyser et le type de rapport que nous allons créer.
• Dans notre cas, nous indiquons que nous allons sélectionner ceux de la liste Excel et que nous allons créer un tableau croisé dynamique.
• Dans le deuxième écran, nous sélectionnons la plage dans laquelle nous avons les données à utiliser dans le tableau croisé dynamique.
• Nous devons sélectionner toutes les cellules, y compris les en-têtes.
• Ensuite, la troisième étape de l’assistant apparaît…

Dans ce dernier écran, nous devons indiquer où nous allons placer le résultat du tableau croisé dynamique. Nous avons choisi de le placer dans une nouvelle feuille de calcul. En cliquant sur Design…, nous accédons à une boîte de dialogue où nous pouvons personnaliser la façon dont les données seront affichées dans le tableau croisé dynamique.

Dans l’image, nous pouvons voir que nous avons une structure formée par des lignes, des colonnes, des données et aussi un champ pour le numéro de page. Et sur le côté droit, nous avons les champs disponibles de la table, que nous pouvons faire glisser vers les différentes parties du diagramme sur la gauche.

Par exemple, nous pourrions construire un tableau croisé dynamique avec la structure suivante :

• Dans le champ Row, nous faisons glisser REF, les références de nos articles.
• Dans le champ Colonne, nous faisons glisser MONTH.
• Et dans Data, nous faisons glisser le MONTANT

Nous pouvons voir que la structure est celle que nous avons définie précédemment, dans le champ de la ligne nous avons les références, dans le champ de la colonne nous avons les mois et au centre du tableau les sommes des montants.

Avec cette structure, il est beaucoup plus facile d’analyser les résultats.

Une fois que le tableau croisé dynamique a été créé, la barre d’outils du tableau croisé dynamique apparaît sur la feuille :

• Nous pouvons l’afficher et la masquer avec l’option de menu Affichage — Barres d’outils.
• Dans cette barre d’outils, nous trouvons des boutons utiles tels que le bouton permettant de donner au tableau un format prédéfini, et d’autres que nous verrons tout au long du sujet.
• Il y a également la zone de liste des champs du tableau croisé dynamique qui nous permet de modifier la structure du tableau en ajoutant des champs aux différentes zones, soit en utilisant le bouton Ajouter à en combinaison avec la zone de liste qui apparaît à côté du bouton, soit en faisant simplement glisser le champ sur la zone souhaitée.
• De la même manière, nous pouvons supprimer un champ d’une zone en le faisant glisser hors de la table.

Nous voyons ainsi qu’en une seconde, nous pouvons modifier la structure du tableau et obtenir d’autres résultats sans pratiquement aucun effort. Cette liste de champs peut être affichée ou masquée à l’aide du bouton de la barre du tableau croisé dynamique. Si nous faisons glisser les champs quantité et total vers la zone de données, nous obtiendrons le tableau suivant, plus complexe mais avec plus d’informations

Supprimer un tableau croisé dynamique.

Pour supprimer un tableau croisé dynamique, il suffit de sélectionner le tableau dans son intégralité et d’appuyer sur la touche Supprimer ou, lorsque le tableau est sélectionné, de cliquer avec le bouton droit de la souris et de sélectionner Supprimer dans le menu contextuel.

Appliquer des filtres à un tableau croisé dynamique

Une autre caractéristique utile des tableaux croisés dynamiques est la possibilité de filtrer les résultats et de n’afficher ainsi que ceux qui nous intéressent à un moment donné. Cette méthode est utilisée notamment lorsque le volume de données est important.

Les principaux champs sont accompagnés, à droite, d’une flèche indiquant une liste déroulante.

Par exemple, si nous cliquons sur la flèche du champ Mois, une liste apparaît comme le montre l’image avec les différents mois disponibles dans le tableau avec une case à cocher dans chacun d’eux pour indiquer si nous voulons les voir ou non, plus une option pour marquer toutes les options, dans ce cas tous les mois.

Si nous laissons les mois de janvier et février cochés, les autres mois disparaîtront du tableau, mais ils ne sont pas perdus, à tout moment nous pouvons les visualiser en faisant défiler à nouveau la liste et en cochant la case (Afficher tout).

Pour fermer ce tableau, nous devons cliquer sur OK ou sur Annuler pour le fermer et le laisser tel quel.

En appliquant le filtre à plusieurs champs, nous pouvons créer des conditions de filtrage plus complexes. Par exemple, nous pouvons choisir de voir les articles portant la référence 1236 du mois d’avril.

Obtenir des moyennes dans un tableau croisé dynamique

Par défaut, lors de la création d’un tableau croisé dynamique, Excel génère des totaux avec sommation, on peut être intéressé à modifier ces formules pour d’autres, comme prendre le maximum ou le minimum, la moyenne, etc.

Pour ce faire, nous devons nous placer dans n’importe quelle cellule de la zone que nous voulons rectifier et cliquer avec le bouton droit de la souris, un menu contextuel apparaît avec différentes options, nous devons choisir l’option Configuration du champ… et une boîte de dialogue apparaît comme celle que nous voyons dans l’image.

Dans cette boîte de dialogue, nous pouvons choisir comment nous voulons faire le résumé, au moyen de Somme, Compte, Moyenne, etc.

Nous pouvons également ouvrir la boîte de dialogue avec le bouton de la barre PivotTable.

Tableaux croisés dynamiques en trois dimensions

Créer un tableau en trois dimensions n’est rien d’autre que d’ajouter un champ supplémentaire au tableau source et de l’utiliser comme champ principal pour le résumé.

Par exemple, pour continuer notre simulation, nous allons ajouter un champ « Semaine » et maintenant nous pourrons organiser par mois et par semaine.

Pour créer le tableau croisé dynamique en trois dimensions, nous procéderions de la même manière que nous l’avons vu précédemment, c’est-à-dire

• Allez dans Données – Tableau croisé dynamique et Rapport de graphique croisé dynamique.
• Nous sélectionnons la plage de données, maintenant avec le champ semaine (il peut nous demander si nous voulons utiliser les données du tableau croisé dynamique précédent, nous répondons NON).
• Et dans la structure de la table, la seule différence est d’utiliser le champ Page pour la troisième dimension.

Par exemple, si nous plaçons dans Page le champ Mois, dans Colonne le champ Semaine, nous verrons les résultats d’un mois dans chaque page.

Graphiques avec tableaux croisés dynamiques

Pour créer un graphique de notre tableau croisé dynamique, cliquez avec le bouton droit de la souris sur n’importe quelle cellule du tableau, et dans le menu contextuel qui s’affiche, sélectionnez Graphique croisé dynamique, qui générera un graphique à barres par défaut dans une nouvelle feuille de graphique. Nous pouvons également utiliser le bouton de la barre du tableau croisé dynamique.

Pour changer le format du graphique à un autre type de graphique que nous aimons plus ou qui nous convient plus selon les données que nous avons, nous devons cliquer avec le bouton droit de la souris sur le graphique et dans le menu pop-up qui apparaît nous devons choisir l’option Type de graphique… et choisir le graphique qui nous convient plus, la mécanique à suivre pour travailler avec les graphiques est la même que celle vue dans le sujet des graphiques.