Jak vytvořit rozsah dynamických grafů v aplikaci Excel

Když vytvoříte graf v aplikaci Excel a změní se zdrojová data, musíte aktualizovat zdroj dat grafu, abyste se ujistili, že odráží nová data.

Pokud pracujete s grafy, které jsou často aktualizovány, je lepší vytvořit dynamický rozsah grafů.

Co je rozsah dynamických grafů?

Dynamický rozsah grafu je datový rozsah, který se automaticky aktualizuje při změně zdroje dat.

Tento dynamický rozsah se pak použije jako zdrojová data v grafu. Jak se data mění, dynamický rozsah se okamžitě aktualizuje, což vede k aktualizaci grafu.

Níže je uveden příklad grafu, který používá dynamický rozsah grafu.

Graf se aktualizuje o nové datové body pro květen a červen, jakmile jsou zadána data.

Jak vytvořit rozsah dynamických grafů v aplikaci Excel?

V aplikaci Excel existují dva způsoby, jak vytvořit dynamický rozsah grafu:

• Použití tabulky Excel
• Použití vzorců

Ve většině případů je použití tabulky Excel nejlepším způsobem, jak v aplikaci Excel vytvářet dynamické rozsahy.

Podívejme se, jak každá z těchto metod funguje.

Kliknutím sem stáhnete ukázkový soubor.

Použití tabulky Excel

Použití tabulky Excel je nejlepší způsob, jak vytvářet dynamické rozsahy, protože se automaticky aktualizuje, když je do něj přidán nový datový bod.

Funkce Excel Table byla zavedena ve verzi Windows Excel pro Excel 2007 a pokud jste před ní verze, nebudete ji moci použít (viz další část o vytváření dynamického rozsahu grafu pomocí vzorců).

Profesionální tip: Chcete -li převést rozsah buněk na tabulku aplikace Excel, vyberte buňky a použijte klávesovou zkratku - Ctrl + T (podržte klávesu Control a stiskněte klávesu T).

V níže uvedeném příkladu vidíte, že jakmile přidám nová data, tabulka aplikace Excel se rozbalí a zahrne tato data jako součást tabulky (všimněte si, že ohraničení a formátování se rozbalí a zahrnou do tabulky).

Nyní musíme při vytváření grafů používat tuto tabulku aplikace Excel.

Zde jsou přesné kroky k vytvoření dynamického čárového grafu pomocí tabulky aplikace Excel:

• Vyberte celou tabulku aplikace Excel.
• Přejděte na kartu Vložit.
• Ve skupině grafů vyberte graf „Čára se značkami“.

A je to!

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 toho, že zatímco přidávání nových dat automaticky aktualizuje graf, odstraněním dat by se úplně neodstranily datové body. Pokud například odeberete 2 datové body, graf vpravo zobrazí prázdné místo. Chcete -li to napravit, přetažením modré značky v pravé dolní části tabulky aplikace Excel odstraníte odstraněné datové body z tabulky (jak je znázorněno níže).

I když jsem vzal příklad čárového grafu, můžete také pomocí této techniky vytvořit další typy grafů, jako jsou sloupcové/sloupcové grafy.

Použití vzorců aplikace Excel

Jak jsem již zmínil, pomocí excelové tabulky je nejlepší způsob vytváření dynamických rozsahů grafů.

Pokud však z nějakého důvodu nemůžete použít tabulku aplikace Excel (možná pokud používáte aplikaci Excel 2003), existuje jiný (mírně komplikovaný) způsob, jak vytvářet dynamické rozsahy grafů pomocí vzorců aplikace Excel a pojmenovaných rozsahů.

Předpokládejme, že máte sadu dat, jak je uvedeno níže:

K vytvoření dynamického rozsahu grafu z těchto dat potřebujeme:

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

Dovolte mi nyní podrobně vysvětlit každý krok.

Krok 1 - Vytvoření dynamicky pojmenovaných rozsahů

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

• Přejděte na kartu „Vzorce“.
• Klikněte na „Správce jmen“.
• V dialogovém okně Správce názvů zadejte název jako ChartValues a do části Odkazuje na následující vzorec: = OFFSET (vzorec! $ B $ 2 ,,, COUNTIF (vzorec! $ B $ 2: $ B $ 100, ””))
• Klikněte na OK.
• V dialogovém okně Správce názvů klikněte na Nový.
• V dialogovém okně Správce názvů zadejte název jako Graf Měsíce a zadejte následující vzorec do Odkazuje na část: = OFFSET (vzorec! $ A $ 2 ,,, COUNTIF (vzorec! $ A $ 2: $ A $ 100, ””))
• Klikněte na Ok.
• Klikněte na Zavřít.

Výše uvedené kroky vytvořily v sešitu dva pojmenované rozsahy - ChartValue a ChartMonth (ty se týkají hodnot a měsíců v datové sadě).

Pokud přejdete a aktualizujete sloupec hodnot přidáním dalšího datového bodu, ChartValue pojmenovaný rozsah se nyní automaticky aktualizuje a zobrazí v něm další datový bod.

Kouzlo zde provádí funkce OFFSET.

Ve vzorci rozsahu pojmenovaném „ChartValue“ jsme jako referenční bod zadali B2. OFFSET vzorec začíná tam a rozšiřuje se tak, aby pokryl všechny vyplněné buňky ve sloupci.

Stejná logika funguje i ve vzorci pojmenovaného rozsahu ChartMonth.

Krok 2 - Vytvořte graf pomocí těchto pojmenovaných rozsahů

Nyní stačí vložit graf, který jako zdroj dat použije pojmenované rozsahy.

Zde jsou kroky k vložení grafu a použití dynamických rozsahů grafu:

• Přejděte na kartu Vložit.
• Klikněte na „Vložit čárový nebo plošný graf“ a vložte graf „Čára se značkami“. Tím se graf vloží do 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 „Legendární záznamy (série)“.
• Do pole Hodnota série zadejte = Vzorec!
• Klikněte na OK.
• Klikněte na tlačítko Upravit v „Štítky vodorovné (kategorie) osy“.
• V dialogovém okně „Popisky os“ zadejte = Formula! ChartMonths
• Klikněte na Ok.

A je to! Nyní váš graf používá dynamický rozsah a bude aktualizován, když přidáte/odstraníte datové body v grafu.

Při používání pojmenovaných rozsahů pomocí grafů 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 je prázdné, pojmenovaný rozsah nebude odkazovat 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žívání názvu listu ve zdroji grafu musíte dodržovat konvenci pojmenování. Pokud je například název listu jedno slovo, například Vzorec, můžete použít = Vzorec! ChartValue. Pokud však existuje více než jedno slovo, například Formula Chart, musíte použít = ‘Formula Chart’! ChartValue.