---

Sloučení sešitů

V předchozí kapitole jsme si vytvořili hlavní tabulku, ve které evidujeme obchodní výsledky jednotlivých pojišťováků. Zpracování dat tabulky jsme si ulehčili tím, že jsme si ji dynamicky pojmenovali. Díky tomu nemusíme pokaždé, kdy chceme data zpracovat v kontingenční tabulce, zadávat novou oblast dat. To nám dělá automaticky pojmenovaná oblast. Ovšem tím profesionální zpracovaní tabulek nekončí. Je potřeba se naučit ještě jednu dovednost.

Musíme vyřešit, jak efektivně naplníme hlavní tabulku daty. Efektivně znamená, že data si budou uživatelé do tabulky sami zapisovat a my si je do své hlavní tabulky automaticky načteme. V našem případě jednoliví pojišťováci budou mít svůj vlastní sešit, do kterého si budou zadávat obchodní výsledky, a my si jenom tato data budeme z jejich tabulek načítat. Pak už bude vyhodnocení sebevětšího objemu dat opravdu hračkou.

Zadávání dat do tabulky

Nejvíce práce zabere zadávání dat do tabluky. Naproti tomu zpracování dat už je hračkou - prakticky veškerou práci za nás udělá Excel. Proto si v této kapitole naučíme profesionálně zadávat data do tabulky.

Vzpomeňte si, kolik dalo práce zadat data do naší hlavní tabulku. Z toho vyplývá jedno poučení. V takové tabulce se data ručně nezadávají. Dalším řešením může být kopírování dat z jednoho sešitu do druhého. To znamená sešit otevřít, vybrat oblast buněk, stisknout CTRL C, otevřít si sešit s hlavní tabulkou, označit si správnou buňku, vložit data klávesovou zkratkou CTRL V, otevřít další sešit a tímto způsobem pořád dokola. Nezdá se to, ale je to neuvěřitelné množství práce. Vemte si, že takových pojišťovacích poradců budete mít sto. Proto i tento způsob je pracný, komplikovaný a časově náročný. Za další, člověk je omylný a může udělat chybu,... stačí se jednou přehlédnout. Věřte tomu, že při takovémto stylu práce hledat chybu je nadlidský výkon. Většinou ani takto vzniklou chybu nenajdete.

Z předchozího odstavce je jasné jedno. Celý proces zadávání dat do hlavní tabulky musí probíhat úplně automaticky. K tomu nám pomůže komponenta MS Query.

MS Query

V tomto kurzu se naučíme tuto komponentu používat pouze k jediné věci. Použijeme ji k automatickému načítání dat z jiných sešitů. Tyto sešity můžou být třeba umístněné ve sdílených složkách (aby s k nim dostali další uživatelé).

Dříve než se pustíme do řešení praktického příkladu, tak si vysvětlíme hlavní princip efektivní práce s tabulkou:

1. Data se zadávají do Excelu (nebo do jakékoliv databáze) jen jednou.
2. Data zadávají jednotliví uživatelé - je těžko proveditelné, aby data zadával jeden člověk (je důležité delegovat odpovědnost za zadávání dat).
3. Data musí být přístupná tomu, kdo je bude zpracovávat. Musí být tedy uložena na databázovém serveru nebo ve sdílených složkách.

My jsme teď v pozici člověka, který data zpracovává. Bude jenom na nás, jakým způsobem navrhneme sběr, uložení a zpracování dat. Většinu jsme se už naučili v předchozích kapitolách. Jen k těmto získaným znalostem přidáme MS Query a všechno spojíme do jednoho fungujícího celku. Budeme pokračovat v našem příkladu a zase si zahrajeme na vedoucího obchodní skupiny v pojišťovně.

Příprava hlavní tabulky

První část příkladu berte jako procvičení předchozích kapitol kurzu.

Jsme vedoucí obchodní skupiny. Máme za úkol vést pojišťovací poradce Petříčka, Nováka, Brumbála a Mastného. Naším úkolem je sledovat jejich obchodní činnost. U každého z poradců budeme sledovat cenu prodané pojistky a druh pojistky. Pro jednoduchost máme tři druhy pojištění s názvy Autopojištění, Bytpojištění a Životní pojištění.

Vytvořte si nový soubor (sešit) se jménem Hlavní. V něm vytvořte hlavní tabulku na listě s názvem Tabulka, ze které si budete dynamicky načítat data (dynamické oblasti dejte název Tab) do kontingenční tabulky, ve které se bude provádět vlastní vyhodnocení obchodní činnosti pojišťovacích poradců. V hlavní tabulce nepoužívejte ověření vkládaných dat do buňky pomocí seznamu (to použijeme až v druhé části příkladu). Hotová hlavní tabulka vypadá takto:

