Üldine valem
=$A$1:INDEX($A:$A,lastrow)
Kokkuvõte
Üks võimalus Excelis dünaamilise nimega vahemiku loomiseks on funktsiooni INDEX kasutamine. Näidatud näites on nimega vahemik "andmed" määratletud järgmise valemiga:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
mis laheneb vahemikku $ A $ 2: $ A $ 10.
Märkus: see valem on mõeldud nimetatava vahemiku määratlemiseks, mida saab kasutada teistes valemites.
Selgitus
Sellel lehel on näide dünaamilise nimega vahemikust, mis on loodud funktsiooniga INDEX koos funktsiooniga COUNTA. Dünaamilised nimega vahemikud laienevad ja tõmbuvad automaatselt kokku, kui andmeid lisatakse või eemaldatakse. Need on alternatiiv Exceli tabeli kasutamisele, mille suurust muudetakse ka andmete lisamisel või eemaldamisel.
Funktsioon INDEX tagastab väärtuse vahemikus või massiivi antud asukohas. Indeksit saate kasutada üksikute väärtuste või tervete ridade ja veergude toomiseks vahemikus. INDEXi teeb dünaamiliste nimega vahemike jaoks eriti kasulikuks see, et see tegelikult tagastab viite. See tähendab, et saate INDEXi abil luua sellise segaviite nagu $ A $ 1: A100.
Näidatud näites on nimega vahemik "andmed" määratletud järgmise valemiga:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
mis laheneb vahemikku $ A $ 2: $ A $ 10.
Kuidas need valemid toimivad
Kõigepealt pange tähele, et see valem koosneb kahest osast, mis asuvad vahemikuoperaatori mõlemal küljel (:). Vasakul on vahemiku algviide, mis on kodeeritud järgmiselt:
$A$2
Paremal on vahemiku lõppviide, mis on loodud INDEX-iga järgmiselt:
INDEX($A:$A,COUNTA($A:$A))
Siin söödame massiivi jaoks kogu veeru A INDEX-i, seejärel kasutame funktsiooni COUNTA, et välja selgitada vahemiku viimane rida. COUNTA töötab siin hästi, kuna veerus A on 10 väärtust, sealhulgas päiserida. Seetõttu tagastab COUNTA 10, mis läheb reana otse INDEX-i. Seejärel tagastab INDEX viite vahemikule viimati kasutatud reale $ A $ 10:
INDEX($A:$A,10) // resolves to $A$10
Seega on valemi lõpptulemus see vahemik:
$A$2:$A$10
Kahemõõtmeline vahemik
Ülaltoodud näide töötab ühemõõtmelise vahemiku puhul. Kahemõõtmelise dünaamilise vahemiku loomiseks, kus ka veergude arv on dünaamiline, võite kasutada sama lähenemisviisi, laiendatud järgmiselt:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Nagu varemgi, kasutatakse "lastrow" välja selgitamiseks COUNTA-d ja viimase veeru saamiseks kasutame uuesti COUNTA-d. Need esitatakse indeksile vastavalt rea_number ja veeru_arv.
Kuid massiivi jaoks pakume täielikku töölehte, mis on sisestatud kõigi 1048576 rida, mis võimaldab INDEXil tagastada viide 2D-ruumis.
Märkus. Excel 2003 toetab ainult 65535 rida.
Viimase rea määramine
Andmekogumi viimase rea (viimase suhtelise positsiooni) määramiseks on mitu võimalust, sõltuvalt töölehe andmete struktuurist ja sisust:
- Viimane rida toorikutega segatud andmetega
- Viimane rida segatud andmetes, kus pole toorikuid
- Viimane tekstiandmete rida
- Viimane arvandmete rida
