Formule moyenne de valeurs maximales

[EmilieKBH]

Bonjour a tous,

J'aimerais creer une formule qui me permette un calcul automatique de ma valeur maximale, de la moyenne des 2 valeurs maximales et de la moyenne des 5 valeurs maximales.
Je ne sais pas si c'est tres clair donc un exemple:
valeurs
1
2
3
4
5
6
5
4
3
2

valeur maximale 6
moyenne des valeurs du top 2 =(5+6)/2
moyenne des valeurs du top 5 = (6+5+5+4+4)/5

Je peux le faire manuellement en cherchant une a une les valeurs les plus grandes et ensuite en calculant leur moyenne mais j'ai un nombre important de donnees a gerer. C'est pourquoi je voudrais appliquer une formule.

Merci beaucoup de votre aide,

Emilie

 

[vgendron]

Re : Formule moyenne de valeurs maximales

Bonjour,
utilise la fonction Grande.valeur qui te retourne la k ieme plus grand valeur d'une range (celle de tes data)
voir PJ

 

[Victor21]

Re : Formule moyenne de valeurs maximales

Bonjour EmilieKBH.

Utilisez les fonctions natives d'Excel Max() et equiv(), index() rang().
Utilisez également les fonctionnalités offertes par XLD pour joindre un court exemple de votre fichier si vous désirez des réponses plus précises. Pour rappel :

- ILLUSTREZ VOTRE DEMANDE : Afin de faciliter la compréhension de votre demande il est conseillé de joindre un PETIT fichier qui illustre votre question précise et montre les résultats que vous cherchez à obtenir.
Pour l'envoyer, cliquez sur le trombone ci-dessous ou sur le bouton "Gérer les pièces jointes" dans la zone "Options supplémentaires".Ensuite, cliquez sur le bouton "Parcourir" et sélectionnez-le (après l'avoir compressé s'il dépasse les 250Ko).

- DONNEES CONFIDENTIELLES : Ne laissez AUCUNE donnée personnelle ou confidentielle (noms, adresses, N° de téléphone, entreprises...) dans vos pièces jointes. Vous mettriez Excel-Downloads en contravention avec la loi Informatique et Liberté et votre fichier devrait être enlevé.

 

[Victor21]

Re : Formule moyenne de valeurs maximales

Re,

Comme c'est votre premier post, et en cadeau de bienvenue :

Bonjour, vgendron

 

[R@chid]

Re : Formule moyenne de valeurs maximales

Bonjour et Bienvenue sur XLD,
Salut les amis,
une formule classique :

=MOYENNE(GRANDE.VALEUR(A1:A10;{1;2;3;4;5}))

@ + +

 

[EmilieKBH]

Re : Formule moyenne de valeurs maximales

Un enorme merci pour votre aide ! Vous avez exactement repondu a ma question.
Je me suis trompee en redigeant le mail, je voulais les 2 (ou 5) plus petites valeurs et non les plus grandes. Mais j'ai compris les formules et ai pu les modifier pour analyser mes data.

Merci merci !

Bonne journee

 

[EmilieKBH]

Re : Formule moyenne de valeurs maximales

Bonjour tout le monde !

J'aimerais maintenant aller plus loin dans le traitement de mes donnees. J'aimerais avoir la moyenne de mes 5 plus petites valeurs ET être sûre que ces valeurs appartiennent a des groupes differents.
Pour illustrer, voici mon fichier.
- la colonne mean 5 correspond a la moyenne des 5 plus petites valeurs de la colonne B
- comme la couleur le montre, mes valeurs correspondent a des cycles
- j'aimerais m'assurer que les 5 valeurs choisies sont chacunes des valeurs minimales de chaque cycle (5 valeurs pour 5 cycles et non 5 valeurs parmi 2 cycles par exemple).

J'espere avoir ete claire. Si ce n'est pas le cas faites le moi savoir !

Merci pour votre aide precieuse,

Emilie

 

[job75]

Re : Formule moyenne de valeurs maximales

Bonjour Emilie, vgendron, Patrick, R@chid,

Avec des minima bien marqués pas de problème, formule matricielle :

=MOYENNE(PETITE.VALEUR(SI((B2:B148>B3:B149)*(B3:B149<B4:B150);B3:B149);{1.2.3.4.5}))

A valider par Ctrl+Maj+Entrée.

Fichier joint, j'ai coloré en jaune les cellules concernées pour vérification.

A+

 

[EmilieKBH]

Re : Formule moyenne de valeurs maximales

Merci de votre reponse.
Malheureusement je ne comprends pas la formule. A quoi correspond la partie ici en rouge ?
=AVERAGE(SMALL(IF((B2:B148>B3:B149)*(B3:B149<B4:B150);B3:B149);{1\2\3\4\5}))
Et la partie en vert est celle permettant la "sepapration" des 5 valeurs ? Dans ce cas est-il possible que j'integre cette derniere partie a ma formule (=SUMIF(C:C; "<6";B:B )/COUNTIF(C:C;"<6")) ?

Cordialement,

Emilie

 

[job75]

Re : Formule moyenne de valeurs maximales

Re,

Décortiquons donc, le coeur de la formule se compose de SMALL(X;Y)

X est un vecteur colonne (de la hauteur de B3:B149) dans lequel se trouvent tous les minima.

Un minimum étant caractérisé par le fait que la valeur précédente et la valeur suivante lui sont supérieures.

Y est un vecteur ligne de 5 éléments pour déterminer les 5 plus petits minima.

A+

 

[job75]

Re : Formule moyenne de valeurs maximales

Re,

En fait au lieu du vecteur ligne on peut utiliser le vecteur colonne {1;2;3;4;5} sans problème.

A+

 

[job75]

Re : Formule moyenne de valeurs maximales

Re,

Quant à votre fonction SUMIF c'est simple, elle ne peut pas isoler les minima dans tous les cas de figure.

A+

 

[EmilieKBH]

Re : Formule moyenne de valeurs maximales

J'ai compris. Merci pour votre aide.

Emilie