Všeobecný vzorec
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Zhrnutie
Ak chcete filtrovať údaje tak, aby sa zobrazilo n najvyšších hodnôt, ktoré vyhovujú konkrétnym kritériám, môžete použiť funkciu FILTER spolu s funkciami LARGE a IF. V zobrazenom príklade je vzorec v F5:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
kde údaje (B5: D16), skupina (C5: C16) a skóre (D5: D16) sú pomenované rozsahmi.
Vysvetlenie
Tento vzorec využíva funkciu FILTER na získanie údajov na základe logického testu zostaveného s funkciami VEĽKÉ a IF. Výsledkom sú 3 najlepšie skóre v skupine B.
Funkcia FILTER použije kritériá s argumentom include. V tomto príklade sú kritériá konštruované s logickou logikou takto:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Ľavá strana výrazu zacieľuje na skóre vyššie alebo rovné 3. najvyššiemu skóre v skupine B:
score>=LARGE(IF(group="b",score),3)
Funkcia IF sa používa na zabezpečenie toho, aby funkcia LARGE fungovala iba so skóre skupiny B. Pretože máme celkovo 12 skóre, IF vráti pole s 12 výsledkami, ako je tento:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Všimnite si, že jediné skóre, ktoré operáciu prežije, je zo skupiny B. Všetky ďalšie skóre sú NEPRAVDIVÉ. Toto pole sa vráti priamo do parametra LARGE ako argument poľa:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE ignoruje FALSE hodnoty a vráti 3. najvyššie skóre, 83.
Teraz môžeme vzorec zjednodušiť na:
=FILTER(data,(score>=83)*(group="b"))
ktorý sa rozhodne:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Nakoniec FILTER vráti záznamy pre Masona, Annie a Cassidy, ktoré sa rozliali do rozsahu F5: H7.
Výsledky zoraďte podľa skóre
V predvolenom nastavení FILTER vráti zodpovedajúce záznamy v rovnakom poradí, v akom sa vyskytujú v zdrojových dátach. Ak chcete výsledky zoradiť zostupne podľa skóre, môžete vnoriť pôvodný vzorec FILTER do funkcie SORT takto:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
FILTER tu vracia výsledky priamo do funkcie SORT ako argument poľa. Index zoradenia je nastavený na 3 (skóre) a poradie zoradenia je nastavené na -1 pre zostupné poradie.
