Pojmy „Power Query“, „Power Pivot“, „Power BI“ a ďalšie „mocnosti“ sa čoraz častejšie objavujú v článkoch a materiáloch o programe Microsoft Excel. Podľa mojich skúseností nie každý jasne chápe, čo sa skrýva za týmito pojmami, ako sú navzájom prepojené a ako môžu pomôcť jednoduchému používateľovi Excelu.
Vyjasnime si situáciu.
mocenský dotaz
V roku 2013 špeciálne vytvorená skupina vývojárov v rámci spoločnosti Microsoft vydala bezplatný doplnok pre Excel. mocenský dotaz (iné názvy sú Data Explorer, Get & Transform), ktorý dokáže veľa vecí užitočných pre každodennú prácu:
- nahrať dáta v Exceli z takmer 40 rôznych zdrojov, vrátane databáz (SQL, Oracle, Access, Teradata…), podnikových ERP systémov (SAP, Microsoft Dynamics, 1C…), internetových služieb (Facebook, Google Analytics, takmer všetky webové stránky).
- Zbierajte údaje z Súbory všetky hlavné typy údajov (XLSX, TXT, CSV, JSON, HTML, XML…), jednotlivo aj hromadne – zo všetkých súborov v zadanom priečinku. Z excelových zošitov môžete automaticky sťahovať údaje zo všetkých hárkov naraz.
- upratať prijaté údaje z „odpadu“: ďalšie stĺpce alebo riadky, opakovania, servisné informácie v „hlavičke“, medzery navyše alebo netlačiteľné znaky atď.
- Preneste údaje do poriadok: správne malé a veľké písmená, čísla ako text, doplňte medzery, pridajte správnu „čiastku“ tabuľky, „lepivý“ text analyzujte do stĺpcov a prilepte ho späť, rozdeľte dátum na komponenty atď.
- všetkými možnými spôsobmi premeniť tabuľky, priviesť ich do požadovaného tvaru (filtrovať, triediť, meniť poradie stĺpcov, transponovať, pridávať súčty, rozširovať krížové tabuľky na ploché a zbaliť späť).
- Nahraďte údaje z jednej tabuľky do druhej prispôsobením jedného alebo viacerých parametrov, tj pekná náhradná funkcia VPR (VLOOKUP) a jeho analógy.
Power Query sa nachádza v dvoch verziách: ako samostatný doplnok pre Excel 2010-2013, ktorý je možné stiahnuť z oficiálnej stránky Microsoftu, a ako súčasť Excelu 2016. V prvom prípade sa po inštalácii zobrazí samostatná karta v Excel:
V Exceli 2016 sú všetky funkcie Power Query už predvolene zabudované a sú na karte dátum (Dátum) ako skupina Získajte a konvertujte (Získať a transformovať):
Možnosti týchto možností sú úplne totožné.
Základnou vlastnosťou Power Query je, že všetky akcie na import a transformáciu údajov sa ukladajú vo forme dotazu – postupnosti krokov v internom programovacom jazyku Power Query, ktorý sa stručne nazýva „M“. Kroky je možné kedykoľvek upraviť a prehrať ľubovoľný počet krát (obnoviť dotaz).
Hlavné okno Power Query zvyčajne vyzerá takto:
Podľa môjho názoru je to najužitočnejší doplnok uvedený v tomto článku pre široké spektrum používateľov. Množstvo úloh, pri ktorých ste museli buď strašne skresľovať vzorce alebo písať makrá, sa teraz v Power Query robí jednoducho a krásne. Áno, a s následnou automatickou aktualizáciou výsledkov. A ak vezmeme do úvahy, že je zadarmo, z hľadiska pomeru ceny a kvality je Power Query jednoducho mimo konkurencie a v dnešnej dobe je absolútnou nevyhnutnosťou každého stredne pokročilého používateľa Excelu.
powerpivot
Power Pivot je tiež doplnkom pre Microsoft Excel, ale určený pre trochu iné úlohy. Ak je Power Query zameraný na import a spracovanie, Power Pivot je potrebný hlavne na komplexnú analýzu veľkého množstva údajov. Ako prvé priblíženie si môžete Power Pivot predstaviť ako efektnú kontingenčnú tabuľku.
Všeobecné princípy práce v Power Pivot sú nasledovné:
- Sme prvý načítavanie údajov v Power Pivot – podporovaných je 15 rôznych zdrojov: bežné databázy (SQL, Oracle, Access…), súbory Excel, textové súbory, dátové kanály. Okrem toho môžete Power Query použiť ako zdroj údajov, vďaka čomu je analýza takmer všežravá.
- Potom medzi načítanými stolmi pripojenia sú nakonfigurované alebo, ako sa hovorí, je stvorený Dátový model. To v budúcnosti umožní vytvárať zostavy pre ľubovoľné polia z existujúcich tabuliek, ako keby to bola jedna tabuľka. A opäť žiadne VPR.
- V prípade potreby sa do dátového modelu pridajú ďalšie výpočty pomocou vypočítané stĺpce (podobne ako stĺpec so vzorcami v „inteligentnej tabuľke“) a Opatrenia (analóg počítaného poľa v súhrne). To všetko je napísané v špeciálnom internom jazyku Power Pivot s názvom DAX (Data Analysis eXpressions).
- Na hárku Excel sú podľa dátového modelu zostavené zostavy, ktoré nás zaujímajú, vo formulári kontingenčné tabuľky a diagramy.
Hlavné okno Power Pivot vyzerá asi takto:
A takto vyzerá Dátový model, teda všetky načítané tabuľky s vytvorenými vzťahmi:
Power Pivot má množstvo funkcií, ktoré z neho robia jedinečný nástroj pre niektoré úlohy:
- V doplnku Power Pivot žiadny limit linky (ako v Exceli). Môžete si načítať stoly ľubovoľnej veľkosti a jednoducho s nimi pracovať.
- Power Pivot je veľmi dobrý komprimovať dáta pri ich načítavaní do modelu. Pôvodný textový súbor s veľkosťou 50 MB sa po stiahnutí ľahko zmení na 3 – 5 MB.
- Keďže má Power Pivot „pod kapotou“ v skutočnosti plnohodnotný databázový stroj, poradí si s veľkým množstvom informácií veľmi rýchlo. Potrebujete analyzovať 10-15 miliónov záznamov a vytvoriť súhrn? A to všetko na starom notebooku? Žiaden problém!
Bohužiaľ, Power Pivot ešte nie je súčasťou všetkých verzií Excelu. Ak máte Excel 2010, môžete si ho bezplatne stiahnuť z webovej stránky spoločnosti Microsoft. Ale ak máte Excel 2013-2016, potom všetko závisí od vašej licencie, pretože. v niektorých verziách je zahrnutá (napríklad Office Pro Plus) a v niektorých nie (Office 365 Home, Office 365 Personal atď.) Viac si o tom môžete prečítať tu.
Power Maps
Tento doplnok sa prvýkrát objavil v roku 2013 a pôvodne sa volal GeoFlow. Je určený na vizualizáciu geodát, teda číselných informácií na geografických mapách. Počiatočné údaje na zobrazenie sú prevzaté z rovnakého dátového modelu Power Pivot (pozri predchádzajúci odsek).
Demoverziu Power Map (mimochodom takmer nelíši od plnej) si môžete opäť stiahnuť úplne zadarmo zo stránky Microsoftu. Plná verzia je súčasťou niektorých balíkov Microsoft Office 2013-2016 spolu s Power Pivot – vo forme tlačidla 3D mapa pútko Vložiť (Vložiť — 3D mapa):
Kľúčové vlastnosti Power Map:
- mapy môže byť plochý aj objemný (guľatina).
- Môžete použiť niekoľko rôznych typy vizualizácií (histogramy, bublinové grafy, tepelné mapy, plošné výplne).
- Môžete pridať meranie času, teda animovať proces a sledovať, ako sa vyvíja.
- Mapy sa načítajú zo služby Bing Maps, tj Na prezeranie potrebujete veľmi rýchle internetové pripojenie. Niekedy sú problémy so správnym rozpoznávaním adries, pretože. názvy v údajoch sa nie vždy zhodujú s názvami v Bing Maps.
- V plnej (nie demo) verzii Power Map môžete použiť svoju vlastnú mapy na stiahnutie, napríklad na vizualizáciu návštevníkov nákupného centra alebo cien bytov v bytovom dome priamo na pláne budovy.
- Na základe vytvorených geovizualizácií môžete vytvárať videá priamo v Power Map (príklad) a neskôr ich zdieľať s tými, ktorí nemajú nainštalovaný doplnok, alebo ich zahrnúť do prezentácie v Power Point.
mocenský pohľad
Tento doplnok bol prvýkrát predstavený v Exceli 2013 a je navrhnutý tak, aby oživil vaše údaje pomocou interaktívnych grafov, tabuliek, máp a tabuliek. Niekedy sa na to používajú výrazy. prístrojová doska (hlavná doska) or prístrojová doska (výsledková karta). Pointa je, že do súboru programu Excel môžete vložiť špeciálny hárok bez buniek – snímku Power View, kde môžete pridať text, obrázky a množstvo rôznych typov vizualizácií na základe údajov z dátového modelu Power Pivot.
Vyzerá to takto:
Tu sú nuansy:
- Počiatočné údaje sa preberajú z rovnakého miesta – z dátového modelu Power Pivot.
- Ak chcete pracovať s Power View, musíte si do počítača nainštalovať Silverlight – analóg Flash od Microsoftu (zadarmo).
Na stránke Microsoftu je mimochodom veľmi slušný školiaci kurz o Power View v .
Power BI
Na rozdiel od predchádzajúcich, Power BI nie je doplnkom pre Excel, ale samostatným produktom, ktorý predstavuje celú sadu nástrojov na podnikovú analýzu a vizualizáciu. Pozostáva z troch kľúčových prvkov:
1. Power BI Desktop – program na analýzu a vizualizáciu dát, ktorý okrem iného obsahuje všetky funkcie doplnkov Power Query a Power Pivot + vylepšené vizualizačné mechanizmy z Power View a Power Map. Môžete si ho bezplatne stiahnuť a nainštalovať z webovej lokality spoločnosti Microsoft.
V aplikácii Power BI Desktop môžete:
- Načítajte údaje od konca 70 rôznych zdrojov (ako v Power Query + ďalšie konektory).
- viažu tabuľky na modelovanie (ako v doplnku Power Pivot)
- Pridajte k údajom ďalšie výpočty pomocou Opatrenia и vypočítané stĺpce v jazyku DAX (ako v Power Pivote)
- Vytvorte si krásne dáta interaktívne správy s rôznymi typmi vizualizácií (veľmi podobné Power View, ale ešte lepšie a výkonnejšie).
- Publikovať vytvorili zostavy na lokalite služby Power BI (pozri ďalší bod) a zdieľali ich s kolegami. Okrem toho je možné udeliť rôzne práva (čítanie, úpravy) rôznym ľuďom.
2. Online služba Power BI – zjednodušene povedané, toto je stránka, na ktorej budete mať vy a každý používateľ vo vašej spoločnosti svoj vlastný „sandbox“ (pracovný priestor), kde môžete nahrávať zostavy vytvorené v aplikácii Power BI Desktop. Okrem prezerania vám ich dokonca umožňuje upravovať, čím sa reprodukujú takmer všetky funkcie aplikácie Power BI Desktop online. Môžete si tu tiež požičať jednotlivé vizualizácie z prehľadov iných ľudí a zbierať z nich vlastné autorské dashboardy.
Vyzerá to asi takto:
3. Power BI Mobile je aplikácia pre iOS / Android / Windows na pripojenie k Službe Power BI a pohodlné prezeranie (nie úpravu) vytvorených zostáv a dashboardov priamo na obrazovke telefónu alebo tabletu. Môžete si ho stiahnuť (úplne zadarmo) tu.
Napríklad na iPhone vyzerá prehľad vygenerovaný vyššie takto:
A to všetko pri zachovaní interaktivity a animácie + uväznené na dotyk a kreslenie na obrazovke perom. Veľmi pohodlne. Business Intelligence sa tak stáva dostupným pre všetky kľúčové osoby spoločnosti kedykoľvek a kdekoľvek – je potrebný iba prístup na internet.
Cenové plány Power BI. Power BI Desktop a Mobile sú zadarmo hneď po vybalení a väčšina funkcií služby Power BI je tiež zadarmo. Takže na osobné použitie alebo použitie v rámci malej spoločnosti nemusíte platiť ani cent za všetky vyššie uvedené a môžete bezpečne zostať v pláne zdarma. Ak chcete zdieľať prehľady s kolegami a spravovať ich prístupové práva, budete musieť prejsť na BESS (10 USD mesačne na používateľa). Je tam ešte nejaké Prémiový – pre veľké spoločnosti (> 500 používateľov), ktoré vyžadujú samostatné úložisko a serverové kapacity pre dáta.
- Projekt Ganttov diagram v Exceli s Power Query
- Ako vytvoriť databázu v Exceli pomocou Power Pivot
- Vizualizácia pohybu po trase na mape v Power Map