Excel: Traiter les valeurs équivalentes dans un top 5

Publié le 10 août 2015
par Sophie Marchand M.Sc., CPA, CGA, MVP
Top5

Excel: Traiter les valeurs équivalentes dans un top 5

Lors d’une de mes formations sur les tableaux de bord dans Excel, j’ai présenté aux apprenants comment construire un top 5 des meilleurs vendeurs, sans passer par un tableau croisé dynamique. La question suivante m’a été posée: Comment traiter les égalités dans un top 5? Cet article vise donc à vous présenter comment construire un top 5 des meilleurs vendeurs, sans passer par un tableau croisé dynamique, et comment traiter les égalités, le cas échéant.

 

Construire le top 5 des ventes

Dans l’exemple ci-bas, on dispose d’une table de ventes avec les ventes des représentants sur 4 ans (cellules K6 à R29). Pour construire un top 5 directement dans Excel, j’ai d’abord inséré une colonne avec les chiffres 1 à 5, dans les cellules B6 à B10. Par la suite, j’ai inséré une colonne “Ventes”, qui utilise la fonction LARGE (ou GRANDE.VALEUR en français). La fonction LARGE (ou GRANDE.VALEUR en français) s’utilise comme un MAX mais il faut déterminer le rang de la valeur recherchée. Ainsi, lorsque j’écris =LARGE(Ventes[2015],B6), je demande à Excel de me rapporter le plus grand montant de la colonne [2015] de la table de ventes. Ensuite, quand je copie cette formule sur la ligne suivante, j’obtiens =LARGE(Ventes[2015],B7) et je demande donc à Excel de me rapporter le deuxième plus grand montant de la colonne [2015] de la table de ventes (puisque B7=2).

Top 5 Vendeurs

 

Trouver le nom des vendeurs correspondants

Pour trouver le nom des vendeurs correspondant au top 5 des ventes, j’ai utilisé la fonction INDEX. Le premier paramètre, Ventes[Rep], indique à Excel que la valeur recherchée se trouve dans la colonne [Rep] de la table de ventes. Le deuxième paramètre, MATCH(D6,Ventes[2015],0) ou en français EQUIV(D6;Ventes[2015];0), rapporte la position du montant de ventes indiqué dans la cellule D6 dans la colonne [2015] de la table de ventes (autrement dit, le numéro de la ligne sur laquelle il apparaît). Finalement, le 3ième paramètre indique que la valeur recherchée se trouvera dans la première colonne de notre table de recherche (qui n’a qu’une seule colonne de toutes façons), soit la colonne [Rep]. En résumé, Excel regarde sur quelle ligne figure le montant 192 dans la colonne [2015] de la table de ventes (22 dans notre exemple) et rapporte ensuite la 22ième ligne de la colonne [Rep], Guy dans notre exemple.

Top 5 Vendeurs

 

Ajouter des informations complémentaires

Dans mon exemple, j’ai également ajouté un format conditionnel, soit une barre de données (data bar en anglais) et un graphique sparkline. Pour en savoir davantage sur les barres de données et les graphiques sparklines, je vous invite à consulter les articles suivants:

 

Traiter les égalités

Les fonctions présentées ci-dessus fonctionnent parfaitement bien, sauf lorsqu’il y a une égalité parmi les 5 plus grandes valeurs. Dans notre exemple, 3 vendeurs ont vendu pour 190$. Comme on utilise une fonction INDEX, Excel rapporte toujours le nom du premier vendeur dans la liste qui a vendu pour 190$, soit Paul, dans notre exemple. Pour régler cette problématique, j’ai modifié légèrement ma table de ventes. D’abord, j’ai ajouté une colonne [Égalités], qui me permet de vérifier si la valeur de ventes de 2015 est unique ou si elle apparaît pour une deuxième ou une troisième fois. Pour ce faire, j’ai utilisé la fonction COUNTIF (ou NB.SI en français).

Top 5 Vendeurs

 

Reconstituer le top 5

Ensuite, j’ai ajouté une dernière colonne à ma table de ventes, soit la colonne [2015Ajust]. Cette colonne me permet simplement de modifier de façon négligeable, les montants de ventes de 2015. Si vous jetez un coup d’oeil aux données du tableau de ventes, vous verrez que 3 représentants ont effectué des ventes de 190$, soit Léon, Alain et Paul. Par contre, dans notre premier tableau (cellules B6 à I10), c’est Paul qui apparaît 3 fois, en position 2, 3, et 4, puisqu’il est le premier qu’Excel trouve dans la colonne [2015] de la table de ventes. La colonne [2015Ajust] recalcule les ventes de 2015, en prenant soin d’ajouter un très faible incrément aux montants égaux, cet incrément dépendant du chiffre de la colonne [Égalités]. Ainsi, lorsqu’un chiffre de ventes apparaît plus d’une fois, le chiffre dans la colonne [Égalités] augmente et le montant de ventes 2015 ajusté également (mais de façon non significative).

Top 5 Vendeurs

 

J’ai donc pu refaire mes formules LARGE (ou Grande.Valeur en français) et INDEX (dans les cellules B13 à I17), mais cette fois-ci, ces calculs ont été basés sur la colonne [2015Ajust] plutôt que sur la colonne [2015]. J’obtiens donc 3 vendeurs ex-aequo, avec 190$ de ventes chacun.

Top 5 Vendeurs

 

Envie d’autres trucs semblables?

Si ce type de trucs vous intéresse, je vous invite à suivre ma série de 3 formation sur les tableaux de bord dans Excel.

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

2 réflexions sur “Excel: Traiter les valeurs équivalentes dans un top 5”

  1. Audrey Boissonneault

    Bonjour,

    Cet article répond exactement à comment arriver à avoir mon top 5. Cependant je n’arrive pas à faire référence à mon tableau croisé dynamique en l’appelant par son nom et en mettant la colonne entre [ ]. Lorsque je fais référence à mon tableau croisé dynamique excel me propose GetPivotData mais cela me donne la somme totale et non la colonne totale. Y a-t-il d’autres moyen de référer à la colonne? Merci pour votre aide.

    1. Bonjour Audrey,

      Je crois que vous confondez “tableaux” et “tableaux croisés dynamiques”.

      Les tableaux sont des données mises sous formes de tableaux, comme expliqué dans l’article suivant: https://www.lecfomasque.com/excel-mettre-sous-forme-de-tableau/. Quand vos données sont sous forme de tableaux, elles agissent dorénavant comme une base de données et on peut donc y référer par noms de tableaux et noms de colonnes (plutôt que par référence d’adresses de cellules).

      Si vous tentez de répéter mon exemple à partir de données mises sous forme de tableaux croisés dynamiques, vous aboutirez effectivement avec des fonctions GetPivotData. Ce n’est pas ce que l’on souhaite ici.

      En espérant que vous pourrez maintenant compléter votre exercice.

      Au plaisir,

      Sophie

Laisser un commentaire

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

Scroll to Top