Excel-formel: FILTER på første eller sidste n-værdi -

Generisk formel

=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))

Resumé

For at FILTERE og udtrække de første eller sidste n-værdier (dvs. de første 3 værdier, de første 5 værdier osv.) Kan du bruge FILTER-funktionen sammen med INDEX og SEKVENS. I det viste eksempel er formlen i D5:

=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))

hvor data er det navngivne område B5: B15.

Forklaring

Når vi arbejder indefra og ud, bruger vi SEQUENCE-funktionen til at konstruere en rækkeværdi for INDEX som denne:

SEQUENCE(3,1,1,1)

Vi beder SEQUENCE om en matrix på 3 rækker x 1 kolonne, der starter ved 1, med en trinværdi på 1. Resultatet er en matrix som denne:

(1;2;3)

som returneres direkte til INDEX-funktionen som argumentet row_num:

=INDEX(FILTER(data,data""),(1;2;3))

For at konstruere array til INDEX, bruger vi FILTER funktion til at hente en liste over ikke-datoer firmaer det navngivne område data (B5: B15) som denne:

FILTER(data,data"")

Arrayargumentet er data, og inkluderingsargumentet er udtryksdata "". Dette kan oversættes bogstaveligt som "returværdier fra de navngivne områdedata , hvor værdier i data ikke er tomme". Resultatet er en matrix med 9 værdier som denne:

("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")

Bemærkningsværdier, der er knyttet til de to tomme celler, er blevet fjernet. Denne matrix returneres til INDEX-funktionen som dens matrixargument.

Endelig returnerer INDEX de 1., 2. og 3. værdier fra det array, der returneres af FILTER:

("Atlanta";"Chicago";"Dallas")

Sidste n-værdier

For at få de sidste n-værdier med FILTER bruger du den samme formelstruktur, med input til SEQUENCE modificeret til at konstruere et "sidste n" -array med rækkenumre. For eksempel, for at få de sidste 3 ikke-tomme værdier i det viste eksempel kan du bruge en formel som denne:

=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))

Det vigtigste trick her er at tælle de ikke-tomme poster i det navngivne område dataene som denne:

SUM(--(data""))

Vi bruger en dobbeltnegativ til at tvinge de SANDE FALSKE værdier til 1s og 0s, og brug derefter SUM-funktionen til at få optællingen. Resultatet returneres som startargumentet i SEQUENCE. Vi leverer -1 til trin for at gå tilbage fra start.

Vi vikler også SORT-funktionen omkring SEQUENCE, så det returnerede array er (7; 8; 9) og ikke (9; 8; 7). Dette sikrer, at værdier returneres i samme rækkefølge, som de vises i kildedataene.