Vispārēja formula
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Kopsavilkums
Lai filtrētu un iegūtu pirmās vai pēdējās n vērtības (ti, pirmās 3 vērtības, pirmās 5 vērtības utt.), Jūs varat izmantot funkciju FILTER kopā ar INDEX un SEQUENCE. Parādītajā piemērā formula D5 ir:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
kur dati ir nosauktais diapazons B5: B15.
Paskaidrojums
Strādājot no iekšpuses, mēs izmantojam funkciju SEQUENCE, lai izveidotu INDEX rindas numura vērtību šādi:
SEQUENCE(3,1,1,1)
Mēs pieprasām SEQUENCE masīvu no 3 rindām x 1 kolonnas, sākot no 1, ar pakāpes vērtību 1. Rezultāts ir šāds masīvs:
(1;2;3)
kas tiek atgriezts tieši INDEX funkcijā kā row_num arguments:
=INDEX(FILTER(data,data""),(1;2;3))
Lai izveidotu masīvu indeksam INDEX, mēs izmantojam funkciju FILTER, lai no nosauktajiem diapazona datiem (B5: B15) izgūtu neizpildītu ierakstu sarakstu šādi:
FILTER(data,data"")
Masīva arguments ir dati, un arguments arguments ir izteiksme dati "". To var tulkot burtiski kā "atgriešanās vērtībām no nosauktajām diapazona datiem , kur vērtības datiem nav tukšs". Rezultāts ir masīvs ar 9 šādām vērtībām:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Paziņojuma vērtības, kas saistītas ar divām tukšajām šūnām, ir noņemtas. Šis masīvs tiek atgriezts funkcijā INDEX kā tā masīva arguments.
Visbeidzot, INDEX atgriež 1., 2. un 3. vērtību no masīva, kuru atdeva FILTER:
("Atlanta";"Chicago";"Dallas")
Pēdējās n vērtības
Lai iegūtu pēdējās n vērtības ar FILTER, izmantojiet to pašu formulas struktūru, un SEQUENCE ievadi ir modificēti, lai izveidotu rindu numuru masīvu "last n". Piemēram, lai parādītajā piemērā iegūtu pēdējās 3 vērtības, kas nav tukšas, varat izmantot šādu formulu:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Galvenais triks šeit ir šādi tukšo ierakstu skaitīšana nosauktajos diapazona datos :
SUM(--(data""))
Mēs izmantojam dubultnegatīvu, lai TRUE FALSE vērtības tiktu piespiestas līdz 1s un 0s, pēc tam izmantojiet funkciju SUM, lai iegūtu skaitīšanu. Rezultāts tiek atgriezts kā sākuma arguments SEQUENCE. Mēs piegādājam -1, lai pakāpeniski spertu atpakaļ no sākuma.
Mēs arī aptveram SORT funkciju ap SEQUENCE, lai atgrieztais masīvs būtu (7; 8; 9), nevis (9; 8; 7). Tas nodrošina, ka vērtības tiek atgrieztas tādā pašā secībā, kādā tās tiek rādītas avota datos.
