Pojmy „Power Query“, „Power Pivot“, „Power BI“ a další „schopnosti“ se stále častěji objevují v článcích a materiálech o aplikaci Microsoft Excel. Podle mých zkušeností ne každý jasně rozumí tomu, co se za těmito pojmy skrývá, jak jsou propojeny a jak mohou pomoci jednoduchému uživateli Excelu.
Pojďme si situaci vyjasnit.
Dotaz na napájení
V roce 2013 speciálně vytvořená skupina vývojářů v rámci společnosti Microsoft vydala bezplatný doplněk pro Excel. Dotaz na napájení (jiné názvy jsou Data Explorer, Get & Transform), které umí spoustu věcí užitečných pro každodenní práci:
- Nahrát data v Excelu z téměř 40 různých zdrojů, včetně databází (SQL, Oracle, Access, Teradata…), podnikových ERP systémů (SAP, Microsoft Dynamics, 1C…), internetových služeb (Facebook, Google Analytics, téměř jakékoli webové stránky).
- Sbírejte data z soubory všechny hlavní datové typy (XLSX, TXT, CSV, JSON, HTML, XML…), jednotlivě i hromadně – ze všech souborů v určené složce. Z excelových sešitů můžete automaticky stahovat data ze všech listů najednou.
- Uklidit přijatá data z „odpadu“: další sloupce nebo řádky, opakování, servisní informace v „záhlaví“, mezery navíc nebo netisknutelné znaky atd.
- Přeneste data do objednat: správná malá a velká písmena, čísla jako text, doplňte mezery, přidejte správnou „čepici“ tabulky, rozložte „lepivý“ text do sloupců a přilepte jej, rozdělte datum na součásti atd.
- všemi možnými způsoby přeměnit tabulky, převést je do požadované podoby (filtrovat, třídit, měnit pořadí sloupců, transponovat, přidávat součty, rozbalit křížové tabulky na ploché a sbalit zpět).
- Nahraďte data z jedné tabulky do druhé pomocí shody jednoho nebo více parametrů, tj. pěkná náhradní funkce VPR (VYHLEDAT) a jeho analogy.
Power Query se nachází ve dvou verzích: jako samostatný doplněk pro Excel 2010-2013, který lze stáhnout z oficiálních stránek Microsoftu, a jako součást Excelu 2016. V prvním případě se po instalaci zobrazí samostatná karta v Vynikat:
V Excelu 2016 jsou všechny funkce Power Query již ve výchozím nastavení integrovány a jsou na kartě Data (Datum) jako skupina Získejte a převeďte (Získat a transformovat):
Možnosti těchto možností jsou zcela totožné.
Základním rysem Power Query je, že všechny akce pro import a transformaci dat se ukládají ve formě dotazu – sekvence kroků v interním programovacím jazyce Power Query, který se stručně nazývá „M“. Kroky lze vždy upravit a přehrát libovolněkrát (obnovit dotaz).
Hlavní okno Power Query obvykle vypadá nějak takto:
Podle mého názoru se jedná o nejužitečnější doplněk uvedený v tomto článku pro širokou škálu uživatelů. Spoustu úkolů, u kterých jste museli buď strašně zkreslovat vzorce, nebo psát makra, se teď v Power Query dělá snadno a krásně. Ano a s následnou automatickou aktualizací výsledků. A vzhledem k tomu, že je zdarma, z hlediska poměru ceny a kvality je Power Query prostě mimo konkurenci a v dnešní době je pro každého středně pokročilého uživatele Excelu naprostou nutností.
Powerpivot
Power Pivot je také doplněk pro Microsoft Excel, ale určený pro trochu jiné úkoly. Pokud je Power Query zaměřen na import a zpracování, pak je Power Pivot potřeba hlavně pro komplexní analýzu velkého množství dat. Jako první přiblížení si můžete Power Pivot představit jako efektní kontingenční tabulku.
Obecné principy práce v Power Pivot jsou následující:
- Jsme první načítání dat v Power Pivot – je podporováno 15 různých zdrojů: běžné databáze (SQL, Oracle, Access …), soubory Excel, textové soubory, datové kanály. Navíc můžete Power Query použít jako zdroj dat, díky čemuž je analýza téměř všežravá.
- Pak mezi načtenými stoly připojení jsou nakonfigurována nebo, jak se říká, je stvořeno Datový model. To v budoucnu umožní vytvářet sestavy pro jakákoli pole z existujících tabulek, jako by to byla jedna tabulka. A zase žádný VPR.
- V případě potřeby jsou do datového modelu přidány další výpočty pomocí vypočítané sloupce (podobně jako sloupec se vzorci v „inteligentní tabulce“) a opatření (obdoba počítaného pole v souhrnu). To vše je napsáno ve speciálním interním jazyce Power Pivot zvaném DAX (Data Analysis eXpressions).
- Na listu Excel jsou podle datového modelu sestaveny ve formuláři zprávy, které nás zajímají kontingenční tabulky a diagramy.
Hlavní okno Power Pivot vypadá asi takto:
A takto vypadá Datový model, tedy všechny načtené tabulky s vytvořenými vztahy:
Power Pivot má řadu funkcí, které z něj dělají jedinečný nástroj pro některé úkoly:
- V Power Pivot žádný limit linky (jako v Excelu). Můžete si načíst tabulky libovolné velikosti a snadno s nimi pracovat.
- Power Pivot je velmi dobrý komprimovat data při jejich načítání do modelu. Původní textový soubor o velikosti 50 MB se po stažení snadno změní na 3–5 MB.
- Vzhledem k tomu, že „pod kapotou“ má Power Pivot ve skutečnosti plnohodnotný databázový stroj, poradí si s velkým množstvím informací velmi rychle. Potřebujete analyzovat 10-15 milionů záznamů a vytvořit souhrn? A to vše na starém notebooku? Žádný problém!
Power Pivot bohužel zatím není součástí všech verzí Excelu. Pokud máte Excel 2010, můžete si jej zdarma stáhnout z webu Microsoftu. Ale pokud máte Excel 2013-2016, pak vše závisí na vaší licenci, protože. v některých verzích je součástí (například Office Pro Plus) a v některých ne (Office 365 Home, Office 365 Personal atd.) Více si o tom můžete přečíst zde.
Power Maps
Tento doplněk se poprvé objevil v roce 2013 a původně se jmenoval GeoFlow. Je určen pro vizualizaci geodat, tedy číselných informací na geografických mapách. Počáteční data pro zobrazení jsou převzata ze stejného datového modelu Power Pivot (viz předchozí odstavec).
Demoverzi Power Map (mimochodem téměř nelišící se od té plné) je možné stáhnout zcela zdarma opět ze stránek Microsoftu. Plná verze je součástí některých balíčků Microsoft Office 2013-2016 spolu s Power Pivot – ve formě tlačítka 3D mapa Karta Vložit (Vložit — 3D mapa):
Klíčové vlastnosti Power Map:
- umístění může být jak plochý, tak objemný (zeměkoule).
- Můžete použít několik různých typy vizualizací (histogramy, bublinové grafy, tepelné mapy, plošné výplně).
- Můžete přidat měření času, tj. animovat proces a sledovat, jak se vyvíjí.
- Mapy se načítají ze služby Bing Maps, tj. K prohlížení potřebujete velmi rychlé připojení k internetu. Někdy jsou potíže se správným rozpoznáním adres, protože. názvy v datech se vždy neshodují s názvy Bing Maps.
- V plné (bez demo) verze Power Map můžete použít svou vlastní mapy ke stažení, například pro vizualizaci návštěvníků obchodního centra nebo cen bytů v bytovém domě přímo na plánu budovy.
- Na základě vytvořených geovizualizací můžete vytvářet videa přímo v Power Map (příklad) a později je sdílet s těmi, kdo nemají nainstalovaný doplněk, nebo je zahrnout do powerpointové prezentace.
mocenský pohled
Tento doplněk byl poprvé představen v Excelu 2013 a je navržen tak, aby oživil vaše data pomocí interaktivních grafů, grafů, map a tabulek. Někdy se k tomu používají termíny. přístrojová deska (přístrojová deska) or přístrojová deska (výsledková karta). Pointa je, že do souboru Excel můžete vložit speciální list bez buněk – snímek Power View, kam můžete na základě dat z datového modelu Power Pivot přidávat text, obrázky a spoustu různých typů vizualizací.
Bude to vypadat takto:
Nuance jsou zde:
- Počáteční data jsou převzata ze stejného místa – z datového modelu Power Pivot.
- Chcete-li pracovat s Power View, musíte si do počítače nainstalovat Silverlight – obdobu Flash od Microsoftu (zdarma).
Na webu Microsoftu je mimochodem velmi slušný kurz školení Power View v .
Power BI
Na rozdíl od předchozích Power BI není doplněk pro Excel, ale samostatný produkt, což je celá sada nástrojů pro obchodní analýzu a vizualizaci. Skládá se ze tří klíčových prvků:
1. Power BI Desktop – program pro analýzu a vizualizaci dat, který mimo jiné zahrnuje veškerou funkcionalitu doplňků Power Query a Power Pivot + vylepšené vizualizační mechanismy z Power View a Power Map. Můžete si jej zdarma stáhnout a nainstalovat z webu společnosti Microsoft.
V Power BI Desktopu můžete:
- Načíst data z konce 70 různých zdrojů (jako v Power Query + další konektory).
- vážou tabulky k modelování (jako v Power Pivot)
- Přidejte k datům další výpočty pomocí opatření и vypočítané sloupce na DAX (jako v Power Pivot)
- Vytvářejte krásná data interaktivní zprávy s různými typy vizualizací (velmi podobné Power View, ale ještě lepší a výkonnější).
- Publikovat vytvořili sestavy na webu služby Power BI (viz další bod) a sdíleli je s kolegy. Navíc je možné udělit různá práva (čtení, úpravy) různým lidem.
2. Online služba Power BI – zjednodušeně řečeno jde o web, kde vy a každý uživatel ve vaší společnosti budete mít svůj vlastní „sandbox“ (pracovní prostor), kam můžete nahrávat sestavy vytvořené v Power BI Desktopu. Kromě prohlížení je dokonce umožňuje upravovat a reprodukovat téměř všechny funkce Power BI Desktopu online. Můžete si zde také vypůjčit jednotlivé vizualizace ze zpráv jiných lidí a sbírat z nich vlastní autorské dashboardy.
Vypadá to takto:
3. Power BI Mobile je aplikace pro iOS / Android / Windows pro připojení ke Službě Power BI a pohodlné prohlížení (nikoli úpravy) vytvořených sestav a dashboardů přímo na obrazovce vašeho telefonu nebo tabletu. Stáhnout si jej můžete (zcela zdarma) zde.
Na iPhonu například výše vygenerovaný přehled vypadá takto:
A to vše při zachování interaktivity a animace + uvěznění za dotyk a kreslení na obrazovce perem. Velmi pohodlně. Business intelligence se tak stává dostupným všem klíčovým osobám společnosti kdykoli a kdekoli – stačí pouze přístup k internetu.
Cenové plány Power BI. Power BI Desktop a Mobile jsou zdarma hned po vybalení a většina funkcí služby Power BI je také zdarma. Takže pro osobní použití nebo použití v malé společnosti nemusíte za vše výše uvedené platit ani cent a můžete bezpečně zůstat v plánu Zdarma. Pokud chcete sdílet sestavy s kolegy a spravovat jejich přístupová práva, budete muset přejít na BESS (10 $ měsíčně na uživatele). Je tam ještě nějaké Premium – pro velké společnosti (> 500 uživatelů), které vyžadují oddělené úložiště a serverové kapacity pro data.
- Projekt Ganttův diagram v Excelu s Power Query
- Jak vytvořit databázi v Excelu pomocí Power Pivot
- Vizualizace pohybu po trase na mapě v Power Map