Université de La Rochelle Informatique Transversale
Partie 1
TP 09 : Analyse de données.
Tri, Filtrage & Tables dynamiques
Objectif du TP :
Lorsqu'on manipule des tableaux de grande taille, il est souvent utile de
pouvoir trier mais également extraire les données qui satisfont un (ou
plusieurs) critère(s). Le début de cette séance va vous faire découvrir les
fonctions de tri et de filtrage offertes par les tableurs.
L’analyse de tableaux de données de grande taille peut rapidement devenir
fastidieuse. Heureusement, les tableurs proposent des outils permettant de
réaliser rapidement des tableaux de synthèse en fonction d’un ou plusieurs
paramètres. Ces outils sont présentés en seconde partie de séance.
Table des matières
I. Préparation......................................................................................................1
II. Tri des données...............................................................................................2
III. Filtres.............................................................................................................4
III.1- Filtres automatiques...............................................................................4
III.2- Filtres standards......................................................................................5
IV. Tables dynamiques........................................................................................6
IV.1- Organisation des données......................................................................6
IV.2- Sélection de la plage de données...........................................................6
IV.3- Paramétrage de la table..........................................................................7
IV.4- Mise en page du premier tableau...........................................................8
IV.5- Modification de la table.........................................................................11
IV.6- Application............................................................................................12
I. Données utilisées..........................................................................................13
II. Travail à réaliser au cours de la séance........................................................13
II.1- Tri des données......................................................................................13
II.2- Filtrage...................................................................................................13
II.3- Extraction d'information........................................................................13
I. Données utilisées..........................................................................................14
II. Travail à réaliser au cours de la séance........................................................14
II.1- Fonctions sur des tableaux de grande dimension..................................14
II.2- Tables dynamiques................................................................................14
II.3- En plus...................................................................................................15
III. Dépôt sur Moodle.........................................................................................15
I. Préparation
Comme d’habitude, créez un dossier « TP09 » dans votre dossier
« Informatique Transversale ». Récupérez sur Moodle, l’archive « Maté[Link] »
et décompressez-la dans votre dossier « TP09 ».
Page 1/15
Université de La Rochelle Informatique Transversale
Partie 1
PARTIE 1 : Tri & Filtres
Afin d'appliquer les techniques décrites ci-dessous, nous utiliserons les
données du fichier [Link]. Ce fichier contient les opérations financières
d'une association.
Ouvrez le fichier [Link].
Observez les données.
Dans ce fichier, les données sont organisées en colonnes. La première ligne
contient des étiquettes qui permettent ainsi d'identifier le contenu de chaque
colonne.
II. Tri des données
Pour trier les données d'un tableau, il faut se placer dans ce tableau, .
Cliquez dans une des cellules du tableau.
Sélectionnez ensuite le menu Données → Trier...
Vous remarquerez que le tableur a sélectionné automatiquement le tableau
(données et entêtes de colonnes).
Remarque : Si Calc ne sélectionne pas correctement le tableau, vous devez
sélectionner vous même le tableau (en incluant les entêtes de colonnes) avant
de sélectionner le menu Données → Trier...
En appelant la fonction de tri, la fenêtre suivante apparaît :
Avant de poursuivre, nous allons vérifier que la fonction de tri est configurée
Page 2/15
Université de La Rochelle Informatique Transversale
Partie 1
correctement pour « gérer » les données organisées en colonnes.
Allez dans l'onglet Options.
Dans la partie Direction, vérifiez que l'option Du haut vers le bas (trier des
lignes) est cochée.
Vérifiez ensuite que l'option La plage contient des étiquettes de colonne est
cochée.
Conservez l'option Inclure les formats cochée.
Revenir ensuite dans l'onglet Critères de tri.
Nous souhaitons trier les données en fonction du numéro d'opération (colonne
B).
Modifiez, en utilisant la liste déroulante sous le terme Clé de tri 1, le champ
actuel pour faire apparaître le champ « N° ». Il correspond à notre critère de
tri.
Validez et observez le résultat.
Dans l'exemple précédent, vous n'avez appliqué qu'un seul critère. Supposons
que l'on souhaite trier les données par date et par n° de Chèque.
Modifiez, en utilisant la liste déroulante sous le terme Clé de tri 1, le champ
pour faire apparaître le champ « Date ». Il correspond à notre premier critère
de tri.
Modifiez ensuite, en utilisant la liste déroulante sous le terme Clé de tri 2, le
champ pour faire apparaître le champ «N° chèque ou remise chèque ». Il
correspond à notre second critère de tri.
Validez et observez le résultat.
Vous avez trié ce tableau par date et pour chaque date le tableau est trié en
fonction des n° de chèque.
Il est possible de cumuler jusqu'à 3 critères et pour chacun d'entre eux il est
possible de spécifier si le tri doit être fait dans l'ordre croissant ou décroissant.
Trier les données par « Date », par « Activité » et par « Crédit » décroissant.
Trier ensuite les données par « Date de val » décroissante.
Page 3/15
Université de La Rochelle Informatique Transversale
Partie 1
III. Filtres
Les filtres permettent de sélectionner, parmi un grand volume de données,
uniquement celles qui satisfont certains critères.
III.1- Filtres automatiques
Les filtres automatiques permettent de sélectionner des données en utilisant
simplement des listes déroulantes.
Nous utiliserons également le fichier [Link].
Comme précédemment, se positionner dans une des cellules du tableau.
Sélectionner le menu Données → Filtre → AutoFiltre
L'icône apparaît dans chaque cellule correspondant aux entêtes de
colonnes.
En cliquant à la souris sur cet icône, vous aurez accès à une liste
déroulante, qui propose une synthèse des données présentes dans la
colonne.
En sélectionnant une de ces données, celle-ci devient un critère de
filtrage.
Seules les lignes du tableau possédant la donnée sélectionnée
s'afficheront
Exemple :
Dans la liste déroulante « Crédit », sélectionner 15.
Observez le résultat.
Remarquez que :
• Certaines lignes du tableau ont été masquées (seules les lignes 17, 20,
25, 42 sont visibles).
• La colonne pour laquelle une sélection a été faite est signalée par une
Page 4/15
Université de La Rochelle Informatique Transversale
Partie 1
liste déroulante à flèche bleue.
Il est possible de cumuler les conditions. Par exemple, si je veux savoir quels
sont les activités billetterie de type théâtre, je sélectionne billetterie dans la
liste Activités, et dans la liste Objet je choisis théâtre. Les colonnes pour
lesquelles une sélection a été faite sont signalées par une liste déroulante à
flèche bleue.
Pour afficher à nouveau toutes les données, il suffit de choisir Tout dans les
listes déroulantes.
Pour supprimer les listes déroulantes, sélectionner à nouveau Données →Filtre
→ AutoFiltre.
III.2- Filtres standards
Les filtres standards permettent de filtrer le tableau de données en appliquant
des tests logiques (conditions) sur une ou plusieurs étiquettes.
Comme précédemment, se positionner dans une des cellules du tableau.
Sélectionner le menu Données → Filtre → Filtre Standard
On souhaite afficher les mouvements de trésorerie entre le 14 et le 21 mai
2006. Dans la fenêtre Filtre Standard, nous devons donc établir les conditions
présentées dans l'image suivant :
A l'aide des filtres standards :
Affichez les mouvements de trésorerie pour lesquels il n'y a aucun n° de
chèque.
Conseil : ce sont les cellules pour lesquelles le champ N° Chèque ou …
sont vides.
Page 5/15
Université de La Rochelle Informatique Transversale
Partie 1
PARTIE 2 : Tables dynamiques
IV. Tables dynamiques
Une table dynamique permet de créer rapidement un tableau de synthèse à
partir de grandes quantités de données. De plus, ce tableau est dynamique.
Cela veut dire qu’il vous est possible d’ajouter des éléments, d’en retirer et de
modifier la présentation du tableau.
IV.1- Organisation des données
Pour créer une table dynamique, les données doivent être sous la forme
suivante :
• Chaque colonne représente un champ (Ex : le nom, le salaire, l'âge, ...)
• La première ligne du tableau contient le nom des champs (Ex : NOM,
SALAIRE, AGE, …)
On utilisera le fichier [Link].
Observez le tableau source afin de comprendre le sens et la nature les
données manipulées
Quels sont les champs de ce tableau ?
Remarque : Les en-têtes du tableau sont intéressants pour saisir la
sémantique associée aux données.
IV.2- Sélection de la plage de données
La première chose que nous allons faire est de sélectionner la zone
correspondant aux données que nous voulons étudier et de définir la plage de
données.
Sélectionner la plage A1:K286.
Sélectionner dans le menu Données → Définir la plage...
Dans le champ Nom, saisir un nom (par exemple Données) et cliquer sur
Ajouter.
Valider par OK
La plage de données est maintenant définie ce qui facilitera l’analyse
automatique du tableur pour la suite.
Pour mettre en place une table dynamique, il suffit de procéder comme suit :
Cliquer dans une des cellules du tableau [Link].
Page 6/15
Université de La Rochelle Informatique Transversale
Partie 1
Sélectionner dans le menu Insertion → Table dynamique
Remarque : Comme pour les opérations de tris et les filtres, on peut
sélectionner la plage manuellement (Ici il s'agit de la plage A1:K286), ou, si elle
est encadrée de cellules vides, sélectionner une cellule de la plage et laisser
opérer la sélection automatique de la plage par le tableur.
IV.3- Paramétrage de la table
Vous obtenez le message suivant :
Accepter la sélection active (zone bleue) : dans cet exemple, la plage de
cellules notée A1:K286
Vous devriez maintenant avoir la fenêtre de la table dynamique affichée ci-
dessous :
Page 7/15
Université de La Rochelle Informatique Transversale
Partie 1
Si vous observez l'image ci-dessus, vous remarquerez que Calc a intégré
automatiquement les champs, sous forme de boutons. Ceux-ci contiennent les
informations nécessaires à la réalisation du tableau croisé.
Ces champs sont définis à partir des entêtes de colonne de la feuille des
données que vous avez étudiées précédemment. On distingue les champs
identifiant les données (MATRICULE, NOM, PRENOM, ...) appelés également
champs « catégories » et les champs de données sur lesquels une opération
peut être appliquée (SALAIRE, AGE).
IV.4- Mise en page du premier tableau
On souhaite obtenir un tableau récapitulatif du total des salaires par sexe et
par qualification et ceci par site. Si vous deviez le faire par vous-même,
comment procéderiez-vous ? Et surtout combien de temps vous faudrait-il pour
réaliser ce tableau ? Avec le pilote de données, rien n’est plus simple. Il vous
suffit de :
faire glisser le bouton « SITE » dans la zone « Champs de la page » ,
faire glisser le bouton « sexe » dans la zone « Champs Ligne »,
faire glisser le bouton « Qualification » dans la zone « Champs de colonne »,
faire glisser le bouton « SALAIRE » dans la zone « Champs de données ».
Cliquer sur Source et destination, ajuster la destination pour insérer le
résultat dans la cellule B3 de la feuille de calcul Résultats.
Remarque importante : Notez qu’il est possible de mettre plusieurs champs
par zone.
Page 8/15
Université de La Rochelle Informatique Transversale
Partie 1
Il y a donc 4 zones de « champs » à renseigner pour concevoir le tableau
croisé. Ici; vous devriez obtenir la fenêtre suivante :C'est terminé, valider par
OK.
Vous devriez obtenir le résultat suivant :
Remarque : Ce tableau présente différents boutons. Ce nombre de boutons
variera selon les paramétrages effectués au cours de l'élaboration du Pilote de
données.
Ici, vous apercevez :
• celui du champ 'SITE',
• ceux des champs 'sexe' et 'qualification',
• un bouton marqué qui fonctionne selon le même principe que
les filtres (cf. TP4) et qui sera bien utile pour n'afficher que les
informations qui correspondent à certains critères.
Observons l'effet du bouton SITE que nous avons placé dans les « champs de la
page ». La liste déroulante affiche '- tout -'. Cela signifie que le calcul du total
des salaires concerne tous les sites.
Dans la liste déroulante, choisir Lille
Page 9/15
Université de La Rochelle Informatique Transversale
Partie 1
Observer les modifications au niveau du tableau.
Vous pouvez constater que le tableau est actualisé automatiquement en
fonction du SITE choisi. Ainsi vous pouvez générer non pas un, mais plusieurs
tableaux en fonction du critère (site) choisi. Si un graphique est attaché aux
données du tableau, le graphique est actualisé en même temps.
Remarque : Attention toutefois, si le choix conduit à modifier le nombre de
lignes ou de colonnes du tableau, la plage source du graphique doit être
réajustée manuellement.
L'exemple précédent avait pour objectif de vous présenter un exemple
complet. Si les champs de colonnes, champs de lignes et champs de données
sont obligatoires, la partie champ de page n'est pas toujours nécessaire. Ainsi
dans l'exemple précédent, il était possible d'envisager la création d'un tableau
de synthèse du total des salaires par sexe et qualification.
Le tableau obtenu (voir ci-dessous) est donc créé pour l'ensemble des sites.
Cependant il reste dynamique dans le sens où toute modification des données
d'origine du tableau (ici la plage A1:K286) sera répercutée sur le tableau crée
par le pilote de données lors que vous demanderez sa mise à jour.
La mise à jour est réalisée par un clic droit sur les « boutons de champs » (ici
Qualification et Sexe) et en choisissant Actualiser.
Page 10/15
Université de La Rochelle Informatique Transversale
Partie 1
Changer sur la ligne 2, le salaire 21433,02 en 1021433,02 (feuille de
Données)
Actualiser le tableau croisé et observer les modifications.
IV.5- Modification de la table
Il est possible de modifier une table existante par un clic droit sur les « boutons
de champs » en choisissant Editer la mise en page.... Vous obtenez de
nouveau la fenêtre de paramétrage de la table.
Vous pouvez ainsi :
• Ajouter des champs (par cliquer-déposer dans l'une des 4 zones comme
précédemment)
• Supprimer des champs (par cliquer-déposer en tirant le champ hors de la
zone)
• Modifier le type d'opération réalisé sur les données du « champ de
données »
Pour ce dernier point, vous avez dû remarquer que, par défaut, l'opération
somme a été utilisée pour le champ « SALAIRE ». Il est possible de changer
l'opération en cliquant sur le champ concerné dans la zone de données (ici
« Somme - SALAIRE »), puis de cliquer sur le bouton Options... (sous le bouton
supprimer). La fenêtre suivante permet de sélectionner l'opération à appliquer
sur le champ.
Les fonctions disponibles sont : somme, moyenne, nombre (nb, nbval), max,
min, etc.
Le bouton Plus (située en bas à droite du Table de pilote – voir sur l'image page
5) permet de modifier la structure du tableau et d'inclure ou ne pas inclure
(total par lignes, colonnes, présence du bouton filtre ou non). Si ces éléments
ne sont pas utiles, vous pouvez les supprimer.
Page 11/15
Université de La Rochelle Informatique Transversale
Partie 1
Modifier le tableau actuel pour faire une synthèse présentant la moyenne
des salaires par sexe et par site. Vous supprimerez les totaux par ligne et le
bouton filtre.
IV.6- Application
Réalisez dans des feuilles de calculs distinctes les tables suivantes :
Age moyen par site en fonction du sexe et de la qualification.
Nombre de personnes par pièce pour chaque site.
Page 12/15
Université de La Rochelle Informatique Transversale
Partie 1
PARTIE 3 : Exercice « Filtrage et Tri »
I. Données utilisées
Nous utiliserons le fichier TournagesFilmsParis_2002-[Link].
Ce fichier contient des informations sur les 8593 lieux de tournage
correspondant aux 600 films tourner à Paris entre 2002 et 2010.
Ce tableau étant relativement important nous allons utiliser les méthodes de tri
et de filtrage étudiées précédemment.
Ouvrir le fichier TournagesFilmsParis_2002-[Link].
Observer le contenu du tableau
II. Travail à réaliser au cours de la séance
II.1- Tri des données
• Trier les données par titre et par date de début d’événement.
II.2- Filtrage
• Filtrer les données, de manière à visualiser les réponses correspondant
aux critères suivants :
1. Les films tournées par Clint Eastwood
2. Les films tournées dans le 12ème arrondissements sur un boulevard.
3. Les films tournées dans un cimetière du 20ème arrondissement en
2006.
• Supprimer les listes déroulantes avant de passer à la suite.
II.3- Extraction d'information
Vous allez maintenant extraire vers la feuille de calcul Filtre les informations qui
satisfont les critères suivants :
1. Films tournés sur le Domaine Public, dans un bois dans le 16ème
arrondissement.
2. Films d'Agnes Varda, tournés sur un pont du 6ème arrondissement.
3. Films de Luc Besson tournés sur une place le 27 et 30 octobre 2009.
4. Films tournées dans un bois entre le 1 er janvier 2007 et le 31 décembre
2008
Page 13/15
Université de La Rochelle Informatique Transversale
Partie 1
PARTIE 4 : Exercices « Tables dynamiques »
I. Données utilisées
Nous utiliserons le fichier Effectifs_Etudiants-[Link].
Ce fichier contient des informations sur les étudiants inscrits dans les
établissements publics d'enseignement supérieur et de recherche entre 2006
et 2013. Ce fichiers contient les données sur les établissements, leur région,
département et académie d'appartenance, les formations, les effectifs, etc.
L'onglet Légende donne la signification de chacune des colonnes. L'onglet
Effectifs contient le tableau de données. Ce tableau contient 31 colonnes et
346 892 lignes et possède une taille d'environ 18 Mo (au format ODS). Le
tableau est donc relativement important et donne une idée du volume de
données que vous pourriez être amené à traiter lors d'un stage ou dans un
futur emploi.
Remarque : En raison de la taille du fichier, il est possible que les temps de
chargement et de calcul prennent, en fonction des caractéristiques de votre
machine quelques secondes voire quelques dizaines de secondes, soyez donc
un peu patient...
Ouvrir le fichier Effectifs_Etudiants-[Link].
Observer le contenu du tableau.
II. Travail à réaliser au cours de la séance
II.1- Fonctions sur des tableaux de grande dimension
On souhaite calculer le total des différents types d'étudiants (colonne Z à AE)
sur la ligne 185837.
• Réaliser la somme des différentes colonnes.
Remarque : on évitera de réaliser la formule avec la souris.
II.2- Tables dynamiques
Réaliser dans des feuilles distinctes, les tableaux ci-dessous :
Q1 : Le total des effectifs par académie et par sexe pour les années
Page 14/15
Université de La Rochelle Informatique Transversale
Partie 1
universitaires de 2010-11 à 2012-13.
Q2 : le nombre de diplôme par type d'établissement pour les années 2010-11 à
2012-13.
Q3 : le total des étudiants venant des pays composant le BRICS par région et
par cursus LMD.
Q4 : le total des nouveaux bacheliers et des pays de l'union européenne (U28)
par grande discipline et discipline en fonction du type de baccalauréats.
II.3- En plus
Q5 : le total des effectifs par grande discipline, discipline et secteur
disciplinaire pour les années 2010-11 à 2012-13
Q6 : l'effectif total par type de baccalauréats et série de baccalauréats pour
l'année 2012-2013
III. Dépôt sur Moodle
Afin de ne pas perdre vos bonnes habitudes, vous allez compresser et déposer
sur Moodle le travail réalisé aujourd'hui.
• Enregistrer votre travail et fermer Libre Office. Aucune fenêtre Calc ne
doit rester ouverte.
• Compresser le dossier TP09 (Celui est censé contenir le travail réalisé).
• Renommez l'archive obtenue afin qu'elle se nomme nom_pré[Link]
(Attention avec VOTRE nom et VOTRE prénom).
• Déposer l'archive sur Moodle en accédant au lien Dépôt_TP09 dans la
rubrique TP9
Page 15/15