3-6_sd_tableau_croise_dynamique.pdf

EXCEL - STATISTIQUE DESCRIPTIVE TABLEAU CROISÉ DYNAMIQUE

COURS 3/6

Université TOULOUSE MIRAIL Statistique descriptive appliquée à Excel

Année 2012/2013 (version 1.0) Cours Éric MESERE

Cours 3/6 Statistique descriptive appliquée à Excel Tableau Croisé Dynamique

/20

SOMMAIRE

1. INTRODUCTION............................................................................................................ 3 1.1. Objectif .................................................................................................................................. 3 1.2. Schéma de principe ............................................................................................................. 3

2. LES BASES DU TABLEAU CROISÉ DYNAMIQUE PAR L’EXEMPLE ........................ 4 2.1. Préparer l’insertion du Tableau Croisé Dynamique ......................................................... 4 2.2. Les quatre zones .................................................................................................................. 5 2.3. Remplir le tableau ................................................................................................................ 5 2.4. Déplacer un champ .............................................................................................................. 6 2.5. Filtrer les champs ................................................................................................................. 6

2.5.1. Filtrer un champ situé dans la liste des données .............................................................................. 7 2.5.2. Filtrer un champ situé dans le tableau croisé dynamique ................................................................. 7 2.5.3. Filtrer le tableau sur un champ qui n’est pas dans le tableau (filtre du rapport) ............................... 8

2.6. Ajouter un champ ................................................................................................................. 8 2.7. Renommer les champs et changer ses propriétés ........................................................... 9 2.8. Modifier le format des nombres d’un champ .................................................................... 9

2.8.1. Formatage des nombres à partir des champs du tableau croisé dynamique ................................... 9 2.8.2. Formatage des nombres à partir de la liste de champs .................................................................. 10

2.9. Retirer un champ ................................................................................................................ 11 2.10. Trier des champs ................................................................................................................ 11 2.11. Regrouper les champs....................................................................................................... 11 2.12. Changer les noms des groupes ........................................................................................ 13 2.13. Actualiser un tableau croisé dynamique ......................................................................... 13 2.14. Exercices de base sur les tableaux dynamiques croisés .............................................. 13

3. POURCENTAGE ET TOTAUX .................................................................................... 14 3.1. Afficher les résultats sous forme de pourcentage ......................................................... 14 3.2. Champs de totaux et de sous-totaux dans un rapport de tableau croisé dynamique 16 3.3. Exercice sur les options de calculs et les sous-totaux .................................................. 17

4. CONCEVOIR UN GRAPHIQUE CROISÉ DYNAMIQUE .............................................. 18 4.1. Création d’un graphique .................................................................................................... 18 4.2. Insérer un segment ............................................................................................................ 19 4.3. Exercices ............................................................................................................................. 20


/20

1. INTRODUCTION

1.1. Objectif Les tableaux croisés dynamiques permettent de générer rapidement un tableau de synthèse à partir de données brutes (ou une base de données). On peut alors effectuer des analyses rapides et efficaces. Le tableau est dit croisé parce que l'on met des données à l'intersection (le « croisement ») entre des champs disposés en ligne et en colonne. Il est dynamique parce que l'on peut apporter des changements à la table des données brutes, il suffit de demander de « rafraîchir » pour que le tableau croisé soit actualisé.

1.2. Schéma de principe

Une source de données externe contient des données structurées organisées sous la forme d’un ou de plusieurs champs (colonnes) affichés dans la liste des champs. Déplacez un champ vers la zone de filtre du rapport dans la liste des champs, ce qui déplace simultanément le champ vers la zone Filtre du rapport dans le rapport de tableau croisé dynamique. Déplacez un champ vers la zone Étiquette de colonnes dans la liste des champs, ce qui déplace simultanément le champ vers la zone Étiquette de colonnes du rapport de tableau croisé dynamique. Déplacez un champ vers la zone Étiquette de lignes dans la liste des champs, ce qui déplace simultanément le champ vers la zone Étiquette de lignes du rapport de tableau croisé dynamique. Déplacez un champ vers la zone Valeurs de colonnes dans la liste des champs, ce qui déplace simultanément le champ vers la zone Valeurs du rapport de tableau croisé dynamique.


