Jak vytvářet interaktivní grafy s dynamickými prvky v aplikaci Excel

Tento tutoriál předvede, jak vytvářet interaktivní grafy s dynamickými prvky ve všech verzích aplikace Excel: 2007, 2010, 2013, 2016 a 2022.

Interaktivní graf je graf, který uživateli umožňuje řídit, které datové řady mají být v grafu znázorněny, což umožňuje zobrazit nebo skrýt data pomocí ovládacích prvků formuláře. Přicházejí v mnoha tvarech a formách, od tlačítek možností po posuvníky a rozevírací seznamy.

Díky dynamizaci některých prvků grafu-například názvu grafu-je možné automatizovat proces aktualizace grafů při každém přidávání nebo odebírání položek z podkladových zdrojových dat.

Vytváření interaktivních grafů s dynamickými prvky vám tedy umožní zabít dvě mouchy jednou ranou.

Interaktivní grafy vám na začátku pomohou úhledně zobrazit obrovské množství dat pomocí jediného grafu:

Kromě toho převedení některých prvků grafu ze statického na dynamické ušetří dlouhodobě nespočet hodin a zabrání vám zapomenout na aktualizaci grafu při každé změně datové tabulky.

Jak již bylo řečeno, tento návod je rychlým kurzem pokročilých technik vytváření grafů v aplikaci Excel: naučíte se vytvářet interaktivní grafy jak pomocí rozevíracího seznamu, tak pomocí posuvníku, stejně jako zvládnete způsoby, jak vytvořit název grafu. a rozsah dat grafu dynamický.

Vzorek dat

Abychom vám ukázali, co je co, potřebujeme pro práci nějaká nezpracovaná data. Z tohoto důvodu zvažte následující tabulku ilustrující čísla prodejů knih fiktivního online knihkupectví:

Jak vytvořit název dynamického grafu

Pojďme se nejprve věnovat jednoduché části: dynamizaci názvu grafu.

Název můžete zdynamizovat tak, že ho propojíte s konkrétní buňkou v listu, aby se všechny změny hodnoty v této buňce automaticky projevily v názvu grafu.

A krása této metody spočívá v tom, že stačí pár jednoduchých kroků, aby se název grafu změnil ze statického na dynamický:

1. Poklepejte na název grafu.
2. Klikněte na Formula Bar a napište „=“Do toho.
3. Vyberte libovolnou buňku, kterou chcete přiřadit jako nový název grafu.

Na první pohled se tato myšlenka zdá být docela primitivní. Ale stěží jsme dokonce poškrábali povrch věcí, které byly možné díky použití této jednoduché techniky. Chcete-li se dozvědět více, podívejte se na podrobný návod, který podrobněji popisuje názvy dynamických grafů v Excelu.

Jak vytvořit rozsah dynamických grafů

Další krok: nastavení dynamického rozsahu grafu. Opět platí, že pokaždé, když přidáte nebo odeberete položky z datového rozsahu používaného při vytváření grafu aplikace Excel, zdrojová data by měla být také ručně upravena.

Naštěstí si můžete ušetřit potíže se vším tím, že vytvoříte dynamický rozsah grafu, který automaticky aktualizuje graf při každé změně zdrojových dat.

V zásadě existují dva způsoby, jak toho dosáhnout: metoda tabulky a technika pojmenovaných rozsahů. Stručně se zde budeme zabývat jednodušší tabulkovou metodou, ale abyste získali úplný přehled o tom, jak využít sílu dynamických rozsahů grafů v Excelu, podívejte se na tutoriál, který podrobněji popisuje všechny vstupy a výstupy.

Každopádně zpět k metodě tabulky. Nejprve přeměňte rozsah buněk, který chcete použít pro sestavení grafu, na tabulku.

1. Vyberte celý rozsah dat (A2: G6).
2. Přejděte na Vložit tab.
3. Klikněte na „Stůl" knoflík.

