Ako vytvoriť interaktívne grafy s dynamickými prvkami v programe Excel

Tento tutoriál vám ukáže, ako vytvárať interaktívne grafy s dynamickými prvkami vo všetkých verziách programu Excel: 2007, 2010, 2013, 2016 a 2022.

Interaktívny graf je graf, ktorý umožňuje používateľovi ovládať, ktoré rady údajov majú byť v grafe znázornené, čo umožňuje zobrazovať alebo skrývať údaje pomocou ovládacích prvkov formulára. Majú mnoho tvarov a foriem, od tlačidiel možností po posúvače a rozbaľovacie zoznamy.

Vďaka dynamizácii niektorých prvkov grafu, ako je napríklad názov grafu, je možné automatizovať proces aktualizácie grafov vždy, keď pridáte alebo odstránite položky z podkladových zdrojových údajov.

Vytváranie interaktívnych grafov s dynamickými prvkami vám teda umožní zabiť dve muchy jednou ranou.

Interaktívne grafy vám na začiatku pomôžu úhľadne zobraziť obrovské množstvo údajov iba pomocou jedného grafu:

Navyše, zmena niektorých prvkov grafu zo statických na dynamické ušetrí z dlhodobého hľadiska nespočetné množstvo hodín a zabráni vám zabudnúť aktualizovať graf vždy, keď zmeníte tabuľku údajov.

Ako už bolo povedané, tento návod je kurzom pre pokročilé techniky vytvárania grafov v programe Excel: naučíte sa vytvárať interaktívne grafy pomocou rozbaľovacieho zoznamu a posúvača, ako aj zvládnuť spôsoby, ako vytvoriť názov grafu. a rozsah údajov grafu je dynamický.

Ukážkové údaje

Aby sme vám ukázali, čo je čo, potrebujeme niekoľko prvotných údajov, s ktorými budeme pracovať. Z tohto dôvodu zvážte nasledujúcu tabuľku ilustrujúcu čísla predajov kníh fiktívneho online kníhkupectva:

Ako vytvoriť názov dynamickej grafu

Najprv sa zamerajme na jednoduchú časť: dynamizácia názvu grafu.

Názov môžete urobiť dynamickým tak, že ho prepojíte s konkrétnou bunkou v hárku, aby sa všetky zmeny hodnoty v tejto bunke automaticky prejavili v názve grafu.

Krása tejto metódy spočíva v tom, že stačí niekoľko jednoduchých krokov na zmenu názvu grafu zo statického na dynamický:

1. Dvakrát kliknite na názov grafu.
2. Kliknite na Formula Bar a napíšte „=" do toho.
3. Vyberte ľubovoľnú bunku, ktorú chcete priradiť ako názov nového grafu.

Na prvý pohľad sa táto myšlienka zdá celkom primitívna. Ale sotva sme dokonca poškriabali povrch vecí, ktoré boli možné pomocou tejto jednoduchej techniky. Ak sa chcete dozvedieť viac, pozrite sa na podrobný návod, ktorý obsahuje podrobnejšie názvy dynamických grafov v programe Excel.

Ako vytvoriť rozsah dynamických grafov

Ďalej: nastavenie rozsahu dynamického grafu. Opäť, konvenčne, zakaždým, keď pridáte alebo odstránite položky z rozsahu údajov používaného pri vytváraní grafu programu Excel, zdrojové údaje by ste mali tiež ručne upraviť.

Našťastie si môžete ušetriť starosti so všetkým tým, že vytvoríte dynamický rozsah grafov, ktorý automaticky aktualizuje graf pri každej zmene zdrojových údajov.

V zásade existujú dva spôsoby, ako to urobiť: tabuľková metóda a technika pomenovaných rozsahov. Stručne sa tu budeme venovať jednoduchšej metóde tabuľky, ale aby ste získali úplný prehľad o tom, ako využiť silu rozsahov dynamických grafov v programe Excel, pozrite sa na návod, ktorý podrobnejšie popisuje všetky vstupy a výstupy.

Každopádne, späť k metóde tabuľky. Najprv z bunky, ktorú chcete použiť na zostavenie grafu, urobte tabuľku.

