---

Flexibilní tabulky

Síla Excelu spočívá v kombinaci několika nástrojů programu. V této kapitole se naučíme zkombinovat filtry a dynamicky pojmenované oblasti. Zároveň se naučíme také ověřovat data vkládaná do tabulky. Tím se vyhneme zbytečným překlepů, které dělají při použití filtrů velké problémy. Tuto kombinaci v následujíci kapitole rozšíříme o analýzu dat pomocí kontigenční tabulky. Cílem kapitoly je vás naučit, že použitím několika nástrojů si velmi usnadníme práci při úpravách tabulky a hlavně při jejím zpracování.

Pomocí dynamicky pojmenované oblasti si můžeme doslova vytvořit flexibilní tabulku. Flexibilní např. z pohledu úpravy vstupních dat do tabulky. Dynamicky pojmenovaná tabulka (oblast dat) se automaticky rozšiřuje o nově zadaná data. Tím si po každém zadání nových dat ušetříme práci s novým definováním oblastí tabulky. To oceníme v další kapitole, ve které si data z dynamicky pojmenované oblasti budeme zpracovávat v kontingenční tabulce.

Dynamicky pojmenovaná oblast

Přínos dynamicky pojmenované oblasti si ukážeme na názorném příkladu. Tím nejlépe pochopíte úsporu práce, kterou přináší dynamicky pojmenované oblasti. 

Zatím si vysvětlíme hlavní princip dynamicky pojmenované oblasti. Buňkám, řádkům, sloupcům nebo jakémukoliv výběru buněk můžeme dát jméno. Jakmile si je pojmenujeme, tak s tímto jménem můžeme začít pracovat. Například ho vkládat do vzorců místo adresy buněk, oblastí atd. To je velmi užitečná možnost Excelu. Ovšem co když víme, že se naše pojmenovaná oblast bude rozšiřovat? Uživatelé do tabulky budou stále vkládat nová data. Přece nebudeme po každém rozšíření pojmenované oblasti (vložením nových dat) upravovat její rozsah. Toho docílíme právě použitím dynamicky pojmenované oblasti. Tu jednou nastavíme a pak už jenom stačí do ní vkládat nová data. A tato nová data se automaticky zahrnují do oblasti. Lépe řečeno se dynamicky pojmenovaná oblast automaticky aktualizuje o nově zadaná data.

Stejně jako v předešlé kapitole i v této kapitole se zahrajeme na vedoucího pojišťovacích poradců. Pamatujete si na tabulku z předchozí kapitoly? V této kapitole si ji vylepšíme o dynamicky pojmenovanou oblast. Otevřete si nový sešit a podle níže uvedených ukázek vyplňujte jednotlivé listy. Na konci kapitoly si sešit uložte. V dalších kapitolách budeme se sešitem pracovat.

Zase povedeme skupinu pojišťováků. Budeme si evidovat jejich uzavřené smlouvy i s jejich cenou. Ovšem teď si v tabulce budeme zobrazovat přesné názvy jednotlivých druhů pojištění. Zároveň očekáváme, že budou vznikat nové druhy pojištění. Tedy že se postupem času bude zvyšovat počet druhů pojištění. Vytvořte si v sešitě list s názvem druh. Podle níže uvedené ukázky si vytvořte tabulku na listu s názvem druh:

Tabulku s druhy pojištění si dynamicky pojmenujeme. Zvolíme nabídku Vložit. V nabídce Vložit klikneme na příkaz Název a zde vybereme příkaz Definovat:

Zobrazí se nám okno s názvem Definovat název. Okno si vyplňte podle níže uvedené ukázky:

Tím jsme si oblast dynamicky pojmenovali jménem druh. Pro vytvoření dynamicky pojmenované oblasti se používá funkce POČET a POČET2. Zároveň jsme ve vzorci použili vyhledávací funkci POSUN.

Popíšeme si zápis vzorce. Podívejme se, co je napsáno v závorkách u funkce POSUN: 

1. Zadali jsme název listu..."druh".
2. Zadali jsme počáteční buňku, tedy buňku, u které posun začíná.. $A$2.
3. Za středníkem jsme zadali nulu... ";0". Právě o tolik řádků se posune levá horní buňka nového odkazu.
4. Za středníkem máme druhou nulu... ";0". Právě o tolik sloupců se posune levá horní buňka nového odkazu.

