Excel: modifier la formule d'un tableau

[Ligeard]

Bonjour,


J'utilise pour le report de notes un tableau, où je souhaite automatiser certaines taches comme le calcul des moyennes du devoir, des élèves: sur le trimestre, année, etc.

Le problème est que j'utilise un tableau mais ne maîtrisant pas les formules, je ne comprends pas celle utilisée et je n'arrive donc pas à la modifier pour insérer une ligne avec les coefficients.

L'oubli des coefficients est assez gênant.

Quelqu'un serait-il capable de m'aider: ajouter une ligne coefficient, puis de modifier la formule de calcul des moyennes pour chaque élève?

Je vous remercie d'avance pour votre aide.

 

[Aliboron]

Quelqu'un serait-il capable de m'aider : ajouter une ligne coefficient, puis de modifier la formule de calcul des moyennes pour chaque élève ?

Est-ce que tu peux donner quelques détails, en particulier décrire la formule à adapter, une petite description des cellules concernées ?

Le principe des moyennes avec coefficients, c'est de multiplier les notes par leur coefficient puis de diviser la somme des notes par la somme des coefficients. Mais difficile de te dire comment l'appliquer sans plus de détails sur l'organisation de tes données...

 

[Ligeard]

Je colle la formule:
=SI(SOMME(C6:AC6)=0;"A";SOMME(C6:AC6)/(SI(ESTNUM(C6)=VRAI;$C$3)+SI(ESTNUM(D6)=VRAI;$D$3)+SI(ESTNUM(E6)=VRAI;$E$3)+SI(ESTNUM(F6)=VRAI;$F$3)+SI(ESTNUM(G6)=VRAI;$G$3)+SI(ESTNUM(H6)=VRAI;$H$3)+SI(ESTNUM(I6)=VRAI;$I$3)+SI(ESTNUM(J6)=VRAI;$J$3)+SI(ESTNUM(K6)=VRAI;$K$3)+SI(ESTNUM(L6)=VRAI;$L$3)+SI(ESTNUM(M6)=VRAI;$M$3)+SI(ESTNUM(N6)=VRAI;$N$3)+SI(ESTNUM(O6)=VRAI;$O$3)+SI(ESTNUM(P6)=VRAI;$P$3)+SI(ESTNUM(Q6)=VRAI;$Q$3)+SI(ESTNUM(R6)=VRAI;$R$3)+SI(ESTNUM(S6)=VRAI;$S$3)+SI(ESTNUM(T6)=VRAI;$T$3)+SI(ESTNUM(U6)=VRAI;$U$3)+SI(ESTNUM(V6)=VRAI;$V$3)+SI(ESTNUM(W6)=VRAI;$W$3)+SI(ESTNUM(X6)=VRAI;$X$3)+SI(ESTNUM(Y6)=VRAI;$Y$3)+SI(ESTNUM(Z6)=VRAI;$Z$3)+SI(ESTNUM(AA6)=VRAI;$AA$3)+SI(ESTNUM(AB6)=VRAI;$AB$3)+SI(ESTNUM(AC6)=VRAI;$AC$3))*20)

Mais peut-être que le plus simple serait d'envoyer le fichier?

Je souhaite insérer une ligne supplémentaire en D, de coefficients.

 

[Aliboron]

Le principe consiste à multiplier numérateur et dénominateur par le coefficient. En admettant que le coefficient soit sur la ligne 4, à chaque fois il faut multiplier par cette ligne les lignes du numérateur et du dénominateur.

Pour simplifier l'écriture, il est préférable de faire appel à des formules matricielles (ça évite de devoir écrire autant de fois les références qu'il y a de colonnes - et donc d'avoir des formules plus courtes et plus lisibles).

Vite fait, deux méthodes (équivalentes) pour obtenir une moyenne pondérée dans ce cas de figure :

=SI(SOMME(C6:AC6)=0;"A";SOMME((C6:AC6)*($C$4:$AC$4)/SOMME(SI(ESTNUM(C6:AC6);($C$3:$AC$3)*($C$4:$AC$4))))*20)

=SI(SOMME(C6:AC6)=0;"A";(SOMMEPROD(C6:AC6;$C$4:$AC$4)/SOMMEPROD($C$4:$AC$4;$C$3:$AC$3)*ESTNUM(C6:AC6))*20)

