Što je Power Query / Pivot / Map / View / BI i zašto im je potreban Excel korisnik

Izrazi “Power Query”, “Power Pivot”, “Power BI” i druge “moći” sve se češće pojavljuju u člancima i materijalima o Microsoft Excelu. Po mom iskustvu, ne razumiju svi jasno što se krije iza ovih pojmova, kako su međusobno povezani i kako mogu pomoći jednostavnom korisniku programa Excel.

Razjasnimo situaciju.

Upit snage

Davne 2013. godine, posebno stvorena grupa programera unutar Microsofta izdala je besplatni dodatak za Excel. Upit snage (drugi nazivi su Data Explorer, Get & Transform), koji može učiniti puno stvari korisnih za svakodnevni rad:

• Postavi podataka u Excelu iz gotovo 40 različitih izvora, uključujući baze podataka (SQL, Oracle, Access, Teradata…), korporativne ERP sustave (SAP, Microsoft Dynamics, 1C…), internetske usluge (Facebook, Google Analytics, gotovo sve web stranice).
• Prikupiti podatke od Slika sve glavne vrste podataka (XLSX, TXT, CSV, JSON, HTML, XML…), i pojedinačno i skupno – iz svih datoteka u navedenoj mapi. Iz Excelovih radnih knjiga možete automatski preuzeti podatke sa svih listova odjednom.
  
• pospremiti primljene podatke iz "smeća": dodatni stupci ili redovi, ponavljanja, servisne informacije u "zaglavlju", dodatni razmaci ili znakovi koji se ne mogu ispisati itd.
• Unesite podatke u kako: ispraviti velika i mala slova, brojeve-kao-tekst, popuniti praznine, dodati ispravnu "kapu" tablice, raščlaniti "ljepljivi" tekst u stupce i zalijepiti ga natrag, podijeliti datum na komponente, itd.
  
• na svaki mogući način transformirati tablice, dovodeći ih u željeni oblik (filtrirajte, sortirajte, promijenite redoslijed stupaca, transponirajte, dodajte ukupne iznose, proširite križne tablice na ravne i sažmite natrag).
• Zamjena podataka iz jedne tablice u drugu podudaranjem jednog ili više parametara, tj. zgodna funkcija zamjene VPR (VLOOKUP) i njegovi analozi.

Power Query postoji u dvije verzije: kao zasebni dodatak za Excel 2010-2013, koji se može preuzeti sa službene Microsoftove web stranice, i kao dio programa Excel 2016. U prvom slučaju, nakon instalacije, pojavljuje se zasebna kartica u Excel:

U programu Excel 2016 sve su funkcije Power Queryja već ugrađene prema zadanim postavkama i nalaze se na kartici Datum (Datum) kao grupa Dobiti i pretvoriti (Dohvati i transformiraj):

Mogućnosti ovih opcija potpuno su identične.

Temeljna značajka Power Queryja je da su sve radnje za uvoz i transformaciju podataka pohranjene u obliku upita – niza koraka u internom Power Query programskom jeziku, koji se sažeto naziva “M”. Koraci se uvijek mogu uređivati ​​i reproducirati neograničeni broj puta (upit za osvježavanje).

Glavni prozor Power Queryja obično izgleda ovako:

Po mom mišljenju, ovo je najkorisniji dodatak naveden u ovom članku za širok raspon korisnika. Puno zadataka za koje ste morali ili užasno iskriviti formulama ili pisati makronaredbe sada se jednostavno i lijepo obavljaju u Power Queryju. Da, uz naknadno automatsko ažuriranje rezultata. A s obzirom da je besplatan, u smislu omjera cijene i kvalitete, Power Query je jednostavno izvan konkurencije i apsolutni must-have za svakog srednje-naprednog korisnika programa Excel ovih dana.

powerpivot

Power Pivot također je dodatak za Microsoft Excel, ali dizajniran za malo drugačije zadatke. Ako je Power Query usmjeren na uvoz i obradu, tada je Power Pivot potreban uglavnom za složenu analizu velikih količina podataka. Kao prvu aproksimaciju, Power Pivot možete zamisliti kao otmjenu stožernu tablicu.

Opća načela rada u Power Pivotu su sljedeća:

