Recherche formule pour Excel 2007 [Résolu/Fermé]

Signaler
Messages postés
59
Date d'inscription
mercredi 27 avril 2011
Statut
Membre
Dernière intervention
18 septembre 2013
-
Messages postés
2966
Date d'inscription
mardi 20 janvier 2009
Statut
Contributeur
Dernière intervention
20 décembre 2016
-
Bonjour à tous,
Instit en CM1, j'améliore inlassablement mon projet d'évaluation de mes élèves (Myta, Raymond et Michel_m se reconnaitront car ils ont une part importante dans la mise au point de cet outil).
En gros, dans une de mes feuilles, j'ai un tableau double entrée avec en ligne les noms des élèves et en colonne les compétences (250). Dans ce tableau apparaissent les % de réussite des élèves pour chaque compétence étudiée.
Visuellement les nombres apparaissent en couleur (mise en forme automatique rouge, orange et vert) ce qui me permet de voir les 3 plages de % (de 0 à 39% en rouge) (de 40 à 59 % en orange) et (de 60 à 100% en vert).
Pour aller plus loin, à partir de ce tableau double entrée, j'aimerai créer une feuille où, en rentrant uniquement le nom de la compétence, je verrai, apparaître la liste de tous les élèves qui sont dans le rouge, le orange ou le vert.
Merci à vous. J'espère avoir été assez clair. En attendant de vous lire je vous souhaite une bonne journée.

8 réponses

Messages postés
59
Date d'inscription
mercredi 27 avril 2011
Statut
Membre
Dernière intervention
18 septembre 2013
2
Un grand merci à toi Myta de t'être penché sur mon problème.
J'ai par habitude de tout garder. Je prends bien soin de tes formules dont de nombreuses honorent déjà mon dossier sur les compétences.
Est-ce que les formules évoluent? Car à mon humble niveau j'avais toujours connu en début de formule le signe égal mais je n'avais jamais vu le signe accolade!
De plus, avant on voyait bien dans la formule lorsqu'on allait chercher un renseignement dans la feuille 1 ou 2 (feuil1'!) etc... maintenant on ne voit plus rien... Du coup j'ai du mal à me repérer.
Il faut vraiment que je me mette aux bases...
Merci encore.
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 65492 internautes nous ont dit merci ce mois-ci

Messages postés
2966
Date d'inscription
mardi 20 janvier 2009
Statut
Contributeur
Dernière intervention
20 décembre 2016
851
Re le Forum

J'ai utilisé des plages nommées :

noms fait référence à =Feuil1!$C$1:$AI$1
notes fait référence à =DECALER(Noms;EQUIV(Feuil2!$C$1;Feuil1!$B:$B;0)-1;;;NBVAL(Noms))

Pour la définition des accolades { } : Créer une formule matricielle

Mytå
Messages postés
12936
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
25 février 2021
2 133
Bonjour

Voilà un exemple de ce qui peux être fait sans macro

https://www.cjoint.com/?0BAm2zkiBKb

A adapter selon tes besoins
et me redemander si nécesssaire

Bonne journée
Messages postés
59
Date d'inscription
mercredi 27 avril 2011
Statut
Membre
Dernière intervention
18 septembre 2013
2
Bonjour via55
Merci de ta réponse rapide. Je dois m'absenter quelques heures mais dès mon retour je m'en occupe.
A priori c'est exactement ce que je recherche. C'est trop bon.. merci beaucoup.
Messages postés
59
Date d'inscription
mercredi 27 avril 2011
Statut
Membre
Dernière intervention
18 septembre 2013
2
Re bonjour via55.
De retour devant l'ordi je me penche désespérément devant tes formules mais reste incapable de les transposer.
Cette fonction "Concatener" avec son esperluette m'est totalement inconnue et me donne bien du fil à retordre.
Généralement j'arrive tant bien que mal à déduire ce qu'il faut faire mais là... je ne suis franchement pas au niveau. De plus cette fonction "CAR" qui s'en mêle dans la fameuse cellule E2 de la feuille 2. Bref je suis noyé...

