Classement avec RANK et PERCENTILE

Souvent, vous ne voulez pas seulement connaître la valeur, mais aussi sa position au sein du groupe. Ce commercial est-il premier ou douzième ? Cette note se situe-t-elle dans les 10 % les plus élevés ?

Deux familles de fonctions répondent à ces questions : RANK indique la position ordinale, tandis que PERCENTILE indique la valeur correspondant à un seuil donné. Ensemble, elles transforment les nombres bruts en classements et en références.

RANK.EQ donne la position d’une valeur dans une plage. Vous fournissez la valeur, la plage et un indicateur d’ordre.

• L’ordre 0 (ou omis) classe de la plus grande à la plus petite valeur (ordre décroissant).
• L’ordre 1 classe de la plus petite à la plus grande valeur (ordre croissant).

Ici, le chiffre d’affaires le plus élevé obtient le rang 1.

Remarquez les signes dollar : $B$2:$B$20. La plage doit rester fixe lorsque vous recopiez la formule vers le bas, tandis que la référence de valeur B2 devient B3, B4, etc.

Si vous oubliez les signes dollar, la plage se déplace à chaque ligne et produit des rangs incohérents. Les références absolues de la plage sont essentielles ici.

RANK.EQ attribue le même rang aux valeurs à égalité, puis saute le rang suivant. Si deux valeurs sont à égalité au deuxième rang, elles affichent toutes deux 2 et la suivante obtient 4 (il n’y a pas de troisième place).

Si vous préférez obtenir le rang moyen en cas d’égalité, utilisez RANK.AVG. Deux valeurs à égalité pour les deuxième et troisième rangs afficheraient chacune 2,5.

Pour imposer des rangs uniques même en cas d’égalité, ajoutez un critère de départage avec COUNTIF. Il compte le nombre de valeurs égales situées au-dessus de la ligne actuelle et ajoute ce décalage.

Cela garantit une séquence nette 1, 2, 3, 4 sans doublons, ce qui est utile pour les tableaux de classement.

PERCENTILE.INC répond à la question suivante : quelle valeur correspond à un pourcentage donné ? Fournissez la plage et une fraction comprise entre 0 et 1.

La formule ci-dessous renvoie le 90e centile, c’est-à-dire la valeur en dessous de laquelle se trouvent 90 % des données. Elle est idéale pour définir des seuils comme « les 10 % de résultats les plus élevés ».

Les quartiles divisent les données en quatre parties. La fonction QUARTILE.INC est un raccourci : le quartile 1 correspond au 25e centile, le quartile 2 au 50e (la médiane) et le quartile 3 au 75e.

La formule ci-dessous renvoie le troisième quartile, identique à PERCENTILE.INC(A2:A101,0.75).

Il existe deux variantes : PERCENTILE.INC (inclusive, accepte 0 et 1) et PERCENTILE.EXC (exclusive, uniquement entre 0 et 1, sans jamais prendre les valeurs extrêmes).

Pour la plupart des rapports courants, .INC est le choix standard. Utilisez .EXC lorsque vous suivez des conventions statistiques qui excluent les valeurs limites.

RANK donne une position ; PERCENTILE donne une valeur correspondant à un seuil. PERCENTRANK.INC fait l’inverse de la fonction de centile : étant donnée une valeur, à quel centile correspond-elle ?

Si un élève a obtenu 88 et que PERCENTRANK renvoie 0,92, cet élève a obtenu un meilleur résultat que 92 % du groupe. Multipliez par 100 pour l’afficher sous forme de pourcentage.

RANK et PERCENTILE fonctionnent uniquement sur les nombres. Les libellés textuels et les cellules vides de la plage sont ignorés : un en-tête isolé ne perturbera donc pas votre classement.

Méfiez-vous toutefois des nombres stockés sous forme de texte : ils sont entièrement ignorés, ce qui décale tous les rangs et percentiles. Si un rang semble incorrect, vérifiez que toutes vos valeurs sont bien de vrais nombres et non du texte aligné à gauche.

Vous avez 20 représentants dont les ventes se trouvent dans la colonne B. Vous souhaitez obtenir le rang de chaque représentant et savoir s’il se trouve dans le quartile supérieur.

• Rang : =RANK.EQ(B2,$B$2:$B$21,0)
• L’indicateur du quartile supérieur compare leurs ventes au 75e percentile.

La formule ci-dessous renvoie « Top » ou « Standard » pour chaque représentant selon le seuil.

Testez votre compréhension des fonctions de classement.

Vous avez appris à positionner des valeurs au sein d’un groupe :

• RANK.EQ donne la position ordinale ; l’ordre 0 place d’abord la plus grande valeur, tandis que 1 place d’abord la plus petite. Verrouillez la plage avec $.
• RANK.AVG calcule la moyenne des rangs ex æquo ; ajoutez COUNTIF pour obtenir des rangs uniques.
• PERCENTILE.INC renvoie la valeur correspondant à un seuil ; QUARTILE.INC est un raccourci pour les seuils 25/50/75.
• PERCENTRANK.INC effectue l’opération inverse : il renvoie le percentile d’une valeur donnée.