Formula generică
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
rezumat
Pentru a filtra date pentru a afișa primele n valori care îndeplinesc criterii specifice, puteți utiliza funcția FILTER împreună cu funcțiile LARGE și IF. În exemplul prezentat, formula din F5 este:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
unde datele (B5: D16), grupul (C5: C16) și scorul (D5: D16) sunt denumite intervale.
Explicaţie
Această formulă folosește funcția FILTER pentru a prelua date pe baza unui test logic construit cu funcțiile LARGE și IF. Rezultatul este primele 3 scoruri din grupa B.
Funcția FILTER aplică criterii cu argumentul include. În acest exemplu, criteriile sunt construite cu logică booleană astfel:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Partea stângă a expresiei vizează scoruri mai mari sau egale cu al 3-lea cel mai mare scor din grupa B:
score>=LARGE(IF(group="b",score),3)
Funcția IF este utilizată pentru a vă asigura că LARGE funcționează numai cu scorurile grupului B. Deoarece avem 12 scoruri în total, IF returnează o matrice cu 12 rezultate ca aceasta:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Observați că singurele scoruri care supraviețuiesc operațiunii provin din grupa B. Toate celelalte scoruri sunt FALSE. Această matrice este returnată direct la LARGE ca argument al matricei:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE ignoră valorile FALSE și returnează al treilea cel mai mare scor, 83.
Acum putem simplifica formula pentru:
=FILTER(data,(score>=83)*(group="b"))
care rezolvă:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
În cele din urmă, FILTER returnează înregistrări pentru Mason, Annie și Cassidy, care se revarsă în intervalul F5: H7.
Sortează rezultatele după scor
În mod implicit, FILTER va returna înregistrările potrivite în aceeași ordine în care apar în datele sursă. Pentru a sorta rezultatele în ordine descrescătoare după scor, puteți cuibula formula FILTRU originală în interiorul funcției SORT astfel:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Aici, FILTER returnează rezultatele direct la funcția SORT ca argument al matricei. Indexul de sortare este setat la 3 (scor) și ordinea de sortare este setată la -1, pentru ordinea descrescătoare.
