CENTILE.EXCLURE : Calculer les Percentiles avec Exclusion des Extrêmes
=CENTILE.EXCLURE(matrice; k)La formule CENTILE.EXCLURE est une fonction statistique avancée d'Excel qui permet de calculer le percentile d'une série de données en excluant les valeurs extrêmes (0 et 1). Contrairement à CENTILE.INCLURE qui considère l'ensemble complet des données, CENTILE.EXCLURE applique une méthode d'interpolation linéaire exclusive, particulièrement utile pour les analyses statistiques rigoureuses où vous souhaitez éliminer les biais potentiels des valeurs aberrantes. Cette fonction est indispensable dans les domaines comme la finance, la qualité, les ressources humaines et la recherche, où la précision statistique est cruciale. Elle permet de déterminer des seuils de performance, d'identifier les quartiles de distribution de salaires, ou d'analyser les variations de rendement sans tenir compte des extrêmes qui pourraient fausser l'interprétation des résultats. Comprendre CENTILE.EXCLURE vous permet de réaliser des analyses de données plus sophistiquées et de prendre des décisions métier basées sur des statistiques fiables et méthodologiquement correctes, en respectant les normes statistiques professionnelles.
Syntaxe et paramètres
La syntaxe de CENTILE.EXCLURE est : =CENTILE.EXCLURE(matrice; k). Le paramètre 'matrice' représente la plage de données sur laquelle vous souhaitez effectuer le calcul statistique. Il peut s'agir d'une simple plage (A1:A100), d'une référence nommée, ou même d'un tableau Excel. Ce paramètre est obligatoire et doit contenir au minimum deux valeurs numériques. Le paramètre 'k' définit le percentile que vous recherchez, exprimé sous forme décimale comprise entre 0 et 1 (exclusif). Par exemple, 0,25 représente le 25e percentile (premier quartile), 0,5 le 50e percentile (médiane), et 0,75 le 75e percentile (troisième quartile). Attention : les valeurs 0 et 1 sont explicitement exclues et généreront une erreur #NUM!. La principale différence avec CENTILE.INCLURE réside dans la méthode de calcul : CENTILE.EXCLURE utilise une interpolation linéaire exclusive, ce qui signifie que les valeurs extrêmes ne sont pas considérées comme des percentiles possibles. Cette approche est recommandée par les statisticiens modernes et correspond aux standards ISO. Utilisez cette formule quand vous avez besoin de résultats statistiquement rigoureux et méthodologiquement corrects.
arraykExemples pratiques
Analyse des Salaires par Quartiles
=CENTILE.EXCLURE(A2:A51; 0,25)Cette formule calcule le 25e percentile (Q1) des 50 salaires. Le résultat indique le seuil sous lequel se situent 25% des salaires de l'entreprise. Pour obtenir Q3, utilisez 0,75 à la place de 0,25.
Contrôle Qualité dans la Production
=CENTILE.EXCLURE(B2:B101; 0,90)Cette formule détermine la valeur en dessous de laquelle se situent 90% des mesures. Les pièces dépassant cette valeur sont considérées comme hors tolérance et nécessitent une investigation.
Analyse de Performance des Temps de Réponse
=CENTILE.EXCLURE(C1:C1000; 0,95)Cette formule calcule le temps de réponse en dessous duquel 95% des requêtes sont traitées. Cet indicateur est crucial pour les SLA (Service Level Agreements) et la gestion des attentes clients.
Points clés à retenir
- CENTILE.EXCLURE calcule les percentiles avec une interpolation linéaire exclusive, excluant les valeurs 0 et 1, ce qui la rend plus rigoureuse statistiquement que CENTILE.INCLURE.
- Le paramètre k doit être un nombre décimal entre 0 et 1 (exclusif) : 0,25 pour Q1, 0,5 pour la médiane, 0,75 pour Q3.
- Utilisez cette formule pour l'analyse de distribution de données, la détection de valeurs aberrantes, et la création de seuils de performance dans les domaines financiers, qualitatif et RH.
- Combinez CENTILE.EXCLURE avec d'autres fonctions (IF, ABS, INDIRECT) pour créer des analyses avancées et des outils de reporting dynamiques.
- Testez toujours vos formules avec des données de test et documentez la méthodologie statistique pour assurer la transparence et la crédibilité de vos analyses.
Astuces de pro
Utilisez des références absolues ($A$1:$A$100) pour la matrice quand vous copiez la formule horizontalement ou verticalement. Cela garantit que tous les calculs utilisent la même plage de données de référence.
Impact : Évite les erreurs de référence et assure la cohérence des analyses comparatives dans vos tableaux de bord.
Combinez CENTILE.EXCLURE avec INDIRECT() et des cellules nommées pour créer des formules dynamiques. Par exemple : =CENTILE.EXCLURE(INDIRECT(A1); B1) où A1 contient le nom de la plage et B1 le percentile.
Impact : Permet de créer des outils d'analyse flexibles et réutilisables sans modifier la formule, idéal pour les tableaux de bord interactifs.
Testez vos formules avec des données de test simples avant de les appliquer à de grandes séries. Par exemple, vérifiez que CENTILE.EXCLURE({1,2,3,4,5}; 0,5) retourne bien 3 (la médiane).
Impact : Réduit les erreurs de logique et vous permet de comprendre le comportement exact de la formule avant de l'utiliser en production.
Documentez le percentile utilisé dans vos rapports (Q1, Q3, 90e percentile, etc.) pour que les lecteurs comprennent la méthodologie. Ajoutez des commentaires Excel ou des cellules de légende.
Impact : Améliore la clarté et la crédibilité de vos analyses, facilitant la communication avec les stakeholders et les auditeurs.
Combinaisons utiles
Calcul d'Intervalle Interquartile (IQR)
=CENTILE.EXCLURE(A1:A100; 0,75) - CENTILE.EXCLURE(A1:A100; 0,25)Cette combinaison calcule l'intervalle interquartile (Q3 - Q1), qui mesure la dispersion des 50% centrales de vos données. L'IQR est crucial pour identifier les valeurs aberrantes en statistiques. Une valeur aberrante est généralement définie comme étant inférieure à Q1 - 1,5*IQR ou supérieure à Q3 + 1,5*IQR.
Détection de Valeurs Aberrantes
=SI(A1 > CENTILE.EXCLURE($A$1:$A$100; 0,75) + 1,5*(CENTILE.EXCLURE($A$1:$A$100; 0,75) - CENTILE.EXCLURE($A$1:$A$100; 0,25)); "Aberrante"; "Normal")Cette formule combine CENTILE.EXCLURE avec une logique IF pour identifier automatiquement les valeurs aberrantes selon la méthode statistique standard (1,5 × IQR). Appliquez-la à chaque ligne pour marquer les données suspectes qui méritent une investigation.
Normalisation des Données avec Percentiles
=(A1 - CENTILE.EXCLURE($A$1:$A$100; 0,25)) / (CENTILE.EXCLURE($A$1:$A$100; 0,75) - CENTILE.EXCLURE($A$1:$A$100; 0,25))Cette formule normalise chaque valeur en utilisant la méthode du percentile, ramenant les données à une échelle 0-1 basée sur l'intervalle interquartile. C'est utile pour comparer des variables avec des unités ou des magnitudes différentes, notamment en machine learning et analyse comparative.
Erreurs courantes
Cause : Le paramètre k est égal à 0, 1, ou en dehors de la plage (0, 1). Par exemple : =CENTILE.EXCLURE(A1:A10; 0) ou =CENTILE.EXCLURE(A1:A10; 1) ou =CENTILE.EXCLURE(A1:A10; 1,5)
Solution : Vérifiez que k est strictement compris entre 0 et 1 (exclusif). Utilisez des valeurs comme 0,25, 0,5, 0,75, 0,9, etc. Si vous avez besoin des extrêmes, utilisez MIN() et MAX() à la place.
Cause : La matrice contient des valeurs non numériques (texte, espaces, cellules vides) ou k n'est pas au format décimal correct. Par exemple : =CENTILE.EXCLURE(A1:A10; "0,5") avec k en texte.
Solution : Nettoyez vos données en supprimant les valeurs texte et les espaces. Assurez-vous que k est un nombre décimal, pas du texte. Utilisez IFERROR pour gérer les erreurs : =IFERROR(CENTILE.EXCLURE(A1:A10; 0,5); "Données invalides")
Cause : La plage de référence (matrice) est invalide ou a été supprimée. Par exemple, si vous avez supprimé la colonne A après avoir créé la formule : =CENTILE.EXCLURE(A1:A10; 0,5) devient invalide.
Solution : Vérifiez que la plage de référence existe toujours et est correctement nommée. Utilisez des références absolues ($A$1:$A$10) pour éviter les modifications accidentelles lors de la copie de formules.
Checklist de dépannage
- 1.Vérifiez que le paramètre k est un nombre décimal strictement compris entre 0 et 1 (exclusif). Les valeurs 0 et 1 génèrent #NUM!.
- 2.Assurez-vous que la matrice contient uniquement des valeurs numériques. Supprimez les cellules vides, le texte et les espaces qui causent #VALUE!.
- 3.Confirmez que la plage de référence existe et n'a pas été supprimée. Utilisez des références absolues ($) pour éviter les erreurs #REF!.
- 4.Vérifiez que vous avez au moins 2 valeurs dans la matrice. Avec une seule valeur, le calcul du percentile n'est pas statistiquement valide.
- 5.Testez votre formule avec une petite plage de données connues pour valider la logique avant de l'appliquer à de grandes séries.
- 6.Si vous utilisez des données filtrées ou masquées, assurez-vous que CENTILE.EXCLURE traite bien les données visibles (Excel 365 utilise FILTRE; les versions anciennes peuvent nécessiter des ajustements).
Cas particuliers
Matrice contenant des valeurs négatives
Comportement : CENTILE.EXCLURE traite les valeurs négatives normalement, les incluant dans le calcul du percentile selon leur position dans la distribution ordonnée.
Solution : Aucune action nécessaire. Si vous souhaitez exclure les valeurs négatives, utilisez FILTRE ou IF pour les éliminer avant le calcul.
Utile pour les analyses d'écarts, de variations, ou de rendements qui peuvent être négatifs.
Toutes les valeurs de la matrice sont identiques
Comportement : CENTILE.EXCLURE retourne cette valeur unique pour tout k, car il n'y a pas de variation dans la distribution.
Solution : Vérifiez si vos données sont correctes. Si c'est intentionnel, le résultat est mathématiquement correct.
Cas rare en pratique, mais important pour les tests et la validation de formules.
Utilisation de k = 0,5 avec un nombre pair vs impair de valeurs
Comportement : CENTILE.EXCLURE retourne la médiane calculée par interpolation linéaire exclusive, qui peut différer légèrement de MEDIAN() selon la taille de l'échantillon.
Solution : Si la précision est critique, comparez les résultats de CENTILE.EXCLURE(plage; 0,5) et MEDIAN(plage) pour comprendre les différences.
Pour les grandes séries de données (n > 100), les différences sont généralement négligeables.
Limitations
- •CENTILE.EXCLURE n'accepte pas k = 0 ou k = 1, contrairement à CENTILE.INCLURE. Si vous avez besoin des valeurs extrêmes absolues, utilisez MIN() et MAX() à la place.
- •La formule ne fonctionne pas avec les plages masquées ou filtrées dans les versions anciennes d'Excel. Excel 365 gère mieux les données filtrées, mais vérifiez toujours que vous traitez les bonnes données.
- •CENTILE.EXCLURE n'est pas disponible dans les versions d'Excel antérieures à 2010. Si vous travaillez avec Excel 2007 ou plus ancien, utilisez PERCENTILE() ou CENTILE.INCLURE().
- •La performance peut être affectée avec des matrices très volumineuses (> 100 000 lignes) combinées avec d'autres fonctions complexes. Envisagez d'utiliser des plages nommées ou des tableaux pour optimiser les calculs.
Alternatives
Simplifie le calcul des quartiles en utilisant directement les numéros 1, 2, 3 au lieu de décimales. Plus lisible pour les calculs de quartiles spécifiques.
Quand : Préférez QUARTILE.EXCLURE si vous ne calculez que des quartiles (25e, 50e, 75e percentiles) car la syntaxe est plus intuitive : =QUARTILE.EXCLURE(plage; 1) pour Q1.
Compatibilité
✓ Excel
Depuis Excel 2010
=CENTILE.EXCLURE(matrice; k) - Disponible dans Excel 2010, 2013, 2016, 2019, 365✓Google Sheets
=PERCENTILE(range; k) - Google Sheets utilise PERCENTILE() avec interpolation inclusive par défaut. Pour l'équivalent exact de CENTILE.EXCLURE, utilisez =PERCENTILE(range; k) avec la méthode d'interpolation linéaire.Google Sheets n'a pas de fonction CENTILE.EXCLURE native. La fonction PERCENTILE se rapproche du comportement de CENTILE.INCLURE. Pour une interpolation exclusive, vous devrez utiliser des formules personnalisées.
✓LibreOffice
=PERCENTILE(range; k) - LibreOffice Calc utilise PERCENTILE() qui se rapproche de CENTILE.INCLURE