Jak přidat Power Query do Excelu: Průvodce krok za krokem

Power Query je neuvěřitelně užitečný nástroj pro uživatele Excelu, kteří chtějí importovat externí data, připojit se k nim a tvarovat je. Proces přidání nástroje do Excelu závisí na verzi, kterou používáte.

Uživatelé Excelu 2016 nebo novějšího nemusí Power Query přidávat do Excelu. Je integrován jako funkce Získat a transformovat data na kartě Data. Uživatelé s Excelem 2013 a 2010 by si měli stáhnout a nainstalovat doplněk Microsoft Power Query.

Budete používat tabulky z Excelu, soubory CSV, databáze a mnoho dalších možností. Od této chvíle vám výkonné funkce umožňují upravovat, transformovat a analyzovat data.

Obsah

• Jak nainstalovat doplněk Power Query do Excelu 2010 nebo 2013
• Excel Power Query pro Excel 2016 a novější verze
• Jak najít nástroje Power Query na kartě Data
  • 1. Získejte data
  • 2. Z textu/CSV
  • 3. Z webu
  • 4. Z tabulky/rozsahu
  • 5. Z obrázku
  • 6. Nejnovější zdroje
  • 7. Stávající připojení
• Jak přidat konektory Power Query do Excelu
• Jak přidat oprávnění Power Query do Excelu
• Jak získat přístup k editoru Power Query
  • Stuha A Karty
  • Podokno náhledu dat
  • Pokročilý editor
• 2 způsoby přizpůsobení Power Query
  • 1. Jak upravit nastavení dotazu
  • 2. Jak upravit globální možnosti
  • 3. Jak upravit možnosti zabezpečení a soukromí
• Jak přidat další nástroje pro vylepšení Power Query
  • 1. Power Pivot
  • 2. Power Map (3D mapy)
  • 3. LuckyTemplates
• Naše poslední slovo nebo dvě

Jak nainstalovat doplněk Power Query do Excelu 2010 nebo 2013

Upozorňujeme, že uživatelé počítačů Mac budou muset mít alespoň Excel 2016, protože Power Query není k dispozici pro předchozí verze Excelu v systému Mac OS. Uživatelé Windows mohou používat bezplatný doplněk z Excelu 2000.

Před stažením doplňku musíte vědět, zda máte 32bitovou nebo 64bitovou instalaci aplikace Excel. Stránka stahování se zeptá, které máte.

Obrázek níže ukazuje vybranou 64bitovou možnost.

Mezi roky 2010 a 2013 je menší rozdíl ve vyhledávání těchto informací. U obou verzí rozbalte nabídku Soubor a klikněte na možnost „Nápověda“. 32bitové/64bitové informace jsou pod:

• „O aplikaci Microsoft Excel“ v aplikaci Excel 2010

• „O Excelu“ v Excelu 2013

Proces instalace doplňku je v podstatě stejný pro obě verze. Chcete-li nainstalovat doplněk Power Query, postupujte takto:

1. Zavřete Excel.

2. Přejděte na oficiální stránku stahování společnosti Microsoft .

3. Klikněte na tlačítko „Stáhnout“.

4. Vyberte správnou verzi (32bitovou nebo 64bitovou) na základě vaší instalace aplikace Excel.

5. Spusťte stažený soubor a postupujte podle pokynů průvodce nastavením.

Pokud Excel během instalace zobrazí chybovou zprávu, zkontrolujte, zda máte nejnovější aktualizace pro vaši verzi.

Po dokončení instalace musíte doplněk v Excelu povolit, abyste viděli kartu Power Query.

1. Otevřete Excel.

2. Zvolte Soubor -> Možnosti.

3. Klikněte na „Doplňky“ v levém podokně.

4. V rozbalovací nabídce v dolní části okna vyberte „COM Add-ins“.

5. Zaškrtněte políčko vedle „Microsoft Power Query for Excel“ a klikněte na „OK“.

Power Query je nyní k dispozici na vlastní kartě na pásu karet Excel!

Excel Power Query pro Excel 2016 a novější verze

V Excelu 2016 a novějších verzích je nyní Power Query součástí pásu karet Excel. Chcete-li se k němu dostat, postupujte takto:

