Splošna formula
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Povzetek
Če želite filtrirati podatke in prikazati zgornjih n vrednosti, ki ustrezajo določenim merilom, lahko uporabite funkcijo FILTER skupaj s funkcijama LARGE in IF. V prikazanem primeru je formula v F5:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
kjer so podatki (B5: D16), skupina (C5: C16) in rezultat (D5: D16) imenovani obsegi.
Pojasnilo
Ta formula uporablja funkcijo FILTER za pridobivanje podatkov na podlagi logičnega testa, izdelanega s funkcijama LARGE in IF. Rezultat so top 3 rezultati v skupini B.
Funkcija FILTER uporablja kriterije z argumentom include. V tem primeru so merila zgrajena z logično logiko, kot je ta:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Leva stran izraza cilja na rezultate, večje ali enake 3. najvišji oceni v skupini B:
score>=LARGE(IF(group="b",score),3)
Funkcija IF se uporablja za zagotovitev, da LARGE deluje samo z rezultati skupine B. Ker imamo skupaj 12 rezultatov, IF vrne matriko z 12 rezultati, kot je ta:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Upoštevajte, da so edini rezultati, ki so preživeli operacijo, iz skupine B. Vsi ostali rezultati so LAŽNI. Ta matrika se vrne neposredno v LARGE kot argument matrike:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE prezre vrednosti FALSE in vrne 3. najvišjo oceno, 83.
Zdaj lahko formulo poenostavimo na:
=FILTER(data,(score>=83)*(group="b"))
ki se razreši na:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Končno FILTER vrne zapise za Masona, Annie in Cassidyja, ki se prelijejo v območje F5: H7.
Razvrsti rezultate po rezultatih
Privzeto bo FILTER vrnil ujemajoče se zapise v enakem vrstnem redu, kot so prikazani v izvornih podatkih. Če želite rezultate razvrstiti po padajočem vrstnem redu po rezultatih, lahko prvotno formulo FILTER ugnezdiš v funkcijo SORT tako:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Tu FILTER vrne rezultate neposredno v funkcijo SORT kot argument matrike. Za padajoči vrstni red je indeks razvrščanja nastavljen na 3 (rezultat), vrstni red razvrščanja pa na -1.
