Keď vytvoríte graf v programe Excel a zdrojové údaje sa zmenia, musíte aktualizovať zdroj údajov grafu, aby sa zaistilo, že bude odrážať nové údaje.
V prípade, že pracujete s grafmi, ktoré sú často aktualizované, je lepšie vytvoriť dynamický rozsah grafov.
Čo je rozsah dynamických grafov?
Rozsah dynamických grafov je rozsah údajov, ktorý sa automaticky aktualizuje pri zmene zdroja údajov.
Tento dynamický rozsah sa potom použije ako zdroj údajov v grafe. Ako sa údaje menia, dynamický rozsah sa okamžite aktualizuje, čo vedie k aktualizácii grafu.
Nasleduje príklad grafu, ktorý používa dynamický rozsah grafu.
Hneď po zadaní údajov sa graf aktualizuje o nové údajové body za máj a jún.
Ako vytvoriť rozsah dynamických grafov v programe Excel?
V programe Excel existujú dva spôsoby vytvorenia rozsahu dynamického grafu:
- Použitie tabuľky programu Excel
- Použitie vzorcov
Vo väčšine prípadov je použitie tabuľky programu Excel najlepším spôsobom vytvárania dynamických rozsahov v programe Excel.
Pozrime sa, ako každá z týchto metód funguje.
Kliknutím sem stiahnete ukážkový súbor.
Použitie tabuľky programu Excel
Použitie tabuľky programu Excel je najlepší spôsob, ako vytvárať dynamické rozsahy, pretože sa automaticky aktualizuje, keď je do nej pridaný nový údajový bod.
Funkcia Excel Table bola predstavená vo verzii Windows Excel Excel 2007 a ak máte verzie pred ňou, nebudete ju môcť použiť (pozrite si nasledujúcu časť o vytváraní rozsahu dynamických grafov pomocou vzorcov).
Pro tip: Ak chcete previesť rozsah buniek na tabuľku programu Excel, vyberte bunky a použite klávesovú skratku - Ctrl + T (podržte kláves Control a stlačte kláves T).V nižšie uvedenom príklade vidíte, že akonáhle pridám nové údaje, tabuľka programu Excel sa rozšíri a zahrnie tieto údaje ako súčasť tabuľky (všimnite si, že orámovanie a formátovanie sa do tabuľky zahrnú).
Teraz musíme pri vytváraní grafov používať túto tabuľku programu Excel.
Tu sú presné kroky na vytvorenie dynamického čiarového grafu pomocou tabuľky programu Excel:
- Vyberte celú tabuľku programu Excel.
- Prejdite na kartu Vložiť.
- V skupine grafov vyberte graf „Riadok so značkami“.
To je všetko!
Vyššie uvedené kroky by vložili čiarový graf, ktorý by sa automaticky aktualizoval, keď do tabuľky programu Excel pridáte ďalšie údaje.
Všimnite si toho, že pri pridávaní nových údajov sa graf automaticky aktualizuje, odstránením údajov sa však úplne neodstránia údajové body. Ak napríklad odstránite 2 dátové body, v grafe sa vpravo zobrazí prázdne miesto. Ak to chcete napraviť, potiahnutím modrej značky v pravom dolnom rohu tabuľky programu Excel odstránite z tabuľky odstránené dátové body (ako je uvedené nižšie).
Aj keď som vzal príklad čiarového grafu, pomocou tejto techniky môžete vytvoriť aj iné typy grafov, ako sú stĺpcové/stĺpcové grafy.
Použitie vzorcov programu Excel
Ako som už spomenul, pomocou tabuľky programu Excel je najlepší spôsob vytvárania rozsahov dynamických grafov.
Ak však z nejakého dôvodu nemôžete používať tabuľku programu Excel (možno ak používate program Excel 2003), existuje iný (mierne komplikovaný) spôsob vytvárania rozsahov dynamických grafov pomocou vzorcov programu Excel a pomenovaných rozsahov.
Predpokladajme, že máte súbor údajov, ako je uvedené nižšie:
Na vytvorenie rozsahu dynamických grafov z týchto údajov potrebujeme:
- Vytvorte dva dynamické pomenované rozsahy pomocou vzorca OFFSET (po jednom pre stĺpce „Hodnoty“ a „Mesiace“). Pridaním/odstránením dátového bodu by sa tieto pomenované rozsahy automaticky aktualizovali.
- Vložte graf, ktorý ako zdroj údajov používa pomenované rozsahy.
Dovoľte mi teraz podrobne vysvetliť každý krok.
Krok 1 - Vytvorenie dynamických pomenovaných rozsahov
Nasledujú kroky na vytvorenie dynamických pomenovaných rozsahov:
- Prejdite na kartu „Vzorce“.
- Kliknite na „Správca mien“.
- V dialógovom okne Správca mien zadajte názov ako ChartValues a do časti Odkazuje na nasledujúci vzorec: = OFFSET (vzorec! $ B $ 2 ,,, COUNTIF (vzorec! $ B $ 2: $ B $ 100, ””))
- Kliknite na tlačidlo OK.
- V dialógovom okne Správca mien kliknite na položku Nový.
- V dialógovom okne Správca mien zadajte názov ako ChartMonths a do časti Odkazuje na nasledujúci vzorec: = OFFSET (vzorec! $ A $ 2 ,,, COUNTIF (vzorec! $ A $ 2: $ A $ 100, ””))
- Kliknite na Ok.
- Kliknite na Zavrieť.
Vyššie uvedené kroky vytvorili v zošite dva pomenované rozsahy - ChartValue a ChartMonth (tieto sa vzťahujú na hodnoty a rozsah mesiacov v množine údajov).
Ak pôjdete a aktualizujete stĺpec hodnôt pridaním ešte jedného údajového bodu, pomenovaný rozsah ChartValue by sa teraz automaticky aktualizoval a zobrazil v ňom ďalší údajový bod.
Kúzlo tu vykonáva funkcia OFFSET.
Vo vzorci rozsahu s názvom „ChartValue“ sme ako referenčný bod uviedli B2. OFFSET vzorec začína tam a rozprestiera sa tak, aby pokryl všetky vyplnené bunky v stĺpci.
Rovnaká logika funguje aj vo vzorci pomenovaného rozsahu ChartMonth.
Krok 2 - Vytvorte graf pomocou týchto pomenovaných rozsahov
Teraz stačí vložiť graf, ktorý ako zdroj údajov použije pomenované rozsahy.
Tu sú kroky na vloženie grafu a použitie dynamických rozsahov grafu:
- Prejdite na kartu Vložiť.
- Kliknite na položku „Vložiť čiarový alebo plošný graf“ a vložte tabuľku „Čiara so značkami“. Tabuľka sa vloží do pracovného hárka.
- Keď je graf vybraný, prejdite na kartu Návrh.
- Kliknite na položku Vybrať údaje.
- V dialógovom okne „Vybrať zdroj údajov“ kliknite na tlačidlo Pridať v časti „Legendárne záznamy (série)“.
- Do poľa Hodnota série zadajte = Vzorec! ChartValues (na to, aby to fungovalo, musíte zadať názov pracovného hárka pred pomenovaným rozsahom).
- Kliknite na tlačidlo OK.
- Kliknite na tlačidlo Upraviť v časti „Menovky horizontálnej (kategórie) osi“.
- V dialógovom okne „Označenia osí“ zadajte = Vzorec! ChartMonths
- Kliknite na Ok.
To je všetko! Váš graf teraz používa dynamický rozsah a aktualizuje sa, keď do neho pridáte/odstránite údajové body.
Niekoľko dôležitých vecí, ktoré je potrebné vedieť pri použití pomenovaných rozsahov s grafmi:
- V údajoch grafu by nemali byť žiadne prázdne bunky. Ak je prázdny, pomenovaný rozsah nebude odkazovať na správny súbor údajov (pretože celkový počet by viedol k tomu, že by odkazoval na menší počet buniek).
- Pri použití názvu hárka v zdroji grafu musíte postupovať podľa konvencií pomenovania. Ak je napríklad názov hárka jediné slovo, napríklad Vzorec, môžete použiť = Vzorec! ChartValue. Ak však existuje viac ako jedno slovo, napríklad Formula Chart, musíte použiť = ‘Formula Chart’! ChartValue.
