[fr-users] [calc] sommeprod, somme.si : incompatibles avec une plage définie sur plusieurs feuilles ?

bonjour à tous

tout d'abord un grand merci à tous ceux qui participent activement à 
cette liste : j'apprends et je progresse grâce à vous (et je ne pense 
pas être la seule!)

Je suis enseignante, et je souhaiterais évaluer mes élèves par 
compétences (plus ou moins..) et pour ne pas y passer mes soirées, j'ai 
essayé de concevoir un classeur pour me faciliter le travail (et surtout 
le décompte des points)

j'ai déposé mon classeur pour faciliter la compréhension de ma demande :

http://www.cjoint.com/c/EHejJVvCQ4L

Première feuille :

je copie colle en début d'année la liste de mes élèves dans la colonne H 
et cela se répercutera sur toutes mes autres feuilles.

Deuxième feuille :

un exemple de DS.

À partir de la colonne L : ligne 1 mes commentaires, ligne 2 le numéro 
de la question, ligne 3 la compétence évaluée, et le formatage 
conditionnel gère la mise en forme.

Lorsque je saisis les notes, le bilan de ce devoir est automatiquement 
effectué dans les premières colonnes. Je saisis le caractère a pour les 
éventuels absents dans la colonne I.

Troisième feuille :

une feuille prête (avec les formules nécessaires et le formatage 
conditionnel), que je copie colle lorsqu'il y a une nouvelle évaluation 
(la feuille nommée modele reste toujours la dernière feuille, j'insère 
les nouvelles feuilles juste avant celle-ci)

Pas de souci jusque là.


Mon problème concerne le bilan à effectuer sur la feuille 1 :

pour chaque élève et chaque compétence 1 j'aimerais additionner toutes 
les notes obtenues dans cette compétence, puis ramener la note sur 20, 
en divisant par la somme des points maximums obtenus et en multipliant 
par 20.

(ex : l'élève 1 a eu 3 sur 5, 8 sur 10 et absent : il obtient 
(3+8)/(5+10)*20 ;

l'élève 2 a eu 3 sur 5, 8 sur 10 et 1 sur 2 : il obtient 
(3+8+1)/(5+10+2)*20 ;)

Ce qui me pose souci est de tenir compte effectivement des absences 
(c'est à dire ici diviser par 15 et non par 17)


J'ai essayé (dans la feuille Bilan, en A4) :

=20*SOMME(DS1.A4:modele.A4)/SOMMEPROD(DS1.A$3:modele.A$3);1*ESTNUM(DS1.A4:modele.A4))

ce qui ne convient pas (on ne peut pas utiliser sommeprod sur une 
matrice définie sur plusieurs feuilles?)

par contre, le début de la formule SOMME(DS1.A4:modele.A4) me convient 
et conviendra encore lorsque j'ajouterai des évaluations, donc des feuilles.


J'ai ensuite testé (toujours dans la feuille Bilan, en A4) :

=20*SOMME(DS1.A4:modele.A4)/SOMME.SI(DS1.A4:modele.A4;"<>"&"" 
;DS1.A$3:modele.A$3)

avec le même problème avec la fonction Somme.si cette fois-ci

j'ai ensuite essayé (pour la somme des coefficients):

=SOMMEPROD(INDIRECT(NomFeuille&".A$3");1*ESTNUM(INDIRECT(NomFeuille&".A4")))

en créant un "champ" (?) nommé NomFeuille en M8, M9 de la première feuille.

Cette formule me semble bien donner la réponse attendue en A4, mais elle 
ne s'étend pas correctement (A$3 et A4 sont des caractères "bloqués")

j'ai tenté de les remplacer par colonne() et ligne() mais j'avais des 
soucis de référence circulaire


et là... je ne sais plus quoi faire !


Désolée d'avoir été aussi longue, mais je ne sais pas s'il y a une 
formule qui "marche" ou si la conception même de mon classeur est à 
revoir (d'où mes explications!)

merci à tous ceux qui m'ont lue jusque là.

je suis sous Ubuntu 14.04 et LibreOffice Version: 4.2.7.2, Build ID: 
420m0(Build:2)


Sylvie


--
Envoyez un mail à users+unsubscribe@fr.libreoffice.org pour savoir comment vous désinscrire
Les archives de la liste sont disponibles à http://listarchives.libreoffice.org/fr/users/
Tous les messages envoyés sur cette liste seront archivés publiquement et ne pourront pas être 
supprimés