Známým způsobem si tuto tabulku dynamicky pojmenujte.

Vytvoření tabulek pro zadávání dat

Hlavní tabulku jsme si vytvořili a teď si ukážeme, jak vytvoříme automatické propojení. Náš systém práce bude založený na jednoduchém principu. Každý pojišťovací poradce od nás dostane připravenou tabulku. V těchto tabulkách použijeme ověření vkládaných dat pomocí seznamu. Pojišťováci si budou tabulku vyplňovat sami. Zároveň budou mít tabulky uložené tam, kde bude možné jejich sdílení. My se jen na tyto jejich tabulky napojíme. Data si z nich automaticky necháme přeposílat do naíi hlavní tabulky. Pak už budeme mít jedinou práci. Data v kontingenční tabulce vyhodnotit.

Nejdříve začneme s přípravou tabulek pro jednotlivé pojišťovací poradce. Každou tabulku si vytvořte ve zvláštním sešitě (souboru) a dejte jí jméno podle jména pojišťováka. Začneme s tabulkou pro pojišťováka Brumbála. V této tabulce použijeme ověření vkládaných dat ve sloupcích Pojišťovák a Druh pojištění podle zadání příkladu:

Úplně stejným způsobem uděláme tabulky pro ostatní pojišťováky. Jen budeme ve sloupci Pojišťovák měnit v ověření jména jednotlivých pojišťováků. U těchto tabulek se vyplatí zadávat ověření sloupce. Vyhneme se zbytečným překlepům a tím i chybám v kontingenční tabulce. Zároveň to pojišťovákům usnadní práci. Nebudou muset takulku složitě vypisovat.

Podle uvedených příkladů naplňte tabulky jednotlivých pojišťováků daty:

Brumbál:

Novák:

Petříček:

Mastný:

Vytvoření propojení pomocí MS Query

Teď si otevřeme soubor (sešit) Hlavní a do naší tabulky si načteme data ze souborů jednotlivých pojišťováků.

Najeďtě si do buňky B3 a klikněte si na nabídku Data. V této nabídce si najeďtě na příkaz Importovat externí data a klikněte na Nový databázový dotaz:

Kdo nemá nainstalovanou MS Query, tomu se zobrazí okno se žádostí o instalaci MS Query. Pokud je MS Query nainstalovaná, tak se zobrazí okno Zvolit zdroj dat. V tomto okně zvolíme Soubory Excel a klikneme na tlačítko OK:

Poté se zobrazí okno Vybrat sešit. Zde si pomocí průvodce najdeme uložené sešity jednotlivých pojišťováků. Vybereme si sešit prvního pojišťováka Brumbála a klikneme na tlačítko OK:

Tím si spustíme průvodce dotazem. Nejdříve si zvolíme sloupce, ze kterých si budeme do hlavní tabulky načítat data:

Pak se nám zobrazí druhé okno průvodce dotazem. V tomto okně si můžeme data před načtením vyfiltrovat. My této možnosti nevyužijeme a klikneme na Další:

Ve třetím okně průvodce si můžeme data před načtením řadit do tabluky. Zase této možnosti nevyužijeme a klikneme na Další:

Ve čtvrtém okně průvodce dotaz ukončíme. Necháme zaškrtnutou volbu Načíst data do aplikace Microsoft Office Excel a klikneme na tlačítko Dokončit:

Zobrazí se nám poslední okno průvodce Importovat data. Pak už jenom zadáme, kam se mají data importovat (do buňky B3):

Načtená data v hlavní tabulce vypadají takto:

Stejným způsobem načtěte data z tabulek ostatních pojišťováků. Hlavní tabulka naplněná daty bude vypadat takto:

Teď si známým způsobem na listu Analýza vytvoříme z těchto dat kontingenční tabulku. Předtím nezapomeňte tabulku dynamicky pojmenovat, pokud jste to už neudělali:

Kontingenční tabulka je propojená s hlavní tabulkou. Jakmile se data v hlavní tabulce změní, pomocí tlačítka Aktualizace se nám hned a bezpracně data načtou do kontingenční tabulky.

Na podobném principu funguje načítání dat v hlavní tabulce z tabulek jednotlivých pojišťováků. Třeba u Brumbála si přidáme dvě nové smlouvy:

Jakmile si v souboru Hlavní klikneme u hlavní tabulky na tlačítko aktualizovat data, tak se nám automaticky do hlavní tabulky načtou:

Kombinací filtrů, dynamicky pojmenovaných oblastí a doplňku MS Query si můžeme velice jednoduše navrhnout profesionální tabulky, které za nás udělají spoustu práce. Nejdůležitější je, že pomocí takových tabulek se můžeme obejít bez drahých programů šitých na míru. Jen stačí vědět, jak na to. A to jsme se teď v kurzu naučili.

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.