Formuła ogólna
=FILTER(data,range>=LARGE(range,n))
Podsumowanie
Aby przefiltrować zestaw danych w celu wyświetlenia n najwyższych wartości, można użyć funkcji FILTER razem z funkcją LARGE. W pokazanym przykładzie formuła w F5 to:
=FILTER(data,score>=LARGE(score,3))
gdzie dane (B5: D16) i wynik (D5: D16) to nazwane zakresy.
Wyjaśnienie
Ta formuła używa funkcji FILTER do pobierania danych na podstawie testu logicznego utworzonego za pomocą funkcji LARGE.
Funkcja LARGE to prosty sposób na uzyskanie n-tej największej wartości w zakresie. Po prostu podaj zakres dla pierwszego argumentu (tablica) i wartość n jako drugi argument (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
W tym przykładzie logika FILTER jest skonstruowana za pomocą tego fragmentu kodu:
score>=LARGE(score,3)
która zwraca PRAWDA, gdy wynik jest większy lub równy 3. najwyższemu wynikowi. Ponieważ zakres D5: D16 zawiera 12 komórek, to wyrażenie zwraca tablicę 12 wartości PRAWDA i FAŁSZ w tablicy takiej jak ta:
(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Ta tablica jest zwracana bezpośrednio do funkcji FILTER jako argument include, gdzie jest używana do filtrowania danych. Tylko wiersze, w których wynik ma wartość TRUE, trafiają do końcowego wyniku. Wynik ze wzoru w F5 obejmuje 3 najwyższe wyniki w danych, które mieszczą się w zakresie 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(score,3)),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.
