Az Excel OFFSET függvényét és a tartományneveket használó dinamikus diagramok mérete az adatok hozzáadásakor vagy törlésekor változik. A dinamikus grafikonok táblázatainak használatával ellentétben ez a módszer visszafelé kompatibilis az Excel korai verzióival.
Az Excel OFFSET és COUNTA függvényeit használva dinamikus tartományneveket hozhat létre, amelyek az adatok hozzáadásakor vagy törlésekor automatikusan alkalmazkodnak.
Az OFFSET és a tartománynevek használatával dinamikusan átméretezett grafikonokról szóló oktató példafájl letöltéséhez kattintson a linkre. A példa vízszintes és függőleges adatokat is tartalmaz.
Az OFFSET és a tartománynév módszer több munkát igényel, mint a táblázathoz használt két kattintás, de a grafikon adatai felett mindenre kiterjedő ellenőrzést biztosít, amint azt a “Balanced Scorecards and Operational Dashboards with Microsoft Excel” című könyvem is mutatja. Ez az oldal, a CriticalToSuccess.com példákat tartalmaz az OFFSET használatának más módjaira, például gördülő diagram létrehozására, a diagramok vezérlésére legördülő menükkel, és még sok másra. A CriticalToSuccess.com oldalon az “OFFSET” vagy a “dinamikus tartomány” kifejezéssel kereshet példákat.
Sztatikus diagram létrehozása
A dinamikus diagramot először egy statikus diagram létrehozásával kezdje az alábbiak szerint,
1. Kattintson a B7:D12 tartományon belülre, hogy az Excel tudja, hol találhatóak a diagram adatai.
2. A Beszúrás lapon, a Diagramok csoportban válasszon egy egyszerű diagramot, például oszlop-, oszlop-, oszlop- vagy vonaldiagramot.
Mozgassa ezt a diagramot, hogy legyen hely az adattartomány alatti adatok hozzáadásához.
Dinamikus tartománynevek létrehozása OFFSET segítségével
Az Excel különböző verzióiban különböző módszerek vannak a tartománynevek képleteinek létrehozására és szerkesztésére. A következő utasítások az Excel 2007, 2010 és 2013 programokra vonatkoznak.
1. A Képletek lapon a Meghatározott név csoportban kattintson a Név meghatározása gombra. Megjelenik a Define Name (Név meghatározása) mező.
Készítsen tartományneveket a Define Name (Név meghatározása) mezőben.
2. A Name (Név) szövegmezőbe írja be az rngVDate (rngVDátum) szót. Az elnevezési konvenciók a következők,
rng Tartomány neve
V Az adatok függőleges irányultsága B7:B16-ban
Date A tartományban lévő adatok típusa
3. Írjon egy megjegyzést a megjegyzés szövegmezőbe, amely segít Önnek vagy másoknak tudni, hogy mire való ez a tartománynév.
4. Írja be a Refers to mezőbe az OFFSET képletet, amely kiszámítja a Date adatok tartományát.
=OFFSET(wrksht!$B$7,0,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5. Írja be az OFFSET képletet, amely a Date adatok tartományát számítja ki. Kattintson az Ok gombra.
Ezzel az OFFSET formulával a B7:B16 tartományon belül bárhová beírhat további dátumokat, amíg az új dátumok a régi dátumok listájának alján vannak, és a dátumok között nincsenek üres cellák.
Most ismételje meg ugyanezt a folyamatot az Értékesítési és az Egység adatok tartományneveinek létrehozásához.
A következő neveket és képleteket adja hozzá,
Az adattartomány neve utal
Eladás rngVEladás =OFFSET(wrksht!$C$7,0,0,0,COUNTA(wrksht!$C$7:$C$16),1)
Egységek rngVEgységek =OFFSET(wrksht!$D$7,0,0,0,COUNTA(wrksht!$D$7:$D$16),1)
Kattints ide, hogy megtudd, hogyan számítja ki az OFFSET a dinamikus tartománynév méretét. Ez a cikk tippeket ad a dinamikus tartománynevek ellenőrzéséhez is.
Kattintson ide, hogy megtudja, milyen hibát nem szabad elkövetnie az OFFSET-tel. Ön is elköveti ezt a hibát?
Kattintson ide az OFFSET és a tartománynevek használatával dinamikusan átméretezett grafikonok dinamikus átméretezéséről szóló bemutató példafájl letöltéséhez. A példa vízszintes és függőleges adatokat is tartalmaz.
Tartománynév beillesztése a grafikon dátumformulájába
Most dinamikussá teheti a grafikonjait azáltal, hogy a létrehozott tartományneveket beilleszti a grafikonok által az adatokra való hivatkozáshoz használt képletekbe. Ne aggódjon! Ez nagyon egyszerű. Valójában, ha megtanulod, hogyan kell ezt csinálni, misztikus képességekkel és azzal a hatalommal ruházod fel, hogy bármilyen diagramot az akaratod szerint hajlíts. (Azt is lehetővé teszi, hogy kijavítsa az elrontott diagramokat.)
Fentebb három dinamikus tartománynevet hozott létre, amelyek a diagram által használt adatokra hivatkoznak,
rngVDate
rngVSales
rngVUnits
A következőkben a diagram által használt cellahivatkozásokat a létrehozott dinamikus tartománynevekkel helyettesítjük,
1. Jelölje ki a diagramot a szélére kattintva.
2. A Diagrameszközök tervezése lap Adatok csoportjában kattintson az Adatok kijelölése eszközre az Adatforrás kijelölése párbeszédpanel megjelenítéséhez.
Az Adatforrás kijelölése párbeszédpanel segítségével mágikusan szabályozhatja a diagram által használt adatokat.
3. A Vízszintes (kategória) tengelycímkék alatt kattintson a Szerkesztés gombra a Tengelycímkék mező megjelenítéséhez. Ebben a párbeszédpanelen nehezen látható a képlet, ezért kattintson a cellahivatkozástól jobbra lévő kijelölő ikonra. Ekkor megjelenik egy szélesebb Tengelycímkék mező, amelyben hivatkozhat a képletre. VIGYÁZZON VIGYÁZATOSAN a szerkesztés előtt!
A tengelycímkék mezőben a vízszintes tengelyre való hivatkozás látható.
4. VIGYÁZZON VIGYÁZATOSAN! Mielőtt megpróbálná szerkeszteni a képletet, kattintson a képleten belülre, és nyomja meg a Szerkesztés billentyűt, az F2-t, hogy szerkeszthesse a képletet. Nyomja meg az End billentyűt, hogy a képlet végére lépjen. A ! jel utáni cellahivatkozásokat helyettesítse a tartomány nevével, ahogy az ezen a képen látható.
A képletének most így kell kinéznie,
=’Dynamically Resize Charts to Data.xlsx’!rngVDate
A tengelycímkék mező így néz ki az F2 megnyomása és a képlet szerkesztése után, hogy tartalmazza a dátumtartomány nevét.
TIPP: Ha a képlet szerkesztése közben hibát követ el, a Ctrl+Z (visszavonás) billentyűkombinációval lépjen vissza.
5. Kattintson az Ok gombra, hogy visszatérjen az Adatforrás kiválasztása párbeszédpanelre, így hozzáadhatja az Értékesítések és az Egységek nevét.
Tartománynév beillesztése a diagram Értékesítések és egységek képletébe
Most az Értékesítések és az Egységek tengely képleteit helyettesítse tartománynevekkel, majdnem ugyanezt az eljárást alkalmazva.
1. Az Adatforrás kiválasztása párbeszédpanelen kattintson az Értékesítés és a Szerkesztés gombra a Sorozat szerkesztése párbeszédpanel megjelenítéséhez.
Az Értékesítési adatok cellahivatkozást helyettesítse a tartomány nevével.
2. Kattintson a kiválasztó ikonra az Értékesítési sorozatra hivatkozó Sorozatérték formula jobb oldalán.
3. Kattintson a képleten belülre, nyomja le az F2 billentyűt a szerkesztéshez, és cserélje ki az Értékesítési adatok cellahivatkozásait a tartomány nevére, rngVSales.
A diagram most már a dinamikus tartománynevet használja az Értékesítési adatokra való hivatkozáshoz.