Qu'est-ce que la fonction CENTILE ?
CENTILE (PERCENTILE en anglais) te sert quand tu dois fixer un seuil, qui entre dans le top 10 %, qui dépasse la médiane, sans l'inventer au hasard. Plutôt que de deviner une valeur ronde, tu la calcules directement depuis la répartition réelle de tes données.
Concrètement, c'est elle qui te permet d'identifier les top performers dans une équipe commerciale, de fixer des seuils de prime RH basés sur la distribution réelle des salaires, de détecter les temps de production anormalement longs en production, ou de segmenter une base clients en groupes automatiquement rééquilibrés. Plus besoin de seuils arbitraires : CENTILE les calcule d'après tes données réelles.
Syntaxe
Clique sur un argument pour aller à son explication.
Si k ne tombe pas exactement sur une valeur de la plage, Excel interpole automatiquement entre les deux valeurs les plus proches. Le résultat peut donc être un nombre décimal qui n'existe pas littéralement dans tes données.
Comprendre chaque paramètre de la fonction CENTILE
Tu donnes d'abord la plage de chiffres, puis le percentile que tu cherches : les deux sont obligatoires. Le piège est dans le second, k, qui s'exprime en décimal entre 0 et 1 et non en pourcentage entier. Le top 10% s'écrit donc 0,9 et pas 90, sinon tu récoltes un #NOMBRE!.
matrice
: la plage de données numériques sur laquelle tu veux calculer le percentileÇa peut être une colonne de salaires comme A1:A100, une ligne de scores comme B2:Z2, ou n'importe quelle plage de cellules contenant des nombres. Excel ignore automatiquement les cellules vides et le texte.
k
: le percentile que tu veux calculer, exprimé sous forme décimale entre `0` et `1`Par exemple : 0,25 = 25ème percentile (premier quartile Q1), 0,5 = 50ème percentile (médiane), 0,75 = 75ème percentile (troisième quartile Q3), 0,9 = 90ème percentile (top 10%), 0,95 = 95ème percentile (top 5%).
Si tu entres une valeur supérieure à 1 ou inférieure à 0, Excel renvoie une erreur #NOMBRE!.
Exemples pratiques pas à pas
Comment calculer le seuil du top 10 % avec la fonction CENTILE
La direction veut savoir à partir de quel salaire un employé entre dans les 10 % les mieux payés, et elle attend un chiffre défendable en réunion, pas une estimation au doigt mouillé. Les dix-huit salaires du périmètre sont saisis dans une colonne, avec des écarts qui vont du simple au double. La fonction CENTILE te sort ce seuil directement depuis la distribution réelle, sans que tu aies à trier ni à compter quoi que ce soit à la main.
- 1Dans une cellule, écris
=CENTILE(. - 2En 1er argument : sélectionne la colonne des salaires annuels,
D2:D19. Les dix-huit montants y sont saisis dans le désordre, et ça ne pose aucun problème. La fonction les range elle-même du plus bas au plus haut avant de découper la distribution. - 3En 2ᵉ argument : saisis le percentile qui ouvre le top 10 %,
0,9. Ce nombre s'écrit toujours en décimal entre0et1, jamais en pourcentage entier. Un90à cette place te renverrait un#NOMBRE!au lieu du seuil attendu. - 4Ferme la parenthèse et appuie sur Entrée.
=CENTILE(D2:D19; 0,9)58 000 € et 62 000 €, alors la fonction interpole et rend 59 200 €. Ce montant n'apparaît sur aucune fiche de paie, et c'est normal : c'est un seuil, pas un salaire existant. Seuls Camille Girard (62 000 €) et Paul Girard (68 000 €) le dépassent.
Comment calculer plusieurs percentiles d'un coup avec la fonction CENTILE
L'équipe commerciale veut afficher un engagement de délai de réponse sur le site et te demande un chiffre tenable, pas un vœu pieux. Tu as relevé les vingt et un derniers temps de réponse du support dans la colonne F, et la moyenne ne t'aide en rien : deux tickets partis en vrille la font grimper à eux seuls. La fonction CENTILE te donne la valeur-seuil de n'importe quel pourcentage, et en alignant plusieurs k dans une colonne tu obtiens le profil complet de la distribution.
- 1Dans une cellule, écris
=CENTILE(. - 2En 1er argument : sélectionne la colonne qui contient les vingt et un temps de réponse relevés,
$F$2:$F$22. Les dollars figent la plage avant la recopie. Sans eux, elle descendrait d'une ligne à chaque seuil et tes cinq résultats ne porteraient plus sur la même série. - 3En 2ᵉ argument : clique sur la cellule qui porte le percentile demandé,
B2. Pointer cette cellule au lieu de taper0,1en dur, c'est ce qui permet à une seule formule de sortir les cinq seuils. - 4Ferme la parenthèse et appuie sur Entrée, puis recopie la formule sur les quatre lignes suivantes. Chaque ligne lit alors son propre percentile en colonne B, de
0,25jusqu'à0,9.
=CENTILE($F$2:$F$22; B2)k en colonne B et la fonction CENTILE rend le seuil correspondant. Le profil se lit alors d'un coup d'œil, puisque la moitié des tickets sont bouclés en 17 minutes ou moins alors qu'un sur dix demande plus de 72 minutes. Les dollars de $F$2:$F$22 sont indispensables, sinon la plage descendrait d'une ligne à chaque recopie et chaque seuil porterait sur des données différentes.
Comment repérer les valeurs aberrantes avec les fonctions SI et CENTILE
Une commande est partie en retard et l'atelier jure que la production tourne normalement. Tu as les temps des douze derniers lots sous les yeux, et à l'œil nu rien ne saute : les chiffres se ressemblent presque tous. Tu veux une colonne qui lève le drapeau toute seule dès qu'un lot sort de l'ordinaire, sans comparer les valeurs une par une ni fixer une limite arbitraire. La fonction CENTILE calcule le seuil au-delà duquel un temps devient exceptionnel, et la fonction SI colle l'étiquette.
- 1Dans une cellule, écris
=SI(. - 2En 1er argument : écris le test qui décide de l'alerte,
B2>=CENTILE($B$2:$B$13; 0,95). La fonction CENTILE y calcule le seuil des 5 % les plus lents sur les douze temps de production, et sa parenthèse se referme aussitôt. Les dollars figent la plage pour que la recopie compare toujours à la série entière. - 3En 2ᵉ argument : saisis l'étiquette du lot qui franchit le seuil,
"Alerte". Les guillemets sont obligatoires pour qu'Excel affiche le mot tel quel au lieu d'y chercher un nom défini dans le classeur. - 4En 3ᵉ argument : saisis l'étiquette de tous les autres lots,
"Normal". Elle s'affiche dès que le test est faux, ce qui arrive onze fois sur douze ici. - 5Ferme la parenthèse et appuie sur Entrée, puis recopie la formule sur les onze autres lots. Celle de la fonction CENTILE a été refermée dès l'étape du test, si bien qu'il n'en reste plus qu'une à ce stade.
=SI(B2>=CENTILE($B$2:$B$13; 0,95); "Alerte"; "Normal")58,75 minutes, et la fonction SI compare le temps de chaque lot à cette barre. Le Lot 3 (67 minutes) est le seul à la franchir, donc il part en investigation pendant que les onze autres restent en Normal. Le Lot 10 (52 minutes) paraît long à l'œil nu mais reste sous le seuil, ce qui est exactement le genre de faux positif que la méthode évite.
Comment segmenter des données en tranches égales avec les fonctions SI et CENTILE
Le prochain e-mailing doit parler autrement aux petits paniers qu'aux gros clients, et tu n'as aucune envie d'inventer des seuils au hasard. Les douze paniers moyens de la base sont alignés dans une colonne et ils sont très étalés, du client à 35 € à celui à 310 €. Tu veux trois groupes d'à peu près la même taille, qui se réajustent tout seuls le jour où la base grossit. La fonction CENTILE pose les deux coupures au tiers et aux deux tiers de la distribution, et la fonction SI range chaque client du bon côté.
- 1Dans une cellule, écris
=SI(. - 2En 1er argument : écris le test de la coupure basse,
B2<CENTILE($B$2:$B$13; 0,33). La fonction CENTILE y mesure le montant sous lequel se rangent un tiers des douze paniers, soit69,45 €, et sa parenthèse se referme aussitôt. Les dollars sont indispensables pour que la coupure reste la même sur toutes les lignes recopiées. - 3En 2ᵉ argument : saisis l'étiquette du groupe le plus économe,
"Petit". Elle revient aux quatre clients qui dépensent moins que la coupure basse,C001,C005,C008etC011. - 4En 3ᵉ argument : écris la seconde fonction SI avec ses deux sorties et referme-la,
SI(B2<CENTILE($B$2:$B$13; 0,66); "Moyen"; "Gros"). Excel ne la consulte que si le premier test est faux. Elle confronte alors le panier à la coupure haute,139,10 €, pour trancher entre les deux groupes restants. - 5Ferme la parenthèse de la première fonction SI et appuie sur Entrée, puis recopie la formule sur les onze autres clients. La seconde fonction SI a déjà été refermée à l'étape précédente, ce qui explique les deux parenthèses collées à la fin de la formule.
=SI(B2<CENTILE($B$2:$B$13; 0,33); "Petit"; SI(B2<CENTILE($B$2:$B$13; 0,66); "Moyen"; "Gros"))69,45 € et 139,10 € sur la plage figée, puis les fonctions SI emboîtées confrontent chaque panier à ces deux bornes. Les douze clients se répartissent en trois groupes de quatre, ce qui est précisément l'intérêt de découper aux tiers plutôt qu'à des montants ronds choisis au hasard. Le client C001 (45 €) reste sous la première coupure, donc il atterrit en « Petit ».
Les erreurs fréquentes avec la fonction CENTILE
Avec CENTILE, deux ennuis sortent du même #NOMBRE! : un k saisi en pourcentage entier (90 au lieu de 0,9), ou une plage qui ne contient aucun vrai nombre (que du texte ou des chiffres stockés comme texte). Le troisième souci n'est pas un message d'erreur mais un contresens : croire que le P90 vaut 90% du total, alors qu'il désigne la valeur sous laquelle se rangent 90% de tes observations.
Erreur #NOMBRE! : k en dehors de l'intervalle 0-1
Si tu entres =CENTILE(A1:A10; 90), Excel retourne #NOMBRE! car k doit être entre 0 et 1, pas entre 0 et 100. C'est le piège classique quand on pense en pourcentage.
Solution : Divise ta valeur de percentile par 100. Pour le 90ème percentile, utilise =CENTILE(A1:A10; 0,9) ou =CENTILE(A1:A10; 90/100) pour rendre l'intention plus lisible.
Erreur #NOMBRE! : plage sans aucune valeur numérique valide
Si ta plage ne contient aucune valeur numérique valide, que du texte, des dates mal formatées, ou des cellules vides, CENTILE ne peut pas calculer de percentile et renvoie #NOMBRE!.
Solution : Vérifie que ta plage contient bien des nombres avec =ESTNUM(). Si tes nombres sont stockés en texte, convertis-les avec CNUM ou via le triangle vert d'avertissement dans Excel.
Confusion entre percentile et part du total
Erreur conceptuelle fréquente : le 90ème percentile n'est pas la valeur qui représente 90% du total des données. C'est la valeur en dessous de laquelle se trouvent 90% des observations.
Solution : Retiens cette formulation : CENTILE(plage; 0,9) retourne la valeur X telle que 90% des données sont inférieures ou égales à X. Si tu as 100 salaires et que le P90 vaut 80 000 €, cela signifie que 90 personnes gagnent 80 000 € ou moins.
Astuces avancées avec CENTILE
Calcule l'intervalle interquartile pour détecter les valeurs aberrantes
L'IQR (intervalle interquartile) mesure la dispersion des 50% centraux de tes données : =CENTILE(A1:A100; 0,75) - CENTILE(A1:A100; 0,25). Toute valeur en dehors de [Q1 - 1,5 x IQR ; Q3 + 1,5 x IQR] est considérée comme aberrante selon la méthode Tukey.
C'est le test outlier le plus utilisé en analyse de données, robuste aux distributions non-normales.
Construis un tableau de bord de distribution en cinq lignes
Aligne cinq formules CENTILE avec les valeurs 0,1 / 0,25 / 0,5 / 0,75 / 0,9 sur une plage en références absolues ($A$1:$A$1000). Tu obtiens un profil complet de la distribution : plancher des 10%, quartiles, médiane, plafond des 10%.
Ajoute une mise en forme conditionnelle sur les données sources pour colorier chaque valeur dans sa tranche.
Rends tes seuils dynamiques via une cellule de paramètre
Stocke la valeur de k dans une cellule dédiée (par exemple E1 = 0,9) et écris =CENTILE($B$2:$B$100; E1). Pour passer du top 10% au top 5%, tu changes E1 : toutes les formules et mises en forme conditionnelles qui référencent ce seuil se mettent à jour instantanément.
C'est la base d'un tableau de bord paramétrable sans modifier les formules.
CENTILE vs QUARTILE vs MEDIANE vs GRANDE.VALEUR
CENTILE est la plus flexible du groupe : elle couvre n'importe quel pourcentage entre 0 et 100. QUARTILE ne couvre que quatre points fixes. MEDIANE est un raccourci pour le 50ème percentile. GRANDE.VALEUR retourne des valeurs exactes par rang, sans interpolation.
| Critère | CENTILE | QUARTILE | MEDIANE | GRANDE.VALEUR |
|---|---|---|---|---|
| Flexibilité | Tout percentile de 0 à 100% | Seulement 0/25/50/75/100% | Uniquement 50% | Par rang (1er, 2ème...) |
| Cas d'usage | Analyse fine (top 10%, P95...) | Segmentation en quartiles | Valeur médiane uniquement | Top N performers |
| Paramètre | 0 à 1 (décimal) | 0, 1, 2, 3, 4 (entier) | Aucun paramètre | 1, 2, 3... (rang) |
| Interpolation | Oui | Oui | Oui (si nombre pair) | Non, valeur exacte |
| Équivalence | Remplace les trois autres | CENTILE(plage;0,75) = QUARTILE(plage;3) | CENTILE(plage;0,5) = MEDIANE(plage) | Pas de remplacement direct |
Questions fréquentes
CENTILE.INC (inclus) est la version moderne de CENTILE et fonctionne exactement de la même façon. CENTILE.EXC (exclus) utilise une méthode de calcul légèrement différente qui exclut les bornes 0 et 1. Pour la compatibilité avec les anciennes versions d'Excel, utilise CENTILE ou CENTILE.INC.
Utilise CENTILE avec k=0,9. Toute valeur supérieure ou égale à ce résultat fait partie du top 10%. Tu peux ensuite utiliser cette valeur dans une condition SI pour identifier les top performers et leur attribuer un statut ou une prime automatiquement.
Oui, CENTILE gère parfaitement les nombres négatifs. La fonction range toutes les valeurs de la plus petite à la plus grande, qu'elles soient positives ou négatives, puis trouve la valeur qui correspond au percentile demandé.
Excel interpole automatiquement entre les deux valeurs les plus proches. Par exemple, si tu demandes le 85ème percentile et qu'il tombe entre deux valeurs, Excel calcule une moyenne pondérée. Le résultat peut être un nombre décimal qui n'existe pas dans tes données, ce qui est tout à fait normal.
Non, CENTILE prend en compte toutes les valeurs de la plage, même celles masquées par un filtre. Si tu veux calculer un percentile uniquement sur les données visibles, tu dois copier les données filtrées dans une autre plage temporaire, ou utiliser une formule matricielle qui intègre le critère de filtre.
CENTILE retourne une valeur (le score correspondant à un percentile). RANG retourne un rang (la position d'une valeur dans la distribution). Les deux sont complémentaires : CENTILE répond à "combien faut-il avoir pour être dans le top 10% ?", RANG répond à "à quelle position se trouve cette personne ?".
Pour aller plus loin
Continue sur ta lancée après la fonction CENTILE : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
