Accéder au contenu principal

Formule de classement dans Excel : Un guide complet avec des exemples

Apprenez à classer des données dans Excel avec les fonctions RANK(), RANK.EQ() et RANK.AVG(). Comprenez leurs différences, leurs applications et les conseils pour une analyse précise des données.
Actualisé 16 janv. 2025  · 30 min de lecture

Le classement vous permet de comparer les valeurs et d'identifier des modèles au sein de vos ensembles de données. Excel fournit des outils puissants tels que RANK(),RANK.EQ(), et RANK.AVG() pour faciliter le classement. Ces fonctions sont utiles dans toutes les situations, qu'il s'agisse de classer les participants d'un tournoi sportif en fonction de leurs points ou d'analyser les tendances du marché sur la base de données de vente classées.

Dans cet article, nous allons explorer les trois fonctions de classement dans Excel et expliquer comment vous pouvez les utiliser à l'aide d'exemples pratiques. Nous nous pencherons également sur l'utilisation des fonctions de classement avec d'autres fonctions Excel et sur le calcul des rangs centiles. 

La réponse rapide : Comment calculer le rang dans Excel 

Pour calculer les rangs dans les ensembles de données souhaités, utilisez la formule suivante :

=RANK(number, ref, [order])

Comprendre la fonction RANK() dans Excel

Supposons que vous ayez organisé un concours entre élèves et que vous souhaitiez classer des milliers d'élèves en fonction de leurs performances. 

Passeriez-vous des heures à trier des données ? Non, car Excel peut vous aider. Parmi ses nombreuses fonctions puissantes, la fonction RANK() vous permet de déterminer la position d'une personne en un seul clic.

Pour calculer le rang d'une valeur dans une plage de valeurs, entrez la formule :

RANK(number, ref, [order])

Dans la formule ci-dessus :

  • number: c'est le nombre que vous voulez classer.
  • refest la fourchette de nombres à prendre en compte pour le classement.
  • order: spécifie l'ordre de classement. Pour l'ordre croissant, utilisez "1" comme valeur ; pour l'ordre décroissant, utilisez "0" (valeur par défaut).

Comment utiliser la formule RANK() dans Excel ?

Mettons en œuvre la fonction RANK() sur des exemples concrets :

Classement des notes des étudiants

Le calcul des notes est un défi et prend du temps si vous avez des milliers d'étudiants. Supposons que vous prépariez les résultats finaux des étudiants et que vous souhaitiez les classer en fonction de leurs notes. 

Pour ce faire, préparez d'abord votre ensemble de données.

Tableau des élèves dans Excel.

Exemple de données d'étudiants dans Excel. Source : Image de l'auteur.

Entrez la formule =RANK(B3,($B$3:$B$7)) dans une cellule et faites-la glisser jusqu'à la dernière cellule remplie pour obtenir le rang.

Utilisation de la fonction RANK pour calculer le rang des étudiants.

Utilisation de la fonction RANK() pour calculer le rang des étudiants. Source : Image de l'auteur.

Vous pouvez désormais obtenir le rang de l'élève en un seul clic.

Mais avez-vous remarqué que deux étudiants ayant les mêmes notes ont le même rang de 4 ? L'élève suivant, avec 23 points, passe au rang 6, sautant le rang 5. Cela signifie que la formule attribue le même rang aux scores dupliqués. 

Cela peut perturber l'ordre des scores serrés. Mais vous pouvez réparer ces liens en utilisant la fonction COUNTIF() . Saisissez la formule suivante : 

=RANK(B3,($B$3:$B$8)) + COUNTIF($B$3:B3, B3)-1

La fonctionCOUNTIF() ajuste le rang pour les valeurs en double. Il compte combien de fois les notes de l'élève actuel apparaissent au-dessus de lui dans la liste.

Rompre les égalités à l'aide de la fonction COUNTIF dans Excel.

