EXERCICES CALC. Relevé de notes
A travers cet exercice, j'ai l'intention de vous apprendre beaucoup de bases en un minimum de temps. Je n'hésite pas à vous emmener à la faute et à vous faire réfléchir. Quelques fois c'est un peu illogique. Non, c'est pour vous obliger à manipuler les fonctions, les raccourcis... C'est comme ça qu'on progresse. Nous progresserons par étapes, le temps de vous laisser souffler un peu. Enfin, je n'apporte aucune solution toute faite, seulement quelques pistes à explorer.
Il faut commencer au niveau le plus simple possible pour ensuite le faire évoluer. Il convient cependant de réfléchir dès le départ sur la présentation finale que l'on souhaite obtenir.
On peut par exemple classer par trimestre. Dans ce cas, on aura 3 feuilles, une par trimestre, par exemple accompagné d'une feuille de synthèse.
Dans chacune des feuilles trimestrielles, identiques entre elles, on classe les matières horizontalement, divisées par le nombre de notes et verticalement, les noms des élèves.
Pour le côté pratique il est utile d'afficher les moyennes par matières, le classement par matières, et en fin de tableau, les moyennes générales et le classement général.
Il faut cependant faire attention à la capacité maximale du tableur (256 colonnes) qui doit suffire dans la majorité des cas.
Nous allons appliquer cette méthode, tout d'abord avec une seule matière, en comparant avantages et inconvénients, puis en améliorant notre tableau. Vous pourrez bien entendu adapter suivant vos besoins, mais si vous débutez, allez jusqu'à la fin de cet exercice. Vous apprendrez rapidement. Attention, quelques pièges et erreurs (volontaires) vous attendent.
Etape 1
Revenons à Trim1. Comme notre tableur fait souvent appel aux fonctions mathématiques, commençons par les maths... |
On passe la première ligne et la première colonne en gras.
Question Savez-vous d'un seul coup passer la 1ère ligne et la 1ère colonne en gras ? Réponse Il suffit de cliquer sur le 1 de la ligne 1, puis en maintenant la touche Ctrl enfoncée, cliquer sur le A de la colonne A et enfin cliquer sur l'icône Gras ou le raccourci Ctrl+G. Quelques valeurs de tests et nous voici avec notre première formule. Nous n'allons pas nous préoccuper de coefficient pour l'instant. En colonne E, nous calculerons les moyennes des notes obtenues. Dans la cellule E3 nous entrons la formule =MOYENNE(B3:D3) Le nom de cette fonction MOYENNE est parfaitement explicite. Il va faire la moyenne de la plage de cellules citées. Ici nous aurons 15,17 comme résultat. Nous avons ici le cas le plus simple. Que se passe t'il lorsqu'il manque une note ou lorsqu'une note est égale à zéro ?
|
|
Avant même d'aller plus en avant, il faut traiter ce cas. Comment ? Avec les fonctions SI et ESTERREUR |
Pour bien voir le comportement de ces fonctions, nous allons les détailler, puis enfin les regrouper. En F3, nous écrivons =ESTERREUR(E3)
Quelques tests simples permettent de vérifier le fonctionnement. Inscrivez une valeur ou deux dans les cellules de B3 à D3. Le retour de ESTERREUR affichera FAUX (aucune erreur). Ensuite effacez (complètement, pas de valeur zéro) le contenu des cellules B3 à D3. Cette fois-ci, le retour de ESTERREUR est VRAI(erreur trouvée).
Poursuivons. En G3 entrons la formule =SI(F3=VRAI; ""; MOYENNE(B3:D3)). Rien entre les guillemets. Pour expliquer cette formule, si la valeur de F3 est VRAI, il y a erreur (vous suivez ?) donc, on affiche rien, sinon on calcule la moyenne.
Remarque : vous avez écrit =SI(F3=VRAI; ""; MOYENNE(B3:D3)) et le tableur a transformé la formule en =SI(F3=1; ""; MOYENNE(B3:D3))
Pourquoi ?
Réponse Les valeurs VRAI ou FAUX sont équivalentes numériquement à 1 ou 0.
La formule finale est donc : =SI(ESTERREUR(MOYENNE(B3:D3));""; MOYENNE( B3:D3)) Enfin, supprimons les formules en F3 et G3 devenues inutiles. |
Sur la partie gauche, on voit la structure de la fonction et son assemblage. Dans l'exemple, la fonction ESTERREUR est sélectionnée. Sur la partie droite, la valeur à examiner, ici MOYENNE (B3:D3), en bas la formule complète avec en vidéo inverse la zone concernèe. Notez, en haut à droite, le résultat partiel, ici VRAI, correspondant bien à une erreur puisque les cellules B3 à D3 sont vides. |
Pour aller plus loin, nous aurions pu écrire différemment pour un résultat identique en nous servant de la fonction ESTNUM comme suit :
=SI(ESTNUM(MOYENNE(B3:D3)); MOYENNE(B3:D3);""). Essayez de comparer les 2 méthodes.
|
Etape 2
|
STOP Avant d'aller plus loin, essayez de comprendre les 2 erreurs, qui en fait n'en sont qu'une. |
Il faut modifier la formule en F3 en jouant avec l'adressage relatif et absolu. STOP Essayez de modifier la formule. Attention, seule la plage de données reste fixe. |
Vous savez faire? Il faut passer par ESTNUM qui teste si une cellule contient une valeur. Avant de lire la solution cherchez un peu. Relisez au besoin ce qui a été dit précédemment. La solution est =SI(ESTNUM(E3); RANG(E3; E$3:E$7); "") à écrire en F3, puis à recopier jusqu'en F7. |
Etape 3
|
Dans la rubrique Critères, il faut sélectionner Nombre décimal, ne pas oublier de cocher Autoriser cellules vides et dans Données, choisir entre. |
|
N'oubliez pas de remplir les rubriques Titre et Message d'erreur.
|
Attention un clic sur OK permet de passer outre ! En revanche, il suffit de faire Entrée ou Return pour valider la touche Annuler. Faites des tests. L'idéal serait d'accepter uniquement les notes entières ou par quart de point (0,25 ou 0,5 ou 0,75). C'est possible nous allons traiter ces cas plus loin. |
Etape 4
|
Pour les notes mini, utilisons le rouge |
|
Condition 2, même principe que précédemment, suivez l'exemple ci-contre. Ici la valeur maxi est 15. |
|
|
Voici ce qu'il faut faire. Il faut en Condition 1, choisir Formule est, puis entrer la formule =E7="" Cette formule simple permet de savoir si la cellule est vide (rien entre les guillemets). Ensuite, on reprend les autres conditions. Je vous suggère de faire des tests en modifiant l'ordre des conditions pour voir les changements. Il conviendra de faire bien attention à l'ordre des conditions. |
Cependant, le système n'est pas au point. On a fixé les valeurs mini et maxi à 5 et 15. Si demain vous voulez changer les règles du jeu, il va falloir reprendre le format conditionnel. Il existe une solution bien pratique. |
Dans la feuille Divers, nous allons ajouter les valeurs mini et maxi à côté des couleurs correspondantes, puis donner un nom aux cellules via les cellules nommées en appelant la boite de dialogue par Ctrl+F3. En respectant cet exemple, on affecte les noms de NoteMini à C2, NoteMaxi à C3, Absentà C4. |
|
Maintenant, je vais vous amener à la faute. Je suis sympa, vous êtes prévenus !
Nota: vous pouvez écrire NoteMini tout en minuscules ou tout en majuscules, la correction sera automatiquement effectuée et correspondra exactement à la définition que vous aurez faite dans Définir des noms. STOP Etes-vous satisfait ? Oui ? Dommage. Il y a une erreur, pas très simple à trouver, sauf si vous faites quelques tests. Cherchez un peu. Vous avez trouvé ? Non ? Pas grave. Inscrivez la valeur zéro dans une des cellule qui correspondait à Absent. Que se passe t-il ? Et bien oui, elle reste dans le style Absent. Pourquoi ? Réponse Parce qu'en écrivant E7=Absent, on tombe sur un cas particulier où zéro sera considéré équivalent à une cellule vide. Disons qu'on a voulu trop bien faire et que pour la première condition, il aurait fallu garder la formule =E7="" |
Rappelez la boite de dialogue Définir des noms via F3. Dans Assigné à, on peut lire l'adresse de la cellule concernée (nom de feuille et cellule, en adressage absolu). Effacez cette zone, puis entrez ="" et faites OK.
Nota : le nom défini Absent ne figure plus dans la liste déroulante de la barre de calculs. Normal, il n'a plus de cellule d'affectation. |
Etape 5
Essayez et si vous doutez, revenez en arrière sur les explications. Normalement, vous devez retrouver un tableau comme sur cet exemple. |
Le bon résultat: Il suffit ensuite de recopier vers la droite. Et le formatage conditionnel ? A refaire. Allez-y |
Etape 6
Je disais un peu plus haut qu'un des avantages du tableur est de détecter les erreurs d'écriture. On sait filtrer les notes entre 0 et 20. Mais pour les décimales, rien. Logiquement on doit pouvoir accepter les valeurs entières, mais également les 1/4 et les 1/2 points et rejeter toutes autres valeurs, comme par exemple 10,20 ou 5,3
Comment traiter simplement ces cas?
Commençons simple en testant la cellule B3. Pour bien faire, nous devons tester 4 conditions. Il faut tout d'abord extraire la partie décimale avec la fonction ENT et la formule =B3-ENT(B3) Vous pouvez placer cette formule n'importe où dans une cellule vide, puisqu'elle sera appelée à disparaître par la suite. Néanmoins, je vous conseille de rester sur la même ligne pour des questions de lisibilité. Ensuite nous effectuons les tests
1er cas, c'est une valeur entière, donc le reste égale zéro
=SI(B3-ENT(B3)=0; "OK"; "Erreur")
Mais il va falloir répéter 4 fois cette opération. Donc il va falloir assembler la fonction SI. Avec les 2 premiers cas (0 et 0,25) nous avons :
=SI(B3-ENT(B3)=0;"OK"; SI(B3-ENT(B3)=0,25; "OK"; "Erreur"))
|
Vous suivez l'évolution. Donc avec 3 cas (0 ou 0,25 ou 0,5), nous aurons...
STOP Essayez de trouver les 2 formules suivantes. Attention aux parenthèses.
=SI(B3-ENT(B3)=0; "OK"; SI(B3-ENT(B3)=0,25; "OK"; SI(B3-ENT(B3)=0,5; "OK"; "Erreur")))
Et enfin pour les 4 cas :
=SI(B3-ENT(B3)=0; "OK"; SI(B3-ENT(B3)=0,25; "OK"; SI(B3-ENT(B3)=0,5; "OK"; SI(B3-ENT(B3)=0,75; "OK"; "Erreur"))))
Ca devient vite illisible. Y a pas plus simple ? Mais si, avec la fonction OU, en reprenant les mêmes principes.
=OU(B3-ENT(B3)=0; B3-ENT(B3)=0,25; B3-ENT(B3)=0,5; B3-ENT(B3)=0,75)
Nous aurons un retour de VRAI ou FAUX suivant cas. C'est tout de même plus simple à comprendre. Faites des tests.
C'est déjà mieux, mais, refaire ce test pour chaque cellule de note est à éviter. On peut donc reprendre la dernière formule et en ajoutant la fonction SOMME
=OU(SOMME(B3:D3) - ENT(SOMME(B3:D3))=0; SOMME(B3:D3) - ENT(SOMME(B3:D3))=0,25;SOMME(B3:D3)- ENT(SOMME(B3:D3))=0,5; SOMME(B3:D3) - ENT(SOMME(B3:D3))=0,75)
|
Astuce. Regardez bien l'illustration ci-contre. La sélection de la liste Elèves s'est faite de bas en haut. En effet notre formule préparée correspond à la ligne 3. Avec cette méthode, c'est la cellule A3 qui est active. |
Vous avez trouvé ? Bien sûr. Il faut tester si le résultat est VRAI ou FAUX, plus précisément FAUX dans notre cas. La formule complète est donc : =OU(SOMME(B3:D3) - ENT(SOMME(B3:D3))=0; SOMME(B3:D3) - ENT(SOMME(B3:D3))=0,25; SOMME(B3:D3)- ENT(SOMME(B3:D3))=0,5; SOMME(B3:D3) - ENT(SOMME(B3:D3))=0,75)= 0 |
Maintenant, faites quelques tests.
Remarque On peut dire qu'il y a erreur dans ce système, puisque si par exemple pour l'élève 04 on entre les valeurs 18,2 et 12,8 la somme sera de 31, donc sans décimale et apparemment juste. En revanche on peut dire que les notes sont inscrites l'une après l'autre, jamais ensemble. L'erreur doit donc être visible. Il vous sera également possible de créer le même principe de recherche d'erreurs, verticalement et qui affichera le code erreur dans les numéros de notes par exemple. |
Autre remarque. Pour plusieurs matières horizontales, les formules vont se rallonger jusqu'à devenir ridicules. Nous modifirons cela plus loin.
Etape 7
Nous allons utiliser les formules matricielles... Vous ne connaissez pas? Suivez-moi, c'est simple (enfin presque) et ça permet de faire beaucoup de choses. |
Au final, nous écrivons =SOMME((B3:B7)="") Ecrite telle quelle, la formule retourne #VALEUR. Il faut en faire une formule matricielle qui va additionner les retours VRAI (ou 1 puisque c'est identique). Pour valider la formule il faudra, au lieu de faire Entrée, faire Ctrl+Maj+Entrée, qui aura pour effet d'écrire la formule entre les crochets et donnera :{=SOMME((B3:B7)="")} Si vous n'y arrivez pas, parce que la cellule est déjà entrée, modifiez là partiellement (enlevez la dernière parenthèse puis rajoutez là, par exemple), puis Ctrl+Maj+Entrée
|
Ensuite, il suffit de recopier la cellule matricielle. C'est le même principe que la copie traditionnelle, mais en plus il faut maintenir la touche Ctrl enfoncée.
|
|
|
Nous arrivons dans un des cas les plus complexes. En E12, le résultat est faussé par les retours de formules de E3 à E7. Faites un test simple dans une cellule libre en écrivant =E7>NoteMaxi . Le retour sera toujours VRAI (bizarre) alors que =D7>NoteMaxiretournera FAUX (logique). Il faut donc contourner cette particularité en décomptant les absents. Ce qui fait qu'en E12, la formule corrigée sera : =SOMME((E3:E7)>=NoteMaxi)-E11 Et en E13, il faudra faire l'inverse en écrivant: =SOMME((E3:E7)<=NoteMini) |
|
Etape 8
On commence a y voir beaucoup plus clair et nous allons pouvoir passer à la suite, c'est à dire recopier ce que nous avons fait pour inscrire les autres matières. Le plus dur est fait.
Le titre Mathématiques est sur plusieurs cellules, via sélection de B1 à F1, puis menu Format, Fusionner les cellules, Définir, et centrage par l'icône |
Notre action va donc être double, d'abord déverrouiller la protection des cellules de notes. Par défaut, toutes les cellules sont verrouillées. Donc, clic droit et dans Formatage des cellules, onglet Protection, décochez Verrouiller.
|
|
|
Notez que si les cellules notes sont effacées, les formatages conditionnels et les validités sont restés. Continuez les recopies pour les matières Histoire et Géographie. |
Etape 9
Et si on parlait des coefficients ?
Revenons dans la feuille Trim1 , cellule V3 nous allons faire le total des points liés à leurs coefficients respectifs. 2 solutions, la plus simple : Nombre de points en V3 : Les puristes préféreront : =(SOMME(B3:D3)*CoefMaths) + (SOMME(G3:I3)*CoefFrançais) + (SOMME(L3:N3)*CoefHistoire) + (SOMME(Q3:S3)*CoefGéographie) En W3, nous calculons la moyenne générale : =V3 / (CoefMaths + CoefFrançais + CoefHistoire + CoefGéographie) Si le nombre total de points est égal à zéro, on admet que l'élève n'existe pas, donc on modifie la formule comme suit : =SI(V3=0;""; V3 / (CoefMaths + CoefFrançais + CoefHistoire + CoefGéographie)) Enfin, calcul du rang en X3 : qu'il faut corriger comme suit pour éviter les erreurs : =SI(W3=""; ""; RANG(W3; W$3:W$7)) |
Vous pourrez là aussi appliquer un formatage conditionnel pour faire ressortir les bons et les mauvais. Maintenant vous savez très bien faire. |
Nettoyez vos feuilles de toutes notes, sélectionnez la zone A1:X13 de Trim1 et faites un copier-coller dans les feuilles Trim2 et Trim3
Astuce Vous avez envie de tester les 3 feuilles et cependant vous n'avez pas trop envie d'aligner des chiffres. Pourquoi ne pas utiliser une fonction toute faite ? La fonction ALEA.ENTRE.BORNES va vous générer des chiffres au hasard entre les limites mini et maxi que vous aurez imposées. Pour éviter des décimales, ENT se chargera d'arrondir le résultat. Placez donc : =ENT(ALEA.ENTRE.BORNES(0;20)) dans B3 de Trim1, ensuite, il suffit de recopier. Vous savez faire !
Etape 10
Ouvrons une nouvelle feuille que nous appelons Annuel et fera la synthèse des trimestres. Nous allons utiliser la Consolidation des données.
Dans la feuille Trim1, sélectionnons la zone A1:X2 que nous recopions à partir de A1 de Annuel. Ensuite plaçons-nous en A3.
|
|
|
|
Notez à gauche la présence de nouveaux symboles Notez également les numéros de lignes 2, 6, 10,... Des lignes sont masquées. |
Quelques mises en pages, formatages conditionnels pour fignoler et tout sera parfait. |
Et maintenant, à vos claviers.
Ce site n'est plus mis à jour. Merci aux nombreux visiteurs depuis 2003. Consultez le Wiki LibreOffice. |
|||||
Le tableur Calc par l'exemple : christianwtd@free.fr
|
. | Site optimisé pour Firefox |
Dernière modification : 29/08/2010 à 18h48
Compteur de visite temps réel LERAPIDE |
|
Compteur non certifié ![]() ![]() ![]() |