Td corrigé Cours Excel - Site des étududiants en DESS IGSI Toulouse 2004 ... pdf

Cours Excel - Site des étududiants en DESS IGSI Toulouse 2004 ...

Jean-Marc Thévenin. TD FC1. Maîtriser les feuilles de calcul ... 2 Nommer les cellules correspondant aux notes de TD et d'examen ..... Pour cela, il suffit de sélectionner la cellule contenant la formule à corriger et d'appeler la commande  ...




part of the document



41815 \h 10
1 Saisir la formule calculant la moyenne dans la cellule $C$9.  RENVOIPAGE _Toc464441816 \h 10
2 Recopier cette formule dans les cellules à côté.  RENVOIPAGE _Toc464441817 \h 11
V Utilisation de cellules nommées dans une formule  RENVOIPAGE _Toc464441818 \h 11
1 Création d'une feuille de paramètres :  RENVOIPAGE _Toc464441819 \h 11
2 Nommer les cellules correspondant aux notes de TD et d'examen  RENVOIPAGE _Toc464441820 \h 12
3 Saisir les formules de calcul de notes  RENVOIPAGE _Toc464441821 \h 12
VI Utilisation de la fonction SI  RENVOIPAGE _Toc464441822 \h 12
1 Compléter la formule à saisir en $F$3.  RENVOIPAGE _Toc464441823 \h 13
2 Complétez la colonne mention du tableau de la feuille 'PV Exam'  RENVOIPAGE _Toc464441824 \h 13
VII Utilisation des fonctions de recherche  RENVOIPAGE _Toc464441825 \h 13
1 Créer une liste de valeur contenant tous les étudiants  RENVOIPAGE _Toc464441826 \h 14
2 Actualiser la feuille 'PV Exam' pour qu'elle cherche les noms des étudiants à partis de leur n°  RENVOIPAGE _Toc464441827 \h 14
Fin de la partie initiation  RENVOIPAGE _Toc464441828 \h 14
Exercices  RENVOIPAGE _Toc464441829 \h 15




I Introduction : structure d'un document Excel
1 Notion de classeur
Un document Excel 97 se présente sous la forme d'un classeur comportant plusieurs feuillets de différent types (feuille de calcul, feuille de graphique). Chaque feuillet comporte un nom et est accessible directement via un onglet situé au bas de la fenêtre. L’exemple de la Figure 1 montre un classeur comportant au moins 4 feuillets respectivement nommés "PV Exam", "ListeEtu", "Coef" et "Tableau". Le nombre maximum de feuillets d'un classeur va au-delà de 200 (il n'est limité que par la taille mémoire de l'ordinateur sur lequel on travaille). Les flèches à gauche des onglets permettent respectivement de se positionner sur le premier onglet, faire défiler les onglets d'un cran vers la gauche, faire défiler les onglets d'un cran vers la droite, se positionner sur le dernier.




Figure  SEQ Figure \* ARABE 1
2 Enregistrement d'un classeur
Un classeur Excel est stocké sur disque dans un fichier et possède un nom. C'est lors de la première opération d'enregistrement du classeur que l'on désignera l'emplacement exact ou l'on souhaite enregistrer le fichier sur disque (voir Figure 2). Il est ainsi possible d'enregistrer le fichier sur disquette ("A:"), sur le disque local de votre poste de travail ("C:"), ou sur un disque accessible depuis un réseau (ex : "K:"). A l'intérieur d'un disque, les fichiers sont généralement classés dans des dossiers (ex : dossier "usr" de l'unité C: dans l'exemple de la figure 1). Dans l'environnement d'UT1, nous vous recommandons d'enregistrer vos fichiers dans la partition allouée à votre compte étudiant ("K:") sur le disque du serveur SIP1 qui est accessible depuis le réseau pédagogique d'UT1. Utilisez les disquettes uniquement pour faire des sauvegardes. N'oubliez pas que d'une séance à l'autre vous ne serez pas forcément sur le même poste de travail et que n'importe qui a accès aux fichiers enregistrés sur un disque local. Evitez donc d'enregistrer vos fichiers sur "C:".

