Ouvrons un nouveau classeur. Nommons les feuilles Trim1, Trim2, Trim3 via clic droit sur l'onglet et Renommer la feuille du menu flottant.

Revenons à Trim1. Comme notre tableur fait souvent appel aux fonctions mathématiques, commençons par les maths...
Horizontalement les matières, verticalement les noms. Nous nous limiterons à 3 notes par trimestre (vous pourrez évoluer facilement) et 5 élèves.

Un minimum de présentation (ajustement largeurs colonnes, mise en gras des titres) pour s'y retrouver.

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 ?

Et bien le résultat est différent, ce qui est normal. Dans un cas, la moyenne est faite sur 3 notes et dans l'autre cas sur 2 notes.

Poursuivons. Si aucune note n'est inscrite, cas du tableau original, le retour de la moyenne se traduit par une erreur #VALEUR

Avant même d'aller plus en avant, il faut traiter ce cas. Comment ? Avec les fonctions SI et ESTERREUR

Maintenant que vous avez compris le fonctionnement, nous allons tout regrouper dans la cellule E3

La formule finale est donc : =SI(ESTERREUR(MOYENNE(B3:D3));""; MOYENNE( B3:D3))

Enfin, supprimons les formules en F3 et G3 devenues inutiles.

Il est possible de vérifier le bon fonctionnement de fonctions assemblées à l'aide de l'Autopilote de fonctions. Ici, la cellule E3 est sélectionnée. Nous avons appelé l'Autopilote de fonctions avec le raccourci F2.

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.

Recopions la formule de E3 jusqu'à E7.
Maintenant l'idéal est de remplir le tableau de valeurs quelconques comme sur ce tableau, mais en laissant une note absente pour un élève et un élève inexistant (par la suite, vous créerez certainement un tableau où le nombre d'élèves sera supérieur au nombre réel, ceci au cas où)

Et le classement? C'est très facile avec une des fonctions de Calc, la fonction RANG. Cette fonction se présente comme suit : RANG (Valeur; données). Dans valeur, la cellule à comparer et dans données, la plage de cellules source.
Donc, en F3 ce sera =RANG(E3; E3:E7)
Il suffit ensuite de recopier vers le bas.

Des erreurs? Il y en a 2.
Plusieurs élèves ont le même classement avec des notes moyennes différentes et la dernière cellule, F7, contient #VALEUR.

STOP Avant d'aller plus loin, essayez de comprendre les 2 erreurs, qui en fait n'en sont qu'une.

Pour trouver, pas difficile, sélectionnez la cellule F4, puis cliquez dans la barre de calculs, OOo 1.1.x ou la barre de formules, OOo 2.x, comme pour modifier cette formule. Vous voyez ceci. Tout s'est décalé vers le bas.

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 avez trouvé ? Bien sûr, il faut entrer : =RANG(E3; E$3:E$7) et recopier vers le bas. Il reste l'erreur #VALEUR en F7.

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.

On avance un peu.
Traitons les cas d'erreurs possibles. Les notes autorisées vont de 0 à 20. Mais rien n'interdit de noter 25 par exemple. Utilisons donc un des avantages de Calc, la validité. Nous l'appelons via les menus Données, Validité ou les raccourcis clavier Alt+S, V.

Sélectionnons la plage de cellules concernées B3:D7, puis appelons via les menus Données, Validité ou les raccourcis clavier Alt+S, V la boite de dialogue Validité.

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.

La boite se modifie comme sur cet exemple. Il suffit d'entrer les valeurs Minimum et Maximum.

Dans l'onglet Message d'erreur, cochez Message d'erreur en cas de saisie de valeurs incorrectes, puis dans Action, choisir Avertissement (ou autre selon vos goûts).

N'oubliez pas de remplir les rubriques Titre et Message d'erreur.

Maintenant, si vous entrez des valeurs hors limite, dans les cellules concernées, vous aurez un 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.

Il serait agréable de pouvoir d'un simple coup d'oeil faire ressortir les bonnes et les mauvaises notes.
Facile ! grâce au formatage conditionnel.
Insérons une nouvelle feuille via Insertion, Feuilles, que nous nommons Divers (elle servira de fourre-tout), on va définir les styles. Dans les exemples j'utilise les couleurs pour aider à faire la différence, mais rien ne vous interdit de jouer avec les polices différentes, leurs apparences (gras, italiques,...) ou leurs tailles. Ce sera de toute façon très facile à modifier par la suite.

Sélectionnons la cellule B2, puis avec l'icône de la barre d'objets pour OOo 1.1.x ou l'icône Windows ou Linux de la barre de formatage pour OOo 2.x, choisissons une couleur d'arrière-plan, comme sur cet exemple.

Pour les notes mini, utilisons le rouge
Pour les notes maxi, utilisons le vert

