Comment exclure les valeurs "0" pour un calcul de centile

Excel
#1

Bonjour,

Je suis actuellement à la recherche de réponses. Je travaille sur une analyse de données où je dois calculer les 99ème et 99,7ème percentile en excluant les valeurs nulles.

Ma formule est assez simple pour le moment, mais elle n’exclue pas les valeurs nulles:
=CENTILE(E67:QG67;0,99)

Je sais que pour exclure les valeurs “0” pour calculer la moyenne par exemple, il suffit de rajouter “>0”, ce que j’ai déjà essayer sans aboutissement.

Avez-vous une solution pour exclure les valeurs “0” ? Sachant que c’est une feuille de calcul que je dois optimiser pour qu’on puisse la réutiliser facilement (je ne peux juste pas effacer tous les “0”, sachant qu’il y en a des centaines, pour que le calcul ne les prennent pas en compte).

Merci d’avance.
Océane.

#2

Hello,
Peux tu voir si la nouvelle fonction CENTILE.EXCLURE te convient ?(Et oui, on ne peut pas deviner la version Excel que tu utilises, il se peut que cette fonction soit absente chez toi :neutral_face:)
Sinon, J’ai une idée pour la plage, mais pour vérifier cela peux-tu nous mettre un échantillons de données exemple dans un fichier ?

#3

Bonjour,

J’utilise la version d’Excel 2010. J’ai essayer avec la fonction CENTILE.EXCLURE mais la valeur exclue est celle du centile, pas les valeurs de la matrice… En gros il ne peut pas être égale à 0 ou 1.

Echantillon de données.xlsx (10,2 Ko)
J’ai joint un échantillon de données sur lequel il y a déjà les percentiles de calculer:
95ème percentile = 118153
97,5ème percentile = 157605
99ème percentile = 210311
99,7ème percentile =447480
Pour vérifier le calcul.

Merci d’avance

#4

Je te propose une formule matricielle qui éliminera les 0, ce qui devrait régler le soucis.
En gros c’est l’équivalent de remplacer les 0 par vide mais dans la formule.
En utilisant la fonction SI (sur toute la matrice), la fonction matricielle traite cellule avant le calcul du centile.
Une fonction matricielle s’ecris comme une fonction classique mais doit être validée par les touche : CTRL+Shift (enfoncée)+ENTREE. (ce qui produit les accolades dans la barre, les mettre a la mainne fonctionnera pas).
Comme tu vois dans l’image le traitement des 0 avant l’evaluation du centile.

soluce
soluce.png877×270 17.8 KB

Fichier exemple: (j’ai réduit l’échantillon pour le coté didactique de l’image)
Echantillon de données.xlsx (10,7 Ko)

#5

Merci, ça m’a l’air de fonctionner !
Vous m’avez fait gagné beaucoup de temps.

#6

De rien, content que ça te fasse avancer. Oublie pas de marquer cocher la réponse qui a solutionner ton problème en réponse,ça aidera d’autres personnes.
Bon week-end

1 J'aime
#7

Bonjour,

J’ai un autre problème, je veux faire la même chose mais en calculant le percentile sur plusieurs lignes qui ne se suivent pas.

Voici ma formule : =CENTILE(SI((E62:IS62;E153:IS153)=0;"";(E62:IS62;E153:IS153));0,99)

En entrant la fonction matricielle ça me met " #VALEUR! "
J’ai regardé si je n’avais pas bien placer les parenthèse ou autre, mais ca ne marche toujours pas.

Merci d’avance.

#8

Hello oceane;
Ton problème de syntaxe viens du fait que tu essais de poser un test unique sur une plage discontinue.
soluce1

La forme suivante aurai marché =SI(E62:IS62=0;"";E62:IS62);SI(E62:IS62=0;"";E153:IS153)
Mais la fonction CENTILE accepte uniquement une plage de cellules contiguës.
Elle ne peux pas traiter plusieurs matrices différentes.
Il manque dans Excel l’opérateur UNION pour joindre 2 plage sans macro.

Essai de voir si tu peux adapter ton modèle.

#9

D’accord, merci pour cette réponse.