Generička formula
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Sažetak
Za FILTRIRANJE i izdvajanje prve ili posljednje n vrijednosti (tj. Prve 3 vrijednosti, prvih 5 vrijednosti itd.), Možete koristiti funkciju FILTER zajedno s INDEX i SEQUENCE. U prikazanom primjeru formula u D5 je:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
gdje su podaci imenovani raspon B5: B15.
Obrazloženje
Radeći iznutra prema van, koristimo funkciju SEQUENCE kako bismo konstruirali vrijednost broja retka za INDEX ovako:
SEQUENCE(3,1,1,1)
Tražimo SEQUENCE za niz od 3 retka x 1 stupac, počevši od 1, s vrijednošću koraka 1. Rezultat je niz poput ovog:
(1;2;3)
koji se vraća izravno funkciji INDEX kao argument row_num:
=INDEX(FILTER(data,data""),(1;2;3))
Da bismo konstruirali niz za INDEX, koristimo funkciju FILTER za dohvaćanje popisa nepraznih unosa iz imenovanih podataka raspona (B5: B15) ovako:
FILTER(data,data"")
Argument niza su podaci, a argument include izraz podataka "". To se može doslovno prevesti kao "vraćanje vrijednosti iz imenovanih podataka raspona gdje vrijednosti u podacima nisu prazne". Rezultat je niz s 9 vrijednosti poput ove:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Vrijednosti obavijesti povezane s dvije prazne ćelije uklonjene su. Taj se niz vraća funkciji INDEX kao njegov argument niza.
Napokon, INDEX vraća 1., 2. i 3. vrijednost iz polja koje je vratio FILTER:
("Atlanta";"Chicago";"Dallas")
Posljednjih n vrijednosti
Da biste dobili posljednjih n vrijednosti s FILTER, koristite istu strukturu formule, s ulazima u SEQUENCE modificiranim za izgradnju "posljednjih n" niza brojeva redaka. Na primjer, da biste dobili posljednje 3 prazne vrijednosti u prikazanom primjeru, možete upotrijebiti formulu poput ove:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Glavni trik ovdje je brojanje nepraznih unosa u imenovanim podacima raspona poput ovog:
SUM(--(data""))
Dvostrukim negativom forsiramo vrijednosti TRUE FALSE na 1s i 0s, a zatim pomoću funkcije SUM dobivamo brojanje. Rezultat se vraća kao početni argument unutar SEQUENCE. Dobavljamo -1 za korak za korak unatrag od početka.
Funkciju SORT također omotamo oko SEQUENCE tako da je vraćeni niz (7; 8; 9), a ne (9; 8; 7). To osigurava da se vrijednosti vraćaju istim redoslijedom u kojem se pojavljuju u izvornim podacima.
