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); E9) } |
E9 est la cellule où se trouve le rang (1,2, etc.) à afficher.
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 F9 sera modifiée en :
| {=GRANDE.VALEUR(ARRONDI(ma liste de salaires;2)+LIGNE(ma liste de salaires)*1e-12; E9) } |
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 F9 vers le bas dans F10-F13. Nous appliquons maintenant les mêmes fonctions ARRONDI et LIGNE à la fonction INDEX / EQUIV de la cellule G9.
| {=INDEX(ma liste de pays ; EQUIV(F6;(ARRONDI(ma liste de salaires;2)+LIGNE(ma liste de salaires)*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 G10-G13.
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échargez 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.
Formules Excel pour réaliser des classements Top/Flop
Envoyer le lien de téléchargement à :
Dites-moi en commentaire si vous avez d’autres exemples d’utilisation des fonctions GRANDE.VALEUR et PETITE.VALEUR ! Merci beaucoup de votre lecture !
Enfin, si vous souhaitez apprendre une méthode rapide et efficace pour collecter et analyser vos données dans Excel à l’aide de PowerQuery ou Power BI, et créer des rapports dynamiques, je vous propose un programme 100% en ligne spécialement conçu pour découvrir et maîtriser les Power Tools de Microsoft facilement👏👏 :
🥇MAITRISEZ L’ANALYSE DES DONNEES AVEC EXCEL ET POWER BI
(RECEVEZ 1H DE FORMATION GRATUITE)
Excellent tutoriel !
Merci !
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
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.
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
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 !
Merci, mais comment faire si deux valeurs de salaires sont strictement identiques ?
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
merci beaucoup j’apprend et je m’adapte très bien avec votre méthode pédagogique et encore merci.
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 🙂
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.
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
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 !
Oups, j’avais oublié de regarder.
J’ai téléchargé la dernière version, c’est parfait.
Merci
Ce sera un fichier très utile
Bon courage
De rien 😉
bonjour,
A l’aide, je cherche à faire un truc simple, mais je bloc
– j’ai des commerciaux qui font un CA, et j’ai besoin d’un classement automatique :
colonne M8 à M24 noms des commerciaux qui doit remonter ou descendre selon leurs CA (colonne B8 à B24)
– en colonne N8 à N24 j’ai le classement de 1 à 17
Exemple : Si B9 est le chiffre du commercial thomas, son CA augmente (3000€) et que B13 est le chiffre du commercial Isabelle et qu’elle est à 2500€, je veux que thomas change de place dans le tableau et lui monte dessus… mais je veux garder dans les deux colonnes que le nom du commercial et sa position (genre : 1er, 2eme;3éme et non son CA .
Espérant avoir été claire et dans l’attente de vous lire
PS : j’ai déjà fait la formule RANG mais celle ci ne fait ni remonter ni descendre les noms…
Mille merci pour ce tuto très bien expliqué et qui m’a été d’une grande aide. Vous maîtrisez l’art de simplifier les choses qui nous paraissent si compliquées. 🙂
Bonjour et merci pour votre commentaire !
merci infiniment , j’ai aimé. c’était mes interrogations ces derniers temps. l’aspect actualisation des TCD me paraissait vraiment contraignant . merci encore
Bonjour,
Un grand merci pour ce tuto très clair qui répond en grande partie à mon besoin.
Ma plage de “Pays” & “Salaire minimum” (pour reprendre votre exemple) est issu d’un tableau croisé dynamique, le nombre de ligne peut donc augmenter. Or, si “Ma lise de salaire” fait référence à des cellules vides, le FLOP 1 me renvoit 0 correspondant à une cellule vide.
Je m’explique : actuellement la plage de cellules avec des données s’arrêtent à la ligne 257, mais dans la formule j’indique la ligne 500 pour prévoir les nouvelles lignes qui s’intégreront après actualisation du tableau croisé dynamique.
Auriez-vous une astuce pour y remédier ?
Je vous remercie de votre aide.
Bonjour Claudie,
Le but de mon article est de montrer une alternative pour la création de TOP/FLOP sans Tableau Croisé Dynamique.
Si vos données sont issues de TCD, c’est beaucoup plus simple de créer un TOP/FLOP ! Il suffit pour obtenir un TOP/FLOP de trier et filtrer votre TCD sur les valeurs pour n’afficher que les 10 premières valeurs par exemple et les trier par ordre croissant ou décroissant pour avoir votre TOP/FLOP.
Ensuite, vous n’avez plus qu’à actualiser votre TCD lorsque vos données sont mises à jour.
Lionel
exercice très intéressant et amusant
Bonjour,
Tout d’abord, merci beaucoup pour ce tutoriel qui me semble très pertinent. En revanche, même après que vous ayez donné une solution pour qu’il n’y ait pas de doublons. Une question subsiste tout de même. Comment faire si nos nombres sont des nombres entiers sans décimales ? Une fois qu’il ont été multipliés par 1e-12 restent tous identiques donc Excel ne fait toujours pas de différence. Dans mon cas, ce sont des performances individuelles en pourcentage (90%,110%, 132% etc…) que je cherche à relier à un NOM de famille.
Je vous remercie par avance.
Quelques erreurs dans l’article (dans le fichier c’est correct) :
{=GRANDE.VALEUR(ARRONDI(ma liste de salaires;2)+LIGNE(E6)*1e-12; E6) }
–> {=GRANDE.VALEUR(ARRONDI(ma liste de salaires;2)+LIGNE(ma liste de salaires)*1e-12; E6) }
{=INDEX(ma liste de pays ; EQUIV(F6;(ARRONDI(ma liste de salaires;2)+LIGNE(F6)*1e-12;0))}
–> {=INDEX(ma liste de pays ; EQUIV(F6;(ARRONDI(ma liste de salaires;2)+LIGNE(ma liste de salaires)*1e-12;0))}
Merci en tout cas, ca m’a bien aidé 🙂
Bonjour,
Merci pour ce tutoriel très bien fait ! Avez-vous des solutions pour aller encore plus loin et créer un Top de plusieurs Top ? Je m’explique :
Imaginons que je souhaite classer les pays par ordre dans lesquels j’aimerais m’y installer. Pour cela 2 critères : votre classement des salaires minimum + un classement du prix de l’essence par pays.
Je souhaite tirer de ces 2 classements un seul classement ; du pays le plus accueillant au pays le moins accueillant selon ces deux critères.
Merci pour toute réponse que vous pourrez m’apporter !
Bonjour, peut-être en construisant un indicateur “Score” qui combinerait les salaires et le prix de l’essence en une valeur qui serait ensuite traitée comme l’exemple des salaires dans l’article ?
Très bonne explication
Mais ne serait-il pas plus simple d’utiliser l’outil Tableau dans la base afin d’automatiser les formules?
Bonne soirée
Laurent
Bonjour, je ne suis pas sûr de comprendre ce que vous suggérez par “automatiser les formules” pour simplifier ?
Bonjour et merci pour votre tutoriel.
Supposons qu’à la 5ième place, on a 3 pays qui ont la même valeur, comment les répercuter sur le top 5 ?
Merci d’avance de votre réponse.
Bonjour, avez-vous essayé de transformer les valeurs égales en valeurs uniques (au paragraphe “Trouver les libellés quand le Top 5 ne contient pas que des valeurs uniques”) ? Cela devrait fonctionner