Ensuite, avant d'aller plus loin, je vous recommande de créer des styles personnalisés.
Sous OOo 1.1.x, à l'aide du styliste , via l'icône de la barre de fonctions ou par le raccourci F11
Sous OOo 2.x, via l'icône Windows ou Linux de la barre de formatage ou par le raccourci F11.
Créons un nouveau style à partir de la sélection. Pour plus de facilité nous donnons des noms causants : StyleMauvais qui sera rouge, StyleExcellent qui sera vert.

Sélectionnez la plage de cellules concernées B3:E7 de la feuille Trim1, puis ensuite appelez le formatage conditionnel via les menus Format, Formatage conditionnel. Dans la boite de dialogue, cochez Condition 1, choisissez Valeur est, puis inférieure ou égale à, ensuite la valeur mini, ici 5 et enfin le Style de cellule, ici StyleMauvais, que l'on vient de définir.

Condition 2, même principe que précédemment, suivez l'exemple ci-contre. Ici la valeur maxi est 15.

Voici le résultat obtenu. Etes-vous satisfait ? Non, il y a une erreur. Les mauvaises notes sont rouges, mais également les cellules vides.

Il faut créer un style Absent. Un gris clair me semble suffisant, à vous de voir. Comment faire ? Maintenant vous savez. Allez-y ! Mais il faut modifier le formatage conditionnel en conséquence. Ce n'est pas très simple pour un vrai débutant. Toutefois avant d'aller plus loin, essayez de le faire.

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.

Voici le résultat obtenu. D'un coup d'oeil, on visualise les bonnes et mauvaises notes, ainsi que les absences.

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.

A gauche de la barre de calculs, si vous ouvrez la liste déroulante, vous trouverez les cellules nommées. Une sélection d'un nom vous amènera directement sur la ou les cellules concernées, même si la feuille n'est pas la feuille courante.

Reprenons notre format conditionnel. Il suffit de modifier comme sur cet exemple. A partir de maintenant vous pouvez à loisir modifier les règles mini et maxi. Faites des tests.

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=""

Pourtant, il existe une solution, moins connue, en servant des définitions de noms. En effet, logiquement on affecte un nom à une cellule ou une plage de cellules. Mais il est également possible d'affecter une formule, une valeur à un nom.

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.

Si vous rappelez par la suite la boite de dialogue le signe égal aura disparu et ne subsistera que le "" Tout ça pour dire qu'après correction, nous pouvons écrire à nouveau E7=Absent en Condition 1. Ouf !

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.

Tiens, mais ce serait bien si on pouvait avoir la moyenne de la classe pour un contrôle. Simple, il suffit de faire la moyenne verticale. Oui, je sais, on n'aurait pu le faire avant. Arrêtez de râler, vous savez très bien faire.

Essayez et si vous doutez, revenez en arrière sur les explications. Normalement, vous devez retrouver un tableau comme sur cet exemple.

Quelle est votre formule en B8 ?
=MOYENNE(B3:B7)
Vous êtes content de vous ? Dommage !
N'oubliez pas. Si le tableau était vide, nous aurions l'erreur #VALEUR. Il faut donc compléter la formule. Mais ça vous savez le faire maintenant.

Le bon résultat:
=SI(ESTERREUR (MOYENNE(B3:B7));""; MOYENNE(B3:B7))

Il suffit ensuite de recopier vers la droite.

Et le formatage conditionnel ? A refaire. Allez-y
Nota Il sera nécessaire de modifier provisoirement les valeurs de notes mini et maxi pour tester le bon fonctionnement du formatage conditionnel.

Ci-contre, le principe pour imbriquer les fonctions SI. On remplace un des membres par une nouvelle condition SI.

Nous allons définir une nouvelle couleur affectée aux erreurs et que nous appelons StyleErreur. Comment et où faire apparaître l'erreur commise? Je vous propose une solution. Se servir du formatage conditionnel et marquer le nom de l'élève. Pourquoi, parce que la limite des 3 conditions (bien qu'on puisse la contourner avec la fonction STYLE) est atteinte dans les cellules notes.

Plus de problème pour la maitrise du formatage conditionnel. Il n'y a plus qu'à appliquer la formule que nous avons créée.

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.

Encore une erreur ? Si vous avez su l'éviter, bravo. Sinon réfléchissez un peu. La formule est pourtant bonne, il manque juste un détail.

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

Et voici un exemple avec une double erreur.

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.

Vous aimeriez d'un simple coup d'oeil voir le nombre de bonnes, de mauvaises notes et d'absents pour un même contrôle. Facile !

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.

Pour connaître le nombre d'absents, il suffit de se servir de la fonction SOMME, mais en faisant un test. Pour détailler, si en B3 on inscrit =B3="", nous allons tester, dans une cellule vide. Au fait, vous rappelez-vous ce qui se passerait avec =B3=Absent ?

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

Astuce Si vous avez fait une fausse manoeuvre, vous aurez des problèmes par la suite. Pour vérifier que la recopie s'est bien effectuée, essayez de supprimer le contenu d'une des cellules. Si vous avez ce message, il y a erreur (vous avez oublié Ctrl). Dans ce cas, Ctrl+Z annulera la dernière action.

En B12, même principe pour la formule qui sera {=SOMME((B3:B7) >=NoteMaxi)}

En B13, même principe, mais avec une astuce supplémentaire. Nous retranchons le nombre d'absents. Pourquoi ? Parce la formule ne va pas distinguer la cellule vide de la valeur zéro.

Bien, les recopies sont faites. Vous faites la moue ? Eh oui, encore 2 problèmes. Dans la colonne des moyennes, les valeurs pour Mauvais et Excellent sont fausses.

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)

