Načtení externích dat pomocí aplikace Microsoft Query

Pomocí aplikace Microsoft Query se můžete připojit k externím zdrojům dat, vybrat data z těchto externích zdrojů, importovat tato data do listu a podle potřeby je aktualizovat, aby byla data listu synchronizovaná s daty v externích zdrojích.

Typy databází, ke kterým máte přístup     Data můžete načíst z několika typů databází, včetně Microsoft Office Accessu, Microsoft SQL Serveru a služeb OLAP Microsoft SQL Serveru. Můžete také načíst data z excelových sešitů a z textových souborů.

Microsoft Office poskytuje ovladače, které můžete použít k načtení dat z následujících zdrojů dat:

• Microsoft SQL Server Analysis Services (Zprostředkovatel OLAP )

• Microsoft Office Access

• dBASE

• Microsoft FoxPro

• Microsoft Office Excel

• Oracle

• Paradox

• Databáze textových souborů

K načtení informací ze zdrojů dat, které tu nejsou uvedené, včetně jiných typů databází OLAP, můžete použít také ovladače ODBC nebo ovladače zdrojů dat od jiných výrobců. Informace o instalaci ovladače ODBC nebo ovladače zdroje dat, který tu není uvedený, najdete v dokumentaci k databázi nebo se obraťte na dodavatele databáze.

Výběr dat z databáze     Data z databáze načítáte vytvořením dotazu, což je otázka týkající se dat uložených v externí databázi. Pokud jsou například vaše data uložená v accessové databázi, můžete chtít znát údaje o prodeji konkrétního produktu v jednotlivých oblastech. Část dat můžete načíst tak, že vyberete jenom data pro produkt a oblast, které chcete analyzovat.

Pomocí aplikace Microsoft Query můžete vybrat požadované sloupce dat a importovat pouze tato data do Excelu.

Aktualizace listu v jedné operaci     Jakmile máte v excelovém sešitu externí data, kdykoli se databáze změní, můžete data Aktualizovat a aktualizovat analýzu – aniž byste museli znovu vytvářet souhrnné sestavy a grafy. Můžete například vytvořit měsíční souhrn prodeje a aktualizovat ho každý měsíc, když se objeví nové údaje o prodeji.

Jak Microsoft Query používá zdroje dat     Jakmile nastavíte zdroj dat pro konkrétní databázi, můžete ho použít vždy, když chcete vytvořit dotaz pro výběr a načtení dat z této databáze – aniž byste museli znovu zadávat všechny informace o připojení. Microsoft Query používá zdroj dat k připojení k externí databázi a k zobrazení dostupných dat. Po vytvoření dotazu a vrácení dat do Aplikace Excel poskytne aplikace Microsoft Query excelový sešit informace o dotazu i zdroji dat, abyste se mohli znovu připojit k databázi, když chcete data aktualizovat.

Import dat pomocí aplikace Microsoft Query     Chcete-li importovat externí data do Excelu pomocí aplikace Microsoft Query, postupujte podle těchto základních kroků, z nichž každý je podrobněji popsán v následujících částech.

Co je zdroj dat?     Zdroj dat je uložená sada informací, která umožňuje aplikacím Excel a Microsoft Query připojit se k externí databázi. Když k nastavení zdroje dat použijete Microsoft Query, pojmenujete zdroj dat a pak zadáte název a umístění databáze nebo serveru, typ databáze a informace o přihlášení a hesle. Informace zahrnují také název ovladače OBDC nebo ovladače zdroje dat, což je program, který vytváří připojení k určitému typu databáze.

Nastavení zdroje dat pomocí aplikace Microsoft Query:

1. Na kartě Data klikněte ve skupině Načíst externí data na Z jiných zdrojů a potom klikněte na Z aplikace Microsoft Query.

   Poznámka: Aplikace Excel 365 přesunula aplikaci Microsoft Query do skupiny nabídek Starší verze Průvodci.  Tato nabídka se ve výchozím nastavení nezobrazuje.  Pokud to chcete povolit, přejděte na Soubor, Možnosti, Data a povolte ho v části Zobrazit průvodce importem starších verzí dat .

2. Udělejte něco z tohoto:

   • Pokud chcete zadat zdroj dat pro databázi, textový soubor nebo excelový sešit, klikněte na kartu Databáze .

   • Pokud chcete určit zdroj dat datové krychle OLAP, klikněte na kartu Datové krychle OLAP . Tato karta je dostupná jenom v případě, že jste aplikaci Microsoft Query spustili z Excelu.

3. Poklikejte na <Nový zdroj dat>.

   – nebo –

   Klikněte na<Nový zdroj dat>a potom klikněte na OK.

   Zobrazí se dialogové okno Vytvořit nový zdroj dat .

4. V kroku 1 zadejte název pro identifikaci zdroje dat.