Figure 2 : boite de dialogue enregistrer ou enregistrer sous
C'est aussi lors de la première opération d'enregistrement du classeur que l'on précisera le nom du fichier qui le contient (voir Figure 2). Dans l'environnement Windows, le nom d'un classeur est toujours suivi du suffixe '.xls', qui signifie que le fichier correspondant contient un classeur Excel.
Les commandes permettant de créer un classeur, enregistrer un classeur et dupliquer un classeur à un nouvel endroit sont respectivement : Fichier/Nouveau, Fichier/Enregistrer, Fichier/Enregister sous.
3 Paramétrage d'Excel
Lorsque l'on crée un nouveau classeur, celui-ci possède un nombre prédéfini de feuillets appelés 'Feuil1, 'Feuil2', 'Feuil3'( Le nombre de feuillets générés à la création d'un nouveau classeur est paramétrable et peut être modifié via la commande Outils/Options/Général permettant d'accéder à la case "Nombre de feuilles par nouveau classeur" (voir Figure 3). Notez que cette commande n’est accessible que si un classeur est ouvert. Comme vous pouvez le constater sur la Figure 3, de nombreux éléments concernant le comportement d'Excel peuvent être paramétrés via la boîte de dialogue "Options". Vous êtes invités à explorer ces différentes options par vous-même, lorsque vous commencerez à maîtriser Excel..

Figure 3 : Boite de dialogue "Options"/ Onglet "Général"
4 Notions de feuille de calcul et de cellule
Une feuille de calcul est constituée d’un tableau de cellules que l’on peut identifier à l'aide d'un quadrillage ligne colonne (voir Figure 1). Suivant l'option choisie au moyen de la commande Outils/Options/Général (voir case à cocher "Style de référence L1C1" Figure 3), les colonnes sont repérées par des lettres (A, B,..., Z, AA, AB,...) ou des numéros (1, 2,..., 256). Les lignes sont toujours repérées par des numéros (1, 2,..., 16 384).
A tout instant, il existe une cellule active dans une feuille active, sur laquelle on peut travailler. La feuille active est affichée au premier plan et la cellule active est encadrée. Pour activer une feuille il suffit de cliquer sur son onglet. Pour activer une cellule, il suffit de cliquer dessus.
Toute cellule est identifiée par une ligne et une colonne. L'identifiant d'une cellule est communément appelé adresse. Suivant que l'on a coché la case "Style de référence L1C1" ou non, l'adresse d'une cellule est représentée respectivement par l'une des deux notations suivantes :
L9C3 qui signifie Ligne 9 Colonne 3 ou
$C$9 qui signifie colonne C ligne 9.
Remarque 1 : dans la deuxième notation, les $ indiquent qu'il s'agit d'une adresse absolue de cellule. Cette notion sera détaillée plus tard.
Remarque 2 : dans l'environnement Excel on privilégie généralement la deuxième notation et la case Style de référence L1C1 n'est pas cochée.
Remarque 3 : l'adresse de la cellule active est affiché dans une case à gauche, au-dessus des noms de colonnes. Dans ce cas précis les $ ne sont pas affichés bien qu'il s'agisse d'une adresse absolue (voire figure 1). Il est possible d'activer directement une cellule en écrivant son adresse dans la case d'adresse puis en appuyant sur la touche entrée. Cette solution évite une utilisation fastidieuse des ascenseurs pour atteindre une cellule éloignée (ex : C300).
Il est possible de désigner des cellules d'une feuille à l'autre, en préfixant l'adresse de la cellule par le nom de la feuille comme ceci : [TD1.XLS]'PV Exam'!$C$9. Remarque : dans cet exemple, le nom de la feuille est entouré de quotes (') car il comporte un espace. Lorsque ce nom ne comporte pas d’espace, les quotes ne sont pas obligatoires. Le nom mis entre crochets correspond au nom du classeur contenant la feuille de calcul. Il est ainsi possible de désigner, dans une feuille de calcul, une cellule provenant d’une autre feuille de calcul et éventuellement d'un autre classeur.
5 Contenu d'une cellule
Une cellule peut contenir du texte, un nombre ou une formule correspondant à l'expression d'un calcul. Dans le dernier cas, la cellule affiche généralement le résultat de ce calcul. Il est possible d'afficher l'expression des formules contenues dans les cellules afin d'aider à déchiffrer la structure d'une feuille de calcul, en exploitant la commande Outils/Options/Affichage et en cochant la case Formule. Les expressions correspondant à des formules commencent toujours par le signe "=". Excel détecte automatiquement si l'expression saisie dans une cellule correspond à un texte, un nombre ou une formule. Si cette expression ne contient que des chiffres, il s'agit d'un nombre ; si elle commence par le signe "=", il s'agit d'une formule ; dans les autres cas, il s'agit d'un texte.
Pour modifier le contenu d'une cellule il faut commencer par activer la cellule. Une fois la cellule activée, son contenu peut être édité (mis à jours) dans la barre de formule (voir Figure 4 et Figure 1). Ce contenu peut aussi être édité directement dans la cellule si l’on double-clique dans celle-ci. On remarquera Figure 4 le curseur d'insertion représenté par une barre verticale placée entre les deux parenthèses. Ce curseur peut être déplacé à l'aide de la souris ou des flèches du clavier. Si l'on opte pour l'option consistant à éditer directement dans la cellule, le curseur d'insertion apparaît directement dans la cellule. On remarquera dans la barre de formule la présence de trois boutons permettant respectivement : d'annuler les modifications que l'on vient de faire (peut être obtenu par la touche Echap), de valider ces modifications (peut être obtenu en appuyant sur la touche entrée ou sur une touche de tabulation), de saisir le symbole = pour indiquer que l'on saisit une formule ; ce dernier bouton a aussi pour effet d'afficher une boîte liste contenant les noms des fonctions couramment utilisées, à l'emplacement où figure habituellement la case adresse de la cellule active.

