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

Termini “Power Query”, “Power Pivot”, “Power BI” i druge “ovlasti” sve više se pojavljuju u člancima i materijalima o Microsoft Excel-u. Prema mom iskustvu, ne razumiju svi jasno šta se krije iza ovih koncepata, kako su međusobno povezani i kako mogu pomoći jednostavnom korisniku Excel-a.

Hajde da razjasnimo situaciju.

power query

Još 2013. godine, posebno kreirana grupa programera u okviru Microsofta objavila je besplatan dodatak za Excel. power query (drugi nazivi su Data Explorer, Get & Transform), koji može učiniti mnogo korisnih stvari za svakodnevni rad:

• upload podaci u Excel-u iz gotovo 40 različitih izvora, uključujući baze podataka (SQL, Oracle, Access, Teradata…), korporativne ERP sisteme (SAP, Microsoft Dynamics, 1C…), Internet servise (Facebook, Google Analytics, skoro sve web stranice).
• Prikupite podatke od Files svi glavni tipovi podataka (XLSX, TXT, CSV, JSON, HTML, XML…), pojedinačno i grupno – iz svih datoteka u navedenom folderu. Iz Excel radnih knjiga možete automatski preuzeti podatke sa svih listova odjednom.
  
• Očisti primljeni podaci iz “smeća”: dodatni stupci ili redovi, ponavljanja, servisne informacije u “zaglavlju”, dodatni razmaci ili znakovi koji se ne mogu ispisati itd.
• Unesi podatke Naredbu: ispravna velika i mala slova, brojevi-kao-tekst, popuniti praznine, dodati ispravan “cap” tablici, raščlaniti “ljepljivi” tekst u kolone i zalijepiti ga nazad, podijeliti datum na komponente, itd.
  
• na svaki mogući način preobraziti tabele, dovodeći ih u željeni oblik (filtrirajte, sortirajte, promijenite redoslijed kolona, ​​transponirajte, dodajte zbrojeve, proširite unakrsne tabele u ravne i sažmite nazad).
• Zamijenite podatke iz jedne tablice u drugu tako što ćete upariti jedan ili više parametara, tj. lijepu zamjensku funkciju VPR (Vlookup) i njegovih analoga.

Power Query se nalazi u dvije verzije: kao poseban dodatak za Excel 2010-2013, koji se može preuzeti sa zvanične Microsoft web stranice i kao dio programa Excel 2016. U prvom slučaju, nakon instalacije, pojavljuje se posebna kartica u Excel:

U programu Excel 2016, sva funkcionalnost Power Queryja je već ugrađena prema zadanim postavkama i nalazi se na kartici podaci (Datum) kao grupa Nabavite i pretvorite (Nabavi i transformiraj):

Mogućnosti ovih opcija su potpuno identične.

Osnovna karakteristika Power Queryja je da se sve akcije za uvoz i transformaciju podataka pohranjuju 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 bilo koji broj puta (upit za osvježavanje).

Glavni Power Query prozor obično izgleda ovako:

Po mom mišljenju, ovo je najkorisniji dodatak naveden u ovom članku za širok krug korisnika. Mnogi zadaci za koje ste morali užasno iskriviti formule ili napisati makroe sada se lako i lijepo rade u Power Queryju. Da, i sa naknadnim automatskim ažuriranjem rezultata. A s obzirom na to da je besplatan, u smislu omjera cijene i kvaliteta, Power Query je jednostavno van konkurencije i apsolutni must have za svakog srednje naprednog korisnika Excela ovih dana.

powerpivot

Power Pivot je također dodatak za Microsoft Excel, ali dizajniran za malo drugačije zadatke. Ako je Power Query fokusiran na uvoz i obradu, onda 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 tabelu.

Opšti principi rada u Power Pivot-u su sljedeći:

1. Mi smo prvi učitavanje podataka u Power Pivot-u – 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 analizu čini gotovo svejedom.
2. Zatim između učitanih tabela veze su konfigurisane ili, kako kažu, stvara se Model podataka. Ovo će omogućiti u budućnosti da se iz postojećih tabela prave izveštaji o svim poljima kao da je jedna tabela. I opet bez VPR-a.
3. Ako je potrebno, dodatni proračuni se dodaju modelu podataka pomoću izračunate kolone (slično koloni sa formulama u „pametnoj tabeli“) i mjere (analog izračunatog polja u sažetku). Sve je to napisano u posebnom Power Pivot internom jeziku pod nazivom DAX (Data Analysis eXpressions).
4. Na Excel listu, prema modelu podataka, izveštaji koji nas zanimaju izgrađeni su u formu pivot table i dijagrami.

Glavni Power Pivot prozor izgleda otprilike ovako:

A ovako izgleda model podataka, odnosno sve učitane tabele sa kreiranim relacijama:

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

• U Power Pivot-u nema ograničenja linije (kao u Excel-u). Možete učitati stolove bilo koje veličine i lako raditi s njima.
  
• Power Pivot je veoma dobar komprimirati podatke kada ih učitavate u Model. Originalni tekstualni fajl od 50 MB se lako može pretvoriti u 3-5 MB nakon preuzimanja.
• Budući da "ispod haube" Power Pivot, zapravo, ima punopravni mehanizam baze podataka, on se nosi s velikim količinama informacija vrlo brzo. Trebate analizirati 10-15 miliona 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 sa Microsoft web stranice. Ali ako imate Excel 2013-2016, onda sve zavisi od vaše licence, jer. u nekim verzijama je uključen (Office Pro Plus, na primjer), a u nekima nije (Office 365 Home, Office 365 Personal, itd.) Više o tome možete pročitati ovdje.