1. Vyberte celý rozsah údajov (A2: G6).
2. Zamierte do Vložiť tab.
3. Kliknite na „Tabuľka”Tlačidlo.

Ak vaše údaje neobsahujú žiadne hlavičky, zrušte začiarknutie políčka „Môj stôl má hlavičky”Box.

Akonáhle sa tam dostanete, rozsah údajov sa prevedie na tabuľku.

Teraz stačí vykresliť graf a použiť tabuľku ako zdrojové údaje grafu:

1. Zvýraznite tabuľku (A2: G6).
2. Prejdite na Vložiť tab.
3. Vytvorte ľubovoľný 2-D graf, napríklad zoskupený stĺpcový graf (Vložiť stĺpcový alebo stĺpcový graf> Klastrovaný pruh).

Voilà! Všetci ste pripravený. Teraz to vyskúšajte vložením nových položiek do tabuľky, aby ste ich videli automaticky reštartované v grafe.

Ako vytvoriť interaktívny graf z rozbaľovacieho zoznamu

Teraz, keď ste urobili prvé kroky do oblasti pokročilých nástrojov na vizualizáciu údajov v programe Excel, je čas prejsť na ťažkú ​​časť: interaktívne grafy programu Excel.

Keď máte k dispozícii veľa údajov na zobrazenie grafu, zavedenie interaktívnych funkcií do grafov programu Excel-či už ide o rozbaľovacie zoznamy, začiarkavacie políčka alebo posuvníky-vám pomôže zachovať miesto na prístrojovom paneli a zbaviť sa grafu grafu, pričom bude pre používateľa pohodlnejší. analyzovať údaje.

Pozrite sa napríklad na nižšie uvedený stĺpcový graf. Namiesto zobrazenia chaotického grafu obsahujúceho celú tabuľku s údajmi sa tento interaktívny graf s rozbaľovacím zoznamom rozhodne pre používateľa, a to pomocou dvoch kliknutí.

Postupujte podľa nižšie uvedených podrobných krokov a zistite, ako môžete urobiť to isté.

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

Skôr ako začneme, otvorte Vývojár karta, ktorá obsahuje niektoré z pokročilých funkcií Excelu, na ktoré sa ešte obrátime. Karta je v predvolenom nastavení skrytá, ale pridanie na ňu trvá iba niekoľko kliknutí stuha.

Najprv kliknite pravým tlačidlom myši na akékoľvek prázdne miesto v stuha a zvoľte „Prispôsobte stuhu.”

V Možnosti programu Excel v dialógovom okne prejdite na Hlavné karty zoznam, skontrolujte „Vývojár“A kliknite na„OK.”

The Vývojár karta by sa mala teraz zobraziť na stuha. V tomto mieste vyberte ľubovoľnú prázdnu bunku na zobrazenie výstupu z rozbaľovacej ponuky (B8). To pomôže Excelu zobraziť správne údaje podľa toho, ktorá možnosť je vybratá z rozbaľovacieho zoznamu.

Krok č. 2: Pridajte rozbaľovací zoznam.

Naším ďalším krokom je pridať rozbaľovaciu ponuku do pracovného hárka:

1. Choďte do Vývojár tab.
2. Kliknite na „Vložiť.”
3. V časti „Ovládacie prvky formulára, “Vyberte„Kombinovaný box.”

Nakreslite toto prázdne kombinované pole kdekoľvek chcete. Na ilustráciu uvádzame, ako vyzerá prázdna rozbaľovacia ponuka:

Krok č. 3: Prepojte rozbaľovací zoznam s bunkami pracovného hárka.

Aby ponuka importovala zoznam rokov do tabuľky s údajmi, musíte ho prepojiť s príslušným rozsahom údajov v pracovnom hárku. Ak to chcete urobiť, kliknite pravým tlačidlom myši na kombinované pole a zvoľte „Ovládanie formátu.”

V dialógovom okne postupujte takto:

