Faire un classement des résultats dans Excel

Dans cet article, on va voir comment classer des résultats dans Excel pour, par exemple, classer des notes des élèves ou bien des résultats sportifs.

On va prendre l’exemple du classement des notes des élèves à partir de leurs moyennes, mais la procédure est la même pour tout autre exemple impliquant le calcul du rang dans Excel.
Par la même occasion, on va résoudre le problème du saut de rang en cas d’ex-aequo des résultats.

1- Comment faire un classement des résultats dans Excel

Pour faire un classement des résultats dans Excel, on utilise la fonction RANG.

Admettons par exemple qu’on a le tableau Excel suivant:

Dans ce tableau, on a calculé la moyenne des notes avec coefficients en utilisant la fonction SOMMEPROD d’Excel.

Pour plus d’informations sur cette fonction, voir comment Calculer la moyenne pondérée dans Excel.
Donc, pour faire un classement des résultats des élèves, on va appliquer la fonction Rang d’Excel à leurs Moyennes.

La syntaxe de la fonction RANG est:

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

• Nombre: C’est la donnée numérique, le nombre, dont vous voulez calculer le rang dans la plage numérique à laquelle il appartient.

• Référence: La plage numérique (liste des nombres).

• Ordre: Cet argument est facultatif. Il détermine si l’ordre base de calcul est croissant ou décroissant.
  C’est à dire, s’il est égal à 0 ou omis, le rang du nombre est attribué en considérant la liste des nombres triée par ordre décroissant (le rang 1 est attribué à la valeur la plus élevée).
  S’il est différent de 0, le rang du nombre est attribué en considérant la liste des nombres triée par ordre croissant (le rang 1 est attribué à la valeur la plus faible).

En appliquant la fonction RANG à la colonne des moyennes de notre tableau, on obtient le résultat suivant:

Bien sûr, on applique la formule juste à la première cellule, dans notre exemple, la cellule F3; puis on la copie pour le reste de la colonne (Pour plus de détails sur la recopie des formules dans Excel, voir Copier une formule Excel sur toute une ligne ou colonne).

Remarquez l’utilisation des références absolues dans la formule pour la plage des cellules dans l’argument “Référence” (E$3:E$22).

Bon; à ce point là, Excel nous a bien calculé le rang de chaque moyenne par rapport à l’ensemble des moyennes des autres élèves. Vous pouvez maintenant, si vous voulez, trier la colonne “Rang des élèves” par ordre croissant pour avoir plus de visibilité sur le classement des élèves par moyenne de notes, ou bien laisser l’organisation initiale de la plage de données telle quelle (suivant les noms et prénoms).

[J’ai détaillé l’utilisation du tri des données dans Excel dans le tutoriel Tri dans Excel: texte, nombres, dates, par couleur ou icône].

En tous cas, la fonction RANG d’Excel a bien fait son travail en nous classant les élèves selon leurs moyennes de notes; sauf que, comme vous pouvez le constater, on a un petit problème, en cas d’égalité des données et donc de rang:

La fonction RANG d’Excel attribue un rang identique pour les valeurs égales, ce qui est tout à fait compréhensible, mais saute un rang pour le suivant, ce qui peut être un peu dérangeant!

Ceci se produit deux fois dans notre exemple (voir cellules colorées en jaune):

• Cas des élèves 5 et 13 où la fonction RANG leur attribue le rang 5, mais saute un rang pour classer le résultat suivant directement en rang 7, et non 6!

• Cas des élèves 11 et 18 où la fonction RANG leur attribue le rang 10, mais saute un rang pour classer le résultat suivant directement en rang 12, et non 11!

Donc, dans notre exemple, personne n’est classé 6^ème ou 11^ème!

Certains trouveront ça logique, mais je présume que d’autres préfèreraient bien l’autre cas: Ne pas sauter de rang en cas d’ex-aequo, mais juste continuer le classement normal.

Bon; c’est la limite majeure de la fonction Rang qui, dans sa formule la plus simple, ne gère pas directement le cas du saut de rang en cas d’ex-aequo (il faudrait faire plus complexe et la coupler à d’autres fonctions en formule matricielle, comme on va voir par la suite).
Donc si vous êtes satisfait du résultat et que vous n’avez pas de souci avec le saut des rangs en cas d’ex-aequo, c’est OK;
Mais sinon, il faut tout simplement se pencher vers deux autres solutions plus ou moins compliquées (vous choisiriez bien évidemment celle qui vous convient le mieux!):

• La première solution est de coupler la fonction Rang avec d’autres fonctions en formule matricielle; complexe mais efficace. Voir sous-section 2.1 ci-après.

• La deuxième solution qui utilise la fonction SI. C’est une solution de contournement qui elle aussi a ses limites, mais au moins a le mérite d’être beaucoup plus simple. Voir sous-section 2.2 ci-après.

Seulement, avant de passer à la section suivante, j’aimerais bien émettre une petite remarque à propos du choix de la fonction RANG pour ce tutoriel Excel:

Dans notre exemple, on a utilisé la fonction “RANG” pour classer les résultats, mais vous pouvez aussi bien utiliser deux autres fonctions équivalentes et qui ont la même syntaxe: EQUATION.RANG et MOYENNE.RANG; sauf que celles-ci ont une légère différence avec “RANG”:

• “RANG” et “EQUATION.RANG” sont équivalentes; la seule différence est la compatibilité avec les versions Excel: RANG est l’ancienne fonction compatible avec toutes les versions Excel, et EQUATION.RANG est la nouvelle fonction qui la remplace et n’est compatible qu’avec les versions Excel 2010 et ultérieures.

