Jak vytvořit dynamický rozsah grafu v aplikaci Excel

Tip pro profesionály:

Na příkladu níže vidíte, že jakmile přidám nová data, tabulka Excelu se rozšíří tak, aby tato data byla součástí tabulky (všimněte si, že ohraničení a formátování se rozšíří tak, aby byla součástí tabulky).

Nyní potřebujeme tuto tabulku Excelu použít při vytváření grafů.

Tady je přesný postup pro vytvoření dynamického čárového grafu pomocí tabulky aplikace Excel:

• Vyberte celou tabulku aplikace Excel.
• Přejděte na kartu Vložení.
• Ve skupině Grafy vyberte graf „Čárový se značkami“.

To je vše!

Výše uvedené kroky by vložily čárový graf, který by se automaticky aktualizoval při přidání dalších dat do tabulky aplikace Excel.

Všimněte si, že zatímco přidáním nových dat se graf automaticky aktualizuje, odstraněním dat by se datové body zcela neodstranily. Pokud například odstraníte 2 datové body, zobrazí se v grafu vpravo prázdné místo. Chcete-li to napravit, přetáhněte modrou značku v pravém dolním rohu tabulky aplikace Excel, čímž odstraníte odstraněné datové body z tabulky (jak je znázorněno níže).

Přestože jsem uvedl příklad čárového grafu, můžete pomocí této techniky vytvořit i jiné typy grafů, například sloupcové/sloupcové grafy.

Použití vzorců aplikace Excel

Jak jsem již zmínil, použití tabulky aplikace Excel je nejlepším způsobem, jak vytvořit dynamické rozsahy grafu.

Pokud však z nějakého důvodu nemůžete použít tabulku Excelu (případně pokud používáte Excel 2003), existuje jiný (poněkud komplikovaný) způsob vytváření dynamických rozsahů grafů pomocí vzorců Excelu a pojmenovaných rozsahů.

Předpokládejme, že máte sadu dat podle následujícího obrázku:

Chceme-li z těchto dat vytvořit dynamický rozsah grafu, musíme:

1. Vytvořit dva dynamické pojmenované rozsahy pomocí vzorce OFFSET (po jednom pro sloupec „Hodnoty“ a „Měsíce“). Přidáním/odstraněním datového bodu by se tyto pojmenované rozsahy automaticky aktualizovaly.
2. Vložit graf, který používá pojmenované rozsahy jako zdroj dat.

Nechte mě nyní podrobně vysvětlit jednotlivé kroky.

Krok 1 – Vytvoření dynamických pojmenovaných rozsahů

Níže jsou uvedeny kroky pro vytvoření dynamických pojmenovaných rozsahů:

• Přejděte na kartu „Vzorce“.
• Klikněte na ‚Správce názvů‘.
• V dialogovém okně Správce názvů zadejte název jako ChartValues a do pole Odkazuje na část zadejte následující vzorec: =OFFSET(Vzorec!$B$2,,,COUNTIF(Formula!$B$2:$B$100,“<>“))
• Klepněte na OK.
• V dialogovém okně Správce názvů klepněte na Nový.
• V dialogovém okně Správce názvů zadejte název ChartMonths a do části Odkazuje na zadejte následující vzorec: =OFFSET(Vzorec!$A$2,,,COUNTIF(Vzorec!$A$2:$A$100,“<>“))
• Klikněte na tlačítko OK.
• Klikněte na Zavřít.

Výše uvedené kroky vytvořily v sešitě dva pojmenované rozsahy – ChartValue a ChartMonth (ty odkazují na rozsah hodnot, resp. měsíců v datovém souboru).

Pokud byste šli aktualizovat sloupec hodnot přidáním dalšího datového bodu, pojmenovaný rozsah ChartValue by se nyní automaticky aktualizoval tak, aby v něm byl zobrazen další datový bod.

Kouzlo zde provádí funkce OFFSET.

V pojmenovaném rozsahu ‚ChartValue‘ jsme jako referenční bod zadali B2. Vzorec OFFSET zde začíná a rozšiřuje se tak, aby pokryl všechny vyplněné buňky sloupce.

Stejná logika funguje i ve vzorci pojmenovaného rozsahu ChartMěsíc.

Krok 2 – Vytvoření grafu pomocí těchto pojmenovaných rozsahů

Teď už stačí jen vložit graf, který bude pojmenované rozsahy používat jako zdroj dat.

Níže jsou uvedeny kroky pro vložení grafu a použití dynamických rozsahů grafu:

• Přejděte na kartu Vložení.
• Klikněte na „Vložit čárový nebo plošný graf“ a vložte graf „Čárový se značkami“. Tím se graf vloží do pracovního listu.
• S vybraným grafem přejděte na kartu Návrh.
• Klikněte na Vybrat data.
• V dialogovém okně „Vybrat zdroj dat“ klikněte na tlačítko Přidat v části „Položky legendy (řady)“.
• V poli Hodnota řady zadejte =Formula!ChartValues (všimněte si, že aby to fungovalo, musíte před pojmenovaný rozsah zadat název pracovního listu).
• Klikněte na tlačítko OK.
• Klikněte na tlačítko Upravit v poli ‚Popisky vodorovné osy (kategorie)‘.
• V dialogovém okně ‚Popisky os‘ zadejte =Formula!ChartMonths
• Klikněte na Ok.

To je vše! Nyní váš graf používá dynamický rozsah a bude se aktualizovat při přidávání/odstraňování datových bodů v grafu.

Při používání pojmenovaných rozsahů s grafy je třeba vědět několik důležitých věcí:

• V datech grafu by neměly být žádné prázdné buňky. Pokud by tam prázdné byly, pojmenovaný rozsah by neodkazoval na správnou datovou sadu (protože celkový počet by vedl k tomu, že by odkazoval na menší počet buněk).
• Při použití názvu listu ve zdroji grafu je třeba dodržovat jmennou konvenci. Pokud je například název listu tvořen jedním slovem, například Formula, pak můžete použít =Formula!ChartValue. Pokud je však v listu více než jedno slovo, například Formula Chart, pak musíte použít =’Formula Chart‘!ChartValue.

Mohou se vám také líbit následující návody k aplikaci Excel:

• Jak vytvořit graf teploměru v aplikaci Excel.
• Jak vytvořit zvonkovou křivku v aplikaci Excel.
• Vytvoření krokového grafu v aplikaci Excel.
• Vytvoření Paretova grafu v aplikaci Excel.
• Jak vytvořit histogram v aplikaci Excel
• Jak přidat chybové sloupce v aplikaci Excel (vodorovné/svislé/vlastní)

.