Fonction de classement dans Excel avec des exemples de formules

Le tutoriel explique les spécificités du fonction Classement dans Excel et montre comment trier dans Excel en fonction de divers critères, trier les données par groupe, calculer le classement centile, etc. Quand tu as besoin déterminer la position relative d'un nombre dans une liste de nombres, le moyen le plus simple est de classer les nombres par ordre croissant ou décroissant. Si, pour une raison quelconque, le tri n'est pas possible, une formule de plage dans Excel est l'outil idéal pour faire le travail.

Fonction GAMME Excel

La Fonction RANG en exceller renvoie l'ordre (ou le rang) d'une valeur numérique par rapport aux autres valeurs de la même liste. En d’autres termes, il vous indique quelle valeur est la plus élevée, la deuxième plus élevée, entre autres.

Peut-être que vous pourriez être intéressé: Excel CHOISISSEZ la fonction. Formules et exemples

Dans une liste ordonnée, le rang d’un certain nombre serait la position. La fonction Range dans Excel peut déterminer la plage en commençant par la plus grande valeur (comme si elle était triée par ordre décroissant) ou par la plus petite valeur (comme si elle était triée par ordre croissant). Le Syntaxe de la fonction RANK d'Excel est le suivant :

RANG (numéro, réf, [commande])

Dónde:

• Numéro (obligatoire) : la valeur dont vous souhaitez retrouver le classement.
• Réf (obligatoire) : une liste de valeurs numériques avec lesquelles trier. Il peut être fourni sous forme de tableau de nombres ou de référence à la liste de nombres.
• Commande (facultatif) : un nombre qui spécifie comment trier les valeurs :
  • Si 0 ou omis, les valeurs sont triées par ordre décroissant, c'est-à-dire du plus élevé au plus bas.
  • S'il s'agit de 1 ou de toute autre valeur non nulle, les valeurs sont triées par ordre croissant, c'est-à-dire du plus bas au plus élevé.

Fonction RANK.EQ dans Excel

RANK.EQ Il s'agit d'une version améliorée de la fonction RANK, introduit dans Excel 2010. Il a la même syntaxe que RANK et fonctionne avec la même logique : si plusieurs valeurs sont classées de manière égale, le rang le plus élevé est attribué à toutes ces valeurs. (EQ signifie « égal »).

RANK.EQ (numéro, réf, [commande])

Dans Excel 2007 et versions antérieures, vous devez toujours utiliser la fonction RANK. Dans Excel 2010, Excel 2013 et Excel 2016, vous pouvez choisir RANK ou RANK.EQ. Il serait cependant conseillé d'utiliser RANK.EQ car RANK peut être suspendu à tout moment.

Fonction Excel RANGE.AVG

RANK.AVG est une autre fonction pour trouver une plage dans Excel qui est disponible uniquement dans Excel 2010, Excel 2013, Excel 2016 et versions ultérieures. Elle a la même syntaxe que les deux autres fonctions :

RANK.AVG (numéro, réf, [commande])

La différence est que si plusieurs nombres ont le même rang, le rang moyen est renvoyé (AVG signifie « moyenne »).

4 choses que vous devez savoir sur le RANK dans Excel

Voici quelques éléments à garder à l’esprit concernant la fonction Classement dans Excel :

• Toute formule de plage dans Excel ne fonctionne que pour les valeurs numériques : nombres positifs et négatifs, zéros, valeurs de date et d'heure. Les valeurs non numériques dans l'argument ref sont ignorées.
• Toutes les fonctions RANGE renvoient la même plage pour les valeurs en double et ignorent tout tri supplémentaire, comme le montre l'exemple suivant.
• Dans Excel 2010 et versions ultérieures, la fonction RANK a été remplacée par RANK.EQ et RANK.AVG. Pour des raisons de compatibilité ascendante, RANK fonctionne toujours dans toutes les versions d'Excel, mais pourrait ne plus être disponible à l'avenir.
• Si le numéro n'est pas trouvé dans la référence, toute fonction de plage Excel renverrait l'erreur #N/A.

Formule de plage Excel de base (du plus élevé au plus bas)

