GLIN102 - TD/TP séance 6 : Tableur avancé.

Durée : 1h30

Objectifs :

Découverte de nouvelles fonctions du tableur :
- Nommage d'une plage de cellules.
- Traitement conditionnel.
- Utilisation de fonctions de type base de données.
- Traitement de chaînes de caractères.

Au menu

Consignes
Quelques fonctionnalités supplémentaires
Récupération automatique d'informations dans un tableau
Traitement de chaînes de caractères
À retenir
Facultatif : le format CSV (séparateur virgule)

A. Consignes (5 min) ↑

Lisez attentivement cet énoncé de TP en suivant les instructions. En cas d'interrogation, faites appel à l'enseignant qui est là pour vous répondre, que ce soit pour en savoir plus sur un des points abordés pendant le TP, ou pour savoir comment effectuer une des tâches demandées (numérotées pour pouvoir y faire référence simplement).

Entre parenthèses, à côté des titres de sous-sections, est indiqué le temps que vous avez dû passer à effectuer les étapes précédentes.

Certains liens conduisent vers un article Wikipédia. Cliquez dessus si vous souhaitez en savoir plus. Enfin, retenez l'emplacement de ces pages! Elles vous serviront d'aide-mémoire pour votre utilisation du tableur.

B. Quelques fonctionnalités supplémentaires (20 min) ↑

^B1Ouvrez le tableur de la suite Open Office (généralement, Spreadsheet ou Calc dans le sous-menu Bureautique de votre menu principal).

La fonction ALEA() permet de renvoyer aléatoirement un nombre décimal compris entre 0 et 1. ^B2Essayez cette fonction dans la case A1 de votre feuille de calcul (sans oublier qu'une formule commence toujours par un '=').
Dans la case B1, on veut noter pile si le nombre tiré dans la case A1 est inférieur à 0,5 et face sinon. Pour cela, on va utiliser la fonction SI(test; valeur si vrai; valeur si faux), où test est le test à effectuer et les deux autres paramètres sont ce que doit afficher la fonction en cas de succès ou d'échec du test. Par exemple, pour obtenir ce que l'on souhaite, ^B3tapez =SI(A1<0,5;"pile";"face") dans la case B1 (notez bien que les chaînes de caractères qui doivent être affichées sont spécifiées entre guillemets droits" : double quotes en anglais).
^B4Faites afficher dans la case C1 la valeur 1 si le contenu de la case A1 est strictement inférieur à 0,5 et 0 sinon (ici, les résultats à afficher étant numériques, il ne faut plus mettre de guillemets droits dans la fonction SI).
Le résultat obtenu simule le lancer d'une pièce de monnaie. ^B5Appuyez sur F9 pour effectuer un autre lancer. La touche F9 refait en fait tous les calculs présents dans la feuille de calcul, c'est ce qui se passe aussi lorsque la feuille est modifiée. On va maintenant effectuer simultanément 10 lancers différents. Pour cela, à l'aide de la poignée d'incrémentation, ^B6étendez le calcul de la ligne 1 aux lignes 1 à 10.

Pour finir, on va compter le nombre de "pile" obtenu sur les 10 lancers. Il suffit pour cela de sommer le contenu de la colonne C. On va voir quatre façons de procéder :

^B7Placez vous dans la cellule C11 et cliquez sur le bouton Somme (noté Σ). Il propose par défaut la somme des cellules de la colonne C placées au dessus de la cellule C11, ce qui correspond à ce que l'on souhaite.
^B8Placez vous dans la cellule D11 et rentrez la formule =SOMME(C1:C10) signifiant que l'on souhaite sommer le contenu des cellules de C1 à C10 (notez que cette notation était celle utilisée dans la cellule C11).
On va nommer la plage de cellules avant d'en calculer la somme. ^B9A l'aide de la souris, sélectionnez le contenu des cellules C1 à C10, puis choisissez Définir dans le sous-menu Noms du menu Insertion (ou raccourci clavier : Ctrl-F3). Tapez NbrePiles dans la boîte dialogue qui est apparue. Il ne reste plus qu'à sommer le contenu des cellules ainsi nommées. ^B10 Placez vous dans la cellule E11 et entrez =SOMME(NbrePiles) . Notez bien l'utilisation du nom de la plage des cellules concernées dans la formule.
Pour finir, on va utiliser la fonction NB.SI qui nous permettra de calculer directement le nombre de fois où le terme pile apparaît dans la colonne B. Cette fonction prend en paramètres deux arguments, le premier étant la plage de cellules à parcourir, le second étant le critère devant remplir une cellule pour être prise en compte. Le résultat retourné est alors le nombre de cellules vérifiant le critère énoncé et appartenant à la plage de cellules concernées.
Pour exemple, ^B11placez vous dans la cellule F11 et entrez =NB.SI(B1:B10;"pile"). Le critère de recherche ici est relativement simple, mais il peut aussi être plus élaboré.

Sauvegardez votre travail.

C. Récupération automatique d'informations dans un tableau (40 min) ↑

Dans ce chapitre, on se sert du relevé de notes établi lors de la précédente séance sur le tableur. Vous avez dû sauvegarder le fichier correspondant dans votre espace personnel.
^C2Ouvrez ce fichier avec Open Office. Si, pour diverses raisons, vous ne retrouvez pas le fichier en question, sauvegardez sur votre compte celui qui est accessible ici : ex2.ods.

L'objectif de l'exercice est d'éditer des bulletins de notes à partir du relevé de notes établi précédemment.

a) Mise en forme du bulletin

Nous allons créer le bulletin dans une autre feuille du classeur.
^C3Cliquez sur l'onglet Feuille 2, en bas à gauche de votre document.
^C4Reproduisez le modèle suivant sur votre feuille de calcul (respectez bien la disposition de chaque cellule remplie).

^C5Mettez en forme le bulletin (caractères, cellules, bordures...), voir ci-dessous pour exemple.

Avant d'extraire les notes du relevé de la feuille 1, on va placer la date du jour dans le bulletin.
^C6Dans la cellule G3, insérez la fonction AUJOURDHUI(). Activez le menu contextuel pour formater la date sous une forme voulue.

b) Extraction des notes à partir du relevé

