Formules Excel pour créer facilement un classement automatique TOP/FLOP

(Last Updated On: 16 décembre 2018)

Aujourd’hui, nous allons créer une liste Top/Flop automatique. Ceci peut être très utile pour comparer dans une liste de produits ceux qui se vendent le mieux et ceux qui se vendent le moins par exemple.

Ici, j’ai pris comme exemple de données la liste des salaires minimum en Europe au second semestre 2017.

Bon, l’exemple des salaires minimum n’est pas vraiment un Top/Flop mais ça donne le principe pour créer des classements de tout type.

Nous voulons générer un classement des 5 pays avec le salaire minimum le plus élevé, ainsi que les 5 pays avec le salaire minimum le moins élevé. Et ce classement se met à jour automatiquement quand les données changent.

Pour cela, on pourrait utiliser un tableau croisé dynamique et trier les pays par salaire décroissant pour obtenir notre TOP, puis créer un deuxième TCD pour trier les pays par salaire croissant pour obtenir notre FLOP, mais cela nous oblige à devoir actualiser les TCD à chaque fois que les données changent, ce qui peut être contraignant.

La solution présentée ci-dessous utilise uniquement des formules EXCEL, donc pas de TCD à mettre à jour ni de macro VBA à maintenir pour l’utilisateur.  🙂

Voici le résultat :

Création de la liste Top 5 avec la formule GRANDE.VALEUR

Premièrement, j’utilise la fonction GRANDE.VALEUR sur la liste des salaires minimum. La syntaxe est la suivante :

=GRANDE.VALEUR(ma liste de salaires; chiffre 1)

Ensuite, on déroule la formule en prenant un chiffre croissant jusqu’à 5. Finalement, on obtient le classement des 5 salaires les plus élevés.

Deuxièmement, pour retrouver le pays correspondant à chaque valeur de salaire, j’effectue une recherche via le combo INDEX-EQUIV :

=INDEX(ma liste de pays;EQUIV(valeur du salaire 1;ma liste de salaires;0))

Ensuite, idem on déroule la formule en prenant les valeurs de salaires suivantes.

Création de la liste Flop 5 avec la formule PETITE.VALEUR

Le principe pour créer le classement des salaires les moins élevés est le même que pour le classement des salaires les plus élevés.

Il suffit de remplacer la formule GRANDE.VALEUR par la formule PETITE.VALEUR :

=PETITE.VALEUR(ma liste de salaires; chiffre 1)

La recherche des pays correspondants au classement des salaires ne change pas :

=INDEX(ma liste de pays;EQUIV(valeur du salaire 1;ma liste de salaires;0))

EDIT : Cependant, il y a un gros problème, comme me l’ont fait remarquer mes lecteurs (merci !) – nos valeurs du Top 5 ne sont pas uniques. Ici il existe 2 valeurs de 1563. Comme INDEX / EQUIV ne renvoie que la première valeur trouvée, le nom du pays n°1 est renvoyé 2 fois. C’est clairement une erreur. Alors, comment pouvons-nous résoudre cela ?

Trouver les libellés quand le Top 5 ne contient pas que des valeurs uniques

Pour résoudre ce problème, nous allons d’abord arrondir les valeurs, puis utiliser la fonction LIGNE pour créer une extension unique qui garantira que les valeurs sont toujours uniques. Continuez à lire, tout deviendra clair.

Tout d’abord, nous devons décider d’un niveau de précision acceptable. Par exemple, si nos nombres sont précis à 2 décimales près, nous devrions arrondir la fonction GRANDE.VALEUR à ce niveau.

{=GRANDE.VALEUR(ARRONDI(ma liste de salaires;2); E6) }

Ceci est maintenant une formule matricielle, ne tapez pas les {}, mais appuyez sur Ctrl + Maj + Entrée pour entrer la formule, Excel saisira les crochets {} automatiquement.
Maintenant que nous avons arrondi les données, nous pouvons ajouter un petit nombre à la fin de nos valeurs pour les rendre uniques. Nous utilisons la fonction LIGNE() pour cela. La formule dans la cellule F6 sera modifiée en :

{=GRANDE.VALEUR(ARRONDI(ma liste de salaires;2)+LIGNE(E6)*1e-12; E6) }

Cette formule est également une formule matricielle. N’oubliez donc pas de saisir la formule avec Ctrl + Maj + Entrée.
En ajoutant la valeur de la ligne multipliée par un petit nombre, par exemple 1e-12, nous créons des nombres uniques. Etant donné que nous avons arrondi la valeur avant d’ajouter le numéro LIGNE, il est garanti que chaque nombre est unique. Le but est que le numéro de ligne ajoute des décimales supplémentaires au lieu de changer la valeur du nombre arrondi.