1. Otevřete Excel a vytvořte nový dokument nebo načtěte existující.

2. Přejděte na kartu „Data“.

3. Pro přístup k nástrojům Power Query použijte tlačítka ve skupině „Získat a transformovat data“.

Jak najít nástroje Power Query na kartě Data

Sekce „Získat a transformovat data“ na kartě Data nabízí tlačítka pro přístup k řadě nástrojů a funkcí, které jsou součástí Power Query v Excelu.

Zde je popis sedmi tlačítek a funkcí.

1. Získejte data

Tato funkce umožňuje připojit a importovat data z externích datových zdrojů. Některé z nejčastěji používaných možností jsou:

• Ze souboru: import z excelových sešitů, CSV, textových, XML nebo JSON souborů.

• Z databáze: import ze serveru SQL Server, Access, Oracle a dalších.

• Z Azure: import z Azure SQL Database, Azure Blob Storage a dalších.

2. Z textu/CSV

Tlačítko „Z textu/CSV“ umožňuje připojení a import dat z textového souboru nebo souboru CSV (hodnoty oddělené čárkami). Otevře se dialogové okno Import dat, ve kterém můžete vybrat soubor.

Ke stejnému souboru TXT nebo CSV můžete přistupovat pomocí rozevíracího seznamu Získat data. Tlačítko „Z textu/CSV“ poskytuje zkratku.

Obrázek níže ukazuje použitou zkratku. V okně se také zobrazí náhled dat obsažených v souboru.

3. Z webu

Toto je další zkratka k funkci dostupné v rozevíracím seznamu „Získat data“. Tlačítko „Z webu“ vám umožňuje připojit se a importovat data z webových stránek pomocí jejich adres URL.

4. Z tabulky/rozsahu

Toto tlačítko umožňuje převést vybranou oblast buněk nebo existující tabulku v excelovém sešitu na dotaz Power Query.

Když vyberete rozsah a kliknete na toto tlačítko, automaticky se otevře Editor Power Query. Tento editor je popsán v další části.

5. Z obrázku

Přestože je funkce „Z obrázku“ seskupena v části Power Query, ve skutečnosti není součástí Power Query.

Jedná se o samostatnou funkci, která umožňuje zachytit data z tištěné nebo ručně psané tabulky pomocí fotoaparátu vašeho zařízení a převést je do digitální tabulky v Excelu.

K rozpoznání a extrahování textu a čísel z obrázku využívá technologii optického rozpoznávání znaků (OCR).

6. Nejnovější zdroje

Toto tlačítko poskytuje rychlý přístup ke zdrojům dat, ke kterým jste se nedávno připojili a z nichž jste importovali data do Excelu. Funguje jako cenná úspora času pro vaše datové dotazy.

Když kliknete na tlačítko „Poslední zdroje“, rozbalovací nabídka zobrazí seznam vašich nedávno použitých zdrojů dat, jako jsou online připojení a soubory Excel.

7. Stávající připojení

To je další úspora času. Poskytuje vám seznam všech datových připojení, která byla dříve vytvořena ve vašem aktuálním sešitu.

Jak přidat konektory Power Query do Excelu

Mohou existovat některé pokročilé funkce nebo konektory Power Query, které jsou dostupné pouze ve specifických edicích Excelu, jako je Excel 2016 Professional Plus nebo Excel pro Microsoft Office 365.

Například konektor SharePoint, který umožňuje připojení ke složkám SharePoint, je k dispozici pouze v rámci podnikových předplatných.

Pokud máte předplatné pro domácnosti nebo firmy, možnost v seznamu konektorů neuvidíte.

Níže uvedený výňatek ze seznamu dostupnosti společnosti Microsoft ukazuje, že připojení SharePoint a PDF nejsou k dispozici v každé edici.

Pokud vám chybí konektory a zdroje, které potřebujete, zvažte upgrade na vyšší edici. Tím povolíte chybějící možnosti.

Jak přidat oprávnění Power Query do Excelu

Mohou existovat určité konektory nebo funkce, které vyžadují další oprávnění nebo nastavení, jako jsou zdroje dat specifické pro organizaci.

