Comment calculer les percentiles dans Excel

Comment calculer les percentiles dans Excel

Excel propose deux fonctions principales pour calculer les percentiles : PERCENTILE.INC et PERCENTILE.EXC.

À proprement parler, il existe également la troisième fonction - PERCEENTILE - 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 fonctions les plus récentes - PERCENTILE.INC et PERCENTILE.EXC.

PERCENTILE.INC Fonction Excel

Syntax

PERCENTILE.INC(tableau, k)

où :

• tableau -tableau ou plage de données pour lequel le percentile est calculé,
• k - lavaleur du percentile dans l'intervalle de 0 à 1 inclus.

Les deux arguments sont nécessaires.

La fonction PERCENTILE.INC 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

Sous le capot, la fonction Excel PERCENTILE.INC exécute les étapes suivantes :

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

Exemple

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

Note: Bien que les valeurs du tableau ci-dessus soient triées à des fins d'illustration, nous n'avons pas besoin de trier les valeurs à l'avance puisque l'algorithme le fera pour nous en coulisses.

Vous trouverez ci-dessous le résultat de nos calculs :

Erreurs possibles

• #NUM!- lorsque le tableau fourni est vide, ou k < 0, ou k > 1.
• #VALUE!-lorsque k n'est pas numérique.

PERCENTILE.EXC Fonction Excel

Syntax

PERCENTILE.EXC(tableau, k)

où :

• tableau -tableau ou plage de données pour lequel le percentile est calculé,
• k-lavaleur du percentile dans l'intervalle de 0 à 1, exclusivement.

Les deux arguments sont nécessaires.

La fonction PERCENTILE.EXC renvoie le k-ième percentile 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 d'autres termes, la fonction PERCENTILE.EXC exclut la première et la dernière valeur du tableau.

Algorithme

Sous le capot, la fonction Excel PERCENTILE.EXC effectue les étapes suivantes :

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

Exemple

Supposons que nous voulions calculer le 30e centile pour un tableau d'entiers de 1 à 10 inclus, en utilisant la fonction Excel PERCENTILE.EXC. Voici à quoi ressemblera notre formule :

Note: Bien que les valeurs du tableau ci-dessus soient triées à des fins d'illustration, nous n'avons pas besoin de trier les valeurs à l'avance puisque l'algorithme le fera pour nous en coulisses.

Vous trouverez ci-dessous le résultat de nos calculs :

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 numérique.

Pour apprendre ou rafraîchir d'autres fonctions Excel populaires, consultez ce tutoriel : Les 15 formules de base d'Excel que tout le monde doit connaître.

PERCENTILE.INC VS. PERCENTILE.EXC dans Excel

Jusqu'à présent, nous pouvons clairement constater de nombreuses similitudes entre les fonctions Excel PERCENTILE.INC et PERCENTILE.EXC. Cependant, il existe également des différences marquées entre ces deux fonctions, qui en font deux fonctions distinctes et aboutissent à des résultats différents. Récapitulons les similitudes et les différences entre les fonctions PERCENTILE.INC et PERCENTILE.EXC dans Excel.

Similitudes:

1. Syntaxe de la fonction.
2. Logique globale de l'algorithme (à l'exception de la formule utilisée pour déterminer la valeur du percentile).
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 numérique).

Différences:

1. Plage possible pour l'argument k :
• PERCENTILE.INC - de 0 à 1, inclus.
• PERCENTILE.EXC - de 1/(N+1) à N/(N+1), exclusif.
2. Traitement des valeurs d'un tableau :
• PERCENTILE.INC - comprend la première et la dernière valeur.
• PERCENTILE.EXC - exclut la première et la dernière valeur.
3. Formule pour déterminer la valeur du percentile :
• PERCENTILE.INC—k(N-1) + 1.
• PERCENTILE.EXC—k(N+1).

Voyons les fonctions Excel PERCENTILE.INC et PERCENTILE.EXC en action en les appliquant toutes deux au même tableau de nombres entiers de 1 à 10 inclus et en jouant avec différentes valeurs de k :

Le tableau ci-dessus montre les limites de la fonction PERCENTILE.EXP lorsqu'il s'agit de calculer les percentiles pour des valeurs trop faibles ou trop élevées de k (plus précisément, lorsque k <= 1/(N+1) ou k >= N/(N+1)), ainsi que les différences par paire dans les résultats pour chaque valeur de k qui augmentent vers les valeurs extrêmes de k et peuvent être expliquées par les différences de calcul des algorithmes sous-jacents.

D'autre part, il faut garder à l'esprit que dans la grande majorité des cas, des valeurs trop faibles ou trop élevées de k n'ont pas de valeur particulière pour nous du point de vue des statistiques. Par conséquent, ces limitations ne posent normalement pas de problème. En outre, dans la vie réelle, nous devons généralement calculer des percentiles 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.

Toutefois, si, pour une raison quelconque, nous ne faisons pas confiance aux premières et dernières valeurs de notre ensemble de données, les considérant comme statistiquement non représentatives, la fonction PERCENTILE.EXP peut être un meilleur choix puisqu'elle exclut ces valeurs des calculs.

Il est important de disposer de données propres et représentatives pour en extraire des informations commerciales précieuses. Le cours sur la préparation des données dans Excel et l'aide-mémoire sur la manipulation des données dans Excel sont 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 essentielle des percentiles, leur définition, les principaux domaines d'application et les deux principales fonctions utilisées pour calculer les percentiles dans Excel, y compris leur syntaxe, leurs algorithmes, les exemples de calcul et les erreurs potentielles.

Nous avons également comparé les deux fonctions Excel, souligné leurs similitudes et leurs différences importantes, mis les deux fonctions en action sur le même tableau de données pour confronter les résultats, et appris le meilleur scénario pour chacune d'entre elles.

Pour exploiter toute la puissance d'Excel, envisagez de suivre un cursus de compétences sur Excel Fundamentals, adapté aux débutants, qui vous aidera à apprendre, par le biais d'exercices pratiques, les fonctions et formules de base et avancées utilisées pour la préparation, la visualisation et l'analyse des données dans Excel.