Rompre les égalités à l'aide de la fonction COUNTIF() dans Excel. Source : Image de l'auteur.

Une valeur de 28 apparaît deux fois, et le siteCOUNTIF() suit le nombre d'occurrences. Ce nombre d'occurrences est utilisé pour ajuster le rang. La formule ajoute le nombre d'occurrences moins un au rang initial. Cela permet de s'assurer que chaque valeur dupliquée reçoit un rang unique.

Classement des chiffres de vente dans un ensemble de données

Prenons un autre exemple pour montrer comment utiliser la fonction Excel RANK() par ordre croissant ou décroissant.

Si vous souhaitez évaluer les performances de votre équipe de vente, vous pouvez classer leurs chiffres de vente mensuels à l'aide de la fonction RANK() d'Excel.

Nous disposons ici d'un ensemble de données contenant les chiffres de vente mensuels de chaque vendeur. Nous utiliserons la fonction RANK() pour les classer en fonction du nombre de ventes réalisées (du plus élevé au moins élevé).

Liste des noms à classer dans Excel.

Liste des chiffres de vente à classer. Source : Image de l'auteur.

Pour classer les ventes par ordre croissant, nous avons appliqué la formule =RANK(B2, ($B$2:$B$6), 1). Ici, une valeur de "1" pour le paramètre order spécifie l'ordre croissant pour le classement.

Classement des valeurs par ordre croissant dans Excel.

Classement des valeurs par ordre croissant. Source : Image de l'auteur.

Une fois la formule saisie, il vous suffit de maintenir le curseur, de faire glisser le coin de la cellule vers le bas et de l'appliquer au reste de vos données de vente.

De même, si vous souhaitez classer les valeurs par ordre décroissant, écrivez "0" comme valeur de paramètre dans la formule, comme suit : "0". order dans la formule, comme ceci : =RANK(B2, ($B$2:$B$6), 0)

Vous pouvez voir la différence dans les résultats :

Classer les valeurs par ordre décroissant dans Excel.

Classement des valeurs par ordre décroissant. Source : Image de l'auteur.

Vous remarquerez peut-être les signes du dollar dans les références ($B$2:$B$6). Il s'agit de références absolues. Ils garantissent que la formule se réfère toujours à la même plage de données de vente, même lorsque vous la copiez, afin d'éviter les erreurs.

Si vous n'utilisez pas la référence absolue, par exemple =RANK(A2, A2:A10)la plage s'ajustera au fur et à mesure que vous faites glisser le curseur vers le bas :

  • Formule en G3 : =RANK(G3,(G3:G7), 1)
  • Formule en G4 : =RANK(G4,(G4:G8), 1)

Pour éviter ce problème, utilisez des références absolues en appuyant sur "F4". La formule est maintenant la suivante : =RANK(G3,($G$3:$G$7), 1)

Lorsque vous faites glisser la formule ci-dessus vers la dernière cellule remplie, les références relatives changent en fonction de la position de chaque cellule. Vous pouvez voir la différence dans les résultats lorsque vous utilisez des références absolues :

Calcul du rang avec et sans référence absolue dans Excel.

Différence entre les rangs avec et sans référence absolue. Source : Image de l'auteur.

Rang avancé Fonctions Excel : RANK.EQ() et RANK.AVG()

Lorsque vous rencontrez des valeurs dupliquées dans Excel, les choses peuvent devenir délicates. C'est là que je préfère utiliser les fonctions RANK.EQ() et RANK.AVG(). Les deux sont légèrement différents à manipuler. Voyons comment ils fonctionnent.

La fonction Excel RANK.EQ()

RANK.EQ() fonctionne comme la fonctionRANK(), mais avec un nom plus précis. Lorsqu'il y a des doublons, RANK.EQ() attribue le même rang à chacun d'entre eux et saute simplement le(s) rang(s) suivant(s).

Syntaxe : =RANK.EQ(number, ref, [order])

