Comment calculer des centiles dans Excel

Comment calculer des centiles dans Excel

Excel propose deux fonctions principales pour calculer les centiles : PERCENTILE.INC() et PERCENTILE.EXC().

À proprement parler, il existe également une troisième fonction,PERCENTILE(), qui était utilisée dans les anciennes versions du logiciel (Excel 2007 et antérieures) et qui est désormais conservée à des fins de compatibilité. Cette ancienne fonction fonctionne de la même manière que PERCENTILE.INC().

Dans ce tutoriel, nous allons nous concentrer sur les nouvelles fonctions :PERCENTILE.INC() et PERCENTILE.EXC().

Fonction Excel PERCENTILE.INC()

Syntax

PERCENTILE.INC(array, k)

où :

• array—l'ensemble ou la plage de données pour lesquels le centile est calculé,

• k— la valeur centile comprise entre 0 et 1 inclus.

Les deux arguments sont obligatoires.

La fonction PERCENTILE.INC() e renvoie le k-ième centile des valeurs d'un intervalle, où k est compris entre 0 et 1, y compris les valeurs 0 et 1. En d'autres termes, cette fonction inclut la première et la dernière valeur du tableau.

Algorithme

En arrière-plan, la fonction Excel « PERCENTILE.INC() » effectue les étapes suivantes :

1. Trie les valeurs du tableau fourni par ordre croissant.
2. Détermine la valeur centile en fonction de l'argument k et du nombre N de points de données dans le tableau à l'aide de la formule suivante : k(N-1) + 1.
3. Si le résultat de l'étape précédente n'est pas un nombre entier, l'algorithme procède à une interpolation entre les valeurs les plus proches du tableau.

Exemple

Supposons que nous souhaitions calculer le 30e centile d'un tableau d'entiers compris entre 1 et 10 inclus, à l'aide de la fonction Excel PERCENTILE.INC(). Voici à quoi ressemblera notre formule :

Remarque: Bien que les valeurs du tableau ci-dessus soient triées à des fins d'illustration, il n'est pas nécessaire de les trier à l'avance, car l'algorithme le fera pour nous en arrière-plan.

Ci-dessous se trouve le résultat de notre calcul :

Erreurs possibles

• #NUM!— lorsque le tableau fourni est vide, ou k < 0, ou k > 1.

• #VALUE!— lorsque k n'est pas un nombre.

Fonction Excel PERCENTILE.EXC()

Syntax

PERCENTILE.EXC(array, k)

où :

• array—l'ensemble ou la plage de données pour lesquels le centile est calculé,

• k— la valeur centile comprise entre 0 et 1, exclusivement.

Les deux arguments sont obligatoires.

La fonction PERCENTILE.EXC() e renvoie le k-ième centile des valeurs d'un intervalle, où k est compris entre 0 et 1, à l'exclusion des valeurs 0 et 1. Plus précisément, pour un tableau comportant N points de données, la valeur de k doit satisfaire aux deux conditions suivantes :

1. K > 1/(N+1)
2. K < N/(N+1)

En termes simples, la fonction PERCENTILE.EXC() ne définit les centiles que pour les probabilités strictement comprises entre 0 et 1. Cela signifie qu'il ne calcule pas les valeurs pour le minimum absolu (0e centile) ou le maximum absolu (100e centile), bien que tous les points de données soient toujours utilisés dans l'interpolation.

Algorithme

En arrière-plan, la fonction Excel « PERCENTILE.EXC() » effectue les étapes suivantes :

1. Trie les valeurs du tableau fourni par ordre croissant.
2. Détermine la valeur centile en fonction de l'argument k et du nombre N de points de données dans le tableau à l'aide de la formule suivante : k(N+1).
3. Si le résultat de l'étape précédente n'est pas un nombre entier, l'algorithme procède à une interpolation entre les valeurs les plus proches du tableau.

Exemple

Supposons que nous souhaitions calculer le 30e centile d'un tableau d'entiers compris entre 1 et 10 inclus, à l'aide de la fonction Excel « PERCENTILE.EXC() ». Voici à quoi ressemblera notre formule :

Remarque: Bien que les valeurs du tableau ci-dessus soient triées à des fins d'illustration, il n'est pas nécessaire de les trier à l'avance, car l'algorithme le fera pour nous en arrière-plan.

Ci-dessous se trouve le résultat de notre calcul :

Erreurs possibles

• #NUM!— lorsque le tableau fourni est vide, ou k ≤ 1/(N+1), ou k ≥ N/(N+1).

• #VALUE!— lorsque k n'est pas un nombre.

