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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - fonction rang - 01

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) 

Excel formation - fonction rang - 02

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].

Excel formation - fonction rang - 03

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

Excel formation - fonction rang - 04

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.

Excel formation - fonction rang - 05

 

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;)) 

Excel formation - fonction rang - 06

 

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 :

Excel formation - fonction rang - 07

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

Excel formation - fonction rang - 08

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é :

Excel formation - fonction rang - 09

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 :

Excel formation - fonction rang - 10

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…

 

 



Articles qui pourraient vous intéresser

Comment effectuer un test sur Excel : la fonction SI() (ainsi que ET() et OU())
Comment convertir des unités de mesure sur Excel ? La fonction CONVERT()
Comment modifier le résultat d’une formule renvoyant une erreur : la fonction SIERREUR() d’Excel ?
Comment obtenir des informations sur le classeur (nom et répertoire d’enregistrement) et sur les cellules (nom de l’onglet,…) sur Excel ?
Comment effectuer un test logique sur Excel : découvrir les fonctions ESTERREUR, ESTERR, ESTNA, ESTREF, ESTNUM, ESTTEXTE, ESTNONTEXTE,…
Comment classer simplement des données avec la fonction RANG (avec ou sans ex æquo) Excel ?
Oubliez RECHERCHEV() ! La fonction INDEX-EQUIV pour réaliser des recherches complexes sur Excel
Comment savoir si une cellule est un nombre sur Excel ?
Découvrir la fonction AGREGAT d’Excel pour effectuer des calculs en tenant compte des filtres en place sur Excel
[VIDEO] Assembler des cellules avec la fonction CONCATENER()
Comment calculer une moyenne arithmétique ou pondérée sur Excel
Comment modifier ou supprimer des caractères dans un texte avec la fonction SUBSTITUE d'Excel

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.