Pokud narazíte na problémy s připojením ke konkrétním zdrojům dat nebo používáním určitých funkcí, může to být způsobeno oprávněními nebo nastaveními na úrovni organizace. V takových případech můžete požádat o pomoc správce IT nebo tým podpory vaší organizace.

Pokud poskytují vašemu účtu Windows další oprávnění, měli byste restartovat Excel, abyste získali přístup ke zvýšeným oprávněním.

Jak získat přístup k editoru Power Query

Je to místo, kde strávíte většinu času prací s Power Query. Jedná se o grafické rozhraní, ve kterém můžete vytvářet, upravovat a spravovat své dotazy.

Editor také poskytuje vizuální znázornění struktury vašeho dotazu a podporuje několik různých zobrazení.

Do editoru se lze dostat několika způsoby. Často nastavujete připojení nebo importujete soubor, čímž se spustí Editor.

Případně můžete otevřít Editor jako prázdný dotaz a nastavit připojení z něj. Zde je návod, jak to provést ve skupině Získat a transformovat data:

1. Přejděte na kartu Data.

2. Klikněte na „Získat data“ ve skupině „Získat a transformovat data“.

3. Vyberte „Spustit editor Power Query“.

4. Pro přístup ke svým datům použijte možnosti na pravé straně pásu karet.

Skupina možností „Nový dotaz“ je zcela vpravo na pásu karet. Odtud můžete importovat soubor, připojit se k databázi a dokonce zadávat data ručně.

Stuha A Karty

Pás karet v editoru Power Query obsahuje několik karet s možnostmi uspořádanými do skupin pro snadný přístup.

Tyto karty slouží k různým účelům a poskytují nástroje a funkce, které budete při práci s Power Query potřebovat. Čtyři záložky jsou:

• Domov

• Přeměnit

• Přidat sloupec

• Pohled

Tento obrázek ukazuje karty v horní části okna. Náhled dat pod pásem karet zobrazuje obsah tabulky aplikace Excel. Panel Dotazy je vlevo.

Podokno náhledu dat

Podokno náhledu dat nezobrazuje pouze vaše data. V tomto okně můžete změnit záhlaví sloupce a typ dat.

Řádek vzorců v horní části podokna zobrazuje speciální kód za databázovým dotazem.

Pokročilý editor

Pro složitější operace nebo přizpůsobení nabízí Power Query pokročilý editor. Tento editor umožňuje přímo upravovat M kód, což je základní jazyk Power Query.

Provedením změn v kódu M můžete doladit své dotazy a získat další funkce, které nemusí být dostupné prostřednictvím grafického rozhraní.

Přístup k pokročilému editoru:

1. Přejděte na kartu Domů v editoru Power Query.

2. Klikněte na „Pokročilý editor“ ve skupině Dotaz.

Pokud chcete prozkoumat některé aspekty kódu, zde jsou některé tutoriály:

Zde je video návod k použití každého výrazu.

2 způsoby přizpůsobení Power Query

Své prostředí Power Query si můžete přizpůsobit:

• úprava nastavení dotazu

• správa globálních možností a možností ochrany osobních údajů

1. Jak upravit nastavení dotazu

Přístup k nastavení dotazu:

1. Otevřete Editor Power Query.

2. Zobrazte nastavení v panelu na pravé straně.

3. Klikněte na odkaz „Všechny vlastnosti“.

4. Podle potřeby změní podrobnosti ve vyskakovacím okně.

Můžete upravit několik aspektů svého dotazu Microsoft, například:

• Název: přejmenujte svůj dotaz na něco popisnějšího.

• Popis: přidáním popisu poskytnete více kontextu.

• Načíst nastavení: zvolte, zda chcete načíst data do datového modelu nebo pouze jako připojení.

Na obrázku níže měním název „stůl“ na informativnější „prodej“ a přidávám popis.

2. Jak upravit globální možnosti

Power Query také umožňuje přizpůsobit celkové prostředí. Chcete-li získat přístup k těmto možnostem, postupujte takto:

1. V Excelu přejděte na kartu Data.

