Všeobecný vzorec
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Zhrnutie
Ak chcete FILTROVAŤ a extrahovať prvých alebo posledných n hodnôt (tj. Prvé 3 hodnoty, prvých 5 hodnôt atď.), Môžete použiť funkciu FILTER spolu s INDEXOM a SEKVENCIOU. V zobrazenom príklade je vzorec v D5:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
kde údajmi je pomenovaný rozsah B5: B15.
Vysvetlenie
Pri práci zvnútra von používame funkciu SEQUENCE na zostrojenie hodnoty čísla riadku pre INDEX takto:
SEQUENCE(3,1,1,1)
Žiadame SEQUENCE o pole 3 riadkov x 1 stĺpec, začínajúci na 1, s krokovou hodnotou 1. Výsledkom je pole ako toto:
(1;2;3)
ktorý sa vráti priamo do funkcie INDEX ako argument row_num:
=INDEX(FILTER(data,data""),(1;2;3))
Na zostrojenie poľa pre INDEX používame funkciu FILTER na získanie zoznamu neprázdnych záznamov z pomenovaných údajov rozsahu (B5: B15) takto:
FILTER(data,data"")
Argumentom poľa sú údaje a argumentom zahrnutia je výraz údaje „“. To možno preložiť doslovne ako „návratové hodnoty z pomenovaných údajov rozsahu, kde hodnoty v dátach nie sú prázdne“. Výsledkom je pole s 9 hodnotami, ako je táto:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Hodnoty upozornení spojené s dvoma prázdnymi bunkami boli odstránené. Toto pole je vrátené funkcii INDEX ako argument jeho poľa.
Nakoniec INDEX vráti 1., 2. a 3. hodnoty z poľa vráteného funkciou FILTER:
("Atlanta";"Chicago";"Dallas")
Posledných n hodnôt
Ak chcete získať posledných n hodnôt pomocou FILTERU, použijete rovnakú štruktúru vzorcov so vstupmi do SEQUENCE upravenými tak, aby sa vytvorilo pole „posledných n“ čísel čísel riadkov. Ak chcete napríklad v zobrazenom príklade získať posledné 3 neprázdne hodnoty, môžete použiť nasledujúci vzorec:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Hlavným trikom je spočítanie neprázdnych položiek v pomenovaných údajoch rozsahu, ako je táto:
SUM(--(data""))
Použijeme dvojitý zápor na vynútenie hodnôt TRUE FALSE na 1 s a 0 s, potom pomocou funkcie SUM dostaneme počet. Výsledok sa vráti ako počiatočný argument vo vnútri SEQUENCE. Dodávame -1 pre krok za krokom späť od začiatku.
Rovnako zabalíme funkciu SORT okolo SEQUENCE, takže vrátené pole je (7; 8; 9) a nie (9; 8; 7). To zaisťuje, že hodnoty sa vrátia v rovnakom poradí, v akom sa vyskytujú v zdrojových dátach.