Power Maps

Ovaj dodatak se prvi put pojavio 2013. godine i prvobitno se zvao GeoFlow. Namijenjen je za vizualizaciju geo-podataka, odnosno numeričkih informacija na geografskim kartama. Početni podaci za prikaz su uzeti iz istog Power Pivot modela podataka (pogledajte prethodni pasus).

Demo verzija Power Map-a (usput rečeno, gotovo se ne razlikuje od pune) može se ponovo potpuno besplatno preuzeti sa Microsoft web stranice. Puna verzija je uključena u neke Microsoft Office 2013-2016 pakete zajedno sa Power Pivot – u obliku dugmeta 3D mapa tabulator Ubacite (Insert — 3D-mapa):

Ključne karakteristike Power Map-a:

• Mape može biti i ravna i obimna (globus).
• Možete koristiti nekoliko različitih vrste vizualizacije (histogrami, balon dijagrami, toplotne karte, popunjavanje područja).
• Možete dodati mjerenje vremena, tj. animirajte proces i gledajte kako se razvija.
  
• Mape se učitavaju iz servisa Mape Bing, tj. Potrebna vam je veoma brza internet konekcija za pregled. Ponekad postoje poteškoće sa ispravnim prepoznavanjem adresa, jer. imena u podacima se ne poklapaju uvijek s onima na Bing mapama.
  
• U punoj (ne-demo) verziji Power Map-a možete koristiti vlastitu mape koje se mogu preuzeti, na primjer, za vizualizaciju posjetitelja trgovačkog centra ili cijene stanova u stambenoj zgradi na planu zgrade.
• Na osnovu kreiranih geo-vizualizacija, možete kreirati video zapise direktno u Power Map-u (primjer) da biste ih kasnije podijelili s onima koji nemaju instaliran dodatak ili ih uključili u Power Point prezentaciju.
  

power view

Prvi put predstavljen u programu Excel 2013, ovaj dodatak je dizajniran da oživi vaše podatke pomoću interaktivnih grafikona, grafikona, mapa i tabela. Ponekad se za to koriste termini. komandna tabla (kontrolna tabla) or komandna tabla (scorecard). Zaključak je da možete umetnuti poseban list bez ćelija u svoju Excel datoteku – Power View slajd, gdje možete dodati tekst, slike i mnogo različitih vrsta vizualizacija na osnovu vaših podataka iz Power Pivot modela podataka.

Izgledat će otprilike ovako:

Ovdje su nijanse:

• Početni podaci su preuzeti sa istog mjesta – iz Power Pivot modela podataka.
• Da biste radili sa Power View-om, potrebno je da instalirate Silverlight na svoj računar – Microsoftov analog Flash-a (besplatno).
  

Na web stranici Microsofta, inače, postoji vrlo pristojan kurs obuke o Power Viewu u .

Power BI

Za razliku od prethodnih, Power BI nije dodatak za Excel, već poseban proizvod, koji predstavlja čitav 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 svu funkcionalnost Power Query i Power Pivot dodataka + poboljšane mehanizme vizualizacije iz Power View-a i Power Map-a. Možete ga besplatno preuzeti i instalirati sa Microsoft web stranice.

U Power BI Desktopu možete:

• Učitavanje podataka preko 70 različitih izvora (kao u Power Query + dodatni konektori).
• vezati tablice za modeliranje (kao u Power Pivot)
• Dodajte dodatne proračune podacima sa mjere и izračunate kolone na DAX-u (kao u Power Pivot)
• Kreirajte prekrasne podatke zasnovane na podacima interaktivni izvještaji sa različitim vrstama vizualizacija (vrlo slično Power Viewu, ali još bolje i moćnije).
• objaviti kreirali izvještaje na web lokaciji Power BI usluge (pogledajte sljedeću tačku) i podijelite ih sa kolegama. Štaviše, moguće je dati različita prava (čitanje, uređivanje) različitim ljudima.
  

2. Power BI online usluga – pojednostavljeno rečeno, ovo je stranica na kojoj ćete vi i svaki korisnik u vašoj kompaniji imati svoj vlastiti “sandbox” (radni prostor) gdje možete otpremati izvještaje kreirane u Power BI Desktop-u. Osim pregleda, čak vam omogućava i da ih uređujete, reproducirajući gotovo svu funkcionalnost Power BI Desktopa na mreži. Ovdje također možete posuditi pojedinačne vizualizacije iz izvještaja drugih ljudi, prikupljajući od njih vlastite autorske kontrolne ploče.

To izgleda otprilike ovako:

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

Na iPhoneu, na primjer, gore generirani izvještaj izgleda ovako:

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

Power BI cjenovni planovi. Power BI Desktop i Mobile su besplatni, a većina funkcija Power BI usluge je takođe besplatna. Dakle, za ličnu upotrebu ili upotrebu unutar male kompanije, ne morate platiti ni peni za sve gore navedeno i možete sigurno ostati na planu besplatno. Ako želite dijeliti izvještaje sa kolegama i upravljati njihovim pravima pristupa, morat ćete otići na BESS (10 USD mjesečno po korisniku). Ima li još Premija – za velike kompanije (> 500 korisnika) koje zahtijevaju odvojene skladišne ​​i serverske kapacitete za podatke.

• Projektni gantogram u Excelu sa Power Queryjem
• Kako kreirati bazu podataka u Excelu koristeći Power Pivot
• Vizualizacija kretanja duž rute na mapi u Power Mapu