Excel-tippek VI: Automatikusan bővülő grafikonok

Kezdetektől fogva a grafikonkészítő - mai divatos szóval az adatvizualizációs - eszköz az Excel egyik, ha nem a legnépszerűbb és legösszetettebb funkciója, amely jelentős változáson, többnyire fejlődésen ment keresztül az elmúlt három verzióban. Ám még mindig vannak olyan hiányosságok, problémák, amelyek sokszor komoly fejtörést tudnak okozni a felhasználóknak. Egy ilyen tipikus probléma, hogy hogyan készíthetünk egyszerűen olyan grafikonokat, amelyek automatikusan bővülnek az újonnan érkező adatokkal.

Nevesített adatforrások

Régebben a felhasználók többsége a grafikonokat a Diagramvarázslóval hozta létre, ám az Excel 2007-ben ez a lehetőség megszűnt, így manapság ezek a [Beszúrás] szalag [Ajánlott diagramok] vagy valamelyik konkrét diagramtípus gombjának megnyomásával készülnek. E művelet során vagy a felhasználó jelöli ki az ábrázolandó adatokat, vagy az éppen aktuális cella, esetleg kijelölt cellák alapján az Excel próbálja meg ezt kitalálni.

Ez a kijelölés mindig az aktuális állapotra vonatkozik, így ha adataink száma változik, jellemzően újabb sorral, sorokkal bővül, akkor ezt valamilyen módon a grafikon tudomására kell hoznunk; amit elvileg nagyon egyszerűen meg tudunk tenni, hiszen az ábra kiválasztása után az Excel automatikusan kijelöli az adattartomány(oka)t, és amennyiben van, úgy a kategóriatengely feliratainak tartományát is, amiket azután a szokásos módon, azaz a kijelölések sarkaiban található "fogantyúk" mozgatásával módosíthatunk. De ugyanez már egy kicsit macerásabb, ha az adatok tartománya olyan hosszú, hogy a vége már nem is látszik a diagram kijelölésekor, nem is beszélve arról, amikor a grafikon és az adatok különböző munkalapokon találhatók. Nehezítő körülmény, hogy ezeket a módosításokat sokszor az utolsó utáni pillanat kapkodásában kell(enne) végrehajtanunk, éppen ezért ismerkedjünk meg egy újabb függvénnyel, az ELTOLÁS-sal (leánykori nevén az OFSZET-tel).

Az ELTOLÁS, amit a keresési és hivatkozási függvények között találunk, egy adott hivatkozástól megadott sornyi és oszlopnyi távolságra lévő megadható magasságú és szélességű hivatkozást ad eredményül.

Először is meg kell jelölnünk egy referenciatartományt (ami természetesen állhat egyetlen cellából is), majd az eredmény és a referenciahivatkozás bal felső cellája közötti függőleges és vízszintes távolságot, azaz az eltolás mértékét sorokban és oszlopokban, végül az eredményül kapott hivatkozás magasságát és szélességét, szintén sorokban és oszlopokban. Utóbbi kettőt el is hagyhatjuk, ilyenkor az új tartomány magassága és/vagy szélessége megegyezik a referenciatartomány megfelelő méreteivel. Így például az =ELTOLÁS(A4;1;2;3;4) képlet a C5:F7 tartományt adja eredményül, hiszen az A4-hez képest a C2 az a cella, amelyik egy sorral lejjebb és két oszloppal jobbra van, és amihez képest az E7-ig tart a három sor széles és négy oszlop magas tartomány; míg az =ELTOLAS(A4:B5;2;3) eredménye a D6:E7 lesz. Mivel az A4-hez képest a D6 van két sorral lejjebb és három oszloppal balra, és mivel nem adtunk meg új szélességet és magasságot, az új tartomány is 2×2 cellából fog állni. Eltolásként negatív számot is megadhatunk, de arra figyelnünk kell, hogy az új tartomány minden cellája a munkalapra kerüljön, különben az eredmény a #HIV! hibaüzenet lesz.

Mindezen ismeretek birtokában képzeljünk el egy olyan táblázatot, amely az említett A4-en kezdődik, az első két oszlop tartalmazza az időszakot (év, hónap), az adatok pedig a C, D és E oszlopokban találhatók - tetszőleges hosszban. Lépjünk át a [Képletek] szalagra, nyissuk meg a Névkezelőt, majd nyomjuk meg az Új… gombot. A név mezőbe írjuk be azt, hogy "Időszak", hatókörnek választhatjuk a munkafüzetet vagy az adott munkalapot (főleg, amennyiben ugyanitt hozzuk majd létre a grafikont), a megjegyzést hagyjuk üresen, hivatkozásnak pedig írjuk be a következő képletet:

Ez egy sorral lejjebb lép az A4-es referencia cellához képest, majd onnan kijelöl annyi sort, ahány havi adatunk van, köszönhetően a DARAB2-nek, és két oszlopot, hogy az év- és a hónapszámok is benne legyenek az immáron "Időszak" nevű tartományban. Mielőtt az OK-val bezárnánk az ablakot, jelöljük ki képletünket akár az egér segítségével, akár - az [F2] billentyű megnyomását követően - a [Shift + balra nyíl] használatával. A következő lépés az első adattartomány létrehozása. Egy újabb név (pl. "Összesen") megadását követően az iménti képletünket - szintén az [F2] megnyomása után - úgy módosítjuk, hogy a vízszintes eltolás 0 helyett 2, az oszlopszélesség pedig 2 helyett 1 legyen (majd ezt ismételjük meg annyiszor, ahány adatoszlopunk van):

Oldalak: 1 2