Pokud vaše data nemají žádná záhlaví, zrušte zaškrtnutí políčka „Můj stůl má záhlaví" krabice.

Jakmile se tam dostanete, rozsah dat bude převeden do tabulky.

Nyní stačí graf vykreslit a použít tabulku jako zdrojová data grafu:

1. Zvýrazněte tabulku (A2: G6).
2. Přejděte na Vložit tab.
3. Vytvořte libovolný 2-D graf-například seskupený sloupcový graf (Vložit sloupcový nebo sloupcový graf> Klastrovaný pruh).

Voilà! Jste připraveni. Nyní to vyzkoušejte vložením nových položek do tabulky, abyste je viděli automaticky znovu načíst v grafu.

Jak vytvořit interaktivní graf s rozevíracím seznamem

Nyní, když jste udělali své první kroky do oblasti pokročilých nástrojů pro vizualizaci dat v aplikaci Excel, je čas přejít k těžké části: interaktivním grafům aplikace Excel.

Když máte spoustu dat k vytváření grafů, zavedení interaktivních funkcí do grafů aplikace Excel-ať už jde o rozevírací seznamy, zaškrtávací políčka nebo posuvníky-vám pomůže zachovat místo na palubní desce a zbavit grafu grafu a zároveň bude pro uživatele pohodlnější analyzovat data.

Podívejte se například na níže uvedený sloupcový graf. Namísto zobrazení chaotického grafu obsahujícího celou tabulku dat se volba tohoto interaktivního grafu s rozevíracím seznamem umožňuje uživateli přiblížit údaje o tržbách za určitý rok pouhými dvěma kliknutími.

Postupujte podle níže uvedených podrobných kroků a zjistěte, jak můžete udělat totéž.

Krok č. 1: Položte základy.

Než začneme, přejděte na Vývojář karta, která obsahuje některé pokročilé funkce aplikace Excel, se kterými se budeme dále ubírat. Karta je ve výchozím nastavení skrytá, ale přidání na ni vyžaduje pouze několik kliknutí stuha.

Nejprve klikněte pravým tlačítkem na libovolné prázdné místo v stuha a vyberte „Přizpůsobte pás karet.”

V Možnosti aplikace Excel v dialogovém okně přejděte na Hlavní záložky seznam, zkontrolujte „Vývojář“A klikněte na„OK.”

The Vývojář karta by se nyní měla zobrazit na stuha. V tomto okamžiku vyberte libovolnou prázdnou buňku pro zobrazení výstupu z rozevírací nabídky (B8). To pomůže Excelu zobrazit správná data podle toho, která možnost je vybrána z rozevíracího seznamu.

Krok č. 2: Přidejte rozevírací seznam.

Naším dalším krokem je přidání rozevírací nabídky na list:

1. Jděte na Vývojář tab.
2. Klikněte na „Vložit.”
3. Pod „Ovládací prvky formuláře"Vyberte"Kombinovaný box.”

Nakreslete prázdné pole se seznamem kamkoli chcete. Pro ilustraci zde vypadá prázdná rozevírací nabídka:

Krok č. 3: Propojte rozevírací seznam s buňkami listu.

Aby nabídka importovala seznam let do datové tabulky, musíte jej propojit s příslušným rozsahem dat v listu. Chcete-li to provést, klikněte pravým tlačítkem na pole se seznamem a vyberte „Ovládání formátu.”

V dialogovém okně proveďte následující:

1. Pro "Vstupní rozsah, ”Zvýrazněte všechny skutečné hodnoty v datové tabulce ($ A $ 3: $ G $ 6).
2. Pro "Odkaz na buňku, ”Vyberte prázdnou buňku přiřazenou k uložení výstupu rozevírací nabídky ($ B $ 8).
3. Pro "Rozbalovací řádky, “Zadejte počet datových bodů ve vaší sadě dat (v našem případě čtyři) a klikněte na„OK.”