Dans la formule ci-dessus :

  • number: c'est le nombre que vous voulez classer.
  • refest la fourchette de nombres à prendre en compte pour le classement.
  • order: spécifie l'ordre de classement. Pour l'ordre croissant, utilisez "1" comme valeur ; pour l'ordre décroissant, utilisez "0" (valeur par défaut).

Comprenons cette formule à l'aide d'un exemple : Je disposais d'un ensemble de données contenant les populations de plusieurs pays et je souhaitais classer ces pays en fonction de leur population.

Voici comment j'ai procédé en utilisant la fonction RANK.EQ() fonction :

Utilisation de la fonction RANK.EQ dans Excel.

Utilisation de la fonction RANK.EQ(). Source : Image de l'auteur.

Dans l'image ci-dessus, le pays le plus peuplé est classé 1, et ainsi de suite. Vous voyez maintenant comment RANK.EQ() gère les cravates .

Comme les États-Unis et l'Indonésie ont des populations identiques, ils partagent le même rang (2). Cela montre clairement comment RANK.EQ() attribue le même rang aux doublons et saute le rang suivant dans la séquence.

La fonction Excel RANK.AVG()

La fonctionRANK.AVG() adopte une approche différente pour gérer les égalités dans les classements. Au lieu d'attribuer le même rang à tous les doublons, il calcule la moyenne des rangs qu'ils auraient reçus individuellement.

Syntaxe : RANK.AVG(number, ref, [order])

Dans la formule ci-dessus :

  • number: c'est le nombre que vous voulez classer.
  • refest la fourchette de nombres à prendre en compte pour le classement.
  • order: spécifie l'ordre de classement. Pour l'ordre croissant, utilisez "1" comme valeur ; pour l'ordre décroissant, utilisez "0" (valeur par défaut).

Comprenons-le à l'aide d'un exemple : Je disposais d'un ensemble de données de scores, j'ai donc appliqué la fonction RANK.AVG() pour voir comment elle gérait les scores ex aequo.

Utilisation de la fonction RANK.AVG dans Excel.

Utilisation de la fonction RANK.AVG(). Source : Image de l'auteur.

En utilisant =RANK.AVG(D3,$D$3:$D$6), le score de 88 reçoit un rang de 2,5. Cela signifie que la fonction établit une moyenne des rangs qu'elle aurait attribués individuellement. 

Cette approche permet de maintenir une distribution plus équilibrée des rangs, en particulier dans les ensembles de données comportant des liens fréquents.

Combiner RANK() avec d'autres fonctions Excel

L'utilisation de la fonctionRANK() avec d'autres fonctions Excel est utile dans certains cas. Passons en revue quelques cas d'utilisation courants.

Combiner RANK() et ROUNDUP() pour catégoriser les données

Voici comment vous pouvez combiner la fonction ROUNDUP() avec la fonction RANK() fonction :

  

Combinaison des fonctions RANK() et ROUNDUP() dans Excel.

Combinaison des fonctions RANK() et ROUNDUP() dans Excel. Source. Image de l'auteur.

J'ai utilisé la formule =ROUNDUP(RANK(B2, $B$2:$B$7, 1)/3, 0), qui calcule le rang de la valeur de la cellule B2 dans la plage B2 par ordre croissant. Il divise ensuite ce rang par trois et arrondit le résultat à l'entier supérieur.

Avec la formule ci-dessus, vous catégorisez efficacement les données en groupes ou niveaux distincts, ce qui peut permettre d'identifier et d'analyser différents niveaux de performance ou de catégoriser les données en vue d'une analyse plus approfondie.

Combinaison de RANK.EQ() et COUNTIFS() pour départager les ex-aequo

Nous avons abordé quelques façons de traiter les égalités à l'aide de la fonctionRANK() d'Excel . Toutefois, imaginons que vous souhaitiez classer les produits uniquement en fonction de leur prix. Cela peut sembler arbitraire lorsque plusieurs articles ont le même coût. 

