Obecný vzorec
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
souhrn
Chcete-li filtrovat data tak, aby zobrazovaly prvních n hodnot, které splňují konkrétní kritéria, můžete použít funkci FILTER společně s funkcemi VELKÉ a IF. V zobrazeném příkladu je vzorec v F5:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
kde data (B5: D16), skupina (C5: C16) a skóre (D5: D16) jsou pojmenovány rozsahy.
Vysvětlení
Tento vzorec používá funkci FILTER k načtení dat na základě logického testu vytvořeného pomocí funkcí VELKÉ a IF. Výsledkem jsou 3 nejlepší skóre ve skupině B.
Funkce FILTER použije kritéria s argumentem zahrnout. V tomto příkladu jsou kritéria konstruována s logickou logikou, jako je tato:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Levá strana výrazu cílí na skóre vyšší nebo rovné 3. nejvyššímu skóre ve skupině B:
score>=LARGE(IF(group="b",score),3)
Funkce IF se používá k zajištění, že LARGE pracuje pouze se skóre skupiny B. Protože máme celkem 12 skóre, IF vrátí pole s 12 výsledky, jako je tento:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Všimněte si, že jediné skóre, které operaci přežije, je ze skupiny B. Všechna ostatní skóre jsou NEPRAVDA. Toto pole je vráceno přímo do LARGE jako argument pole:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE ignoruje FALSE hodnoty a vrátí 3. nejvyšší skóre, 83.
Nyní můžeme vzorec zjednodušit na:
=FILTER(data,(score>=83)*(group="b"))
který řeší na:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Nakonec FILTER vrátí záznamy pro Masona, Annie a Cassidy, které se rozlévají do rozsahu F5: H7.
Seřadit výsledky podle skóre
Ve výchozím nastavení FILTER vrátí odpovídající záznamy ve stejném pořadí, v jakém se objevují ve zdrojových datech. Chcete-li výsledky seřadit v sestupném pořadí podle skóre, můžete vnořit původní vzorec FILTER do funkce SORT takto:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Zde FILTER vrací výsledky přímo do funkce SORT jako argument pole. Index řazení je nastaven na 3 (skóre) a pořadí řazení je nastaveno na -1 pro sestupné pořadí.
