Excel 2010

Spojení sešitů

V této kapitole budeme pojednávat o spojení jednolivých sešitů a tím i dat-údajů do sešitu jednoho pomocí aplikace MS Query. Sešit je v tomto případě soubor, který si ukládáme pod námi zvoleným názvem do určené složky v počítači. V předchozích kapitolách jsme pracovali s daty-údaji jednotlivých pojišťováků v jednom sešitě na záložce Hlavní. Obvykle si každý pojišťovák vede vlastní sešit se zaznamenanými údaji. My tyto údaje chceme sloučit do jedné přehledné tabulky. Můžeme je buďto manuálně přepsat nebo pří otevření více sešitů pomocí CTRL+C a CTRL+V kopírovat, ale vhodnějším řešením je automatické zpracování pomocí MS Query.
Data jsou do excelu (databáze) zadávána pouze jednou jednotlivými uživateli, kteří mohou mít omezená práva. Předpokládáme tedy, že data budou přístupná tomu kdo je bude zpracovávat na databázovém servru nebo ve sdílených složkách.
Máme pojišťovací poradce Pepu, Jardu, Otu, Máňu a Karla a prodáváme Pojištění život, Povinné ručení, Pojištění havarijní, Pojištění nemovitosti, Pojištění proti náhodě a Pojištění Novinka.
Vytvoříme si nový sešit s názvem "pojistky". V něm vytvoříme tabulku na listě "vyhodnoceni". Z té budeme dynamicky načítat data-údaje do kontingenční tabulky. Dynamickou oblast pojmenujeme "vyh".
Na záložce Vzorce - Definované názvy - Definovat název zvolíme Nový název a doplníme pole Název: vyh a Odkaz na: =POSUN(vyhodnoceni!$B$2;0;0;POČET2(vyhodnoceni!$B:$B);3)
Po vytvoření dynamické oblasti můžeme tuto oblast editovat ve Správci názvů.

Tabulky pojišťovacích poradců

Nyní pro každého pojišťováka vytvoříme tabulku ve zvláštním sešitě-souboru s použitím ověření vkládaných dat pomocí seznamu. Stejně jako v kapitole Flexibilní tabulky. Sešit pojmenujeme podle pojišťováka. Tabulky budou uložené tam, kde je možné jejich sdílení. Na tyto data-údaje se napojíme a necháme je automaticky přeposílat do hlavní tabulky. Potom je můžeme vyhodnotit v kontingenční tabulce.

     

Stejným způsobem vytvoříme sešity ostatním pojišťovákům.

Spojení sešitů pomocí MS Query

Otevřeme si sešit Pojistky a postupně budeme načítat data jednotlivých pojišťováků. Označíme myší nebo kurzorem buňku B3. Zvolíme záložku Data - Z jiných zdrojů a vybereme z aplikace MS Query.



Jako zdroj dat zvolíme Excel files a potvrdíme OK. Vybereme Adresář a Název databáze v příslušném sešitě.

     

Následuje Průvodce dotazem - volba sloupců. Vybereme tedy postupně: Pojišťovák, Druh pojištění, Cena a zvolíme Další. Možnost filtrování dat zatím nevyužijeme. Klikneme na Další.

  

Můžeme zvolit pořadí řazení nebo ho také přeskočit. V dalším okně stačí zvolit tlačítko Dokončit.

     

Při importování dat ponecháme předzvolený způsob zobrazení Tabulka. Pro umístění dat ponecháme existující list vyhodnoceni a zvolíme buňku $B$3, tedy =vyhodnoceni!$B$3. Postupně naimportujeme ostatní pojišťováky.

     

Vytvoříme nový list s názvem kontingencka. Na záložce Vložení - Kontingenční tabulka vybereme Kontingenční tabulka a do pole Tabulka/oblast zadáme odkaz na dynamickou oblast - vyh. Potvrdíme OK a následně kontingeční tabulku nastavíme podle požadovaného zobrazení dat-ůdajú.

Aktualizace dat

V sešitech jednotlivých pojišťováků se mění data-ůdaje podle toho jak přibývají nebo se mění smlouvy. Provedeme zkusmo pár změn. Pro zobrazení aktuální situace v sešitě pojistky zvolíme list kontingencka. Klikneme kamkoli do kontingenční tabulky a na záložce Nástroje kontingenční tabulky - Možnosti dvakrát stiskneme Aktualizovat. Tím dojde k aktualizaci dat na listě vyhodnoceni a následně v souvislosti s dynamicky pojmenovanou oblastí na listě kontingencka.