Formule générique
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Sommaire
Pour filtrer les données afin d'afficher les n premières valeurs qui répondent à des critères spécifiques, vous pouvez utiliser la fonction FILTRE avec les fonctions LARGE et IF. Dans l'exemple illustré, la formule en F5 est:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
où les données (B5: D16), le groupe (C5: C16) et le score (D5: D16) sont des plages nommées.
Explication
Cette formule utilise la fonction FILTER pour récupérer des données sur la base d'un test logique construit avec les fonctions LARGE et IF. Le résultat est les 3 meilleurs scores du groupe B.
La fonction FILTER applique des critères avec l'argument include. Dans cet exemple, les critères sont construits avec une logique booléenne comme ceci:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Le côté gauche de l'expression cible des scores supérieurs ou égaux au 3e score le plus élevé du groupe B:
score>=LARGE(IF(group="b",score),3)
La fonction IF est utilisée pour s'assurer que LARGE ne fonctionne qu'avec les scores du groupe B. Parce que nous avons un total de 12 scores, IF renvoie un tableau avec 12 résultats comme celui-ci:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Notez que les seuls scores qui survivent à l'opération proviennent du groupe B. Tous les autres scores sont FAUX. Ce tableau est renvoyé directement à LARGE comme argument de tableau:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE ignore les valeurs FALSE et renvoie le troisième score le plus élevé, 83.
Nous pouvons maintenant simplifier la formule pour:
=FILTER(data,(score>=83)*(group="b"))
qui se résout à:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Enfin, FILTER renvoie des enregistrements pour Mason, Annie et Cassidy, qui se retrouvent dans la plage F5: H7.
Trier les résultats par score
Par défaut, FILTER renvoie les enregistrements correspondants dans le même ordre qu'ils apparaissent dans les données source. Pour trier les résultats par ordre décroissant de score, vous pouvez imbriquer la formule FILTER d'origine dans la fonction TRIER comme ceci:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Ici, FILTER renvoie les résultats directement à la fonction SORT comme argument de tableau. L'index de tri est défini sur 3 (score) et l'ordre de tri sur -1, par ordre décroissant.