Introduisons donc un critère secondaire pour départager les ex-aequo et créer un système de classement plus objectif.

Considérez une liste de clients qui ont acheté des écouteurs. Nous voulons les classer en fonction de leur prix (critère principal), puis utiliser l'évaluation des clients (critère secondaire) pour les départager en cas d'égalité.

Exemple de liste de clients dans Excel.

Exemple de liste de clients. Source : Image de l'auteur 

Pour ce faire, nous combinerons la fonction RANK.EQ() avec la fonctionCOUNTIFS(). Voici comment :

=RANK.EQ($B2,$B$2:$B$9) + COUNTIFS($B$2:$B$9, $B2, $C$2:$C$9, ">"& $C2)

Calculer le rang en utilisant RANK.EQ et COUNTIFS dans Excel.Calculer le rang en utilisant RANK.EQ() et COUNTIFS(). Source : Image de l'auteur.

La section COUNTIFS($B$2:$B$9,$B2)compte le nombre de fois où le prix actuel apparaît dans la liste. Nous utilisons des références absolues pour la plage, $B$2:$B$9, afin de maintenir la cohérence, mais nous gardons la cellule de critère, $B2, relative afin que la formule vérifie chaque ligne individuellement.

La deuxième partie, $C$2:$C$9,">"&$C2trouve le nombre d'évaluations de clients supérieures à l'évaluation de l'écouteur actuel, C2.

COUNTIFS() suit la logique "ET" : Il ne compte que les cellules qui remplissent toutes les conditions. Si aucun autre casque au même prix n'a une note supérieure, COUNTIFS() renvoie 0. C'est pourquoi le rang déterminé par RANK.EQ() reste inchangé.

Calculer le rang percentile dans Excel

Étant donné que la fonction RANK() classe une valeur numérique, vous pouvez utiliser une formule Excel de rang centile pour calculer le rang de la valeur dans un ensemble de données en tant que pourcentage de l'ensemble des données. 

Excel propose deux fonctions pour calculer les rangs centiles. Explorons les deux. 

La fonction Excel PERCENTRANK.INC()

PERCENTRANK.INC() calcule le rang centile d'une valeur, y compris les valeurs les plus petites et les plus grandes de l'ensemble de données.

Comprenons cette fonction à l'aide d'un exemple : Je disposais d'un ensemble de données représentant les notes d'examen de 10 étudiants et je souhaitais calculer le rang centile de chaque note, y compris les valeurs les plus faibles et les plus élevées. 

J'ai donc utilisé la formule suivante =PERCENTRANK.INC($A$2:$A$11, A2):

Calculer le rang en utilisant PERCENTRANK.INC dans Excel.

Calcul du rang à l'aide de la fonction PERCENTRANK.INC(). Source : Image de l'auteur

Dans l'exemple ci-dessus, vous pouvez voir que la fonctionPERCENTRANK.INC() inclut les valeurs les plus petites et les plus grandes de l'ensemble de données. Le score de 45 (valeur la plus faible) a un rang centile de 0, et le score de 90 (valeur la plus élevée) a un rang centile de 1.

La fonction Excel PERCENTRANK.EXC()

PERCENTRANK.EXC() calcule le rang centile d'une valeur, en excluant les valeurs les plus petites et les plus grandes de l'ensemble de données.

Comprenons cette fonction à l'aide d'un exemple :  J'ai appliqué la fonction PERCENTRANK.EXC() sur le même ensemble de données d'étudiants que dans la section précédente :

Calcul du rang à l'aide de la fonction PERCENTRANK.EXC() dans Excel.

Calcul du rang à l'aide de la fonction PERCENTRANK.EXC(). Source : Image de l'auteur