Pour en savoir plus sur le classement avec la fonction Rank dans Excel, jetez un œil à cette capture d'écran :

Les trois formules trient les nombres de la colonne B par ordre décroissant (l'argument d'ordre est omis) :

Dans toutes les versions d'Excel 2003 – 2016 :

= RANG($B2,$B$2 :$B$7)

Dans Excel 2010 – 2016 :

=RANK.EQ($B2,$B$2:$B$7)

= RANG.MOYENNE($B2,$B$2 :$B$7)

La différence réside dans la manière dont ces formules traitent les valeurs en double. Comme vous pouvez le constater, le même score apparaît deux fois, dans les cellules B5 et B6, ce qui influe sur le tri ultérieur :

• Les formules RANK et RANK.EQ donnent un rang de 2 aux deux scores en double. Le deuxième score le plus élevé (Daniela) occupe la quatrième place. Personne n’obtient le rang 3.
• La formule RANK.AVG attribue un classement différent à chaque doublon en coulisses (2 et 3 dans cet exemple) et renvoie la moyenne de ces classements (2.5). Encore une fois, le troisième rang n’est attribué à personne.

Comment utiliser RANK dans Excel – exemples de formules

La route vers l’excellence, disent-ils, est pavée de pratique. Alors, pour mieux apprendre à utiliser la fonction RANK dans Excel, seule ou en combinaison avec d'autres fonctions, examinons des solutions pour certaines tâches réelles.

Comment trier dans Excel du plus petit au plus grand

Comme le montre l'exemple ci-dessus, pour trier les nombres du plus grand au plus petit, utilisez l'une des formules de plage d'Excel avec l'argument d'ordre défini sur 0 ou omis (par défaut).

Pour trier un nombre par rapport à d'autres nombres triés par ordre croissant, mettez 1 ou toute autre valeur non nulle dans le troisième argument facultatif. Par exemple, pour évaluer les temps de sprint sur 100 mètres des élèves, vous pouvez utiliser l'une des formules suivantes :

= RANG (B2, $ B $ 2: $ B $ 7,1)

=RANK.EQ(B2,$B$2:$B$7,1)

Faites attention à ce que nous verrouillions la plage dans l'argument ref en utilisant des références de cellule absolues, afin que cela ne change pas lorsque nous copions la formule dans la colonne.

En conséquence, la valeur la plus basse (temps le plus rapide) prend la première place et la valeur la plus grande (temps le plus lent) obtient le rang le plus bas, soit 6. Des temps égaux (B2 et B7) reçoivent le même rang.

Comment classer de manière unique les données dans Excel

Comme indiqué ci-dessus, toutes les fonctions de plage Excel renvoient la même plage pour des éléments de valeur égale. Si ce n'est pas ce que vous souhaitez, utilisez l'une des formules suivantes pour résoudre les situations de départage et attribuer à chaque numéro un rang unique.

Classement unique du plus élevé au plus bas

Pour trier les résultats en mathématiques des élèves par ordre décroissant uniquement, utilisez cette formule :

=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1

Classement unique du plus bas au plus élevé

Pour trier vos résultats de course de 100 mètres par ordre croissant sans doublons, utilisez cette formule :

=RANK.EQ(B2,$B$2:$B$7,1) + COUNTIF($B$2:B2,B2)-1

Comment fonctionnent ces formules

Comme vous l'avez remarqué, La seule différence entre les deux formules est l'argument d'ordre de la fonction RANK.EQ : omis pour le tri des valeurs décroissantes, 1 pour le tri ascendant. Dans les deux formules, c'est la fonction COUNTIF avec l'utilisation intelligente de références de cellules relatives et absolues qui fait l'affaire.

Bref, utilisez COUNTIF pour savoir combien d'occurrences du nombre en cours de tri se trouvent dans les cellules précédentes, y compris la cellule numérique. Dans la ligne supérieure où vous saisissez la formule, la plage se compose d'une seule cellule ($B$2:B2). Mais comme elle verrouille uniquement la première référence ($B$2), la dernière référence relative (B2) change en fonction de la ligne où la formule est copiée.

