Office Guru

Variációk egy témára: dinamikus tartományok és automatikusan bővülő formulák

2017. március 25. - Office Guru

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ó:

sumext1.jpg

sumext2.jpgHogy é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:

sumext3.jpgÉ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:

sumext4.jpgDe 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):

sumext5.jpgInnentő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:

sumext6.jpgViszont 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:

sumext7.jpgA 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á:

sumext8.jpgÉ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.

sumext9.jpg

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr4712369351

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

Norman Nailer 2017.03.26. 11:54:23

Ravasz az ötlet a képletezett Range megadással, de részemről a Table használatát jobban szeretem, többek közt azért, mert sokkal áttekinthetőbbé teszi a képleteket - ha a példádban tényleg létrehoztad volna a táblázatot, a képlet Sum(Table1[Lélekszám]) lenne, ami egy kicsit többet mond a másik képletnél. Ráadásul a Table-es képlethivatkozások akkor is helyesek maradnak, ha közben összevissza pakolod az oszlopokat.
süti beállítások módosítása