Dans l'exemple ci-dessus, la fonctionPERCENTRANK.EXC() exclut les valeurs les plus petites et les plus grandes de l'ensemble de données. Cela signifie que le score 45 (valeur la plus faible) n'a pas un rang centile de 0, et que le score 90 (valeur la plus élevée) n'a pas un rang centile de 1.

Les erreurs courantes de la fonction Excel RANK() et comment les éviter

Les fonctions d'Excel sont pratiques et faciles à utiliser pour tous les travaux professionnels. Mais parfois, même les utilisateurs les plus compétents peuvent commettre quelques erreurs courantes. C'est pourquoi je vous présente quelques-unes des erreurs les plus courantes et vous explique comment les éviter :

Copier-coller de formules

Lorsque vous copiez votre formule de classement d'une cellule à l'autre, veillez à ne pas utiliser accidentellement des références absolues. Chaque cellule peut faire référence à un ensemble de données différent et perturber votre classement. 

Pour éviter cela, utilisez des références absolues, comme $A$1:$A$10lors de l'élaboration de vos formules afin de verrouiller la plage correcte dans tous vos classements.

Ne pas traiter les doublons

Supposons que vous ayez deux vendeurs à égalité pour le plus grand nombre de ventes. La façon dont vous traitez les liens dans votre classement peut faire une grande différence : les fonctions RANK() et RANK.EQ() traitent les liens de la même façon.

Vous devez donc décider si les valeurs liées méritent le même rang ou un rang différent, ou si leur rang doit être moyenné à l'aide de RANK.AVG(). Utilisez ensuite la fonction appropriée. 

Mauvaise commande 

Cela semble simple, mais le fait d'oublier de définir l'argument order dans votre fonction peut entraîner un classement dans le mauvais sens (croissant ou décroissant).

C'est pourquoi vous devez toujours revérifier cet argument : Utilisez "0" (ou laissez-le vide) pour l'ordre décroissant et "1" pour l'ordre croissant. Veillez à ce que l'ordre corresponde à la manière dont vous souhaitez que vos données soient classées.

Fumée de fonction

Lorsque vous travaillez avec différents ensembles de données, il est très important de choisir la bonne fonction de classement, car une mauvaise fonction de classement donnera des résultats indésirables.

Pour éviter ces problèmes, utilisez RANK.EQ() pour un classement standard et RANK.AVG() pour les scénarios où le traitement des ex aequo affecte le résultat, comme les analyses statistiques.

Utilisation de formules obsolètes 

Au fur et à mesure que vos données augmentent, vous devez mettre à jour les références de plage dans vos formules. Cela signifie que les nouveaux points de données ne seront pas inclus dans vos classements, et vous risquez d'être frustré de ne pas obtenir une réponse correcte.

C'est pourquoi vous devez régulièrement mettre à jour et vérifier les fourchettes de vos formules. Cela permet de s'assurer que toutes vos données précieuses sont prises en compte pour le classement.

Réflexions finales

Vous savez maintenant comment utiliser des fonctions telles que RANK(), RANK.EQ() et RANK.AVG() pour gérer les égalités et résoudre divers problèmes de classement. Ces fonctions sont utiles dans différents scénarios, tels que l'analyse des notes des élèves ou la commande de chiffres de vente.

Si vous souhaitez développer davantage vos compétences en Excel, je vous recommande vivement de consulter d'excellents cours et ressources disponibles sur DataCamp : 

FAQ

Quelle est la différence entre RANK.EQ() et RANK.AVG() ?

RANK.EQ() attribue le même rang aux valeurs dupliquées, tandis que RANK.AVG() attribue le rang moyen aux valeurs dupliquées.

Pouvez-vous classer des valeurs de texte à l'aide de la formule RANK() ?

Non, la formuleRANK() est conçue pour les valeurs numériques. Vous devrez utiliser des fonctions différentes comme RANK() combinées à d'autres logiques pour les valeurs de texte.

Quelle est l'utilité de la fonction COUNTIF() pour ajuster les rangs en cas d'égalité ?

