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

Tento tutoriál předvede, jak vytvořit dynamický rozsah grafů ve všech verzích aplikace Excel: 2007, 2010, 2013, 2016 a 2022.

Ve výchozím nastavení při rozbalení nebo smrštění datové sady používané k vykreslení grafu v aplikaci Excel je nutné ručně upravit také podkladová zdrojová data.

Vytvořením dynamických rozsahů grafů se však můžete těmto potížím vyhnout.

Dynamické rozsahy grafů vám umožňují automaticky aktualizovat zdrojová data pokaždé, když přidáváte nebo odebíráte hodnoty z datového rozsahu, což šetří spoustu času a úsilí.

V tomto tutoriálu se naučíte vše, co potřebujete vědět, abyste uvolnili sílu Rozsahy dynamických grafů.

Rozsahy dynamických grafů - úvod

Zvažte následující ukázkovou sadu dat analyzující kolísání ziskové marže:

V zásadě existují dva způsoby, jak nastavit rozsah dynamických grafů:

1. Převod rozsahu dat na tabulku
2. Použití dynamických pojmenovaných rozsahů jako zdrojových dat grafu.

Obě metody mají svá pro a proti, proto si o každé z nich povíme podrobněji, abychom vám pomohli určit, která vám bude nejlépe vyhovovat.

Bez dalších okolků začneme.

Metoda tabulky

Dovolte mi začít tím, že vám ukážu nejrychlejší a nejsnadnější způsob, jak splnit daný úkol. Takže tady je cvičení: Proměňte datový rozsah v tabulku a jste zlatí jednodušší než loupání hrachu.

Tímto způsobem bude vše, co zadáte do buněk na konci této tabulky, automaticky zahrnuto do zdrojových dat grafu.

Zde je návod, jak toho dosáhnout ve dvou jednoduchých krocích.

Krok č. 1: Převeďte rozsah dat na tabulku.

Přímo z brány transformujte rozsah buněk obsahující data vašeho grafu do tabulky.

1. Zvýrazněte celý rozsah dat (A1: B6).
2. Klikněte na Vložit tab.
3. Udeř "Stůl" knoflík.

V Vytvořit tabulku v dialogovém okně proveďte následující:

1. Znovu zkontrolujte, zda se zvýrazněný rozsah buněk shoduje s celou tabulkou dat.
2. Pokud tabulka neobsahuje žádný řádek záhlaví, zrušte zaškrtnutí políčka „Můj stůl má záhlaví" krabice.
3. Klikněte na „OK.“

V důsledku toho byste měli skončit s touto tabulkou:

Krok č. 2: Vytvořte graf na základě tabulky.

Základ byl položen, což znamená, že nyní můžete pomocí tabulky nastavit graf.

1. Zvýrazněte celou tabulku (A1: B6).
2. Přejděte na Vložit tab.
3. Vytvořte libovolný 2-D graf. Pro ilustraci vytvořme jednoduchý sloupcový graf (Vložit sloupcový nebo sloupcový graf> Seskupený sloupec).

A je to! Chcete -li otestovat techniku, zkuste přidat nové datové body ve spodní části tabulky abyste je viděli automaticky mapované na grafu. O kolik jednodušší to může být?

POZNÁMKA: S tímto přístupem by měl soubor dat nikdy obsahovat prázdné buňky-to zničí graf.

Metoda dynamického pojmenovaného rozsahu

Ačkoli se snadno aplikuje, dříve prokázané, Metoda tabulky má vážné nevýhody. Graf se například zpacká, kdykoli je nová sada dat menší než původní tabulka dat plus, někdy prostě nechcete, aby byl rozsah dat převeden na tabulku.

Volba pojmenovaných rozsahů může z vaší strany vyžadovat trochu více času a úsilí, ale tato technika popírá nevýhody metody tabulky a navíc umožňuje dynamický rozsah mnohem pohodlněji pracovat na dálku.

Krok č. 1: Vytvořte dynamické pojmenované rozsahy.

Nejprve nastavte pojmenované rozsahy, které budou nakonec použity jako zdrojová data pro váš budoucí graf.

1. Jděte na Vzorce tab.
2. Klikněte na „Správce jmen.”
3. V Správce jmen v zobrazeném dialogovém okně vyberte „Nový.”

