Bendroji formulė
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Santrauka
Norėdami filtruoti ir ištraukti pirmąsias arba paskutines n reikšmes (ty pirmąsias 3 reikšmes, pirmąsias 5 reikšmes ir kt.), Galite naudoti funkciją FILTER kartu su INDEX ir SEQUENCE. Parodytame pavyzdyje formulė D5 yra:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
kur duomenys yra įvardytas diapazonas B5: B15.
Paaiškinimas
Dirbdami iš vidaus, mes naudojame funkciją SEQUEN, kad sukurtume INDEX eilutės numerio vertę taip:
SEQUENCE(3,1,1,1)
Mes prašome SEQUENCE 3 eilių x 1 stulpelio masyvo, prasidedančio 1, žingsnio reikšme 1. Rezultatas yra toks masyvas:
(1;2;3)
kuris grąžinamas tiesiogiai į INDEX funkciją kaip argumentas row_num:
=INDEX(FILTER(data,data""),(1;2;3))
Norėdami sukurti „INDEX“ masyvą, naudodamiesi funkcija „FILTRAS“ iš pavadintų diapazono duomenų (B5: B15) gauname ne tuščių įrašų sąrašą :
FILTER(data,data"")
Masyvo argumentas yra duomenys, o argumentas „include“ yra išraiškos duomenys „“. Tai gali būti verčiami pažodžiui kaip "grįžimas vertybių iš pavadintą diapazoną duomenimis , kur vertybės duomenys nėra tuščias". Rezultatas yra masyvas, kuriame yra 9 tokios vertės:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Su dviem tuščiais langeliais susijusios pranešimo vertės buvo pašalintos. Šis masyvas grąžinamas į INDEX funkciją kaip jo masyvo argumentą.
Galiausiai INDEX grąžina 1, 2 ir 3 reikšmes iš masyvo, kurį grąžino FILTER:
("Atlanta";"Chicago";"Dallas")
Paskutinės n vertės
Norėdami gauti paskutines n reikšmes naudodami filtrą FILTER, naudokite tą pačią formulės struktūrą, pakeisdami įvestis į SEQUENCE, kad sukurtumėte „paskutinį n“ eilučių skaičių masyvą. Pvz., Norėdami gauti paskutines 3 neužpildytas reikšmes pateiktame pavyzdyje, galite naudoti tokią formulę:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Pagrindinis triukas yra ne tuščių įrašų skaičiavimas pavadintuose diapazono duomenyse :
SUM(--(data""))
Mes naudojame dvigubą neigiamą vertę, kad TRUE FALSE reikšmės būtų 1s ir 0s, tada naudokite SUM funkciją, kad gautume skaičių. Rezultatas grąžinamas kaip pradinis argumentas SEQUENCE viduje. Mes tiekiame -1, kad žingsnis būtų žingsnis atgal nuo pradžių.
Mes taip pat apvyniojame SORT funkciją aplink SEQUENCE, kad masyvas būtų (7; 8; 9), o ne (9; 8; 7). Tai užtikrina, kad reikšmės bus grąžintos ta pačia tvarka, kaip ir pateikiamos šaltinio duomenyse.
