Quelle est la différence entre CENTILE, PERCENTILE.INC et PERCENTILE.EXC?

CENTILE (ancien nom) et PERCENTILE.INC utilisent la même méthode d'interpolation inclusive et donnent des résultats identiques. PERCENTILE.EXC (exclusif) utilise une méthode légèrement différente qui exclut les valeurs extrêmes du calcul. Pour la plupart des analyses, CENTILE et PERCENTILE.INC sont équivalents. PERCENTILE.EXC est utile pour les analyses statistiques rigoureuses où vous voulez exclure les points extrêmes.

Puis-je utiliser CENTILE avec des données négatives?

Oui, absolument. CENTILE fonctionne avec n'importe quelle valeur numérique, y compris les nombres négatifs. Par exemple, =CENTILE(-10:10;0,5) calculera correctement le centile même si votre plage contient des valeurs négatives. Cela est particulièrement utile pour analyser les pertes, les écarts ou les variations.

Comment calculer le 95ème centile pour identifier les valeurs aberrantes?

Utilisez =CENTILE(plage;0,95) pour obtenir le seuil du 95ème centile. Les valeurs supérieures à ce résultat représentent les 5% supérieures (potentiellement aberrantes). Pour une détection bidirectionnelle, combinez-la avec =CENTILE(plage;0,05) pour le 5ème centile. Les valeurs en dehors de cet intervalle [5ème; 95ème] peuvent être considérées comme aberrantes.

CENTILE fonctionne-t-il avec des plages non contiguës?

Non, CENTILE ne fonctionne qu'avec des plages contiguës. Si vous avez besoin d'analyser des colonnes séparées, créez d'abord une plage unique en les combinant, ou utilisez plusieurs formules CENTILE distinctes. Vous pouvez utiliser UNION pour combiner des plages : =CENTILE(UNION(A1:A10;C1:C10);0,5), mais cette approche est complexe.

Que se passe-t-il si ma plage contient des valeurs dupliquées?

CENTILE traite chaque valeur individuellement, y compris les doublons. Si vous avez 10 valeurs dont 5 sont identiques, elles sont toutes comptabilisées séparément dans le calcul du centile. Cela est le comportement attendu et correct pour les analyses statistiques. Les doublons n'affectent pas la précision du calcul.

CENTILE : Maîtrisez le calcul des centiles dans Excel

Intermédiaire

=CENTILE(matrice; k)

La formule CENTILE est une fonction statistique incontournable pour analyser la distribution de vos données dans Excel. Elle permet de déterminer la valeur en dessous de laquelle se situe un pourcentage donné d'observations, ce qui en fait un outil précieux pour les analyses statistiques, les études de performance et la gestion de projets. Que vous travailliez dans le domaine financier, les ressources humaines ou la qualité, vous rencontrerez régulièrement le besoin de calculer des percentiles pour identifier les seuils critiques, les valeurs aberrantes ou les performances comparatives. La maîtrise de CENTILE vous permettra d'extraire des insights pertinents de vos données brutes. Par exemple, vous pouvez identifier le 25ème centile (premier quartile) pour évaluer les performances faibles, le 50ème centile (médiane) pour la tendance centrale, ou le 75ème et 90ème centiles pour repérer les excellentes performances. Cette fonction accepte des valeurs de k comprises entre 0 et 1, où 0 représente le minimum et 1 le maximum de votre ensemble de données. Dans ce guide complet, nous explorerons la syntaxe détaillée, les cas d'usage concrets, les pièges courants à éviter et les meilleures pratiques pour optimiser vos analyses statistiques avec CENTILE.

Syntaxe et paramètres

