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

Pomocí Microsoft Query můžete načítat data z externích zdrojů. Když budete načítat data z podnikových databází a souborů pomocí aplikace Microsoft Query, nebudete muset data, která chcete analyzovat v Excelu, znovu zadávat. Excelové sestavy a souhrny můžete taky automaticky aktualizovat z původní zdrojové databáze vždy, když se databáze aktualizuje o nové informace.

Další informace o Microsoft Query

Pomocí 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čítat z několika typů databází, mezi které patří systém Microsoft Office Access, Microsoft SQL Server a Microsoft SQL Server OLAP Services. Data můžete načítat také z excelových sešitů a textových souborů.

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

• Služba Analysis Services serveru SQL (zprostředkovatel OLAP)
• systém Microsoft Office Access
• dBASE
• Microsoft FoxPro
• systém Microsoft Office Excel
• Oracle
• Paradox
• Databáze textových souborů

K načtení informací ze zdrojů dat, které zde nejsou uvedeny, 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ý zde není uveden, najdete v dokumentaci k databázi nebo se obraťte na dodavatele databáze.

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

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

Aktualizace listu jednou operací Jakmile máte v excelovém sešitu externí data, můžete při každé změně databáze aktualizovat data a aktualizovat tak 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ž přijdou nové údaje o prodeji.

Jak Microsoft Query používá zdroje dat Po nastavení zdroje dat pro konkrétní databázi jej můžete použít vždy, když budete chtít vytvořit dotaz k výběru a načtení dat z této databáze – aniž byste museli znovu zadávat všechny informace o připojení. Microsoft Query použije zdroj dat k připojení k externí databázi a zobrazí vám, která data jsou k dispozici. Po vytvoření dotazu a vrácení dat do Excelu poskytne Microsoft Query excelovému sešitu informace o dotazu a zdroji dat, takže se můžete k databázi znovu připojit, když budete chtít data aktualizovat.

Pomocí Microsoft Query importujte data Pokud chcete do Excelu pomocí Microsoft Query importovat externí data, postupujte podle těchto základních kroků, které jsou v následujících částech podrobněji popsány.

Připojení ke zdroji dat

Co je zdroj dat?  Zdroj dat je uložená sada informací, která umožňuje Excelu a Microsoft Query připojit se k externí databázi. Při nastavení zdroje dat pomocí aplikace Microsoft Query zadejte název zdroje dat a potom název a umístění databáze nebo serveru, typ databáze a přihlašovací údaje a heslo. Tyto 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 Microsoft Query.

   Poznámka

   Excel 365 přesunul Microsoft Query do skupiny nabídek Starší 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 v části Zobrazit starší průvodce importem dat .

2. Udělejte jednu z těchto věcí:

   • Pokud chcete zadat zdroj dat pro databázi, textový soubor nebo excelový sešit, klikněte na kartu Databáze .
   • Chcete-li určit zdroj dat datové krychle OLAP, klikněte na kartu Datové krychle OLAP . Tato karta je dostupná jenom v případě, že jste Microsoft Query spustili z Excelu.

3. Poklikejte na <Nový zdroj> dat.
   – nebo –
   Klikněte na <tlačítko Nový zdroj> dat a potom na tlačítko OK.
   Zobrazí se dialogové okno Vytvořit nový zdroj dat .

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

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

   Poznámka

   • Pokud ovladače ODBC nainstalované s Microsoft Query nepodporují externí databázi, ke které chcete získat přístup, budete muset získat a nainstalovat ovladač ODBC kompatibilní s systém Microsoft Office od jiného dodavatele, například od výrobce databáze. Pokyny k instalaci získáte od dodavatele databáze.
   • Databáze OLAP ovladače ODBC nevyžadují. Při instalaci aplikace Microsoft Query se nainstalují ovladače pro databáze, které byly vytvořené pomocí služby Microsoft Služba Analysis Services serveru SQL. Pro připojení k dalším databázím OLAP je potřeba nainstalovat ovladač zdroje dat a klientský software.

6. Klikněte na tlačítko 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í poskytnuté informace na typu vybraného zdroje dat. Aplikace vás může vyzvat k zadání přihlašovacího jména, hesla, verze používané databáze, umístění databáze nebo dalších informací specifických pro daný typ databáze.

   Důležité

   • 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 zobrazila automaticky v Průvodci dotazem, klikněte na pole pro krok 4 a potom klikněte na požadovanou tabulku.

9. Nechcete-li při použití zdroje dat zadávat přihlašovací jméno a heslo, zaškrtněte políčko Uložit ID uživatele a heslo v definici zdroje dat . Uložené heslo není zašifrováno. Pokud je toto zaškrtávací políčko nedostupné, požádejte správce databáze o rozhodnutí, zda je možné tuto možnost zpřístupnit.

   Poznámka

   Vyhněte se ukládání přihlašovacích údajů při připojování ke zdrojům dat. Tyto informace mohou být uloženy jako prostý text a kyberzločinec by mohl získat přístup k informacím a ohrozit zabezpečení zdroje dat.

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

Definování dotazu pomocí Průvodce dotazem

