Mis on Power Query / Pivot / Map / View / BI ja miks nad vajavad Exceli kasutajat

Mõisteid "Power Query", "Power Pivot", "Power BI" ja muud "volitused" ilmuvad Microsoft Exceli käsitlevates artiklites ja materjalides üha sagedamini. Minu kogemuse põhjal ei saa kõik selgelt aru, mis nende mõistete taga peitub, kuidas need omavahel seotud on ja kuidas need lihtsat Exceli kasutajat aidata saavad.

Teeme olukorra selgeks.

Toite päring

2013. aastal andis Microsofti spetsiaalselt loodud arendajate rühm Exceli jaoks välja tasuta lisandmooduli. Toite päring (muud nimed on Data Explorer, Get & Transform), mis võivad teha palju igapäevatöös kasulikke asju:

• Täiendava Exceli andmed peaaegu 40 erinevast allikast, sealhulgas andmebaasidest (SQL, Oracle, Access, Teradata…), ettevõtte ERP-süsteemidest (SAP, Microsoft Dynamics, 1C…), Interneti-teenustest (Facebook, Google Analytics, peaaegu kõik veebisaidid).
• Koguge andmeid aadressilt Faile kõik peamised andmetüübid (XLSX, TXT, CSV, JSON, HTML, XML…), nii üksikult kui ka hulgi – kõigist määratud kaustas olevatest failidest. Exceli töövihikutest saate automaatselt alla laadida andmeid kõigilt lehtedelt korraga.
  
• Koristage "prügist" saadud andmed: lisaveerud või -read, kordused, teenuseteave "päises", lisatühikud või mitteprinditavad märgid jne.
• Tooge andmed sisse et: korrigeerige suurtähti, numbrid-tekstina, täitke lüngad, lisage tabeli õige "kork", sõeluge "kleepuv" tekst veergudeks ja liimige tagasi, jagage kuupäev komponentideks jne.
  
• igal võimalikul viisil muutma tabelid, viies need soovitud kujule (filtreerige, sorteerige, muutke veergude järjekorda, transponeerige, lisage kogusummasid, laiendage risttabeleid tasaseks ja ahendage tagasi).
• Asendage andmed ühest tabelist teise, sobitades ühte või mitut parameetrit ehk kena asendusfunktsiooni VPR (OTSING) ja selle analoogid.

Power Query on saadaval kahes versioonis: eraldi lisandmoodulina Excel 2010-2013 jaoks, mille saab alla laadida ametlikult Microsofti veebisaidilt, ja osana Excel 2016. Esimesel juhul ilmub pärast installimist eraldi vahekaart Excel:

Rakenduses Excel 2016 on kõik Power Query funktsioonid juba vaikimisi sisse ehitatud ja vahekaardil kuupäev (Kuupäev) rühmana Hankige ja teisendage (Hangi ja teisenda):

Nende valikute võimalused on täiesti identsed.

Power Query põhiomadus seisneb selles, et kõik toimingud andmete importimiseks ja teisendamiseks salvestatakse päringu vormis – sisemises Power Query programmeerimiskeeles, mida lühidalt nimetatakse M-ks, toimingute jada. Samme saab alati redigeerida ja uuesti esitada suvalise arvu kordi (värskenda päringut).

Power Query põhiaken näeb tavaliselt välja umbes selline:

Minu arvates on see selles artiklis loetletud kõige kasulikum lisand paljudele kasutajatele. Paljud ülesanded, mille jaoks tuli kas kohutavalt valemitega moonutada või makrosid kirjutada, on nüüd Power Querys lihtsalt ja ilusti tehtud. Jah, ja tulemuste hilisema automaatse värskendamisega. Ja arvestades, et see on tasuta, siis hinna ja kvaliteedi suhte osas on Power Query lihtsalt konkurentsist väljas ja tänapäeval on see hädavajalik iga keskmise tasemega Exceli kasutaja jaoks.

PowerPivot

Power Pivot on ka Microsoft Exceli lisandmoodul, kuid mõeldud veidi erinevate ülesannete jaoks. Kui Power Query on keskendunud importimisele ja töötlemisele, siis Power Pivoti on vaja peamiselt suurte andmemahtude kompleksseks analüüsiks. Esimese ligikaudsusena võite mõelda Power Pivotile kui uhkele pöördetabelile.

Power Pivotis töötamise üldpõhimõtted on järgmised.

