Applies ToExcel pro Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016 Excel 2013

Pomocí Editor Power Query jste celou dobu vytvářeli Power Query vzorce. Podívejme se, jak Power Query funguje, když se podíváme pod pokličku. Informace o tom, jak aktualizovat nebo přidat vzorce, se dozvíte jenom tak, že se díváte na Editor Power Query v akci.  Pomocí Rozšířený editor můžete dokonce rolovat vlastní vzorce.           

Editor Power Query poskytuje dotaz na data a prostředí pro formování pro Excel, které můžete použít k přetváření dat z mnoha zdrojů dat. Pokud chcete zobrazit okno Editor Power Query, importujte data z externích zdrojů datv excelovém listu, vyberte buňku v datech a pak vyberte Dotaz > Upravit. Následuje souhrn hlavních komponent.

Části editoru dotazů

  1. Pás karet Editor Power Query, který používáte k tvarování dat

  2. Podokno Dotazy, které používáte k vyhledání zdrojů dat a tabulek

  3. Místní nabídky, které jsou praktickými zástupci příkazů na pásu karet

  4. Náhled dat zobrazující výsledky kroků použitých u dat

  5. Podokno Nastavení dotazu se seznamem vlastností a jednotlivých kroků dotazu

Každý krok dotazu je na pozadí založený na vzorci, který je viditelný na řádku vzorců.

Příklad vzorce v editoru dotazů

Může se stát, že budete chtít vzorec upravit nebo vytvořit. Vzorce používají Power Query jazyk vzorců, který můžete použít k vytváření jednoduchých i složitých výrazů. Další informace o syntaxi, argumentech, poznámkách, funkcích a příkladech najdete v tématu Power Query jazyk vzorců M.

Pokud použijete jako příklad seznam fotbalových mistrovství, použijete Power Query k tomu, abyste mohli vzít nezpracovaná data, která jste našli na webu, a převést je na dobře formátovanou tabulku. V podokně Nastavení dotazů v části Použitý postup a na řádku vzorců můžete sledovat, jak se pro jednotlivé úlohy vytvářejí kroky dotazu a odpovídající vzorce.

Váš prohlížeč nepodporuje video. Nainstalujte si Microsoft Silverlight, Adobe Flash Player nebo Internet Explorer 9.

Postup

  1. Pokud chcete data importovat, vyberte Data > Z webu, do pole ADRESA URL zadejte "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" a pak vyberte OK.

  2. V dialogovém okně Navigátor vyberte na levé straně tabulku Results [Edit] (Výsledky [Upravit] a pak dole vyberte Transformovat data ). Zobrazí se editor Power Query.

  3. Pokud chcete změnit výchozí název dotazu, v podokně Nastavení dotazu v části Vlastnosti odstraňte "Results [Edit]" (Výsledky [Upravit]) a pak zadejte "LOCKER champs".

  4. Pokud chcete odebrat nežádoucí sloupce, vyberte první, čtvrtý a pátý sloupec a pak vyberte Domů > Odebrat sloupec > Odebrat ostatní sloupce.

  5. Pokud chcete odebrat nežádoucí hodnoty, vyberte Sloupec1, vyberte Domů > Nahradit hodnoty, do pole Nalezené hodnoty zadejte "podrobnosti" a pak vyberte OK.

  6. Pokud chcete odebrat řádky, které obsahují slovo Rok, vyberte šipku filtru ve sloupci Column1, zrušte zaškrtnutí políčka vedle položky Rok a pak vyberte OK.

  7. Pokud chcete záhlaví sloupců přejmenovat, poklikejte na ně a změňte Sloupec1 na Rok, Sloupec4 na Vítěz a Sloupec5 na Konečné skóre.

  8. Pokud chcete dotaz uložit, vyberte Domů > Zavřít & Načtení.

Výsledek

Výsledky návodu – prvních několik řádků

Následující tabulka obsahuje souhrn jednotlivých použitých kroků a odpovídající vzorec.

Krok a úkol dotazu

Vzorec

Zdroj

Připojení k webovému zdroji dat

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Navigace

Vyberte tabulku, která se má připojit.

=Source{2}[Data]

Změněný typ

Změna datových typů (což Power Query dělá automaticky)

= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})

Odebrané ostatní sloupce

Odebrání ostatních sloupců tak, aby se zobrazovaly jenom potřebné sloupce

= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})

Nahrazená hodnota

Nahrazení hodnot pro vyčištění hodnot ve vybraném sloupci

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})

Filtrované řádky

Filtrování hodnot ve sloupci

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

Přejmenované sloupce

Změna záhlaví sloupců tak, aby byla smysluplná

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Důležité:    Při úpravách kroků Zdroj, Navigace  a Změněný typ buďte opatrní, protože jsou vytvořené Power Query k definování a nastavení zdroje dat.

Zobrazení nebo skrytí řádku vzorců

