Sommaire
Pour afficher une liste de noms, classés par une valeur numérique, vous pouvez utiliser un ensemble de formules basées sur LARGE, INDEX, MATCH, avec l'aide de la fonction TEXT. Dans l'exemple illustré, la formule en G5 est:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Et la formule du G10 est:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
où le client (B5: B17) Date (C5: C17) et quantité (C5: C17) sont des plages nommées.
Remarque: ce sont des formules matricielles et doivent être saisies avec Ctrl + Maj + Entrée, sauf dans Excel 365.
Explication
Cet exemple est mis en place en deux parties pour plus de clarté: (1) une formule pour déterminer les 3 premiers montants pour chaque mois et (2) une formule pour récupérer le nom du client pour chacun des 3 premiers montants mensuels.
Notez qu'il n'y a pas de classement réel dans les données source. Au lieu de cela, nous utilisons la fonction LARGE pour travailler directement avec des montants. Une autre approche consisterait à ajouter un rang aux données source avec la fonction RANK et à utiliser la valeur de rang pour récupérer les noms de client.
Partie 1: récupérer les 3 premiers montants chaque mois
Pour récupérer les 3 premiers montants pour chaque semaine, la formule dans G5 est:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée, sauf dans Excel 365.
En travaillant de l'intérieur vers l'extérieur, nous utilisons d'abord la fonction TEXT pour obtenir les noms de mois pour chaque date dans la plage de dates nommée :
TEXT(date,"mmmm") // get month names
Le format numérique personnalisé «mmmm» renverra une chaîne comme «avril», «mai», «juin» pour chaque nom dans la plage de dates nommée . Le résultat est un tableau de noms de mois comme celui-ci:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
La fonction TEXT fournit ce tableau à la fonction IF, qui est configurée pour filtrer les dates sur un mois donné en testant le nom du mois par rapport à la valeur dans G4 (une référence mixte, de sorte que la formule peut être copiée vers le bas et à travers):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Seuls les montants en avril survivent et passent par IF; toutes les autres valeurs sont FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Enfin, la fonction LARGE utilise la valeur de F5 (également une référence mixte) pour renvoyer la «nième» plus grande valeur qui reste. Dans la cellule G5, LARGE renvoie 18 500, la "1ère" valeur la plus élevée. Lorsque la formule est copiée vers le bas et à travers le tableau, la fonction LARGE renvoie les 3 premiers montants pour chacun des trois mois.
Maintenant que nous connaissons les 3 premières valeurs de chaque mois, nous pouvons utiliser ces informations comme une «clé» pour récupérer le nom du client pour chacune.
Partie 2: récupérer les noms des clients
Remarque: Ceci est un exemple d'utilisation d'INDEX et de MATCH avec plusieurs critères. Si ce concept est nouveau pour vous, voici un exemple de base.
Pour récupérer le nom associé aux trois premières valeurs dans G5: I7, nous utilisons INDEX et MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée, sauf dans Excel 365.
Travaillant de l'intérieur vers l'extérieur, la fonction MATCH est configurée pour utiliser la logique booléenne comme ceci:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
La valeur de recherche est 1 et le tableau de recherche est construit avec cette expression:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
L'expression qui crée le tableau de recherche utilise la logique booléenne pour "filtrer" les montants qui ne sont (1) pas en avril et (2) pas la valeur dans G5 (18 500). Le résultat est un tableau de 1 et de 0 comme ceci:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Avec une valeur de recherche de 1 et zéro pour le type de correspondance (pour forcer une correspondance exacte) MATCH renvoie 3 directement à la fonction INDEX:
=INDEX(client,3) // returns "Janus"
INDEX renvoie la troisième valeur du client de plage nommé, "Janus".
Au fur et à mesure que la formule est copiée vers le bas et à travers le tableau, elle renvoie les 3 premiers clients pour chacun des trois mois.
