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 trier les valeurs équivalentes dans un top 5 dans Excel.

Construire le top 5 des ventes dans Excel

Dans l’exemple ci-dessous, 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). Pour le moment, je n’effectue aucun traitement au niveau des valeurs équivalentes de mon top 5 dans Excel. Je cherche simplement les 5 résultats les plus élevés.

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. On peut s’apercevoir qu’il y a des valeurs équivalentes dans mon top 5 dans Excel.

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:

• Excel: 4 façons de présenter les données positives et négatives avec les barres de données
• Excel: Utiliser les « sparklines » pour des tableaux de bord performants

Traiter les valeurs équivalentes dans un top 5 dans Excel

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 la problématique des valeurs équivalentes dans mon top 5 dans Excel, 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).

Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel.

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).

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. C’est ainsi que je peux trier convenablement mes valeurs équivalentes dans mon top 5 dans Excel.

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.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Pour apprendre des façons créatives d’utiliser Excel pour créer des solutions surprenantes et régler des problématiques qui semblent parfois sans solution, suivez la formation Excel – Trucs de pro.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.