Formule générique
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Sommaire
Pour obtenir le nom de la nième valeur la plus grande, vous pouvez utiliser INDEX et MATCH avec la fonction LARGE. Dans l'exemple illustré, la formule de la cellule H5 est:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
où nom (B5: B16) et score (D5: D16) sont des plages nommées.
Explication
En un mot, cette formule utilise la fonction LARGE pour trouver la nième valeur la plus grande d'un ensemble de données. Une fois que nous avons cette valeur, nous la connectons à une formule INDEX et MATCH standard pour récupérer le nom associé. En d'autres termes, nous utilisons la nième valeur la plus grande comme une "clé" pour récupérer les informations associées.
La fonction LARGE est un moyen simple d'obtenir la nième valeur la plus grande d'une plage. Fournissez simplement une plage pour le premier argument (tableau) et une valeur pour n comme second argument (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
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 avec la fonction LARGE:
LARGE(score,F5) // returns 93
Dans ce cas, la valeur de F5 est 1, nous demandons donc le 1er score le plus élevé (c'est-à-dire le score le plus élevé), qui est de 93. Nous pouvons maintenant simplifier la formule pour:
=INDEX(name,MATCH(93,score,0))
Dans la fonction INDEX, la fonction MATCH est configurée pour localiser la position de 93 dans le score de plage nommé (D5: D16):
MATCH(93,score,0) // returns 3
Puisque 93 apparaît dans la 3ème ligne, MATCH renvoie 3 directement à INDEX comme numéro de ligne, avec le nom comme tableau:
=INDEX(name,3) // Hannah
Enfin, la fonction INDEX renvoie le nom de la 3ème ligne, "Hannah".
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 lorsque la formule est copiée.
Récupérer le groupe
La même formule de base fonctionnera pour récupérer toutes les informations associées. Pour obtenir le groupe des valeurs les plus élevées, vous pouvez simplement changer le tableau fourni en INDEX avec le groupe de plages nommé :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Avec la valeur 1 dans F5, LARGE obtiendra le score le plus élevé et la formule renverra "A".
Remarque: avec Excel 365, vous pouvez utiliser la fonction FILTRE pour lister dynamiquement les résultats supérieurs ou inférieurs.
Avec XLOOKUP
La fonction XLOOKUP peut également être utilisée pour renvoyer le nom de la nième plus grande valeur comme ceci:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE renvoie la plus grande valeur, 93, directement à XLOOKUP comme valeur de recherche:
=XLOOKUP(93,score,name) // Hannah
Avec le score de plage nommé (D5: D16) comme tableau de recherche et le nom (B5: B16) comme tableau de retour, XLOOKUP renvoie "Hannah" comme auparavant.
Manipulation des cravates
Les valeurs dupliquées dans les données numériques créeront un "lien". Si une égalité se produit dans les valeurs classées, par exemple, si les première et deuxième plus grandes valeurs sont identiques, LARGE renverra la même valeur pour chacune. Lorsque cette valeur est passée dans la fonction MATCH, MATCH renverra la position de la première correspondance, vous verrez donc le même (premier) nom renvoyé.
S'il y a possibilité d'égalité, vous voudrez peut-être mettre en œuvre une sorte de stratégie de bris d'égalité. Une approche consiste à créer une nouvelle colonne d'aide de valeurs qui ont été ajustées pour rompre les liens. Utilisez ensuite les valeurs de la colonne d'assistance pour classer et récupérer des informations. Cela rend la logique utilisée pour rompre les liens claire et explicite.
Une autre approche consiste à rompre les égalités en fonction de la position uniquement (c'est-à-dire que la première égalité "gagne"). Voici une formule qui adopte cette approche:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée, sauf dans Excel 365.
Ici, nous utilisons MATCH pour trouver le nombre 1, et nous construisons un tableau de recherche en utilisant une logique booléenne qui (1) compare tous les scores à la valeur retournée par LARGE:
score=LARGE(score,F5)
et (2) utilise un contrôle de plage extensible si le nom est déjà dans la liste classée:
COUNTIF(H$4:H4,name)=0
Lorsqu'un nom est déjà dans la liste, il est "annulé" par la logique et la valeur (dupliquée) suivante est mise en correspondance. Notez que la plage d'expansion commence sur la ligne précédente, afin d'éviter une référence circulaire.
Cette approche fonctionne dans cet exemple car il n'y a pas de noms en double dans la colonne de nom. Cependant, si des noms en double se produisent dans les valeurs classées, l'approche doit être ajustée. La solution la plus simple est de s'assurer que les noms sont uniques.
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.
