Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce » dynamicky-definovana-oblast-pro-graf

Dynamická oblast pro graf - Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Využití definovaných názvů pro tvorbu dynamických grafu., aneb automatická změna grafu na základě výběru.

Aktualizováno: 10.6.2014

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.

MS Excel 2010 - dynamicky definovaná oblast

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:

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

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:

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á.

MS Excel 2010 - dynamicky definovaná oblast

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á.

MS Excel 2010 - dynamicky definovaná oblast

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 soubor ve formátu *.xls 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: 01.11.2014 09:33

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2000 (od 2004 ne této doméně) - Roku 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 také na on-line videích pro SEDUO. Školím a konzultuji, učím na MUNI. Hlavně tvořím tento web. Je zde k dispozici přes 1.000 návodu, tipů a triků včetně stovek různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře


Petr

Přidáno: 11.12.12 08:27

"Poznámka: Vím, že pomocí VBA (maker) zle tohoto dosáhnout také..." Slůvko "zle" je předpokládám překlep (lze)

Pavel

Přidáno: 04.05.13 23:10

Díky moc za tento návod. Trochu mne zmátla část "Praktické zjištění", kterou pro sestavení dynamického grafu není nutné použít. Stejně tak je nadbytečným krokem ověření dat (byť teoreticky smysl má). Dlouho se mi nedařilo z vytvořené dynamické oblasti sestavit ten graf. Naučil jsem se, že je třeba ve vzorci pro hodnoty řad po názvu listu zmáčknout F3, aby se mi zobrazila nabídka definovaných názvů.

Pavel Lasák

Přidáno: 05.05.13 18:25

To Pavel: Děkuji za postřehy. U článku předpokládám mírně pokročilé znalosti, postupně ještě článek upravím ať je pochopistelnější a jasnější.

Jakub

Přidáno: 06.05.14 08:37

Dobrý den, nemohu přijít na to, jak z dynamických oblastí vytvořit graf. Nevím kam do grafu vložit odkazy na oblast. Prosím o radu. Děkuji.

Pavel Lasák

Přidáno: 06.05.14 18:15

To Jakub: Do řady v grafu vložit jméno příslušné dynamické oblasti.

Jakub

Přidáno: 06.05.14 22:12

Děkuji za rychlou odpověď. Ale stále se v tom plácám. Mám načtenou tabulku tří teplot, které byly zaznamenávány každou minutu (přibližně 1400 záznamů). Tyto teploty chci zobrazit v grafu se dvěma osami, ale rád bych zadal časový rozsah (např. od 14:00 do 15:00).

Radek

Přidáno: 13.08.14 06:48

Dobrý den, chtěl jsem se zeptat. Měl jsem zobrazený graf pro jednu hodnotu a dobře se to načítalo, ale když jsem změnil velikost vstupní oblasti, tak najednou vzorec přestal fungovat a píše mi to, že je v něm chyba. Nevíte, čím by to mohlo být? Díky.

Pavel Lasák

Přidáno: 20.07.15 08:20

To Radek: Zkontrolovat zadaný vzorec. Obsahuje tabulky čísla v oblasti, kde se počítá velikost této oblasti? V článku josu uvedeny odkazy na funkce, které jsou ve vzorcích použity.

Mirek

Přidáno: 05.10.15 09:52

Dobrý den, chtěl bych se zeptat na dynamiku spojnicových grafů. Vyvíjím kalkulátor, který má spojnicový graf zobrazit pro měnící se počet datových řad (jednou dvě, jindy šest). Potřeboval bych zdynamizovat oblast grafu. Zkoušel jsem to pomocí pojmenovaných oblastí. Chovalo se to tak, že graf se napoprvé vytvořil nad správnou oblastí, ale po změně rozsahu oblasti dat se tomu již nepřizpůsobil. Myslím, že je to tím, že graf si místo uchování si odkazu na dynamickou oblast jednorázově staticky namapoval do oblasti dat pro graf konkrétní rozsah buněk a ztratil tak svoji dynamiku. Neumíte náhodou toto obejít a plně to zdynamizovat? Děkuju za radu.

Mirek

Přidáno: 05.10.15 10:41

Tak už jsem to téměř vyřešil pomocí hodnot nedef() do příslušné datové oblasti. Jak ale dotáhnout dynamizaci legendy? Pokud v popisku dat je "", tak se příslušná značka (bez popisu) stále zobrazuje. Nevíte, jak ji automaticky odstranit (bez použití VBA)?

Jakub

Přidáno: 20.01.16 07:06

