Az egyik kedves Olvasó vetett fel egy kérdést nemrégiben egy, valószínűleg mindennapos Exceles formula problémával kapcsolatban, úgyhogy a következő posztban ezen kérdéskör körül fogok ötletelni.

A példa a következő kis táblán egyszerűen bemutatható:

Hogy érjük el azt, hogy a H4-es cellában lévő összegzés mindig magától frissüljön, ha a C-D oszlopokban található táblázatunkhoz új sorokat adunk hozzá?

A legegyszerűbb és legkönnyebben megadható válasz az Excel automatikus "hivatkozás-bővítés" funkciója, azaz ha bárhova beszúrunk egy üres sort a táblába az első és az utolsó érték közé, akkor a függvény automatikus frissülni fog és beleveszi a frissen beszúrt sorunk értékét is:

Értelemszerűen ez okozhat problémákat, ha rengeteg oszlopunk van vagy ha minden sorunk mellett van egy beképletezett másik cella is, de például ha az összegzésünk egy másik sheeten található és a táblánk is egyszerű, akkor ennél nincs jobb megoldás (mondjuk ez nem is megoldás, hanem maga a program nyújtotta automatizmus). Viszont ha mindig az utolsó sor után adunk hozzá egy újat (mondjuk napokat), akkor ez nem működő opció.

Erre lehetőségünk az, hogy a formulánkban nem a pontos tartományt, hanem akár az egész oszlopot meghivatkozzuk:

De hivatkozhatunk egy elég nagy tartományt is, mondjuk D10:D100000, ha az lefedi jó előre a hozzáadandó celláinkat.

Kezelhetőség szempontjából biztosan nem a legegyszerűbb megoldás, de biztosan működő ötlet a táblafunkció használata, ha extra sor akár hozzáadásáról akár beszúrásáról van szó. A táblázatunk kijelölése után az Insert ribbonfül Tables szekciójának Table opcióját választva szúrjunk be egy táblát (figyelve arra, hogy van-e már fejlécünk vagy nincs):

Innentől kezdve pedig akár a sorok közé, akár a sorok után szúrunk be új adatot, a tábla automatikusan frissülni fog:

Viszont legnagyobb valószínűség szerint a fentiekhez képest jóval kényelmesebb és stabilabb megoldást kínálnak a dinamikus tartományok, amely megoldásokban az a közös, hogy egytől-egyig mindet a Formulas ribbonfül Defined names szekciójában fogjuk megalkotni a Define Name menüpont segítségével:

A következőkben néhány ilyen nevesített tartományhoz rendelt formulát fogunk megnézni, amelyek mindegyike segíteni fog a fent felvetett kérdés megoldásában.

Az elsőben a Range-ként elnevezett tartományunk Refers to sorába a következő formulát írjuk:

=OFFSET(Sheet1!$D$4,0,0,COUNT(Sheet1!$D:$D),1)

A korábbi posztokban mindkét függvényt megismertük már, de alapvetően ez a formula azt teszi, hogy táblánk első számértékétől kezdve (ez a D4-es abszolút hivatkozás) visszaad egy olyan celltartományt, amely ettől a D4-es cellától annyi cellával lefelé nyúlik, ahány számértékünk van a D oszlopban (ez lesz a COUNT függvény eredménye, jelen esetben hét).

Ezután pedig már szimplán elég ha a SUM függvényt erre a Range nevű nevesített tartományra húzzuk rá:

És akkor ennek az OFFSET-es nevesített tartományos hivatkozásnak használhatjuk jópár másik verzióját is, például ezt:

=OFFSET($D$4,0,0,MATCH(1E+306,$D:$D,1),1)

Ez valamelyest hasonló az előbbihez, azzal a változással, hogy a MATCH függvény alkalmazásával tartományunk mindig az utolsó számot tartalmazó celláig terjed majd.

Utána pedig értelemszerűen erre a nevesített tartományra kell az érintett alapfüggvényt elkészítenünk.