Exercice sur une table de données de musiciens de jazz
L’exercice se pratique depuis le fichier suivant :
Naviguer dans une table de données avec les raccourcis clavier
Ctrl + Origine pour positionner votre sélection en cellule A1,
Ctrl + Fin pour vous rendre à la fin de la table, dernière ligne et dernière colonne,
Ctrl + Origine pour revenir en A1,
Ctrl + flèche à droite pour vous rendre à la première cellule vide sur la première ligne,
Ctrl + flèche vers le bas pour vous rendre à la dernière cellule non vide avant la première cellule vide :-), vous devriez être sur la même cellule que celle obtenue avec un Ctrl + Fin,
si vous ajoutez un doigt sur la touche Maj, au-dessus de la touche Ctrl, vous obtenez les mêmes déplacements avec une sélection des cellules,
Ctrl + Maj + Fin sélectionne toute la table, depuis la cellule en cours jusqu’à la fin.
L’AutoFiltre
Placez-vous dans une cellule de la table,
cliquez sur le menu Données,
choisissez Filtre, AutoFiltre,
observez les flèches qui viennent d’apparaître sur la première ligne, dans chaque cellule,
cliquez sur la flèche de la cellule instrument,
décochez tout, sélectionnez seulement guitar,
validez, vous obtenez les guitaristes de jazz de la base,
cliquez sur la flèche de la cellule sexe,
décochez f, validez, vous obtenez les guitaristes hommes de la table de données,
remarquez que les flèches permettant ce filtre changent de couleur, elles deviennent bleues.
La suppression des filtres
Cliquez, depuis la barre de menus, sur Données, puis Filtre, Supprimer le filtre,
Masquez l’AutoFiltre peut aussi être choisi plutôt que de le supprimer ; il vous faudra alors passer par le Filtre standard..., supprimer le premier champ et valider pour retrouver votre table complète,
demandez Filtre standard..., notez l’affichage de tous les filtres en cours, modifiez pour Aucun le contenu du premier champ pour voir tous les filtres disparaître.
La feuille tableur
Changez son nom pour base jazz pour vous rappeler qu’il s’agit des données,
n’insérez rien dans la table de données, sinon des données respectant la structure des enregistrements existante. Cette feuille doit rester une table de données uniquement.
Le traitement des données
La table de pilote, pour quoi faire ?
La table de pilote permet de faire des tableaux croisés dynamiques (TCD) présentant :
une distribution (ou un tri à plat), un dénombrement (comptage),
un tri croisé (ou multivarié) dans un tableau contenant des étiquettes de lignes et des étiquettes de colonnes,
un tri croisé (ou multivarié) dans un tableau contenant des filtres sur des modalités ou valeurs contenues dans la table de données.
Les TCD vous permettent de générer :
des calculs statistiques, mathématiques, financiers,
des filtres par modalités, dates,
des groupements de modalités, de dates.
Le tri à plat sur le genre
Faites un tri à plat sur le "genre" de la colonne sexe,
sélectionnez la colonne D,
cliquez sur le menu Données, Table de pilote, Créer,
validez Sélection active,
découvrez la représentation du tableau en cours de création,
déplacez la variable "sexe" par un glisser-déposer dans la partie Champs de ligne située à votre gauche,
déplacez à nouveau la variable extérieure dans le Champs de données, la partie centrale du tableau,
double-cliquez sur le mot Somme - sexe qui apparaît dans le Champs de données, puis choisissez la fonction Nombre qui assure le dénombrement de chacune des modalités de cette variable,
validez,
validez,
découvrez le tableau généré : un tri à plat sur la colonne D,
remarquez la cellule A1 comme étant la première du tableau,
renommez la feuille en double-cliquant sur son nom : "distributions".
Le tri à plat sur les instruments
Faites un tri à plat sur la colonne G, la variable "instrument",
sélectionnez la colonne G,
cliquez sur le menu Données, Table de pilote, Créer,
validez Sélection active,
déplacez la variable "instrument" par un glisser-déposer dans la partie Champs de ligne située à votre gauche,
déplacez à nouveau la variable extérieure dans le Champs de données, la partie centrale du tableau,
double-cliquez sur Somme - instrument qui apparaît dans le Champs de données, puis choisissez la fonction Nombre qui assure le dénombrement de chacune des modalités de cette variable,
validez,
cliquez sur le bouton inférieur droit, dénommé Plus, qui ouvre une nouvelle boîte de dialogue sur les options du tableau dynamique en cours de création,
choisissez Résultats à - indéfini -, puis cliquez dans le formulaire à suivre ou bien sur le bouton de réduction avec la petite flèche verte,
rendez-vous à l’endroit désiré. Une fois la boîte de dialogue réduite, cliquez sur la feuille "distributions", puis en A12. Le formulaire renseigne automatiquement : $distributions.$A$12,
cliquez sur l’icône d’agrandissement du formulaire,
validez la création de votre tableau dynamique,
revenez sur la feuille "distributions",
optimisez la largeur de la colonne A en double-cliquant entre les lettres A et B,
découvrez le tableau généré : un nouveau tri à plat sur la colonne G, une distribution de la variable "instruments".
vous devriez obtenir ce fichier : base_jazz_1
- base_jazz_1
Le tri croisé des instruments par le genre
Cliquez dans une cellule quelconque de la table de données, par exemple en C9,
cliquez sur le menu Données, puis Table de pilote, Créer,
observez la sélection automatique de la table de données,
validez sur Sélection active,
observez les variables présentes sur la droite du tableau,
déplacez la variable lourde, “instrument”, dans le Champs de ligne pour obtenir les modalités de réponses (les instruments) en ligne,
déplacez la variable discriminante, “sexe”, dans le Champs de colonne pour obtenir les calculs répartis sur deux (ou trois) colonnes,
déplacez la variable de dénombrement (comptage) dans le Champs de données. Cette variable doit être choisie avec attention, car elle ne doit contenir aucune cellule vide sur toute la colonne. Nous choisirons ici la variable “instrument”,
double-cliquez sur la variable “instrument” placée dans le Champs de données pour changer la fonction Somme par la fonction Nombre située juste en-dessous, puis validez,
validez la Table de pilote,
observez le résultat : la variable lourde en ligne présente les instruments, la variable discriminante en colonne présente les genres,
cliquez sur la petite flèche de filtre de la variable "sexe" en colonne et décochez les groupes, la lettre "g",
analysez votre tableau.
Le tri par ordre décroissant sur une colonne
Faites un clic droit dans le tableau réalisé précédemment,
demandez Éditer la mise en page,
double-cliquez sur la variable en ligne “instrument” ; vous êtes dans les calculs,
cliquez sur les Options, une nouvelle fenêtre apparaît ; dans le haut, vous pouvez Trier par,
changez "instrument" par "Nombre instrument" par ordre Décroissant,
validez chaque boîte de dialogue,
observez le tri par ordre décroissant,
renommez la feuille en double-cliquant sur son nom en bas : "instruments X genre".
vous devriez obtenir ce fichier : base_jazz_2
- base_jazz_2
Les calculs sur la table de données
L’exemple de l’exercice nous permet de calculer :
la moyenne d’âge au décès (des personnes décédées !),
la moyenne d’âge des musiciens de la table,
la moyenne d’âge des guitaristes vivants,
la moyenne d’âge au décès des guitaristes décédés,
l’espérance de vie des batteurs, des guitaristes ou des saxophonistes, etc.
Pour cela, vous devez prévoir de nouveaux calculs sur la table de données.
Calcul conditionnel de l’âge des personnes décédées :
entrez le nom du nouveau champ dans la cellule H1 : "Ages DCD",
placez-vous en H2 et imaginez quelle fonction doit vous permettre de calculer l’âge de la personne si, et seulement si, elle est décédée,
utilisez la formule conditionnelle =SI(),
découvrez la formule dans sa syntaxe : =si(test ;valeur si vrai ;valeur si faux),
découvrez la formule aussi depuis l’assistant fonction en cliquant dans le menu Insertion, puis sur Fonction (ou Ctrl+F2),
choisissez la catégorie Logique, puis la Fonction SI,
notez sa syntaxe =SI(Test ;Valeur_si_vrai ;Valeur_si_faux) et sa définition : Spécifie un test logique à effectuer,
cliquez sur Suivant,
notez les définitions posées pour chaque formulaire proposé.
essayez de savoir si la personne du premier enregistrement, en ligne 2, est décédée ou non. Plusieurs façon de tester : la cellule F2 est vide, la cellule F2 est égale à zéro (ça marche), la cellule F2 est différente de vide ou de zéro. On choisira ici de demander si la cellule est vide : entrez F2="",
appliquez la valeur si vrai : si le test est bien égal à vide, dans ce cas, l’individu n’est pas décédé, il est donc vivant, en toute logique ; dans ce cas, on ne veut pas son âge pour l’instant, on veut simplement savoir qu’il est vivant : entrez "vivant",
appliquez la valeur si faux : si le test est faux, c’est que la cellule n’est pas vide, on suppose donc qu’il n’y a pas d’erreur de saisie et qu’on y trouve l’année de décès. Dans ce cas, la personne étant décédée, nous devons faire en sorte de calculer son âge au décès : entrez F2-E2,
validez,
incrémentez en faisant un double-clic !
Vous devriez voir cette boîte de dialogue.
- LibO-Calc-formule-SI
Calcul rapide de la moyenne :
faites un clic droit dans la barre d’état, en bas, sur le mot Somme=,
demandez Moyenne=,
cliquez sur la lettre H de la colonne, vous obtenez la moyenne des chiffres sur toute la colonne : 56,46. Ce résultat est juste à titre indicatif pour nous donner une idée. Les décimales ne sont pas intéressantes et il faudrait aussi enlever les enregistrements des groupes de jazz qui cassent la moyenne.
Calcul de l’âge de chaque personne vivante :
entrez le nom du champ dans la cellule I1 : "Âges vivants",
entrez directement la formule conditionnelle que vous connaissez : =SI(F2="" ;2013-E2 ;"dcd"),
incrémentez la formule sur tous les enregistrements de la table de données, sur toute la colonne.
Calcul d’un âge quelle que soit l’année d’ouverture du fichier :
remarquez que l’entrée de l’année 2013 dans la formule précédente n’est pas très dynamique et qu’il manquera un an au calcul lorsque nous passerons en 2014 !,
pensez, dans ce cas, à générer un calcul sur la date du jour,
découvrez la fonction =aujourdhui(), qui donne la date du jour, et la fonction =annee(), qui extrait d’une date l’année seulement. Ainsi, =annee(aujourdhui()) donne l’année du jour et la formule devient : =SI(F2="" ;ANNEE(AUJOURDHUI())-E2 ;"dcd").
vous devriez obtenir ce fichier : base_jazz_3
- base_jazz_3
Quelle est la moyenne d’âge au décès par instruments ? :
sélectionnez la table de données (ou pas) avec les raccourcis Ctrl+Origine et Ctrl+Maj+Fin,
cliquez, dans le menu, sur Données, Table de pilote, Créer,
validez la Sélection active,
glissez et déposez la variable "instrument" dans le Champs de ligne,
glissez et déposez la variable "Âges DCD" dans le Champs de données,
glissez et déposez la variable "instrument" dans le Champs de données,
notez que les deux derniers déplacements proposent leur Somme ; or, nous voulons la moyenne des âges des personnes décédées et le nombre d’individus pour chaque population d’instrumentistes (parce que nous devinons que certaines moyennes ne portent pas sur une assez grande population (c’est-à-dire sur assez d’instrumentistes),
double-cliquez sur Somme - Âges DCD et choisissez Moyenne,
double-cliquez sur Somme - instrument et choisissez Nombre,
validez votre table de pilote,
déplacez, dans le haut de votre tableau dynamique, la variable Données pour la faire apparaître en colonne ; faites un simple glisser-déposer avec votre souris de la cellule Données qui se trouve en B3,
optimisez la largeur de toutes vos colonnes en sélectionnant toutes les colonnes, puis en double-cliquant entre deux lettres de colonnes,
remarquez que vous obtenez deux colonnes "Total" que vous pouvez faire disparaître. Pour cela, faites un clic droit dans le tableau, demandez Éditer la mise en page, cliquez sur le Plus pour obtenir des options, décochez Total - colonnes, puis validez. Vous devrez alors déplacer les données en colonnes,
triez, pour une lecture ordonnée des espérances de vie, d’une manière décroissante, la colonne relative aux moyennes d’âge.
Triez la colonne dans un ordre décroissant :
faites un clic droit sur le tableau,
demandez Éditer la mise en page,
double-cliquez sur la variable “instrument” dans le Champs de ligne,
cliquez sur Options...,
changez "instrument" par "Moyenne - Âges DCD" par ordre Décroissant,
validez,
validez,
validez !,
déplacez les données en colonnes : la première concerne les moyennes d’âge au décès, la seconde le nombre d’individus dans la population étudiée,
analysez votre population. Des batteurs ou des guitaristes, lesquels ont l’espérance de vie la plus longue (si nous devions nous en tenir à ces quelques données) ?
Filtrer les groupes des individus personnes physiques :
faites un clic droit sur le tableau dynamique et demandez Filtre,
indiquez Nom de champ sexe,
indiquez Condition <> (différent de),
indiquez Valeur g (pour les groupes),
validez.
vous devriez obtenir ce fichier : base_jazz_4
- base_jazz_4
Quelle est la moyenne d’âge des instrumentistes vivants ? :
cliquez n’importe où dans la table de données,
cliquez sur Données,
cliquez sur Table de pilote, Créer, Sélection active, OK,
glissez et déposez les variables comme précédemment ; "instrument" en Champs de ligne, "Âges vivants" et "instrument" en Champs de données,
changez les sommes des champs de données par la moyenne et le dénombrement des variables “Âges” et “instrument”,
validez,
déplacez les données en colonnes,
observez,
supprimez les colonnes "Total" et triez par "Moyenne d’âge",
analysez votre population : des batteurs ou des guitaristes, lesquels ont la moyenne d’âge la plus appréciable (si nous devions nous en tenir à ces quelques données) ?
vous devriez obtenir ce fichier : base_jazz_5
- base_jazz_5
Bravo !