Par conséquent, pour la ligne 7, la plage est étendue à $B$2:B7 et la valeur de B7 est comparée à chacune des cellules précédentes. Par conséquent, pour toutes les premières occurrences, COUNTIF renvoie 1 ; et soustrayez 1 à la fin de la formule pour restaurer la plage d'origine.

Pour la deuxième occurrence, COUNTIF renvoie 2. En soustrayant 1, vous incrémentez la plage de 1 point, évitant ainsi les doublons. S'il y a 3 occurrences de la même valeur, COUNTIF() – 1 en ajouterait 2 à votre tri, et ainsi de suite.

Solution de contournement pour rompre les liens Excel RANK

Une autre forme de trier les nombres dans Excel La seule façon est d'ajouter deux fonctions COUNTIF :

• La première fonction détermine combien de valeurs sont supérieures ou inférieures au nombre à trier, selon que vous triez respectivement par ordre décroissant ou ascendant.
• La deuxième fonction (avec la "plage extensible" $B $2:B2 comme dans l'exemple précédent) vous obtenez le nombre de valeurs égal au nombre.

Par exemple, pour trier les nombres uniquement du plus haut au plus bas, vous utiliserez cette formule :

=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIF($B$2:B2,B2)

Comme le montre la capture d'écran ci-dessous, le bris d'égalité est résolu avec succès et un rang unique est attribué à chaque élève :

Classification dans Excel basée sur plusieurs critères

L'exemple précédent a démontré deux solutions de travail pour une situation de Bris d'égalité Excel RANK. Cependant, il peut sembler injuste que des nombres égaux soient classés différemment en fonction uniquement de leur position sur la liste.

Pour améliorer votre classement, vous souhaiterez peut-être ajouter un critère supplémentaire à prendre en compte en cas d'égalité. Dans notre exemple d'ensemble de données, ajoutons les scores totaux dans la colonne C et calculons la plage comme suit :

• Tout d'abord, classez avec un score mathématique (critère principal).
• En cas d'égalité, départagez-la avec le score total (critère secondaire)

Pour ce faire, nous utiliserons une formule RANK/RANK.EQ classique pour trouver le classement, et la fonction COUNTIF pour départager une égalité :

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

Par rapport à l'exemple précédent, cette formule de classement est plus objective : Timothée est classé 2ème car son score total est supérieur à celui de Julia :

Comment fonctionne cette formule

La partie RANK de la formule est évidente et la fonction COUNTIFS effectue les opérations suivantes :

• La première paire critères_range/critère ($B$2 :$B$7,$B2) compte les occurrences de la valeur que vous classez. Notez que nous fixons la plage avec des références absolues, mais nous ne verrouillons pas la ligne de critères ($B2) afin que la formule vérifie la valeur de chaque ligne individuellement.
• La deuxième paire critère_plage/critère ($C$2 :$C$7, ">" et $C2) détermine combien de scores totaux sont supérieurs au score total de la valeur classée.

Étant donné que COUNTIFS fonctionne avec la logique AND, c'est-à-dire qu'il ne compte que les cellules qui remplissent toutes les conditions spécifiées, il renvoie 0 pour Timothy, puisqu'aucun autre élève ayant le même score en mathématiques n'a un score total plus élevé. Par conséquent, le rang de Timothy renvoyé par RANK.EQ ne change pas.

Pour Julia, la fonction COUNTIF renvoie 1 car un élève avec le même score en mathématiques a un score total plus élevé, donc son numéro de rang est augmenté de 1. Si un élève supplémentaire a le même score en mathématiques et un score total inférieur à celui de Timothy et Julia, votre rang augmentera de 2. , et ainsi de suite.

Solutions alternatives pour trier les nombres avec plusieurs critères

Au lieu de la fonction RANK ou RANK.EQ, vous pouvez utiliser COUNTIF pour vérifier les critères principaux et COUNTIFS ou SUMPRODUCT pour résoudre une question de départage :

=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)+1

