Excel: Fonctions en pratique

Fonction CENTILE Excel: Tutoriel Facile et Détaillé

Mis à jour |1 commentaire

Fonction CENTILE Excel - Guide Complet

Lorsque vous travaillez avec des données, comprendre leur distribution est essentiel. La fonction CENTILE d'Excel est un outil puissant pour cela. Imaginez que vous ayez une liste de scores ou de revenus et que vous souhaitiez connaître le score médian ou le revenu situé au 90ème centile. C'est exactement là que la famille des fonctions CENTILE entre en jeu. Ces fonctions vous aident à identifier la valeur à laquelle un pourcentage donné de l'ensemble de données est inférieur. Ce tutoriel vous guidera à travers les nuances de différents fonctions de calcul du centile, de sa syntaxe de base à ses applications pratiques, en passant par la résolution des erreurs courantes.

Qu'est-ce qu'un centile exactement ?

Un centile, dans le domaine des mathématiques et de la statistique, est une mesure indiquant la valeur sous laquelle un certain pourcentage de l'ensemble de données se trouve. Par exemple, le 50ème centile, souvent appelé médiane, est la valeur en dessous de laquelle se trouvent 50% des données. De manière plus générale, le centile est utilisé pour comprendre la distribution des données en les divisant en 100 parties égales. Chaque "centile" révèle donc le seuil au-delà duquel se situe un pourcentage donné de l'ensemble des données, permettant une analyse approfondie de leur répartition.

Évolution de la fonction CENTILE: CENTILE.EXCLURE et CENTILE.INCLURE

Il est important de noter que la fonction CENTILE d'Excel, bien qu'encore opérationnelle, est désormais considérée comme obsolète. Depuis la version Excel 2013, Microsoft recommande d'utiliser deux nouvelles fonctions: CENTILE.EXCLURE et CENTILE.INCLURE. Ces fonctions améliorées offrent une plus grande précision et flexibilité dans le calcul des centiles.

Bien que la fonction CENTILE fonctionne toujours dans les versions actuelles d'Excel, il est conseillé d'opter pour CENTILE.EXCLURE ou CENTILE.INCLURE pour assurer la compatibilité avec les futures versions d'Excel et tirer parti des améliorations apportées. Ces nouvelles fonctions permettent de calculer les centiles en incluant ou excluant les valeurs extrêmes de la plage de données, offrant ainsi une analyse plus raffinée et adaptée aux besoins spécifiques de l'utilisateur.

Syntaxe des fonctions Excel CENTILE, CENTILE.EXCLURE et CENTILE.INCLURE

Ces 3 fonctions partagent la même structure de base en termes de syntaxe et d'arguments, bien qu'elles traitent les données de manière légèrement différente.

Voici les syntaxes de ces trois fonctions:

=CENTILE(matrice; k)
=CENTILE.EXCLURE(matrice; k)
=CENTILE.INCLURE(matrice; k)

Les arguments pour ces fonctions sont suivants:

  • matrice : La plage de cellules contenant les données numériques. Cet argument est obligatoire et doit être une série de valeurs numériques ou une référence à une telle série.
  • k : Le centile à calculer. Ce doit être une valeur décimale comprise entre 0 et 1. Par exemple, utiliser 0,3 pour le 30ème centile. Cet argument est également obligatoire.

Ces fonctions permettent une analyse statistique détaillée et précise des données. Elles offrent la possibilité de calculer des centiles en incluant ou excluant les valeurs extrêmes de l'ensemble de données.

Pour information, voici les équivalents anglais de ces trois fonctions: PERCENTILE, PERCENTILE.EXC et PERCENTILE.INC

Exclure ou inclure? – Un peu de théorie

La différence principale entre les fonctions CENTILE.INCLURE et CENTILE.EXCLURE réside dans leur manière de traiter les valeurs extrêmes de l'ensemble de données.

En général, il est conseillé d'utiliser PERCENTILE.INC. Cette fonction est conforme aux méthodes utilisées dans les langages de programmation tels que R et Python, et elle est plus adaptée à la plupart des cas d'utilisation. Cependant, la différence entre les résultats des deux fonctions est souvent mineure. Et elles peuvent même produire des résultats identiques dans certains cas, en fonction de la séquence des nombres dans l'ensemble de données​​.

Pour ceux qui souhaitent en savoir plus sur la différence entre ces deux fonctions, je vous invite à consulter le contenu détaillé dans le spoiler ci-dessous. Cette information supplémentaire vous aidera à comprendre quand utiliser chacune de ces fonctions pour vos analyses de données.

