Un tableau croisé dynamique est un outil qui permet de ressortir rapidement une synthèse à partir d’une masse de données. Il est possible de comparer une variable, une donnée, par rapport à un autre (aspect croisé). Mais la partie la plus intéressante est l’aspect dynamique. Le tableau croisé se recalcule rapidement après que vous ayez apporté des changements. Vous pouvez ainsi faire des analyses plus approfondies ou essayer plusieurs combinaisons très rapidement. Le prochain exercice consiste à créer un tableau croisé dynamique et de voir les options principales.
Avant de commencer
Pour suivre cet exercice, vous pouvez aller chercher le classeur demos-excel-3.xlsx qui se trouve sur la page des fichiers de démonstrations.
Il faut des données pour être capable de générer votre premier tableau croisé dynamique. Mais ces données doivent être organisées d’une certaine façon.
Dans une liste de données, le nom des variables doit être sur la première ligne. Aucune colonne ne doit être laissée sans noms.
Toutes les lignes suivantes doivent contenir des données. Vous ne pouvez pas laisser des lignes vides; tout doit se suivre.
Placez le pointeur sur l’une des cellules de la liste de données.
Sélectionnez l’onglet Insertion. Appuyez sur le bouton Tableau croisé dynamique. Assurez-vous d’avoir la bonne sélection de cellules.
Il est préférable de placer le tableau dans une nouvelle feuille de calcul. Appuyez sur le bouton OK.
Voici votre premier aperçu de votre tableau croisé dynamique vide. Vous devez le remplir avec les champs qui sont dans le volet à la droite de l’écran ou créer vos propres champs calculés.
Note :
La liste des champs de tableau croisé dynamique va disparaître si vous cliquez à l'extérieur du tableau. Sélectionnez une cellule du tableau croisé dynamique pour ravoir accès à ses options.
Le tableau croisé dynamique est composé de quatre zones. Chacune à une fonction particulière.
Les quatre zones
Filtre du rapport
Permets de filtrer les données du tableau sur un ou plusieurs champs.
Étiquettes de colonnes
Regroupe les valeurs communes ensemble et affiche le résultat de chaque valeur dans une colonne.
Étiquettes de lignes
Affiche les résultats de la synthèse de chaque valeur sur une ligne différente.
Valeurs
Affiche les résultats des fonctions demandées (somme, nombre, moyenne …)
Selon les valeurs affichées sur les lignes et les colonnes.
Remplir le tableau
Cochez la case à côté du champ Salaire.
Le contenu sera automatiquement placé dans la zone Valeur puisqu’il s’agit de chiffres. Le tableau va aussi choisir par défaut l’option Somme des valeurs regroupées. Vous verrez comment changer l’opération un peu plus loin dans le texte.
Cochez la case à côté du champ Titre.
La première description est automatiquement placée dans la zone des étiquettes de lignes. Chaque ligne représente l’une des valeurs du champ (Administrateur, Ouvrier …)
Sélectionnez la variable Sexe.
Cette variable s’ajoute aussi dans la zone des étiquettes de lignes. Cela fait un tableau qui est plus difficile à lire. Il serait préférable de déplacer ce champ dans la zone des étiquettes de colonnes.
Déplacer un champ
De la fenêtre de la liste des champs à la droite de l’écran, sélectionnez le champ Sexe de la zone des étiquettes de lignes. Sélectionnez l’option Déplacer dans la zone Étiquettes de colonnes. OU Placez le pointeur par-dessus le bouton Sexe de la zone des étiquettes de lignes. En gardant un doigt sur le bouton gauche de la souris, déplacez le champ dans la zone des étiquettes de colonnes.
Voici un tableau qui est maintenant plus facile à lire parce qu’un champ a été déplacé de zone.
Filtrer les champs
Il y a des moments ou vous ne voulez pas avoir une vision globale des données mais plutôt filtrer sur un groupe qui pourrait mieux vous éclairer votre analyse. Le tableau croisé offre plusieurs façons de filtrer
Filtrer un champ situé dans la liste des données
De la liste des champs, appuyez sur le triangle pointant vers le bas à la fin de la barre du champ Titre.
Vous verrez la liste des options pour trier et filtrer le champ que vous avez choisi. Parmi les filtre, vous allez voir une liste des valeurs contenu dans le champ. Vous pouvez décider de filtrer et de retirer certaines de ces valeurs.
De la liste des valeurs du champ, décochez les cases devants Ouvrier et Vendeur.
Cela va vous laisser seulement les employés qui travaillent au bureau et non ceux qui sont sur la route.
On constate que le total des employés est moindre qu'auparavant puisqu'il y a seulement les administrateurs et les secrétaires. Remarquez aussi qu'il y a l'icone d'un filtre à coté de Étiquettes de lignes.
Filtrer un champ situé dans le tableau croisé dynamique
En plus de filtrer un champ actif du tableau, vous pouvez aussi filtrer directement du tableau croisé dynamique.
Appuyez sur le à la fin de l'étiquette de lignes.
Cette même option est disponible pour l'étiquette de colonnes. La même liste d'options va apparaître.
De la liste des options, sélectionnez Effacer le filtre de Titre.
Filtrer le tableau sur un champ qui n’est pas dans le tableau (filtre du rapport)
La zone Filtre du rapport peut filtrer les données des champs sans que ceux-ci apparaissent dans le tableau croisé dynamique.
Appuyez sur le bouton à la fin du champ Catégorie.
De la liste des valeurs de champs, décochez la case pour les valeurs 1 et 2.
Le filtre est activé pour le champ. Mais celui-ci n'est pas encore placé dans l'une des zones du tableau croisé dynamique.
De la liste des champs, placez le pointeur par dessus le champ Catégorie. Appuyez sur le bouton droit de la souris. De la liste des options, sélectionnez Ajouter au filtre de rapport.
Un tableau apparaît avec le champ Catégorie dans la zone des filtres de rapport. Il filtre le tableau puisque les soldes de plusieurs valeurs, ainsi que le grand total, sont moindres que dans le tableau initial. La prochaine étape consiste à retirer le champ Catégorie de la zone de filtre du rapport.
De la liste des champs, décochez le champ que vous ne désirez plus voir dans le tableau croisé dynamique.
Ajouter un champ
Vous n’êtes pas limité à utiliser un seul champ la zone des valeurs. Le prochain exercice consiste à ajouter un champ pour savoir combien de personnes sont dans le groupe.
De la liste de champs, sélectionnez encore une fois le champ Salaire. En gardant un doigt sur le bouton gauche de la souris, déplacez le champ Salaire encore une fois dans la zone Valeurs.
Renommer les champs et changer ses propriétés
Vous avez maintenant deux fois le champ Salaire dans la zone valeur. Mais vous en avez besoin d’un de ces champs pour déterminer la masse salariale et l’autre pour connaître le nombre de personnes qui sont dans ce groupe.
Vous avez deux façons de sélectionner un champ.
Dans le tableau croisé dynamique, cliquez dans la case Somme de salaires. De l’onglet des options de tableau croisé dynamique, vous pouvez changer le nom du champ ou sélectionner l’option Paramètres des champs de valeur. OU Faire un double-clic dans la cellule Somme de salaires.
OU
Dans la zone des valeurs, placez le pointeur par-dessus la case Somme de salaire. Appuyez sur le bouton droit de la souris. Sélectionnez l’option Paramètres des champs de valeurs.
OU Faire un double-clic dans la cellule Somme de Salaire.
À partir de cette fenêtre, vous pouvez changer le nom du champ ainsi que certaines propriétés.
Faire une Synthèse par Nombre. Changez le nom du champ à Nombre d’employés. Appuyez sur le bouton OK.
Sélectionnez le champ Somme de salaire2. Changer le nom à Salaires. Appuyez sur le bouton OK.
Le tableau croisé dynamique montre maintenant le nombre de personnes des regroupements ainsi que leur masse salariale. Le nom des champs a aussi été changé pour mieux représenter le contenu.
Retirer un champ
Il y a deux façons de retirer un champ du tableau.
De la liste des champs, décochez le champ que vous ne désirez plus voir dans le tableau croisé dynamique.
OU
De la liste des zones, sélectionnez le champ à retirer. Appuyez sur le triangle à la fin du champ. De la liste des options, sélectionnez Supprimer le champ.
Retirez le champ Sexe de la zone Étiquettes de colonnes.
Ajouter un champ calculé
Il vous est possible d’ajouter des champs calculés dans le tableau croisé dynamique pour mieux comprendre les données. Le prochain exercice consiste à ajouter un champ Calculé Bénéfices qui représente 50% du salaire des employés.
De l'onglet des Options du tableau croisé dynamique, appuyez sur le bouton Formules. Sélectionnez l’option Champ calculé.
Dans la case Nom, entrez le texte Bénéfices. Sélectionnez la case Formule.
Comme pour les formules dans les cellules, les champs calculés commencent avec un « = ». De la liste des champs, sélectionnez le champ Salaire. Appuyez sur le bouton Insérer un champ. Retournez à la case Formule. Ajoutez * 0,5 à la formule. Appuyez sur le bouton Ajouter. Appuyez sur le bouton OK.
Il y a maintenant un nouveau champ disponible. Vous pouvez l'utiliser comme les autres.
Appuyez sur le bouton OK. Déplacez le nouveau champ Bénéfices dans la zone des valeurs.
Regrouper les champs
La prochaine étape consiste à la création de deux groupes : Bureau et Extérieur. Le premier groupe inclura les administrateurs et les secrétaires. Le second inclura les ouvriers et les vendeurs.
Dans le tableau croisé dynamique, sélectionnez la cellule ayant le texte Administrateur. En gardant un doigt sur la touche CTRL, sélectionnez la cellule ayant le texte Secrétaire.
De l’onglet des options du tableau croisé dynamique, sélectionnez l’option Grouper la sélection. OU Appuyez sur le bouton droit de la souris. De la liste des options, sélectionnez Grouper.
Il y a maintenant un regroupement pour les valeurs Administrateur et Secrétaire. Le boutonsert à masquer les valeurs. Vous pouvez toujours les réafficher en appauyant sur le bouton .
Il faut ensuite créer un regroupement pour les valeurs Ouvrier et Vendeur.
Dans le tableau croisé dynamique, sélectionnez la cellule ayant le texte Ouvrier (celui sans la boite ). En gardant un doigt sur la touche CTRL, sélectionnez la cellule ayant le texte Vendeur (celui sans la boite ). De l’onglet des options du tableau croisé dynamique, sélectionnez l’option Grouper la sélection.
Changer les noms des groupes
Placez le pointeur dans la cellule ayant le texte Groupe1. Appuyez sur la touche F2 et modifiez le titre à Bureau. OU
Placez le pointeur dans la cellule ayant le texte Groupe1. Placez le pointeur dans la barre formule et modifiez le titre à Bureau.
Changez le nom du regroupement Groupe2 à Extérieur.
Actualiser un tableau croisé dynamique
Contrairement aux formules que vous utilisez tous les jours, le tableau croisé dynamique ne se met pas à jour lorsque vous modifiez une donnée. Il faut le forcer à s’actualiser.
Retournez à la feuille de calcul Données. Allez à la cellule F12. Changez le salaire d’Aline St-Pierre de 22 500 $ à 27 000 $. Retournez à la feuille de calcul ayant le tableau croisé dynamique.
Vous remarquerez que le total des salaires est toujours le même soit 394 400 $.
De l’onglet complémentaire Options de tableau croisé dynamique, appuyez sur le bouton Actualiser. OU Appuyez sur les touches Alt et F5.
Le grand total des salaires va passer de 394 400 $ à 398 900 $.
Afficher les options d'un tableau croisé dynamique
Toutes les commandes reliées au tableau croisé dynamique se retrouvent sur deux onglets contextuels : Options et Création.
À partir de l’onglet Création, vous pouvez décider d’afficher les totaux et sous-totaux, les titres des lignes et des colonnes ainsi que de changer la présentation du tableau.
Voir un graphique croisé dynamique
Excel 2007 vous permet aussi de représenter les données sous forme d’un graphique croisé dynamique. Vous pouvez changer les champs de la même façon que pour un tableau.
De l’onglet des Options du tableau croisé dynamique, appuyez sur le bouton Graphique croisé dynamique.
De la liste des types de graphiques disponibles, sélectionnez un histogramme empilé en 3D. Appuyez sur le bouton OK.
Placez le pointeur par dessus le titre du groupe Bureau. Appuyez sur le bouton droit de la souris. Du menu contextuel, sélectionnez les options Développer/Réduire et Réduire.
Répétez la même opération pour le groupe Extérieur.
Le graphique offre moins de détails, mais regroupe les détails des groupes en une seule barre.
Les graphiques croisés dynamiques offrent plusieurs options pour représenter ce que vous désirez. Il y a premièrement le volet pour les filtres du graphique. Il y a aussi quatre onglets contextuels pour gérer tout ce que vous pouvez réaliser avec un graphique.
Excel 2010: Les segments
Excel 2010 offre une nouvelle façon de filtrer un tableau croisé dynamique. Voici une petite vidéo pour expliquer son fonctionnement.
Abonnez-vous à l'infolettre
Abonnez-vous à l'infolettre du site pour recevoir les dernières nouvelles et aussi des formations à votre courriel. Vous pouvez voir la liste des dernières infolettres ici.
Cliquez ici pour voir les produits
Placez le pointeur sur l’une des cellules de la liste de données.
sert à masquer les valeurs. Vous pouvez toujours les réafficher en appauyant sur le bouton 
.
