Vytvoření, načtení nebo úprava dotazu v Excel (Power Query)

Vytvoření, načtení nebo úprava dotazu v Excel (Power Query)

Power Query nabízí několik způsobů, jak do sešitu vytvářet a načítat power dotazy. Můžete taky nastavit výchozí nastavení načítání dotazů v okně Možnosti dotazu.   

Tip:      Pokud chcete vědět, jestli jsou data v listu ve tvaru Power Query, vyberte buňku dat a pokud se zobrazí karta kontextového pásu karet Dotazu, pak se data načetla z Power Query. 

Výběrem buňky v dotazu zobrazíte kartu Dotaz.

Vědět, ve kterém prostředí se nacházíte Power Query je dobře integrovaný do uživatelského rozhraní Excel, zejména při importu dat, práci s připojeními a úpravách kontingenčních tabulek, Excel tabulek a pojmenovaných rozsahů. Abyste se vyhnuli nejasnostem, je důležité kdykoli vědět, ve kterém prostředí se právě nacházíte, Excel nebo Power Query.

Známé Excel listu, pásu karet a mřížky

Pás karet a náhled dat v Power Query Editoru 

Typický list Excel listu Typické zobrazení Editoru Power Query

Například manipulace s daty v listu Excel se zásadně liší od Power Query. Připojená data, která vidíte v listu Excel, navíc vidíte v Power Query, která pracují na pozadí, aby data utvářely. K tomu dochází jenom v případě, že data načtete do listu nebo datového modelu z Power Query.

Přejmenování karet listu    Je dobré karty listu přejmenovat smysluplně, zvlášť pokud jich máte hodně. Zvlášť důležité je objasnit rozdíl mezi listem dat a listem načteným z Power Query Editoru. I když máte jenom dva listy, jeden s tabulkou Excel s názvem List1Excel druhý dotaz vytvořený importem této tabulky s názvem Tabulka1, je snadné se zmást. Vždycky je vhodné změnit výchozí názvy karet listu na názvy, které vám budou dávat větší smysl. Například přejmenujte List1 na DataTable a Table1 na QueryTable. Teď je jasné, která karta obsahuje data a která karta obsahuje dotaz.

Můžete buď vytvořit dotaz z importovaných dat, nebo vytvořit prázdný dotaz.

Vytvoření dotazu z importovaných dat

Jedná se o nejběžnější způsob vytvoření dotazu.

  1. Import některých dat Další informace najdete v tématu Import dat z externích zdrojů dat.

  2. Vyberte buňku v datech a pak vyberte Dotaz> Upravit.

Vytvoření prázdného dotazu

Možná budete chtít začít od začátku. Tuto akci můžete provést dvěma způsoby:

  • Vyberte Data> data > z jiných zdrojů > prázdný dotaz.

  • Vyberte Data > Získat data > Spuštění Power Query Editoru.

V tomto okamžiku můžete ručně přidat kroky a vzorce, pokud dobře znáte jazyk vzorců Power Query M.

Nebo můžete vybrat Domů a pak vybrat příkaz ve skupině Nový dotaz. Proveďte jeden z následujících kroků:

  • Pokud chcete přidat zdroj dat, vyberte Nový zdroj. Tento příkaz je stejně jako příkaz Data> na pásu karet Excel dat.

  • Vyberte Poslední zdroje a vyberte zdroj dat, se který jste pracovali. Tento příkaz je stejně jako příkaz Data> Poslední zdroje na pásu Excel pásu karet.

  • Pokud chcete data zadat ručně, vyberte Zadat data. Tento příkaz můžete zvolit, pokud chcete vyzkoušet Power Query Editor nezávisle na externím zdroji dat.

Za předpokladu, že je dotaz platný a nemá žádné chyby, můžete ho načíst zpátky do listu nebo datového modelu.

Načtení dotazu z Power Query Editoru