1. Pre “Vstupný rozsah, ”Zvýraznite všetky skutočné hodnoty v tabuľke údajov ($ A $ 3: $ G $ 6).
2. Pre “Bunkový odkaz„” Vyberte prázdnu bunku priradenú na uloženie výstupu rozbaľovacej ponuky ($ B $ 8).
3. Pre “Rozbaľovacie riadky, “Zadajte počet dátových bodov vo svojom súbore údajov (v našom prípade štyri) a kliknite na„OK.”

Táto akcia by mala viesť k tomu, že názvy údajových bodov budú pridané do rozbaľovacieho zoznamu, zatiaľ čo hodnota pomocnej bunky (B8) charakterizuje aktuálne zvolenú položku z ponuky ako možnosť 1, 2, 3 alebo 4:

Krok č. 4: Nastavte tabuľku údajov grafu.

Pretože graf by mal súčasne znázorňovať iba jednu sériu údajov, ako alternatívne riešenie vytvorte dynamickú pomocnú tabuľku, ktorá bude prehľadávať nespracované údaje a vracať iba údaje o tržbách za rok vybratý zo zoznamu.

Táto tabuľka sa nakoniec použije ako zdrojové údaje grafu, čo efektívne umožní používateľovi prepínať zobrazenia pomocou rozbaľovacej ponuky.

Najprv skopírujte hlavičku tabuľky s údajmi o predaji a nechajte prázdny riadok na zobrazenie filtrovaných údajov o tržbách.

Ako však zaistíte správne extrahovanie údajov? Práve tu vstupuje do hry funkcia INDEX. Zadajte nasledujúci vzorec do A11 a skopírujte ho do G11:

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

V zásade vzorec filtruje stĺpcom Rok (stĺpec A) a vráti iba hodnotu v riadku, ktorého číslo zodpovedá aktuálne vybranej položke v rozbaľovacom zozname.

Krok č. 5: Nastavte graf na základe údajov dynamického grafu.

Prešli ste najťažšou časťou, takže zostáva len vytvoriť 2-D graf na základe pomocnej tabuľky (zvýraznite pomocnú tabuľku [A10: G11]> Vložiť> vytvorte zoskupený stĺpcový graf).

Nakoniec, ak chcete, aby bola rozbaľovacia ponuka umiestnená v hornej časti grafu, kliknite pravým tlačidlom myši na pole so zoznamom, kliknite na položku „objednať, “A zvoľte„Preniesť dopredu. ” (Prípadne kliknite pravým tlačidlom myši na graf, kliknite na „objednať, “A zvoľte„Odoslať späť.”)

To je všetko! Tu máte svoj interaktívny stĺpcový graf prepojený s rozbaľovacou ponukou.

Ako vytvoriť interaktívny graf pomocou posúvača

Po pravde povedané, proces pridávania posúvača do grafov programu Excel je podobný postupu uvedenému vyššie.

Ale čo keď chcete analyzovať dynamiku predaja každej kategórie knihy (stĺpce), na rozdiel od zobrazovania ročných tržieb (riadkov)? Na ilustráciu sa pozrite na nižšie uvedený interaktívny čiarový graf, ktorý robí iba to:

Ako to stiahnete? Tu sa veci začínajú trochu komplikovať. Ak sa však budete držať nižšie uvedeného postupu krok za krokom, dodržiavanie pokynov by vám malo pripadať ako prechádzka parkom, aj keď ste úplný nováčik.

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

Skôr ako začnete, pridajte príponu Vývojár záložka do stužka, pretože obsahuje funkciu, ktorá nám umožňuje nakresliť posúvač (kliknite pravým tlačidlom myši na ľubovoľné prázdne miesto na páse s nástrojmi> prispôsobte pás s nástrojmi> hlavné karty> začiarknite políčko „Vývojár“> OK).

Aby veci fungovali ako hodinky, priraďte prázdnu bunku na zobrazenie výstupu posúvača, aby Excel mohol správne umiestniť posúvač do posúvača (B8).

Krok č. 2: Nakreslite posúvač.

Naším ďalším krokom je vloženie posúvača do pracovného hárka:

1. Choďte do Vývojár tab.
2. Kliknite na „Vložiť”Tlačidlo.
3. V časti „Ovládacie prvky formulára, “Vyberte„Posuvník.”

V tomto mieste môžete posúvač nakresliť kdekoľvek chcete. Neskôr ho presunieme na miesto.

