Obecný vzorec
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
souhrn
K FILTROVÁNÍ a extrakci prvních nebo posledních n hodnot (tj. Prvních 3 hodnot, prvních 5 hodnot atd.) Můžete použít funkci FILTER společně s INDEX a SEQUENCE. V zobrazeném příkladu je vzorec v D5:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
kde data jsou pojmenovaný rozsah B5: B15.
Vysvětlení
Při práci zevnitř ven používáme funkci SEQUENCE k vytvoření hodnoty čísla řádku pro INDEX takto:
SEQUENCE(3,1,1,1)
Žádáme SEQUENCE o pole 3 řádků x 1 sloupec, počínaje 1, s krokovou hodnotou 1. Výsledkem je pole jako toto:
(1;2;3)
který se vrací přímo do funkce INDEX jako argument row_num:
=INDEX(FILTER(data,data""),(1;2;3))
Chcete-li vytvořit pole pro INDEX, použijeme funkci FILTER k načtení seznamu neprázdných položek z pojmenovaných dat rozsahu (B5: B15) takto:
FILTER(data,data"")
Argument pole jsou data a argument zahrnutí je výraz data. To lze přeložit doslovně jako „návratové hodnoty z pojmenovaných dat rozsahu, kde hodnoty v datech nejsou prázdné“. Výsledkem je pole s 9 hodnotami, jako je tato:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Hodnoty oznámení spojené s dvěma prázdnými buňkami byly odstraněny. Toto pole je vráceno funkci INDEX jako argument pole.
Nakonec INDEX vrátí 1., 2. a 3. hodnoty z pole vráceného FILTREM:
("Atlanta";"Chicago";"Dallas")
Posledních n hodnot
Chcete-li získat posledních n hodnot pomocí FILTERU, použijete stejnou strukturu vzorců se vstupy SEQUENCE upravenými tak, aby vytvořily pole „posledních n“ čísel čísel řádků. Chcete-li například v zobrazeném příkladu získat poslední 3 neprázdné hodnoty, můžete použít vzorec takto:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Hlavním trikem je počítání neprázdných položek v pojmenovaných datech rozsahu, jako je tato:
SUM(--(data""))
Použijeme dvojitý zápor k vynucení hodnot TRUE FALSE na 1 s a 0 s, pak použijeme funkci SUM k získání počtu. Výsledek je vrácen jako počáteční argument uvnitř SEQUENCE. Dodáváme -1 pro krok za krokem od začátku.
Také zabalíme funkci SORT kolem SEQUENCE, takže vrácené pole je (7; 8; 9) a ne (9; 8; 7). Tím je zajištěno, že hodnoty budou vráceny ve stejném pořadí, v jakém se zobrazují ve zdrojových datech.