La syntaxe de la formule CENTILE est simple mais puissante : =CENTILE(matrice; k). Le premier paramètre, 'matrice', est obligatoire et doit contenir la plage de données que vous souhaitez analyser. Cette plage peut être une colonne, une ligne ou un tableau complet, et doit contenir des valeurs numériques. Excel ignorera automatiquement les cellules vides et les valeurs textuelles, ce qui rend la fonction robuste face aux données imparfaites. Le deuxième paramètre, 'k', est également obligatoire et représente la position du centile exprimée sous forme décimale entre 0 et 1. Par exemple, k=0,25 correspond au 25ème centile (premier quartile), k=0,5 à la médiane, et k=0,75 au 75ème centile. Si vous souhaitez calculer un centile spécifique comme le 90ème, vous devez entrer k=0,9. Excel utilise la méthode d'interpolation linéaire pour calculer les centiles, ce qui signifie que si votre valeur de centile ne correspond pas exactement à une observation, Excel interpolera entre deux valeurs adjacentes pour fournir un résultat précis. Il est important de noter que CENTILE fonctionne uniquement avec des valeurs numériques. Si votre plage contient du texte ou des valeurs logiques, ces éléments seront ignorés. Assurez-vous également que votre paramètre k est compris entre 0 et 1 ; une valeur en dehors de cette plage générera une erreur #NUM!. Pour les analyses plus avancées, Excel propose également PERCENTILE.INC et PERCENTILE.EXC, qui offrent des variantes de calcul légèrement différentes.

array

Plage de donnees

k

Valeur du centile (0 a 1)

Exemples pratiques

Analyse des salaires par centile dans les RH

=CENTILE(B2:B51;0,25) pour le 25ème centile
=CENTILE(B2:B51;0,5) pour la médiane
=CENTILE(B2:B51;0,75) pour le 75ème centile

Ces trois formules extraient respectivement le premier quartile, la médiane et le troisième quartile de la colonne des salaires. Le 25ème centile identifie le seuil en dessous duquel se situent 25% des salaires, utile pour les ajustements de masse salariale.

Évaluation des performances clients en vente

=CENTILE(C2:C101;0,9)

Cette formule calcule le seuil du 90ème centile, permettant d'identifier les transactions exceptionnelles. Les valeurs supérieures à ce résultat représentent les 10% meilleures performances, essentielles pour comprendre les facteurs de succès.

Contrôle qualité dans la production industrielle

=CENTILE(D2:D201;0,05) pour le seuil bas
=CENTILE(D2:D201;0,95) pour le seuil haut

Ces deux formules définissent les limites acceptables. Les pièces avec un poids inférieur au 5ème centile ou supérieur au 95ème centile sont considérées comme défectueuses et doivent être écartées du lot.

Points clés à retenir

CENTILE calcule les centiles (percentiles) d'un ensemble de données avec la syntaxe =CENTILE(matrice;k) où k est entre 0 et 1
Les valeurs courantes sont k=0,25 (1er quartile), k=0,5 (médiane), k=0,75 (3e quartile) et k=0,9 (90ème centile pour les top performers)
CENTILE utilise l'interpolation linéaire pour calculer les centiles précis, même si la valeur ne correspond pas exactement à une observation
PERCENTILE.INC et PERCENTILE.EXC sont les alternatives modernes ; CENTILE est l'ancien nom mais reste entièrement fonctionnel
Combinez CENTILE avec SI, COUNTIF ou MOYENNE pour créer des analyses avancées et des systèmes de classification automatiques

Astuces de pro

Utilisez des références absolues ($) pour les plages de centile dans les formules copiées : =CENTILE($A$1:$A$100;0,5). Cela garantit que la plage reste fixe quand vous copiez la formule vers d'autres cellules.

Impact : Évite les erreurs de référence et facilite la maintenance des feuilles de calcul complexes avec des centiles multiples.

Combinez CENTILE avec COUNTIF pour compter les valeurs dans chaque intervalle centile : =COUNTIF($A$1:$A$100;">"&CENTILE($A$1:$A$100;0,75)) pour compter les valeurs du 75ème centile+.

Impact : Permet une analyse segmentée rapide sans créer de colonnes d'aide supplémentaires, gagnant du temps et de l'espace.

