EXCEL et Copies de formules Fermé

Question

Bonjour,à tous
Pour faire un tableau de notation d'éleves pour un ami :
1-j'ai  le nom de 30 élèves sur 30 lignes correspondant aux cellules B9 à B39 par ex.
 Les notes correspondantes sont dans la colonne C9 à C39;
Pour calculer la moyenne de la classe pour une matière donnée j'ai rentré la formule: moyenne(C9:C39)qui marche bien mais à condition qu'il y est une note pour chaque élève.Si une note n'est pas remplie ma valeur moyenne calculée me répond :#DIV/0!.	
Je remercie à l'avance la personne qui pourra me rectifier ma formule.
DAN

gbinforme · Answer

bonjour

Tu as dû louper quelque chose ou avoir autre chose dans les cellules car sinon ta formule tiens compte des absents sans problème.

Vaucluse · Answer

Boinjour
je ne suis pas certain, d'avoir bien compris, mais moyenne renvoi un résultat dès qu'il y a une valeur dans le champ, comme le précise gbinform. Ce qui voudrait donc dire que le #DIV0 se renvoit uniquement quand tout le champ est vide
Auquel cas l'affichage de #DIV0 peut s'éliminer comme suit:
SI(SOMME(C9:C39)=0;0;MOYENNE(C9:B39)
Si par contre, le champ à calculer affiche déja des #DIV0, c'est en amont qu'il faut éliminer sur le même principe, pour ne pas les retrouver bentre C9 et C39
Pour info: la moyenne tient évidemment compte des valeurs 0 affichées dans la liste C9:C39

Crdlmnt

gbinforme · Answer

bonjour

dans la cellule il y a déjà la formule: moyenne de 3 notes

c'est sans doute là que se situe le problème : si là aussi tu fait =moyenne(A1:C1) tu ne devrait plus avoir de division par zéro.

Si tu as mis somme(A1:C1)/NBVAL(A1:C1) ne t'étonnes pas du résultat si tu n'as pas de notes pour un éléve.

Pour que ta moyenne générale soit juste il te faut :
=SI(NBVAL(A1:C1)=0;"";MOYENNE(A1:C1) car si tu avais des éléves sans note il seraient considérés à zéro.

gbinforme · Answer

bonjour

Mets ton classeur test sur : http://www.cijoint.fr/index.php

et colles nous ici le lien généré car il doit y avoir un gros truc que l'on ne peut deviner.

Raymond PENTIER · Answer

Bonjour DAN.

Tu as reçu d'excellents conseils. Je te suggère deux méthodes pratiques de travail :

1) La plus expéditive consiste à effacer les formules quand il n'y a pas de note pour une matière (dans ton fichier, la plage AE9:AF38 en est un bon exemple).

2) Plus sophistiquée, celle qui t'amène à remplacer en W9 ta formule 
=MOYENNE(U9:V9) par =SI(NB(U9:V9)>0;MOYENNE(U9:V9);"/")
et en X9 ta formule 
=SI(W9>=17;"A";SI(W9>=15,5;"B+";SI(W9>=13;"B";SI(W9>=12;"C+";SI(W9>=8;"C";"D"))))) par
=SI(W9="/";"/";SI(W9>=17;"A";SI(W9>=15,5;"B+";SI(W9>=13;"B";SI(W9>=12;"C+";SI(W9>=8;"C";"D"))))))
Ces formules sont à recopier vers le bas ; si tu veux le même affichage en ligne 38 tu y recopies les mêmes ; si tu dois faire une moyenne générale toutes matières-tous élèves (par exemple en E38) il faut laissser vides de toute formule les cellules W38 et X38.
https://www.cjoint.com/?mjsUHV2et3

P.S. 1 &#9658; Comme le signale Vaucluse à 11:52:54, il y a des cellules parasitées par un format TEXTE, comme E38, ce qui peut être source d'erreurs.
P.S. 2 &#9658; Tu peux enrichir tes grilles de notes en affectant des couleurs selon les niveaux de moyennes A, B, C, D (en employant la mise en forme conditionnelle).

gbinforme · Answer

bonjour à tous

Je pense qu'il y a eu plein de conseils sur ce classeur qui va fonctionner à merveille :
beaucoup de 20 et de A !

