XL 2013 Fonction Grande.Valeur dynamique

[Brain Box]

Bonjour à tous !

Je m'excuse de venir déranger la communauté, vous êtes tous surement très occupés à préparer vos cadeaux de Noël ou à penser aux repas à venir pour les fêtes Si je viens vous demander un peu d'aide, c'est à propos d'une formule un peu récalcitrante !

Pour faire simple, je souhaite pouvoir calculer une moyenne sur les X% des plus grandes valeurs d'une liste de nombres, et je n'arrive pas à faire en sorte que ma formule s'adapte lorsque je rajoute de nouvelles données dans mon fichier...

Même si je joins un exemple en pièce jointe, voici la formule que j'utilise actuellement (colonne SS à TB dans le fichier !) :
"{=MOYENNE(GRANDE.VALEUR(C4:SR4;{1;2;3;4;5;6;7;8;9;10}))}"

J'obtiens donc une moyenne des 10 plus grandes valeurs. Comment faire si ne veux plus uniquement les 10 plus hautes, mais les 10% les plus hautes (moyenne qui dépendra donc du nombre de colonnes...).
J'ai bien vu qu'au lieu d'utiliser "{1;2;3;4;5;6;7;8;9;10}" on peux utiliser la fonction LIGNE (ou même encore COLONNE visiblement), mais il semble que l'on ne puisse pas mettre une autre fonction comme argument (ou alors je n'ai pas trouvé...).

J'ai visité d'autres topics comme celui-ci (https://www.excel-downloads.com/threads/extraire-le-top-10-par-formule.20013088/#post-20098392), mais sans réellement trouver mon bonheur...

Par ailleurs, vous verrez que pour les lignes 17 à 22 la formule se complexifie pour ne tenir compte que des colonnes paires ou impaires selon le cas, si jamais quelqu'un a plus simple je suis preneur

Merci à tous, et bonnes fêtes pour ceux qui n'auront que le temps de lire et pas de répondre !

Maxence

 

[JHA]

Bonjour à tous,

A essayer et à vérifier

=MOYENNE(GRANDE.VALEUR($C4:SR4;LIGNE(INDIRECT("1:"&ARRONDI(NBVAL($C4:$SR4)*10%;0)))))

JHA

 

[Brain Box]

Merci beaucoup pour cette réponse (très) rapide ! A priori cette formule fait des miracles, pour vérifier j'ai mis le cursus à 50% des plus hautes valeurs, 50% des plus basses... et la moyenne des deux revient bien à la moyenne globale calculée par ailleurs, donc tout va bien !

Un seul ennui toutefois, c'est que je ne sais pas comment l'appliquer pour ne sélectionner que les colonnes paires d'un côté et impaires de l'autre... (lignes 21 et 22 de mon exemple) Des idées sur la marche à suivre ?

Merci beaucoup en tout cas !

Maxence

 

[JHA]

Bonjour à tous,

A essayer,

JHA

 

[JHA]

Bonjour à tous,

Celle ci est plus juste mais à vérifier

JHA

 

[Brain Box]

Bonjour JHA,

Merci pour votre réponse rapide ! (Je n'ai pas pu jeter un oeil sur le fichier ce week-end je suis désolé)

Je regarde ça dans la journée, mais il n'y a pas de raisons que ça ne fonctionne pas ! Une question toute bête néanmoins : comment sont traitées les cellules vides ? Sont-elles ignorées ? Comptabilisées comme un 0 à chaque fois ?

Merci d'avance pour votre retour !

Maxence

 

[JHA]

Bonjour à tous,

pour la 2ème colonne la formule est:

=SIERREUR(MOYENNE(GRANDE.VALEUR(SI((MOD(COLONNE($C21:SR21);2)=0)*($C21:SR21>0);$C21:SR21);LIGNE(INDIRECT("1:"&ARRONDI(NB(SI(MOD(COLONNE($C21:$SR21);2)=0;$C21:$SR21))*10%;0)))));"")

Donc :
MOD(COLONNE($C21:SR21);2)=0 ne gère que les colonnes "D, F, H," etc..
$C21:SR21>0 on ne prend en compte que les cellules >0

JHA

 

[Brain Box]

JHA,

Merci beaucoup, je viens de regarder ça fonctionne du tonnerre ! N'étant pas un expert des formules matricielles, je ne pense pas retoucher la formule, néanmoins pour ma culture Excel j'aimerais comprendre un peu mieux ce qui se situe après la fonction INDIRECT : il y a un compte du nombre de colonnes (1 sur 2) grâce à la fonction MOD ? Si au lieu de mettre 10% je souhaite mettre 30% ou 50%, est-ce possible ? Y a t-il une limite ?

Je m'excuse pour ces questions qui vous paraîtront peut être naïves, mais comme dit plus haut les fonctions matricielles ne sont pas mon fort !

Merci d'avance,

Maxence

 

[mutzik]

bonjour, Salut JHA

en sachant que tu ne peux rien 'casser', essayer est aussi un bon moyen pour apprendre
remplace ton 10% par 30, vérifie les calculs ...

 

[Brain Box]

Bonsoir à vous deux,

Merci pour ces conseils, je sais bien entendu que je ne risque pas de casser quoique ce soit (depuis que cela m'ait arrivé sur un fichier professionnel je fais des copies en permanence !!), mais l'idée était d'abord de comprendre comment la formule fonctionne, car même avec l'aide Microsoft je ne suis toujours pas au clair avec cette fonction INDIRECT.

Concernant la formule, j'ai remarqué que lorsque que je souhaite monter le curseur (50% par exemple) et qu'il n'y a pas assez de colonnes remplies la cellule affiche du vide ("") : il doit donc bien y avoir un nombre de valeurs minimal à respecter en fonction du % que l'on souhaite afficher, sinon il y a une erreur (et notre fameux "").

En tout cas je vous remercie tous pour votre contribution sur ce sujet, cela m'a sorti une belle épine du pied !

Cordialement,

Maxence