Le détail des différences (cliquez pour afficher)
Les fonctions Excel CENTILE.INCLURE et CENTILE.EXCLURE utilisent des méthodes différentes pour calculer les percentiles, ce qui peut conduire à des résultats distincts en fonction de l’ensemble de données et de la valeur du percentile (k) spécifiée.

La fonction CENTILE.INCLURE inclut toute la plage de 0 à 1 comme valeurs valides pour k. Cela signifie qu’elle considère l’ensemble complet des données lors du calcul du percentile. Par exemple, si vous utilisez une valeur de k de 0,4, cela signifie que 40 % des valeurs de votre ensemble de données sont inférieures ou égales au percentile calculé. Cette fonction interpole entre les valeurs de l’ensemble de données pour calculer des percentiles qui ne correspondent pas directement à un point de données spécifique. Ainsi, elle fournit une valeur de percentile qui pourrait être une valeur non existante dans l’ensemble de données mais représente la position du percentile.

D’autre part, CENTILE.EXCLURE, est conçue pour être exclusive dans ses calculs de percentiles. Elle exclut certaines valeurs de percentiles, spécifiquement celles en dessous de 1/(N+1) et au-dessus de N/(N+1), où N est la taille de l’ensemble de données. Cette exclusion signifie que CENTILE.EXCLURE choisit généralement une valeur plus éloignée de la moyenne de l’ensemble de données par rapport à CENTILE.INCLURE. La nature exclusive de cette fonction signifie qu’elle ne calcule pas les percentiles pour certains petits ensembles de données ou valeurs de percentile extrêmes, renvoyant une erreur #NOMBRE! si k est inférieur à 1/(N+1) ou supérieur à N/(N+1).

Pour clarifier, “en dessous de 1/(N+1)” signifie que pour un ensemble de données de taille N, la fonction exclut le calcul des percentiles pour les valeurs de k qui sont inférieures à la proportion représentant un partage équitable de l’ensemble de données plus un. Par exemple, si N est 100, CENTILE.EXCLURE n’utilisera pas les valeurs de k inférieures à 1/101, soit environ 0,0099. De même, “au-dessus de N/(N+1)” signifie que pour des valeurs de k supérieures à cette proportion, la fonction n’effectuera pas le calcul. En reprenant l’exemple précédent, cela serait pour des valeurs de k supérieures à 100/101, soit environ 0,9901.

En résumé, tandis que CENTILE.INCLURE est inclusive et interpole pour fournir une valeur de percentile représentant l’ensemble complet des données, CENTILE.EXCLURE est exclusive et peut omettre certains calculs de percentile, en particulier pour les petits ensembles de données ou les valeurs extrêmes de k.

Utilisation pratique de la fonction CENTILE.INCLURE

La fonction CENTILE.INCLURE est extrêmement utile pour analyser la distribution des données dans un ensemble. Elle est souvent utilisée en statistiques pour déterminer des seuils ou pour comprendre comment les données se répartissent.

Voici quelques exemples pratiques:

  • Exemple avec une plage de cellules: Supposons que vous ayez les scores d'un test dans les cellules A1 à A10 et que vous vouliez trouver le 80ème percentile de ces scores. La formule serait :
    =CENTILE.INCLURE(A1:A10; 0,8)
  • Exemple avec des valeurs spécifiques: Si vous avez un ensemble de données spécifiques, par exemple [3, 5, 7, 8, 9, 10], et que vous souhaitez calculer le 50ème percentile (médiane), vous pouvez utiliser la formule:
    =CENTILE.INCLURE({3; 5; 7; 8; 9; 10}; 0,5)
  • Utilisation de l'argument "k" en pourcentage: Cette fonction permet d'utiliser l'argument "k" soit sous forme décimale (par exemple 0,8), soit en pourcentage (par exemple 80%). Voici un exemple de chaque:
    En format décimal: =CENTILE.INCLURE(A1:A10; 0,8) pour calculer le 80ème percentile.
    En format pourcentage: =CENTILE.INCLURE(A1:A10; 80%) pour calculer le 80ème percentile.

Ces exemples montrent comment utiliser cette fonction de manière flexible. Que ce soit avec des plages de cellules ou des ensembles de données définis.

Exemple d'utilisation de la fonction CENTILE dans Excel
Exemple d'utilisation de la fonction dans Excel

 

Exemple avec les valeurs directement dans la fonction
Exemple avec les valeurs directement dans la fonction

 

Problèmes courants et erreurs possibles avec les fonctions CENTILE

