Generieke formule
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Samenvatting
Om de eerste of laatste n waarden (dwz eerste 3 waarden, eerste 5 waarden, enz.) Te FILTEREN en extraheren, kunt u de FILTER-functie samen met INDEX en SEQUENCE gebruiken. In het getoonde voorbeeld is de formule in D5:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
waarbij data het benoemde bereik B5: B15 is.
Uitleg
We werken van binnen naar buiten en gebruiken de functie SEQUENCE om als volgt een rijnummerwaarde voor INDEX te construeren:
SEQUENCE(3,1,1,1)
We vragen SEQUENCE om een array van 3 rijen x 1 kolom, beginnend bij 1, met een stapwaarde van 1. Het resultaat is een array als deze:
(1;2;3)
die direct wordt geretourneerd naar de INDEX-functie als het argument row_num:
=INDEX(FILTER(data,data""),(1;2;3))
Om de array voor INDEX bouwen, maken we gebruik van de filterfunctie om een lijst van niet-blank inzendingen uit het benoemde bereik te halen gegevens (B5: B15) als volgt uit:
FILTER(data,data"")
Het array-argument is data, en het include-argument is de expressie data "". Dit kan letterlijk worden vertaald als "return waarden uit het benoemde bereik data waarin waarden data niet leeg zijn". Het resultaat is een array met 9 waarden, zoals deze:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Merk op dat waarden die zijn gekoppeld aan de twee lege cellen zijn verwijderd. Deze array wordt teruggestuurd naar de functie INDEX als zijn array-argument.
Ten slotte retourneert INDEX de 1e, 2e en 3e waarden uit de array die wordt geretourneerd door FILTER:
("Atlanta";"Chicago";"Dallas")
Laatste n waarden
Om de laatste n waarden met FILTER te krijgen, gebruik je dezelfde formulestructuur, met de invoer naar SEQUENCE aangepast om een "laatste n" array van rijnummers te construeren. Om bijvoorbeeld de laatste 3 niet-lege waarden in het getoonde voorbeeld te krijgen, kunt u een formule als volgt gebruiken:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
De belangrijkste truc hier is het tellen van de niet-blank gegevens in het benoemde bereik gegevens als volgt uit:
SUM(--(data""))
We gebruiken een dubbel negatief om de TRUE FALSE-waarden naar 1s en 0s te forceren en gebruiken vervolgens de functie SOM om de telling te krijgen. Het resultaat wordt geretourneerd als het startargument binnen SEQUENCE. We leveren -1 voor stap tot stap achteruit vanaf het begin.
We wikkelen ook de SORT-functie rond SEQUENCE, zodat de geretourneerde array (7; 8; 9) is en niet (9; 8; 7). Dit zorgt ervoor dat waarden worden geretourneerd in dezelfde volgorde waarin ze in de brongegevens voorkomen.