Pour récupérer des informations présentes dans le relevé de notes de la feuille 1, nous allons utiliser quelques fonctions proposées par le logiciel.

Tout d'abord, le bulletin contient, à titre indicatif, les coefficients des différentes matières. On va recopier ceux-ci depuis la feuille 1. ^C7Placez vous à la cellule E9 et entrez la formule =Feuille1.A23. Notez le préfixe utilisé dans la désignation de la cellule, celui-ci est obligatoire pour ne pas confondre les cellules A23 des feuilles de calcul 1 et 2.
^C8Remplissez de la même manière les coefficients des autres matières.
Nous allons maintenant nommer le relevé de notes de la feuille 1 (ce n'est pas nécessaire, mais cela sera plus pratique pour la suite). ^C9Nommez Notes le tableau constitué des cellules A3 à H14 (voir partie B si besoin).

Pour en savoir plus : Une base de données est un ensemble structuré permettant le stockage de grandes quantités d'informations. La structure utilisée (hiérarchique, relationnelle, orientée objet...) a pour but d'optimiser les requêtes effectuées sur la base : ajout, suppression d'entrées, mise à jour, recherche de données.

Ensuite, nous allons récupérer, dans ce tableau, les informations relatives à une personne de nom connu. Le logiciel considère qu'un tableau de données peut être "vu" comme un embryon de base de données. Le tableau que nous avons dénommé Notes sera donc interprété comme une base de données dont les champs sont Nom, Prénom, Mathématiques...
La fonction de base de données BDLIRE(nom de la base; "nom du champ"; critère de recherche) permettra de récupérer dans le tableau Notes les informations nécessaires. Cette fonction possède 3 paramètres : nom de la base, ici ce sera Notes le nom du tableau, nom du champ, par exemple, pour avoir la note de mathématiques, on indiquera "Mathématiques", ce critère est toujours entre guillemets, et enfin le paramètre critère de recherche, ici ce sera le nom de l'élève. Pour spécifier ce dernier paramètre, il faut obligatoirement utiliser des cellules de la page de calcul (cela permet d'avoir des critères de recherche plus ou moins compliqués) : retourner sur la feuille de calcul 2 du bulletin scolaire et^C10tapez Nom dans la cellule I8 et André dans la cellule I9.
La recherche d'une information se fait finalement de la façon suivante : ^C11Dans la cellule D9 (note de mathématiques), tapez =BDLIRE(Notes;"Mathématiques";I8:I9).
La case D9 contient maintenant la note de mathématiques de l'élève André. Si on change le critère de recherche (le nom de l'élève), la note correspondante changera. ^C12Remplacez le contenu de la cellule I9 par le nom d'un autre élève, par exemple Benoit.
^C13Remplissez maintenant, de la même façon, les cases C5, C6, D10, D11, D12 et C15 (en C15 c'est la valeur de la moyenne pondérée qu'il faut renseigner).