5. V kroku 2 klikněte na ovladač pro typ databáze, kterou používáte jako zdroj dat.

   Poznámky: 

   • Pokud externí databáze, ke které chcete získat přístup, není podporována ovladači ODBC, které jsou nainstalovány s aplikací Microsoft Query, musíte získat a nainstalovat ovladač ODBC kompatibilní s Microsoft Office od jiného dodavatele, například výrobce databáze. Pokyny k instalaci získáte od dodavatele databáze.

   • Databáze OLAP nevyžadují ovladače ODBC. Při instalaci aplikace Microsoft Query jsou nainstalovány ovladače pro databáze, které byly vytvořeny pomocí služby Microsoft SQL Server Analysis Services. Pokud se chcete připojit k jiným databázím OLAP, musíte nainstalovat ovladač zdroje dat a klientský software.

6. Klikněte na Připojit a zadejte informace potřebné pro připojení ke zdroji dat. U databází, sešitů aplikace Excel a textových souborů závisí informace, které zadáte, na typu vybraného zdroje dat. Může se zobrazit výzva k zadání přihlašovacího jména, hesla, verze databáze, kterou používáte, umístění databáze nebo jiné informace specifické pro daný typ databáze.

   Důležité informace: 

   • Používejte silná hesla kombinující malá a velká písmena, čísla a symboly. Slabá hesla obsahují jenom některé z těchto znaků. Příklad silného hesla: Y6dh!et5. Příklad slabého hesla: Domek27. Hesla by měla obsahovat minimálně 8 znaků. Ještě vhodnější jsou hesla obsahující 14 nebo víc znaků.

   • Je velice důležité, abyste si své heslo zapamatovali. Pokud je zapomenete, společnost Microsoft je nemůže odnikud načíst. Pokud si heslo zapíšete, uložte je na bezpečném místě odlišném od místa uložení informací, které heslo pomáhá chránit.

7. Po zadání požadovaných informací se kliknutím na TLAČÍTKO OK nebo Dokončit vraťte do dialogového okna Vytvořit nový zdroj dat .

8. Pokud databáze obsahuje tabulky a chcete, aby se určitá tabulka automaticky zobrazovala v Průvodci dotazem, klikněte na pole kroku 4 a potom klikněte na požadovanou tabulku.

9. Pokud nechcete při používání zdroje dat zadávat přihlašovací jméno a heslo, zaškrtněte políčko Uložit moje ID uživatele a heslo do definice zdroje dat . Uložené heslo není zašifrované. Pokud je zaškrtávací políčko nedostupné, obraťte se na správce databáze a zjistěte, jestli je tato možnost dostupná.

   Poznámka zabezpečení: Při připojování ke zdrojům dat se vyhněte ukládání přihlašovacích informací. Tyto informace mohou být uloženy jako prostý text a uživatel se zlými úmysly může k informacím přistupovat, aby mohl ohrozit zabezpečení zdroje dat.

Po dokončení těchto kroků se název zdroje dat zobrazí v dialogovém okně Zvolit zdroj dat .

Použití Průvodce dotazem pro většinu dotazů     Průvodce dotazem usnadňuje výběr a spojení dat z různých tabulek a polí v databázi. Pomocí Průvodce dotazem můžete vybrat tabulky a pole, které chcete zahrnout. Vnitřní spojení (operace dotazu, která určuje, že řádky ze dvou tabulek se zkombinují na základě stejných hodnot polí) se vytvoří automaticky, když průvodce rozpozná pole primárního klíče v jedné tabulce a pole se stejným názvem v druhé tabulce.

Můžete také použít průvodce k seřazení sady výsledků a k jednoduchému filtrování. V posledním kroku průvodce můžete zvolit vrácení dat do Excelu nebo další upřesnění dotazu v aplikaci Microsoft Query. Po vytvoření dotazu ho můžete spustit v Excelu nebo v Microsoft Query.

Průvodce dotazem spustíte provedením následujících kroků.

1. Na kartě Data klikněte ve skupině Načíst externí data na Z jiných zdrojů a potom klikněte na Z aplikace Microsoft Query.

2. V dialogovém okně Zvolit zdroj dat se ujistěte, že je zaškrtnuté políčko Použít Průvodce dotazem k vytvoření nebo úpravě dotazů .

3. Poklikejte na zdroj dat, který chcete použít.

   – nebo –

   Klikněte na zdroj dat, který chcete použít, a potom klikněte na OK.

Práce přímo v Microsoft Query pro jiné typy dotazů     Pokud chcete vytvořit složitější dotaz, než umožňuje Průvodce dotazem, můžete pracovat přímo v aplikaci Microsoft Query. Pomocí aplikace Microsoft Query můžete zobrazit a změnit dotazy, které začnete vytvářet v Průvodci dotazem, nebo můžete vytvářet nové dotazy bez použití průvodce. Pracujte přímo v Aplikaci Microsoft Query, pokud chcete vytvářet dotazy, které dělají následující:

• Výběr konkrétních dat z pole     Ve velké databázi můžete chtít vybrat některá z dat v poli a vynechat data, která nepotřebujete. Pokud například potřebujete data pro dva produkty v poli, které obsahuje informace pro mnoho produktů, můžete pomocí Kritéria vybrat data jenom pro dva požadované produkty.