/20

2. LES BASES DU TABLEAU CROISÉ DYNAMIQUE PAR L’EXEMPLE Pour suivre cet exercice, vous pouvez aller chercher le classeur demo1-bases_TCD.xlsx.

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.

2.1. Préparer l’insertion du Tableau Croisé Dynamique • Sélectionner le tableau. • Dans l’onglet Insertion, cliquer sur TblCroiséDynamique.

Pour sélectionner rapidement un tableau, cliquez sur une cellule à l’intérieur du tableau et appuyer

simultanément sur les touches +


/20

Assurez-vous d’avoir la bonne sélection de cellules.

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

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 accéder à nouveau à ses options. Le tableau croisé dynamique est composé de quatre zones. Chacune à une fonction particulière.

2.2. Les quatre zones

Filtre du rapport Permet 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.

2.3. 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 cours.

• Cochez la case à côté du champ Titre


/20

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. Le tableau est plus difficile à lire. Il serait préférable de déplacer ce champ dans la zone des étiquettes de colonnes.

2.4. Déplacer un champ

Placez le pointeur de la souris 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.

Le tableau est alors plus facile à lire. Un champ a été déplacé de zone.

2.5. Filtrer les champs Parfois, vous ne voulez pas avoir de vision globale des données, mais plutôt filtrer sur un groupe qui pourrait mieux éclairer votre analyse. Le tableau croisé offre plusieurs façons élégantes de filtrer.


/20

2.5.1. 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 filtres, vous allez voir une liste des valeurs contenues 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 devant 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. Vous remarquerez aussi qu'il y a l'icône d'un filtre à côté d’Étiquettes de lignes.

2.5.2. Filtrer un champ situé dans le tableau croisé dynamique En plus de filtrer un champ actif du tableau, vous pouvez filtrer directement depuis le tableau croisé dynamique.

Appuyez sur le symbole filtre à la fin des Etiquettes 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 ».


/20

2.5.3. 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 au-dessus du 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.

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


/20

2.7. Renommer les champs et changer ses propriétés Vous avez maintenant deux fois le champ Salaire dans la zone valeur. Vous avez besoin d’un champ pour déterminer la masse salariale et d’un autre pour connaître le nombre de personnes qui sont dans ce groupe.

Dans le volet des options du tableau croisé dynamique, vous pouvez changer le nom du champ en cliquant dessus et en choisissant l’option « Paramètres des champs de valeurs… »

À partir de cette fenêtre, vous pouvez changer le nom du champ ainsi que certaines propriétés.

• Faites 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. • Changez le nom à Salaires. • Appuyez sur le bouton OK.

Le tableau croisé dynamique montre maintenant le nombre de personnes après les regroupements ainsi que leur masse salariale. Le nom des champs a aussi été changé pour mieux représenter le contenu.

2.8. Modifier le format des nombres d’un champ En fonction du type de valeurs que vous analysez, il peut être utile de formater les nombres. Il existe deux manières pour mettre en forme un champ présent dans un tableau croisé dynamique.

2.8.1. Formatage des nombres à partir des champs du tableau croisé dynamique • On formate directement à travers le tableau croisé dynamique en cliquant avec le bouton de gauche sur une

donnée d’un champ que l’on veut impacter.

• Puis en cliquant sur l’option « Format de nombre… »


/20

Il suffit alors de choisir ses options de formatage.

Le champ Salaire est alors formaté en unité monétaire euro.

2.8.2. Formatage des nombres à partir de la liste de champs Il est possible d’obtenir le même résultat à partir de la liste de champs de tableau croisé dynamique.


/20

• Dans la zone Liste de champs, cliquez sur le libellé Somme de SALAIRE.

• Appuyez sur le bouton gauche de la souris.

• Sélectionnez l’option Paramètres des champs de valeurs.

Vous cliquez alors sur l’option Format de Nombre.

Dès lors, vous allez retomber sur la même fenêtre « Format de Cellule » vue précédemment.

2.9. Retirer un champ

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.

