Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Applications Microsoft Office Excel afficher un top 5

afficher un top 5

11 réponses
Avatar
test
Bonjour a tous,

Afin de produire des statistiques lisibles et compréhensibles par tous,
j'aimerais pouvoir afficher dans un tableau a part une sorte de top 5 des
éléments d'une colonne triés selon une autre colonne.
Pour etre plus précis, voici un exemple:
Prenons une classe de 45 élèves,
dans la colonne A vient le nom des élèves
dans la colonne B le nombre d'absences au mois de janvier
dans la colonne C le nombre d'absences au mois de fevrier
et ainsi de suite....
J'aimerais avoir dans un autre tableau, le nom des 5 élèves ayant cumulé le
plus d'absences au mois de janvier puis les 5 du mois de fevrier et ainsi de
suite.
Existe-t-il une formule ou une suite de formules a appliquer pour cela? la
solution du tri par excel par colonnes par mois n'est pas possible, vous
l'aurez compris!!

Merci d'avance pour votre aide.

Un ptit prof
Signaler un problème avec ce contenu

10 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
1 2
Avatar
JB
Bonjour

=INDEX(nom;EQUIV(GRANDE.VALEUR(absence;{1;2;3;4;5});absence;0))

Valider avec Maj+Ctrl+entrée

JB
Avatar
test
Merci JB,

Juste pour ma culture personnelle, si le tableau initial est en lignes et
non en colonnes que faut-il faire?

Peux-tu aussi s'il te plait me donner une formule plus générale ou
m'expliquer le pourquoi du comment tu ecris ca et pas autre chose!

Thanks in advance



"JB" wrote in message
news:
Bonjour

=INDEX(nom;EQUIV(GRANDE.VALEUR(absence;{1;2;3;4;5});absence;0))

Valider avec Maj+Ctrl+entrée

JB
Avatar
JB
Voir PJ

http://cjoint.com/?dkocBmiyUA

Sélectionner 5 cellules en vertical:

=GRANDE.VALEUR(Absence;{1;2;3;4;5}) donne les 5 + grandes valeurs
Maj+Ctrl+entrée

{1;2;3;4;5} représente un vercteur colonne

=EQUIV(GRANDE.VALEUR(absence;{1;2;3;4;5});absence;0) donne la position
dans Absence

=INDEX(nom;EQUIV(GRANDE.VALEUR(absence;{1;2;3;4;5});absence;0)) donne
les noms

Si on écrit la formule en horizontal:
=INDEX(nom;EQUIV(GRANDE.VALEUR(absence;{1.2.3.4.5});absence;0))

Si les tableaux nommés Nom et Absence sont en horiz, les formules sont
invariantes.

JB
Avatar
JB
Erreur sur les noms de champs

http://cjoint.com/?dkoxk2pzL7

JB
Avatar
test
Merci beaucoup!
Ca m'a bien avancé et bcp appris! c sympa.
Dernier petit détail si pierre et paul ont tous deux 4 absences en janvier,
ta formule m'indique deux fois paul (car alphabetiquement le premier) et non
pas pierre et paul!
Si tu as une idee
Merci d'avance.

PS: les fichiers joints sont extras et supers parlants, en plus ils ont les
formules en francais et en anglais ce qui n'est pas un mal!
Avatar
test
Merci beaucoup!
Ca m'a bien avancé et bcp appris! c sympa.
Dernier petit détail si pierre et paul ont tous deux 4 absences en janvier,
ta formule m'indique deux fois paul (car alphabetiquement le premier) et non
pas pierre et paul!
Si tu as une idee
Merci d'avance.

PS: les fichiers joints sont extras et supers parlants, en plus ils ont les
formules en francais et en anglais ce qui n'est pas un mal!
Avatar
Trirème
Bonsoir Test

Dans le fichier de JB...
Dans l'onglet 'Vertical' remplace 'absence' par
abscence+LIGNE(abscence)/10000
pour obtenir :
=INDEX(nom;EQUIV(GRANDE.VALEUR(abscence+LIGNE(abscence)/10000;{1;2;3;4;5});abscence+LIGNE(abscence)/10000;0))

et dans l'onglet 'Horizontal' remplace 'Absence' par
Absence+COLONNE(Absence)/10000
pour obtenir :
=INDEX(nom;EQUIV(GRANDE.VALEUR(Absence+COLONNE(Absence)/10000;{1;2;3;4;5});Absence+COLONNE(Absence)/10000;0))


Et toujours validation matricielle.
Pas cool les deux noms qui changent d'une majuscule :-(

Cela aura pour effet d'ajouter un epsilon basé arbitrairement sur les n°
de ligne ou de colonne. En cas d'ex aequo, les élèves situés dans les
cellules de N° de ligne élevés (resp colonne) seront choisis.

