Qu'est-ce que la fonction CENTILE.
Tu sors CENTILE.EXCLURE (PERCENTILE.EXC en anglais) quand ton échantillon n'est qu'un extrait d'une population plus large et que tu veux un seuil qui tienne, pas une borne qui bouge dès que la prochaine mesure vient s'ajouter au tableau. C'est le réflexe à avoir dès qu'une valeur extrême isolée ne doit pas, à elle seule, fixer la limite retenue.
Concrètement, c'est elle qui définit le seuil du top 10 % de ton équipe commerciale sans être biaisé par une performance exceptionnelle isolée, qui construit des grilles salariales interquartiles pour les recrutements, qui fixe des limites de contrôle qualité basées sur la distribution réelle de production, ou qui établit des SLA de temps de réponse fondés sur des données terrain plutôt que sur des promesses.
Syntaxe
Clique sur un argument pour aller à son explication.
Pour n valeurs dans la matrice, k doit rester entre 1/(n+1) et n/(n+1), bornes comprises. Avec 9 valeurs, k va donc de 0,1 à 0,9 inclus : k=0,1 renvoie la plus petite valeur et k=0,9 la plus grande. Toute valeur hors de cet intervalle provoque l'erreur #NOMBRE!.
Comprendre chaque paramètre de la fonction CENTILE.EXCLURE
Deux arguments, tous les deux obligatoires : d'abord matrice, la plage de tes données, puis k, le percentile que tu vises. Le piège est dans le second : k s'écrit en décimal entre 0 et 1, donc 0,9 pour le 90e percentile, jamais 90. Et contrairement à CENTILE.INCLURE, les deux bornes exactes (0 et 1) te sont interdites ici.
matrice
: la plage de cellules contenant tes données numériquesElle peut prendre la forme d'une colonne (A1:A100), d'une ligne (B5:Z5) ou même d'une matrice 2D (A1:D10). Excel prend toutes les valeurs numériques et ignore automatiquement les cellules vides ou contenant du texte.
La matrice doit contenir au moins deux valeurs pour que la fonction puisse calculer un percentile. Plus tu as de valeurs, plus le résultat sera statistiquement significatif.
k
: le percentile que tu veux calculer, exprimé en décimal strictement entre 0 et 1Par exemple : 0,25 pour le 1er quartile, 0,5 pour la médiane, ou 0,9 pour le 90e percentile.
Avec CENTILE.EXCLURE, k ne peut pas être exactement 0 ou 1, et doit rester dans l'intervalle 1/(n+1) à n/(n+1) selon le nombre n de valeurs dans ta matrice. Ces deux bornes-là sont acceptées : avec 9 valeurs, k=0,1 et k=0,9 fonctionnent, alors que 0,09 et 0,91 renvoient #NOMBRE!. C'est le nombre de données qui limite les percentiles atteignables, pas une interdiction de toucher aux bornes.
Attention : Si tu écris =CENTILE.EXCLURE(A1:A10; 90) au lieu de 0,9, tu obtiens #NOMBRE! car 90 est hors de l'intervalle [0 ; 1]. Pense toujours en décimales, ou utilise le symbole % directement : 90% est automatiquement converti en 0,9.
Exemples pratiques pas à pas
Comment calculer le seuil du top 10 % avec la fonction CENTILE.EXCLURE
La grille de primes du trimestre se cale sur le top 10 % de la force de vente, et le seuil chiffré doit être annoncé à la réunion de lundi. Tes dix-huit vendeurs et leur chiffre d'affaires du mois sont dans le tableau, et retenir simplement le meilleur score reviendrait à récompenser un coup de chance plutôt qu'un niveau tenable. Le 90e centile répond à la vraie question : à partir de quel chiffre d'affaires entre-t-on dans les 10 % du haut. La fonction CENTILE.EXCLURE sort ce seuil de la colonne des ventes, en refusant qu'il tombe pile sur le plus petit ou le plus gros montant du tableau.
- 1Dans une cellule, écris
=CENTILE.EXCLURE(. - 2En 1er argument : sélectionne la colonne des chiffres d'affaires à analyser,
B2:B19. Ces dix-huit montants sont les seuls chiffres qui entrent dans le calcul, et les colonnes voisines comme Région ou Équipe restent en dehors de la sélection. L'en-tête de la ligne 1 en est exclu lui aussi, puisque la plage démarre en ligne 2. - 3En 2ᵉ argument : saisis le centile recherché en décimal,
0,9. Ce nombre vaut 90 %, et un90tapé à sa place sortirait de l'intervalle autorisé. Avec ces dix-huit valeurs, le niveau demandé doit rester entre1/19et18/19, soit à peu près entre0,053et0,947, et0,9s'y trouve sans difficulté. - 4Ferme la parenthèse et appuie sur Entrée.
=CENTILE.EXCLURE(B2:B19; 0,9)0,9 × 19 = 17,1, qui tombe entre le 17e (58 000 €) et le 18e (62 000 €). Elle s'arrête à un dixième de l'écart qui les sépare et renvoie 58 400 €, un montant qu'aucun vendeur n'atteint exactement, ce qui est parfaitement normal pour un centile. La méthode exclusive pèse lourd ici : sur ces mêmes chiffres, CENTILE.INCLURE s'arrêterait à 55 900 €, soit 2 500 € plus bas.
Comment borner une fourchette interquartile avec la fonction CENTILE.EXCLURE
Une offre part la semaine prochaine pour un poste que tu n'as jamais recruté, et il faut un salaire défendable, ni hors marché ni ridicule. Ton étude comparative rassemble onze offres relevées chez des concurrents, avec un 52 000 € d'un grand groupe et un 36 000 € d'une jeune structure qui ne représentent ni l'un ni l'autre le marché réel. Plutôt qu'une moyenne que ces deux bornes déforment, tu veux la fourchette dans laquelle se situe la moitié centrale des offres. La fonction CENTILE.EXCLURE sort le bas puis le haut de cette fourchette, sans jamais s'appuyer sur les deux extrêmes.
- 1Dans une cellule, écris
=CENTILE.EXCLURE(. - 2En 1er argument : sélectionne la colonne des salaires relevés,
B2:B12. Les onze offres du tableau y figurent dans l'ordre où tu les as saisies, et cet ordre n'a aucune importance puisque la fonction trie la plage en interne avant de calculer. - 3En 2ᵉ argument : saisis le bas de la fourchette en décimal,
0,25. Ce niveau est celui du premier quartile, autrement dit le montant sous lequel se situe le quart le moins généreux du marché. - 4Ferme la parenthèse et appuie sur Entrée, puis reprends la même formule dans une autre cellule en remplaçant
0,25par0,75. Cette seconde valeur te rend le troisième quartile, et les deux bornes ensemble délimitent la moitié centrale des offres.
=CENTILE.EXCLURE(B2:B12; 0,25)39 000 € pour le premier quartile. Le rang visé vaut ici 0,25 × 12 = 3 tout rond, donc aucune interpolation n'entre en jeu : le seuil tombe pile sur la 3e offre une fois la colonne triée. En repassant le centile à 0,75, tu obtiens le troisième quartile (46 000 €), et les deux ensemble bornent la fourchette dans laquelle se tient la moitié centrale du marché.
Comment rattraper l'erreur #NOMBRE! d'un centile hors bornes avec les fonctions CENTILE.EXCLURE et SIERREUR
Le contrat de transport se signe bientôt et on te demande un délai tenu dans 95 % des cas. Tu n'as pour l'instant que neuf livraisons de test derrière toi, et la formule qui marchait très bien sur l'historique complet de l'an dernier renvoie maintenant une erreur que personne dans la salle ne saura interpréter. Cette version de la fonction refuse en effet les centiles trop extrêmes pour la taille de l'échantillon, ce qui est une protection et non un caprice. La fonction SIERREUR intercepte le code d'erreur et le remplace par un message qui explique quoi faire.
- 1Dans une cellule, écris
=SIERREUR(. - 2En 1er argument : écris le calcul à surveiller et referme-le,
CENTILE.EXCLURE(B2:B10; 0,95). La plageB2:B10couvre les neuf délais relevés, et le centile demandé y est fixé à0,95. Avec neuf valeurs, il aurait fallu rester entre1/10et9/10, bornes comprises, et c'est ce dépassement qui déclenche l'erreur. - 3En 2ᵉ argument : saisis le message affiché quand le calcul échoue,
"Échantillon trop petit". Les guillemets encadrent obligatoirement ce texte, comme tout libellé écrit directement dans une formule. Nomme la cause réelle plutôt que d'afficher une cellule vide, sinon la personne qui lit le tableau croira que le calcul n'a jamais été lancé. - 4Ferme la parenthèse et appuie sur Entrée. Celle de la fonction CENTILE.EXCLURE a déjà été refermée à l'étape précédente.
=SIERREUR(CENTILE.EXCLURE(B2:B10; 0,95); "Échantillon trop petit")9/10 = 0,9, si bien que 0,95 déclenche #NOMBRE!. La fonction SIERREUR remplace ce code par une phrase qui dit la vraie cause, à savoir qu'il manque des données et non que la formule est mal écrite. Le message est d'ailleurs juste : en redescendant à 0,9 tu obtiens 8 jours, et à 0,75 tu obtiens 6,5 jours.
Astuces avancées avec CENTILE.EXCLURE
Convertis tes pourcentages sans te tromper
Tu veux le 75e percentile ? Utilise 0,75 et non 75. Pour éviter l'erreur de saisie, tu peux écrire directement =CENTILE.EXCLURE(A1:A100; 75%) avec le symbole % : Excel convertit automatiquement en 0,75.
C'est plus lisible dans une feuille partagée et ça évite les erreurs #NOMBRE! dues à un oubli de conversion.
Vérifie la validité de k avant de lancer l'analyse
Avant de partager tes résultats, assure-toi que ton k est dans les limites acceptables : =SI(ET(k>1/(NBVAL(A:A)+1); k<NBVAL(A:A)/(NBVAL(A:A)+1)); "OK"; "k invalide"). Si ta plage contient beaucoup de cellules vides, le n réel peut être bien inférieur à la taille de ta plage.
Une plage A1:A100 avec seulement 5 valeurs remplies limite k entre 0,17 et 0,83.
Combine CENTILE.EXCLURE avec un graphique en boîte
Pour visualiser une distribution en réunion, construis un box plot en combinant =CENTILE.EXCLURE(data; 0,25), =CENTILE.EXCLURE(data; 0,5) et =CENTILE.EXCLURE(data; 0,75) avec MIN et MAX dans un graphique à barres empilées.
C'est la manière standard de présenter des distributions de salaires, de temps de traitement ou de performances commerciales.
Les erreurs fréquentes avec la fonction CENTILE.EXCLURE
Presque tous les soucis avec CENTILE.EXCLURE tournent autour de k. Soit il sort de l'intervalle autorisé (#NOMBRE!), soit tu as tapé 90 en pensant 90 %, soit ta plage cache des cellules vides qui font chuter le nombre réel de valeurs et resserrent silencieusement les bornes valides. Le dernier cas est plus sournois : croire qu'il faut trier les données à la main, alors que la fonction le fait déjà en interne.
Erreur #NOMBRE! : k hors des limites de la distribution
L'erreur survient quand k est exactement 0 ou 1, ou en dehors de l'intervalle 1/(n+1) à n/(n+1). Avec 9 valeurs, k=0,05 génère cette erreur car 0,05 < 1/10 = 0,1.
Solution : Vérifie le nombre de valeurs dans ta matrice avec =NBVAL(A1:A100), puis assure-toi que ton k respecte les limites. Si tu as besoin de k=0 ou k=1, utilise CENTILE.INCLURE ou directement MIN/MAX.
Résultat inattendu avec une plage contenant des cellules vides
Si ta matrice contient beaucoup de cellules vides, le nombre réel de valeurs (n) peut être bien inférieur à la taille de ta plage, ce qui restreint l'intervalle valide pour k de façon silencieuse.
Solution : Utilise une plage dynamique adaptée au nombre réel de valeurs : =CENTILE.EXCLURE(DECALER(A1;;;NBVAL(A:A);1); 0,9), ou convertis ta plage en tableau structuré pour qu'elle s'ajuste automatiquement.
Confusion décimale : entrer 90 au lieu de 0,9
Entrer =CENTILE.EXCLURE(A1:A10; 90) provoque #NOMBRE! car 90 est hors de l'intervalle [0 ; 1].
Solution : Pense toujours en décimales. Tu peux aussi taper 90% directement : Excel le convertit en 0,9. Ou divise par 100 : =CENTILE.EXCLURE(A1:A10; C1/100) si tu as tapé 90 en C1.
Résultat différent selon l'ordre des données
Beaucoup pensent que CENTILE.EXCLURE nécessite des données pré-triées. C'est faux : la fonction trie automatiquement en interne. Trier manuellement n'apporte rien et risque de désaligner les colonnes si tu oublies de retrier après un ajout.
Solution : Garde tes données dans leur ordre naturel (chronologique, alphabétique) et laisse Excel trier en interne. Si tu as plusieurs colonnes liées, un tri manuel peut désynchroniser les lignes.
CENTILE.EXCLURE vs CENTILE.INCLURE vs QUARTILE.EXCLURE
Prends CENTILE.EXCLURE quand tu veux des seuils statistiquement propres, calculés comme si ton échantillon n'était qu'un extrait d'une population plus large : études salariales, contrôle qualité, SLA. Elle refuse k=0 et k=1, et resserre les percentiles atteignables à mesure que tes données se raréfient. Si au contraire tu veux pouvoir demander le 0e ou le 100e percentile (rapports business classiques), CENTILE.INCLURE et son k de 0 à 1 te suffisent. Et si tu ne cherches que Q1, la médiane et Q3, QUARTILE.EXCLURE fait le même calcul exclusif sans avoir à manier de décimales.
| Critère | CENTILE.EXCLURE | CENTILE.INCLURE | QUARTILE.EXCLURE |
|---|---|---|---|
| Plage k valide | 1/(n+1) ≤ k ≤ n/(n+1) | 0 ≤ k ≤ 1 | Quartiles 1, 2 ou 3 |
| Peut retourner min/max | Oui, si k = 1/(n+1) ou n/(n+1) | Oui (k=0 et k=1) | Non (0 et 4 refusés) |
| Méthode de calcul | Méthode exclusive | Méthode inclusive | Méthode exclusive |
| Recommandé pour | Analyses statistiques rigoureuses | Rapports business standard | Analyses Q1/Q2/Q3 |
| Flexibilité | Tout k valide (percentile libre) | Tout k de 0 à 1 | 3 valeurs fixes uniquement |
Questions fréquentes
CENTILE.INCLURE accepte k de 0 à 1 (0 % à 100 %). CENTILE.EXCLURE refuse k=0 et k=1, et limite k à l'intervalle 1/(n+1) à n/(n+1), bornes comprises. Ce qu'elle exclut, ce sont donc les percentiles 0 et 100, pas les valeurs extrêmes elles-mêmes : avec 9 données, k=0,1 te rend bel et bien le minimum de la plage.
La différence de fond est la méthode : CENTILE.EXCLURE traite ta série comme un échantillon d'une population plus large, ce qui est plus rigoureux statistiquement.
Utilise CENTILE.EXCLURE pour des analyses statistiques rigoureuses, où ta série n'est qu'un échantillon d'une population plus large : études salariales, contrôles qualité, SLA. Elle refuse les percentiles 0 et 100, ce qui évite d'appeler « seuil » une borne que le prochain relevé fera bouger. CENTILE.INCLURE suffit pour des rapports business standard.
L'erreur #NOMBRE! survient quand k est hors des limites. Avec n valeurs, k doit être entre 1/(n+1) et n/(n+1). Par exemple, avec 9 valeurs, k doit être entre 0,1 et 0,9. Si tu demandes k=0,95 avec seulement 9 valeurs, tu obtiens cette erreur.
Compte le nombre de valeurs (n) avec =NBVAL(A:A), puis calcule 1/(n+1) pour la limite basse et n/(n+1) pour la limite haute. Avec 19 valeurs : limite basse = 1/20 = 0,05 et limite haute = 19/20 = 0,95. Ton k doit être entre ces deux valeurs.
Oui, CENTILE.EXCLURE fonctionne parfaitement avec des nombres négatifs. La fonction trie d'abord toutes les valeurs (positives et négatives) puis calcule le percentile. C'est utile pour analyser des écarts, des pertes ou des températures.
CENTILE.EXCLURE te permet de calculer n'importe quel percentile (0,1, 0,33, 0,72...) via le paramètre k décimal. QUARTILE.EXCLURE est limitée à 3 valeurs : Q1, la médiane et Q3. Contrairement à QUARTILE.INCLURE, elle n'accepte ni 0 ni 4 et renvoie #NOMBRE! sur ces deux arguments, ce qui est cohérent avec la méthode exclusive.
Si tu veux uniquement les quartiles, QUARTILE.EXCLURE est plus simple ; pour des percentiles libres, tu dois utiliser CENTILE.EXCLURE.
Pour aller plus loin
Continue sur ta lancée après la fonction CENTILE.EXCLURE : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