Obdobný zápis má i funkce POČET2:

1. Zadali jsme název listu..."druh".
2. Pak zadáme počáteční a koncovou buňku oblasti... $A:$A. V našem případě jsme si zadali neomezenou velikost budoucí dynamicky pojmenované oblasti. Pokud chceme zadat pevný počet buněk, například jen 50, tak přepíšeme vzorec do tvaru $A$:$A$50.
3. Za závorkou ve vzorci následuje -1. Právě o jeden řádek dolů se připíše levá horní buňka.
4. A za středníkem následuje 1. To znamená, že se nám připíše jeden sloupec. Pokud bychom měli sloupce tři, tak tam napíšeme číslici 3.

Ověřování vkládaných dat do tabulky

Pomocnou tabulku máme hotovou. Také máme dynamicky pojmenovanou oblast se jménem druh. Teď si připravíme hlavní tabulku. Vytvořte si nový list s názvem tabulka. Na listě tabulka si připravte podle níže uvedeného vzoru tabulku s filtry:

Základ tabulky máme hotový a teď se naučíme v tabulce ověřovat vkládaná data. Nejjednodušeji se data ověřují pomocí seznamu. My si takový seznam připravíme ve sloupci Pojišťovák. Nejdříve si označíme celý sloupec B:

U každého ověřování je důležité označit celý sloupec. Děláme to proto, aby se ověřování týkalo celého sloupce a ne jen několika buněk ve sloupci. Po označení sloupce si otevřeme nabídku Data a klikneme na příkaz Ověření:

Tím se nám otevře okno Ověření dat. V okně Ověření dat si nastavíme Ověřovací kritéria na Seznam:

Teď už zbývá zadat jména pojišťováků do našeho seznamu který se bude zobrazovat v každé buňce ve sloupci B. Jména pojišťováků necháme stejná jako v minulém příkladě (Petříček, Novák, Brumbál, Mastný). Tato jména zapíšeme do kolonky zdroj a oddělíme je středníkem:

Tímto postupem jsme si udělali u každé buňky ve sloupci B rozevírací seznam se jmény pojišťováků:

Díky rozevíracímu seznamu jména už nemusíme vypisovat. Stačí jen na jméno v rozbalovacím seznamu kliknout a ono se bez psaní do buňky samo vloží. Tím zamezíme možným překlepům. Překlepy jsou ve filtrech velmi nebezpečné. Překlepem ve filtru nechtěně vytvoříme novou položku filtru - a toho se právě tímto seznamem vyvarujeme.

Stejným způsobem si vytvoříme seznam pro Druh pojištění ve sloupci C na listu tabulka. Jen ve zdroji dat napíšeme rovná se a název dynamicky pojmenované oblasti:

Propojený seznam druhů pojištění v hlavní tabulce vypadá takto:

Pro lepší přehlednost můžeme seznam doplnit i popiskem. Popisek je vhodné k seznamu přidat v případech, kdy s tabulkou budou pracovat i jiní lidé. Věřte, usnadní jim to práci. Na ukázku si popisek přidáme do sloupce B se jménem Pojišťovák.

Postup je stále stejný. Označíme si celý sloupec B. Zvolíme nabídku Data a pak klikneme na příkaz Ověření. Zobrazí se nám okno Ověření dat. V tomto okně si překlikneme na záložku Zpráva při zadávání a údaje vyplňte podle níže uvedeného vzoru:

Jakmile klikneme do jakékoliv buňky ve sloupci B, tak se zobrazí následující popisek:

V následující kapitole si tabulku vyplníme daty. Zároveň si i ukážeme, že můžeme do dynamicky pojmenované oblasti jenom připosovat nové názvy pojištění a tyto názvy se nám automaticky zobrazí v seznamu na listu tabulky.

Více informací k Excelu

© Fotis Fotopulos, 2010
Obrázky © CopyRights Fotis Fotopulos, 2010

Všechna práva vyhrazena. Žádná část této publikace se nesmí reprodukovat, ukládat do informačních systémů či rozšiřovat jakýmkoliv způsobem, ať již polygraficky, fotografickou reprodukcí, elektronicky, audio a video cestou, ani jinými prostředky bez písemného souhlasu majitele práv.