ranking

Classement avec la fonction Excel GRANDE.VALEUR() :

22/07/2016

Sur une grande série de données, plusieurs fonctions sont utiles pour distinguer les valeurs remarquables. On utilisera par exemple les fonctions MIN et MAX pour la plus grande et la plus petite valeur d’une série de donnée. En revanche, pour les autres (de la 2ème plus grande valeur à l’avant dernière valeur de la série), il faut recourir à l’utilisation de la fonction GRANDE.VALEUR(). La fonction GRANDE.VALEUR (qui s’écrit Large() en anglais) renvoie la  k-ième valeur d’une série de données.

Par exemple, si on dispose d’une série de données sur les cellules A1 à A6 du fichier Excel

– GRANDE.VALEUR(B1:B6; 1) renverra le maximum de la série (214)
– GRANDE.VALEUR(B1:B6 ; 5) renverra la 5ème plus grande valeur de la série (17)

Picture1

 

Si ces données sont séparées par catégorie, ou par pays, il semble peu utile d’obtenir la k-ième plus grande valeur de la série sans savoir à quoi elle correspond. Il est alors possible de combiner GRANDE.VALEUR() avec les fonctions INDEX() et EQUIV() pour renvoyer l’intitulé correspondant à cette k-iéme valeur de la série.

Ici, on renvoie le nom de la plus grande valeur de la série située en B1:B6. On obtiendra donc la valeur « Allemagne ».Picture2

 

Cependant, si la série de donnée est suffisamment grande, il est probable que la même valeur apparaisse plusieurs fois. Dans ce cas, si on cherche à utiliser la même méthode que précédemment pour obtenir le nom de la catégorie des 5 premières valeurs de la série, on risque d’obtenir ceci :

Picture3

On constate que L’Espagne apparaît à la fois au 3e et 4e rang.

 

Etant donné que la 3e et la 4e plus grande valeur de la série de données sont toutes deux égales à 55, EQUIV ne renvoie que la première catégorie du tableau qu’il rencontre dont la valeur est égale à 55. L’Espagne apparaît donc une seconde fois à la place de la Belgique.

Il existe une astuce simple pour contourner le problème. Afin de différencier très légèrement les valeurs pour que notre formule fonctionne, il suffit de créer une seconde colonne en ajoutant aux valeurs de la première colonne LIGNE( )*0,00…001 (une dizaine de 0 suffisent largement).

Picture4

Ainsi, les valeurs qui étaient identiques dans la colonne initiale mais situées sur des lignes différentes, auront une valeur très légèrement inégale dans cette nouvelle colonne.

 

En changeant la plage utilisée par notre formule, qui va chercher notre nouvelle colonne au lieu de la colonne B, on obtient un top 5 exact.Picture7

Remarque : On a ajouté une liste allant de 1 à 5 à côté de notre top 5, pour pouvoir indexer notre formule dessus et pouvoir l’étirer, plutôt que de demander manuellement la k-ième valeur en remplaçant dans la formule.