Formuła ogólna
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Podsumowanie
Aby odfiltrować i wyodrębnić pierwsze lub ostatnie n wartości (tj. Pierwsze 3 wartości, pierwsze 5 wartości itd.), Można użyć funkcji FILTER razem z INDEKSEM i SEKWENCJĄ. W przedstawionym przykładzie formuła w D5 to:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
gdzie dane to nazwany zakres B5: B15.
Wyjaśnienie
Pracując od wewnątrz, używamy funkcji SEKWENCJA do konstruowania wartości numeru wiersza dla INDEKSU w następujący sposób:
SEQUENCE(3,1,1,1)
Pytamy SEKWENCJĘ o tablicę składającą się z 3 wierszy x 1 kolumna, zaczynając od 1, z wartością kroku 1. Wynikiem jest tablica taka:
(1;2;3)
który jest zwracany bezpośrednio do funkcji INDEKS jako argument nr_wiersza:
=INDEX(FILTER(data,data""),(1;2;3))
Aby skonstruować tablicę dla INDEKSU, używamy funkcji FILTER, aby pobrać listę niepustych wpisów z nazwanych danych zakresu (B5: B15) w następujący sposób:
FILTER(data,data"")
Argumentem tablicy są dane, a argumentem włączającym jest wyrażenie „”. Można to przetłumaczyć dosłownie jako „zwracane wartości z danych zakresu nazwanego, gdzie wartości w danych nie są puste”. Wynikiem jest tablica zawierająca 9 wartości, takich jak ta:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Zwróć uwagę, że wartości skojarzone z dwiema pustymi komórkami zostały usunięte. Ta tablica jest zwracana do funkcji INDEKS jako argument tablicy.
Wreszcie funkcja INDEKS zwraca pierwszą, drugą i trzecią wartość z tablicy zwróconej przez FILTER:
("Atlanta";"Chicago";"Dallas")
Ostatnie n wartości
Aby uzyskać ostatnie n wartości za pomocą funkcji FILTER, należy użyć tej samej struktury formuły, a dane wejściowe do SEKWENCJI zostały zmodyfikowane w celu skonstruowania „ostatniej n” tablicy numerów wierszy. Na przykład, aby uzyskać ostatnie 3 niepuste wartości w pokazanym przykładzie, możesz użyć następującego wzoru:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Główna sztuczka polega na zliczaniu niepustych wpisów w nazwanych danych zakresu w następujący sposób:
SUM(--(data""))
Używamy podwójnej ujemnej wartości, aby wymusić wartości TRUE FALSE na 1 i 0, a następnie użyć funkcji SUMA, aby uzyskać liczbę. Wynik jest zwracany jako argument początkowy wewnątrz SEQUENCE. Dostarczamy -1 za krok, aby cofnąć się od początku.
Funkcję SORT owijamy również wokół SEQUENCE, więc zwracana tablica to (7; 8; 9), a nie (9; 8; 7). Gwarantuje to, że wartości są zwracane w tej samej kolejności, w jakiej pojawiają się w danych źródłowych.