Pour DAN et tous ceux qui vont s'en inspirer, j'ai fait quelques ajouts à ma façon :

- mise de certaines  formules en "formules nommées" ce qui facilite la modification et le calcul.
- modification de la notation alphabétique par utilisation d'un tableau facilement modifiable.
- ajout de tableaux croisés dynamiques (à compléter)  pour faire la synthèse des notes.
Si les tableaux des différents onglets étaient un peu plus standardisés cela permettrait de tout regrouper en automatique.

http://www.cijoint.fr/cjlink.php?file=cj200812/cijRyiZuB1.xls

Ce sont quelques exemples d'autres possibilités qui peuvent être utiles à certains : bonne découverte.

Raymond PENTIER · Answer

Dans le même esprit que gbinforme, mais beaucoup plus modestement, j'ai repris l'onglet MOYENNE ELEVES en permutant les lignes et les colonnes pour récupérer plus facilement les notes des onglets précédents en vue de réaliser une consolidation (calcul en 3D).
https://www.cjoint.com/?mjtWOkJuG4

DAN · Answer

Merci à toute l'équipe de comment ca marche mon fichier grace à vous a bien progressé.
Mes connaissances d'excel et celle de la personne  qui l'exploiteras ne me permettes pas de réaliser des tableaux croisés dynamiques.
J'ai retenu l'inversion dans le classeur Moyennes des données élèves /matières c'est en effet plus pratique.
Par contre dans ce tableau on me demande de rajouter une colonne supplémentaire(notation A,B,C,D) pour chaque matière.Cela complique la dernière colonne moyenne générale qui de ce fait est fausse car il y a des cellules avec du texte.
J'attend aujourd'hui mercredi la visite de l'utilisatrice qui me fera part de ses remarques ,et je vous ferais parvenir le fichier modifié.
Encore une fois merci,mais je ne voudrais pas trop abusé
DAN

Raymond PENTIER · Answer

... ça fait surtout un tableau de très grande largeur ! 45 colonnes, je trouve que ce n'est pas très pratique à consulter et à imprimer ...
J'aurais proposé plutôt 2 tableaux de 23 colonnes, l'un avec les notes, l'autre avec la notation A, B ...
Si tu veux ajouter ces colonnes dans ton propre fichier, procède ainsi :

&#9658; supprime toutes les fusions de cellules (en D4 notamment) ; tu les remettras après tes modifications.

&#9658; pour la "poésie" tu as tes notes en colonne D ; tu insères une colonne vierge en E ; tu sélectionnes la colonne D ; tu saisis la poignée en bas à droite de la désignation de colonne, "D" (c'est à dire en haut à droite de la cellule D1) et tu tires vers la droite sur un cran : tes formules de D sont recopiées en D et ajustées.
Tu renouvelles pour chaque matière.

&#9658; bien entendu les formules finales sont à revoir : 
En AT6 =MOYENNE(D6:AR6) deviendra =MOYENNE(D6;F6;H6 ... AP6;AR6)
En AU6 il faut reprendre la définition 
=SI(AT6>=17;"A";SI(AT6>=15,5;"B+";SI(AT6>=13;"B";SI(AT6>=12;"C+";SI(AT6>=8;"C";"D")))))
et la recopier jusqu'à AU35.

https://www.cjoint.com/?mkpuBHEnOt

DAN · Answer

Merci Raymond
Nos messages se croisent
Je vais te joindre les fichiers partiellement remplis avec les modifications que j'ai faites avec l'aide de chacun.
Seul problème que je n'ai pas su résoudre: dans le classeur MOYENNES GENERALES ,du fait de mon rajout des notations A,B,C,D ,je n'ai pas su corriger les formules en AV7.......AV33 de facon à calculer les reports de moyennes.
Tu verras j'ai aussi rajouté un tableau classement élèves qui sera fait  partir des moyennes générales.
Je pense ca prend forme grace à votre aide.
Autre chose aussi je n'ai pas su revenir à l'affichage classique sur la modifs que l'on m'a envoyé.
Très bonne soirée et merci encore de la part de l'instit (ma fille ) qui seras l'utilisatrice.
DAN

DAN · Answer