=COUNTIF($B$2:$B$7,»>»&B2)+SUMPRODUCT(–($C$2:$C$7=C2),–($B$2:$B$7>B2))+1

Le résultat de ces formules est exactement le même que celui indiqué ci-dessus.

Comment calculer le rang centile dans Excel

En statistiques, un percentile est la valeur en dessous de laquelle se situe un certain pourcentage de valeurs dans un ensemble de données donné. Par exemple, si 70 % des étudiants ont un résultat égal ou inférieur à votre score au test, votre rang centile est 70.

Pour obtenir le rang centile dans Excel, utilisez la fonction RANK ou RANK.EQ avec un argument d'ordre non nul pour classer les nombres du plus bas au plus élevé, puis divisez le classement par le nombre de nombres. Ainsi, la formule générique de classement centile Excel est la suivante :

RANK.EQ (topmost_cell, rang, 1) / COUNT (rang)

Pour calculer le rang centile des étudiants, la formule prend la forme suivante :

=RANK.EQ(B2,$B$2:$B$7,1)/COUNT($B$2:$B$7)

Pour que les résultats s'affichent correctement, assurez-vous de définir le format de pourcentage dans les cellules de la formule :

Peut-être voulez-vous savoir : Comment ajouter par catégorie dans Excel

Comment trier les nombres dans des cellules non adjacentes

Dans les situations où vous devez trier des cellules non contiguës, fournissez ces cellules directement dans l'argument ref de votre formule de plage Excel sous la forme d'une union de référence, bloquant les références avec le signe $. Par exemple:

= RANG(B2,($B$2,$B$4,$B$6))

Pour éviter les erreurs dans les cellules non triées, ajustez RANG en fonction SIERREUR, Alors:

=SIERREUR(RANK(B2,($B$2,$B$4,$B$6)),»»)

Notez qu'un numéro en double se voit également attribuer une plage, bien que la cellule B5 ne soit pas incluse dans la formule :

Si vous devez trier plusieurs cellules non contiguës, la formule ci-dessus peut être trop longue. Dans ce cas, une solution plus élégante serait de définir une plage nommée et de référencer ce nom dans la formule :

=SIERREUR(RANK(B2,plage),»»)

Comment trier dans Excel par groupe

Lorsque vous travaillez avec des entrées organisées dans un certain type de structure de données, les données peuvent appartenir à plusieurs groupes et vous souhaiterez peut-être trier les nombres au sein de chaque groupe individuellement. La fonction RANK d'Excel ne peut pas résoudre ce problème, utilisons donc une formule SUMPRODUCT plus complexe :

Trier par groupe par ordre décroissant :