Il faut saisir ces formules et les valider avec la combinaison "Commande-Entrée" (au lieu de "Entrée" seulement) ce qui se traduit par l'apparition de crochets {} autour de la formule et permet d'avoir le résultat attendu (au lieu de #Valeur ou de 0 selon le cas)...

 

[Ligeard]

Merci pour ton aide.

Pour les deux formules que tu proposes, elles correspondent à une écriture simplifiée de celle que j'utilise actuellement, ou prennent-elles en compte la ligne coefficient?

Merci.


Je viens de tester les formules mais elles ne fonctionnent pas.
J'ai oublié de préciser que toutes les notes ne sont pas sur 20, dans la grille actuelle j'ai la ligne 3 dans la laquelle j'indique sur combien est noté le devoir (20, 10, etc.)

 

[Aliboron]

Pour les deux formules que tu proposes, elles correspondent à une écriture simplifiée de celle que j'utilise actuellement, ou prennent-elles en compte la ligne coefficient ?

Comme je l'ai indiqué, elles partent du principe qu'il y a sur la ligne 4 le coefficient de chacune des épreuves. Donc :
pour une note de 16 sur 20 dans une épreuve de coefficient 3, on aurait :
- en C3 : 20
- en C4 : 3
- en C6 : 16

pour une note de 4 sur 10 dans une épreuve de coefficient 1, on aurait :
- en D3 : 10
- en D4 : 1
- en D6 : 4

C'est du moins comme ça que j'ai compris ton tableau. Si ce n'est pas ça, il fallait penser à décrire les données, comme demandé.

Je viens de tester les formules mais elles ne fonctionnent pas.

Chez moi elles fonctionnent toutes les deux comme indiqué (et avec les données ci-dessus, j'obtiens un résultat de "14,85714286"). Est-ce que tu as bien validé avec la combinaison "Commande-Entrée" (ou "Contrôle-Majuscule-Entrée") pour que ce soit une formule matricielle ? Est-ce que tu vois bien les crochets { et } qui encadrent ta formule ?

 

[Ligeard]

Je suis désolé si j'ai oublié d'avoir donné des informations utiles.

Je pense que le problème vient de la ligne C5, en fait j'ai passé deux lignes entre celle des coefficients et les notes, alors que toi une seule.

Par contre, la formule est bien entre crochets, comme tu l'as décrit.

Je te mets une capture du résultat:
http://img855.imageshack.us/i/screencapturei.png/

La moyenne du devoir est alors de 106,7, et pour l'élève, il y a #DIV/0!

 

[Aliboron]

Je pense que le problème vient de la ligne C5, en fait j'ai passé deux lignes entre celle des coefficients et les notes, alors que toi une seule.

... et puisque les notes sont sur la ligne 7 (et suivantes) tu as adapté la formule en conséquence ?

Pour le coup, ce devrait donc être :

=SI(SOMME(C7:AC7)=0;"A";SOMME((C7:AC7)*($C$4:$AC$4)/SOMME(SI(ESTNUM(C7:AC7);($C$3:$AC$3)*($C$4:$AC$4))))*20)

 

[Ligeard]

Ca ne fonctionne pas, mais en fait nos deux tableaux ne sont pas disposés de la même manière.
J'ai :
- sur une ligne, par exemple 7, toutes les notes d'un élève, et en AD7, je souhaite y mettre la formule pour calculer sa moyenne (en fonction donc des coefficients)
- une ligne 45, où je souhaite avoir la moyenne pour le devoir.

D'après ta capture, pour un élève les notes sont sur une même colonne, alors que moi, elles sont sur une même ligne.

 

[Aliboron]

D'après ta capture, pour un élève les notes sont sur une même colonne, alors que moi, elles sont sur une même ligne.

???? Dans mon test (et donc dans la copie d'écran) les notes de l'élève Ligeard (16 et 4) sont sur la ligne 7 (en C7 et D7 comme indiqué) et la formule fonctionne sans souci où qu'elle se trouve. Que ce soit en AD7 ou n'importe où n'a pas d'importance (sous réserve qu'elle contienne précisément la formule telle qu'indiquée). C'est pour qu'on puisse les voir que j'ai positionné les moyennes sous les notes, je pensais qu'un exemple d'une ligne suffisait pour comprendre la mécanique, d'autant que j'avais pris soin de mettre en rouge les notes pour bien les distinguer...

C'est bien simple : je n'avais pas enregistré le classeur après les premiers essais. Quand j'ai voulu répondre à ton message n°5, j'ai tout simplement copié/collé les deux formules depuis mon message n°4 pour reconstituer mon exemple, plutôt que de tout retaper. Plus tard, je les ai recopiées une ligne plus bas pour que les 6 soient remplacés par des 7 et ainsi être adaptées à ta ligne supplémentaire pour obtenir la formule du message n°8.

Je viens de refaire la même expérience (pour les mêmes raisons) et n'ai de nouveau rencontré aucune difficulté. Avec quatre élèves, cette fois (en recopiant la formule vers le bas, donc).

note : ah si, problème quand il y a des lettres à la place des chiffres, je n'avais testé qu'avec des notes ou des zones vides. Mais est-ce vraiment un problème ?

 

[Ligeard]

Cela fonctionne!

En reprenant avec une nouvelle feuille, je me suis rendu compte que la formule faisant référence à une ligne vide, j'ai tout corrigé.

J'obtiens les mêmes résultats que toi, un grand merci.


Bonne soirée, et un grand merci pour ta patience.

 

[Ligeard]

Par contre, effectivement, la formule ne fonctionne pas si je mets un + ou - plutôt qu'une note.

 

[Ligeard]

J'ai un souci avec les formules, en cas d'absence, la formule ne fonctionne pas puisque j'ai mis "Absente" au lieu d'une note.

Comment faire dans ce cas?

 

[Aliboron]

J'ai un souci avec les formules, en cas d'absence, la formule ne fonctionne pas puisque j'ai mis "Absente" au lieu d'une note. Comment faire dans ce cas ?

il faut tester sur le contenu. Mais bon, là, je n'ai pas vraiment le temps de refaire tout et de regarder. En attendant, si personne d'autre ne vient te proposer de solution, tu ne mets rien pour les absents...

 

[zeltron54]

Bonsoir,

Sans certitude, mais cela devrait fonctionner avec:
=SI(SOMME(C7:AC7)=0;"A";SOMME(SI(ESTNUM(C7:AC7)*($C$4:$AC$4);(C7:AC7)*$C$4:$AC$4)/SOMME(SI(ESTNUM(C7:AC7);($C$3:$AC$3)*($C$4:$AC$4))))*20)

 

[Ligeard]

Je comprends que tu m'as déjà aidé et que j'en demande beaucoup.

Le problème de la case vide est que le devoir pourrait être en attente ou autre.
Enfin, tu proposes effectivement une solution, mais j'espère que quelqu'un pourra me dire comment modifier la formule.

Merci

 

[zeltron54]

bonsoir,

Après essai chez moi cela fonctionne....

bon courage

 

[Ligeard]

Bonsoir,

Excuse d'avoir répondu précipitamment, mais effectivement ta formule fonctionne parfaitement.
Un grand merci.

Je l'ai testé sur quelques lignes, et c'est parfait.

Je te renouvelle mes remerciements, bonne soirée.

 

[Ligeard]

Les péripéties se poursuivent: il y désormais un léger problème d'arrondi pour la moyenne pour chaque devoir.

J'ai corrigé la formule suivante:
=SI(C3="";"NN";SI(ESTNUM(C3)=VRAI;MOYENNE(C7:C43);NB.SI(C7:C43;"+")/NBVAL(C7:C43)*100))

Elle fonctionne, mais lorsque je souhaite ajouter l'arrondi au dixième, Excel refuse car il y a trop d'arguments entrés.

Comment faire?
Ce n'est pas si gênant, mais je pensais que c'était possible.

 

[zeltron54]

bonjour,

comment ajoutes-tu arrondi??
il suffit d'ajouter, arrondi(le nombre;nombre de chiffre après la virgule)

soit pour 1 seul chiffre:
=ARRONDI(SI(C3="";"NN";SI(ESTNUM(C3)=VRAI;MOYENNE(C7:C43); NB.SI(C7:C43;"+")/NBVAL(C7:C43)*100));1)