Řádek vzorců se ve výchozím nastavení zobrazuje, ale pokud není viditelný, můžete ho znovu zobrazit.

  • Vyberte Zobrazit > rozložení > řádku vzorců.

Edit vzorec v řádku vzorců

  1. Pokud chcete otevřít dotaz, vyhledejte dříve načtený z Editor Power Query, vyberte buňku v datech a pak vyberte Dotaz > Upravit. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excelu.

  2. V podokně Nastavení dotazů v části Použitý postup vyberte krok, který chcete upravit.

  3. Na řádku vzorců vyhledejte a změňte hodnoty parametrů a pak vyberte ikonu Enter Ikona Enter nalevo od řádku vzorců v Power Query nebo stiskněte Klávesu Enter. Změňte například tento vzorec tak, aby se zachoval také sloupec Column2:Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Výběrem ikony Enter Ikona Enter nalevo od řádku vzorců v Power Query nebo stisknutím klávesy Enter zobrazte nové výsledky v náhledu dat.

  5. Pokud chcete zobrazit výsledek v excelovém listu, vyberte Domů > Zavřít & Načíst.

Vytvoření vzorce v řádku vzorců

V příkladu jednoduchého vzorce převedeme textovou hodnotu na velká písmena pomocí funkce Text.Proper.

  1. Pokud chcete otevřít prázdný dotaz, v Excelu vyberte Data > Získat data > Z jiných zdrojů > Prázdný dotaz. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excelu.

  2. Na řádku vzorců zadejte=Text.Proper("text value")a pak vyberte ikonu Enter Ikona Enter nalevo od řádku vzorců v Power Query nebo stiskněte Enter.Výsledky se zobrazí v náhledu dat .

  3. Pokud chcete zobrazit výsledek v excelovém listu, vyberte Domů > Zavřít & Načíst.

Výsledek

Text.Proper

 Když vytvoříte vzorec, Power Query ověří syntaxi vzorce. Když ale vložíte, změníte pořadí nebo odstraníte mezikrok v dotazu, můžete dotaz potenciálně přerušit.  Vždy ověřte výsledky v náhledu dat.

Důležité:    Při úpravách kroků Zdroj, Navigace  a Změněný typ buďte opatrní, protože jsou vytvořené Power Query k definování a nastavení zdroje dat.

Úprava vzorce pomocí dialogového okna

Tato metoda používá dialogová okna, která se liší v závislosti na kroku. Nemusíte znát syntaxi vzorce.

  1. Pokud chcete otevřít dotaz, vyhledejte dříve načtený z Editor Power Query, vyberte buňku v datech a pak vyberte Dotaz > Upravit. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excelu.

  2. V podokně Nastavení dotazu v části Použitý postup vyberte ikonu upravit nastavení Ikona Nastavení kroku, který chcete upravit, nebo klikněte pravým tlačítkem na krok a pak vyberte Upravit nastavení.

  3. V dialogovém okně proveďte změny a pak vyberte OK.

Vložení kroku

Po dokončení kroku dotazu, který mění tvar dat, se pod aktuální krok dotazu přidá. Když ale vložíte krok dotazu doprostřed kroků, může v následujících krocích dojít k chybě. Power Query zobrazí upozornění Vložit krok, když se pokusíte vložit nový krok a nový krok změní pole, například názvy sloupců, která se použijí v některém z kroků, které následují po vloženého kroku.

  1. V podokně Nastavení dotazů v části Použitý postup vyberte krok, který má bezprostředně předcházet novému kroku a odpovídajícímu vzorci.

  2. Vyberte ikonu Přidat krok Ikona Funkce vlevo od řádku vzorců. Případně klikněte pravým tlačítkem na krok a pak vyberte Vložit krok za. Vytvoří se nový vzorec ve formátu := <nameOfTheStepToReference>, například =Production.WorkOrder.

  3. Zadejte nový vzorec ve formátu:=Class.Function(ReferenceStep[,otherparameters]) Předpokládejme například, že máte tabulku se sloupcem Gender a chcete přidat sloupec s hodnotou "Ms". nebo "Pan.", v závislosti na pohlaví osoby. Vzorec by byl:=Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Příklad vzorce

Změna pořadí kroku

  • V podokně Nastavení dotazů v části Použitý postup klikněte pravým tlačítkem na krok a pak vyberte Přesunout nahoru nebo Přesunout dolů.

Odstranit krok

  • Vyberte ikonu Odstranit Odstranit krok nalevo od kroku nebo klikněte pravým tlačítkem myši na krok a pak vyberte Odstranit nebo Odstranit až do konce. Ikona Odstranit Odstranit krok je dostupná také nalevo od řádku vzorců.

V tomto příkladu převedeme text ve sloupci na velká písmena pomocí kombinace vzorců v Rozšířený editor. 

Máte například excelovou tabulku s názvem Objednávky se sloupcem ProductName, který chcete převést na velká písmena. 

Před:

Před

Po:

Krok 4 – výsledek

Při vytváření rozšířeného dotazu vytvoříte řadu kroků vzorce dotazu na základě výrazu let. Výraz let slouží k přiřazení názvů a výpočtu hodnot, na které pak odkazuje klauzule in , která definuje krok. Tento příklad vrátí stejný výsledek jako v části Vytvoření vzorce v řádku vzorců.

let       Source = Text.Proper("hello world") in       Source  

Uvidíte, že každý krok vychází z předchozího kroku a odkazuje na krok po názvu. Připomínáme, že jazyk vzorců Power Query rozlišuje velká a malá písmena.

Fáze 1: Otevřete Rozšířený editor

  1. V Excelu vyberte Data > Získat data > Jiné zdroje > prázdný dotaz. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excelu.

  2. V Editor Power Query vyberte Domů > Rozšířený editor, která se otevře se šablonou výrazu let.

Rozšířený editor2

Fáze 2: Definování zdroje dat

  1. Pomocí funkce Excel.CurrentWorkbook vytvořte výraz let následujícím způsobem:let    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]in      Source#x4

  2. Pokud chcete dotaz načíst do listu, vyberte Hotovo a pak vyberte Domů > Zavřít & Načíst > Zavřít & Načíst.

Výsledek

Krok 1 – výsledek

Fáze 3: Zvýšení úrovně prvního řádku na záhlaví

  1. Dotaz otevřete tak, že na listu vyberete buňku v datech a pak vyberete Dotaz > Upravit. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excelu (Power Query).

  2. V Editor Power Query vyberte Domů > Rozšířený editor, který se otevře s příkazem, který jste vytvořili ve fázi 2: Definování zdroje dat.

  3. Do výrazu let přidejte #"First Row as Header" a funkci Table.PromoteHeaders následujícím způsobem:let      Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],    #x4#"First Row as Header" = Table.PromoteHeaders(Source)#x3

  4. Pokud chcete dotaz načíst do listu, vyberte Hotovo a pak vyberte Domů > Zavřít & Načíst > Zavřít & Načíst.

Výsledek

Krok 3 – výsledek

Fáze 4: Změna každé hodnoty ve sloupci na velikost písmen

  1. Dotaz otevřete tak, že na listu vyberete buňku v datech a pak vyberete Dotaz > Upravit. Další informace najdete v tématu Vytvoření, načtení nebo úprava dotazu v Excelu.

  2. V Editor Power Query vyberte Domů > Rozšířený editor, která se otevře s příkazem, který jste vytvořili ve fázi 3: Zvýšení úrovně prvního řádku na záhlaví.

  3. Ve výrazu let převeďte každou hodnotu sloupce ProductName na správný text pomocí funkce Table.TransformColumns s odkazem na předchozí krok vzorce dotazu First Row as Header, přidáním souboru #"Capitalized Each Word" do zdroje dat a následným přiřazením #"Capitalized Each Word" k výsledku in.let    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],     #"First Row as Header" = Table.PromoteHeaders(Source),     #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})in     #"Capitalized Each Word"

  4. Pokud chcete dotaz načíst do listu, vyberte Hotovo a pak vyberte Domů > Zavřít & Načíst > Zavřít & Načíst.

Výsledek

Krok 4 – výsledek

Můžete řídit chování řádku vzorců ve Editor Power Query pro všechny sešity.

Zobrazení nebo skrytí řádku vzorců

  1. Vyberte Možnosti a nastavení> souboru > Možnosti dotazu.

  2. V levém podokně v části GLOBAL vyberte Editor Power Query.

  3. V pravém podokně v části Rozložení vyberte nebo zrušte zaškrtnutí políčka Zobrazit řádek vzorců.

Zapnutí nebo vypnutí M IntelliSense

  1. Vyberte Možnosti a nastavení> soubor > Možnosti dotazu .

  2. V levém podokně v části GLOBAL vyberte Editor Power Query.

  3. V pravém podokně v části Vzorec vyberte nebo zrušte zaškrtnutí políčka Povolit M IntelliSense v řádku vzorců, rozšířeném editoru a dialogovém okně vlastního sloupce.

Poznámka    Změna tohoto nastavení se projeví při příštím otevření okna Editor Power Query.

Viz také

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

Vytvoření a vyvolání vlastní funkce

Použití seznamu Použitý postup (docs.com)

Použití vlastních funkcí (docs.com)

vzorce Power Query M (docs.com)

Zpracování chyb (docs.com)

Potřebujete další pomoc?

Chcete další možnosti?

Prozkoumejte výhody předplatného, projděte si školicí kurzy, zjistěte, jak zabezpečit své zařízení a mnohem více.

Komunity vám pomohou klást otázky a odpovídat na ně, poskytovat zpětnou vazbu a vyslechnout odborníky s bohatými znalostmi.