Formuła ogólna
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Podsumowanie
Aby przefiltrować dane w celu wyświetlenia n pierwszych wartości, które spełniają określone kryteria, można użyć funkcji FILTER razem z funkcjami LARGE i IF. W pokazanym przykładzie formuła w F5 to:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
gdzie dane (B5: D16), grupa (C5: C16) i wynik (D5: D16) to nazwane zakresy.
Wyjaśnienie
Ta formuła używa funkcji FILTER do pobierania danych na podstawie testu logicznego utworzonego przy użyciu funkcji LARGE i IF. Rezultatem są 3 najlepsze wyniki w grupie B.
Funkcja FILTER stosuje kryteria z argumentem include. W tym przykładzie kryteria są konstruowane za pomocą logiki boolowskiej w następujący sposób:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Wyniki po lewej stronie wyrażenia są wyższe lub równe trzeciemu najwyższemu wynikowi w grupie B:
score>=LARGE(IF(group="b",score),3)
Funkcja JEŻELI służy do upewnienia się, że LARGE działa tylko z wynikami grupy B. Ponieważ mamy łącznie 12 wyników, JEŻELI zwraca tablicę z 12 wynikami w następujący sposób:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Zauważ, że jedyne wyniki, które przetrwały operację, pochodzą z grupy B. Wszystkie inne wyniki są FAŁSZ. Ta tablica jest zwracana bezpośrednio do LARGE jako argument tablicy:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE ignoruje wartości FALSE i zwraca trzeci najwyższy wynik, 83.
Możemy teraz uprościć formułę, aby:
=FILTER(data,(score>=83)*(group="b"))
co prowadzi do:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Na koniec FILTER zwraca rekordy dla Masona, Annie i Cassidy'ego, które dochodzą do zakresu F5: H7.
Sortuj wyniki według wyniku
Domyślnie FILTER zwraca pasujące rekordy w tej samej kolejności, w jakiej występują w danych źródłowych. Aby posortować wyniki w porządku malejącym według wyniku, możesz zagnieździć oryginalną formułę FILTER wewnątrz funkcji SORT w następujący sposób:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
W tym przypadku FILTER zwraca wyniki bezpośrednio do funkcji SORT jako argument tablicy. Indeks sortowania jest ustawiony na 3 (wynik), a porządek sortowania jest ustawiony na -1, dla porządku malejącego.
