Általános képlet
=$A$1:INDEX($A:$A,lastrow)
Összegzés
Az egyik dinamikus elnevezett tartomány létrehozása az Excelben az INDEX függvény használata. A bemutatott példában az elnevezett "adatok" tartományt a következő képlet határozza meg:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
amely a $ A $ 2 tartományba oldódik: $ A $ 10.
Megjegyzés: ez a képlet egy megnevezett tartomány meghatározására szolgál, amely más formulákban is használható.
Magyarázat
Ez az oldal az INDEX függvény és a COUNTA funkció mellett létrehozott dinamikus elnevezett tartomány példáját mutatja. A dinamikus nevű tartományok automatikusan bővülnek és összehúzódnak, amikor az adatokat hozzáadják vagy eltávolítják. Alternatívak az Excel tábla használatára, amely átméretezik az adatok hozzáadásakor vagy eltávolításakor is.
Az INDEX függvény visszaadja az értéket egy tartomány vagy tömb adott helyzetében. Az INDEX használatával lekérheti az egyes értékeket vagy egy tartomány teljes sorait és oszlopait. Ami az INDEX-et különösen hasznos a dinamikus nevű tartományok számára, az az, hogy valóban referenciát ad vissza. Ez azt jelenti, hogy az INDEX segítségével létrehozhat egy olyan vegyes referenciát, mint a $ A $ 1: A100.
A bemutatott példában az elnevezett "adatok" tartományt a következő képlet határozza meg:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
amely a $ A $ 2 tartományba oldódik: $ A $ 10.
Hogyan működnek ezek a képletek
Először vegye figyelembe, hogy ez a képlet két részből áll, amelyek a tartomány operátorának mindkét oldalán találhatók (:). A bal oldalon van a kezdő referencia a tartományra, kemény kódolással:
$A$2
A jobb oldalon található a tartomány végződő hivatkozása, amelyet az INDEX segítségével hoztak létre:
INDEX($A:$A,COUNTA($A:$A))
Itt az INDEX-et adjuk meg az A oszlop egészének a tömb számára, majd a COUNTA függvény segítségével kitaláljuk a tartomány "utolsó sorát". A COUNTA itt jól működik, mert az A oszlopban 10 érték található, egy fejlécsorral együtt. A COUNTA ezért 10-et ad vissza, amely közvetlenül az INDEX-be kerül sorszámként. Az INDEX ezután visszaad egy hivatkozást a $ A $ 10-re, a tartomány utolsó használt sorára:
INDEX($A:$A,10) // resolves to $A$10
Tehát a képlet végeredménye ez a tartomány:
$A$2:$A$10
Kétdimenziós tartomány
A fenti példa egydimenziós tartományra vonatkozik. Kétdimenziós dinamikus tartomány létrehozásához, ahol az oszlopok száma is dinamikus, ugyanazt a megközelítést használhatja, így kibővítve:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
A korábbiakhoz hasonlóan a COUNTA-t használják az "utolsó sor" kitalálására, és a COUNTA-t ismét használjuk az "utolsó oszlop" megszerzésére. Ezeket az index sorszámként és oszlopszámként adja meg.
A tömbhöz azonban megadjuk a teljes munkalapot, amelyet mind az 1048576 sorba beírtunk, amely lehetővé teszi az INDEX számára, hogy egy referenciát adjon vissza egy 2D-s helyre.
Megjegyzés: Az Excel 2003 csak 65535 sort támogat.
Az utolsó sor meghatározása
Számos módja van az adatok utolsó sorának (utolsó relatív pozíciójának) meghatározására, a munkalap adatainak felépítésétől és tartalmától függően:
- Az utolsó sor vegyes adatokban, üresen
- Az utolsó sor vegyes adatokban, üresek nélkül
- Az utolsó sor a szöveges adatokban
- A numerikus adatok utolsó sora
