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).
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.
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 é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).
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.
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.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou info@lecfomasque.com