L'utilisation des fonctions CENTILE.INCLURE et CENTILE.EXCLURE peut parfois entraîner des erreurs spécifiques en raison de leur nature et de la manière dont elles traitent les données. Voici un récapitulatif des problèmes courants et des erreurs que vous pourriez rencontrer avec ces deux fonctions :

  • Erreur #VALEUR!: Cette erreur se produit généralement lorsque les arguments ne sont pas correctement spécifiés. Si l'argument "matrice" ne contient pas de valeurs numériques ou est laissé vide, ou si les données fournies ne sont pas numériques, Excel ne peut pas calculer le centile et renvoie une erreur #VALEUR!.
  • Erreur #NOMBRE!: Cette erreur est fréquente avec CENTILE.EXCLURE lorsqu'il est utilisé avec une valeur de "k" hors de la plage acceptable. Pour CENTILE.INCLURE, cette erreur se produit si "k" est en dehors de la plage de 0 à 1. Avec CENTILE.EXCLURE, cette erreur survient si "k" est inférieur à 1/(N+1) ou supérieur à N/(N+1), où N est la taille de l'ensemble de données.
  • Sélection de "k" inappropriée: Une erreur courante est de choisir un "k" qui n'est pas adapté à l'ensemble de données, en particulier pour CENTILE.EXCLURE. Choisir un "k" trop proche de 0 ou de 1 peut conduire à des résultats biaisés ou non représentatifs.
  • Problèmes de cohérence des données: Si la plage de données contient des valeurs incohérentes ou inattendues, les résultats des fonctions peuvent être incorrects ou trompeurs. Assurez-vous que les données sont propres et cohérentes avant d'effectuer le calcul.

En comprenant et en anticipant ces erreurs, vous pouvez utiliser ces fonctions de manière plus efficace et précise dans vos analyses de données.

Conseils pratiques pour un calcul sans soucis

Pour garantir une utilisation pratique sans erreur des deux fonctions qui sont le sujet de ce tutoriel, voici quelques conseils pratiques:

  • Vérifiez vos données: Assurez-vous que la plage de données que vous utilisez est appropriée pour l'analyse. Les données doivent être numériques et pertinentes pour le calcul du centile.
  • Soyez précis avec la valeur de k: La valeur de "k" doit être choisie avec soin. Elle doit être une valeur décimale entre 0 et 1 pour CENTILE.INCLURE, et entre 1/(N+1) et N/(N+1) pour CENTILE.EXCLURE. Assurez-vous que la valeur de "k" est adaptée à votre analyse.
  • Utilisation de plages continues: Lors de la sélection des plages de données pour le calcul du centile, utilisez des plages continues sans cellules vides pour éviter des erreurs de calcul.
  • Comprendre la différence entre les deux fonctions: Gardez à l'esprit que CENTILE.INCLURE est plus inclusif et convient mieux pour la plupart des analyses standards, tandis que CENTILE.EXCLURE peut être utilisé pour des analyses plus spécifiques où les valeurs extrêmes doivent être exclues.
  • Tester avec différentes valeurs de k: Parfois, il peut être utile de tester votre ensemble de données avec différentes valeurs de "k" pour mieux comprendre la distribution de vos données.

En suivant ces conseils, vous devriez être en mesure d'utiliser ces fonctions de manière plus efficace et d'éviter les erreurs courantes liées à leur utilisation.

Vers une maîtrise parfaite des centiles

Avec cet article, nous avons plongé dans les subtilités des fonctions CENTILE.INCLURE et CENTILE.EXCLURE, explorant leur syntaxe, utilisation pratique, erreurs courantes et des conseils pour une utilisation optimale. Ces outils, bien que simples en apparence, ouvrent la porte à une analyse statistique riche et nuancée.

Pour aller plus loin en Excel

Et comme d'habitude, je me permets de vous proposer quelques articles supplémentaires. Ils pourraient vous être utiles et qui pourraient rendre votre travail avec Excel plus efficace et plus efficient…

Auteur

Martin Hudec ( Excel-Malin.com )
Je suis analyste et développeur de systèmes, spécialisé dans l'automatisation des processus. Fort de nombreuses années d'expérience dans des institutions financières de renom, j'ai acquis une expertise approfondie en Excel et VBA. Créateur d'Excel-Malin.com, l'un des plus grands sites web francophones dédiés à Excel et VBA, je partage mes connaissances pour rendre ces outils accessibles à tous. En complément de mes compétences en Excel et VBA, je me suis tourné vers l'Intelligence Artificielle, notamment dans le domaine de l'automatisation et de l'application pratique dans les entreprises. Mon approche reste toujours pratique et orientée résultats, sans oublier ma passion pour simplifier les processus quotidiens avec des solutions efficaces.

Vous pourriez aimer également

Laissez un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Commentaire sur “Fonction CENTILE Excel: Tutoriel Facile et Détaillé”