Tato akce by měla vést k přidání názvů datových bodů do rozevíracího seznamu, zatímco hodnota pomocné buňky (B8) charakterizuje aktuálně vybranou položku z nabídky jako možnost 1, 2, 3 nebo 4:

Krok č. 4: Nastavení tabulky dat grafu.

Vzhledem k tomu, že graf by měl ilustrovat pouze jednu datovou řadu najednou, můžete jako alternativní řešení nastavit dynamickou pomocnou tabulku, která bude procházet nezpracovaná data a bude vracet pouze údaje o tržbách za rok vybraný ze seznamu.

Tato tabulka bude nakonec použita jako zdrojová data grafu, což uživateli efektivně umožní přepínat zobrazení pomocí rozevírací nabídky.

Nejprve zkopírujte záhlaví tabulky údajů o prodeji a ponechte prázdný řádek pro zobrazení filtrovaných údajů o tržbách.

Jak ale zajistíte, aby byla data správně extrahována? Právě zde vstupuje do hry funkce INDEX. Zadejte následující vzorec do A11 a zkopírujte jej do G11:

1	= INDEX (A3: A6, $ B $ 8)

V zásadě vzorec filtruje sloupec Rok (sloupec A) a vrací pouze hodnotu v řádku, jehož číslo odpovídá aktuálně vybrané položce v rozevíracím seznamu.

Krok č. 5: Nastavte graf na základě dat dynamického grafu.

Prošli jste nejtěžší částí, takže zbývá jen vytvořit 2-D graf na základě pomocné tabulky (zvýrazněte pomocnou tabulku [A10: G11]> Vložit> vytvořte seskupený sloupcový graf).

Nakonec, pokud chcete rozbalovací nabídku umístit na začátek grafu, klikněte pravým tlačítkem na pole se seznamem a klikněte na „Objednat, “A vyberte„Přesunout dopředu. ” (Případně klikněte pravým tlačítkem na graf, klikněte na „Objednat, “A vyberte„Poslat zpět.”)

A je to! Zde máte svůj interaktivní sloupcový graf propojený s rozbalovací nabídkou.

Jak vytvořit interaktivní graf pomocí posuvníku

Je pravda, že proces přidání posuvníku do grafů aplikace Excel je podobný výše popsaným krokům.

Ale co když chcete analyzovat dynamiku prodeje každé kategorie knihy (sloupce), na rozdíl od zobrazení ročních prodejních čísel (řádků)? Pro ilustraci se podívejte na níže uvedený interaktivní spojnicový graf:

Jak to stáhnete? Tady to začíná být trochu složitější. Pokud se však budete držet níže uvedeného postupu krok za krokem, mělo by se podle pokynů cítit jako procházka v parku, i když jste úplný nováček.

Krok č. 1: Položte základy.

Než začnete, přidejte Vývojář záložka do stuha, protože obsahuje funkci, která nám umožňuje nakreslit posuvník (klikněte pravým tlačítkem na libovolné prázdné místo na pásu karet> přizpůsobte pás karet> hlavní karty> zaškrtněte políčko „Vývojář“> OK).

Aby věci fungovaly jako hodinky, přiřaďte prázdnou buňku pro zobrazení výstupu posuvníku, aby aplikace Excel mohla správně umístit posuvník v posuvníku (B8).

Krok č. 2: Nakreslete posuvník.

Naším dalším krokem je vložení posuvníku do listu:

1. Jděte na Vývojář tab.
2. Udeř "Vložit" knoflík.
3. Pod „Ovládací prvky formuláře"Vyberte"Posuvník.”

V tomto okamžiku můžete posuvník nakreslit, kamkoli chcete. Přesuneme to na místo později.

Krok č. 2: Propojte posuvník s daty listu.

Klikněte pravým tlačítkem na posuvník a vyberte „Ovládání formátu.”

V Ovládání formátu v dialogovém okně upravte nastavení posuvníku:

• Současná cena: Tato hodnota definuje výchozí polohu posuvníku v posuvníku. Nastavte hodnotu na „1.”
• Minimální hodnota: Toto nastavení znamená nejnižší hodnotu posuvníku. V našem případě zadejte „1“Do pole.
• Maximální hodnota: Tento parametr určuje nejvyšší hodnotu posuvníku a měl by odpovídat počtu datových bodů, které mají být použity pro vykreslení interaktivního grafu. Protože se datová tabulka skládá ze šesti kategorií knih, změňte hodnotu na „6.”
• Přírůstková změna: Tato hodnota určuje, jak hladce změní rolovací pole svoji polohu, když ji přesunete. Vždy nastavte hodnotu na „1.”
• Změna stránky: Tato hodnota určuje, jak hladce změní rolovací pole svoji polohu, když kliknete na oblast mezi posuvníkem a šipkami. Vždy nastavte hodnotu na „1.”
• Odkaz na buňku: Tato hodnota charakterizuje aktuální pozici posuvníku. Jednoduše zvýrazněte buňku v listu, který byl dříve přiřazen k tomuto účelu ($ B $ 8).

Krok č. 3: Vytvořte tabulku dat grafu.

Jakmile dokončíte proces instalace, vytvořte novou pomocnou tabulku pro ukládání dat dynamického grafu extrahovaných z nezpracovaných dat. Data grafu automaticky upraví hodnoty v závislosti na aktuální poloze posuvníku-efektivně propojí posuvník s grafem grafu.

Chcete -li to provést, začněte s kopírováním sloupec Rok jak je znázorněno na níže uvedeném snímku obrazovky (A2: A6).

Jakmile tam budete, nasaďte následující funkci INDEX, která vybere oblast buněk obsahující názvy kategorií knih a vrátí odpovídající hodnotu na základě polohy posuvníku.

Do buňky zadejte následující vzorec B10:

1	= INDEX ($ B $ 2: $ G $ 2, $ B $ 8)

Nyní importujte údaje o prodeji odpovídající aktuální kategorii knih zadáním tohoto vzorce do B11 a zkopírujte ho (B11: B14):

1	= INDEX ($ B $ 3: $ G $ 6, MATCH (A11, $ A $ 3: $ A $ 6,0), MATCH ($ B $ 10, $ B $ 2: $ G $ 2, 0))

Kombinace funkcí INDEX a MATCH pomáhá přesně vrátit hodnotu čísla řádku a sloupce, které odpovídá zadaným kritériím.

Vzorec nejprve profiltruje sloupec Rok ($ A $ 3: $ A $ 6) a vybere řádek, který odpovídá příslušnému roku v pomocné tabulce (A11). Potom vzorec prochází názvy kategorií knih stejným způsobem ($ B $ 2: $ G $ 2). Výsledkem je, že vzorec vrátí správnou dynamickou hodnotu, která se upraví při každém přesunutí posuvníku.

Krok č. 4: Vytvořte graf na základě pomocné tabulky.

Jakmile je tabulka dat grafu sestavena, stačí sestavit jednoduchý graf a zobrazit posuvník v akci.

Zvýrazněte celý pomocný stůl (A10: B14), přejděte na Vložit kartu a vykreslete libovolný 2-D graf-například spojnicový graf (Vložte čárový nebo plošný graf> Čára se značkami).

POZNÁMKA: Pokud chcete, aby se posuvník zobrazoval v horní části grafu, klikněte pravým tlačítkem na posuvník a vyberte „Objednat, “A vyberte„Předložit. ” (Případně klikněte pravým tlačítkem na graf grafu a klikněte na „Objednat, “A vyberte„Poslat zpět.”)

A takhle to děláš. Právě jste se dozvěděli vše, co potřebujete vědět o výkonných pokročilých technikách, které vaši hru s vizualizací dat posunou mílovými kroky.