2.10. Trier des champs Il est possible de trier les champs d’un tableau croisé dynamique. Il faut savoir que le tri est effectué automatiquement lors de la création du tableau croisé dynamique, mais pas lors de sa mise à jour. Dans ce dernier cas, il suffit :

• De faire un clic droit sur le champ concerné, • De pointer l’option Trier puis cliquer sur Trier de A à Z ou autre…

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


/20

• Dans le tableau croisé dynamique, sélectionnez la cellule ayant le texte Administrateur.

• En gardant un doigt sur la touche , 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 bouton sert à masquer les valeurs. Vous pouvez toujours les réafficher en appuyant 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 , 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.

Lorsque vous souhaitez regrouper des données de type date ou numérique, Excel vous propose un regroupement automatique. Pour cela, il suffit de cliquer sur une cellule de la colonne étiquette, et de cliquer sur le

bouton . Une fenêtre vous propose un regroupement automatique.


/20

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

2.13. 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 € à 30 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 (Alt + F5).

Le grand total des salaires va passer de 394 400 € à 401 900 €.

2.14. Exercices de base sur les tableaux dynamiques croisés Trois tableaux dynamiques permettent d’appliquer les notions vues ci-dessus :

• TD1-Automobiles.xlsx • TD2-Personnel.xlsx

L’énoncé de chaque exercice se trouve à chaque fois sur le premier onglet de chaque classeur.


/20

3. POURCENTAGE ET TOTAUX Pour suivre cet exercice, vous pouvez aller chercher le classeur demo2-Stages.xlsx.

3.1. Afficher les résultats sous forme de pourcentage À partir de ces données, vous créez un tableau dynamique croisé qui permet de visualiser la durée totale de chaque type de stage en fonction de la période. Vous avez le tableau suivant :

Il peut être intéressant de connaître la répartition des nombres de jours de stages par sport par rapport à chaque période. Cliquez avec le bouton droit de la souris sur une valeur pour sélectionner l’option « Paramètres de champs de valeurs » dans le menu contextuel.

Activer l’onglet « Afficher les valeurs », dans la zone Afficher les valeurs, sélectionnez l’option % du total de ligne. Dans la zone Nom personnalisé, saisissez le texte « % durée stages par période »


/20

On obtient le résultat suivant :

On peut aussi arriver au même résultat en passant par l’onglet Options, dans le groupe Calculs, en cliquant sur Afficher les valeurs.


/20

Nous l’avons vu précédemment, le menu déroulant « Afficher les valeurs » propose plusieurs options de calculs pour afficher les valeurs du tableau :

OPTION DE CALCUL RÉSULTAT

Pas de calcul Affiche la valeur qui est entrée dans le champ.

% du total général Affiche les valeurs sous forme de pourcentage du total général de toutes les valeurs ou points de données du rapport.

% du total de la colonne

Affiche toutes les valeurs de chaque colonne ou série sous forme de pourcentage du total de la colonne ou des séries.

% du total de la ligne Affiche les valeurs de chaque ligne ou catégorie sous forme de pourcentage du total de la ligne ou de la catégorie.

% de Affiche les valeurs sous forme de pourcentage de la valeur de l’élément de base dans le Champ de base.

% du total de la ligne parente

Calcule les valeurs de la façon suivante : (valeur pour l’élément) / (valeur pour l’élément parent dans les lignes)

% du total de la colonne parente

Calcule les valeurs de la façon suivante : (valeur pour l’élément) / (valeur pour l’élément parent dans les colonnes)

% du total parent Calcule les valeurs de la façon suivante : (valeur pour l’élément) / (valeur pour l’élément parent du Champ de base sélectionné)

Différence par rapport Affiche les valeurs comme la différence par rapport à la valeur de l’élément de base dans le Champ de base.

Différence en % par rapport

Affiche les valeurs comme la différence en pourcentage par rapport à la valeur de l’élément de base dans le Champ de base.

Résultat cumulé par Affiche les valeurs pour des éléments successifs dans le Champ de base sous forme de total cumulé.

% résultat cumulé par Calcule la valeur en tant que pourcentage pour des éléments successifs dans le Champ de base qui s’affichent comme total cumulé.