Dobrý den, snažím se vytvořit podobný typ dynamického grafu. S návodem se mi podařilo generovat graf viz. výše. Trochu jsem u něj zavařil mozek, ale nakonec to funguje. Pro mé účely potřebuji ale vytvořit graf s prohozenými osami. Ve sloupcích budou kalendářní týdny a v řádcích různé typy vad (to znamená sloupcový skládaný graf). Nedaří se mi vytvořit ten graf s převrácenými osami, můžete poradit? Děkuji

Jakub

Přidáno: 20.01.16 15:03

Tak nakonec jsem se k tomu dobral. = v indexu vyměnit řádky za sloupce + začínat ve výběru pro data od jiné buňky. Teď mě trápí další věc. K mým účelům potřebuji, abych měl dynamickou část jak pro sloupce tak pro řádky. Oblast dokážu označit, tato oblast však nechce spolupracovat s grafem. Do grafu musím zadávat osy postupně, a jen ty které jsou definované...takže to postrádá dynamiku. Nevíte co s tím?? Díky

Andrea

Přidáno: 01.03.16 09:14

Dobrý den, jde prosím něco podobné udělat i u kontingenčních grafů? Děkuji moc za odpověď.

Igor

Přidáno: 26.04.16 19:45

Dobry den, dlhsie sa trapim s problemom dynamickych dat. V jednoduchosti rozpisem moju zakladnu ulohu: a) Mam nasledujucu tabulku v Zosit_1: A B 1 Nazov Cislo 2 W 100 3 X 200 4 Y 300 b) Chcem vytvorit take makro, ktore mi vytvori v Zosit_2 takyto obsah: A B C 1 Nazov Cislo 1. cislica B 2 W 100 1 3 X 200 2 4 Y 300 3 Toto viem naucit makro jednoducho tak, ze skopirujem stlpec A zo Zosit_1 do Zosit_2, funkciou VLOOKUP() vyplnim v nom stlpec B a pomocou funkcioe LEFT() stlpec C. Problem, ktory potrebujem vyriesit je, ze makro si pamata tieto operacie len pre staticku tabulku v Zosit_1. Ako docielim dynamicku pracu makra? V Zosit_1 mi pribudne Riadok 5: A B 1 Nazov Cislo 2 W 100 3 X 200 4 Y 300 5 Z 400 a makro zareaguje na toto doplnenie tak, ze Zosit_2 bude vyzerat: A B C 1 Nazov Cislo 1. cislica B 2 W 100 1 3 X 200 2 4 Y 300 3 5 Z 400 4 Budem Vam velmi vdacny za pomoc, Igor.

Igor

Přidáno: 26.04.16 20:13

Ono to trochu rozhadzalo. Neviem, ci sa to da pochopit. V skratke - ako vytvorit makro, ktore bude reagovat dynamicky na zmenu dat v Zosit_1 a dynamicky podla toho upravi Zosit_2. Dakujem, Igor.

Tomio

Přidáno: 19.06.16 16:33

Dobrý den. Popsaný postup mi pomohl, ale objevil se problém, že mi nevyhovuje definovaný číslem ani automatický rozsah na svislé ose. Lze tyto hodnoty max/min načítat z buňky? V té buňce bych si požadovanou hodnotu měnil třeba prvkem posuvník. Zkoušel jsem do parametrů osy zadat definovaný název a nechce to vzít. Tak nevím jestli zadávam špatnou syntaxi =osa_X nebo to jde nějak jinou cestou?

Tomio

Přidáno: 19.06.16 21:00

Oprava: =osa_Y

Dave Severýn

Přidáno: 08.09.16 08:29

Zdravím Vás, vaše stránky sleduji už více jak 6 roků. Byly pro mě cennými informacemi. Ale dnes jsem narazil na jednu nepochopitelnou "věc". Při použití vzorce pro vytvoření dynamicky vytvořenou oblast mi to nechce fungovat. Ve Vašich sešitech je to zázračně funkční?! Nechápu a nerozumím. Potom mě napadla jedna úvaha, že nerozumím tomu, proč se používá ve vzorci funkce POČET, která dle nápovědy MS 2007 říká, že vrátí počet buněk obsahující ČÍSLA. Ale Váš i můj seznam má první sloupec textové řetězce?! Pokud fce POČET použiji přímo v buňce, tak mi vrátí pochopitelně chybu, ale Vám to pro definici oblasti funguje :-o . A k mému úžasu bylo, vyměnit fce POČET za POČET2, která říká, že vrátí počet buněk, které nejsou prázdné. A potom to vše začalo fungovat :) Nyní jsem zmatený a nerozumím myšlence těchto dvou funkcí a jejich využití pro správce názvů a nebo pro využití přímo v buňce (Excelu 2007). Můžu Vás požádat o vysvětlení? Předem děkuji :)

Dave Severýn

Přidáno: 08.09.16 13:27

Ostuda :( Už mě to trklo :-)






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2017 | 27117

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.