LeCompagnon.info

Excel

Excel - Exercice sur le tableau croisé dynamique

Introduction
Avant de commencer
Les quatre zones
Remplir le tableau
Déplacer un champ
Filtrer les champs
Ajouter un champ
Renommer un champ et changer ses propriétés
Retirer un champ
Ajouter un champ calculé
Regrouper les champs
Actualiser un tableau croisé dynamique
Actualiser le tableau croisé dynamique
Afficher les options du tableau croisé dynamique
Voir un graphique croisé dynamique

Excel 2010: Les segments

Tableau croisé dynamique - Partie 1

Tableau croisé dynamique - Partie 2

Introduction à Excel
Menu Excel
Fichier
Accueil
Insertion
Mise en page
Formules
Données
Révision
Affichage
Thèmes
Opérations de base
Présentation
Mise en page et Impression
Fonctions
Fonctions matricielles
Copier, coller
Référence relative et absolue
Graphiques
Formules 3D
Outils d'analyse
Commentaires
Liste de données
Macro commandes
Tableau croisé dynamique
Exercices : création de modèle
Graphique: Insérer un second axe
Graphique: créer un graphique demi-lune
Graphique: Créer une pyramide d'âge
Gestionnaire de scénarios
Mise en forme conditionnelle
Remplissage instantané et analyse rapide
Solveur
Somme automatique
Sous-total
Tableau croisé dynamique
Table de données (1 et 2 variables)
Valeur cible
Validation - Liste de choix
Quiz
Navigation

Word 2003
Word 2007
Excel
Excel
VBA pour Excel
PowerPoint 2003
PowerPoint 2007
Access
Access 2010
Internet
Général
Intelligence financière

Navigation
Précédent
Suivant
Page Principale
FAQ
Nouveautés
Plan du site
Références
Motivations
Manuels de formation
Fichiers de démonstration
Nous joindre
Par courriel
Abonnez-vous à
l'infolettre LCI


Abonnez-vous à l'infolettre LeCompagnon.info










Introduction

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.

Voici les même liste de données que vous retrouvez sur la page des tableaux croisés dynamiques pour Excel.

Excel 2007 : Données de base pour l'exerice sur le tableau croisé dynamique

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.

Excel 2007 : Sélection de la table 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.

Excel 2007 : Tableau croisé dynamique vide

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.

Excel 2007 : Liste des champs de tableau croisé dynamique

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

Excel 2007 : Choisir le champ Salaire de la liste pour le tableau croisé dynamique

*Cochez la case à côté du champ Salaire.

Excel 2007 : Premier contenu du tableau croisé dynamique

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.

Excel 2007 : Tableau croisé dynamique avec la description d'un champ

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 …)

Excel 2007 : Tableau croisé dynamique avec deux champs

*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

Excel 2007 : Tableau croisé dynamique - Déplacer un champ dans une autre zone

*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.

Excel 2007 : Tableau croisé dynamique avec deux champs - 1 en zone de lignes, 1 en zone 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

Excel 2007 : Filtrer un champ

*De la liste des champs, appuyez sur le triangle pointant vers le bas à la fin de la barre du champ Titre.

Excel 2007 : Liste des options pour gérer l'affichage d'un champ : Tri et filtre

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.

Excel 2007 : Tableau croisé dynamique filtré sur le champ Titre

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.

Excel 2007 : Retirer le filtre 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.

Excel 2007 : Filtrer un champ à partir de la liste des champs du tableau croisé dynamique

*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.

Excel 2007 : Ajouter un champ dans la zone de rapport 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.

Excel 2007 : Tableau crois dynamique avec 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.

Excel 2007 : Retirer le champ Catégorie du tableau croisé dynamique

*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.

Excel 2007 : Tableau croisé dynamique avec deux champs dans la zone valeur

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.

Excel 2007 : Paramètres d'un champ du tableau croisé dynamique

*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

Excel 2007 : Paramètre d'un champ d'un tableau croisé dynamique

*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.

Excel 2007 : Paramètre d'un champ de tableau croisé dynamique

*À 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.

Excel 2007 : Tableau croisé dynamique avec des noms de champs plus représentatif du contenu

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.

Excel 2007 : Décochez un champ de la liste des champs utilisés dans le tableau croisé dynamique *De la liste des champs, décochez le champ que vous ne désirez plus voir dans le tableau croisé dynamique.
OU  
Excel 2007 : Supprimer un champ du tableau croisé dynamique *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.

Excel 2007 : Insérer un champ calculé dans un tableau croisé dynamique

*De l'onglet des Options du tableau croisé dynamique, appuyez sur le bouton Formules.
*Sélectionnez l’option Champ calculé.


Excel 2007 : Entrer les options du champ calculé pour le tableau croisé dynamique

*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.


Excel 2007 : Liste des champs du tableau croisé dynamique avec le nouveau champ calculé


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.

Excel 2007 : Sélectionner deux valeurs d'un tableau croisé dynamique

*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.

Excel 2007 : Grouper deux valeurs dans un tableau croisé dynamique

*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.

Excel 2007 : Premier regroupement de champs du tableau croisé dynamique

Il y a maintenant un regroupement pour les valeurs Administrateur et Secrétaire. Le boutonOffice 2007 : Boite moinssert à masquer les valeurs. Vous pouvez toujours les réafficher en appauyant sur le bouton Office 2007 : Bouton Plus.

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 Office 2007 : Boite moins).
*En gardant un doigt sur la touche CTRL, sélectionnez la cellule ayant le texte Vendeur (celui sans la boite Office 2007 : Boite moins).
*De l’onglet des options du tableau croisé dynamique, sélectionnez l’option Grouper la sélection.

Excel 2007 : Tableau croisé dynamique avec deux regroupements de valeurs

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

Excel 2007 : Changer le nom d'un regroupement à partir de la barre formule

*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.

Excel 2007 : Noms de regroupements appropriés

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.

Excel 2007 : Changez la valeur de la cellule F12 de la feuille de calcul Donnés à 27 000.

*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 $.

Excel 2007 : Bouton Actualiser les données du tableau croisé dynamique

*De l’onglet complémentaire Options de tableau croisé dynamique, appuyez sur le bouton Actualiser.
OU
*Appuyez sur les touches Alt et F5.

Excel 2007 : Résultats actualisés du tableau croisé dynamique

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.

Excel 2007: Onglet des options du tableau croisé dynamique

Excel 2007 : Onglet Création du tableau croisé dynamique

À 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.

Excel 2007 : Bouton Graphique croisé dynamique

*De l’onglet des Options du tableau croisé dynamique, appuyez sur le bouton Graphique croisé dynamique.

Excel 2007 : Graphique Histogramme 3D

*De la liste des types de graphiques disponibles, sélectionnez un histogramme empilé en 3D.
*Appuyez sur le bouton OK.

Excel 2007 : Premier graphique croisé dynamique

Excel 2007 : Menu contextuel pour graphique croisé dynamique

*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.

Excel 2007 : Graphique croisé dynamique avec regroupements de valeurs

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 vous abonner à l'infolettre





Ce site est hébergé par 1&1.com