Renvoi de la ligne des x plus grandes valeurs d'un tableau

[jay_vive_sly]

Bonjour à tous !

Je cherche à renvoyer le référence des x plus grandes valeurs d'un tableau.

Je vous joins un exemple.

Je souhaiterais renvoyer l'élément de la colonne B correspondant au x plus grands nombres du tableau.

Merci d'avance pour votre aide !!

 

[tototiti2008]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Bonjour jay,


Bienvenue sur XLD

un essai en C11

=INDIRECT("B"&MIN(SI($C$2:$F$8=GRANDE.VALEUR($C$2:$F$8;LIGNE(A1));LIGNE($C$2:$F$8))))

à valider par Ctrl+Maj+Entrée
puis à recopier vers le vas

 

[jay_vive_sly]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Super ! Cela fonctionne !

Si j'obtiens des valeurs égales, lequel Excel met-il en premier ?

Est-il possible de faire passer en priorité la plus grande valeur de la première colonne ?

Encore un énorme merci !

 

[jay_vive_sly]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Si jamais tu as le temps, pourrais tu m'expliquer ta formule ?

=INDIRECT("B"&MIN(SI($C$2:$F$8=GRANDE.VALEUR($C$2:$F$8;LIGNE(A1));LIGNE($C$2:$F$8))))

Encore merci !

 

[tototiti2008]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Re,

Est-il possible de faire passer en priorité la plus grande valeur de la première colonne ?

Peut-être

=INDEX($B$2:$B$8;EQUIV(GRANDE.VALEUR($C$2:$F$8;LIGNE(A1));DECALER($A$2:$A$8;0;MIN(SI($C$2:$F$8=GRANDE.VALEUR($C$2:$F$8;LIGNE(A1));COLONNE($C$2:$F$8)))-1);0))

pourrais tu m'expliquer ta formule

C'est une formule matricielle, elle compare la matrice $C$2:$F$8 à la nième plus grande valeur

Si une valeur de cette matrice est égale à cette nième plus grande valeur, on prend son numéro de ligne

SI($C$2:$F$8=GRANDE.VALEUR($C$2:$F$8;LIGNE(A1));LIGNE($C$2:$F$8))

La fonction Min prend la plus petite valeur de ligne correspondante

MIN(SI($C$2:$F$8=GRANDE.VALEUR($C$2:$F$8;LIGNE(A1));LIGNE($C$2:$F$8)))

Et la fonction indirect permet de créer la référence de cellule en colonne B et au numéro de ligne trouvé pour aller y chercher la valeur (G, F, E...)

 

[jay_vive_sly]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Je vais essayer tout cela !

Un grand merci à toi !

 

[jay_vive_sly]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Re bonjour,


Malheureusement la première formule ne fonctionne pas lorsqu'il y a plusieurs valeurs identiques.

Ce qui manque dans la formule, c'est le fait que chaque nom (A,B,C...) des plus grandes valeurs identiques apparaissent.

Dans mon exemple, le 1000 apparait sans problème, mais pour les deux 500, il renvoie deux fois .

Ce que je voudrais, c'est que C et D apparaisse.

Et si possible que le premier 500 qui est renvoyé soit celui du groupe C, c'est-à-dire le plus grand de la première colonne.


Est-ce que c'est assez clair ou je m'embrouille ?

 

[tototiti2008]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Re,

à tester

=INDIRECT("B"&MIN(SI($C$2:$F$8+COLONNE($C$2:$F$8)/100000=GRANDE.VALEUR($C$2:$F$8+COLONNE($C$2:$F$8)/100000;LIGNE(A1));LIGNE($C$2:$F$8))))

Edit : ou plutôt :

=INDIRECT("B"&MIN(SI($C$2:$F$8+COLONNE($C$2:$F$8)/100000+LIGNE($C$2:$F$8)/100000000=GRANDE.VALEUR($C$2:$F$8+COLONNE($C$2:$F$8)/100000+LIGNE($C$2:$F$8)/100000000;LIGNE(A1));LIGNE($C$2:$F$8))))

 

