Chercher les plus grandes valeurs d'un tableau de données et renvoyer à l'entête

[THOMASD]

Bonjour,

Ma recherche est la suivante :

Je tente d'extraire les 5 plus grandes valeurs d'un tableau de donnée, et leur attribuer le nom de l'entête correpondante.
La formule Index/Equiv fonctionne très bien si l'on considère une ligne en particulier, mais pas sur l'ensemble du jeu de données (tableau lignes X colonnes).
Y a t'il une solution sur la base de cette fonction, ou faut il chercher ailleurs ?

Fichier joint pour détailler un peu cela (formule en D1-D5 explicite ma recherche)
Pour préciser, une même personne pourrait détenir plusieurs des 5 valeurs max (ce qui exclue une solution ou je trie d'abord le max de chaque personne puis ordonne ces valeurs).

Merci d'avance,

 

[Jocelyn]

Bonjour le Forum,
Bonjour THOMASD,

un essai en fichier joint par formule matricielle voir cellule J1:J5

Cordialement

EDIT : on peut aussi remplacer la formule a validation matricielle par celle-ci

=INDEX($B$7:$O$7;;SOMMEPROD(($B$8:$O$43=GRANDE.VALEUR($B$8:$O$43;LIGNES($1:1)))*COLONNE($B$7:$O$7))-1)

a mettre en J1 et à étirer jusqu'à j5

 

[THOMASD]

Bonjour, et merci, je suis bluffé par la réactivité de ce forum.
Je me penche sur ta formule pour en comprendre la logique et reviens vers toi si besoin !
Thomas

 

[Jocelyn]

re,

Pour le fun et pour le cas ou dans les 5 plus grandes valeurs tu aurais 2 (ici j'ai mis la même valeur à henri et gérard) valeurs identiques modification de la formule de

j1:j5 renvoie des prenons seulement

et nouvelle formule en

k1:k5 renvois des prenons et des valeurs associées

pour cela il faut une ligne vide au dessus elle peut etre masquée

cordialement

EDIT : bonjour mapomme eh eh le même type d'idée avec solution différente parfait pour le choix

 

[mapomme]

Bonjour à THOMASD, Jocelync ,

Une autre formule matricielle qui tient compte des ex-aequo le cas échéant.

edit: mille excuses, pas vu ton dernier message Jocelyn (pas réactualisé )

 

[Jocelyn]

re,

pas de soucis plus on est de fou plus on rit et plus il y a de solutions

en plus nous avons posté pratiquement en même temps

 

[THOMASD]

Vous anticipez les problèmes, bravo
Justement, le cas des ex-aequo s'est posé un peu avant pour moi. Les valeurs du tableau sont en fait initialement des entiers (score pour une série de pronostics pour tout vous dire) auxquels j'ai déjà affecté ajouté deux critères complémentaires (poids 1/100 et 1/1000) afin de pouvoir départager des ex-aequo.
Donc pas de souci à ce niveau en théorie !

Vous posez les formules plus vite que je les comprends en tout cas
Pas toujours aisé de "traduire" en français l'écriture de vos formules pour en comprendre la logique !!

 

[THOMASD]

(Re)Bonjour,

J'ai étudié (longuement..) vos propositions depuis hier, mais après les avoir décomposées dans tous les sens, j'ai toujours du mal à les comprendre.
Jocelyn, si tu repasses par là (ou une personne maîtrisant ce type de formules), je suis preneur d'explications complémentaires sur les solutions que tu m'as proposé (fichier joint, sans les ex_aequo pour ne pas compliquer)
C'est le 3ème terme de la fonction index ou tu appelles le numéro de colonne concerné par la n-ième grande valeur que j'ai du mal à saisir.
Notamment pourquoi :
1/ COLONNE($B$7:$O$7) renvoie bien la colonne que l'on cherche
2/ $B$8:$O$43=GRANDE.VALEUR($B$8:$O$43;LIGNES($1:1) si je comprends bien ce que l'on cherche ici (la 1ere plus grande valeur du tableau de donnée), je ne saisis pas l'écriture.

J'imagine que c'est pas le plus drôle (et le plus facile) d'expliciter cela mais on sait jamais !

 

[Jocelyn]

re,

A lors je vais essayer de t'expliquer

=INDEX($B$7:$O$7;;MAX(SI($B$8:$O$43=GRANDE.VALEUR($B$8:$O$43;LIGNES($1:1));COLONNE($B$1:$O$1)-1)))

ce que l'on veut c'est l'une des valeurs se trouvant en $B$7:$O$7

pour cela on va rechercher un numéro de colonne puisque l'on se promène sur la ligne 7 de la colonne B à la colonne O : COLONNE($B$1:$O$1)-1 ici on a un -1 pour parce que l'on travail sur les n° de colonne si la valeur a renvoyer était en colonne B le renvoie serais 2 mais cela renverrais la colonne puisque c'est elle qui ce trouve en 2 eme position dans $B$7:$O$7 en gros si la plage dans laquelle se trouve la valeur recherchée débute en colonne 2 on fera -1 si elle débute en colonne 5 on fera -4 etc ...

ensuite ou du moins avant il faut la déterminer cette colonne pour cela $B$8:$O$43=GRANDE.VALEUR($B$8:$O$43;LIGNES($1:1)); ici comme tu l'as compris on cherche la plus grande valeur du tableau, dans cette écriture la partie LIGNES($1:1) va permettre de dire 1 er grande valeur , 2 eme grande valeur etc... car la fonction LIGNES (avec un S) comte le nombre de ligne donc LIGNES($1:1)=1 étiré vers le bas cela devient LIGNES($1:2)=2 donc 2ème grande valeur Etc.

donc on prend le numéro max de colonne si celle contient la plus grande valeur 1 du tableau $B$8:$O$43 et nous somme obliger de la valider matriciellement a cause de MAX(SI())

bon alors comme je ne suis même pas sur de me comprendre moi même (c'est vraiment pas mon truc les long discours ) n'hésite pas si tu a besoin d'info complémentaire

Cordialement

EDIT : si tu veux des infos concernant la partie qui évite les doublons c'est pareil n'hésites pas

 

[THOMASD]

Merci tout d'abord pour ta patience,

Indépendamment, j'avais bien compris l'utilité de chaque terme, et les subtilités que tu as détaillé (le -1 sur les colonnes, la fonction LIGNES qui permet d'étirer les n-ième valeurs...).
La seule chose qui m'intriguait au départ était l’intérêt de la fonction MAX. A priori, la suite de la formule permet d'identifier 1 et 1 seule valeur, MAX n'a donc pour seul intérêt d’appeler cette valeur (on pourrait ainsi le remplacer par MIN ou MOYENNE par exemple).

Au final, ce que j'ai du mal à saisir, c'est le fonctionnement de la fonction SI et notamment :
COLONNE($B$1:$O$1) : Pour moi, c'est la "Valeur si vraie" de la fonction SI. Et ce terme n'est qu'une plage de réponses possibles, elle ne fait pas spécifiquement référence à la colonne ou l'on trouve la plus grande valeur (le même type d'interrogations se pose pour la version avec SOMMEPROD)
Je pense que mon erreur est de raisonner par rapport au fonctionnement d'une fonction SI classique, et non d'une matricielle MAX(SI qui a certainement ses spécificités.

Cdt,

 

[Jocelyn]

re,

Le max tu as bien compris ne sert qu'a renvoyer le 1 ou autre chose et effectivement ce pourrait être min grande.valeur, petite..valeur qui sont des valeurs entières (pas une moyenne)

En fin de compte COLONNE($B$1:$O$1) donne une série de valeur que l'on peut imaginer être {2;3;4;5;6;7;8;9;10;11;12;13;14} de la excel en matricielle va déterminer dans laquelle des cellules ce trouve la grande.valeur 1 par le si $B$8:$O$43=GRANDE.VALEUR($B$8:$O$43;LIGNES($1:1)) excel va marquer la cellule de la grande.valeur et de la si la cellule est (C31) va renvoyer le numéro de la colonne ({2;3;4;5;6;7;8;9;10;11;12;13;14}) qui la contient donc 3 (avec évidement le -1 dont tu as compris le fonctionnement)

dans la formule sommeprod qui soit dit en passant est une formule matricielle qui n'a pas besoin de validation particulière $B$8:$O$43=GRANDE.VALEUR($B$8:$O$43;LIGNES($1:1)) la formule va marquer la ou les colonnes contenant la grande valeur 1 et faire l'addition du numéro de la ou des colonnes

j'avais proposer le sommeprod car tu n'avais de doublons dans tes 5 plus grandes valeur par contre quand j'ai créé des doublons j'ai fait disparaitre le sommeprod car si une grande valeur dans la colonne portant 5 et la même grande valeur dans la colonne portant 10 résultat du sommeprod = 15

je trouve génial de tomber sur quelqu'un qui cherche a comprendre le fonctionnement des propositions pour vraiment se les approprier et pas seulement les consommer comme dans un self alors merci a toi pour cela (même je suis toujours ennuyer pour faire des kilomettres de texte)

voila n'hésites toujours pas si quelque chose d'échappe encore

Cordialement

 

[THOMASD]

Tes explications et quelques lectures complémentaires sur le support ont bien éclaircis mon pb.
Merci pour tout !