De plus, un détail de taille certainement, c'est que mon tableau est inversé. Les élèves sont en ligne de (C1 à AI1) et les compétences sont en colonne.
- 1ere colonne (de A2 à A215) en texte (Ex: Lit d'une manière fluide)
-2ieme colonne (de B2 à B215) en code Ex: L1 pour Lit d'une manière fluide).
Par contre si tu as le temps... je n'abandonne pas car c'est tout à fait ça que je veux faire.
Si cela ne te dérange pas trop tu trouveras ci-joint mon classeur. Inutile de tout faire mais juste le début et je me débrouillerai... (Afin d'éviter tout problème j'ai changé les noms)

https://www.cjoint.com/?0BBb0lzYsiU

Encore merci.
Messages postés
12936
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
25 février 2021
2 133
Ok je regarde cela demain no problemo

En attendant quelques explications pour t'éviter de t'arracher les cheveux :

l'esperluette remplace le + pour concatener des chaines de caractères
A1 + B1 renvoie la somme si dans A1 et B1 se trouve des nombres mais une erreur si c'est du texte A1 & B1 renvoie par contre les 2 texte concaténés
la fonction CAR (n) renvoie le caractere de code n (code ASCII 65=A 66=B etc) je l'ai utilisé pour recuperer la lettre de la colonne d'après son rang
si je veux faire reference à C4 par exemple je peux ecrire dans une autre cellule =C4
mais si la reference est obtenue par intermédiaire d'autres cellules une (A1 )donnant le C et l'autre(A2) le 4 il faut concatener et obtenir la reference avec la fonction INDIRECT
ce qui ici donnerait =INDIRECT(A1 & A2) formule qui renvoie donc le contenu de la cellule C4

A +
Messages postés
12936
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
25 février 2021
2 133
Bonjour

Voilà le fichier modifié avec les explications des formules

https://www.cjoint.com/?0BBobsHPHde

J'ai rajouté pour le fun un calcul de la moyenne des % en début de colonne, à supprimer si non utile
J'ai également figé les volets de la première feuille pour une meilleure lisibilité quand on doit rentrer des % dans le tableau

En espérant que cela te convient

Cdlmnt
Messages postés
2966
Date d'inscription
mardi 20 janvier 2009
Statut
Contributeur
Dernière intervention
20 décembre 2016
851
Salut le Forum

Une version avec formules matricielles sur le Lien(#9)

Mytå
Messages postés
59
Date d'inscription
mercredi 27 avril 2011
Statut
Membre
Dernière intervention
18 septembre 2013
2
Bonjour via55,
Un énorme énorme merci pour ce coup de baguette magique.
C'est vraiment exactement ce que je voulais...
Par contre j'ai beau essayer de comprendre le "CAR" et "&" mais je n'arrive pas à accrocher. Pour l'instant c'est bien au dessus de mes possibilités. J'ai, je crois, un gros problème de sémantique (contatener, renvoie la valeur, retourne la chaine, caractere de code ...). Mais je garde tes explications dans un coin et j'y viendrai petit à petit. Génial pour les % que tu as rajouté.

Une cellule me chagrine... A partir de la G28 de la feuille 2 (car cette année je n'ai que 24 élèves) Faudra-t-il que je change cela? En effet l'an prochain je serais certainement à 29 ou 30 élèves?

Pour info:
En fait j'ai créé ce classeur (via55) à part mais à terme je vais l'intégrer dans mon usine à gaz... (((Je dois être le seul instit à me casser la tête comme ça))).
En effet le tableau double entrée dont je te parle fais parti d'un classeur "RESULTATS FINAUX" qui se met à jour automatiquement au fur et à mesure que je rentre mes résultats (1 feuille par compétences). Mais ce tableau est présenté comme notre livret papier et il y a des lignes vides entre certaines compétences.
Pour l'instant il m'est facile de rapidement faire un copier coller des résultats de temps à autre dans le classeur dont le nom restera classeur "via55" (ça c'est par respect et reconnaissance). Je ne peux te l'envoyer par cjoint car il fait 10Mo.

Encore une chose, maintenant que j'ai ce tableau qui m'indique d'un coup d'oeil les lacunes de mes élèves, les idées fusent. Je vais fabriquer des séries d'exercices sur Excel.
Exemple: en A1 la phrase avec un verbe à conjuguer au présent. En B1 l'élève écrira sa réponse. en C1 il s'affichera BIEN ou FAUX selon que la cellule aura reconnue la bonne écriture du verbe qui sera caché en D1 par exemple.
Vois-tu ce que je veux dire?. As-tu le temps de me sortir une petite formule toute bête du style
- écrire dans C1 BON ou FAUX si la valeur est la même que D1. Peut être que pour toi cela n'est qu'une bagatelle! (Valable pour du texte ou des résultats d'opération)... Voir fichier joint... Mais c'est si tu as du temps bien sûr..
Par contre et c'est là que ça se complique; il faudrait que les résultats "BON ou FAUX" ne s'écrivent qu'à la fin d'une série de 10 ou 20 phrases par exemple et pas au fur et à mesure. (Au pire cela s'écrira au fur et à mesure...). L'élève serait obligé de m'appeler pour visualiser ses réponses... (Moi seul connaîtrais le bouton qui déclencherait l'apparition des résultats).

En tout cas un grand grand merci. Tu ne peux pas savoir l'intérêt que cela peux avoir pour le suivi de mes élèves...

https://www.cjoint.com/?0BBsRsZnqck
Messages postés
12936
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
25 février 2021
2 133
Bonsoir crikitu

Heureux que ça convienne, étant moi même enseignant je vois bien l'utilité de ton projet
et merci de l'hommage, il y en a qui ont leur nom sur une plaque de rue ou le fronton d'une école, j'aurai mon pseudo sur un classeur c'est aussi bien !

Pour la colonne G pas de problème tu étires la formule jusqu'à la ligne nécessaire(tant qu'on ne risque pas de changer de reference et de passer en BA ; mais cela supposerait39 élèves)

Par contre la plage nomseleves de la colonne C s'arrete en 37 (34 eleves quand même)
Pour la rallonger il faut aller dans Formules Gestionnaire de noms cliquer sur le nom de la liste et changer le 37 en 38 ouu 40 dans Fait référence à

En ce qui concerne les lignes vides pas de probleme A CONDITION que ce soient strictement les mêmes sur la feuille 1 et la feuille 2 puisque à partir de la liste déroulante on repere la ligne du code en feuille 2 et on va recuperer les % situés sur la même ligne en feuille 1

Pour tes question c'est une bonne idée voilà un début de ce qui peux être fait
https://www.cjoint.com/?0BBwxTgzpDn

N'hésite pas si tu as questions pour ceci ou autre chose; tu peux me contacter en message privé si tu veux, je suis alerté sur ma messagerie

Bon courage pour la suite et a+
Messages postés
59
Date d'inscription
mercredi 27 avril 2011
Statut
Membre
Dernière intervention
18 septembre 2013
2
Re Via55,
Petit oubli. Dans le classeur "via55" Est-il possible que le texte de la compétence choisie en C1 (la cellule jaune fluo) s'écrive en entier dans D1?
Je demande ça à tout hasard...

Bravo pour les % qui s'incrivent dans la colonne G cela m'évite de faire des allers retours.
D'autre part, que représentent les % de la case rouge, orange et verte. Ils représentent quoi, ils sont calculés comment?
Encore merci pour tout.
Messages postés
12936
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
25 février 2021
2 133
Re crikitu

formule à mettre en D1 :
=INDIRECT("A"&(EQUIV($C$1;CODES;0)+1))

Les % dans les cases representent la moyenne des % des eleves de cette categorie
si par ex 2 eleves dans colonne orange l'un à 20% l'autre à 30% tu dois obtenir 25% dans la case orange

Lorsque j'aurai le temps j'essaierai de t'envoyer quelque chose de pus complet pour des exercices avec questions

Bonne suite

Cdlmnt
Messages postés
2966
Date d'inscription
mardi 20 janvier 2009
Statut
Contributeur
Dernière intervention
20 décembre 2016
851
Salut le Forum

Une version avec des formules matricielles pour ton premier fichier

Le fichier : Crikitu13.xlsx

Mytå
Messages postés
59
Date d'inscription
mercredi 27 avril 2011
Statut
Membre
Dernière intervention
18 septembre 2013
2
OK via55 pour l'explication des % en case rouge, orange et vert...
Super pour la formule qui écrit les compétences en D1. ça marche très bien...
Merci