V Nové jméno dialogové okno, vytvořte zbrusu nový pojmenovaný rozsah:

1. Zadejte „Čtvrťák" vedle "název”Pole. Pro vaše pohodlí přizpůsobte název dynamického rozsahu odpovídající buňce řádku záhlaví sloupec A (A1).
2. V “Rozsah”Vyberte aktuální list. V našem případě ano List 1.
3. Do pole „Zadejte následující vzorec“Odkazuje na”Pole: = OFFSET (Sheet1! $ A $ 2,0,0, COUNTA (Sheet1! $ A: $ A) -1,1)

Jednoduše řečeno, při každé změně libovolné buňky v listu vrátí funkce OFFSET pouze skutečné hodnoty v sloupec A, vynechání buňky řádku záhlaví (A1), zatímco funkce COUNTA přepočítá počet hodnot ve sloupci pokaždé, když se list aktualizuje-efektivně za vás provede veškerou špinavou práci.

Pojďme si vzorec rozebrat podrobněji, abychom vám pomohli pochopit, jak funguje:

POZNÁMKA: Název pojmenovaného rozsahu musí začínat písmenem nebo podtržítkem a nesmí obsahovat mezery.

Stejným tokenem nastavte další pojmenovaný rozsah na základě sloupec Marže zisku (sloupec B) pomocí tohoto vzorce a označte jej „Zisk_Margin”:

1	= OFFSET (Sheet1! $ B $ 2,0,0, COUNTA (Sheet1! $ B: $ B) -1,1)

Pokud tabulka dat obsahuje více sloupců se skutečnými hodnotami, opakujte stejný postup. V našem případě byste ve výsledku měli mít připravené k akci dva pojmenované rozsahy:

Krok č. 2: Vytvořte prázdný graf.

Prošli jsme nejtěžší částí. Nyní je čas nastavit prázdný graf, abyste do něj mohli ručně vkládat dynamicky pojmenované rozsahy.

1. Vyberte libovolnou prázdnou buňku v aktuálním listu (List 1).
2. Vraťte se zpět do Vložit tab.
3. Nastavte libovolný 2-D graf, který chcete. Pro náš příklad vytvoříme sloupcový graf (Vložit sloupcový nebo sloupcový graf> Seskupený sloupec).

Krok č. 3: Přidejte pojmenovaný rozsah/rozsahy obsahující skutečné hodnoty.

Nejprve vložte pojmenovaný rozsah (Zisk_Margin) spojené se skutečnými hodnotami (sloupec B) do grafu.

Klikněte pravým tlačítkem na prázdný graf a vyberte „Vyberte Data”Z kontextové nabídky.

V Vyberte Zdroj dat v dialogovém okně klikněte na „Přidat.”

V Upravit sérii pole, vytvořte novou datovou řadu:

1. Pod „Název série, ”Zvýrazněte odpovídající buňku řádku záhlaví (B1).
2. Pod „Hodnoty řady, “Zadejte pojmenovaný rozsah, který se má vykreslit do grafu, zadáním následujícího:„= List1! Profit_Margin."Odkaz se skládá ze dvou částí: názvů aktuálního listu." (= List1) a příslušný dynamický pojmenovaný rozsah (Profit_Margin). Vykřičník slouží k propojení dvou proměnných dohromady.
3. Vyberte „OK.”

Jakmile tam bude, Excel automaticky namapuje hodnoty:

Krok č. 4: Vložte pojmenovaný rozsah s popisky os.

Nakonec nahraďte výchozí popisky os osy kategorie pojmenovaným rozsahem, ze kterého se skládají sloupec A (Čtvrťák).

V Vyberte Zdroj dat dialogové okno v části „Štítky osy horizontální (kategorie)," vybrat "Upravit" knoflík.

Poté vložte pojmenovaný rozsah do grafu zadáním následujícího odkazu pod „Rozsah označení osy:”

1	= List1! Čtvrtletí

Nakonec je připraven sloupcový graf na základě rozsahu dynamického grafu:

Podívejte se na to: Graf se automaticky aktualizuje, kdykoli přidáte nebo odeberete data v dynamickém rozsahu.