Krok č. 2: Posuňte posúvač k údajom pracovného hárka.

Pravým tlačidlom myši kliknite na posúvač a zvoľte „Ovládanie formátu.”

V Ovládanie formátu v dialógovom okne upravte nastavenia posúvača:

• Súčasná hodnota: Táto hodnota definuje predvolenú polohu posúvača v posuvníku. Nastavte hodnotu na „1.”
• Minimálna hodnota: Toto nastavenie znamená najnižšiu hodnotu posúvača. V našom prípade zadajte „1“Do poľa.
• Maximálna hodnota: Tento parameter určuje najvyššiu hodnotu posúvača a mal by zodpovedať počtu dátových bodov, ktoré sa majú použiť na vykreslenie interaktívneho grafu. Pretože tabuľka údajov obsahuje šesť kategórií kníh, zmeňte hodnotu na „6.”
• Prírastková zmena: Táto hodnota určuje, ako plynulo posúvacie pole zmení svoju polohu, keď ho presuniete. Vždy nastavte hodnotu na „1.”
• Zmena stránky: Táto hodnota určuje, ako plynulo zmení posúvač svoje umiestnenie, keď kliknete na oblasť medzi posúvačom a šípkami. Vždy nastavte hodnotu na „1.”
• Odkaz na bunku: Táto hodnota charakterizuje aktuálnu polohu posúvača. Jednoducho zvýraznite bunku v pracovnom hárku, ktorý bol predtým priradený na tento účel ($ B $ 8).

Krok č. 3: Vytvorte tabuľku s údajmi grafu.

Po dokončení procesu nastavenia vytvorte novú pomocnú tabuľku na ukladanie údajov dynamického grafu extrahovaných z nespracovaných údajov. Údaje grafu automaticky upravia hodnoty v závislosti od aktuálnej polohy posúvača-efektívne prepojí posúvač s diagramom grafu.

Ak to chcete urobiť, začnite kopírovaním stĺpček Rok ako je znázornené na obrázku nižšie (A2: A6).

Akonáhle ste tam, nasaďte nasledujúcu funkciu INDEX, ktorá vyberie rozsah buniek obsahujúci názvy kategórií kníh a vráti zodpovedajúcu hodnotu na základe polohy posúvača.

Do bunky zadajte nasledujúci vzorec B10:

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

Teraz importujte údaje o predaji zodpovedajúce aktuálnej kategórii kníh zadaním tohto vzorca do B11 a skopíruj to (B11: B14):

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

Kombinácia funkcií INDEX a MATCH pomáha presne vrátiť hodnotu, ktorej číslo riadka a stĺpca zodpovedá zadaným kritériám.

Vzorec najskôr filtruje stĺpček Rok ($ A $ 3: $ A $ 6) a v tabuľke pomocníkov vyberie riadok, ktorý zodpovedá príslušnému roku (A11). Potom vzorec rovnakým spôsobom preoseje názvy kategórií kníh ($ B $ 2: $ G $ 2). Výsledkom je, že vzorec vráti správnu dynamickú hodnotu, ktorá sa upraví pri každom posúvaní posúvača.

Krok č. 4: Vytvorte graf na základe pomocnej tabuľky.

Keď je tabuľka údajov grafu zostavená, zostavte jednoduchý graf, aby ste videli posuvník v prevádzke.

Zvýraznite celý pomocný stôl (A10: B14), prejdite na Vložiť kartu a nakreslite ľubovoľný 2-D graf-napríklad čiarový graf (Vložiť čiarový alebo plošný graf> Čiara so značkami).

POZNÁMKA: Ak chcete, aby sa posuvník zobrazoval v hornej časti grafu, kliknite naň pravým tlačidlom myši a zvoľte „objednať, “A vyberte„Predložiť. ” (Prípadne kliknite pravým tlačidlom myši na graf, kliknite na „objednať, “A zvoľte„Odoslať späť.”)

A takto to robíte. Práve ste sa dozvedeli všetko, čo potrebujete vedieť o výkonných pokročilých technikách, ktoré vašu hru s vizualizáciou dát skáču míľovými krokmi.