Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2021 Correction de test psychométrique automatisée

MaiBZH

XLDnaute Nouveau
Bonjour !

Je suis en train d'automatiser la correction des protocoles de tests que j'utilise dans le cadre de mon travail de rééducatrice avec des enfants. Je souhaite gagner du temps de correction en informatisant les tableaux de données de correction des tests, cela en passant par Excel.

Lors de l'évaluation, je récupère les scores obtenus par mes patients (appelés "scores bruts") et je les compare aux résultats obtenus par d'autres enfants du même âge pour ensuite les situer au regard d'une norme (cela peut donner comme ici une "Note standard" : 1 étant la pire, 10 équivalente à la moyenne et 19 la meilleure possible).

J'aimerais trouver une solution pour que Excel retrouve dans mon tableau d'étalonnage le score brut de l'enfant que j'aurais inséré en H3. La recherche dépendra de l'âge de l'enfant, mentionné en H2.

Donc, la recherche informatique est censée se faire sur la colonne correspondante à l'âge indiqué en H2, puis une fois le score brut trouvé, trouver horizontalement la "Note Standard" correspondante. La Note standard retrouvée s'affichera en H5.

Exemple : l'enfant a 5 ans, un score brut de 10, ce qui équivaut à une note standard de 13.

Pas évident car beaucoup de paramètres à prendre en compte, alors je ne sais pas vraiment par où commencer.

Vous trouverez en pièce jointe mon tableau.

J'espère que vous pourrez m'aider dans ce projet fastidieux !
 

Pièces jointes

  • Exemple pour excel downloads.xlsx
    11.2 KB · Affichages: 5

TooFatBoy

XLDnaute Barbatruc
Bonjour,

Une proposition en pièce jointe, mais avec une modification du tableau initial.


Il y a en fait deux propositions : Feuil1 et Feuil2.

Formule de Feuil1 :
Code:
=SI(H4>28;"Trop lent";EQUIV(H4;DECALER(C6:C24;0;EQUIV(H3 & " ans";C5:E5;0)-1);1))

Formule de Feuil2 :
Code:
=INDEX(B6:B25;EQUIV(H4;DECALER(C6:C25;0;EQUIV(H3&" ans";C5:E5;0)-1);1);1)
 

Pièces jointes

  • MaiBZH_(TFB-v01).xlsx
    21.4 KB · Affichages: 3
Dernière édition:

MaiBZH

XLDnaute Nouveau
Bonjour,

Une proposition en pièce jointe, mais avec une modification du tableau initial.

Il y a en fait deux propositions : Feuil1 et Feuil2.
Extraaaa !!! Merci infiniment !

Est-ce que vous savez s'il est possible d'ajouter d'autres tranches d'âge telles que
5,0-5,5 pour une colonne, 5,6 - 5,11 pour une autre ... j'ai abandonné l'idée rapidement et me suis retrouvée à faire l'âge en mois et répéter les données car impossible de faire des intervalles d'âge (ce qui me donne un tableau immense car l'étalonnage va de 5 ans à 16 ans 11 mois vous imaginez !!)

Si vous avez une solution, je suis preneuse. Le tableau ici est fictif car je ne peux pas me permettre de diffuser les données des tests qui sont protégés.

Encore merci !
 

njhub

XLDnaute Occasionnel
Bonjour MaiBZH, le forum

Si je vous ai bien comprise,

Essayez avec la formule suivante :
Code:
=INDIRECT("B"&EQUIV(H3;INDIRECT(CONCATENER(ADRESSE(1;EQUIV(H2;A3:E3;0));":";ADRESSE(23;EQUIV(H2;A3:E3;0)));1);0);1)

EQUIV(H2;A3:E3;0): Cette partie de la formule trouve la position de la valeur en H2 dans la plage A3:E3. Il retourne le numéro de la colonne où la valeur est trouvée. Si "H2" est trouvé dans la colonne "C" (qui est la troisième colonne), cette fonction retournerait "3".
ADRESSE(1;EQUIV(H2;A3:E3;0))...ADRESSE(23;EQUIV(H2;A3:E3;0)): Ces fonctions ADRESSE sont utilisées pour créer des références cellulaires en fonction du numéro de colonne renvoyé par la fonction EQUIV. La première fonction ADDRESSE crée une référence à la première ligne de la colonne trouvée, et la seconde à la 23 ème ligne de la même colonne. Par exemple, si EQUIV trouvait la valeur dans la troisième colonne, il créerait des références "C1" et "C23".
La fonction CONCATENER est utilisée pour joindre les deux références de cellule créées par les fonctions ADDRESSE dans une seule chaîne de texte, qui représente une plage dans la colonne identifiée. Par exemple, il concaténerait "C1" et "C23" en "C1:C23".
INDIRECT(...;1): Cette fonction INDIRECT est utilisée pour convertir la chaîne de texte représentant une plage en une référence de plage réelle avec laquelle Excel peut travailler. Ainsi, "C1:C23" devient une gamme réelle qu'Excel peut reconnaître.
EQUIV(H3;INDIRECT(...);0): Cette fonction EQUIV recherche la valeur en H3 dans la plage identifiée par la fonction INDIRECT précédente (par exemple, dans C1:C23). Il retourne le numéro de ligne à l'intérieur de cette colonne où la valeur est trouvée.
INDIRECT("B"''...1): Cette fonction INDIRECT finale construit une référence de cellule en concaténant "B" avec le numéro de ligne renvoyé par la fonction EQUIV précédente. Cela crée dynamiquement une référence de cellule dans la colonne B correspondent à la ligne trouvée. Si le numéro de ligne renvoyé est "5", la référence serait "B5". Cette fonction INDIRECT retourne alors la valeur dans cette cellule, ce qui vous permet effectivement de sélectionner dynamiquement une valeur basée sur les critères d'entrée en H2 et H3.



Il faudra modifier l'âge recherché en H2, en mettant le libellé exact de la ligne 3 soit : 5 ans, 6 ans ou 7 ans
 

TooFatBoy

XLDnaute Barbatruc
Est-ce que vous savez s'il est possible d'ajouter d'autres tranches d'âge telles que
5,0-5,5 pour une colonne, 5,6 - 5,11 pour une autre ...
Normalement, tu mets autant de tranches et de valeurs que tu veux.

Le principe est de mettre la valeur de début de la tranche dans le tableau, d'où la ligne supplémentaire en fin de tableau, dans la feuille "Feuil2".
 
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…