Fonctions Excel PERCENTILE - Calculer le kième centile

Télécharger un exemple de classeur

Téléchargez l'exemple de classeur

Ce didacticiel montre comment utiliser le Fonction PERCENTILE Excel dans Excel pour calculer le centile.

Présentation de la fonction PERCENTILE

La fonction PERCENTILE Calcule le kième percentile.

Pour utiliser la fonction de feuille de calcul Excel PERCENTILE, sélectionnez une cellule et tapez :

(Remarquez comment les entrées de formule apparaissent)

Fonction PERCENTILE Syntaxe et entrées :

1	=CENTILE(tableau,k)

déployer - Un tableau de nombres.

k - La valeur centile comprise entre 0 et 1.

Qu'est-ce que la fonction PERCENTILE ?

PERCENTILE est l'une des fonctions statistiques d'Excel. Il renvoie le k-ème centile d'une plage de données donnée.

PERCENTILE est une fonction de « compatibilité »

À partir d'Excel 2010, Microsoft a remplacé PERCENTILE par deux variantes : PERCENTILE.INC et PERCENTILE. EXC.

PERCENTILE fonctionne toujours, donc les anciennes feuilles de calcul qui l'utilisent continueront de fonctionner normalement. Cependant, si vous n'avez pas besoin que votre feuille de calcul reste compatible avec les anciennes versions d'Excel, vous devez utiliser PERCENTILE.INC ou PERCENTILE.EXC.

Nous discuterons des trois afin que vous compreniez les tenants et aboutissants de chacun.

Qu'est-ce que le centile ?

Le centile vous indique comment une valeur se compare aux autres valeurs dans une plage donnée. Lorsqu'une valeur est au k-ème centile, cela signifie qu'elle est supérieure à k% des autres valeurs du groupe.

Par exemple, si vous êtes un homme vivant aux États-Unis et que vous mesurez 6 pieds (180 cm), vous êtes dans le 71e centile - vous êtes plus grand que 71% des autres hommes aux États-Unis. Si vous êtes une femme et mesurez 180 cm, vous êtes dans le 99^e centile - vous êtes plus grand que 99% des autres femmes.

Lorsque vous traitez des centiles, notez que les 50^e centile est égal à la médiane <> des données.

La fonction Excel PERCENTILE vous indique quel est le point de coupure pour un centile donné, c'est-à-dire à quelle hauteur une valeur doit être pour être dans le k-ème centile.

Comment utiliser la fonction PERCENTILE

Utilisez PERCENTILE comme ceci :

1	=CENTILE(C4:C13,F3)

Ici, nous avons quelques amis qui ont décidé de calculer les 80^e centile de la hauteur de leur groupe, et nous voyons le résultat - 183,4 cm.

Le premier argument de la fonction est la plage de données - leurs hauteurs, que nous avons présentées dans C4:C13.

L'argument suivant est le centile. Dans cet exemple, j'ai mis le centile dans la cellule F3, puis j'ai référencé cette cellule dans la fonction. De cette façon, il est facile de modifier le centile et de voir les nouveaux résultats.

Cependant, vous pouvez mettre le centile directement dans la formule si vous le souhaitez. Vous pouvez le définir comme un nombre décimal, comme ceci :

1	=CENTILE(C4:C13,.8)

Ou en pourcentage, comme ceci :

1	=CENTILE(C4:C13,80 %)

Et ceux-ci renvoient tous la même valeur.

Quelques points à prendre en compte avec PERCENTILE :

• Si le centile que vous définissez n'est pas un nombre, la fonction renverra une #VALUE ! Erreur
• Si le centile est inférieur à 0 ou supérieur à 1, vous obtiendrez un #NUM ! Erreur
• Le texte et les cellules vides de votre plage de données seront ignorés

Deux méthodes de calcul des centiles

Mettons nos données en ordre pendant une seconde :

Vous vous demandez peut-être pourquoi les années 80^e centile n'est pas 183, puisque Chandler est le 8^e personne la plus grande du groupe.

Eh bien, il existe en fait de nombreuses façons de calculer le centile mathématiquement, et aucune méthode standardisée ou préférée pour toutes les situations. C'est quelque chose que les statisticiens discutent dans les articles universitaires.

Excel vous propose deux manières de procéder, ce qui nous amène à PERCENTILE.INC et PERCENTILE.EXC.

PERCENTILE.INC