RAYMOND
Ci-joint lien de mon fichier Audrey livret scolaire dont je te parle.
http://www.cijoint.fr/cjlink.php?file=cj200812/cijoELQsWn.xls
Cordialement
DAN

Raymond PENTIER · Answer

Salut, DAN.

Je viens de revérifier sur toutes les versions d'Excel : la présence de lettres ne perturbe pas le calcul des moyennes. Saisis 10 en A1, 20 en B1 et W en C1, en G1 tu saisis la formule =MOYENNE(A1:B1) ou =MOYENNE(A1:C1), tu trouves le même résultat 15 !

Non, ce qui fausse tes moyennes générales en colonne AV, c'est la présence des zéros des colonnes AL, AN et AP, qui ne sont pas la note zéro, mais le résultat d'une formule qui n'a rien trouvé et qui affiche donc 0 !
En effet les colonnes-source respectives Y, AA et Z, dans la feuille précédente "HIST-GEO...", sont absolument VIDES, car tu n'y as saisi aucune formule.
Au contraire la colonne H (feuille "MGE") récupère les données de la colonne-source AH (feuille "HGSAL") dans des cellules qui contiennent une formule, à cause de laquelle elles affichent "", ce qui n'est pas la même chose que "rien" !

Il faut absolument que ton tableau soit homogène : ou bien il y a des formules partout, ou bien tu les effaces pour les matières qui n'ont pas de notes.

D'ailleurs pourquoi mentionner des matières qui ne sont pas notées par l'enseignant ? Mieux vaut les faire disparaître complètement !

Raymond PENTIER · Answer

Pour revenir sur la nécessité d'avoir des documents homogénes, je suis très perturbé par ce mélange de notes chiffrées et d'appréciations par lettres, qui n'autorise AUCUNE SYNTHESE CHIFFREE, donc pas de moyenne générale. Je m'en explique.

Examinons la matière HISTOIRE. Il y a 2 notes chiffrées (colonnes D et E) et une note par lettre. Il faut s'en tenir là et en tirer ses propres commentaires oraux lors du Conseil de Classe. Ce qui figure dans tes colonnes G et H n'est pas toujours exact :
* L'élève LETUR, ligne 23, a une moyenne de 6,8 pour les 2 premiers devoirs, ce qui correspond à l'appréciation D. Mais il a mérité un B au troisième devoir, soit une note entre 15,5 et 17 ; par conséquent sa véritable mpoyenne pour les 3 devoirs se situe entre 9,7 et 10,2 ! Il est donc pénalisé de 2,9 à 3,4 points ... Il aurait dû être classé C, et pas D.
* Inversement l'élève Antoine, ligne 18, a eu 18,3 pour les deux premiers devoirs, mais seulement un C au troisième, soit entre 8 et 12 seulement, ce qui abaisse sa moyenne réelle entre 14,8 et 16,2 soit B ou B+, mais sûrement pas A !

Discutes-en avec l'instit.
J'ai moi-même été enseignant à une époque, et c'était la grande polémique sur le rôle et l'utilité pédagogique des notes : mettre en avant ce qui est positif dans une leçon ou un exercice, ne pas stigmatiser les échecs, et concevoir une appréciation globale ! On en est revenu, heureusement, mais il faut tirer les conséquences d'un double système de notation : ce ne sont pas les maths qui permettront d'en tirer une moyenne chiffrée ...

Je n'ai constaté cette hérésie (mathématique) que pour l'histoire et la géographie, fort heureusement.
Il y a également le cas des écrits, qui n'ont aucune note chiffrée, mais seulement des lettres !
Pour compléter la liste des dysharmonies, alors que les formules produisent les notes en lettre A, B+, B, C+, C et D,
les colonnes F et L nous proposent en plus des A- et des B- dont on ne connaît pas l'équivalence en chiffres ...

Raymond PENTIER · Answer

"Comment revenir à l'affichage classique sur le classeur Moyennes générales ? "
Tu veux bien me pardonner, mais je ne comprends pas ta question (déjà posée au post 20). Je ne vois pas ce que tu entends par "affichage classique" : La police de caractères ? l'orientation du texte en ligne 6 ? les noms d'élèves en ligne 6 et les matières en colonne C ? supprimer les colonnes E, G, I, K ... ? ou encore autre chose ?

Raymond PENTIER · Answer

