Amikor egy diagramot hoz létre Excelben, és a forrásadatok megváltoznak, frissítenie kell a diagram adatforrását, hogy az tükrözze az új adatokat.
Amennyiben gyakran frissülő diagramokkal dolgozik, jobb, ha dinamikus diagramtartományt hoz létre.
Ez az oktatóanyag a következőket tartalmazza:
Mi az a dinamikus diagramtartomány?
A dinamikus diagramtartomány egy olyan adattartomány, amely automatikusan frissül, amikor megváltoztatja az adatforrást.
Ezt a dinamikus tartományt azután forrásadatként használjuk egy diagramban. Ahogy az adatok változnak, a dinamikus tartomány azonnal frissül, ami a diagram frissítéséhez vezet.
Az alábbiakban egy példa egy dinamikus diagramtartományt használó diagramra.
Megjegyezzük, hogy a diagram a májusi és júniusi új adatpontokkal frissül, amint az adatok bevitelre kerülnek.
Hogyan hozhatunk létre dinamikus diagramtartományt az Excelben?
A dinamikus diagramtartomány létrehozásának két módja van az Excelben:
- Az Excel táblázat használata
- A képletek használata
A legtöbb esetben az Excel táblázat használata a legjobb módja a dinamikus tartományok létrehozásának az Excelben.
Lássuk, hogyan működnek az egyes módszerek.
A példa fájl letöltéséhez kattintson ide.
Az Excel táblázat használata
A dinamikus tartományok létrehozásának legjobb módja az Excel táblázat használata, mivel az automatikusan frissül, amikor új adatpont kerül hozzá.
Az Excel táblázat funkciót az Excel 2007-es Windows verziójában vezették be, és ha az azt megelőző verziókról van szó, akkor nem tudja használni (lásd a következő, a dinamikus diagramtartomány képletek segítségével történő létrehozásáról szóló részt).
Az alábbi példában látható, hogy amint új adatokat adok hozzá, az Excel-táblázat kitágul, hogy ezeket az adatokat a táblázat részeként tartalmazza (vegye figyelembe, hogy a keret és a formázás kitágul, hogy a táblázatban szerepeljenek).
Most ezt az Excel-táblázatot kell használnunk a diagramok létrehozása során.
Íme a dinamikus vonaldiagram létrehozásának pontos lépései az Excel táblázat felhasználásával:
- Kijelöljük a teljes Excel táblázatot.
- Menjünk a Beszúrás lapra.
- A Diagramok csoportban válasszuk a ‘Vonal jelölésekkel’ diagramot.
Ez az!
A fenti lépésekkel egy vonaldiagramot illesztünk be, amely automatikusan frissül, amikor újabb adatokat adunk hozzá az Excel táblázathoz.
Megjegyezzük, hogy míg az új adatok hozzáadása automatikusan frissíti a diagramot, az adatok törlése nem távolítja el teljesen az adatpontokat. Ha például eltávolít 2 adatpontot, a diagram jobb oldalon üres helyet fog mutatni. Ennek kijavításához húzza az Excel táblázat jobb alsó részén lévő kék jelet, hogy a törölt adatpontokat eltávolítsa a táblázatból (az alábbiakban látható módon).
Míg én a vonaldiagram példáját vettem, ezzel a technikával más típusú diagramokat, például oszlop- és oszlopdiagramokat is létrehozhat.
Excel képletek használata
Amint említettem, az Excel táblázat használata a legjobb módja a dinamikus diagramtartományok létrehozásának.
Ha azonban valamilyen okból nem tudja használni az Excel táblázatot (esetleg ha az Excel 2003-at használja), van egy másik (kissé bonyolult) módja is a dinamikus diagramtartományok létrehozásának az Excel képletek és nevesített tartományok használatával.
Tegyük fel, hogy az alábbiakban bemutatott adatkészlettel rendelkezünk:
Hogy dinamikus diagramtartományt hozzunk létre ezekből az adatokból, a következőkre van szükségünk:
- Két dinamikus névleges tartományt kell létrehoznunk az OFFSET képlet segítségével (egyet-egyet az ‘Értékek’ és a ‘Hónapok’ oszlophoz). Egy adatpont hozzáadása/törlése automatikusan frissítené ezeket a megnevezett tartományokat.
- Beillesztünk egy diagramot, amely a megnevezett tartományokat adatforrásként használja.
Most részletesen elmagyarázom az egyes lépéseket.
1. lépés – Dinamikus megnevezett tartományok létrehozása
A következőkben a dinamikus megnevezett tartományok létrehozásának lépései következnek:
- Menjen a ‘Képletek’ lapra.
- Kattintson a ‘Name Manager’ gombra.
- A Name Manager párbeszédpanelen adja meg a ChartValues nevet, és írja be a következő képletet a Refers to part részbe: =OFFSET(Formula!$B$2,,,COUNTIF(Formula!$B$2:$B$100,”<>”))
- Kattintson az OK gombra.
- A Name Manager párbeszédpanelen kattintson az Új gombra.
- A Name Manager párbeszédpanelen adja meg a ChartMonths nevet, és írja be a következő képletet a Refers to részbe: =OFFSET(Formula!$A$2,,,COUNTIF(Formula!$A$2:$A$100,”<>”))
- Kattintson az OK gombra.
- Kattintson a Bezárás gombra.
A fenti lépésekkel két elnevezett tartományt hoztunk létre a munkafüzetben – ChartValue és ChartMonth (ezek az értékek, illetve a hónapok tartományára utalnak az adathalmazban).
Ha megyünk és frissítjük az érték oszlopot egy újabb adatpont hozzáadásával, a ChartValue nevű tartomány most automatikusan frissül, hogy megjelenítse a további adatpontot benne.
A varázslatot itt az OFFSET függvény végzi.
A ‘ChartValue’ nevű tartomány képletében a B2-t adtuk meg referenciapontként. Az OFFSET formula ott kezdődik, és kiterjed az oszlop összes kitöltött cellájára.
Ugyanez a logika működik a ChartMonth elnevezett tartomány képletében is.
2. lépés – Diagram létrehozása ezen elnevezett tartományok felhasználásával
Most már csak be kell illesztenünk egy diagramot, amely a megnevezett tartományokat használja adatforrásként.
A következők a lépések egy diagram beszúrásához és a dinamikus diagramtartományok használatához:
- Menjen a Beszúrás lapra.
- Kattintson a “Vonal- vagy területi diagram beszúrása” lehetőségre, és szúrja be a “Vonal jelölésekkel” diagramot. Ezzel beszúrja a diagramot a munkalapba.
- A diagram kijelölésével lépjen a Tervezés lapra.
- Kattintson az Adatok kiválasztása gombra.
- Az ‘Adatforrás kiválasztása’ párbeszédpanelen kattintson a ‘Legenda bejegyzések (sorozatok)’-nál a Hozzáadás gombra.
- A Sorozat érték mezőbe írja be a =Formula!ChartValues (vegye figyelembe, hogy meg kell adnia a munkalap nevét a megnevezett tartomány előtt, hogy ez működjön).
- Kattintson az OK gombra.
- Kattintson a Szerkesztés gombra a ‘Vízszintes (kategória) tengelycímkék’ mezőben.
- A ‘Axis Labels’ párbeszédpanelen írja be a =Formula!ChartMonths
- Kattintson az Ok gombra.
Ez az! Mostantól a diagramja dinamikus tartományt használ, és frissülni fog, amikor adatpontokat ad hozzá/töröl a diagramban.
Néhány fontos dolog, amit tudnia kell, amikor név szerinti tartományokat használ a diagramokkal:
- Nem lehetnek üres cellák a diagram adataiban. Ha van üres, a named range nem a megfelelő adatkészletre hivatkozna (mivel az összeszámlálás miatt kevesebb cellára hivatkozna).
- A táblázat forrásában a lap nevének használatakor követni kell a névadási konvenciót. Ha például a lap neve egyetlen szó, például Formula, akkor használhatja a =Formula!ChartValue. De ha egynél több szó van benne, például Formula Chart, akkor a =’Formula Chart’!ChartValue-t kell használnia.
Az alábbi Excel oktatóanyagok is tetszhetnek:
- Hogyan készítsünk hőmérő diagramot az Excelben.
- Hogyan készítsünk haranggörbét Excelben.
- Léptékdiagram készítése Excelben.
- Pareto-diagram készítése Excelben.
- Hogyan készítsünk hisztogramot Excelben
- Hogyan adjunk hozzá hibasávokat Excelben (vízszintes/függőleges/egyéni)
Mi a hibasávok hozzáadása Excelben?