La fonction COUNTIF() dans Excel utilise la formule RANK() pour compter les occurrences de la valeur jusqu'à la cellule actuelle et ajoute ce nombre (moins un) au rang d'origine.

Quelle est l'erreur dans la formule RANK() dans Excel ?

Deux erreurs différentes peuvent se produire lors de l'utilisation de la formuleRANK(): #VALUE! Erreur et #N/A erreur.

  • #VALUE! Erreur : se produit si les arguments fournis à la fonctionRANK() contiennent des valeurs non numériques. Assurez-vous que toutes les cellules de la plage à classer et celles dont le rang est calculé contiennent des données numériques.
  • #N/A Erreur : Cette erreur se produit si la cellule classée se trouve en dehors de la plage spécifiée. Pour éviter cette erreur, vérifiez que la valeur classée est incluse dans la plage sélectionnée.

Laiba Siddiqui's photo
Author
Laiba Siddiqui
LinkedIn
Twitter

Je suis un stratège du contenu qui aime simplifier les sujets complexes. J'ai aidé des entreprises comme Splunk, Hackernoon et Tiiny Host à créer un contenu attrayant et informatif pour leur public.

Sujets

Apprenez-en plus sur Excel et l'analyse de données avec ces cours !

cours

Data Analysis in Excel

3 hr
69.7K
Learn how to analyze data with PivotTables and intermediate logical functions before moving on to tools such as what-if analysis and forecasting.
Afficher les détailsRight Arrow
Commencer le cours
Voir plusRight Arrow
Apparenté

blog

Les 32 meilleures questions d'entretien sur AWS et leurs réponses pour 2024

Un guide complet pour explorer les questions d'entretien AWS de base, intermédiaires et avancées, ainsi que des questions basées sur des situations réelles. Il couvre tous les domaines, garantissant ainsi une stratégie de préparation bien équilibrée.
Zoumana Keita 's photo

Zoumana Keita

30 min

blog

Les 20 meilleures questions d'entretien pour les flocons de neige, à tous les niveaux

Vous êtes actuellement à la recherche d'un emploi qui utilise Snowflake ? Préparez-vous à répondre à ces 20 questions d'entretien sur le flocon de neige pour décrocher le poste !
Nisha Arya Ahmed's photo

Nisha Arya Ahmed

20 min

blog

2022-2023 Rapport annuel DataCamp Classrooms

À l'aube de la nouvelle année scolaire, DataCamp Classrooms est plus motivé que jamais pour démocratiser l'apprentissage des données, avec plus de 7 650 nouveaux Classrooms ajoutés au cours des 12 derniers mois.
Nathaniel Taylor-Leach's photo

Nathaniel Taylor-Leach

8 min

blog

Q2 2023 DataCamp Donates Digest

DataCamp Donates a offert plus de 20k bourses d'études à nos partenaires à but non lucratif au deuxième trimestre 2023. Découvrez comment des apprenants défavorisés et assidus ont transformé ces opportunités en réussites professionnelles qui ont changé leur vie.
Nathaniel Taylor-Leach's photo

Nathaniel Taylor-Leach

blog

Nous avons fait don de bourses DataCamp Premium à un million de personnes, et ce n'est pas fini.

Réparties entre nos deux programmes d'impact social, DataCamp Classrooms et #DCDonates, les bourses offrent un accès illimité à tout ce que DataCamp Premium a à offrir.
Nathaniel Taylor-Leach's photo

Nathaniel Taylor-Leach

blog

Célébration de Saghar Hazinyar : Une boursière de DataCamp Donates et une diplômée de Code to Inspire

Découvrez le parcours inspirant de Saghar Hazinyar, diplômée de Code to Inspire, qui a surmonté les défis en Afghanistan et s'est épanouie grâce à une bourse de DataCamp Donates.
Fereshteh Forough's photo

Fereshteh Forough

4 min

Voir plusVoir plus