récupérer les 10 noms correspondant au 10 plus grandes valeurs sur plusieurs feuilles

[bérylion]

Salut la foule

la question n'étant pas claire est difficile à espliker, il faut regarder le classeur ci-joint.

je cherche la formule kivabien pour renseigner la plage en jaune (c116:c125) sur la feuil7

Je sais le faire en matricielle pour une plage sur une seule feuille, mais pas dans ce cas la (bien que la plage reste à 2 dim)

Si vous êtes joueurs, lachez-vous !!

A vot' bon coeur...

 

[repcheks]

Re : récupérer les 10 noms correspondant au 10 plus grandes valeurs sur plusieurs feu

Un petit essai en piece jointe, j'ai mis les formules dans le colonnes juste a coté pour que tu puisses comparer les resultats obtenus/souhaités.

Le probleme c'est que si ton nombre de feuilles est variable tu devras editer la formule. Perso je ferais plutot ca en VBA pour avoir quelque chose de perenne si le nombre de feuilles devait etre indefini.

 

[BOISGONTIER]

Re : récupérer les 10 noms correspondant au 10 plus grandes valeurs sur plusieurs feu

Bonjour,

voir pj

=INDEX(INDIRECT("feuil"&EQUIV(VRAI;(NB.SI(INDIRECT("Feuil"&LIGNE($1:$6)&"!G2:G1000");G116)>0);0)&"!c2:c1000");EQUIV(G116;INDIRECT("feuil"&EQUIV(VRAI;(NB.SI(INDIRECT("Feuil"&LIGNE($1:$6)&"!G2:G1000");G116)>0);0)&"!g2:g1000");0))
Valider avec maj+ctrl+entrée


Autre version avec noms de feuilles non génériques et nombre de feuilles variable

Nom de champ NF: =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

=INDEX(INDIRECT(INDEX(nf;EQUIV(VRAI;(NB.SI(INDIRECT(nf&"!G2:G1000");D116)>0);0))&"!c2:c1000");EQUIV(D116;INDIRECT(INDEX(nf;EQUIV(VRAI;(NB.SI(INDIRECT(nf&"!G2:G1000");D116)>0);0))&"!g2:g1000");0))
Valider avec maj+ctl+entrée



jb

 

[bérylion]

Re : récupérer les 10 noms correspondant au 10 plus grandes valeurs sur plusieurs feu

Bonjour,

voir pj

Autre version avec noms de feuilles non génériques et nombre de feuilles variable

Nom de champ NF: =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")

=INDEX(INDIRECT(INDEX(nf;EQUIV(VRAI;(NB.SI(INDIRECT(nf&"!G2:G1000");D116)>0);0))&"!c2:c1000");EQUIV(D116;INDIRECT(INDEX(nf;EQUIV(VRAI;(NB.SI(INDIRECT(nf&"!G2:G1000");D116)>0);0))&"!g2:g1000");0))
Valider avec maj+ctl+entrée



jb

Bonjour, et pour commencer MERCI

cette solution aurait été parfaite, si je n'avais point omis les 2 détaux qui tuent :

1: le classeur contient d'autres feuilles qui n'ont pas à entrer dans ce calcul
dont la 1ere qui fait la bagatelle de 175.000 lignes sur 25 colonnes (temps de calcul 00:57:43 !!!)
2: la plage G116:G125 de la feuille concernée doit impérativement contenir les 10 plus grandes valeurs des plages G116:G125 des autres feuilles, à cause d'un affichage dynamique sur la 2eme feuille.

finalement, puisque les plages en question sont fixes ainsi que le nbre et l'ordre des feuilles, je me suis rabattu sur la vieille méthode de ramener les données sur la feuille voulue et d'en extraire les noms correspondants aux valeurs
(dis comme ça c'est pas clair, je joins donc un exemple de ce que ça donne)

encore merci pour ces contributions, et longue vie à XLD !!

 

[BOISGONTIER]

Re : récupérer les 10 noms correspondant au 10 plus grandes valeurs sur plusieurs feu

Bonjour,

NF peut être un champ qui contient les noms des feuilles concernées seulement.

JB

 

[hbenalia]

Re : récupérer les 10 noms correspondant au 10 plus grandes valeurs sur plusieurs feu

Bonjour à tous,

Avec quelques changements sur les feuilles (ajout de H116:H125 contenant le même résultat que C116:H125 dans chaque feuille), les plages $G$116:$H$125 de toutes les feuilles seront nommées respectivement: Plage1, Plage2, Plage3, ...
Et en utilisant une fonction personalisée (créée par un ami très cher) dont le code est:

Function RechercheP(ParamArray My_Arg()) As Variant
Dim Max As Integer
Dim Index As Integer
Dim FindOK As Boolean
Dim MyFind As String
Dim CIndex As Integer
MyFind = My_Arg(0)
CIndex = My_Arg(1)
Max = UBound(My_Arg)
FindOK = False
For Index = 2 To Max
If FindOK Then Exit For
    RechercheP = My_Arg(Index).Name
    R = Range(RechercheP).Rows.Count
    C = Range(RechercheP).Columns.Count
    If CIndex > C Then RechercheP = "#REF!": Exit Function
        For MyR = 1 To R
        If LCase(Range(RechercheP)(MyR, 1)) = LCase(MyFind) Then
            RechercheP = Range(RechercheP)(MyR, CIndex)
            Exit Function
        End If
        Next MyR
Next Index
RechercheP = "#N/A"
End Function

qu'on utilise de la façon suivante:

=RechercheP(Valeur_cherchée;no_index_col;Plage1;Plage2;Plage3;...)

Avec:
valeur_cherchée est la valeur à trouver dans la première colonne de chaque Plage. Elle peut être une valeur, une référence ou une chaîne de texte.
no_index_col est le numéro de la colonne des arguments (Plage1, Plage2, Plage3...) dont la valeur correspondante doit être renvoyée.
Plage1, Plage2, Plage3... sont les tables de données dans lesquelles est exécutée la recherche de la valeur. Utilisez impérativement des noms de plages et non des références comme $G$116:$H$125.



Et en application voir fichier en pièce jointe...

Cordialement

 

[bérylion]

Re : récupérer les 10 noms correspondant au 10 plus grandes valeurs sur plusieurs feu

Bonjour,

NF peut être un champ qui contient les noms des feuilles concernées seulement.

JB

re

oui, j'ai potassé la dessus :
Formules Matricielles 3D
(un grand bravo en passant !!!)

mais finalement je m'en suis sorti par un détour comme expliqué plus haut, et je m'en contente pour l'instant.
Si le nbre ou les noms de feuilles sont aménés à varier alors je reconsidèrerai la question...

 

[bérylion]

Re : récupérer les 10 noms correspondant au 10 plus grandes valeurs sur plusieurs feu

bonjour,

merci pour la contrib. mais c'est pas utilisable dans mon cas :
la plage H116:H125 est déja occupée par d'autres données (je pourrais les déplacer, il est vrai...)
de plus, les function perso vont perturber (de la même manière que l'utilisation des macro XL4 faite par môssieu JB) les personnes qui voudront consulter la 2eme feuille sur laquelle se font les reports dynamiques qui alimentent les graphs

en tout cas, joli bout de code !!