Classer du plus petit au plus grand

Affiche le rang des valeurs sélectionnées dans un champ spécifique, en attribuant au plus petit élément dans le champ la valeur 1 et à chaque valeur plus élevée une valeur de classement supérieure.

Classer du plus grand au plus petit

Affiche le rang des valeurs sélectionnées dans un champ spécifique, en attribuant au plus grand élément dans le champ la valeur 1 et à chaque valeur plus petite une valeur de classement supérieure.

Index Calcule les valeurs de la façon suivante : ((valeur dans la cellule) x (Total général)) / ((Total général de la ligne) x (Total général de la colonne)).

3.2. Champs de totaux et de sous-totaux dans un rapport de tableau croisé dynamique

Lorsque vous travaillez avec un rapport de tableau croisé dynamique, vous pouvez afficher ou masquer les sous-totaux de champs de colonnes et de lignes. Vous pouvez afficher et masquer les totaux généraux des lignes et des colonnes pour le rapport complet. Il est même possible de calculer les sous-totaux et les totaux généraux avec ou sans éléments filtrés. Pour les étiquettes de lignes externes sous forme compacte ou hiérarchique, vous pouvez afficher les totaux au-dessus ou au-dessous de leurs éléments. Les totaux peuvent être masqués. Procédez comme suit :

• Dans le groupe Disposition de l’onglet Conception, cliquez sur Sous-totaux.


/20

Effectuez une des actions suivantes :

• Sélectionnez Désactivé pour les lignes et les colonnes,

• Sélectionnez Activé pour les lignes et les colonnes, • Sélectionnez Activé pour les lignes uniquement, • Sélectionnez Activé pour les colonnes uniquement.

Vous pouvez jouer sur les mêmes options des sous-totaux lorsque le tableau croisé dynamique possède des sélections groupées.

3.3. Exercice sur les options de calculs et les sous-totaux Un tableau croisé dynamique permet d’appliquer les notions vues ci-dessus :

• TD3-Vente_Vins.xlsx L’énoncé de l’exercice se trouve à chaque fois sur le premier onglet de chaque classeur.


/20

4. CONCEVOIR UN GRAPHIQUE CROISÉ DYNAMIQUE Excel 2010 permet 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. Pour aborder cette partie, vous devez utiliser le fichier demo3-Depenses_Foyer.xlsx.

4.1. Création d’un graphique Construire un graphique revient en grande partie à concevoir un tableau croisé dynamique. Nous allons donc débuter par la création d’un tableau croisé dynamique comme ci-dessous :

On crée le graphique croisé dynamique en cliquant dans le tableau puis dans les outils de tableau croisé dynamique, onglet Options, groupe Outils, « Graphique croisé dynamique »

On valide le choix par défaut en cliquant sur OK.


/20

La magie de l’informatique va faire apparaitre un graphique incorporé sur la feuille. Vous avez alors accès à l’outil de graphique croisé dynamique avec onglet :

• Création, • Disposition, • Mise en forme,

• Analyse. On peut dès lors :

• Ajouter des titres au graphique et aux axes, • Filtrer les éléments d’un graphique,

• Insérer des zones de textes, ou des champs dynamiques, • Modifier le type de graphique ou sa mise en forme, • Appliquer des styles.

4.2. Insérer un segment Cette nouvelle fonctionnalité est présente depuis Excel 2010. Elle permet de filtrer très simplement les informations sources. Effectuez un clic sur le graphique pour le sélectionner. Dans l’onglet Outils de graphique croisé dynamique – Analyse, cliquez sur le bouton insérer un segment du groupe Données. La boîte de dialogue Insérer des segments est affichée. Cochez uniquement le champ DATES, puis validez par OK. La fenêtre de sélection du segment des dates apparaît dans la feuille, toutes les dates présentes dans les données sources sont affichées.


/20

Il ne reste plus qu’à sélectionner la plage de dates souhaitées.

4.3. Exercices TD4-SALARIES.xlsx L’énoncé de l’exercice se trouve à chaque fois sur le premier onglet de chaque classeur.

3-6_sd_tableau_croise_dynamique.pdf

Documents