Cette fois-ci nous arrivons (enfin) à des résultats corrects.

Il est temps de songer à la présentation générale. Ici un exemple que vous n'êtes pas obligé de suivre. On trace les bordures, via clic droit, Formater les cellules, dans la boite de dialogue Formatage des cellules, onglet Bordure.

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 de la barre d'objets sous OOo 1.1.x ou l'icône de la barre de formatage sous OOo 2.x

Ensuite, nous allons protéger les cellules à ne pas toucher. Il ne faut pas pouvoir écrire sur une formule, mais seulement dans les cellules où on entre les notes.

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.

Ensuite, il suffit de protéger la feuille via le menu Outils, Protection, feuille. Le mot de passe est facultatif. Pour déprotéger, il suffira de refaire le même chemin et OK.

Une coche signifie que la feuille est protégée.

Si la feuille est protégée et que vous tentiez un modification dans une cellule verrouillée, vous aurez droit à ce message.

Suite: recopions les autres matières. D'abord, déprotégez la feuille (provisoirement), effacez les cellules notes de B3 à E7, puis sélectionnez la zone B1:F13, copiez, via Ctrl+C, placez-vous en G1, et collez via Ctrl+V. Réajustez les largeurs colonnes. Il reste à modifier le titre de la matière comme ci-contre.

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.

Dans la feuille Divers, ajoutons les rubriques comme ci-contre, avec les coefficients. Peu importe l'emplacement, puisque nous allons nommer (Ctrl+F3) les valeurs des coefficients. Nous utilisons des noms faciles : CoefMaths, CoefFrançais, CoefHistoire, CoefGéographie.

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 :
=(E3*CoefMaths) + (J3*CoefFrançais) + (O3*CoefHistoire) + (T3*CoefGéographie)

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 :
=RANG(W3; W$3:W$7)

qu'il faut corriger comme suit pour éviter les erreurs :

=SI(W3=""; ""; RANG(W3; W$3:W$7))

Vous devriez avoir quelque chose de semblable à cet exemple. Pour tester tout ce qui vient d'être fait, protégez la feuille, puis faites des tests.

Vous pourrez là aussi appliquer un formatage conditionnel pour faire ressortir les bons et les mauvais. Maintenant vous savez très bien faire.

Sélectionnons la feuille Annuel. L'appel de la boite de dialogue se fait via le menu Données, Consolider.

La fonction la plus usitée étant la moyenne, sélectionnons Moyenne.

Puis sélectionnons les zones des feuilles à consolider. Les zones doivent être identiques. Nous sélectionnons donc les zones A1:X7 successivement des feuilles Trim1, Trim2 et Trim3, en cliquant sur Ajouter à chaque fois.

Cliquez sur options puis cochez les options Etiquettes de ligne (ce sera les noms d'élèves) et Lier aux données source (les modifications seront répercutées).

Enfin, OK, et dans la feuille Annuel, vous avez un résultat similaire (les valeurs sont évidements variables) à celui-ci.

Notez à gauche la présence de nouveaux symboles et

Notez également les numéros de lignes 2, 6, 10,... Des lignes sont masquées.

Si comme sur cet exemple vous cliquez sur le 2ème . Il devient et les lignes masquées apparaissent et vous laissent voir les détails de notes par trimestre. Vous commencez à saisir toute la puissance de la consolidation. Ici on fait une synthèse pratique. Déplacez-vous sur les cellules B7, B8 par exemple. Chaque cellule est liée à la cellule de la feuille indiquée. La ligne 10 fait les moyennes. Pour les erreurs, la plus évidente est le rang, où la aussi on fait des moyennes. Il faut donc corriger manuellement, ce que vous savez parfaitement faire.

Quelques mises en pages, formatages conditionnels pour fignoler et tout sera parfait.

Le tableur Calc par l'exemple : christianwtd@free.fr

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.0 France License.