1. Mi smo prva učitavanje podataka u Power Pivotu – podržano je 15 različitih izvora: uobičajene baze podataka (SQL, Oracle, Access…), Excel datoteke, tekstualne datoteke, izvori podataka. Osim toga, možete koristiti Power Query kao izvor podataka, što čini analizu gotovo svejednom.
2. Zatim između učitanih stolova veze su konfigurirane ili, kako kažu, nastaje Model podataka. To će u budućnosti omogućiti izradu izvješća o svim poljima iz postojećih tablica kao da je to jedna tablica. I opet nema VPR-a.
3. Ako je potrebno, dodatni izračuni dodaju se podatkovnom modelu pomoću izračunati stupci (slično stupcu s formulama u “pametnoj tablici”) i mjere (analog izračunatog polja u sažetku). Sve je to napisano u posebnom Power Pivot internom jeziku koji se zove DAX (Data Analysis eXpressions).
4. Na Excel listu, prema Data Modelu, grade se izvještaji koji nas zanimaju u formi stožerne stolove i dijagrami.

Glavni prozor Power Pivota izgleda otprilike ovako:

A ovako izgleda Data Model, odnosno sve učitane tablice sa kreiranim relacijama:

Power Pivot ima niz značajki koje ga čine jedinstvenim alatom za neke zadatke:

• U Power Pivotu bez ograničenja linije (kao u Excelu). Možete učitati tablice bilo koje veličine i lako raditi s njima.
  
• Power Pivot je vrlo dobar u komprimirati podatke prilikom učitavanja u model. Izvorna tekstualna datoteka od 50 MB može se lako pretvoriti u 3-5 MB nakon preuzimanja.
• Budući da "ispod haube" Power Pivot zapravo ima potpunu bazu podataka, nosi se s velikim količinama informacija vrlo brzo. Trebate analizirati 10-15 milijuna zapisa i napraviti sažetak? I sve to na starom laptopu? Nema problema!

Nažalost, Power Pivot još nije uključen u sve verzije programa Excel. Ako imate Excel 2010, možete ga besplatno preuzeti s Microsoftove web stranice. Ali ako imate Excel 2013-2016, onda sve ovisi o vašoj licenci, jer. u nekim verzijama je uključen (Office Pro Plus npr.), a u nekima nije (Office 365 Home, Office 365 Personal itd.) Više o tome možete pročitati ovdje.

Karte snage

Ovaj se dodatak prvi put pojavio 2013. godine i izvorno se zvao GeoFlow. Namijenjen je vizualizaciji geopodataka, odnosno numeričkih informacija na geografskim kartama. Početni podaci za prikaz preuzeti su iz istog Power Pivot podatkovnog modela (pogledajte prethodni odlomak).

Demo verzija Power Map-a (usput, gotovo se ne razlikuje od pune) može se ponovno potpuno besplatno preuzeti s Microsoftove web stranice. Puna verzija uključena je u neke pakete Microsoft Office 2013-2016 zajedno s Power Pivotom – u obliku gumba 3D karta kartica umetak (Umetni — 3D-karta):

Ključne značajke Power Map-a:

• Karte može biti i ravna i voluminozna (globus).
• Možete koristiti nekoliko različitih vrste vizualizacije (histogrami, dijagrami s mjehurićima, toplinske karte, ispune područja).
• Možete dodati mjerenje vremena, tj. animirajte proces i gledajte kako se razvija.
  
• Karte se učitavaju sa servisa Bing Maps, tj. Za gledanje vam je potrebna vrlo brza internetska veza. Ponekad postoje poteškoće s točnim prepoznavanjem adresa, jer. imena u podacima ne odgovaraju uvijek onima u Bing kartama.
  
• U punoj (nedemo) verziji Power Map-a možete koristiti vlastitu karte za preuzimanje, na primjer, za vizualizaciju posjetitelja trgovačkog centra ili cijena stanova u stambenoj zgradi odmah na planu zgrade.
• Na temelju stvorenih geo-vizualizacija možete kreirati videozapise izravno u Power Mapu (primjer) kako biste ih kasnije podijelili s onima koji nemaju instaliran dodatak ili ih uključili u Power Point prezentaciju.
  

moćni pogled