Pro většinu dotazů použijte Průvodce dotazem . Průvodce dotazem usnadňuje výběr a sloučení dat z různých tabulek a polí v databázi. Pomocí Průvodce dotazem můžete vybrat tabulky a pole, které chcete zahrnout. Inner join (operace dotazu, která určuje, že řádky ze dvou tabulek jsou sloučeny na základě identický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.

Pomocí průvodce také můžete sadu výsledků seřadit a jednoduše filtrovat. V posledním kroku průvodce můžete určit, jestli chcete vrátit data do Excelu, nebo můžete dotaz dál upřesnit v Microsoft Query. Po vytvoření dotazu ho můžete spustit buď v Excelu, nebo v Microsoft Query.

Průvodce dotazem spustíte následujícím postupem.

1. Na kartě Data klikněte ve skupině Načíst externí data na Z jiných zdrojů a potom klikněte na Z Microsoft Query.
2. V dialogovém okně Zvolit zdroj dat zkontrolujte, zda je zaškrtnuto políčko Vytvářet nebo upravovat dotazy pomocí Průvodce dotazem .
3. Poklikejte na zdroj dat, který chcete použít.
   – nebo –
   Klikněte na zdroj dat, který chcete použít, a potom klikněte na tlačítko OK.

Přímá práce s jinými typy dotazů v Microsoft Query Pokud chcete vytvořit složitější dotaz, než umožňuje Průvodce dotazem, můžete pracovat přímo v Microsoft Query. Pomocí Microsoft Query můžete zobrazit a změnit dotazy, které začnete vytvářet v Průvodci dotazem, nebo můžete nové dotazy vytvořit bez průvodce. Pokud chcete vytvářet dotazy, které mají následující možnosti, pracujte přímo v Microsoft Query:

• Výběr určitých dat z pole Ve velké databázi můžete chtít vybrat některá data v poli a vynechat ta, která nepotřebujete. Pokud například potřebujete data pro dva produkty v poli, které obsahuje informace pro mnoho produktů, můžete pomocí kritérií vybrat data pouze pro dva produkty, které chcete.
• 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, budete vyzváni k zadání hodnoty, kterou použijete jako kritérium, když dotaz vybírá záznamy. Parametrický dotaz vás třeba může vyzvat k zadání určité oblasti, který můžete opakovaně použít k vytvoření jednotlivých sestav prodeje v jednotlivých oblastech.
• Spojování dat různými způsoby Vnitřní spojení, která Průvodce dotazem vytvoří, jsou nejběžnějším typem spojení používaným při vytváření dotazů. Někdy ale můžete chtít použít jiný typ spojení. Máte-li například tabulku s informacemi o prodeji výrobků a tabulku s informacemi o zákaznících, zabrání vnitřní spojení (typ vytvořený Průvodcem dotazem) načtení záznamů o zákaznících u zákazníků, kteří neuskutečnili žádný nákup. Pomocí Microsoft Query můžete tyto tabulky spojit a načíst tak všechny záznamy o zákaznících společně s údaji o prodeji těchto zákazníků, kteří již uskutečnili nákup.

Microsoft Query spustíte následujícím postupem.

1. Na kartě Data klikněte ve skupině Načíst externí data na Z jiných zdrojů a potom klikněte na Z Microsoft Query.
2. V dialogovém okně Zvolit zdroj dat se ujistěte, že není zaškrtnuté políčko Použít Průvodce dotazem k vytváření/úpravám 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 tlačítko OK.

Opakované použití a sdílení dotazů V Průvodci dotazem i v Microsoft Query můžete ukládat dotazy jako soubor .dQY, který můžete upravovat, opakovaně používat a sdílet. Excel umí přímo otevírat soubory .dqy, což umožňuje vám nebo jiným uživatelům vytvářet další oblasti 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 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 Microsoft Query je už otevřený, klikněte na nabídku Soubor Microsoft Query a potom na Otevřít.

Pokud poklikáte na soubor .dqy, Excel se otevře, spustí dotaz a výsledky vloží do nového listu.

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

Práce s daty v Excelu

Po vytvoření dotazu v Průvodci dotazem nebo v Microsoft Query můžete data vrátit do listu aplikace Excel. Data se pak stanou oblastí externích dat nebo sestavou kontingenční tabulky, kterou můžete formátovat a aktualizovat.

Formátování načtených dat V Excelu můžete použít nástroje, jako jsou grafy nebo automatické mezisoučty, k prezentaci a shrnutí dat načtených aplikací Microsoft Query. Data můžete naformátovat a formátování zůstane zachováno i při aktualizaci externích dat. Místo názvů polí můžete použít vlastní popisky sloupců a čísla řádků můžete přidávat automaticky.

Excel dokáže automaticky naformátovat nová data, která zadáte na konci oblasti, tak, aby odpovídala předchozím řádkům. Excel taky umí automaticky zkopí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 naUpřesnitmožnosti>souboru>.
2. V části Možnosti úprav zaškrtněte políčko Rozšířit rozsah formátů a vzorců dat. Pokud chcete automatické formátování oblastí 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 veškerá nová nebo změněná data, která odpovídají vašim specifikacím. Dotaz můžete aktualizovat v Microsoft Query i v Excelu. Excel nabízí několik možností aktualizace dotazů, včetně aktualizace dat při každém otevření sešitu a automatické aktualizace v časových intervalech. Během aktualizace dat můžete pokračovat v práci v Excelu a můžete taky kontrolovat stav aktualizace dat. Další informace naleznete v tématu Aktualizace externího datového připojení v aplikaci Excel.

Začátek stránky