• Quant à “MOYENNE.RANG”, elle diffère avec “RANG” dans le cas de valeurs égales de rang où elle renvoie la moyenne des rangs et non les rangs eux-mêmes: par exemple, si on a deux valeurs qui auraient dû avoir le même rang 3 avec la fonction RANG, la fonction MOYENNE.RANG renvoie le rang 3.5 pour les deux valeurs.

2- Comment ne pas sauter de rang en cas d’ex-aequo des résultats

2.1- Rang sans trou en cas d’ex aequo en utilisant les fonctions RANG, NB.SI et SOMME en formule matricielle

Dans cette méthode, on va coupler la fonction RANG avec les deux fonctions NB.SI et SOMME; tout ça, en formule matricielle.

Bon, ce n’est pas vraiment notre sujet principal, mais j’en profite pour vous expliquer brièvement ce qu’est en fait une formule matricielle en Excel:

En premier lieu, j’aimerais avouer qu’il me serait très difficile de vous fournir une explication satisfaisante dans cet article; il faudrait un ou même plusieurs tutoriels dédiés pour détailler en profondeur ce que c’est une formule matricielle; mais je donnerai pour le moment une petite définition, en attendant plus d’explications et d’exemples dans d’autres articles:

Une formule matricielle est un type particulier de formules qui permet d’obtenir des résultats que les simples formules Excel ne permettent pas. Comme son nom l’indique, une formule matricielle permet d’effectuer des opérations sur des blocs d’éléments d’une matrice (tableau, plage de données, ou ensemble de lignes ou colonnes) en une seule fois, sans avoir à parcourir ces éléments un à un.

Elles sont généralement validées par Ctrl + Shift + Entrée, et affichées entres accolades ajoutées automatiquement par Excel.

Exemple: {=A3:A7*B3:B7/C3:C7} pour des opérations de calcul entre trois colonnes (ou plages de cellules).

Bon, j’avoue que c’est une définition simpliste et qu’on a pas été trop loin avec les formules matricielles, tellement elles sont complexes et diverses; mais contentons nous de ce niveau de complexité pour les besoins de ce tutoriel.

Revenons maintenant à notre fonction RANG en formule matricielle pour calculer les rangs des différents éléments d’une liste sans sauter de rang en cas d’ex aequo des résultats.

Reprenons notre tableau utilisé tout au long de ce tutoriel:

La formule (issue de cet exemple) à saisir dans la première cellule de notre colonne “Rang” (on va utiliser dans notre cas la cellule G3) est:

=RANG(E3;$E$3:$E$22;0)-(NB.SI($E$3:$E$22;">"&E3)-SOMME((1/NB.SI($E$3:$E$22;$E$3:$E$22))*($E$3:$E$22>E3)))

Mais à ne pas valider par une simple Entrée; plutôt utiliser comme précédemment dit Ctrl + Shift + Entrée.

Excel ajoute les accolades tout autour de la formule, confirmant que c’est une formule matricielle. Vous devriez donc avoir:

{=RANG(E3;$E$3:$E$22;0)-(NB.SI($E$3:$E$22;">"&E3)-SOMME((1/NB.SI($E$3:$E$22;$E$3:$E$22))*($E$3:$E$22>E3)))}

Remarquez l’utilisation des références absolues pour les plages de cellules ($E$3:$E$22 dans notre cas).

Puis, double-cliquez sur la poignée de recopie incrémentée au niveau de la cellule résultat (G3 dans notre cas) pour copier la formule sur toute la colonne (Plus de détails sur la procédure sur Copier une formule Excel sur toute une ligne ou colonne).

Le résultat de l’utilisation en formule matricielle de la fonction RANG, couplée avec les fonctions NB.SI et SOMME, nous donne le tableau suivant:

2.2- Rang sans trou en cas d’ex aequo en utilisant la fonction SI

Cette solution abandonne la fonction Rang et utilise à la place la fonction SI.

La formule est vraiment simple, mais nécessite les étapes suivantes:

1- Trier d’abord les résultats par ordre de croissance.
2- Taper manuellement le chiffre 1 dans la première cellule de la colonne Rang (le rang 1 est donc saisi manuellement!).
3- Appliquer la formule suivante (issue de cet exemple) avec la fonction Si pour les cellules en dessous:

=SI(E4<E3;G3+1;G3)

Commençons par saisir la formule dans la cellule G4 (La colonne E est la colonne des moyennes; la colonne G contiendra les rangs des étudiants par moyennes; et la cellule G3 contient le chiffre 1 qui va nous servir de base de calcul pour les cellules suivantes);

Il nous reste à présent d’étendre la formule aux cellules en dessous par recopie incrémentée.

On obtient ainsi le résultat suivant:

Le seul point faible de cette solution (utilisation de la fonction SI pour calculer le rang dans Excel) est que dès qu’un changement de données est opéré dans les valeurs des résultats (Les notes et leurs moyennes dans notre cas), il faudrait refaire la procédure avec ses 3 étapes; un petit rappel, je présume, ne fera de mal à personne:
Trier; Taper dans un deuxième temps le chiffre 1 dans la première cellule; et enfin Ré-appliquer la formule.

---

Voilà; j’espère bien que ce tutoriel vous ait été utile. N’hésitez pas à partager, dans les commentaires, vos propres expériences personnelles avec l’utilisation de la fonction RANG d’Excel et la mise en pratique des autres solutions invoquées dans ce tutoriel.