Itération automatique dans une formule.

[Christophe791]

Bonjour à tous,
J'ai un problème avec le fichier que je vous joins : comment généraliser et automatiser la formule donnant la note de l'étudiant en colonne H ?

Données :
- Un contrôle est fait de plusieurs questions (comptées en H2): ici 5 de Q1.1 à Q2.5 mais cela peut changer en fonction des contrôles.
- Chaque question est notée sur 4 (voir commentaire en cellule G5) mais cela peut changer.
- Chaque étudiant est noté en faisant l'addition des notes qu'il obtient sur chaque question. La somme (tenant compte des coefficients) est calculée pour l'étudiant 1 en cellule H5.

Cela marche tel quel, mais si je rajoute des questions, je suis obligé de refaire la formule et obligé de la dupliquer pour chaque étudiant. C'est long et source d'erreurs.

Comment puis-je automatiser l'écriture de cette formule en H5 pour que la formule tienne compte du nombre de questions en H2 pour faire une itération réalisant la somme des cellules de la ligne et tenant compte des coefficients des lignes 3 et 4 ?

... Quelqu'un peut-il m'aider s'il vous plaît ??
A vous lire.

Regarde la pièce jointe controle.xls

 

[phlaurent55]

Re : Itération automatique dans une formule.

Bonjour christophe,

pas certain d'avoir tout compris
essaie cette formule en H5 ( à recopier vers le bas)

=C5/$C$3*C$4+D5/$D$3*D$4+E5/$E$3*E$4+F5/$F$3*F$4+G5/$G$3*G$4

à+
Philippe

 

[Christophe791]

Re : Itération automatique dans une formule.

Effectivement Philippe, ce n'est pas ce que je recherche.

Ce que je voudrais, c'est qu'en ajoutant (ou supprimant) une ou des colonnes (puisque le nombre de questions des contrôles n'est pas constant), le re-calcul automatique prenant en compte toutes les questions du contrôle à évaluer soit recalculé.
En fait il y aura autant de fichiers que de contrôles. Ou pour une classe, (sur une feuille), autant de plages de cellules que de contrôles à corriger.


Je suppose qu'il faut du VBA avec une itération allant de 1 à la valeur en H2.

 

[Chris401]

Re : Itération automatique dans une formule.

Bonjour

En H5 :

=SI(NB(C5:G5)>0;SOMMEPROD(C5:G5;$C$4:G$4)/SOMMEPROD(ESTNUM(C5:G5)*$C$4:G$4)*20/4;0)

En H22 :

=SOMME(H5:H21)/(NB(H5:H21)-NB.SI(H5:H21;0))

Ajouter la nouvelle colonne AVANT la dernière colonne question (dans cet exemple, avant la colonne G)


Cordialement
Chris

 

[Christophe791]

Re : Itération automatique dans une formule.

Chris, ce n'est pas encore ça, car :
- la formule comporte des divisions et des multiplications...
- et si je rajoute une colonne, la cellule en H5 n'est pas recalculée en prenant en compte l'ajout de cette nouvelle colonne.

J'ai mis en couleur les cinq additions qui sont issues des 5 colonnes. Il faut autant de somme que prévu dans H2, donc ici 5.
=C5/C$3*C$4+
D5/D$3*D$4+
E5/E$3*E$4+
F5/F$3*F$4+
G5/G$3*G$4

Merci d'avance.
Christophe

 

[Chris401]

Re : Itération automatique dans une formule.

Pourtant il me semblait avoir compris ta demande.

Regarde le fichier

Cordialement

 

[Christophe791]

Re : Itération automatique dans une formule.

Merci Chris, on y est presque, tu as raison l'ajout d'une colonne fonctionne, par contre la formule n'est pas la bonne.
J'ai mis le fichier en PJ et j'ai modifié la ligne 3 et les commentaires car je m'étais surement mal expliqué.
Ce que j'ai mal expliqué est ceci :
La note mise à une question est indépendante du coefficient qu'elle a sur la note globale. En effet si il y avait 3 choses à dire pour cette question, je note la question sur 3 c'est plus facile pour moi. Si la question avait 2 points clé, je note la question sur 2... etc
Ensuite, cette question, selon son importance a un coefficient en ligne 4 qui lui donnera un poids sur la note globale (sur 20 dans l'exemple).

Dans l'exemple en PJ, l'étudiant 1 a tout juste et a donc obtenu 1/1 à la Q1.1, 3/3 à la Q2.2...etc d'où les divisions. Ces résultats devront ensuite être multipliés par le coefficient respectif de chaque question.
- donc sur le fichier l'étudiant 1 devrait obtenir la note de 20/20 mais avec ta formule actuelle il n'a que 15.8/20.
- l'étudiant 2 devrait obtenir la note de 10/20 et il n'a que 7.8/20


SOMMEPROD fait des sommes de produits et j'ai besoin pour la moitié de la formule de division.
Je pourrais écrire au lieu de 4 en ligne 3 les inverses soit 1/4=0.25 et cette pirouette devrait permettrait l'utilisation de SOMMEPROD, mais visuellement ça ne sera pas génial, j'aurai un risque d'erreur.
Je préférerai une formule qui permette de faire : =C5/C3*C4+...etc

Aurais-tu une idée ?
Je reste à votre disposition si vous voulez que je ré-explique mon problème... expliquer par clavier n'est pas aisé.
Merci encore à vous,
Christophe

 

[Chris401]

Re : Itération automatique dans une formule.

Peux-tu tester (fonctionne pour les exemples fournis) :

En H5

=SI(NB(C5:G5)>0;SOMMEPROD(C5:G5;$C$4:G$4)/SOMMEPROD(ESTNUM(C5:G5)*($C$4:G$4)*($C$3:G$3));0)*20

A tirer vers le bas

Chris

 

[Christophe791]

Re : Itération automatique dans une formule.

Super génial.
En regardant ton code sur cette fonction que je ne connaissais pas, j'ai fait cette simplification :

=SOMMEPROD(C5:H5)/SOMMEPROD(C$3:H$3)*SOMMEPROD(C$4:H$4)

Qui me permet de noter sur la valeur en B4, car c'est ce qui me fallait.
Je vais maintenant pouvoir affiner et adapter à mon véritable fichier.

Merci encore
Christophe

 

[Chris401]

Re : Itération automatique dans une formule.

Avec ta formule, si la valeur en B4 = 8, la note finale du candidat qui aurait toutes les réponses justes serait de 8 et non pas 20 - Si c'est bien cela que tu veux, tu n'as pas besoin de SOMMEPROD

=SOMME(C5:H5)/SOMME(C$3:H$3)*SOMME(C$4:H$4)

Chris

 

[Christophe791]

Re : Itération automatique dans une formule.

Bon sang... Pourquoi faire simple quand on peut faire compliqué !!

Merci Chris.