=SUMPRODUCT((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

Trier par groupe par ordre croissant :

=SUMPRODUCT((A2=$A$2:$A$7)*(C2>$C$2:$C$7))+1

Dónde:

• A2: A7 sont des groupes attribués à des numéros.
• C2: C7 sont des numéros pour la classification.

Dans cet exemple, nous utilisons la première formule pour trier les nombres de chaque groupe du plus grand au plus petit :

Comment fonctionne cette formule

Fondamentalement, la formule évalue 2 conditions :

• Tout d’abord, vérifiez le groupe (A2=$A$2:$A$7). Cette partie renvoie un tableau de VRAI et FAUX selon qu'un élément de plage appartient ou non au même groupe que A2.
• Deuxièmement, vérifiez le score. Pour trier les valeurs de la plus haute à la plus basse (ordre décroissant), utilisez la condition (C2 < $C$2:$C$11), qui renvoie VRAI pour les cellules supérieures ou égales à C2, FAUX sinon.

Étant donné qu'en termes Microsoft Excel, VRAI = 1 et FAUX = 0, la multiplication des deux matrices donne une matrice de 1 et 0, où 1 est renvoyé uniquement pour les lignes où les deux conditions sont remplies. Alors, SUMPRODUCT Il ajoute les éléments du tableau 1 et 0, il renvoie donc 0 pour le plus grand nombre de chaque groupe. Et ajoutez 1 au résultat pour commencer le tri avec 1.

La formule qui classe les nombres en groupes du plus petit au plus grand (ordre croissant) fonctionne avec la même logique. La différence est que SOMME PRODUIT renvoie 0 pour le plus petit nombre d'un groupe particulier, puisqu'aucun nombre de ce groupe ne remplit la 2ème condition (C2 > $C$2 : $C$7). Encore une fois, remplacez le rang zéro par le premier rang en ajoutant 1 au résultat de la formule.

Au lieu de SUMPRODUCT, tu peux utiliser la fonction SUM pour ajouter les éléments de la matrice. Mais cela nécessiterait d'utiliser une formule matricielle, complétée via Ctrl + Maj + Entrée. Par exemple:

=SUM((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

Comment classer séparément les nombres positifs et négatifs

Si votre liste de nombres contient à la fois des valeurs positives et négatives, la fonction RANK d'Excel les classera toutes en un rien de temps. Mais que se passerait-il si vous vouliez classer séparément les nombres positifs et négatifs ? Avec des nombres dans les cellules A2 à A10, utilisez l'une des formules suivantes pour obtenir un classement individuel pour les valeurs positives et négatives :

Trier les nombres positifs par ordre décroissant :

=IF($A2>0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)

Triez les nombres positifs par ordre croissant :

=IF($A2>0,COUNTIF($A$2:$A$10,»>0″)-COUNTIF($A$2:$A$10,»>»&$A2),»»)

Trier les nombres négatifs par ordre décroissant :

=IF($A2<0,COUNTIF($A$2:$A$10,»<0″)-COUNTIF($A$2:$A$10,»<«&$A2),»»)

Triez les nombres négatifs par ordre croissant :

=IF($A2<0,COUNTIF($A$2:$A$10,»<«&$A2)+1,»»)

Les résultats ressembleront à ceci :

Comment fonctionnent ces formules

Pour commencer, regardons la formule qui trie les nombres positifs par ordre décroissant :

• Dans le test logique de la fonction IF, vérifie si le nombre est supérieur à zéro.
• Si le nombre est supérieur à 0, la fonction COUNTIF renvoie le nombre de valeurs supérieures au nombre à trier.

Dans cet exemple, A2 contient le deuxième nombre positif le plus élevé, pour lequel COUNTIF renvoie 1, ce qui signifie qu'il n'y a qu'un seul nombre supérieur à lui. Pour commencer notre tri avec 1 et non 0, nous ajoutons 1 au résultat de la formule, elle renvoie donc une sorte de 2 pour A2.

Si le nombre est supérieur à 0, la formule renvoie une chaîne vide (""). La formule qui trie les nombres positifs par ordre croissant fonctionne un peu différemment : si le nombre est supérieur à 0, le premier COUNTIF obtient le nombre total de nombres positifs dans l'ensemble de données et le second COUNTIF Découvrez combien de valeurs sont supérieures à ce nombre.

Ensuite, vous soustrayez le dernier du premier et obtenez la plage souhaitée. Dans cet exemple, il y a 5 valeurs positives dont 1 supérieure à A2. Ensuite, soustrayez 1 de 5, obtenant ainsi un rang de 4 pour A2. Les formules de classification des nombres négatifs sont basées sur une logique similaire.

Note: Toutes les formules ci-dessus ignorent les valeurs nulles car 0 n'appartient ni à l'ensemble des nombres positifs ni à l'ensemble des nombres négatifs. Pour inclure des zéros dans votre tri, remplacez >0 et <0 par >=0 et <=0, respectivement, là où la logique de la formule l'exige. Par exemple, pour trier les nombres positifs et les zéros du plus grand au plus petit, utilisez cette formule :

=IF($A2>=0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)

Comment trier les données dans Excel en ignorant les valeurs nulles

Comme vous le savez déjà, un La formule GAMME est Excel gère tous les nombres : positifs, négatifs et zéros. Mais dans certains cas, nous souhaitons uniquement classer les cellules dont les données ignorent les valeurs 0. Vous pouvez trouver quelques solutions possibles pour cette tâche, mais la formule Excel RANK IF est la plus universelle :

Numéros décroissants ignorant zéro :

=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10), RANK($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0)))

Numéros de plage ascendants ignorant zéro :

=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10,1) – COUNTIF($B$2:$B$10,0), RANK($B2,$B$2:$B$10,1)))

