Excel képlet: Dinamikus elnevezésű tartomány az OFFSET -

Általános képlet

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Összegzés

A dinamikus nevű tartomány képlettel történő létrehozásának egyik módja az OFFSET függvény és a COUNTA funkció használata. A dinamikus tartományok kiterjesztő tartományok néven is ismertek - automatikusan kibővülnek és összehúzódnak az új vagy törölt adatok befogadására.

Megjegyzés: Az OFFSET volatilis függvény, ami azt jelenti, hogy a munkalap minden változásakor újraszámol. Modern géppel és kisebb adathalmazokkal ez nem okozhat problémát, de a nagyobb adathalmazoknál lassabb teljesítményt tapasztalhat. Ebben az esetben fontolja meg egy dinamikus nevű tartomány felépítését az INDEX függvény helyett.

A bemutatott példában a dinamikus tartományhoz használt képlet a következő:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Magyarázat

Ez a képlet az OFFSET függvény segítségével olyan tartományt állít elő, amely kibővül és összehúzódik a magasság és a szélesség beállításával a nem üres cellák száma alapján.

Az OFFSET első argumentuma az adatok első celláját (eredetét) jelenti, amely ebben az esetben a B5 cella. A következő két argumentum a sorok és oszlopok eltolódása, és nulla értékkel kerül megadásra.

Az utolsó két érv a magasságot és a szélességet jelenti. A magasságot és a szélességet menet közben hozzák létre a COUNTA használatával, ami dinamikussá teszi az eredményül kapott referenciát.

A magassághoz a COUNTA függvényt használjuk a B5: B100 tartomány nem üres értékeinek számolásához. Ez feltételezi, hogy az adatok nem tartalmaznak üres értékeket, és nincsenek a B100-nál nagyobb értékek sem. A COUNTA értéke 6.

A szélességhez a COUNTA függvényt használjuk a B5: Z5 tartomány nem üres értékeinek számolásához. Ez feltételezi, hogy nincsenek fejléccellák, és nincsenek fejlécek a Z5-nél. A COUNTA értéke 6.

Ezen a ponton a képlet így néz ki:

=OFFSET(B5,0,0,6,6)

Ezzel az információval az OFFSET visszaküldi a B5: G10 hivatkozást, amely 6 sor magasság és 6 oszlop közötti tartománynak felel meg.

Megjegyzés: A magassághoz és szélességhez használt tartományokat úgy kell beállítani, hogy megfeleljenek a munkalap elrendezésének.

Változat teljes oszlop / sor hivatkozásokkal

Használhat teljes oszlop- és sorhivatkozásokat is a magassághoz és a szélességhez, így:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Vegye figyelembe, hogy a magasság -2-vel van beállítva, hogy figyelembe vegye a fejléc és a cím értékeit a B4 és B2 cellákban. Ennek a megközelítésnek az előnye a COUNTA belsejében található tartományok egyszerűsége. Hátránya a hatalmas méretű, teljes oszlopokból és sorokból származik - ügyelni kell arra, hogy a tartományon kívül ne tévesszenek meg hibás értékeket, mivel ezek könnyen kidobhatják a számlálást.