La division par 10000 dépend de l'ordre de grandeur des écarts
'attendus' entre les ex aequo. CàD si les écarts tiennent au
millionième, il faudra diviser par plus grand (1 milliard).

Cordialement,
Trirème

Merci beaucoup!
Ca m'a bien avancé et bcp appris! c sympa.
Dernier petit détail si pierre et paul ont tous deux 4 absences en janvier,
ta formule m'indique deux fois paul (car alphabetiquement le premier) et non
pas pierre et paul!
Si tu as une idee
Merci d'avance.

PS: les fichiers joints sont extras et supers parlants, en plus ils ont les
formules en francais et en anglais ce qui n'est pas un mal!




Avatar
JB
Pour une version de formule recopiable (janvier,férier,mars,..):

http://cjoint.com/?dlhQeSHZ3U

Nom Janvier Février Mars Avril
Dupont 12 12 12 12
Durand 8 8 8 8
Martin 16 16 5 5
Charlie 13 13 13 21
Lolo 20 12 11 9
Lulu 11 9 9 11
Momo 16 14 14 14
Mimi 10 10 10 10
Lili 5 5 5 5


En colonne H:
=INDEX(nom;EQUIV(GRANDE.VALEUR(INDEX(absence-LIGNE(absence)*0,0001;;COLON NE()-7);{1;2;3;4;5});INDEX(absence-LIGNE(absence)*0,0001;;COLONNE()-7);0))

-LIGNE(absence)*0,0001 (0,0003 - 0,0004,...) permet de différencier
les égalités.
-La soustraction conserve l'ordre de la liste des noms

JB
Avatar
JB
Une seule formule pour tous les mois:

http://cjoint.com/?dlj3ird5NI

-Sélectionner H3:K7
=INDEX(nom;EQUIV(GRANDE.VALEUR(INDEX(absence-LIGNE(absence)*0,0001;;{1.2. 3.4});{1;2;3;4;5});INDEX(absence-LIGNE(absence)*0,0001;;{1.2.3.4});0))
-Valider avec Maj+Ctrl+Entrée

Pour les 12 mois, remplacer {1.2.3.4} par {1.2.3.4.5.6.7.8.9.10.11.12}

Nom Janvier Février Mars Avril
Dupont 12 12 12 12
Durand 8 8 8 8
Martin 16 16 5 5
Charlie 13 17 13 21
Lolo 20 12 11 9
Lulu 11 9 9 11

JB
Avatar
Trirème
Bonjour JB et test
Juste un petit bémol aux formules.
D'abord je constate qu'il y en a qui ne savent pas diviser et qui
préfèrent multiplier ;-)

Ensuite, j'écrivais hier soir :
"La division par 10000 dépend de l'ordre de grandeur des écarts
'attendus' entre les ex aequo. CàD si les écarts tiennent au
millionième, il faudra diviser par plus grand (1 milliard)."

J'ajoute qu'une table de données de plusieurs milliers de lignes pose
également problème puisque mon epsilon devient énorme (6,5 pour la 65536
ème ligne en divisant par 10000). Ce qui veut dire qu'un '12' en
dernière ligne est meilleur qu'un '18,5' en ligne 2.

D'autre part, le milliardième multiplicateur ou tout autre 1E-99 semble
superflu (après des essais) car Excel ne se souvient pas de plus de 13
décimales, même s'il consent à en afficher une palanquée (dans les
calculs intermédiaires et pédagogiques).

Un bon compromis serait donc de multiplier par 10^-13 ou, pour les fanas
des formules courtes, diviser par 10^13 (un caractère de moins et je
garde ma division).

Soit, la formule finale :
=INDEX(nom;EQUIV(GRANDE.VALEUR(absence+LIGNE(absence)/10^13;{1;2;3;4;5});absence+LIGNE(absence)/10^13;0))
et validation matricielle

Bonne journée à vous deux, aux autres et à ceux qui restent.

Cordialement,
Trirème

Une seule formule pour tous les mois:

http://cjoint.com/?dlj3ird5NI

-Sélectionner H3:K7
=INDEX(nom;EQUIV(GRANDE.VALEUR(INDEX(absence-LIGNE(absence)*0,0001;;{1.2.3.4});{1;2;3;4;5});INDEX(absence-LIGNE(absence)*0,0001;;{1.2.3.4});0))
-Valider avec Maj+Ctrl+Entrée

Pour les 12 mois, remplacer {1.2.3.4} par {1.2.3.4.5.6.7.8.9.10.11.12}

Nom Janvier Février Mars Avril
Dupont 12 12 12 12
Durand 8 8 8 8
Martin 16 16 5 5
Charlie 13 17 13 21
Lolo 20 12 11 9
Lulu 11 9 9 11

JB



1 2