2. Klikněte na rozevírací nabídku Získat data.

3. V dolní části vyberte Možnosti dotazu.

Otevře se dialogové okno Možnosti dotazu. Zde můžete změnit rozvržení a chování editoru Power Query zaškrtnutím nebo zrušením zaškrtnutí políčka.

3. Jak upravit možnosti zabezpečení a soukromí

Možnosti dotazu zahrnují sekci zabezpečení a ochrany osobních údajů.

Sekce zabezpečení vám umožňuje ovládat kontroly certifikátů a webová varování, pokud se vaše dotazy obracejí na online externí zdroj dat.

V části Ochrana osobních údajů můžete nakonfigurovat, jak Power Query zpracovává a chrání citlivá data.

Jak přidat další nástroje pro vylepšení Power Query

Existují další doplňky a nástroje, které můžete použít k rozšíření funkčnosti Power Query. Zde jsou tři oblíbené možnosti:

1. Power Pivot

Power Pivot je výkonný nástroj pro modelování a analýzu dat zabudovaný do novějších verzí Excelu, které dobře fungují s Power Query.

Umožňuje vám:

• vytvářet komplexní datové modely.

• spravovat vztahy mezi tabulkami.

• provádět pokročilé výpočty pomocí výrazů analýzy dat (DAX).

Pokud používáte Excel pro Microsoft 365, Excel 2016 nebo Excel 2019, povolte Power Pivot pro své kontingenční tabulky takto:

1. Přejděte na kartu Soubor.

2. Výběrem možnosti Možnosti otevřete dialogové okno Možnosti aplikace Excel.

3. Klikněte na kategorii „Doplňky“ v levém podokně.

4. Z rozbalovací nabídky v dolní části vyberte „COM Addins“ a klikněte na „Go“.

5. Najděte v seznamu „Microsoft Power Pivot pro Excel“ a zaškrtněte políčko vedle něj.

6. Kliknutím na „OK“ povolíte Power Pivot.

Po povolení Power Pivot se na pásu karet Excelu objeví nová karta Power Pivot. Dalším krokem je naučit se pracovat s kontingenční tabulkou pomocí okna Power Pivot.

Tento obrázek ukazuje Power Pivot i Power Map v okně doplňků:

2. Power Map (3D mapy)

Power Map, také známý jako 3D mapy, je nástroj pro vizualizaci dat pro Excel, který vám umožňuje vytvářet interaktivní 3D geoprostorové vizualizace pomocí vašich dat. Je to skvělý způsob, jak vizualizovat geografická data z Power Query.

Pokud používáte Excel pro Microsoft 365, Excel 2016 nebo Excel 2019, povolte Power Map takto:

1. Přejděte na kartu „Vložit“.

2. Klikněte na tlačítko „3D mapa“ ve skupině „Prohlídky“.

3. Pokud budete požádáni o povolení doplňků analýzy dat, klikněte na „Povolit“.

Otevře se okno 3D mapy, kde můžete:

• vizualizujte svá geoprostorová data ve třech rozměrech

• vytvářet interaktivní prohlídky

• přizpůsobte vzhled svých map.

3. LuckyTemplates

které vám mohou pomoci analyzovat a vizualizovat data.

Toto není doplněk pro Excel. Místo toho je to samostatná samostatná aplikace s názvem LuckyTemplates Desktop. Lze jej nainstalovat na Windows 10 nebo novější a vyžaduje 64bitový OS.

Uživatelé Excel Mac mohou používat službu LuckyTemplates, což je cloudová verze LuckyTemplates, která běží ve vašem webovém prohlížeči.

Naše poslední slovo nebo dvě

Nyní jste se naučili, jak přidat nebo získat přístup k nástroji Power Query v listu aplikace Excel. Tento nástroj vám umožňuje provádět komplexní obchodní analýzu pomocí několika kliknutí.

Možná se budete cítit trochu zahlceni všemi funkcemi na kartě transformace a v editoru. Zkuste vytvořit nový databázový dotaz nebo provést načtení dat z textových souborů, abyste si procvičili transformaci datových sad.

Brzy budete na cestě k využití síly Power Query.