1. Me oleme esimesed andmete laadimine Power Pivotis – toetatakse 15 erinevat allikat: levinud andmebaasid (SQL, Oracle, Access jne), Exceli failid, tekstifailid, andmekanalid. Lisaks saab andmeallikana kasutada Power Queryt, mis muudab analüüsi peaaegu kõigesööjaks.
2. Siis laetud laudade vahel ühendused on konfigureeritud või, nagu öeldakse, on loodud Andmemudel. See võimaldab tulevikus koostada olemasolevate tabelite mis tahes väljade kohta aruandeid nii, nagu oleks see üks tabel. Ja jälle ei mingit VPR-i.
3. Vajadusel lisatakse Andmemudelile täiendavad arvutused kasutades arvutatud veerud (sarnane valemitega veerule "nutikas tabeli") ja meetmed (arvutusvälja analoog kokkuvõttes). Kõik see on kirjutatud spetsiaalses Power Pivoti sisekeeles nimega DAX (Data Analysis eXpressions).
4. Exceli lehel ehitatakse Andmemudeli järgi meid huvitavad aruanded vormile pöördlauad ja diagrammid.

Power Pivoti põhiaken näeb välja umbes selline:

Ja selline näeb välja andmemudel, st kõik laaditud tabelid koos loodud seostega:

Power Pivotil on mitmeid funktsioone, mis muudavad selle mõne ülesande jaoks ainulaadseks tööriistaks.

• Power Pivotis liinipiirang puudub (nagu Excelis). Saate laadida mis tahes suurusega tabeleid ja nendega hõlpsalt töötada.
  
• Power Pivot on väga hea tihendada andmeid nende mudelisse laadimisel. 50 MB originaaltekstifail võib pärast allalaadimist kergesti muutuda 3–5 MB suuruseks.
• Kuna "kapoti all" Power Pivotil on tegelikult täieõiguslik andmebaasimootor, saab see hakkama suure hulga teabega väga kiiresti. Kas on vaja analüüsida 10-15 miljonit kirjet ja koostada kokkuvõte? Ja seda kõike vanal sülearvutil? Pole probleemi!

Kahjuks pole Power Pivot veel kõigis Exceli versioonides kaasatud. Kui teil on Excel 2010, saate selle Microsofti veebisaidilt tasuta alla laadida. Aga kui teil on Excel 2013-2016, siis kõik sõltub teie litsentsist, sest. mõnes versioonis on see kaasas (Office Pro Plus näiteks), mõnes aga mitte (Office 365 Home, Office 365 Personal jne) Selle kohta saad täpsemalt lugeda siit.

Power Maps

See lisandmoodul ilmus esmakordselt 2013. aastal ja kandis algselt nime GeoFlow. See on mõeldud geoandmete ehk numbrilise info visualiseerimiseks geograafilistel kaartidel. Kuvamise algandmed on võetud samast Power Pivoti andmemudelist (vt eelmist lõiku).

Power Mapi demoversiooni (muide, peaaegu ei erine täisversioonist) saab uuesti täiesti tasuta alla laadida Microsofti veebisaidilt. Täisversioon sisaldub mõnes Microsoft Office 2013–2016 paketis koos Power Pivotiga – nupu kujul 3D kaart tab Sisesta (Sisesta – 3D-kaart):

Power Mapi põhifunktsioonid:

• kaardid võib olla nii lame kui ka mahukas (gloobus).
• Võite kasutada mitut erinevat visualiseerimise tüübid (histogrammid, mulldiagrammid, soojuskaardid, alatäidised).
• Saate lisada aja mõõtminest animeerige protsessi ja jälgige selle arengut.
  
• Kaardid laaditakse teenusest Bing Mapsst Vaatamiseks on vaja väga kiiret internetiühendust. Mõnikord on aadresside õige tuvastamisega raskusi, kuna. andmetes olevad nimed ei kattu alati Bing Mapsi nimedega.
  
• Power Mapi täisversioonis (mitte demo) saate kasutada oma versiooni allalaaditavad kaardid, näiteks visualiseerida kaubanduskeskuse külastajaid või elamu korterite hindu otse ehitusplaanil.
• Loodud geovisualisatsioonide põhjal saate luua videoid otse Power Mapis (näide), et neid hiljem jagada nendega, kellel pole lisandmoodulit installitud või lisada need Power Pointi esitlusse.
  

võimsusvaade