La fonction Excel PERCENTILE.INC est la même que PERCENTILE. La partie « INC » est l'abréviation d'inclusif, car elle peut calculer n'importe quel centile valide (c'est-à-dire n'importe quoi entre 0 % et 100 %).

Vous l'utilisez comme ceci :

1	=CENTILE.INC(C4:C13,F3)

Comme vous pouvez le voir, il renvoie le même résultat que PERCENTILE a fait plus tôt.

Alors, revenons à pourquoi les années 80^e centile n'est pas exactement égal à la taille de Chandler. N'oubliez pas que nous effectuons ici un calcul inclusif, nous incluons donc toutes les valeurs k de 0 à 1 (ou de 0 % à 100 %).

Cela signifie que notre plus petite amie, Janice, est au 0^e centile, et Richard, le plus grand, est le 100^e centile. Tous les autres amis sont à des incréments égaux entre les deux, et cet incrément est égal à 1/(n-1), où n est le nombre de points de données dans la plage.

Dans notre cas, c'est :

1	1 / (10 - 1) = 11.111…

Cela signifie que Chandler n'est pas réellement au 80^e centile, il est au 77,777^e centile. Si nous insérons ce nombre dans PERCENTILE.INC, nous devrions obtenir la taille de Chandler…

… et nous le faisons.

Interpolation linéaire

Désormais, chaque fois que la valeur k que vous spécifiez n'est pas un multiple de 1/(n-1), PERCENTILE.INC appliquera un processus appelé interpolation linéaire pour calculer le résultat. Cela semble compliqué, mais cela signifie essentiellement qu'Excel choisira une valeur entre les deux.

Alors pourquoi avons-nous obtenu 183,4 lorsque nous avons demandé les 80^e centile plus tôt?

Le calcul fonctionne comme suit :

• Les 80^e le centile tombe entre Chandler et Ross, Chandler au 77,777^e centile, et Ross au 88,888e.
• Les 80^e centile est de 2,222% devant la taille de Chandler
• Nous savons qu'il y a une distance de 11% entre les hauteurs de Chandler et Ross
• 222% / 11,111% = 20% après arrondi. On sait maintenant que les années 80^e centile est de 20% de la distance entre les hauteurs de Ross et Chandler.
• La différence entre la taille de Ross de 185 cm et celle de Chandler de 183 cm est de 2 cm
• 20% de 2cm est 0.4cm
• Ajoutez cela à la taille de Chandler, et nous obtenons 183,4 cm

POURCENTILE.EXC

Vous utilisez PERCENTILE.EXC essentiellement de la même manière :

1	=CENTILE.EXC(C4:C13,F3)

D'accord, nous avons donc les mêmes données, nous voulons le même centile, mais nous avons des résultats différents. Pourquoi donc?

C'est parce que PERCENTILE.EXC exclut les première et dernière valeurs lors du calcul de ses intervalles de centiles. Voir ci-dessous:

Au lieu que l'intervalle soit égal à 1/(n-1), avec PERCENTILE.EXC l'intervalle est 1/(n+1), soit dans ce cas 9,091%.

Tout le reste fonctionne de la même manière qu'avec PERCENTILE.INC. Encore une fois, les 80^e centile est entre Chandler et Ross, donc Excel applique la même méthode d'interpolation linéaire :

• Les 80^e le centile tombe entre Chandler et Ross, Chandler au 72,727^e centile, et Ross au 81,818e.
• Les 80^e centile est de 7,272% devant la taille de Chandler
• Nous savons qu'il y a une distance de 9,091% entre les hauteurs de Chandler et Ross
• 272% / 9,091% = 80% après arrondi. On sait maintenant que les années 80^e centile est de 80% du chemin entre les hauteurs de Ross et Chandler.
• La différence entre la taille de Ross de 185 cm et celle de Chandler de 183 cm est de 2 cm
• 80% de 2cm est 1,6cm
• Ajoutez cela à la taille de Chandler, et nous obtenons 184,6 cm

PERCENTILE dans Google Sheets

La fonction PERCENTILE fonctionne exactement de la même manière dans Google Sheets que dans Excel :

PERCENTILE Exemples en VBA

Vous pouvez également utiliser la fonction PERCENTILE dans VBA. Taper:
application.worksheetfunction.percentile(array,k)
Pour les arguments de la fonction (tableau, etc.), vous pouvez soit les saisir directement dans la fonction, soit définir des variables à utiliser à la place.

Revenir à la liste de toutes les fonctions dans Excel