Yleinen kaava
=$A$1:INDEX($A:$A,lastrow)
Yhteenveto
Yksi tapa luoda dynaaminen nimetty alue Excelissä on käyttää INDEX-toimintoa. Esitetyssä esimerkissä nimetty alue "data" määritetään seuraavalla kaavalla:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
joka hajoaa alueelle $ A $ 2: $ A $ 10.
Huomaa: Tämä kaava on tarkoitettu määrittelemään nimetty alue, jota voidaan käyttää muissa kaavoissa.
Selitys
Tällä sivulla on esimerkki dynaamisesta nimellisestä alueesta, joka on luotu INDEX-toiminnolla yhdessä COUNTA-toiminnon kanssa. Dynaamiset nimetyt alueet laajenevat ja supistuvat automaattisesti, kun tietoja lisätään tai poistetaan. Ne ovat vaihtoehto Excel-taulukon käytölle, joka muuttaa myös kokoa, kun tietoja lisätään tai poistetaan.
INDEX-funktio palauttaa arvon tietylle alueelle tai taulukolle. Indeksin avulla voit hakea yksittäisiä arvoja tai kokonaisia rivejä ja sarakkeita alueelta. Mikä tekee INDEXistä erityisen hyödyllisen dynaamisille nimetyille alueille, on se, että se palauttaa viitteen. Tämä tarkoittaa, että voit luoda INDEX: n avulla sekaviitteen, kuten $ A $ 1: A100.
Esitetyssä esimerkissä nimetty alue "data" määritetään seuraavalla kaavalla:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
joka hajoaa alueelle $ A $ 2: $ A $ 10.
Kuinka tämä kaava toimii
Huomaa ensin, että tämä kaava koostuu kahdesta osasta, jotka ovat alueen operaattorin kummallakin puolella (:). Vasemmalla puolella on alueen lähtöviite, joka on koodattu seuraavasti:
$A$2
Oikealla on alueen viittaus, joka on luotu INDEX: llä seuraavasti:
INDEX($A:$A,COUNTA($A:$A))
Tässä syötetään INDEX koko sarakkeeseen A taulukolle, ja sitten COUNTA-funktiolla selvitetään alueen "viimeinen rivi". COUNTA toimii tässä hyvin, koska sarakkeessa A on 10 arvoa, mukaan lukien otsikkorivi. Siksi COUNTA palauttaa arvon 10, joka menee suoraan INDEX: iin rivinumerona. INDEX palauttaa sitten viitteen dollariin $ 10, joka on viimeksi käytetty rivi alueella:
INDEX($A:$A,10) // resolves to $A$10
Joten kaavan lopullinen tulos on tämä alue:
$A$2:$A$10
Kaksiulotteinen alue
Yllä oleva esimerkki toimii yksiulotteisella alueella. Voit luoda kaksiulotteisen dynaamisen alueen, jossa myös sarakkeiden määrä on dynaaminen, käyttämällä samaa lähestymistapaa laajennettuna seuraavasti:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Kuten aiemmin, COUNTAa käytetään "viimeisen rivin" selvittämiseen, ja käytämme COUNTAa uudelleen saadaksesi "viimeisen sarakkeen". Nämä toimitetaan hakemistoon rivinumerolla ja sarakkeenumerolla.
Matriisille toimitamme kuitenkin koko laskentataulukon, joka on syötetty kaikkiin 1048576 riveihin, jolloin INDEX voi palauttaa viitteen 2D-tilaan.
Huomaa: Excel 2003 tukee vain 65535 riviä.
Viimeisen rivin määrittäminen
On useita tapoja määrittää tietojoukon viimeinen rivi (viimeinen suhteellinen sijainti) laskentataulukon tietojen rakenteen ja sisällön mukaan:
- Viimeinen rivi sekatiedoissa tyhjillä
- Viimeinen rivi sekatiedoissa ilman tyhjiä kohtia
- Viimeinen rivi tekstitiedoissa
- Viimeinen rivi numeerisissa tiedoissa