Créez un tableau de centiles (5ème, 25ème, 50ème, 75ème, 95ème) pour une vue d'ensemble rapide : utilisez une colonne pour les labels (5%, 25%, etc.) et une colonne pour les formules CENTILE correspondantes.

Impact : Fournit un résumé statistique complet en quelques secondes, idéal pour les présentations et les rapports exécutifs.

Testez la robustesse de vos centiles en utilisant à la fois CENTILE et PERCENTILE.INC sur les mêmes données pour vérifier la cohérence, surtout avec de petits ensembles de données.

Impact : Augmente la confiance dans vos analyses et détecte les anomalies potentielles dans vos données.

Combinaisons utiles

Identifier les valeurs aberrantes avec CENTILE et SI

=SI(A1>CENTILE($A$1:$A$100;0,95);"Aberrant";"Normal")

Cette formule combine CENTILE avec SI pour identifier automatiquement les valeurs aberrantes. Elle teste si chaque valeur dépasse le 95ème centile et affiche 'Aberrant' ou 'Normal'. Parfait pour le flagging automatique dans les analyses de qualité.

Calculer l'intervalle interquartile (IQR) avec CENTILE

=CENTILE($A$1:$A$100;0,75)-CENTILE($A$1:$A$100;0,25)

Cette formule calcule l'écart entre le 75ème et 25ème centile, appelé intervalle interquartile. C'est une mesure de dispersion robuste, utile pour comprendre la variabilité de vos données indépendamment des valeurs extrêmes.

Créer des seuils dynamiques avec CENTILE et MOYENNE

=SI(A1>MOYENNE($A$1:$A$100)+2*CENTILE($A$1:$A$100;0,75);"Très haut";SI(A1<MOYENNE($A$1:$A$100)-2*CENTILE($A$1:$A$100;0,25);"Très bas";"Normal"))

Cette formule avancée combine CENTILE avec MOYENNE pour créer une classification en trois niveaux basée sur la moyenne et les centiles. Utile pour segmenter les données en performances basses, normales et hautes.

Erreurs courantes

#NUM!

Cause : Le paramètre k est en dehors de la plage [0;1]. Par exemple, =CENTILE(A1:A10;1,5) ou =CENTILE(A1:A10;-0,1)

Solution : Vérifiez que k est exprimé en décimal entre 0 et 1. Pour le 95ème centile, utilisez k=0,95 et non k=95 ou k=1,95. Utilisez des parenthèses si vous calculez k dynamiquement : =CENTILE(A1:A10;95/100)

#VALUE!

Cause : La matrice contient du texte non numérique ou le paramètre k est du texte. Par exemple, =CENTILE(A1:A10;"0,5") avec des guillemets autour de 0,5

Solution : Assurez-vous que tous les éléments de la plage sont numériques ou vides. Supprimez les guillemets autour de k. Si votre plage contient du texte mélangé, utilisez une plage plus précise ou nettoyez vos données avec NETTOYER().

#REF!

Cause : La plage de référence a été supprimée ou n'existe pas. Par exemple, si vous supprimez la colonne A après avoir créé la formule =CENTILE(A:A;0,5)

Solution : Vérifiez que la plage référencée existe toujours. Utilisez des références absolues avec $ pour les formules critiques : =CENTILE($A$1:$A$100;0,5). Recréez la formule avec la bonne plage si nécessaire.

Checklist de dépannage

1.Vérifiez que le paramètre k est un nombre décimal entre 0 et 1 (non un pourcentage comme 95 ou 0.95% avec le signe %)
2.Confirmez que la plage de données contient uniquement des valeurs numériques ou des cellules vides (pas de texte mélangé)
3.Assurez-vous que la plage est contiguë et ne contient pas de sauts ou de zones non adjacentes
4.Vérifiez que la plage n'a pas été supprimée ou modifiée si vous recevez une erreur #REF!
5.Testez avec une formule simple =CENTILE(A1:A10;0,5) pour isoler les problèmes avant d'ajouter de la complexité
6.Utilisez le gestionnaire de noms ou le vérificateur de formules (Formules > Vérification des erreurs) pour diagnostiquer les problèmes dans les formules complexes