Figure 4: Barre de formule
Dans l'exemple de la Figure 1, la cellule active affiche la valeur 8.66666667 correspondant au résultat d'une expression calculant la moyenne des nombres contenus dans le groupe de cellules allant de la cellule $C$3 à la cellule $C$8. La barre de formule affiche l'expression permettant d'effectuer cette moyenne. Il s'agit de l'expression suivante : =Moyenne(C3:C8). Le signe = correspondant au premier caractère de cette expression indique que l'expression est une formule à évaluer.
6 Notion de plage
Un groupe de cellules contiguës correspond à une plage que l'on désigne par les deux cellules en haut à gauche et en bas à droite du carré délimitant les cellules du groupe. On remarquera que dans la formule de l'exemple de la figure 1, la plage de cellule pour laquelle on effectue la moyenne est désignée par une adresse relative (C3:C8) et non par une adresse absolue ($C$3:$C$8). On a omis les $. Le fait d'utiliser une adresse relative permet de translater cette formule d'une cellule à l'autre par le biais des commandes copier/coller ou recopier vers la droite. En effet, dans les cellules $D$9 et $E$9 on a aussi besoin d'effectuer la moyenne d'une plage de cellule, mais cette plage de cellule porte sur une colonne différente, celle située au-dessus de la formule. Le fonctionnement des adresses relatives sera détaillé ultérieurement.
7 Application : création d'un document comportant un tableau simple
a) Paramétrez Excel en fonction de vos options personnelles
Paramétrez Excel pour que les classeurs nouvellement créés contiennent un seul feuillet. Paramétrez alternativement le repérage des colonnes avec des numéros (standard Lotus123) ou des lettres (standard Excel). Dans la version finale paramétrez le repérage des colonnes avec des lettres. Observez les autres options que vous pouvez paramétrer.
Les commandes nécessaires à la réalisation de ces actions ont été vues dans la section précédente.
b) Créez un nouveau classeur
c) Renommez le 1er feuillet en "PV Exam"
-> Double cliquer sur l'onglet
ou Format/Feuille/Renommer
ou Menu contextuel Renommer
-> Entrer le nouveau nom dans la boite de dialogue ainsi ouverte.
d) Enregistrez votre document
Enregistrez votre document dans le dossier (répertoire) "Excel/TD" sur votre compte personnel sous le nom "TD1". Cette action suppose que vous alliez créer le dossier "Excel" sur votre compte personnel, puis le dossier "TD" sous le dossier Excel. Ces deux commandes peuvent être exécutées via l'explorateur ou directement via la boîte de dialogue "enregistrer" en exploitant le bouton créer un dossier.
e) Créez un tableau
Créez un tableau comportant 4 colonnes intitulées respectivement "N°Etu", "nom", "note td" et "note exam" et remplissez ce tableau en vous inspirant des valeurs contenues dans la plage $A$2:$D$8 du feuillet PV Exam représenté Figure 1.
Remarque : le calcul des moyennes, des notes finales et des mentions sera effectué ultérieurement, de façon automatique, à l'aide de formules.
Pensez à enregistrer tout de suite votre travail avant qu'il ne soit perdu !
II Adresses relatives / Adresses absolues
Les adresses de cellules sont fréquemment utilisées dans les formules pour faire référence aux valeurs contenues dans les cellules ainsi désignées. Par exemple, la formule =MOYENNE(C3:C8) signifie que l'on veut faire la moyenne des valeurs contenues dans la plage désignée par l'adresse C3:C8.
1 Définitions
Une adresse absolue permet de désigner une cellule précise indépendamment de la cellule dans laquelle on est placé (ex : Place du Capitole, 31 000 Toulouse).
Une adresse relative permet désigner une cellule à partir de la cellule dans laquelle on se trouve, en se déplaçant de X lignes et de Y colonnes (ex : 1ère rue à gauche puis première rue à droite en sortant du TD).
Par exemple, l'adresse absolue $A$1 permet d'atteindre la 1ère cellule en haut à gauche de la feuille, quel que soit l'endroit où l'on se trouve. L'adresse relative C2 placée dans cette cellule ($A$1), permet de désigner la cellule située deux colonnes à droite et une ligne plus bas (c'est à dire $C$2). Si l'on copie cette adresse relative dans la cellule $C$2, elle sera automatiquement translatée deux cellules à droite et une ligne plus bas (l'adresse relative C2 devient E3 si on la copie de la cellule $A$1 vers la cellule $C$2).
2 Notations
Lorsque l'on utilise des notations de style "A1", un $ placé devant un nom de colonne ou un numéro de ligne signifie qu'il s'agit d'un nom ou d'un numéro absolu. Ce numéro ou ce nom n'est pas converti si l'on recopie l'adresse à un autre endroit. En l'absence de $, les noms de colonne ou numéros de ligne sont considérés comme relatifs et sont convertis lorsque l'on les recopie dans d'autres cellules. On peut ainsi définir des adresses absolues, des adresses relatives ou des adresses mixtes :
A2 -> Adresse relative
$A$2 -> Adresse absolue
$A2 -> Adresse mixte
A$2 -> Adresses mixtes
Quelles sont les cellules désignées par ces 4 adresses si elles sont placées dans la cellule A1. Que deviennent ces adresses si on les recopie en C1 ?
Lorsque l'on utilise des notations de style "L1C1", un numéro écrit sans parenthèses est un numéro absolu, tandis qu'un numéro écrit entre parenthèses est un numéro relatif. Par exemple l'adresse absolue $A$1 s'écrit L1C1 tandis que l'adresse relative C2 placée dans la cellule $A$1s'écrit L(1)C(2). Transcrire les adresses relatives, absolues et mixtes données ci-dessus dans le style de notation "L1C1".
3 Exercice de l'échiquier
a) Créez le nom 'Cellule_A1' associé à l'adresse absolue $A$1.
Utiliser la commande Insertion/Nom/Définir et remplire la boîte de dialogue comme indiqué Figure 5. La cellule active n'a aucune importance lorsque l'on définit un nom associé à une adresse absolue.
Remarque : pour remplir la case "Réfère à", il suffit de cliquer dans la cellule A1 de la feuille 'PV Exam'.

