„Excel“ formulė: dinaminis pavadintas diapazonas su INDEX -

Turinys

Bendroji formulė

=$A$1:INDEX($A:$A,lastrow)

Santrauka

Vienas iš būdų sukurti dinaminį pavadintą diapazoną „Excel“ yra naudoti INDEX funkciją. Parodytame pavyzdyje pavadintas diapazonas „duomenys“ apibrėžiamas pagal šią formulę:

=$A$2:INDEX($A:$A,COUNTA($A:$A))

kuris išsiskiria į diapazoną $ A $ 2: $ A $ 10.

Pastaba: ši formulė skirta apibrėžti pavadintą diapazoną, kurį galima naudoti kitose formulėse.

Paaiškinimas

Šiame puslapyje pateikiamas dinaminio pavadinto diapazono, sukurto naudojant funkciją INDEX, kartu su funkcija COUNTA, pavyzdys. Dinaminiai pavadinti diapazonai automatiškai plečiasi ir susitraukia, kai duomenys pridedami arba pašalinami. Jie yra „Excel“ lentelės naudojimo alternatyva, kuri taip pat keičiasi, kai duomenys pridedami arba pašalinami.

INDEX funkcija grąžina vertę tam tikroje diapazono ar masyvo pozicijoje. Norėdami gauti atskiras reikšmes arba visas eilutes ir stulpelius diapazone, galite naudoti „INDEX“. INDEX ypač naudinga dinaminiams pavadintiems diapazonams, nes jis iš tikrųjų grąžina nuorodą. Tai reiškia, kad naudodami INDEX galite sukurti mišrią nuorodą, pvz., $ A $ 1: A100.

Parodytame pavyzdyje pavadintas diapazonas „duomenys“ apibrėžiamas pagal šią formulę:

=$A$2:INDEX($A:$A,COUNTA($A:$A))

kuris išsiskiria į diapazoną $ A $ 2: $ A $ 10.

Kaip veikia šios formulės

Pirmiausia atkreipkite dėmesį, kad šią formulę sudaro dvi dalys, esančios abiejose diapazono operatoriaus pusėse (:). Kairėje pusėje yra pradinė diapazono nuoroda, koduota kaip:

$A$2

Dešinėje yra galinė diapazono nuoroda, sukurta tokiu INDEX:

INDEX($A:$A,COUNTA($A:$A))

Čia pateikiame INDEX visą masyvo A stulpelį, tada naudokite funkciją COUNTA, kad išsiaiškintumėte „paskutinę eilutę“ diapazone. COUNTA čia gerai veikia, nes A stulpelyje yra 10 verčių, įskaitant antraštės eilutę. Todėl COUNTA pateikia 10, kuris eina tiesiai į INDEX kaip eilutės numerį. Tada INDEX grąžina nuorodą į $ A $ 10, paskutinę naudojamą eilutę diapazone:

INDEX($A:$A,10) // resolves to $A$10

Taigi, galutinis formulės rezultatas yra šis diapazonas:

$A$2:$A$10

Dviejų matmenų diapazonas

Aukščiau pateiktas pavyzdys tinka vieno matmens diapazonui. Norėdami sukurti dvimatį dinaminį diapazoną, kuriame stulpelių skaičius taip pat yra dinamiškas, galite naudoti tą patį metodą, išplėstą taip:

=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))

Kaip ir anksčiau, COUNTA naudojama norint išsiaiškinti „paskutinįjį langelį“, o mes vėl naudojame „COUNTA“, kad gautume „paskutinį stulpelį“. Jie pateikiami indeksui kaip atitinkamai eilutės_numeris ir stulpelio_suma.

Tačiau masyvui mes pateikiame visą darbalapį, įvestą kaip visas 1048576 eilutes, o tai leidžia INDEX grąžinti nuorodą 2D erdvėje.

Pastaba: „Excel 2003“ palaiko tik 65535 eilutes.

Paskutinės eilės nustatymas

Yra keli būdai, kaip nustatyti paskutinę duomenų rinkinio eilutę (paskutinę santykinę padėtį), atsižvelgiant į darbalapio duomenų struktūrą ir turinį: