Comment classer simplement des données avec la fonction RANG (avec ou sans ex æquo) Excel ?

Dans ce tutoriel, je vais vous montrer comment réaliser un classement avec la fonction RANG() d’Excel. Nous verrons également comment extraire les premiers ou les derniers éléments de ce classement (ici les noms et notes des élèves). Enfin, nous verrons comment adapter la formule RANG() pour obtenir un classement sans ex æquo !

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. La fonction RANG

La fonction RANG() d’Excel permet d’obtenir la position d’une valeur parmi un ensemble donné de valeur.

Pour illustrer cet exemple, nous allons partir d’un bulletin de relevé de notes des élèves d’une classe :

Comme nous pouvons le constater, nous retrouvons pour chacun d’entre eux la moyenne générale.

L’objectif est alors de déterminer le classement de chacun des élèves en fonction de cette moyenne.

La fonction RANG() attend trois arguments :

 =RANG(nombre;référence;ordre)  

Où :

• nombre : il s’agit ici de la valeur numérique pour laquelle nous souhaitons obtenir le classement, il s’agira ici de la moyenne de l’élève en cours d’analyse,
• référence : il s’agit ici de la plage des cellules complète dans laquelle se trouve toutes les données mises en concurrence pour déterminer le classement de chaque élément. Bien entendu l’argument nombre doit se trouver à l’intérieur de cette plage de cellules. Dans notre exemple, nous sélectionnons la plage des cellules dans laquelle se trouvent toutes les notes de la classe.
• [ordre] : cet argument facultatif permet définir l’ordre dans lequel nous souhaitons effectuer l’analyse : soit nous souhaitons une analyse par ordre décroissant (les valeurs les plus grandes en premières) et dans ce cas, nous omettons l’argument, ou nous lui donnons la valeur de 0, soit nous souhaitons utiliser une analyse croissante (les valeurs les plus petites en premières) et nous lui donnons la valeur de 1. Ici, nous pouvons omettre cet argument

 =RANG(F8;F8:F16) 

Anna est donc quatrième de classe.

Pour étendre la formule sur les autres notes de la classe, nous allons devoir insérer des dollars sur l’argument référence, afin de figer les coordonnées de la plage des cellules correspondantes.

Pour cela, comme nous l’avons vu dans le chapitre dédié de la formation sur la découverte d’Excel pour les débutants, nous revenons dans la formule, puis nous sélectionnons les coordonnées utilisées en tant que référence, et nous cliquons sur la touche [F4] du clavier.

Attention, sur certains claviers, notamment sur les PC portables, la touche [F4] n’est pas directement disponible, et il faut appuyer en même temps sur la touche [Fn].

Ensuite, nous pouvons étendre la formule en double-cliquant sur la poignée de recopie.

Comme nous pouvons le constater, lorsque des élèves obtiennent la même note, Excel leur attribue alors la même place dans le classement (ex æquo) : Anna et Jack sont toutes les deux quatrièmes.

Les places suivantes ne sont donc pas attribuées, il n’y a donc pas de cinquième de classe.

2. Obtenir le classement des meilleurs

Maintenant que nous connaissons le classement de l’ensemble des élèves de la classe, nous allons pouvoir faire ressortir simplement les trois meilleurs, en utilisant simplement la fonction INDEX-EQUIV que nous avons déjà eu l’occasion de découvrir récemment dans un précédent tutoriel.

Pour obtenir le nom du meilleur élève :

 =INDEX($A$8:$A$16;EQUIV(A20;$G$8:$G$16;)) 

Puis pour obtenir sa note :

 =INDEX($F$8:$F$16;EQUIV(A20;$G$8:$G$16;)) 

Ici encore, nous ajoutons les dollars pour figer la référence aux plages des cellules utilisées.

3. Obtenir le classement des élèves les moins bons

Pour obtenir le classement des élèves, en partant de la fin, nous disposons de plusieurs options :

• Soit utiliser une nouvelle colonne pour calculer le classement par ordre croissant (en utilisant le troisième argument),
• Soit utiliser la colonne existante, mais modifiant légèrement la formule

En effet, plutôt que d’utiliser directement la valeur de la cellule du classement, nous allons l’utiliser en soustraction du nombre d’élèves dans la classe.

Ainsi E20 devient NB($G$8:$G$16)+1-E20.

Les formules sont alors :

 =INDEX($A$8:$A$16;EQUIV(NB($G$8:$G$16)+1-E20;$G$8:$G$16;))  

Et :

  =INDEX($F$8:$F$16;EQUIV(NB($G$8:$G$16)+1-E20;$G$8:$G$16;)) 

4. Un classement sans ex æquo

Maintenant, voyons comment mettre en place un classement sans ex æquo.

Pour cela, nous allons effectuer un traitement dans une nouvelle colonne :

Nous allons partir du classement obtenu dans la première colonne :

Puis, nous allons compter le nombre de cellules étant classées au même niveau parmi les cellules situées au-dessus.

Pour cela, nous allons utiliser la fonction NB.SI(), qui effectue un dénombrement conditionné :

 =G8+NB.SI(G88:G8;G8) 

Ici, nous regardons le nombre de fois que la valeur contenue dans la cellule G8 est répétée dans la plage allant de la cellule G8, jusqu’à la cellule… G8 !

Si la démarche vous parait quelque peu bizarre, pas de panique, nous en comprendrons juste après la raison.

En revanche, vous pourrez noter que le premier 8 de la plage G88:G8 possède un dollar qui permet de figer la cellule de départ de la plage.

Maintenant, si nous regardons le résultat retourné, nous pouvons constater que le résultat retourné est erroné :

Nous devons en effet retrancher une unité du résultat retourné !

 =G8+NB.SI(G$8:G8;G8)-1 

Maintenant, si nous étendons la formule aux cellules de la colonne, nous pourrons constater qu’il n’y a plus aucun ex æquo dans le tableau :

Jack se retrouve en effet en cinquième position !

Alors évidemment, ce classement n’est pas très juste, étant donné que la place perdue ici n’est due qu’à la position de Jack dans l’ordre alphabétique…