Classer des valeurs ex aequo

Les fonctions comme GRANDE.VALEUR ou PETITE.VALEUR sont pratiques pour établir des classements dynamiques à partir d’une source de données, nous en avons déjà parlé dans cet article. Leur utilisation est très simple. Malheureusement, les égalités ne sont pas simples à gérer et il est très courant d’avoir plusieurs valeurs ex aequo. Voyons alors comment nous pouvons corriger ce problème.


 

Quel est le problème exact ?

Prenons l’exemple suivant, nous avons une liste de 10 commerciaux et le nombre de commandes qu’ils ont prises. Nous les classons selon cet indicateur. La plus grande valeur est de 92, viennent ensuite deux ex aequo à 87. 80 est alors la 4ème valeur dans ce classement.

Liste valeurs egalite ex aequo

Si nous utilisons la fonction GRANDE.VALEUR, les 4 valeurs sont bien identifiées. Mais Excel ne note pas qu’il y a deux valeurs 87. Ainsi, l’une se classe 2ème et l’autre 3ème.

Classement ex aequo fonction GRANDE.VALEUR egalite

Mais c’est surtout problématique pour récupérer le nom en face des valeurs. Généralement, on utilise pour cela une RECHERHEV. Mais avec 2 valeurs identiques, le premier nom sera mis en face des deux valeurs.
Plus précisément, on a utilisé ici une combinaison d’INDEX et EQUIV. Avec ces deux fonctions, on peut faire sans aucun problème une RECHERCHEV vers la gauche.

RECHERCHEV classement ex aequo valeurs egalite

Les valeurs sont donc correctement identifiées mais on note deux problèmes à corriger :

  • Modifier le classement des valeurs ex aequo pour les afficher au même rang
  • Identifier tous les noms distincts pour une même valeur

 

Comment corriger ce problème facilement ?

Nous allons voir deux solutions différentes pour la résolution de ce problème : la première en utilisant des données d’origine ‘modifiées’ pour faciliter le classement, la seconde en nous concentrant uniquement sur la formule dans le tableau de classement.

 

En rendant les valeurs uniques

Récupérer le nom

Evidemment, l’idée n’est pas de modifier complètement les valeurs, le classement en serait faussé. Nous allons apporter de légères modifications qui ne changeront rien pour nous mais qui permettent à Excel, beaucoup plus précis de distinguer toutes les valeurs.
Les deux valeurs ci-dessous présentent une différence au niveau de la 10ème décimale. Excel y voit bien 2 nombres distincts mais nous afficherons uniquement la partie entière.

Distinction valeurs ajout decimales egalite ex aequo

Ajouter un nombre 10 chiffres après la virgule permet d’utiliser cette technique y compris avec des nombres décimaux, ce qui n’est pas le cas dans cet exemple.
Pour ajouter facilement un nombre qui sera différent à chaque ligne, on utilise la fonction LIGNE sans argument. Elle donne alors le numéro de la ligne sur laquelle elle se trouve. On le divise ensuite par 10^10.
Nous prenons soin d’ajouter une colonne pour ne pas modifier les données d’origine.

Fonction LIGNE ajout decimales distinctes

Par défaut, Excel n’affiche aucune différence à l’écran. Il faudrait forcer un affichage avec 10 décimales pour les voir.
Ainsi, on vérifie bien que les deux ‘87’ sont maintenant différents.

Tableau valeurs modifiees uniques pour classement egalite ex aequo

Et le classement est désormais correct avec un nom différent sur chaque ligne.

Classement ex aequo avec egalites rang distinct
 

Modifier le classement

Pour un classement impeccable, il ne reste plus qu’à afficher 2 deuxièmes places.
Nous allons conserver les valeurs de la colonne classement telles qu’elles, Excel les utilise pour récupérer les valeurs des commandes. Insérons plutôt une nouvelle colonne.
Dans celle-ci, utilisons une formule pour vérifier le nombre de commandes de chaque ligne :

  • S’il est identique à la ligne juste au-dessus, on récupère le classement de la ligne au-dessus
  • S’il est différent, on conserve le classement d’origine

Comme nous avons modifié les valeurs, comparons les arrondis à l’entier. Si nous avions des nombres décimaux, nous utiliserions un arrondi au niveau de cette décimale, d’où l’importance d’ajouter une valeur suffisamment faible pour le classement.
Voilà à quoi ressemble la formule, nous traitons le rang 1 à part pour éviter une erreur d’arrondi avec l’en-tête mais conserver une seule formule valable pour toute la colonne.

Formule calcul rang egalite ex aequo premiere solution

