Moyenne.si.ens() excluant les + et - 10 % extrèmes

Question

Bonjour à toutes et tous,

Avec un coucou particulier à Daniel que je vois toujours présent malgré les années qui passent.

J'ai des datas avec des valeurs variant suivant des paliers. Et a priori que 2 colonnes.

Je dois mettre en rouge et vert les +/- 10% supérieurs et inférieurs pour chaque palier, là c'est ok. 

Ceci dit, je l'ai fait palier par palier, ne voyant pas trop comment faire autrement.

Mais cela se corse quand je dois faire une moyenne par palier excluant ces dites valeurs.

Ce classeur "essai" possède 2 feuilles :

• Une "Datas" donnée avec 2 colonnes, j'ai ajouté la 3ème
• Une "Synthèse" qui doit recevoir ces moyennes (j'y ai déjà mis max, min ...).

Le fichier

Donc, si vous pouvez m'éclairer; merci d'avance

Bàv,

Answer 1

Avec un coucou particulier à Daniel que je vois toujours présent malgré les années qui passent.

Bonjour,

Effectivement, je ne vois pas le temps passer...

https://www.dropbox.com/s/ac925pypgwt16wi/essai.xlsx?dl=0

Je ne suis pas sûr que ce soit ce que tu veuilles :

J'ai mis le résultat en G2 pour la plage A2:A21. Je suis passé par une colonne de calcul intermédiaire en E2:E21. Enfin, il peut y avoir une différente sur les valeurs retenues (par exemple A3, Pris dans mon calcul, mais pas par la MFC. Ca vient de l'utilisation des opérateurs ">=" et "<=" au lieu de ">" et "<".

Cordialement.

Daniel

Answer 2

Voilà voilà,

Bonsoir Daniel,

https://www.dropbox.com/s/h1shryib8lhdawi/essai.xlsx?dl=0

Sur la feuille Syntèse :

Les Valeurs correspondent à Datas!A2:A101

Les Paliers correspondent à Datas!C2:C101

j'ai passé en revue les fonctions qu'offre Excel  en + de MOYENNE.SI.ENS()

=MOYENNE.SI.ENS(Valeurs;Paliers;C3)

J'ai retenu les fonctions GRANDE.VALEUR() et PETITE.VALEUR()qui peuvent servir de critères supplémentaires et adéquats dans mon cas.

=MOYENNE.SI.ENS(Valeurs;Paliers;C3;Valeurs;"<" & GRANDE.VALEUR(Paliers;2); ;Valeurs;">" & PETITE.VALEUR(Paliers;2))

Mais si dans mon exemple joint, je peux utiliser "2" pour le rang parce que les paliers ont tous 20 valeurs (2 = 10% de 20), ce n'est pas le cas dans la réalité. 

J'avais alors 2 solutions : soit calculer palier par palier, soit trouver une solution universelle. Mais pour cela, il me fallait une méthode pour retourner une plage de données pour chaque palier, et je suis tombé sur une toute nouvelle fonction de cette année 2020 : FILTRE

J'y ajoute

ENT(NB.SI.ENS(Paliers;C3)*0,1)

pour le k du Filtre, et le tour est joué !

La formule devient donc :

=MOYENNE.SI.ENS(Valeurs;Paliers;C3;Valeurs;"<" & GRANDE.VALEUR( FILTRE(Valeurs;Paliers=C3);ENT(NB.SI.ENS(Paliers;C3)*0,1));Valeurs;">" & PETITE.VALEUR( FILTRE(Valeurs;Paliers=C3);ENT(NB.SI.ENS(Paliers;C3)*0,1)))

Merci à toutes et tous !

Answer 3

Merci Daniel, je vais regarder cela.

Bon WE !