Bonjour, DAN.

C'est "Fenêtre/Libérer les volets".

* Je t'indique que ma fille Karine est instit, et que sa fille se prénomme Audrey !
* C'est vrai que je travaille tard, souvent, mais tout est relatif : quand il est déjà 23 h chez toi, il n'est encore que 18 h en Guadeloupe !

Raymond PENTIER · Answer

"... education civique,problèmes,eps,art plastique,musique qui n'ont qu'une seul note donc pas de moyenne calculée et actuellement pas remplie pour ce trimestre."
Faux problème : Même s'il n'y a qu'une seule note, la moyenne existe, elle est égale à la note ! 
En Sciences aussi, il n'y a qu'une note ...

"Voila je voudrais pas abuser mais pense tu que sans "rechambouler" tous mes tableaux cela soit récupérable ?"
On peut donc "récupérer" les moyennes des matières avec une seule note.
On peut "récupérer" des données fantaisistes en supprimant les formules de moyenne pour les matières sans aucune note.
Ce qu'on ne peut pas "récupérer", ce sont les notations par lettre ! Il faudrait faire comme si elles n'existaient pas. Mais ça va fausser l'appréciation globale ...

Raymond PENTIER · Answer

Tu ne peux pas fermer la discussion, puisque tu ne t'es toujours pas inscrit comme membre !
Il n'y a pas de formule équivalente à "rien", puisque la formule elle-même est physiquement dans la cellule : Il faut vraiment effacer toute formule, que la cellule soit vierge. 
Je reste à ta disposition et à celle d'Audrey.

gbinforme · Answer

bonjour

Il n'y a pas de formule équivalente à "rien", puisque la formule elle-même est physiquement dans la cellule : Il faut vraiment effacer toute formule, que la cellule soit vierge.

Objection, "votre honneur" comme disent les séries américaines.

Dans une cellule tu peux avoir :
- une valeur (saisie)
- une formule et dans ce cas c'est le résultat de la formule qui est considéré comme la valeur de la cellule et affiché.

Si tu saisis la formule que j'avais donnée :
=SI(NBVAL(A1:C1)=0;"";MOYENNE(A1:C1) )

et si aucune saisie n'est faite en A1:C1, la cellule de la formule sera avec une valeur vide et ne sera pas prise en compte par la formule MOYENNE suivante.

exemple : http://www.cijoint.fr/cjlink.php?file=cj200812/cij5OaQ8Q3.xls

gbinforme · Answer

bonjour

Je ne comprends pas pourquoi par ex La moyenne Musique Mélanie (AP10='HIST-GEO-SCIENCES-ANGLAIS-LITT'!Z10) donne 0,0 ;et donc est vu comme une note de 0 et fausse donc sa moyenne. 

C'est tout à fait normal tu affecte la valeur d'une autre cellule et pour que cela fonctionne comme tu veux, même punition qu'auparavant :

=SI('HIST-GEO-SCIENCES-ANGLAIS-LITT'!Z10="";"";'HIST-GEO-SCIENCES-ANGLAIS-LITT'!Z10)

et ainsi tes moyennes sont justes au cours de l'année.

C'est vraiment dommage que tu n'ai pas été plus loin avec le tableaux croisés car tout ceci est automatique et toujours correct.


Pour le classement, tu peux utiliser :
=RANG(AV7;$AV$7:$AV$33;) à tirer jusqu'en bas dans la colonne AY par exemple.
cela ne peux pas se trier.

pour avoir les élèves classés dans la colonne BA par exemple

=DECALER($C$6;EQUIV(ENT(AZ7);$AY$7:$AY$33;0);;1;1) à tirer jusqu'en bas

gbinforme · Answer

bonjour

Le classeur avec les moyennes et le classement :

http://www.cijoint.fr/cjlink.php?file=cj200812/cijkas5t1u.xls

gbinforme · Answer

bonjour

au lieu de =AV2 il faut mettre :

=RECHERCHEV(AZ7;$AU$7:$AV$33;2;0)

gbinforme · Answer

bonjour

Merci de ton message et j'espère que ta fille va te mettre une bonne note !

Elle a pas le choix si elle veut un beau cadeau du père Noël !

EXCEL et Copies de formules

22 réponses

Discussions similaires