Pour terminer, on masque la colonne Classement initiale pour éviter toute confusion.

Classement final avec egalites ex aequo premiere solution
 

En utilisant une RECHERCHEV sur la 2ème valeur

Récupérer le nom

La seconde solution consiste tout simplement à modifier la formule pour récupérer le nom. Il faut qu’elle soit capable de trouver le premier comme le deuxième nom en face de chaque valeur, voire plus.
Pour faire une RECHERCHEV sur la n-ième valeur d’une série, nous avons déjà vu deux méthodes :

Je vous invite à consulter les deux articles et à choisir la méthode qui vous convient. C’est la 2ème que nous allons utiliser maintenant.
Nous remplaçons la formule vue dans la première partie par la suivante :

Formule classement sans modification valeurs

Elle est très proche de celle présentée dans l’article détaillé. Résumons en quelques points :
SI($B$2:$B$11=F2;LIGNE($B$2:$B$11)-1;NA()) : nous cherchons dans le tableau source les lignes ayant 92 commandes et nous récupérons leur numéro de ligne ou une erreur #N/A si ce n’est pas le cas. Le numéro de ligne, entre 2 et 11, est ramené ensuite entre 1 et 10.
AGREGAT(15;6; SI(…) ;NB.SI(F$2:F2)) : nous utilisons la fonction AGREGAT pour éliminer les erreurs (6) et cherchons la plus petite valeur (15). Cette valeur est déterminée la fonction NB.SI qui sert de ‘compteur’ : pour 92, il n’y a qu’une valeur ; pour 87, elle prend les valeurs 1 puis 2.
INDEX($A$2:$A$11; AGREGAT(…)) ) : on termine en récupérant le nom du commercial sur la ligne identifiée.
C’est une formule matricielle, que l’on valide donc avec Ctrl+Maj+Entrée.

De cette façon, on identifie clairement chaque occurrence du nombre de commandes. Peu importe, le nombre de valeurs à égalité.
Ne reste plus qu’à modifier le classement.

 

Modifier le classement

Comme dans le cas de la première solution, nous conservons la colonne Classement et nous en insérons une nouvelle.
De nouveau, nous vérifions le nombre de commandes sur chaque ligne. Mais cette fois, pas la peine d’utiliser un arrondi, nous pouvons conserver les valeurs du tableau.
Voilà la retranscription en formule, nous pouvons l’utiliser, elle aussi, dans toute la colonne.

Formule calcul rang egalite ex aequo deuxieme solution

Pour terminer, on masque la colonne Classement initiale.

Classement final avec egalites ex aequo deuxieme solution
 

Quelle solution préférer ?

La deuxième solution fait appel à une formule matricielle plus complexe. Elle n’est donc pas évidente à appréhender mais elle a le mérite de ne pas toucher aux données initiales.
On pourrait également utiliser la méthode décomposée pour retrouver la 2ème valeur dans la RECHERCHEV, cliquez pour retrouver l’article.

La première solution est sans doute la plus simple et la plus rapide à mettre en place. Si on se contente d’une présentation dans un tableau ou un graphique, cela ne devrait pas poser de problème.
Si on souhaite réutiliser les valeurs issues du classement, il vaudra mieux utiliser les données non modifiées. Nous avons alors deux possibilités : arrondir la valeur, comme pour l’affichage du rang final, ou récupérer la valeur initiale, avec une RECHERCHEV comme pour le nom.

Enfin, notons une dernière différence. La deuxième méthode affiche les valeurs dans le même ordre que le tableau initial. A l’inverse de la première méthode qui ajoute un nombre plus grand au fur et à mesure que nous descendons dans le tableau, les valeurs seront alors en ordre inversé.

 

Nous disposons donc de deux solutions pour une gestion parfaite des égalités dans nos classements. J’espère que vous aurez trouvé votre bonheur avec une de ces deux méthodes.
N’hésitez pas à poser vos questions en commentaires ou à nous faire part des solutions que vous utilisez. Il est possible d’utiliser encore d’autres techniques, partagez-les avec nous !

 

Abonnez-vous à la newsletter

Illustration couverture ebook 100 trucs et astuces pour Excel-ler au travail

Et recevez gratuitement l'ebook 100 trucs et astuces pour Excel-ler au travail (5€ sur Amazon)

Votre adresse mail ne sera pas communiquée et vous pourrez vous désabonner à tout moment.

Taggé , , , , , , , , .Mettre en favori le Permaliens.

Une réponse à Classer des valeurs ex aequo

  1. toon dit :

    C’est LA solution que je cherchais depuis des jours ! MERCI !

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.