Figure 5 : boîte de dialogue "définir un nom"
b) Créez le nom 'Cheval' permettant de se déplacer de 2 colonnes à droite et une ligne plus bas.
Sélectionner la cellule $A$1, puis exécutez la commande Insertion/Nom/Définir. Ecrire l'adresse relative C2 dans la case "Réfère à". Si vous le faite à la souris, pensez à retirer les $. Vous pouvez utiliser la touche F4 à cet effet (cette touche permet de convertir une adresse absolue en adresse relative ou en adresse mixe et vice versa)
Remarque : la cellule active est importante lorsque l'on définit un nom associé à une adresse relative, puis que l'on enregistre un déplacement à partir de cette cellule. Si vous changez de cellule active après avoir défini le nom cheval, puis activez à nouveau la commande Insertion/Nom/Définir, vous pourrez constater que l'adresse contenue dans la case "Réfère à" pour le nom cheval a été translatée.
c) Déplacez-vous à l'aide de ces noms comme sur un échiquier.
Cliquer sur la flèche à gauche de la barre de formule (voir ci-dessous), puis sélectionner un nom de la liste affichée.
INCORPORER PBrush \s \* fusionformat
Conclusion :
Dans une feuille de calcul, on utilise souvent des formules similaires d'une ligne à l'autre ou d'une colonne à l'autre (ex : dans 'PV Exam', les moyennes sont toutes calculées en exploitant les valeurs des 6 cellules au-dessus). Ainsi, on utilise généralement des adresses relatives dans les formules pour permettre de copier les formules d'une cellule à l'autre, afin d'en accélérer la saisie. Les adresses absolues sont réservées aux cas rares où l'on veut désigner une cellule ou une plage qui ne change pas lors que l'on recopie la formule ailleurs.
IV Saisir des formules
Une formule commence toujours par un signe = suivi d'une expression. Cette expression peut correspondre à un simple calcul sur des valeurs contenues dans des cellules (ex : =$A$1 + C2 ou =Cellule_A1 + Cheval). Elle peut aussi faire appel à une fonction préprogrammée d'Excel ou à des fonctions personnelles créées à l'aide de macros commandes. Une fonction est une commande que l'on active par son nom, qui prend un ou plusieurs arguments (ou paramètres) en entrée et retourne une valeur (ex : Moyenne (8 ; 4) -> 6). La valeur d'un argument peut elle-même être obtenue par un appel emboîté de fonction (ex : Moyenne ( Somme (3 ; 5) ; 4) -> 6).
On obtient la liste des fonctions préprogrammées via l'utilitaire Insertion/Fonction qui aide à la saisie des noms de fonction et des arguments.
1 Saisir la formule calculant la moyenne dans la cellule $C$9.
Sélectionnez la cellule $C$9 et activez la commande Insertion/Fonction (combinaison au clavier "Alt+f" puis "i") ou cliquer sur le bouton INCORPORER PBrush \s \* fusionformat. Cette commande ouvre une première boîte de dialogue permettant de choisir une fonction parmi un groupe de fonction. Choisissez la fonction Moyenne du groupe Statistiques. En cliquant sur le bouton OK vous passez à une boite de dialogue similaire à celle représentée Figure 6, facilitant la saisie des arguments de la fonction choisie. Cette boîte affiche une case par argument, précédée du nom de l'argument et décrit l'utilisation de l'argument actif. Un bouton à droite de chaque argument permet d'aller sélectionner une plage de cellules à la souris dans le cas ou l'argument serait un ensemble de valeurs contenu dans une plage. Pensez à vous faire aider en cliquant le bouton "?" de cette boîte de dialogue. Le bouton "OK" a pour effet de fermer cette boîte de dialogue.

