Generisk formel
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Sammanfattning
För att FILTERA och extrahera de första eller sista n-värdena (dvs. de första 3 värdena, de första 5 värdena osv.) Kan du använda FILTER-funktionen tillsammans med INDEX och SEKVENS. I exemplet som visas är formeln i D5:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
där data är det namngivna området B5: B15.
Förklaring
Arbetar inifrån och ut, använder vi SEQUENCE-funktionen för att konstruera ett radnummervärde för INDEX så här:
SEQUENCE(3,1,1,1)
Vi ber SEQUENCE för en matris med 3 rader x 1 kolumn, med början 1, med ett stegvärde på 1. Resultatet är en matris så här:
(1;2;3)
som returneras direkt till INDEX-funktionen som radnummer-argument:
=INDEX(FILTER(data,data""),(1;2;3))
För att konstruera arrayen för INDEX använder vi filterfunktionen för att hämta en lista över icke-tomma poster från det namngivna området uppgifter (B5: B15) så här:
FILTER(data,data"")
Arrayargumentet är data, och inkluderingsargumentet är uttrycksdata "". Detta kan översättas bokstavligen som "returvärden från det namngivna området uppgifter där värden i uppgifterna inte är tom". Resultatet är en matris med 9 värden så här:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Observeringsvärden associerade med de två tomma cellerna har tagits bort. Denna array returneras till INDEX-funktionen som dess arrayargument.
Slutligen returnerar INDEX de första, andra och tredje värdena från matrisen som returneras av FILTER:
("Atlanta";"Chicago";"Dallas")
Senaste n värden
För att få de sista n-värdena med FILTER använder du samma formelstruktur, med ingångarna till SEQUENCE modifierade för att konstruera en "last n" -matris med radnummer. För att till exempel få de tre sista värdena som inte är tomma i exemplet som visas kan du använda en sådan formel:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Huvud Tricket räknar icke-tomma poster i det namngivna området uppgifter så här:
SUM(--(data""))
Vi använder en dubbelnegativ för att tvinga de Sanna FALSKA värdena till 1s och 0s, använd sedan SUM-funktionen för att få räkningen. Resultatet returneras som startargumentet i SEQUENCE. Vi levererar -1 för steg till steg bakåt från start.
Vi slingrar också SORT-funktionen runt SEQUENCE så att den returnerade matrisen är (7; 8; 9) och inte (9; 8; 7). Detta säkerställer att värden returneras i samma ordning som de visas i källdata.
