Download - STATISTIQUE DESCRIPTIVE APPLIQUÉE À EXCEL
STATISTIQUE DESCRIPTIVE APPLIQUÉE À EXCEL
TABLEAU CROISÉ DYNAMIQUE
COURS 2/6
UNIVERSITÉ TOULOUSE JEAN-JAURÈS
Statistique descriptive appliquée à Excel
Année 2016/2017 (version 3.0)
Cours Éric MESERE
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 2/22
SOMMAIRE
I - INTRODUCTION ............................................................................................................... 4
1 - Objectif ........................................................................................................................................... 4
2 - Les bases du tableau croisé dynamique ........................................................................................ 4 a) Données pour construire un tableau croisé dynamique .............................................................................. 5 b) Créer un tableau croisé dynamique ............................................................................................................ 5 c) Insérer les champs dans le tableau ............................................................................................................ 7 d) Déplacer un champ .................................................................................................................................... 8 e) Filtrer et trier les champs ............................................................................................................................ 9 f) Appliquer un filtre sur tout le tableau (Filtre de rapport) ............................................................................... 9 g) Ajouter un nouveau champ VALEURS ..................................................................................................... 10 h) Renommer et paramétrer les champs de valeurs ..................................................................................... 11 i) Supprimer un champ ................................................................................................................................. 12 j) Modifier le format des nombres d’un champ .............................................................................................. 12
3 - Grouper des champs .................................................................................................................... 12 a) Regrouper les champs.............................................................................................................................. 12 b) Changer les noms des groupes ................................................................................................................ 13
4 - Actualiser un tableau croisé dynamique ...................................................................................... 14
5 - Exercices ...................................................................................................................................... 14
II - POURCENTAGE ET SOUS-TOTAUX ............................................................................ 15
1 - Afficher les résultats sous forme de pourcentage ........................................................................ 15
2 - Champs totaux et sous-totaux ..................................................................................................... 17
3 - Exercice ........................................................................................................................................ 18
III - CONCEVOIR UN GRAPHIQUE CROISÉ DYNAMIQUE ............................................... 19
1 - Création d’un graphique ............................................................................................................... 19
2 - Insérer un segment ...................................................................................................................... 21
3 - Exercice ........................................................................................................................................ 21
IV - BIBLIOGRAPHIE .......................................................................................................... 22
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 3/22
Jean-Claude Van Damme
Acteur, réalisateur et producteur de cinéma
Selon les statistiques il y a
une personne sur cinq qui est
déséquilibré. S'il y a quatre
personnes autour de toi et
qu'elles te semblent
normales, c'est pas bon.
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 4/22
I - INTRODUCTION
1 - Objectif
Les tableaux croisés dynamiques est un outil très puissant pour organiser des données brutes. Il est essentiel et efficace pour construire des tableaux d’effectifs entre autre.
Il permet aussi de créer des graphiques croisés dynamiques.
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.
2 - Les bases du tableau croisé dynamique
Pour mieux appréhender l’utilisation des tableaux croisés dynamiques, nous allons découvrir de manière pragmatique son utilisation et ses options.
Pour suivre ce chapitre, vous devez télécharger l’exercice suivant.
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 5/22
2-6 TD1 Créer un tableau croisé dynamique
a) Données pour construire un tableau croisé dynamique
Les données utilisées pour créer une tableau croisé dynamique doivent posséder
obligatoirement un entête de colonne.
b) Créer un tableau croisé dynamique
Sélectionner le tableau (ne pas oublier les entêtes de colonne !)
Dans l’onglet Insertion, cliquer sur Tableau croisé dynamique.
Pour sélectionner rapidement un tableau, cliquez sur une cellule à l’intérieur du
tableau et appuyer simultanément sur les touches +
Assurez-vous d’avoir la bonne sélection des cellules.
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 6/22
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.
Les zones du tableau croisé dynamique
Filtre Permet de filtrer les données du tableau sur un ou plusieurs champs.
Colonnes Regroupe les valeurs communes ensemble et affiche le résultat de chaque valeur dans une colonne.
Lignes Affiche les résultats de la synthèse de chaque valeur sur une ligne
différente.
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 7/22
Valeurs Affiche les résultats des fonctions demandées (somme, nombre, moyenne…) selon les valeurs affichées sur les lignes et les colonnes.
c) Insérer les champs dans le tableau
Déplacer avec la souris le champ
« SALAIRE » dans la zone « VALEURS ».
Par défaut l’option Somme de
SALAIRE sera calculée.
Il est possible d’effectuer d’autres
types de calcul (chapitre suivant).
Sur la feuille de calcul, le tableau se construit.
À ce stade, il fait apparaître la somme des salaires
issue du tableau de données.
Déplacer avec la souris le champ « QUALIFICATION » dans la zone
« LIGNES ».
Le tableau est complété en positionnant dans l’étiquette de lignes des données regroupées par
qualification.
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 8/22
Déplacer avec la souris le champ « SEXE » dans la zone « LIGNES »
juste dessous la variable « QUALIFICATION ».
Cette variable s’ajoute dans la zone des étiquettes de lignes.
d) Déplacer un champ
Déplacer le champ « SEXE » de la zone
étiquette de lignes vers la zone étiquette de colonnes.
Le tableau prend alors l’aspect d’un véritable tableau croisé dynamique.
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 9/22
e) Filtrer et trier les champs
Il est possible de trier directement les résultats du tableau croisé dynamique en passant
par les filtres présents sur l’étiquettes de lignes ou de colonnes.
Une fois le bouton activé, il est possible de réaliser des filtres ou des tris des données.
f) Appliquer un filtre sur tout le tableau (Filtre de rapport)
Pour appliquer un filtre de rapport à
l’intégralité du tableau, il suffit d’insérer par exemple le champ « CATEGORIE » dans la zone « FILTRES ».
Le tableau peut alors être filtrer sur un
champ qui n’apparaît pas dans le tableau.
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 10/22
g) Ajouter un nouveau champ VALEURS
Sélectionnez encore une fois le champ « SALAIREé à positionner dans la zone « VALEURS ».
Dans le tableau croisé dynamique, le
champ Salaire est alors doublé avec avec « Somme de SALAIRE » et « Somme de
SALAIRE2 »
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 11/22
h) Renommer et paramétrer les champs de valeurs
Vous avez deux fois le champ « SALAIRE » dans la zone « VALEURS ». Il est possible
d’utiliser un champ pour la masse salariale et d’un autre pour connaître le nombre de personnes.
Dans le volet des options du tableau croisé dynamique, vous devez cliquer
sur le bouton du menu déroulant en face du champ valeur.
Choisir 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.
Le tableau croisé dynamique montre
maintenant le nombre de personnes ainsi que leur masse salariale
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 12/22
i) Supprimer un champ
Pour supprimer un champ du tableau croisé dynamique, il suffit de retirer le champ hors
du volet des options du tableau croisé dynamique
j) 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.
Cliquer avec le bouton de gauche sur une donnée d’un champ que l’on veut formater.
Puis en sélectionner l’option « Format de nombre… »
Il suffit alors de choisir ses options de formatage.
3 - Grouper des champs
a) Regrouper les champs
À partir du même fichier, on va créer 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 , sélectionnez la cellule ayant le texte Secrétaire.
Appuyez sur le bouton droit de la souris.
De la liste des options, sélectionnez Grouper
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 13/22
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.
b) Changer les noms des groupes
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.
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 14/22
4 - 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 €.
Pour actualiser les données du tableau croisé dynamique, cliquer sur l’onglet complémentaire [Analyse] de tableau croisé dynamique, appuyez sur le bouton
Le grand total des salaires va passer de 394 400 € à 401 900 €.
5 - Exercices
2-6 TD2 TCD Automobiles.xlsx
2-6 TD3 TCD Personnel.xlsx
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 15/22
II - POURCENTAGE ET SOUS-TOTAUX
1 - Afficher les résultats sous forme de pourcentage
Pour suivre ce chapitre, vous devez télécharger le fichier suivant.
2-6 TD4 TCD Totaux pourcentage.xlsx
À 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 »
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 16/22
On obtient le résultat suivant :
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 17/22
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)).
2 - Champs totaux et sous-totaux
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.
Pour travailler sur les totaux généraux, vous devez dans le groupe Disposition de l’onglet Conception, puis cliquer sur Sous-totaux.
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 18/22
Il est possible alors d’effectuer 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 avec les mêmes options concernant les sous-totaux lorsque le
tableau croisé dynamique possède des sélections groupées.
3 - Exercice
2-6 TD5 TCD Vente vins.xlsx
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 19/22
III - CONCEVOIR UN GRAPHIQUE CROISÉ
DYNAMIQUE
Excel permet de présenter les données sous forme d’un graphique croisé dynamique.
1 - Création d’un graphique
Pour suivre ce chapitre, vous devez télécharger le fichier suivant.
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 sélectionnant le tableau puis dans l’onglet Insertion / Groupe Graphique / Graphique croisé dynamique.
On valide le choix par défaut en cliquant sur OK, puis on sélectionne les champs que
l’on souhaite incorporer dans le graphique. Dans notre exemple on mettra :
2-6 TD6 TCD Dépenses Foyer.xlsx
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 20/22
Le graphique est créé (par défaut l’histogramme est proposé).
Vous avez alors accès à l’outil de graphique croisé dynamique avec les onglets :
Analyse
Création
Format
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.
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 21/22
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.
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.
Il ne reste plus qu’à sélectionner la plage des dates souhaitées.
3 - Exercice
2-6 TD7 TCD Salariés.xlsx
Cours
1/6
Statistique descriptive appliquée à Excel
Tableau croisé dynamique
quadribox.fr 22/22
IV - BIBLIOGRAPHIE
La statistique sans formule mathématique : Comprendre la logique et maîtriser les outils de Bernard Py
La Statistique en clair de François Grosjean et Jean-Yves Dommergues
Statistiques descriptives : Cours de Pierre Bailly et Christine Carrère
L'essentiel de Statistique descriptive de Elisabeth Olivier
Statistiques pour la gestion - Applications avec Excel, SPSS, Amos et SmartPLS de Pierre-Charles Pupion
Statistique descriptive - en 27 fiches de Maurice Lethielleux
Initiation aux statistiques descriptives avec Excel de André Massoni
L'analyse statistique des données : Apprendre, comprendre et réaliser avec Excel de Alain Morineau et Yves-Marie Chatelin
Statistique descriptive pour l'économie et la gestion de Bernard Delmas
Statistique descriptive : Séries statistiques à une et deux variables / Séries chronologiques / Indices de Fabrice Mazerolle.
Statistiques descriptives : Applications avec Excel et calculatrices d’Étienne Bressoud et Jean-Claude Kahané.