Figure 6 : boîte de dialogue de saisie des arguments d'une fonction.
2 Recopier cette formule dans les cellules à côté.
Cliquer sur le petit carré en bas à droite de la cellule active et faire glisser vers la droite
ou Sélectionner les 3 cellules devant contenir la moyenne puis activer la commande Edition/Recopier/A Droite.
Observez les formules générées.
V Utilisation de cellules nommées dans une formule
Le calcul de la note finale utilise des coefficients permettant de pondérer la note de TD par rapport à celle de l'examen : note finale = Coef td * note td + Coef exam * note exam
Ainsi, la formule pour calculer la note finale figurant dans la cellule E3 pourrait être :
= 0.4 * C3 + 0.6 * D3
Une telle formule comporte deux problèmes :
1) elle est difficile à interpréter (à quoi correspond un calcul faisant intervenir des adresses de formules ?)
2) si les coefficients sont amenés à changer, toutes les cellules faisant intervenir ces coefficients sont à changer elles aussi, ce qui rend difficile la maintenance des feuilles de calcul dans le temps.
Le premier problème peut être résolu en nommant les cellules intervenant dans les calculs. Le deuxième problème peut être évité en centralisant à un endroit précis les valeurs susceptibles de changer. C'est l'objet de l'exercice suivant.
1 Création d'une feuille de paramètres :
L'objectif de cette la feuille de paramètres est de centraliser les valeurs susceptibles de changer.
a) Créez un nouveau feuillet et renommez le Coef
b) Insérer les paramètres suivants dans ce feuillet
INCORPORER PBrush \s \* fusionformat
c) Créez les noms permettant de référencer ces paramètres
-> Sélectionner la plage $A$3:$B$4
-> Insertion/Nom/Créer/Colonne de gauche
Les noms ainsi créés font-il référence à des adresses relatives ou absolues ?
A-t-on besoin d'adresses relatives ou absolues pour référencer les coefs ?
2 Nommer les cellules correspondant aux notes de TD et d'examen
De quel type de référence a-t-on besoin pour référencer les notes de TD et les notes d'examen dans une formule calculant le note finale et placée en $E$3 ?
Créer les noms NoteTD et NoteExam faisant référence aux adresses adéquates.
3 Saisir les formules de calcul de notes
Saisir la formule : =MAX(noteTD*coef_TD+noteE*coef_Exam ; D3) en $E$3, puis recopier cette formule vers le bas (même principe que pour la recopie à droite)
VI Utilisation de la fonction SI
La fonction Si prend trois arguments, conformément à la syntaxe ci-dessous :
SI (Test_logique ; Valeur_si_vrai ; Valeur_si_faux)
Elle permet de retourner alternativement le résultat Valeur_si_vrai ou le résultat Valeur_si_faux en fonction du test logique donné en premier argument. Un test logique est une expression logique qui s'évalue soit à Vrai soit à Faux. Il s'agit généralement d'une comparaison entre deux valeurs (ex : NoteTD>12 ou C3>12 ou C3=C4 ou Nom = 'jules'). Lorsque ce test s'évalue à Vrai, la fonction SI retourne le résultat de l'expression Valeur_si_vrai ; lorsque ce test s'évalue à Faux, la fonction SI retourne le résultat de l'expression Valeur_si_faux. Comme avec toute fonction, il est possible d'imbriquer des appels de fonctions avec la fonction SI en faisant appel à ces fonctions dans les expressions Test_logique, Valeur_si_vrai, Valeur_si_faux.
Nous allons utiliser cette fonction pour calculer automatiquement la mention retournée à un étudiant en fonction de sa note finale. La mention Très Bien est accordée à partir d'une note au moins égale à 16, la mention Bien est accordée à partir d'une note au moins égale à 14, à partir de 10 l'étudiant est reçu avec une mention Passable et pour une note inférieure à 10, l'étudiant est Collé.
1 Compléter la formule à saisir en $F$3.
Cette formule commence par : =SI (NoteFinale >= 16 ; "Très Bien" ; SI (NoteFinale ...
Entrez cette formule en utilisant la commande Insertion/Fonction de façon récursive :
Remarquez tout d'abord que les guillemets autour de la valeur Très Bien se mettent automatiquement lorsque l'on passe à la case du troisième argument.
La case du troisième argument doit contenir un appel emboîté à la fonction SI. Il est possible d'appeler récursivement la commande Insertion/Fonction en allant sélectionner le nom d'une fonction dans la boîte liste à gauche de la barre de formule.
Vous pouvez revenir dans la boîte de dialogue de saisie des arguments de la fonction appelante en cliquant sur le nom de cette fonction dans la barre de formule. Cette facilité permet de corriger d'éventuelles erreurs commises en début de formule. Essayez-la pour remplacer la valeur "Très Bien" par "TB" dans la formule.
Même après avoir validé une formule, il est possible de rappeler la boîte de dialogue de saisie des arguments pour corriger une erreur. Pour cela, il suffit de sélectionner la cellule contenant la formule à corriger et d'appeler la commande Insertion/Fonction (ne pas cliquer dans la barre de formule).
L'intérêt de l'utilisation récursive de la commande Insertion/Fonction est d'éviter les erreurs de parenthèses et les oublis d'arguments.

2 Complétez la colonne mention du tableau de la feuille 'PV Exam'
Exploitez les connaissances acquises auparavant
VII Utilisation des fonctions de recherche
Excel propose deux fonctions de recherche (RechercheV pour recherche verticale et RechercheH pour recherche horizontale), qui restituent la valeur trouvée dans une table à partie d'une autre valeur passée en argument. Nous allons illustrer l'utilisation de ces fonctions au travers de la fonction RechercheV dont la syntaxe est donnée ci-après :
RECHERCHEV(valeur_cherchée ; table_matrice ; no_index_col ; valeur_proche)
Cette fonction recherche la valeur donnée dans l'argument valeur_cherchée, dans la première colonne du tableau dont les références sont données dans l'argument table_matrice sous la forme d'un nom ou d'une adresse de plage. Puis elle retourne la valeur située à l'intersection de la ligne contenant valeur trouvée et de la colonne dont le numéro est indiqué dans l'argument no_index_col, sachant que les colonnes du tableau sont numérotées de 1 à n. Si l'argument valeur_proche est positionné à Faux, la fonction RechercheV retourne le message d'erreur #N/A dans le cas ou elle n'a pas trouvé la valeur cherchée. Si cet argument est positionné à Vrai, elle cherche une valeur égale ou immédiatement inférieure à valeur_cherchée et retourne une valeur placée sur la même ligne.
Nous allons centraliser les informations concernant les étudiants dans une feuille de calcul séparée, et exploiter la fonction RechercheV pour restituer automatiquement les noms des étudiants dans la plage $B$3:$B$8 de la feuille 'PV Exam', à partir de leur numéro. Comme nous l'avons vu en IV, centraliser les informations fréquemment utilisées dans des pages adéquates, facilite la maintenance des feuilles de calcul.
D'une manière générale, il est toujours souhaitable d'éviter de stocker des informations de façon redondante pour
minimiser les risques d'erreur en cas de mise à jour et
économiser de la place.
1 Créer une liste de valeur contenant tous les étudiants
a) créer la nouvelle feuille et la renommer 'ListeEtu'
b) Copier la plage $A$2:$B$8 de la feuille 'PV Exam' en $A$2 dans 'ListeEtu'
2 Actualiser la feuille 'PV Exam' pour qu'elle cherche les noms des étudiants à partis de leur n°
a) Effacer les valeurs de la plage $B$3:$B$8
b) Saisir en $B$3 la formule ci-dessous, puis la recopier vers le bas
=RECHERCHEV(A3 ; ListeEtu!A$3:B$8 ; 2 ; FAUX)
Commentez le choix du type d'adresse choisi pour désigner la plage contenant la liste de valeurs.
c) Modifier un numéro d'étudiant dans la feuille 'PV Exam', de sorte à donner un n° d'étudiant qui n'existe pas. Que se passe-t-il ?
d) Ce petit inconvénient peut être résolu en remplaçant la formule saisie précédemment par la formule suivante :
=SI(ESTNA(RECHERCHEV(A3;ListeEtu!A$3:B$12;2;FAUX));"étudiant inconnu"; RECHERCHEV(A3;ListeEtu!A$3:B$12;2;FAUX))
Exploiter les commandes Copier, Coller et Insertion/Fonction pour saisir cette formule.