Pour découvrir ou rafraîchir vos connaissances sur d'autres fonctions Excel courantes, veuillez consulter ce tutoriel : Les 15 formules Excel de base que tout le monde devrait connaître.

PERCENTILE.INC C. PERCENTILE.EXC dans Excel

Jusqu'à présent, nous pouvons clairement observer de nombreuses similitudes entre les fonctions Excel PERCENTILE.INC() et PERCENTILE.EXC(). Cependant, il existe également des différences distinctes entre elles qui en font deux fonctions distinctes et aboutissent à des résultats différents. Récapitulons les similitudes et les différences entre les fonctions Excel « PERCENTILE.INC() » et « PERCENTILE.EXC() ».

Similitudes:

1. Syntaxe de la fonction.

2. Logique générale de l'algorithme (à l'exception de la formule utilisée pour déterminer la valeur centile).

3. Erreurs possibles (#NUM! lorsque le tableau est vide ou que k est hors de sa plage possible et #VALUE! lorsque k n'est pas un nombre).

Différences:

1. Plage possible pour l'argument k :

• PERCENTILE.INC()—de 0 à 1 inclus.

• PERCENTILE.EXC()—de 1/(N+1) à N/(N+1), exclusivement.

2. Traitement des valeurs d'un tableau :

• PERCENTILE.INC()—inclut la première et la dernière valeur.

• PERCENTILE.EXC()—ne définit pas les centiles aux première et dernière valeurs (0 % et 100 %), mais inclut néanmoins toutes les données dans son interpolation.

3. Formule pour déterminer la valeur centile :

• PERCENTILE.INC()—k(N-1) + 1.

• PERCENTILE.EXC()—k(N+1).

Examinons les fonctions Excel ` PERCENTILE.INC() ` et ` PERCENTILE.EXC() ` en action en les appliquant toutes deux au même tableau de nombres entiers compris entre 1 et 10 inclus et en testant différentes valeurs de k :

Le tableau ci-dessus montre que la fonction PERCENTILE.EXC() n'est pas définie pour les valeurs k situées à l'extérieur de sa plage valide (k ≤ 1/(N+1) ou k ≥ N/(N+1)). Ce n'est pas un défaut. Il reflète la définition mathématique des centiles exclusifs. Les légères différences entre les résultats obtenus par .INC() et .EXC() à proximité des extrêmes proviennent de la manière dont chaque fonction interpole les positions des centiles.

D'autre part, il convient de garder à l'esprit que dans la grande majorité des cas, des valeurs trop faibles ou trop élevées de k ne présentent pas d'intérêt particulier pour nous d'un point de vue statistique. Ces restrictions posent rarement problème dans la pratique. De plus, dans la réalité, nous devons généralement calculer les centiles pour des tableaux beaucoup plus grands que celui de notre exemple ci-dessus, ce qui atténue les différences de résultats.

Dans la plupart des cas, il est plus judicieux d'opter pour la fonction Excel « PERCENTILE.INC() », qui correspond également aux formules par défaut utilisées pour calculer les centiles dans de nombreux langages de programmation, notamment Python et R.

Cependant, si nous souhaitons éviter de définir des centiles aux extrémités de l'ensemble de données, la fonction PERCENTILE.EXC() constitue le choix approprié. Il ne calculera pas les résultats pour le 0e ou le 100e centile, mais utilisera néanmoins tous les points de données lors de l'interpolation entre les rangs.

Il est essentiel de disposer de données propres et représentatives afin d'en extraire des informations commerciales précieuses. Le cours « Préparation des données dans Excel » et l'aide-mémoire « Manipulation des données dans Excel » constituent des ressources utiles pour apprendre à préparer efficacement vos données en vue d'une analyse ultérieure.

Conclusion

Nous avons abordé la théorie statistique fondamentale qui sous-tend les centiles, leur définition, leurs principaux domaines d'application et les deux fonctions principales utilisées pour calculer les centiles dans Excel, y compris leur syntaxe, leurs algorithmes, des exemples de calcul et les erreurs potentielles.

Nous avons également comparé les deux fonctions Excel, souligné leurs similitudes et leurs différences importantes, appliqué les deux fonctions au même tableau de données afin de comparer les résultats, et déterminé le scénario le plus approprié pour chacune d'elles.

Pour exploiter pleinement le potentiel d'Excel, envisagez de suivre un cursus Excel Fundamentals adapté aux débutants, qui vous permettra d'apprendre, à travers des exercices pratiques, les fonctionnalités et formules de base et avancées utilisées pour la préparation, la visualisation et l'analyse des données dans Excel.