Esmakordselt Excel 2013-s tutvustatud lisandmoodul on loodud teie andmete interaktiivsete graafikute, diagrammide, kaartide ja tabelitega ellu äratamiseks. Mõnikord kasutatakse selleks termineid. armatuurlaud (armatuurlaud) or armatuurlaud (tulemuskaart). Põhimõte on see, et saate Exceli faili sisestada spetsiaalse lahtriteta lehe – Power View slaidi, kuhu saate Power Pivoti andmemudeli andmete põhjal lisada teksti, pilte ja palju erinevat tüüpi visualiseerimisi.

See näeb välja umbes selline:

Nüansid on siin järgmised:

• Algandmed on võetud samast kohast – Power Pivoti andmemudelist.
• Power View'ga töötamiseks peate oma arvutisse installima Silverlighti – Microsofti Flashi analoogi (tasuta).
  

Muide, Microsofti veebisaidil on Power View kohta väga korralik koolituskursus aastal.

Võimsus BI

Erinevalt eelmistest ei ole Power BI Exceli lisandmoodul, vaid eraldi toode, mis on terve komplekt tööriistu ärianalüüsiks ja visualiseerimiseks. See koosneb kolmest põhielemendist:

1. Power BI Desktop – programm andmete analüüsimiseks ja visualiseerimiseks, mis sisaldab muuhulgas kõiki Power Query ja Power Pivoti lisandmoodulite funktsioone + Power View ja Power Mapi täiustatud visualiseerimismehhanisme. Saate selle Microsofti veebisaidilt tasuta alla laadida ja installida.

Power BI Desktopis saate teha järgmist.

• Andmete laadimine otsast 70 erinevat allikat (nagu Power Querys + lisapistikud).
• siduda modelleeritavad tabelid (nagu Power Pivotis)
• Lisage andmetele täiendavaid arvutusi kasutades meetmed и arvutatud veerud DAX-is (nagu Power Pivotis)
• Looge ilus andmepõhine interaktiivsed aruanded erinevat tüüpi visualiseerimisega (väga sarnane Power View'ga, kuid veelgi parem ja võimsam).
• Avalda lõi Power BI teenuse saidil aruandeid (vt järgmist punkti) ja jaga neid kolleegidega. Veelgi enam, erinevatele inimestele on võimalik anda erinevaid õigusi (lugemine, toimetamine).
  

2. Power BI võrguteenus – Lihtsamalt öeldes on see sait, kus teil ja igal teie ettevõtte kasutajal on oma "liivakast" (tööala), kuhu saate Power BI Desktopis loodud aruandeid üles laadida. Lisaks vaatamisele võimaldab see isegi neid redigeerida, taasesitades peaaegu kõik Power BI Desktopi funktsioonid võrgus. Siin saate laenata ka individuaalseid visualiseerimisi teiste inimeste aruannetest, kogudes nendelt oma autori armatuurlauad.

See näeb välja umbes selline:

3. Power BI Mobile on iOS / Android / Windows rakendus Power BI teenusega ühenduse loomiseks ning loodud aruannete ja armatuurlaudade mugavaks vaatamiseks (mitte redigeerimiseks) otse oma telefoni või tahvelarvuti ekraanil. Saate selle (täiesti tasuta) alla laadida siit.

Näiteks iPhone'is näeb ülal loodud aruanne välja järgmine:

Ja seda kõike interaktiivsust ja animatsiooni säilitades + puudutuste ja pliiatsiga ekraanile joonistamise eest vangistatud. Väga mugav. Seega muutub äriteave kättesaadavaks kõigile ettevõtte võtmeisikutele igal ajal ja igas kohas – selleks on vaja ainult Interneti-juurdepääsu.

Power BI hinnaplaanid. Power BI Desktop ja Mobile on karbist välja võttes tasuta ning ka enamik Power BI teenuse funktsioone on tasuta. Nii et isiklikuks kasutamiseks või väikeses ettevõttes kasutamiseks ei pea te kõige eelneva eest sentigi maksma ja võite julgelt plaani juurde jääda tasuta. Kui soovite kolleegidega aruandeid jagada ja nende juurdepääsuõigusi hallata, peate minema aadressile Bess (10 dollarit kuus kasutaja kohta). Kas on veel preemia – suurettevõtetele (> 500 kasutajat), kes vajavad andmete jaoks eraldi salvestus- ja serverimahtu.

• Projekteerige Gantti diagramm Excelis Power Queryga
• Kuidas luua andmebaasi Excelis Power Pivoti abil
• Marsruudil liikumise visualiseerimine Power Mapi kaardil