[jay_vive_sly]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

C’est parfait ! Je suis assez impressionné !

Juste qq petites questions :

1. Dans le but de pouvoir utiliser cette formule dans un autre tableau, pourrais-tu m’expliquer le rôle de Ligne(A1) ?

3. A quoi dois-je faire attention si j’utilise la même formule à un autre endroit du classeur ?

2. Je rajoute une petite difficulté, serait-ce possible d’effectuer le même classement mais quand il y a égalité, de se baser sur une autre série de données liée à A, B, C…

Dans mon exemple, il s’agirait de classer les deux 1000 par rapport à la plus grande valeur de la colonne C. Donc d’arriver à A, C, A…

Merci mille fois pour toute cette aide, cela m’aide énormément !

 

[tototiti2008]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Bonjour jay,

pourrais-tu m’expliquer le rôle de Ligne(A1) ?

Ligne(A1) devient Ligne(A2), etc.. en le recopiant, ça sert à renvoyer 1, 2, 3, etc qui est le 2ème argument de GRANDE.VALEUR
ça permet de préciser qu'on veut la 1ère plus grande valeur, puis la seconde, puis la 3ème... etc

A quoi dois-je faire attention si j’utilise la même formule à un autre endroit du classeur ?

A rien en particulier si tu fais référence aux mêmes plages, la formule telle quelle devrait pouvoir se mettre n'importe où puis se recopier vers le bas

Dans mon exemple, il s’agirait de classer les deux 1000 par rapport à la plus grande valeur de la colonne C

peut-être

=INDIRECT("B"&MIN(SI($E$2:$H$8+$C$2:$C$8/1000000=GRANDE.VALEUR($E$2:$H$8+$C$2:$C$8/1000000;LIGNE(A1));LIGNE($E$2:$H$8))))

 

[jay_vive_sly]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Bonjour à tous !

Merci à toi tototiti, j'ai testé ta formule mais quand je l'étire j'arrive à des différences entre tes deux formules.

J'ai un peu prospecté mais je ne suis pas parvenu à en trouver l'origine.

Pourrais-tu m'aider ?

Un tout grand merci d'avance !

 

[tototiti2008]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Re,

Oui, le paramètre de la colonne C était trop important, en divisant par 1000000000 ça devrait passer
tu peux diviser par plus encore si tu veux plus de marge

=INDIRECT("B"&MIN(SI($E$2:$BZ$5+$C$2:$C$5/1000000000=GRANDE.VALEUR($E$2:$BZ$5+$C$2:$C$5/1000000000;LIGNE(A1));LIGNE($E$2:$BZ$5))))

 

[jay_vive_sly]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Je vais tester cela !

Peux-tu m'expliquer l'intérêt de diviser par 1000000000 ?

Un grand merci pour ta patience tototiti !

 

[tototiti2008]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Re,

Il faut diviser le chiffre en colonne C par un nombre suffisamment grand pour qu'il n'interfère pas dans l'ordre des données de B2 à EZ5 mais qu'il fasse intervenir une différence pour départager les ex aequo
Tout dépend de l'ordre de grandeur des différences des valeurs de B2 à EZ5 et de l'ordre de grandeur des valeurs de la colonne C

 

[jay_vive_sly]

Re : Renvoi de la ligne des x plus grandes valeurs d'un tableau

Un énorme merci à toi !!

J'ai encore une question qui se rapporte au même sujet, mais je sors un peu de l'intitulé de mon titre. Dites moi si il est préférable que je recommence une discussion.

1. J'ai un tableau avec plusieurs éléments dont certains se répètent. J'ai déjà trouvé (sur ce même forum) le moyen d'isoler les éléments sans doublons. Je n'ai cependant pas réussi à comprendre cette formule donc j'ai du la replacer comme je l'avais trouvée : en haut à gauche ! Toute aide pour la déplacer est la bienvenue.

2. Ce que je cherche à faire maintenant, c'est de renvoyer les éléments correspondants à ceux qui sont en double.

Avec l'exemple ce sera plus clair.

Un énorme merci d'avance !