Pour finir, nous allons automatiser l'attribution des mentions. On peut le faire à l'aide de la fonction SI vue dans la partie B.^C14 Essayer de trouver la solution.
Nous allons voir maintenant un moyen plus direct.
Pour cela, il faut entrer au préalable dans une plage de cellules de votre choix (mais toujours dans la feuille 2) les informations suivantes :

^C15Recopiez cet ensemble de cellules et nommez-le Mentions.
Il reste à utiliser la fonction RECHERCHEV(critère; tableau; indice), cette fonction permet d'aller rechercher dans un tableau (ici la plage Mentions) la ligne correspondant au critère recherché (ici, la moyenne de l'élève, contenue case C15) et retourne la valeur située dans la ligne trouvée à la colonne d'indice donné (ici 2, les mentions se trouvant dans la deuxième colonne de la plage Mentions).
^C16Remplissez la cellule C16 comme indiqué.
^C17Faites quelques tests en changeant le nom de l'élève dans la cellule I9.

Sauvegardez votre travail.

D. Traitements de chaînes de caractères (25 min) ↑

Des informations sur les utilisateurs du réseau informatique de l'Université ont été récupérées et stockées dans le fichier suivant : ex3.ods. Ces informations contiennent une chaîne de caractères : NOM_Prénom, appelé code utilisateur, et 2 ou 3 lettres indiquant le statut de chaque utilisateur : Et pour les étudiants, Ens pour les enseignants et Adm pour les administrateurs du réseau. Le but de l'exercice est de reconstruire automatiquement, à partir de ces informations, les chaînes de caractères correspondant aux nom, prénom, login et adresse mail de chaque utilisateur. On va utiliser pour cela les outils de traitement de chaînes de caractères offerts par le tableur.

^D1Sauvegardez sur votre compte le fichier mentionné ci-dessus et ouvrez-le avec le tableur d'Open Office.

^D2Cliquez sur le bouton Assistant fonctions (voir tp 4 pour mémoire), choisissez Catégorie → Texte et observez les différentes fonctions. Une fois que vous les avez toutes parcourues, cliquez sur Annuler
Toutes ces fonctions permettent d'effectuer des opérations sur les chaînes de caractères, nous allons en utiliser quelques-unes :

NBCAR(A1) : donne le nombre de caractères de la chaîne contenue dans la cellule A1.
CHERCHE("a";A1;1) : donne la position du caractère a dans la chaîne de caractères contenue dans la cellule A1, en partant de la position 1.
STXT(A1;1,10) : extrait la sous-chaîne correspondant aux caractères 1 à 10 de la chaîne de caractères contenue dans la cellule A1.
CONCATENER("bla";"blu";"bli") : produit la concaténation des chaînes passées en paramètre.
MINUSCULE("Bonjour") : retourne la chaîne de caractères passée en paramètre, écrite entièrement en minuscule.