V Power Query Editoru proveďte jednu z těchto akcí:

  • Pokud chcete načíst list, vyberte Domů> Zavřít & Načíst > Zavřít & Načíst.

  • Pokud chcete načíst datový model, vyberte Domů > Zavřít & > Zavřít & Načíst do.

    V dialogovém okně Importovat data vyberte Přidat tato data do datového modelu.

Tip:   Někdy je příkaz Načíst do neaktivní nebo zakázaný. K tomu může dojít při prvním vytvoření dotazu v sešitu. Pokud k tomu dojde, vyberte Zavřít &Načíst , v novém listu vyberte Data > Dotazy & Připojení > Dotazy, klikněte pravým tlačítkem myši na dotaz a pak vyberte Načíst do. Nebo na pásu karet Power Query Editoru vyberte Dotaz> Načíst do.

Načtení dotazu z podokna Dotazy a připojení 

V Excel můžete chtít dotaz načíst do jiného listu nebo datového modelu.

  1. V Excel vyberte Data> Dotazy & Připojenía pak vyberte kartu Dotazy.

  2. V seznamu dotazů vyhledejte dotaz, klikněte na něj pravým tlačítkem myši a pak vyberte Načíst do. Zobrazí se dialogové okno Importovat data.

  3. Rozhodněte se, jak chcete data importovat, a pak vyberte OK. Další informace o použití tohoto dialogového okna získáte tak, že vyberete otazník (?).

Dotaz načtený do listu můžete upravit několika způsoby.

Úprava dotazu z dat v Excel listu

  • Pokud chcete upravit dotaz, vyhledejte dříve načtený dotaz z Power Query Editoru, vyberte buňku v datech a pak vyberte Dotaz > Upravit.

Úprava dotazu z podokna Dotazy & Připojení

Možná zjistíte, že & připojení je vhodnější použít, když máte v jednom sešitu mnoho dotazů a chcete ho rychle najít.

  1. V Excel vyberte Data> Dotazy & Připojenía pak vyberte kartu Dotazy.

  2. V seznamu dotazů vyhledejte dotaz, klikněte na něj pravým tlačítkem myši a pak vyberte Upravit.

Úprava dotazu v dialogovém okně Vlastnosti dotazu

  • V Excel vyberte Data> Data & Connections > Queries tab, klikněte pravým tlačítkem myši na dotaz a vyberte Vlastnosti ,v dialogovém okně Vlastnosti vyberte kartu Definice a pak vyberte Upravit dotaz.

Tip    Pokud jste na listu s dotazem, vyberte >vlastnosti, v dialogovém okně Vlastnosti vyberte kartu Definice a pak vyberte Upravit dotaz. 

Datový model obvykle obsahuje několik tabulek uspořádaných v relaci. Dotaz načítáte do datového modelu pomocí příkazu Načíst do, který zobrazí dialogové okno Importovat data, a pak zaškrtujete políčko Přidat tato datado datového režimu l. Další informace o datových modelech najdete v tématu Zjistěte, které zdroje dat se používají v datovém modelu sešitu ,Vytvoření datového modeluv aplikaci Excel a Použití více tabulek k vytvoření kontingenční tabulky.

  1. Pokud chcete otevřít datový model, vyberte Power Pivot >Spravovat.

  2. V dolní části okna Power Pivotu vyberte požadovanou tabulku na kartě listu.

    Zkontrolujte, že se zobrazí správná tabulka. Datový model může mít mnoho tabulek.

  3. Poznamenejte si název tabulky.

  4. Okno Power Pivotu zavřete tak, že vyberete Soubor> Zavřít. Získání paměti může trvat několik sekund.

  5. Vyberte Data > Připojení & vlastnosti > dotazy, klikněte pravým tlačítkem myši na dotaz a pak vyberte Upravit.

  6. Po dokončení provádění změn v Power Query Editoru vyberte Soubor a> Zavřít & Načíst.

Výsledek

Dotaz na listu a v tabulce v datovém modelu se aktualizují.

Pokud si všimnete, že načítání dotazu do datového modelu trvá mnohem déle než načítání do listu, podívejte se na postup v Power Query a podívejte se, jestli filtrujete textový sloupec nebo strukturovaný sloupec Seznam pomocí operátoru Obsahuje. Tato akce Excel znovu vytvořit výčet v celé sadě dat pro každý řádek. Kromě toho Excel efektivně používat spouštění s více vlákny. Jako alternativní řešení zkuste použít jiný operátor, například Rovná se nebo Začíná od.

Společnost Microsoft o tomto problému ví a je v šetření.

Power Query můžete načíst:

  • Na list. V Editoru Power Query vyberte Domů a> Zavřít & načíst > Zavřít & Načíst.

  • Do datového modelu. V Editoru Power Query vyberte Domů a > zavřít & >Zavřete & Načíst do.

    Power Query ve výchozím nastavení načte dotazy do nového listu při načítání jednoho dotazu a načte více dotazů současně do datového modelu.  Výchozí chování můžete změnit pro všechny sešity nebo jenom pro aktuální sešit. Při nastavování těchto možností Power Query nezmění výsledky dotazu v listu ani v datech a poznámkách datového modelu.

    Výchozí nastavení dotazu můžete také dynamicky přepsat pomocí dialogového okna Importovat, které se zobrazí po výběru možnosti Zavřít & načístdo. 

Globální nastavení, která platí pro všechny sešity

  1. V Power Query Editoru vyberte Možnosti > a nastavení > Možnosti dotazu.

  2. V dialogovém okně Možnosti dotazu na levé straně v části GLOBAL (GLOBÁLNÍ)  vyberte Data Load (Načtení dat).

  3. V části Default Query Load Nastavení (Výchozí načtení dotazu) proveďte toto:

    • Vyberte Použít standardní nastavení zatížení

    • Vyberte Zadat vlastní výchozí nastavení načítánía pak vyberte nebo zrušte zaškrtnutí políčka Načíst do  listu nebo Načíst do datového modelu.

Tip    V dolní části dialogového okna můžete vybrat Obnovit výchozí nastavení a pohodlně se vrátit k výchozímu nastavení.

Nastavení sešitu, která platí jenom pro aktuální sešit

  1. V dialogovém okně Možnosti dotazu na levé straně v části CURRENT WORKBOOK (AKTUÁLNÍ SEŠIT) vyberte Data Load (Načtení dat).

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

    • V části Rozpoznávání typůvyberte nebo zrušte zaškrtnutí políčka Rozpoznat typy sloupců a záhlaví nestrukturovaných zdrojů.

      Výchozí chování je jejich rozpoznání. Pokud chcete data utvářet sami, zrušte zaškrtnutí tohoto políčka.

    • V části Relacevyberte nebo zrušte zaškrtnutí políčka Při prvním přidání do datového modelu vytvořit relace mezi tabulkami.

      Před načtením do datového modelu je výchozím chováním nalezení existujících relací mezi tabulkami, jako jsou cizí klíče v relační databázi, a jejich import s daty. Tuto možnost zrušte, pokud to chcete udělat sami.

    • V části Relacevyberte nebo zrušte zaškrtnutí políčka Aktualizovat relace při aktualizaci dotazů načtených do datového modelu.

      Výchozí chování je ne aktualizovat relace. Při aktualizaci dotazů, které už jsou načtené do datového modelu, Power Query najde existující relace mezi tabulkami, jako jsou cizí klíče, v relační databázi a aktualizuje je. To může odebrat relace vytvořené ručně po importu dat nebo zavést nové relace. Pokud to ale chcete udělat, vyberte tuto možnost.

    • V části Data pozadívyberte nebo zrušte zaškrtnutí políčka Povolit stahování náhledůdat na pozadí .

      Výchozí chování je stahování náhledů dat na pozadí. Tuto možnost zrušte, pokud chcete hned zobrazit všechna data.

Viz také

Nápověda pro doplněk Power Query pro Excel

Správa dotazů v Excel

Potřebujete další pomoc?

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

×