Cas particuliers

Ensemble de données avec une seule valeur unique (ex: A1:A10 contient tous les 5)

Comportement : CENTILE retournera toujours 5, quel que soit k (sauf k=0 qui retourne 5 et k=1 qui retourne 5)

Solution : C'est le comportement correct. Un ensemble homogène n'a qu'un seul centile possible.

Cela indique une absence de variation dans vos données, ce qui peut signifier un problème de collecte ou une vraie uniformité.

Ensemble très petit (ex: seulement 3 valeurs : 10, 20, 30) avec k=0,5 (médiane)

Comportement : CENTILE interpolera et retournera 20, la valeur médiane exacte

Avec de très petits ensembles, les centiles interpolés peuvent ne pas correspondre exactement à une observation réelle, ce qui est normal et attendu.

Plage contenant des zéros et des valeurs négatives mélangées

Comportement : CENTILE traite correctement tous les nombres, y compris les zéros et les négatifs, en les classant dans l'ordre numérique complet

Assurez-vous que vos zéros ne sont pas des placeholders pour des données manquantes ; utilisez plutôt des cellules vides si c'est le cas.

Limitations

•CENTILE ne fonctionne qu'avec des plages contiguës. Vous ne pouvez pas analyser directement deux colonnes séparées sans les combiner d'abord.
•La fonction ignore les cellules vides et le texte, ce qui signifie que si votre plage contient beaucoup de données manquantes, le calcul porte uniquement sur les valeurs numériques présentes.
•CENTILE ne peut analyser que des données numériques. Les dates sont traitées comme des nombres (jours depuis le 1/1/1900), mais les valeurs logiques (VRAI/FAUX) et le texte sont ignorés.
•Avec des très petits ensembles de données (moins de 10 valeurs), l'interpolation linéaire peut donner des résultats contre-intuitifs qui ne correspondent à aucune observation réelle de votre ensemble.

Alternatives

PERCENTILE.INC

Fonction moderne et recommandée, syntaxe identique à CENTILE mais plus explicite. Compatible avec toutes les versions récentes d'Excel.

Quand : À privilégier pour les nouveaux fichiers Excel 2010+, car elle est plus claire et mieux documentée que CENTILE (ancien nom).

PERCENTILE.EXC

Utilise une méthode d'interpolation exclusive qui exclut les valeurs extrêmes. Donne des résultats légèrement différents et plus conservateurs.

Quand : Idéale pour les analyses statistiques rigoureuses, notamment en recherche ou en contrôle qualité où l'exclusion des extrêmes est souhaitable.

QUARTILE

Fonction spécialisée pour les quartiles (0, 1, 2, 3, 4). Plus simple si vous n'avez besoin que des quartiles.

Quand : Utilisez QUARTILE(plage;1) pour le 1er quartile au lieu de CENTILE(plage;0,25). Plus lisible pour les analyses par quartiles.

Compatibilité

✓ Excel

Depuis 2007

=CENTILE(matrice;k) - Fonctionne identiquement dans Excel 2007, 2010, 2013, 2016, 2019 et 365

✓Google Sheets

=PERCENTILE(range;k) - Google Sheets utilise PERCENTILE au lieu de CENTILE, mais la syntaxe et le résultat sont identiques

Utilisez =PERCENTILE(A1:A100;0,5) au lieu de =CENTILE(A1:A100;0,5) dans Google Sheets. Les deux fonctions donnent exactement le même résultat.

✓LibreOffice

=PERCENTILE(array;k) - LibreOffice Calc utilise également PERCENTILE et accepte la même syntaxe que Google Sheets

Questions fréquentes

Découvrez comment ElyxAI optimise vos analyses Excel avec des formules intelligentes et des modèles prédéfinis. Gagnez du temps et améliorez la précision de vos calculs statistiques dès maintenant avec ElyxAI.

LOI.NORMALE.STANDARD.INVERSE

RANG.POURCENTAGE