Par exemple, la formule =STXT(A4;1;CHERCHE("_";A4;1)-1) va extraire de la la chaîne de caractères stockée dans cellule A4, la chaîne comprise entre le premier caractère et le premier "_" rencontré dans cette chaîne, c'est-à-dire pour notre problème, exactement le nom de l'utilisateur.
^D3Entrez cette formule dans la case C4 et étendez-la à toute la colonne C.
^D4Entrez dans la case D4 la formule permettant d'extraire le prénom depuis le code utilisateur. Étendez cette formule à toute la colonne D.
La colonne E doit contenir le login de chaque utilisateur, ce login devant être écrit en miniscule et formé de la première lettre du prénom puis de la totalité du nom.
^D5Entrez dans la case E4 la formule permettant de former le login de l'utilisateur dont le code est dans la case A4. Étendez cette formule à toute la colonne E.
Pour finir, on va former les adresses mail des utilisateurs. Une adresse mail est constituée du login, suivi du symbole @, puis de la chaîne univ-montp2.fr si l'utilisateur est un personnel de l'université ou de la chaîne etud.univ-montp2.fr si l'utilisateur est un étudiant.
^D6Entrez dans la case F4 la formule permettant de former l'adresse mail de l'utilisateur dont le code est dans la case A4 (pensez à la fonction SI...). Étendez cette formule à toute la colonne E.

Sauvegardez votre travail.

Comme on vient de le voir, un tableur permet d'effectuer des traitements simples sur les chaînes de caractères. D'autres langages ou logiciel permet de faire des traitements plus approfondis comme le langage de script, le langage awk, ou encore le langage Perl...

E. À retenir (0 min) ↑

Voici la liste des diverses fonctions utilisées dans ce TP :

ALEA
SI
SOMME
NB.SI
AUJOURD'HUI
BDLIRE
NBCAR
CHERCHE
STXT
CONCATENER
MINUSCULE

F. Facultatif : le format CSV (séparateur virgule) (0 min) ↑

Nous avons vu que le tableur permettait d'effectuer des traitements divers sur de nombreux types de données différentes. Reste à savoir comment importer certains types de données qu'on ne peut copier/coller facilement vers le tableur.

Par exemple, si vous voulez récupérer la liste des notes du CC1 pour en calculer la moyenne, une possibilité est de sélectionner l'intégralité des notes et de la coller dans la première colonne du tableur. Il faut alors utiliser une formule pour séparer le numéro d'étudiant de la note, en détectant l'espace. Selon le type de données, ce genre de formules peut devenir assez compliqué.

Une solution alternative est d'utiliser le format CSV, qui est un format de fichier texte où chaque ligne du fichier tableur est une ligne du fichier CSV, et, pour chaque ligne, chaque colonne est séparée par un caractère spécial, usuellement une virgule. ^F1Copiez l'intégralité des notes depuis la page du contrôle continu. ^F2Ouvrez un éditeur de textes (par exemple Kate ou Kwrite) et collez-y l'ensemble de ces notes.

La virgule est utilisée à l'intérieur de chaque note, il n'est donc pas possible de l'utiliser comme caractère séparateur. En revanche, l'espace qui sépare actuellement le numéro d'étudiant de sa note convient parfaitement.

Il serait bon de se débarasser du "/20" pour pouvoir effectuer des calculs, ^F3faites-le par un simple rechercher/remplacer (menu Édition, Remplacer) de "/20" par "" (rien du tout).

^F4Enregistrez maintenant le fichier en lui donnant l'extension .csv. ^F5Ouvrez-le avec Open Office (clic droit, Ouvrir avec, Autre..., Bureautique, Spreadsheet) : dans la rubrique "options de séparation" de la boîte de dialogue qui s'ouvre, cochez "séparé", et "espace" pour indiquer que le caractère séparateur est un espace. Les données sont maintenant traitables dans le tableur. Autre intérêt, le résultat est aussi enregistrable au format CSV, en choisissant le caractère séparateur que vous désirez. En passant par un éditeur de texte et sa fonction rechercher/remplacer, vous pourrez donc faire les opérations voulues pour le retransformer en liste au format HTML des pages web par exemple. Les détails sur ce format seront abordés à la séance 8.