Prvi put predstavljen u programu Excel 2013, ovaj je dodatak osmišljen kako bi oživio vaše podatke interaktivnim grafikonima, grafikonima, kartama i tablicama. Ponekad se za to koriste izrazi. kontrolna ploča (kontrolna ploča) or kontrolna ploča (kartica rezultata). Suština je da u svoju Excel datoteku možete umetnuti poseban list bez ćelija – Power View slajd, gdje možete dodati tekst, slike i puno različitih vrsta vizualizacija na temelju vaših podataka iz Power Pivot podatkovnog modela.

Izgledat će ovako:

Ovdje su nijanse sljedeće:

• Početni podaci preuzeti su s istog mjesta – iz Power Pivot podatkovnog modela.
• Da biste radili s Power Viewom, trebate instalirati Silverlight na svoje računalo – Microsoftov analog Flasha (besplatno).
  

Usput, na web stranici Microsofta postoji vrlo pristojan tečaj o Power Viewu u .

Power BI

Za razliku od prethodnih, Power BI nije dodatak za Excel, već zaseban proizvod, koji je cijeli skup alata za poslovnu analizu i vizualizaciju. Sastoji se od tri ključna elementa:

1. Power BI Desktop – program za analizu i vizualizaciju podataka koji između ostalog uključuje sve funkcionalnosti dodataka Power Query i Power Pivot + poboljšane mehanizme vizualizacije iz Power View-a i Power Map-a. Možete ga besplatno preuzeti i instalirati s Microsoftove web stranice.

U Power BI Desktopu možete:

• Učitaj podatke iz preko 70 različitih izvora (kao u Power Queryju + dodatni konektori).
• vezati tablice za modeliranje (kao u Power Pivotu)
• Dodajte dodatne izračune podacima s mjere и izračunati stupci na DAX-u (kao u Power Pivotu)
• Stvorite prekrasne podatke na temelju interaktivna izvješća s različitim vrstama vizualizacija (vrlo slično Power Viewu, ali još bolje i snažnije).
• Objaviti izradio izvješća na web mjestu Power BI Service (vidi sljedeću točku) i podijeli ih s kolegama. Štoviše, moguće je dati različita prava (čitanje, uređivanje) različitim osobama.
  

2. Power BI online usluga – pojednostavljeno rečeno, ovo je stranica na kojoj ćete vi i svaki korisnik u vašoj tvrtki imati svoj vlastiti “sandbox” (radni prostor) na koji možete uploadati izvješća kreirana u Power BI Desktopu. Osim pregledavanja, omogućuje vam čak i njihovo uređivanje, reproducirajući gotovo sve funkcionalnosti Power BI Desktop online. Ovdje također možete posuditi pojedinačne vizualizacije iz tuđih izvješća, prikupljajući iz njih vlastite nadzorne ploče autora.

Izgleda otprilike ovako:

3. Power BI Mobile je aplikacija za iOS / Android / Windows za povezivanje s Power BI uslugom i praktičan pregled (ne uređivanje) kreiranih izvješća i nadzornih ploča izravno na zaslonu vašeg telefona ili tableta. Možete ga preuzeti (potpuno besplatno) ovdje.

Na primjer, na iPhoneu, gore generirano izvješće izgleda ovako:

I sve to uz zadržavanje interaktivnosti i animacije + zatvoren za dodir i crtanje po ekranu olovkom. Vrlo udobno. Time poslovna inteligencija postaje dostupna svim ključnim osobama tvrtke u bilo koje vrijeme i na svakom mjestu – potreban je samo pristup Internetu.

Power BI planovi cijena. Power BI Desktop i Mobile besplatni su iz kutije, a većina značajki Power BI Service također je besplatna. Dakle, za osobnu upotrebu ili upotrebu unutar male tvrtke, ne morate platiti ni peni za sve gore navedeno i možete sigurno ostati pri planu Besplatno. Ako želite dijeliti izvješća s kolegama i upravljati njihovim pravima pristupa, morat ćete otići na Bess (10 USD mjesečno po korisniku). Ima li još premija – za velike tvrtke (> 500 korisnika) koje zahtijevaju odvojene kapacitete za pohranu i poslužitelj za podatke.

• Projicirajte gantogram u Excelu s Power Queryjem
• Kako stvoriti bazu podataka u Excelu pomoću Power Pivota
• Vizualizacija kretanja po ruti na karti u Power Mapu