Formule générique
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Sommaire
Pour obtenir le nom de la nième valeur la plus grande avec des critères, vous pouvez utiliser INDEX et MATCH, la fonction LARGE et un filtre créé avec la fonction IF. Dans l'exemple illustré, la formule de la cellule G5, copiée vers le bas, est:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
où nom (B5: B16), groupe (C5: C16) et score (D5: D16) sont des plages nommées. La formule renvoie le nom associé aux 1ère, 2ème et 3ème valeurs les plus élevées du groupe A.
Remarque: Il s'agit d'une formule matricielle qui doit être entrée avec Ctrl + Maj + Entrée, sauf dans Excel 365.
Explication
La fonction LARGE est un moyen simple d'obtenir la nième valeur la plus grande d'une plage:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Dans cet exemple, nous pouvons utiliser la fonction LARGE pour obtenir un score le plus élevé, puis utiliser le score comme une "clé" pour récupérer le nom associé à INDEX et MATCH. Notez que nous prenons les valeurs de n dans la plage F5: F7, afin d'obtenir les 1er, 2ème et 3ème scores les plus élevés.
La torsion cependant dans ce cas est que nous devons faire la distinction entre les scores du groupe A et du groupe B. En d'autres termes, nous devons appliquer des critères. Nous faisons cela avec la fonction IF, qui est utilisée pour "filtrer" les valeurs avant qu'elles ne soient évaluées avec LARGE. À titre d'exemple générique, pour obtenir la plus grande valeur (c'est-à-dire la 1ère valeur) de la plage 2 où plage 1 = "A", vous pouvez utiliser une formule comme celle-ci:
LARGE(IF(range="A",range2),1)
Remarque: utiliser IF de cette façon en fait une formule matricielle.
En travaillant de l'intérieur vers l'extérieur, la première étape consiste à obtenir la "1ère" plus grande valeur des données associées au groupe A avec la fonction LARGE:
LARGE(IF(group="A",score),F5)
Dans ce cas, la valeur de F5 est 1, nous demandons donc le score le plus élevé du groupe A. Lorsque la fonction IF est évaluée, elle teste chaque valeur du groupe de plages nommé . Le score de la plage nommée est fourni pour value_if_true. Cela génère un nouveau tableau, qui est renvoyé directement à la fonction LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Notez que les seuls scores qui survivent au filtre proviennent du groupe A. LARGE renvoie alors le score restant le plus élevé, 93, directement à la fonction MATCH en tant que valeur de recherche. Nous pouvons maintenant simplifier la formule pour:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Nous pouvons maintenant voir que la fonction MATCH est configurée en utilisant le même tableau filtré que nous avons vu ci-dessus. La fonction IF filtre à nouveau les valeurs indésirables et la partie MATCH de la formule se résout en:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Puisque 93 apparaît en 3ème position, MATCH renvoie 3 directement à la fonction INDEX:
=INDEX(name,3) // Hannah
Enfin, la fonction INDEX renvoie le nom de la 3ème ligne, "Hannah".
Avec XLOOKUP
La fonction XLOOKUP peut également être utilisée pour résoudre ce problème, en utilisant la même approche expliquée ci-dessus:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Comme ci-dessus, LARGE est configuré pour fonctionner avec un tableau filtré par IF et renvoie un résultat de 93 à XLOOKUP comme valeur de recherche:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Le tableau de recherche est également créé en utilisant IF comme filtre sur les scores du groupe A. Avec le tableau de retour fourni comme nom (B5: B16). XLOOKUP renvoie "Hannah" comme résultat final.
Remarques
- Pour obtenir le nom de la nième valeur avec des critères, (c'est-à-dire limiter les résultats au groupe A ou B), vous devrez étendre la formule pour utiliser une logique supplémentaire.
- Dans Excel 365, la fonction FILTRE est un meilleur moyen de répertorier dynamiquement les résultats supérieurs ou inférieurs. Cette approche gérera automatiquement les liens.