Copiez la formule dans la cellule F6 vers le bas dans F7-F10. Nous appliquons maintenant les mêmes fonctions ARRONDI et LIGNE à la fonction INDEX / EQUIV de la cellule G6.

{=INDEX(ma liste de pays ; EQUIV(F6;(ARRONDI(ma liste de salaires;2)+LIGNE(F6)*1e-12;0))}

Encore une fois, il s’agit d’une formule matricielle – entrez avec Ctrl + Maj + Entrée. Copiez cette formule dans les cellules G7-G10.

Notre liste du Top 5 ressemble à ceci :

Comme vous pouvez le voir sur l’animation au début de l’article, tout changement de valeur dans les données se répercute dans le classement TOP 5 et FLOP 5 !

TELECHARGER LE FICHIER D’EXEMPLE

Télécharger le classeur Excel pour ce tutoriel. Seuls votre adresse e-mail et nom vous seront demandés dans la prochaine étape. Vous recevrez le lien de téléchargement par e-mail.

 

Dites-moi en commentaire si vous avez d’autres exemples d’utilisation des fonctions GRANDE.VALEUR et PETITE.VALEUR ! Merci beaucoup de votre lecture !

 

 

13 commentaires

  1. Jacques dit

    Bonsoir,

    Comment gérer vous les doublons ? Imaginons que deux pays aient le même salaire minimum/maximum, quel serait le résultat ?
    La question n’est pas piègeuse et je suis en plein dans cette problématique sans arriver à m’en sortir 😉

    Bien cordialement,

    Jacques

  2. Kaven dit

    Super tutoriel, ma aider grandement avec quelque chose que j’ai essayer de faire durant 1 mois.

    Le seul petit bémol que j’ai c’est que dans un de mes tableau j’ai 2 ou 3 fois la même valeur mais il me donne toujours le même nom ( soit le premier qui apparaît dans la liste ).
    Il y aurait un option pour éviter cela ? Je m’en sert pour un rendement d’employer.

  3. Benoît dit

    Bonjour,

    Votre post m’a bien aidé et je vous en remercie.
    Par contre, lorsque 2 valeurs sont identiques. Excel reprend, par défaut, l’ordre alphabétique.

    Avez-vous une solution à ce problème? Je cherche mais ne parviens pas à trouver.

  4. CHALAMET dit

    Bonjour,

    Très bon tutoriel.

    Un petit problème cependant que je n’arrive pas à résoudre :
    Si deux pays dans votre exemple ont le même salaire alors dans le top la valeur ressort deux fois mais avec le même pays.
    Y’a t il un moyen de contourner ce désagrément ?
    Merci par avance
    Cordialement
    Guillaume

  5. ThibGUY dit

    Merci pour ce tutoriel excellent. J’ai juste une question : comment ferais tu si deux valeurs de salaires étaient identiques ? Car dans ce cas Excel affiche deux fois le même pays. Merci de ta réponse !

  6. Elyes Aouadi dit

    Bonjour,

    Dans le classement lorsqu’il y’a un salaire équivalent entre 2 pays (ex: France 2000€ et Belgique 2000€). Le classement montre un seul des 2 pays (France – France) Comment peut on remedier à ça ?

    Merci

  7. Guillaume dit

    Petite question… Il se trouve que j’ai deux salaires égaux. Du coup, dans mon classement au lieu d’avoir, par exemple:

    1. 6’000.- France
    2. 6’000.- Allemagne

    J’ai:

    1. 6’000.- France
    2. 6’000.- France

    Que faire? Merci 🙂

  8. Robin dit

    Superbe exemple, merci beaucoup, très intuitif.

    Pour ma part, il s’agit de mieux se rendre compte au niveau des dépenses mensuelles à quelle période nous dépensons le plus.

  9. Gilles Jalucyne dit

    Bonjour,
    Merci pour tous ces exemples très utiles
    A propos du fichier Top flop
    Sauf erreur de ma part dans la feuille Val Identiques , la formule en G9 n’a pas été mise à jour et j’avoue ne pas réussir à la corriger., même avec tes explications sur l’article…
    Bonne fêtes de fin d’année
    Cordialement
    Gilles Jalucyne

    • Lionel dit

      Bonjour Gilles,
      Meilleurs vœux 2019 !
      Je n’avais pas mis à jour avec la dernière version du fichier, c’est chose faite maintenant, il ne devrait plus y avoir de problème.
      Merci !

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *