vendredi 16 janvier 2015

Excel - RANG - Comment obtenir un classement sans ex-aequo

Excel - Fonction RANG - Gérer les égalités
Comment obtenir un classement sans ex-aequo avec les fonctions RANG et EQUATION.RANG?



1 - Introduction:

Les fonctions RANG (Excel 2007) et EQUATION.RANG (Excel 2010), renvoient le même rang en cas d'égalité, et poursuivent le classement au niveau suivant pour reprendre la série.
Ce qui peut poser des problèmes lorsque l'on veut ré-utiliser les résultats avec d'autres fonctions d'Excel.
Alors comment obtenir un classement propre permettant l'utilisation avec d'autres fonctions qui n'acceptent pas de valeurs identiques sans renvoyer une erreur?

2 - Exemple :

Nous allons prendre l'exemple suivant, dans lequel nous souhaitons dans un premier temps établir un classement de nos magasins par CA HT et ensuite établir un TOP3.
Pour cela, nous utilisons la fonction RANG, =RANG(C2;$C$2:$C$7), pour le classement et la fonction RECHERCHEV ,=RECHERCHEV(A10;$A$2:$B$7;2;FAUX), pour le TOP3.

Excel - Fonction RANG - Gérer les égalités

Dans cette version, des doublons apparaissent au niveau du classement, et ceux-ci posent un problème évident à la formule de recherche, qui n'arrive pas à donner un classement au deuxième magasin puisque 2 n'existe pas dans la zone de référence $A$2:$B$7.

Pour palier à ce défaut, nous allons utiliser la fonction statistique NB.SI, fonction qui permet de déterminer le nombre de valeurs identiques dans une liste.
Donc nous complétons notre formule comme ceci, =RANG(C16;$C$2:$C$7)+NB.SI(C$16:C16;C16)-1
NB.SI ajoute au résultat de RANG le nombre d'occurrences existantes pour un même résultat.

Excel - Fonction RANG - Gérer les égalités

Cette fois il n'existe plus aucun doublon, notre classement est correct et notre TOP3 également.

A noter: Il est nécessaire de verrouiller la première cellule de la zone de référence de NB.SI avec un $, comme ceci,  =RANG(C16;$C$2:$C$7)+NB.SI(C$16:C16;C16)-1, pour permettre  la fonction de s'ajuster à la longueur de la liste =RANG(C3;$C$2:$C$7)+NB.SI(C$2:C3;C3)-1. Si vous omettez cette manipulation la formule ne fonctionnera pas.

3 - Conclusion :

Cette formule est utilisable aussi bien avec  EQUATION.RANG que MOYENNE.RANG , cependant pour cette dernière une adaptation sera nécessaire, car cette fonction affiche une moyenne en cas d'égalité.

4 - Téléchargements :


Retrouvez cet exemple dans le fichier des Fonctions Excel.

5 - Autres références au sujet (ou articles complémentaires):


Les 50 fonctions essentielles.

6 - Autres liens :

Excel
Forum
Newsletter

Print Friendly and PDF


Blog Excel-Dev  - Trucs et Astuces pour Excel
Pays/territoire : Bry-sur-Marne, France

4 commentaires :

  1. Merci pour votre aide !

    RépondreSupprimer
  2. Merci,
    mais cela fonctionne si on a une égalité à plus de 2 lignes ?
    1 Paris
    1 Lille
    1 Reims

    RépondreSupprimer
  3. Bonjour, oui bien sur, cela fonctionne avec plus de 2 égalités.

    RépondreSupprimer
  4. Explications très claires... Merci
    J'aimerais pouvoir départager les ex-æquo selon un deuxième critère. Avez-vous une idée qui me permettrait de classer une série de personnes selon le nombre de participation. Les personnes ayant participer un même nombre de fois seraient classées en fonction du total des km parcourus.

    RépondreSupprimer