Wat is Power Query / Pivot / Map / View / BI en hoekom het hulle 'n Excel-gebruiker nodig

Die terme "Power Query", "Power Pivot", "Power BI" en ander "powers" duik toenemend op in artikels en materiaal oor Microsoft Excel. In my ervaring verstaan ​​nie almal duidelik wat agter hierdie konsepte skuil, hoe hulle onderling verbind is en hoe dit 'n eenvoudige Excel-gebruiker kan help nie.

Kom ons verduidelik die situasie.

Kragnavraag

Terug in 2013 het 'n spesiaal geskepte groep ontwikkelaars binne Microsoft 'n gratis byvoeging vir Excel vrygestel. Kragnavraag (ander name is Data Explorer, Get & Transform), wat baie dinge nuttig kan doen vir alledaagse werk:

• Laai data in Excel van byna 40 verskillende bronne, insluitend databasisse (SQL, Oracle, Access, Teradata ...), korporatiewe ERP-stelsels (SAP, Microsoft Dynamics, 1C ...), Internetdienste (Facebook, Google Analytics, byna enige webwerwe).
• Versamel data van lêers alle belangrikste datatipes (XLSX, TXT, CSV, JSON, HTML, XML ...), beide afsonderlik en in grootmaat – van alle lêers in die gespesifiseerde vouer. Van Excel-werkboeke kan jy outomaties data vanaf alle blaaie gelyktydig aflaai.
  
• Maak skoon ontvang data van “rommel”: ekstra kolomme of rye, herhalings, diensinligting in die “kopskrif”, ekstra spasies of nie-drukbare karakters, ens.
• Bring data in einde: korrigeer hoofletters, getalle-as-teks, vul gapings in, voeg die korrekte “cap” van die tabel by, ontleed “taai” teks in kolomme en plak dit terug, verdeel die datum in komponente, ens.
  
• op elke moontlike manier transformeer tabelle, wat hulle in die verlangde vorm bring (filtreer, sorteer, verander die volgorde van kolomme, transponeer, voeg totale by, brei kruistabelle uit na plat en vou terug).
• Vervang data van een tabel na 'n ander deur een of meer parameters te pas, dws goeie vervangingsfunksie VPR (VERSOEKING) en sy analoë.

Power Query word in twee weergawes gevind: as 'n aparte byvoeging vir Excel 2010-2013, wat van die amptelike Microsoft-webwerf afgelaai kan word, en as deel van Excel 2016. In die eerste geval, na installasie, verskyn 'n aparte oortjie in Excel:

In Excel 2016 is al die funksionaliteit van Power Query reeds by verstek ingebou en is dit op die blad data (Datum) as 'n groep Kry en omskep (Kry en transformeer):

Die moontlikhede van hierdie opsies is heeltemal identies.

Die fundamentele kenmerk van Power Query is dat alle aksies vir die invoer en transformasie van data in die vorm van 'n navraag gestoor word - 'n reeks stappe in die interne Power Query-programmeertaal, wat bondig "M" genoem word. Stappe kan altyd geredigeer en herhaal word enige aantal kere (herlaai navraag).

Die hoof Power Query-venster lyk gewoonlik so:

Na my mening is dit die nuttigste byvoeging wat in hierdie artikel gelys word vir 'n wye verskeidenheid gebruikers. Baie take waarvoor jy óf vreeslik met formules moes verdraai óf makros moes skryf, word nou maklik en pragtig in Power Query gedoen. Ja, en met daaropvolgende outomatiese opdatering van die resultate. En aangesien dit gratis is, in terme van prys-kwaliteitverhouding, is Power Query eenvoudig buite kompetisie en 'n absolute moet-hê vir enige intermediêre gevorderde Excel-gebruiker deesdae.

kragspil

Power Pivot is ook 'n byvoeging vir Microsoft Excel, maar ontwerp vir effens verskillende take. As Power Query gefokus is op invoer en verwerking, dan is Power Pivot hoofsaaklik nodig vir komplekse ontleding van groot hoeveelhede data. As 'n eerste benadering kan jy aan Power Pivot dink as 'n spoggerige spiltabel.

Die algemene beginsels van werk in Power Pivot is soos volg:

1. Ons is die eerste laai data in Power Pivot – 15 verskillende bronne word ondersteun: algemene databasisse (SQL, Oracle, Access …), Excel-lêers, tekslêers, datavoere. Boonop kan u Power Query as 'n databron gebruik, wat die analise amper alleseter maak.
2. Dan tussen gelaaide tafels verbindings gekonfigureer word of, soos hulle sê, geskep word Datamodel. Dit sal dit moontlik maak om in die toekoms verslae te bou oor enige velde vanaf bestaande tabelle asof dit een tabel is. En weer geen VPR nie.
3. Indien nodig, word addisionele berekeninge by die Data Model gevoeg deur gebruik te maak van berekende kolomme (soortgelyk aan 'n kolom met formules in 'n "slim tabel") en maatreëls ('n analoog van die berekende veld in die opsomming). Dit alles is geskryf in 'n spesiale Power Pivot interne taal genaamd DAX (Data Analysis eXpressions).
4. Op die Excel-blad, volgens die Data Model, word die verslae van belang vir ons in die vorm gebou spiltafels en diagramme.

Die hoof Power Pivot-venster lyk so:

En dit is hoe die Data Model lyk, maw alle gelaaide tabelle met geskepde verhoudings:

Power Pivot het 'n aantal kenmerke wat dit 'n unieke hulpmiddel vir sommige take maak:

• In Power Pivot geen lynlimiet nie (soos in Excel). Jy kan tafels van enige grootte laai en maklik daarmee werk.
  
• Power Pivot is baie goed daarmee data saam te pers wanneer hulle in die Model laai. 'n Oorspronklike tekslêer van 50 MB kan maklik in 3-5 MB verander nadat dit afgelaai is.
• Aangesien Power Pivot "onder die enjinkap" eintlik 'n volwaardige databasis-enjin het, hanteer dit groot hoeveelhede inligting baie vinnig. Moet jy 10-15 miljoen rekords ontleed en 'n opsomming bou? En dit alles op 'n ou skootrekenaar? Geen probleem!

Ongelukkig is Power Pivot nog nie in alle weergawes van Excel ingesluit nie. As jy Excel 2010 het, kan jy dit gratis van die Microsoft-webwerf aflaai. Maar as jy Excel 2013-2016 het, hang dit alles af van jou lisensie, want. in sommige weergawes is dit ingesluit (Office Pro Plus, byvoorbeeld), en in sommige is dit nie (Office 365 Home, Office 365 Personal, ens.) Jy kan meer hieroor lees.

Kragkaarte

Hierdie byvoeging het die eerste keer in 2013 verskyn en is oorspronklik GeoFlow genoem. Dit is bedoel vir visualisering van geo-data, dws numeriese inligting op geografiese kaarte. Die aanvanklike data vir vertoon is geneem uit dieselfde Power Pivot Data Model (sien die vorige paragraaf).

Die demo-weergawe van Power Map (byna nie anders as die volledige een, terloops nie) kan heeltemal gratis weer van die Microsoft-webwerf afgelaai word. Die volledige weergawe is ingesluit in sommige Microsoft Office 2013-2016-pakkette saam met Power Pivot - in die vorm van 'n knoppie 3D-kaart tab Plaas (Voeg in — 3D-kaart):

Sleutel kenmerke van Power Map:

• Kaarte kan beide plat en volumineus (aardbol) wees.
• Jy kan verskeie verskillende gebruik tipes visualisering (histogramme, borrelkaarte, hittekaarte, areavulsels).
• Jy kan voeg tydmeting, dws animeer die proses en kyk hoe dit ontwikkel.
  
• Kaarte word vanaf die diens gelaai Bing Maps, dws Jy benodig 'n baie vinnige internetverbinding om te sien. Soms is daar probleme met die korrekte herkenning van adresse, want. die name in die data stem nie altyd ooreen met dié van Bing Maps nie.
  
• In die volledige (nie-demo) weergawe van Power Map kan jy jou eie gebruik aflaaibare kaarte, byvoorbeeld om besoekers aan 'n winkelsentrum of pryse vir woonstelle in 'n residensiële gebou reg op die bouplan te visualiseer.
• Op grond van die geskepte geo-visualisering, kan jy video's direk in Power Map skep (voorbeeld) om dit later te deel met diegene wat nie die byvoeging geïnstalleer het nie of dit in 'n Power Point-aanbieding insluit.
  

krag aansig

Hierdie byvoeging is eers in Excel 2013 bekendgestel en is ontwerp om jou data lewendig te maak met interaktiewe grafieke, kaarte, kaarte en tabelle. Soms word die terme hiervoor gebruik. dashboard (dashboard) or dashboard (telkaart). Die slotsom is dat jy 'n spesiale blad sonder selle in jou Excel-lêer kan invoeg – 'n Power View-skyfie, waar jy teks, prente en baie verskillende soorte visualiserings kan byvoeg gebaseer op jou data van die Power Pivot Data Model.

Dit sal so lyk:

Die nuanses hier is:

• Die aanvanklike data word van dieselfde plek geneem – van die Power Pivot Data Model.
• Om met Power View te werk, moet jy Silverlight op jou rekenaar installeer – Microsoft se analoog van Flash (gratis).
  

Op die Microsoft-webwerf, terloops, is daar 'n baie ordentlike opleidingskursus oor Power View in.

Krag BI

Anders as die voriges, is Power BI nie 'n byvoeging vir Excel nie, maar 'n aparte produk, wat 'n hele stel gereedskap vir besigheidsanalise en visualisering is. Dit bestaan ​​uit drie sleutelelemente:

1. Power BI Desktop – ’n program vir die ontleding en visualisering van data, wat onder andere al die funksionaliteit van die Power Query- en Power Pivot-byvoegings + verbeterde visualiseringsmeganismes van Power View en Power Map insluit. U kan dit gratis aflaai en installeer vanaf die Microsoft-webwerf.

In Power BI Desktop kan jy:

• Laai data van oor 70 verskillende bronne (soos in Power Query + addisionele verbindings).
• bind tabelle om te modelleer (soos in Power Pivot)
• Voeg bykomende berekeninge by data met maatreëls и berekende kolomme op DAX (soos in Power Pivot)
• Skep pragtige data gebaseer interaktiewe verslae met verskillende soorte visualisasies (baie soortgelyk aan Power View, maar selfs beter en kragtiger).
• Publiseer verslae op die Power BI Service-webwerf geskep (sien volgende punt) en dit met kollegas deel. Boonop is dit moontlik om verskillende regte (lees, redigering) aan verskillende mense te gee.
  

2. Power BI aanlyn diens – om dit eenvoudig te stel, dit is 'n webwerf waar jy en elke gebruiker in jou maatskappy hul eie "sandbox" (werkspasie) sal hê waar jy verslae kan oplaai wat in Power BI Desktop geskep is. Benewens kyk, laat dit jou selfs toe om hulle te redigeer, wat byna al die funksies van Power BI Desktop aanlyn weergee. Jy kan ook individuele visualiserings uit ander mense se verslae hier leen, en jou eie skrywer se dashboards daaruit versamel.

Dit lyk so iets:

3. Power BI Mobile is 'n toepassing vir iOS / Android / Windows om aan die Power BI-diens te koppel en die geskepte verslae en kontroleskerms gerieflik op die skerm van jou foon of tablet te bekyk (nie redigeer nie). Jy kan dit (heeltemal gratis) hier aflaai.

Op 'n iPhone, byvoorbeeld, lyk die verslag wat hierbo gegenereer is soos volg:

En dit alles met behoud van interaktiwiteit en animasie + gevange geneem vir aanraking en teken op die skerm met 'n pen. Baie gemaklik. Besigheidsintelligensie word dus op enige tyd en op enige plek vir alle sleutelpersone van die maatskappy beskikbaar – slegs internettoegang is nodig.

Power BI-prysplanne. Power BI Desktop en Mobile is gratis uit die boks, en die meeste Power BI Service-kenmerke is ook gratis. So vir persoonlike gebruik of gebruik binne 'n klein maatskappy hoef jy nie 'n sent te betaal vir al die bogenoemde nie en jy kan veilig op die plan bly Verniet. As jy verslae met kollegas wil deel en hul toegangsregte wil administreer, sal jy moet gaan na BES ($10 per maand per gebruiker). Is daar nog iets Premium – vir groot maatskappye (> 500 gebruikers) wat aparte berging en bedienerkapasiteit vir data benodig.

• Projek Gantt-grafiek in Excel met Power Query
• Hoe om 'n databasis in Excel te skep met behulp van Power Pivot
• Visualisering van beweging langs die roete op die kaart in Power Map