où B2 : B10 est la plage de nombres à trier. La meilleure chose à propos de cette formule est qu'elle fonctionne à merveille pour les nombres positifs et négatifs, laissant les valeurs nulles en dehors de la classification :

Comment fonctionne cette formule

À première vue, la formule peut paraître un peu compliquée. En y regardant de plus près, la logique est très simple. Voici comment la formule RANK IF d'Excel classe les nombres du plus haut au plus bas en ignorant les zéros :

• Le premier IF vérifie si le nombre est 0 et si c'est le cas, renvoie une chaîne vide :

SI ($B2 = 0, «»,…)

• Si le nombre n'est pas zéro, le deuxième IF vérifie s'il est supérieur à 0, et si c'est le cas, une fonction RANK/RANK.EQ normale calcule le rang :

SI ($B2 > 0, RANG ($B2, $B$2 : $B$10),…)

• Si le nombre est inférieur à 0, ajustez le tri en fonction du nombre de zéro. Dans cet exemple, il y a 4 nombres positifs et 2 zéros. Ainsi, pour le plus grand nombre négatif de B10, une fonction Range dans Excel renverrait 7. Mais nous avons omis les zéros et devons donc ajuster la plage de 2 points. Pour ce faire, on soustrait le nombre de zéros de la plage :

PLAGE($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0))

Oui, c'est aussi simple que ça ! La formule permettant de trier les nombres du plus petit au plus grand en ignorant les zéros fonctionne de la même manière et pourrait constituer un bon exercice mental pour déduire sa logique.

Comment calculer la plage dans Excel par valeur absolue

Lorsqu'il s'agit d'une liste de valeurs positives et négatives, il peut être nécessaire de trier les nombres selon leurs valeurs absolues en ignorant le signe. La tâche peut être accomplie avec l'une des formules suivantes, au cœur de laquelle se trouve la fonction ABS qui renvoie le valeur absolue d'un nombre :

Plage ABS descendante :

=SOMMEPRODUIT((ABS(A2)<=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SOMMEPROD((ABS(A2)=ABS($A$2:$A$7 )) * (2$AU :7$AU<>»»))+1

Gamme ABS ascendante :

=SOMMEPRODUIT((ABS(A2)>=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SOMMEPROD((ABS(A2)=ABS($A$2:$A$7 )) * (2$AU :7$AU<>»»))+1

En conséquence, les nombres négatifs sont classés comme s’il s’agissait de nombres positifs :

Comment obtenir N valeurs plus grandes ou plus petites

Si vous souhaitez obtenir un nombre N réel des valeurs les plus grandes ou les plus petites au lieu de leur classement, utilisez respectivement la fonction LARGE ou SMALL. Par exemple, nous pouvons obtenir les 3 meilleurs scores des élèves avec cette formule :

=GRAND($B$2 :$B$7, $D3)

où B2 : B7 est la liste de scores et D3 est la plage souhaitée. De plus, vous pouvez récupérer les noms des étudiants à l'aide de la formule CORRESPONDANCE D'INDICE (à condition qu'il n'y ait pas de scores en double dans le top 3) :

=INDEX($A$2:$A$7,MATCH(E3,$B$2:$B$7,0))

De même, vous pouvez utiliser la fonction SMALL pour extraire les 3 valeurs inférieures :

=PETIT($B$2 :$B$7, $D3)

Jette un coup d'œil à: Comment combiner Excel avec Word : importer des données d'Excel vers Word

Pensamientos finales

Voici comment classer à l'aide de la fonction de classement dans Excel. Merci d'avoir lu et nous espérons vous voir sur notre blog la semaine prochaine ! Il existe un grand nombre de tutoriels disponibles pour clarifier les doutes liés au domaine de l'informatique. Pour en revenir au sujet, il existe de nombreuses façons alternatives de calculer la plage dans Excel, chacune avec ses propres particularités. Vous décidez comment vous voulez travailler.