Si vous avez besoin d'afficher un palmarès, un hit-parade ou une scorecard (excusez le franglais) affichant en permanence des résultats triés selon une performance donnée, vous pouvez vous heurter à l'absence de dynamisme des plages Excel.
L'exemple traité ici reprend la trame du précédent, et a pour objectif d'afficher les ventes de fruits en commençant par ceux dont la quantité est la plus élevée.
Voici un exemple de saisie des quantités vendues. Notez que les colonnes (des lignes 5 à 30) ont été nommées Fruit et Quantités (commande de menu Insertion > Nom > Créer > Ligne du haut). Comment calculer et afficher les quantités de fruits vendues en les classant par ordre décroissant de quantités ?
La table des fruits s'enrichit de deux colonnes :
1. Une colonne calculant les totaux de quantités pour chaque fruit (C).
2. Une colonne calculant le rang de chaque fruit en fonction des quantités vendues (D).
La colonne C (plage baptisée TableQuantités) utilise la formule suivante :
=SOMME.SI(Fruit;B3;Quantité)
Cette fonction effectue la somme de la plage des Quantités lorsque la plage Fruit est égale à la cellule B3, soit Pomme. Recopiée vers le bas, elle se décline pour les autres fruits.
La colonne D utilise une fonction qui renvoie la place (rang) d'une valeur dans une liste :
=RANG(C3;TableQuantités)
La fonction examine le rang de la cellule C3 (494,42 Kg) dans la plage des quantités (cellules C3 à C9) et renvoie donc 1 pour les pommes, 2 pour les citrons, 3 pour les oranges, etc.
Nommez la plage D3:D9 selon la même racine que les autres : TableRang.
Vous remarquez que si le hasard a correctement placé les pommes en ligne 1, les citrons (deuxième position au palmarès) restent à leur place en ligne 7 alors qu'ils sont deuxièmes au classement. Vous pourriez bien sûr trier le tableau par ordre croissant de rang, mais l'opération devra être répétée à chaque fois que les données saisies changent, ce qui est malcommode (une macro en VBA pourrait être écrite, mais ce serait peu élégant).
Un second tableau est créé sous le premier. Sa troisième colonne (D, désignée par les flèches bleues) comporte une simple suite de nombres de 1 à 7, absolument statiques. Comment retrouver dans la colonne B la valeur du fruit selon sa place ?
Une combinaison des fonctions INDEX et EQUIV fait l'affaire (voir l'article qui leur est consacré sur ce blog, ainsi que les conseils 62 et 63 du livre, à partir de la page 216).
Écrivez donc en B14 la formule suivante :
=INDEX(TableFruits;EQUIV(D14;TableRang;0))
La formule renvoie la ligne de TableFruits (INDEX) équivalant à la valeur 1 (la première place du palmarès) dans la plage TableRang (D3:D9). Dans l'exemple, ce sont bien les Pommes.
Dans la cellule B15, la formule devient :
=INDEX(TableFruits;EQUIV(D15;TableRang;0))
Elle renvoie alors le Citron, qui est bien à la deuxième place du classement.
La colonne des quantités est calculée selon la même formule, dans laquelle TableQuantités remplace TableFruits. Elle affiche donc les quantités de pommes (494,42 kg), de citrons (379,11 kg), d'oranges (246 kg), etc.
Lorsque la saisie des ventes évolue, le palmarès se réorganise instantanément, sans qu'aucune intervention manuelle (un tri) ou animée (une macro VBA) ne soient nécessaires.
Dans l'exemple, nous avons placé des formules aléatoires (ALEA) dans le tableau de saisie, ce qui permet de varier le palmarès à chaque déclenchement des calculs (touche F9 sous Windows et Commande-= sous Mac OS).
Mes remerciements vont à une participante à un stage de formation que j'ai animé récemment, et qui m'a fait découvrir la fonction RANG que, ma foi, je n'avais jamais remarquée !
Le classeur ayant servi à cet exemple est à votre disposition sur simple demande. Utilisez la page contacts de mon site de références professionnelles http://jeanluctafforeau.com
Aucun commentaire:
Enregistrer un commentaire