• Načtení dat na základě různých kritérií při každém spuštění dotazu     Pokud potřebujete vytvořit stejnou excelovou sestavu nebo souhrn pro několik oblastí ve stejných externích datech – například samostatnou sestavu prodeje pro každou oblast – můžete vytvořit Parametrický dotaz. Když spustíte parametrický dotaz, zobrazí se výzva k zadání hodnoty, která se použije jako kritérium při výběru záznamů dotazem. Parametrický dotaz vás může například vyzvat k zadání konkrétní oblasti a tento dotaz můžete znovu použít k vytvoření každé sestavy prodeje v jednotlivých oblastech.

• Různé způsoby spojení dat     Vnitřní spojení, která Průvodce dotazem vytvoří, jsou nejběžnějším typem spojení, které se používá při vytváření dotazů. Někdy ale chcete použít jiný typ spojení. Pokud máte například tabulku informací o prodeji produktů a tabulku informací o zákaznících, vnitřní spojení (typ vytvořený Průvodcem dotazem) zabrání načtení záznamů o zákaznících pro zákazníky, kteří neprošli nákupem. Pomocí Microsoft Query můžete tyto tabulky spojit, aby se načetly všechny záznamy zákazníků spolu s prodejními daty pro zákazníky, kteří provedli nákupy.

Pokud chcete spustit Microsoft Query, proveďte následující kroky.

1. Na kartě Data klikněte ve skupině Načíst externí data na Z jiných zdrojů a potom klikněte na Z aplikace Microsoft Query.

2. V dialogovém okně Zvolit zdroj dat se ujistěte, že políčko Použít Průvodce dotazem k vytvoření nebo úpravě dotazů není zaškrtnuté.

3. Poklikejte na zdroj dat, který chcete použít.

   – nebo –

   Klikněte na zdroj dat, který chcete použít, a potom klikněte na OK.

Opakované nasazení a sdílení dotazů     V Průvodci dotazem i v Aplikaci Microsoft Query můžete dotazy uložit jako soubor .dqy, který můžete upravovat, opakovaně používat a sdílet. Excel může přímo otevírat soubory .dqy, což umožňuje vám nebo jiným uživatelům vytvářet další rozsahy externích dat ze stejného dotazu.

Otevření uloženého dotazu z Excelu:

1. Na kartě Data klikněte ve skupině Načíst externí data na Z jiných zdrojů a potom klikněte na Z aplikace Microsoft Query. Zobrazí se dialogové okno Zvolit zdroj dat .

2. V dialogovém okně Zvolit zdroj dat klikněte na kartu Dotazy .

3. Poklikejte na uložený dotaz, který chcete otevřít. Dotaz se zobrazí v aplikaci Microsoft Query.

Pokud chcete otevřít uložený dotaz a aplikace Microsoft Query je již otevřená, klikněte na nabídku Soubor dotazu Microsoft a potom klikněte na Otevřít.

Pokud dvakrát kliknete na soubor .dqy, excel se otevře, spustí dotaz a potom vloží výsledky do nového listu.

Pokud chcete sdílet excelový souhrn nebo sestavu, která je založená na externích datech, můžete ostatním uživatelům poskytnout sešit obsahující oblast externích dat nebo můžete vytvořit Šablona. Šablona umožňuje uložit souhrn nebo sestavu bez uložení externích dat, aby byl soubor menší. Externí data se načtou, když uživatel otevře šablonu sestavy.

Po vytvoření dotazu v Průvodci dotazem nebo v aplikaci Microsoft Query můžete data vrátit do excelového listu. Data se pak stanou Oblast externích dat nebo Kontingenční tabulka, které můžete formátovat a aktualizovat.

Formátování načtených dat     V Excelu můžete pomocí nástrojů, jako jsou grafy nebo automatické mezisoučty, prezentovat a shrnout data načtená aplikací Microsoft Query. Data můžete formátovat a při aktualizaci externích dat se formátování zachovají. Místo názvů polí můžete použít vlastní popisky sloupců a čísla řádků přidat automaticky.

Excel dokáže automaticky formátovat nová data, která zadáte na konec oblasti, aby odpovídala předchozím řádkům. Excel může také automaticky kopírovat vzorce, které se opakovaly v předchozích řádcích, a rozšířit je na další řádky.

Tuto možnost můžete kdykoli zapnout (nebo znovu vypnout):

1. Klikněte na Soubor > Možnosti > Upřesnit.

2. V části Možnosti úprav zaškrtněte políčko Rozšířit formáty a vzorce oblasti dat . Pokud chcete automatické formátování rozsahu dat znovu vypnout, zrušte zaškrtnutí tohoto políčka.

Aktualizace externích dat:     Při aktualizaci externích dat spustíte dotaz, který načte všechna nová nebo změněná data, která odpovídají vašim specifikacím. Dotaz můžete aktualizovat v Microsoft Query i Excelu. Excel nabízí několik možností pro aktualizaci dotazů, včetně aktualizace dat při každém otevření sešitu a automatické aktualizace v časových intervalech. V Excelu můžete pokračovat v práci, zatímco se data aktualizují, a můžete také zkontrolovat stav během aktualizace dat. Další informace najdete v článku Aktualizace externího datového připojení v Excelu.

Začátek stránky