Chapitre 17 · 17.16
Afficher le top 5 par catégorie
Les produits les plus vendus par service, les plus gros clients par région, les jours les plus chargés par mois : vous ne voulez pas le top 5 de toute la liste, mais le top 5 au sein d'une seule catégorie. GRANDE.VALEUR travaille sur toute la colonne et renvoie deux fois le même produit en cas de montants identiques. Dans Euro-Office, une seule formule suffit pour résoudre ce problème.
Prenons comme exemple le chiffre d'affaires annuel par produit de Bloemisterij Het Zonnetje : la colonne A contient le produit, la colonne B la catégorie (Rozen, Tulpen, Planten) et la colonne C le chiffre d'affaires, de la ligne 2 à la ligne 18. La cellule F1 contient la catégorie que vous souhaitez afficher : Rozen.
Le top 5 en une seule formule
Saisissez dans H2 :
=PRENDRE(TRIER(FILTRE(A2:C18;B2:B18=F1);3;-1);5)
Lisez-la de l'intérieur vers l'extérieur :
- FILTRE(A2:C18;B2:B18=F1) ne conserve que les lignes de la catégorie indiquée dans F1, avec les trois colonnes.
- TRIER(...;3;-1) trie ces lignes sur la troisième colonne (le chiffre d'affaires), de la plus grande à la plus petite valeur (-1).
- PRENDRE(...;5) conserve les 5 premières lignes.
Appuyez sur Entrée : les cinq produits apparaissent avec leur catégorie et leur chiffre d'affaires dans les cellules H2 à J6. Si vous saisissez Planten dans F1, la liste change immédiatement.

Les montants identiques ne posent aucun problème : dans l'exemple, Grand Prix et Ecuadoriaanse roos ont tous deux un chiffre d'affaires de 3 400, et les deux figurent dans la liste. Avec deux fois GRANDE.VALEUR combinée à INDEX et EQUIV, Grand Prix apparaîtrait deux fois.
Uniquement les montants, par catégorie côte à côte
Si vous souhaitez uniquement les cinq montants les plus élevés, par exemple pour plusieurs catégories côte à côte, utilisez GRANDE.VALEUR sur une liste filtrée. Placez les catégories dans F1, G1 et ainsi de suite, sélectionnez F2:G6, saisissez :
=SIERREUR(GRANDE.VALEUR(FILTRE($C$2:$C$18;$B$2:$B$18=F$1);LIGNES($1:1));"")
puis appuyez sur Ctrl+Entrée. FILTRE ne conserve que les montants de la catégorie indiquée en ligne 1. LIGNES($1:1) vaut 1 dans la première ligne et devient 2, 3, 4 et 5 dans les lignes suivantes : la plus grande valeur, la deuxième plus grande, et ainsi de suite. SIERREUR laisse la cellule vide lorsqu'une catégorie compte moins de cinq produits ; Tulpen n'en compte que quatre.
Attention : sur les sites consacrés à Excel, on trouve souvent =GRANDE.VALEUR(SI($B$2:$B$18=F$1;$C$2:$C$18);LIGNES($1:1)). Validée avec une simple pression sur Entrée, cette formule donne un résultat erroné dans Euro-Office : SI ne tient alors pas compte de toute la colonne, de sorte que vous obtenez les montants les plus élevés de toutes les catégories, ou #NOMBRE!. Si vous la validez avec Ctrl+Shift+Entrée, la formule reçoit des accolades {…} et le résultat est correct. La méthode avec FILTRE présentée ci-dessus est plus simple.
Uniquement le montant le plus élevé
Pour une seule valeur, c'est plus simple : =MAX.SI.ENS(C2:C18;B2:B18;F1) renvoie le chiffre d'affaires le plus élevé de la catégorie, ici 6 150.
Pièges à éviter
- Moins de 5 produits dans une catégorie : sans SIERREUR, GRANDE.VALEUR renvoie #NOMBRE! pour les lignes qui n'existent pas. PRENDRE n'a pas ce problème et affiche simplement moins de lignes.
- La formule avec PRENDRE reste vide : quelque chose se trouve dans les 5 lignes et 3 colonnes où le résultat doit s'afficher. Consultez Des listes qui se mettent à jour toutes seules avec FILTRE, UNIQUE et TRIER.
- Un collègue qui utilise Excel 2019 ou une version antérieure voit #NOM? avec PRENDRE, TRIER et FILTRE.
