Splošna formula
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Povzetek
Če želite FILTRIRATI in izvleči prvo ali zadnjo vrednost n (tj. Prve 3 vrednosti, prvih 5 vrednosti itd.), Lahko uporabite funkcijo FILTER skupaj z INDEX in SEQUENCE. V prikazanem primeru je formula v D5:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
kjer so podatki imenovani obseg B5: B15.
Pojasnilo
Če delamo od znotraj navzven, s funkcijo SEQUENCE sestavimo vrednost številke vrstice za INDEX, kot je ta:
SEQUENCE(3,1,1,1)
Prosimo SEQUENCE za matriko 3 vrstic x 1 stolpec, začenši z 1, z vrednostjo koraka 1. Rezultat je matrika, kot je ta:
(1;2;3)
ki se vrne neposredno v funkcijo INDEX kot argument row_num:
=INDEX(FILTER(data,data""),(1;2;3))
Za izdelavo matrike za INDEX s funkcijo FILTER pridobimo seznam nepraznih vnosov iz poimenovanih podatkov obsega (B5: B15), kot je ta:
FILTER(data,data"")
Argument polja so podatki, argument vključitve pa izraz "". To lahko prevedemo dobesedno kot "vrnitev vrednosti iz poimenovanih podatkov obsega, kjer vrednosti v podatkih niso prazne". Rezultat je matrika z 9 vrednostmi, kot je ta:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Vrednosti obvestil, povezane z dvema praznima celicama, so bile odstranjene. Ta matrika se vrne v funkcijo INDEX kot njen argument array.
Nazadnje INDEX vrne 1., 2. in 3. vrednost iz polja, ki ga vrne FILTER:
("Atlanta";"Chicago";"Dallas")
Zadnjih n vrednosti
Če želite dobiti zadnjih n vrednosti s FILTER, uporabite isto strukturo formule, pri čemer so bili vhodi v SEQUENCE spremenjeni, da sestavijo "zadnji n" niz številk vrstic. Če želite na primer dobiti zadnje 3 neprazne vrednosti v prikazanem primeru, lahko uporabite formulo, kot je ta:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Glavni trik tukaj je štetje praznih vnosov v poimenovanih podatkih obsega, kot je ta:
SUM(--(data""))
Z dvojnim negativom prisilimo vrednosti TRUE FALSE na 1s in 0s, nato pa s funkcijo SUM dobimo štetje. Rezultat se vrne kot začetni argument znotraj SEQUENCE. Od začetka dovajamo -1 za korak za korak nazaj.
Funkcijo SORT zavijemo tudi okoli SEQUENCE, tako da je vrnjena matrika (7; 8; 9) in ne (9; 8; 7). To zagotavlja, da se vrednosti vrnejo v enakem vrstnem redu, kot so prikazane v izvornih podatkih.
