Tweet

Dynamická oblast pro graf - Excel

Cílem tohoto článku

Cílem je na praktické ukázce vysvětlit, jak využít dynamicky definované oblasti (pojmenované oblasti) pro tvorbu dynamických grafů (budou se automaticky měnit). V ukázce vytvoříme definovanou oblast, která bude mít volitelné omezení (zdola a počet), data budou postupně přibývat (takže musíme vytvořit i dynamickou celou oblast). Pomocí ovládacích prvků vyberete část tabulky, kterou chcete zobrazit v grafu. Graf se poté automaticky přepočítá (na základě vámi zvolených požadavků).

Ukázka co vytvoříme

Z ukázkové tabulky s měsíčními výnosy zobrazíte v grafu jen potřebnou oblast dat. Požadovanou oblast omezíme v ovládacím poli se seznamem (označeno žlutým podbarvením). Volit lze:

• počáteční měsíc
• počet zobrazených měsíců

Viz ukázka.

Znalosti potřebné k řešení

V tomto článku předpokládám základní znalosti při tvorbě definované oblasti (pojmenované oblasti), jak jí vložit do vzorce (klávesová zkratka F3), jsem sepsal v článku:

• Definovaný název - danamická oblast

Dále předpokládám znalost z tvorby grafů, které jsem sepsal v článku:

• Tvorba grafu úvod

A v neposlední řadě využítí Ověření dat a funkci INDEX

Poznámky k řešení

Poznámka: Další možnosti jak tento úkol vyřešit je VBA (makra), ale cílem článku je ukázat, jak automaticky vytvořit graf bez znalosti maker. Další možnosti je vytvoření speciální tabulky jen pro graf, kde se data budou pomocí vybraných požadavků kopírovat pomocí funkcí.

Ke stažení: Na konci článku je odkaz na sešit s řešením, který je ke stažení zdarma.

Jak se bude řešit

Řešení je rozděleno na tyto části:

• Zdrojová data
• Ověření dat
• Tvorba dynamické oblasti - celá tabulka
• Oblast pro graf
• Tvorba grafu
• Vylepšení grafu
• Ke stažení - ukázkový sešit

Zpracovávána data

K dispozici máme tabulku, kde v řádku je příslušný měsíc a k němu data nákup a prodej (případně jiné data). Na tomto příkladu předvedu jak vytvořit graf s dynamicky definovanou oblasti, uživatel jen zvolí měsíc a počet měsíců které chce v dynamickém grafu zobrazit.

Ukázka požadavku

Z rozbalovacího seznamu vyberete požadovaný měsíc a počet měsíců, které se od tohoto měsíce dále mají zobrazit. To je vše, graf se ihned přepočítá.

Ověření dat

Pro výběr požadavané oblasti využijeme ověření dat. Případně můžete využít i ovládací prvky.

Tvorba dynamická oblasti - počáteční datum

Nejprve vytvoříte dynamickou oblast ze sloupce Datum, oblast pojmenujete definovaným názvem Datumy_seznam a pro tuto oblast vytvoříte kód. Tento bude použít pro ověření dat.

=POSUN(List1!$B$7; 0; 0; POČET(List1!$B:$B); 1)

Protože jak sjem psal v úvodu data přibívají je použita funkce POČET, která zjistí poslední buňku v daném sloupci.

První ověření dat

Prvnímu ověření dat, přiřadíte oblast s definovaným názvem Datumy_seznam vložíte do Ověření dat na kartě Data

Druhé Ověření dat

Přiřadíte počet zobrazených měsíců. Například čísly odděleny středníky tj. 1; 3; 6; 9; 12; 24.

Tvorba dynamické oblasti - celá tabulka

Tato oblast bude potřeba pro další práci (budeme z ní vybírat).

=POSUN(List1!$B$7; 0; 0; POČET(List1!$B:$B); POČET(List1!$7:$7))

Oblast pro graf

Oblast pro X-ovou osu

Umíte vytvořit celou dynamickou oblast pro data, které máte aktuálně k dispozici, teď z ní uděláte požadovanou (výběrovou) oblast. Vytvoříte druhé ověření dat pod názvem PocetMesicu. a tyto pojmenované oblasti použijete při tvorbě nové oblasti pod názvem DatumOblast z které pak vytvoříte graf (datum bude na x-ové ose). Takže kód je sice trochu složitější, ale pokud znáte funkce, není problém jej pochopit.

INDEX zajistí oblast: INDEX(prvni buňka):INDEX(poslední buňka)

=INDEX(ZdrojovaData;POZVYHLEDAT(VybraneDatum;Datumy_seznam)-PocetMesicu+1;1):INDEX(ZdrojovaData;POZVYHLEDAT(VybraneDatum;Datumy_seznam);1)

Oblast pro Y-ovou osu

Podobně pro y-ovou osu pod názvem Data1Oblast.

=INDEX(ZdrojovaData;POZVYHLEDAT(VybraneDatum;Datumy_seznam)-PocetMesicu+1;2):INDEX(ZdrojovaData;POZVYHLEDAT(VybraneDatum;Datumy_seznam);2)

Poznámka: Podobně pro druhou y-ovou osu pod názvem Data2Oblast. To již nechám na Vás :)

Tvorba grafu

Teď jen zbývá vytvořit graf, pro x osu =dynamickaOblastPriklady.xls!DatumOblast a Y-osu=dynamickaOblastPriklady.xls!Data1Oblast. To jen pro připomenutí jak se vkládají do grafu odkazy na oblast. A máte hotovo.

Z rozbalovacího seznamu vyberete požadovaný měsíc a počet měsíců. To je vše graf se ihned přepočítá.

Další vylepšení grafu

Další možná vylepšení dynamického grafu. Například pro kontrolu zadávání hodnot lze využít Ověření dat. Kontrolovat ať se oblast nedostane přes požadované hranice, měnit barvy datových řad, nastavit tabulku jako Tabulka, atd.

Ke stažení

Soubor Dynamicky definovaná oblast pro tvorbu grafu ke stažení zdarma. Lepší než slova je praktická ukázka ve které prezentované funguje prakticky.

Závěrem

Využívate dynamické oblasti v grafech? Případně proč dynamické oblasti nevyužívate? Můžete se zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 10:57

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře