95 percentiles avec restriction

[amazigh42]

Bonjour,
J'ai la formule suivante :

Code : Tout sélectionner

=CENTILE(E3:E82;0,95)

Je n'arrive pas à transformer cette formule avec un SI pour qu'elle ne prenne pas en compte les cellules vides et nulles.

Merci de votre aide

[Jean-Louis Cadeillan]

Bonjour,
Une solution :

Code : Tout sélectionner

=CENTILE(INDEX(E1:E82;PETITE.VALEUR(SI((E3:E82<>"")*(E3:E82<>0);LIGNE(E3:E82);"");LIGNE(INDIRECT("A1:A"&NB(E3:E82)-NB.SI(E3:E82;0)))));0,95)

À valider (formule matricielle) par Ctrl+Maj+Entrée.
Cordialement,
Jean-Louis

[amazigh42]

Merci de votre aide

J'ai une erreur 504

[Jean-Louis Cadeillan]

J'ai une erreur 504

C'est l'erreur qu'on obtient quand on n'a pas validé par Ctrl+Maj+Entrée. Tu dois avoir des accolades { et } entourant ta formule dans la barre de formule.
Remarque : si tu as validé la première fois par Entrée, et qu'ensuite tu as édité la cellule pour revalider par Ctrl+Maj+Entrée, il ne va rien se passer, tu auras toujours l'erreur 504 : en mode édition, il faut au moins changer un caractère (par exemple, tu supprimes le signe = et tu le ressaisis), puis tu valides par Ctrl+Maj+Entrée.

J'ai sorti du placard une vieille version se rapprochant de la tienne (la 6.2.2.2), ça fonctionne :

amazigh42_20210503_JLC01.jpg

Cordialement,
Jean-Louis

[amazigh42]

J'ai bien fait Ctrl + Alt + Suppr
Cependant dans ta formule je ne comprends pas le motif en rouge ? Pourquoi cette référence à la cellule A ?
Il me semble aussi qu'il y a une erreur au début E1 au lieu de E3
=CENTILE(INDEX(E1:E82;PETITE.VALEUR(SI((E3:E82<>"")*(E3:E82<>0);LIGNE(E3:E82);"");LIGNE(INDIRECT("A1:A"&NB(E3:E82)-NB.SI(E3:E82;0)))));0,95)

[Jean-Louis Cadeillan]

je ne comprends pas le motif en rouge ?

A1:A fait partie du deuxième argument de PETITE.VALEUR(). Ce deuxième argument va donner 1, 2, 3, etc. … 1 étant la plus petite valeur (le minimum), 2, la deuxième plus petite valeur, etc… le problème est d'aller jusqu'à la nième plus petite valeur, n étant égal au nombre de valeurs de la plage E3:E82 qui sont différentes du vide ou de 0, donc on ne connaît pas n a priori, il va falloir le calculer.
n = nombre de valeurs différentes du vide de la plage E3-E82 - nombre de valeurs nulles
n = NB (E3:E82) - NB.SI(E3:E82;0)
Donc je sais que je dois donner toutes les « petites valeurs » de A1:An, ce qui en texte donne "A1:A" &n
Mais l'interpréteur de Calc ne peut comprendre ce texte que si on lui dit que c'est une référence de plage… d'où l'usage de INDIRECT().
Ne pas oublier que PETITE.VALEUR() va classer les valeurs retournées pas SI(), SI() étant le premier argument de PETITE.VALEUR(). SI() retourne les n° de ligne (c'est la fonction LIGNE()) de la plage E3:E82, à condition que la cellule en cours ne contienne ni 0 ni rien (dans ces deux cas, SI() retourne une cellule vide). Or par chance, les cellules vides sont classées après les cellules non vides par PETITE.VALEUR().

Il me semble aussi qu'il y a une erreur au début E1 au lieu de E3

Le but de la fonction INDEX() utilisée en matriciel est de retourner le vecteur qui contient les valeurs non nulles et non vides de la plage E3:E82. Son second argument est un ensemble de n° de lignes de la plage E3:E82. Malheureusement , INDEX () attend comme second argument l'ensemble des rangs de sa plage de référence (plage qui constitue son premier argument) et non pas des n° de ligne en absolu : pour que le rang et le n° de ligne veuillent dirent la même chose, il faut faire commencer la plage de référence à la première ligne … d'où E1:E82 comme plage de référence.
Une autre manière de faire aurait été de décaler le second argument d'INDEX() de 2 :

Code : Tout sélectionner

INDEX(E3:E82;PETITE.VALEUR(…) - 2)

Ce qui donne la même résultat.

Cordialement,
Jean-Louis

[amazigh42]

Je te remercie de ton aide.
Pourquoi est-ce aussi compliqué, sincèrement c'est décourageant.
J'ai l'impression de prendre un canon pour tuer une mouche.

[Jean-Louis Cadeillan]

C'est une question de ressenti et de familiarité avec des outils.
Si tu ne veux pas t'embêter, tu sélectionnes la plage E3-E82, tu opères un tri décroissant (icône en forme de ↑), les cellules nulles et vides se trouveront en bas, puis tu saisis ta fonction CENTILE() et définissant ta plage par un cliqué/glissé de toutes les cellules au-dessus.
Cordialement,
Jean-Louis