[XL-2003] Noms des élèves qui ont la meilleure note [Résolu]

[bolide7]

Bonjour,
Les noms sont dans la colonne B.
Les notes sont dans la colonne C.

Le nom de l'élève qui a la meilleure note est :

Code :

=INDEX($B$2:$B$61;EQUIV(GRANDE.VALEUR(C$2:C$61;1);C$2:C$61;0))

Par contre quand plusieurs élèves ont la meilleure note, quelle est la formule ?
A priori, il doit s'agir d'une formule matricielle à valider avec CTRL + MAJ + ENTREE.
Avec mes connaissances (qui hélas sont limitées !), je n'en sais pas plus.
D'avance merci beaucoup pour la réponse.
bolide7

[hben1961]

Bonjour à tous,

Avec ajout d'une colonne D (par exemple) contenant la formule suivante (recopiée vers le bas):

Code :

=RANG(C2;$C$2:$C$61;0)+NB.SI($C$1:C1;C2)

qui donne par ordre décroissant les notes des élèves (l'ordre concerne aussi les notes identiques selon l'ordre d'apparition)...

Et pour extraire les noms des élèves ayant la meilleure note une autre formule à mettre en H2 (par exemple) et recopiée vers le bas (un nombre de cellules supérieur ou égal au nombre d'élèves ayant la meilleure note)...

Code :

=SI(LIGNE()-1<=NB.SI($C$2:$C$61;GRANDE.VALEUR($C$2:$C$61;1));INDEX($B$2:$B$61;EQUIV(LIGNE()-1;D$2:D$61;0));"")

Cordialement

[aereno]

Bonjour,

Une autre solution :

en k2 :

Code :

=EQUIV(MAX(C$2:C$61);INDIRECT("c" & 2+K1 & ":c$61");0)+K1

en l2 :

Code :

=INDEX(B$2:B$61;K2)

en recopiant vers le bas, on ontient la liste des noms associés à la meilleure note

[bolide7]

Bonjour hben1961 et aereno
Bravo à vous deux, vos 2 formules fonctionnent très bien même si je préfère celle de hben1961.
Même si je n’ai pas tout assimilé, elle me paraît plus adaptée à mon niveau en EXCEL.

De mon côté, sur Internet, j’avais trouvé cette formule qui me plaisait bien, car elle évite la création de nouvelles colonnes et la « Recopie incrémentée ».
Après la sélection d'une plage de 4 cellules, cette formule, qui donne les noms des 5 meilleurs notes, est :

Code :

=INDEX($B$2:$B$61;EQUIV(GRANDE.VALEUR(C$2:C$61/9^9;{1;2;3;4;5});C$2:C$61/9^9;0))

Validation par Ctrl+Maj+Entrée

Serait il donc possible d’améliorer cette formule, afin de n’avoir que les noms de ceux qui ont la meilleur note et non les noms des 5 meilleures notes ?
Je pense qu’il faudrait légèrement modifier cette formule afin d’obtenir le résultat souhaité.
Mais, je suis peut-être trop gourmand !
D’avance merci pour ce complément d’information.
bolide7

[aereno]

Bonjour,

Pour un calcul en une cellule à recopier vers le bas, j'ai ça (à placer en 1ère ligne) :

Code :

=SI(LIGNE()<=NB.SI(C$2:C$61;MAX(C$2:C$61));INDEX(B$2:B$61;EQUIV(MAX(C$2:C$61);INDIRECT("c"&2+SIERREUR(EQUIV(K5;B$2:B$61;0);0)&":c$61");0)+SIERREUR(EQUIV(K5;B$2:B$61;0);0);0);"-")

Mais ça me parait un peu lourd comme formule.

Pour la faire fonctionner sur une autre ligne que la première, il faut modifer en ligne()-1 pour la deuxième ligne, ligne()-2 pour la troisième et ainsi de suite.

J'ai essayé, mais je ne vois vraiment pas comment adapter ta formule matricielle.

[bolide7]

Bonjour aereno,
Merci beaucoup pour prendre du temps pour te pencher sur mon problème.
Mais, comme cela ne marche pas, j’aurai plusieurs questions :
Lorsque tu dis "à placer en première ligne", cela correspond à la première ligne de la feuille "Ligne 1" ou à la première ligne du tableau "Ligne 2".
Il y a 2 "c" minuscules dans ta formule. Est-ce normal ?
A quoi correspond le "K5" ? Est-ce une quelconque cellule vide ?. Ce qui n’est pas le cas dans mon tableau.
Cependant, j'ai fait plusieurs tentatives (changement de ligne, avec des "C" majuscules, changement de "K5" en "BB5" qui est une cellule vide) sans succès.

Précision pour la formule matricielle, ce n'est pas la mienne, mais celle trouver sur Internet.
Rendons à César, ce qu'il lui appartient !
Pour cette formule matricielle, j’avais comme idée de la modifier en lui indiquant de notifier le nom de l’élèves qui ont les 5 meilleurs notes "Et" qui ont la plus "Grande valeur".
Par exemple, cela aurait pour conséquence de ne pas indiquer les noms des élèves qui n’ont pas la note la plus élevée.
Mais peut-on intégrer ce paramètre dans cette formule matricielle ?
N’ayant pas les compétences pour, je l’ignore totalement.
bolide7

[aereno]

Désolé, j'avais fait quelques modifications malheureuses avant de copier coller ma formule.

La formule suivante placée en k2 et recopiée vers le bas fonctione :

Code :

=SI(LIGNE()-1<=NB.SI(C$2:C$61;MAX(C$2:C$61));INDEX(B$2:B$61;EQUIV(MAX(C$2:C$61);INDIRECT("c"&2+SIERREUR(EQUIV(K1;B$2:B$61;0);0)&":c$61");0)+SIERREUR(EQUIV(K1;B$2:B$61;0);0);0);"-")

On pourra changer la colonne à condition de changer les "K" dans la formule.

Quand on fait un EQUIV, c'est toujours le premier résultat qui est retourné d'où mon idée de réduire à chaque ligne la zone de recherche.

la fonction GRANDE.VALEUR retourne bien les plus grandes valeurs dans l'ordre mais si on veut chercher les noms associés, on se confronte à nouveau au problème d'EQUIV.

[bolide7]

Rebonjour aereno,
Ma colonne K étant occupée par d'autres données, j'ai placé ta formule en AV2 et remplavé les K1 par AV1, qui est une cellule vide, mais ça ne marche toujours pas.
Il doit y avoir un "petit grain de sable" !
Mais où ?

[aereno]

Ca ne fonctionne pour aucune ligne?
Qu'est-ce qui est affiché?

J'ai bien mes notes de c2:c61 et mes noms en b2:b61.
La formule fonctionne avec des lignes vides et des notes "non numériques"

Je ne vois pas ce qui peut aller mal.

Il faudrait voir ce qu'affichent les cellules

[bolide7]

Mes noms sont en B2/B61.
Mes notes sont en C2:C61.
Ma colonne K étant occupée par d'autres données, j'ai placé ta formule en AV2 et remplacé les K1 par AV1, qui est une cellule vide, et tiré vers le bas.
Résultant : En AV2 apparaît : #NOM?
Et dans toutes les cellules en-dessous apparaît le signe "-".

[aereno]

Je ne comprends pas.

La même formule pour être collée en AV2 :

Code :

=SI(LIGNE()-1<=NB.SI(C$2:C$61;MAX(C$2:C$61));INDEX(B$2:B$61;EQUIV(MAX(C$2:C$61);INDIRECT("c"&2+SIERREUR(EQUIV(AV1;B$2:B$61;0);0)&":c$61");0)+SIERREUR(EQUIV(AV1;B$2:B$61;0);0);0);"-")

Il serait peut-être intéressant de revenir à la première proposition que je t'avais faite (beaucoup plus simple) en masquant la colonne de calcul.

[hben1961]

Bonjour à tous,

La fonction SIERREUR() ne fonctionne pas en 2003... Remplaces la partie

Code :

SIERREUR(EQUIV(AV1;B$2:B$61;0);0)

dans la formule par

Code :

SI(ESTERREUR(EQUIV(AV1;B$2:B$61;0));0;EQUIV(AV1;B$2:B$61;0))

et la formule donnée par aereno sera

Code :

=SI(LIGNE()-1<=NB.SI(C$2:C$61;MAX(C$2:C$61));INDEX(B$2:B$61;EQUIV(MAX(C$2:C$61);INDIRECT("c"&2+SI(ESTERREUR(EQUIV(AV1;B$2:B$61;0));0;EQUIV(AV1;B$2:B$61;0))&":c$61");0)+SI(ESTERREUR(EQUIV(AV1;B$2:B$61;0));0;EQUIV(AV1;B$2:B$61;0));0);"-")

Cordialement

[hben1961]

Bonjour à tous,

On pourra aussi utiliser la formule :

Code :

=SI(LIGNE()-1<=NB.SI(C$2:C$61;MAX(C$2:C$61));INDEX(B$2:B$61;EQUIV(MAX(C$2:C$61);C$2:C$61;0));"")

en AV2 (seulement) et la fomule :

Code :

=SI(LIGNE()-1<=NB.SI(C$2:C$61;MAX(C$2:C$61));INDEX(B$2:B$61;EQUIV(MAX(C$2:C$61);INDIRECT("C"&2+EQUIV(AV2;B$2:B$61;0)&":C$61");0)+EQUIV(AV2;B$2:B$61;0));"")

en AV3 qu'on recopie vers bas...

Cordialement

[bolide7]

Même si mon fichier n'est pas terminé, vous venez de faire un très grand pas.
En effet, j'ai une bonne nouvelle à vous annoncer : CA MARCHE !
Et le résultat obtenu correspond à celui recherché.
Maintenant pour une raison de présentation, j'aurai souhaité déplacer ma cellule AV2, en C68, un peu en-dessous le la colonne des notes.
Sachant que la cellule C67 est vide, j'ai donc remplacé les 4 "AV1" par "C68" pour obtenir la formule suivante :

Code :

=SI(LIGNE()-1<=NB.SI(C$2:C$61;MAX(C$2:C$61));INDEX(B$2:B$61;EQUIV(MAX(C$2:C$61);INDIRECT("c"&2+SI(ESTERREUR(EQUIV(C67;B$2:B$61;0));0;EQUIV(C67;B$2:B$61;0))&":c$61");0)+SI(ESTERREUR(EQUIV(C67;B$2:B$61;0));0;EQUIV(C67;B$2:B$61;0));0);"-")

Hélas, cette manipulation ne conserve pas le résultat, peut-être à cause du début de la formule EXCEL, qui fait référence à :

Code :

SI(LIGNE()-1

Par contre, il est fort probable que vous n'ayez pas de solution à me proposer.

[hben1961]

Bonjour à tous,

Remplaces l'expression

Code :

 LIGNE()-1

par l'expression

Code :

 LIGNE()-67

et le tour est joué...

Cordialement

[bolide7]

Bonsoir hben1961,

Avant les aliens d'INDEPENDENCE DAY, je n'avais pas vu ton message de 19h03.
Tes 2 formules fonctionnent également très bien.

Sans aucune difficulté, avec le "-67", j'ai pu déplacer les résultats dans un endroit plus adapté. En effet, le tour est facile lorsque, comme toi, on le maîtrise.

Il me reste un dernier tableau à réaliser, celui des résultats par matière, avec sur la "Feuille2", les noms en colonne B et les notes décroissantes en colonnes C.
Pour les notes, avec la fonction GRANDE.VALEUR, pas de problème.
C'est un peu plus compliqué pour retrouver les propriétaires des notes.
La fonction :

Code :

=INDEX(Feuille1!$B$2:$B$61;EQUIV(GRANDE.VALEUR(Feuille1!C$2:C$61;1);Feuille1!C$2:C$61;0))

marche très bien, mais ne convient absolument pas lorsqu'il y a plusieurs noms derrière une même note.
Seul le premier nom se retrouve notifié plusieurs fois.

Encore bravo pour le travail déjà accompli.
Merci également à aereno, qui a bien dégrossi le problème.
bolide7

[hben1961]

Bonjour à tous, bonjour bolide7

Pour ta dernière requête, je ne vois de solutions que d'ajouter une colonne (colonne D par exemple) dans "Feuille2" avec la formule donnée plus haut

Code :

=RANG(C2;$C$2:$C$61;0)+NB.SI($C$1:C1;C2)

et recopiée vers le bas... Et la formule d'extraction demandée sera:

Code :

=INDEX(Feuille1!$B$2:$B$61;EQUIV(PETITE.VALEUR(Feuille1!D$2:D$61;LIGNES($1:1));Feuille1!D$2:D$61;0))

Cordialement

[bolide7]

Bonsoir hben1961,
Je suis en train d'essayer de créer une formule pour retrouver la note de l'élève, car si j'ai bien un nom, je n'ai pas sa note.
Mais avant, pourrais tu me préciser à quoi correspond dans ta deuxième formule (la formule d'extraction) :

Code :

LIGNES($1:1)

On parle bien de la même chose, il s'agit bien de la ligne 1 de la feuille 1, c'est à dire la ligne qui se trouve juste au-dessus du tableau des noms et des notes.
En effet, j'ai un petit problème, la première ligne de données de mon tableau de la feuille 2 n'est pas la ligne 2 comme sur la feuille 1, mais la ligne 10.
Je m'interrogais donc pour savoir s'il fallait que je change ces 2 chiffres. Sur le petit test que j'ai fais en mettant :

Code :

LIGNES($9:9)

le résultat reste identique.

Pour ce qui est de l'extraction de la note correspondant à l'élève, je pense y arriver (peut-être au cours de cette nuit !) et te tiendrais au courant.

En attendant pourrais tu jeter un oeil sur mon autre discussion, qui concerne ce même fichier, mais un tout autre sujet (MFC incompatibles) ?
Etant en EXCEL 2003, je ne sais pas s'il y a une solution à cet autre problème.
D'avance merci.
bolide7

[hben1961]

Bonjour à tous, bonjour bolide7

L'expression LIGNES($1:1) dans la formule donne le nombre de lignes à partir de $1 (absolue) jusqu'au chiffre après les deux points ":" (ce chiffre change en faissant glisser la formule vers le bas... En la recopiant vers le bas elle devient: LIGNES($1:1) , LIGNES($1:2) , LIGNES($1:3) , LIGNES($1:4) ...etc et qui donne respectivement: 1 , 2 , 3 , 4 ... etc. Et ces chiffres alimentent les rangs de la matrice de la fonction PETITE.VALEUR..
L'expression LIGNES($9:9) est la même que LIGNES($1:1), elle correpondent toutes les deux au nombre 1...
J'espère venir à ton aide avec cette explication...

Cordialement

[bolide7]

Rebonsoir ou plus exactement bonjour hben1961,
J'ai compris l'histoire des lignes et pourquoi le fait de mettre :

Code :

LIGNES($9:9)

donne le même résultat.

J'ai cependant laissé :

Code :

LIGNES($1:1)

car cette partie de ta formule concernait bien le tableau de la feuille 1.

Pour ce qui est de la formule proposée, elle fonctionne seulement dans la mesure où il n'y a pas d'absent. En effet, elle ne supporte pas les données manquantes. Je ne sais pas si une amélioration est possible.

Pour ce qui est de retrouver la note de chaque élève, je n'ai pas eu besoin d'utiliser la fonction RECHERCHE, car la fonction GRANDE.VALEUR fonctionne très bien, même avec une donnée manquante qu'elle place judicieusement en dernière position dans le tableau.

Je sens que grâce à toi j'approche du but !