Fin de la partie initiation

Exercices
Faites une copie de votre classeur avant de faire ces exercices.
1) Adaptez le classeur créé précédemment pour prendre en compte les adresses des étudiants.
2) Les choses se compliquent, on souhaite intégrer à ce classeur la gestion de plusieurs matières (math, français, Anglais, Espagnol, Allemand).
Certaines matières sont optionnelles (un étudiant choisit 2 langues parmi les trois proposées). Dans chaque matière, les étudiants inscrits reçoivent une note de TD et une note d'examen. Comment stocker les notes des étudiants pour chaque matière ?
a) On souhaite pouvoir éditer une page sur laquelle figure un tableau similaire à celui de la figure 1 pour chaque matière. Le tableau d'une matière ne doit contenir que les étudiants inscrits dans cette matière. Comment s'y prendre ?
b) On souhaite enfin imprimer un tableau récapitulatif comportant une ligne par étudiants, les colonnes 'note td' et 'note exam' de chaque matière, une colonne 'note finale' et une colonne mention. Pour le calcul de la note finale, chaque matière possède un coef qui s'applique la note globale de la matière :
NoteFinale = CoefM*NoteM + CoefF*NoteF + CoefA*NoteA + CoefE*NoteE + CoefAl*NoteAl
La note globale d'une matière (ex : NoteM) est obtenue par la formule : Coef td * note td + Coef exam * note exam. Pour les matières dans lesquelles l'étudiant n'est pas inscrit, les notes de Td et d'exam ne sont pas affichées mais elles sont assimilées à zéro pour le calcul de la note finale (les options se compensent).
Les Coefs sont M->6, F->4, A->2, E->2, Al->2.
Comment produire ce tableau ?

PAGE15


PAGE \# "'Page: '#' '" 

Adresse de la
cellule active

Nom du classeur

Cellule active


Feuillet actif, onglet 'PV Exam'


Barre de
formule


Boîte liste permettant de choisir un emplacement sur disque

Nom donné au fichier


 :