Excel powerpivot tečajevi. Korištenje PowerPivota u Microsoft Excelu. Razumijevanje agregacija u Power Pivotu

Oni koji često koriste Zaokretne tablice, već su uspjeli procijeniti koliko moćan alat oni su. No, nažalost, zaokretne tablice ne mogu izvještavati iz različitih izvora podataka, bilo da se radi o bazama podataka ili datotekama. Također, kada radimo s velikim količinama podataka, naš obična računala Počinju biti nesposobni obraditi te podatke i počinju "usporiti" ili se čak zamrznuti. Ovo se odnosi na tablice koje imaju više od 300 tisuća redaka. Dakle, kako bi riješio sve te probleme, Microsoft je razvio besplatna aplikacija Do Excel, koji proširuje mogućnosti konvencionalnih Zaokretne tablice.

Stoga vam predstavljamo dodatak za MS Excel, koji će vam omogućiti stvaranje supertablica - Power Pivot .

Dakle, što je PowerPivot?

Power Pivot- Ovo je dodatni dodatak za Excel, što proširuje funkcionalnost Zaokretne tablice (Zaokretne tablice). Korišten samo za MS Office 2010. In nova verzija Ured 2013 PowerPivot već je sastavni element Excel.

Kako instalirati PowerPivot?

Prije instalacije PowerPivot, trebate (za Windows XP):

Nakon instaliranja PowerPivota u Excel, pojavljuje se dodatna kartica:

Klikanje PowerPivot prozor Otvorit će se poseban prozor u koji možete učitati podatke iz različitih izvora.

Nakon preuzimanja podataka iz raznih datoteka Excel na karticu PowerPivot prozor, možete raditi s njima na običnom listu Excel, tj. dodavanje stupaca, izvođenje izračuna pomoću formula. Također, pojavljuje se jedinstven za oboje Excel"Mogu uspostaviti veze između učitanih tablica kao u Pristup. Korištenje povezanih tablica možemo učiniti Stožerna tablica, koji će uzimati podatke iz različitih tablica, bez prethodnog ručnog kombiniranja, bez ponovnog korištenja VLOOKUP (VLOOKUP), jednostavno i brzo.

Klikom na sliku Stožerna tablica, dobivamo priliku početi ga oblikovati prema svojim potrebama. Tako ćemo dobiti ovaj izgled tablice sa svim dostupnim podacima koje smo prethodno pripremili i učitali:

Također, kako vidimo, u Stožerna tablica pojavljuju se nova dodatna polja koja se tzv Vizualni filtri (rezači).

Još jedna važna značajka naprednih Zaokretne tablice je ubrzana obrada velikih tablica (filtriranje, sortiranje itd.), koje zauzimaju više od 1 milijun redaka, što u standardu Excel je gotovo nemoguće.

Također predlažemo da pogledate video o alatima za samoanalizu podataka u PowerPivot.

Milijuni korisnika koriste Excel formule za izvođenje izračuna. Ovi izračuni mogu biti jednostavni poput dodavanja stupaca brojeva ili mnogo složeniji, poput modeliranja poslovnih procesa. Ali u svakom slučaju, svaka formula izgrađena je pomoću osnovnih operatora i funkcija, koji su sastavni dijelovi takvih formula.

PowerPivot je građevni blok koji Excel treba za stvaranje rješenja poslovne inteligencije. Bez obzira koristi li rješenje jednostavne izračune ili nešto puno složenije. PowerPivot uključuje mogućnost uvoza tablica iz širokog raspona izvora podataka, izvođenje brzih izračuna na velikim količinama podataka, korištenje DAX (Data Analysis Expressions) formula i stvaranje izvješća stožerne tablice na temelju dobivenih informacija.

DAX izrazi vrlo su slični Excel formulama. No iako je popis DAX funkcija uglavnom isti kao popis u Excelu, postoje nove funkcije u izrazima analize koje Excel nema. Ove su funkcije osmišljene kako bi omogućile analizu podataka, posebice povezivanje tablica i dinamičku analizu. Sposobnost stvaranja izračuna koji će se dinamički procjenjivati ​​u različitim kontekstima moćan je alat. Prije PowerPivota i DAX-a, ova vrsta izračuna često je zahtijevala poznavanje koncepata višedimenzionalnog programiranja.

Uvod u dodatak PowerPivot

Nakon instalacije, na vrpci će se pojaviti nova kartica PowerPivot.

Kliknite na ProzorPower Piwot pokrenut će novu karticu PowerPivotZaExcel.

Uzmimo trenutak da razmotrimo kako se PowerPivot i Excel nadopunjuju.

PowerPivot ima ugrađeni algoritam za obradu podataka koji vam omogućuje 1) pohranu i obradu podataka O veće količine podataka nego što je dostupno u Excelu, 2) uvoz podataka iz različitih izvora podataka, 3) operacije modela, kao što je definiranje odnosa između tablica i stvaranje DAX formula.

Nadgradnja PowerPivot zaExcel(vidi gore) ima korisničko sučelje orijentirano na Excel.

Excel ima širok raspon alata za vizualizaciju kao što su , PivotCharts i . Također se mogu koristiti za rad s PowerPivot podacima.

Excel dokument izvrstan je način za pakiranje podataka i njihovu vizualizaciju. To znači da je sve što vidite u prozoru dodatka pohranjeno u vašoj radnoj knjizi. To olakšava upravljanje i razmjenu podataka.

Uvoz podataka

Naravno, sve počinje s podacima, zbog čega vam PowerPivot omogućuje uvoz podataka iz raznih izvora. Ispod je mali isječak popisa mogućih izvora.

Ovisno o vrsti izvora, možete odabrati potrebne tablice, pregledati i filtrirati polja tablice i/ili koristiti upit za uvoz.

Kopiraj zalijepi

Korisnici često moraju kombinirati podatke pohranjene u PowerPivotu s malom količinom podataka koji se nalaze na radnom listu. To se može učiniti stvaranjem odnosa između tablica.

Rad s tablicama

Nakon što su podaci uvezeni, oni se prikazuju u prozoru PowerPivot zaExcel.

Ako vaši izvori podataka imaju odnose između tablica, PowerPivot će ih automatski povući. U suprotnom, možete ih izraditi ručno.

Zašto stvarati odnose između tablica? Takvi odnosi omogućuju vam ispravnu navigaciju i grupiranje podataka, eliminirajući potrebu za kombiniranjem povezanih stupaca u jednu veliku tablicu.

Izračuni

Konačni cilj PowerPivot zaExcel učiniti analizu podataka jednostavnom. Za razliku od proizvoda za analizu podataka koji su namijenjeni IT profesionalcima (kao što je SQL Server Analysis Services), PowerPivot je dizajniran za ljude koji koriste zaokretne tablice u svakodnevnom radu. Ideja je da korisnici mogu koristiti postojeće Excel vještine bez učenja specijaliziranih jezika.

If(>100000000, “Izvrsno”, if(>10000000, “Dobro”, “Loše”))

Stoga, dok radite, možete naići na poznate formule kao što su ABS, AVERAGE, AVERAGEA (mislim da će s vremenom biti prevedene i na ruski). Tu su i potpuno novi: ALL, ALLEXEPT, AVERAGEX.

Vizualizacija

Kako bi stvorio , PowerPivot koristi alate za vizualizaciju programa Excel. Srećom, Excel ima dosta toga: zaokretne tablice, zaokretne grafikone i rezače.

Nakon nekoliko minuta oblikovanja i postavljanja kontrola, imao sam funkcionalnu samostalnu radnu knjigu koja se mogla pokrenuti kao aplikacija.

Poanta

Dakle, što imamo? PowerPivot za Excel je dodatak koji se koristi za dodavanje i analizu velikih količina podataka. U Excel radna knjiga pruža sve mogućnosti za vizualizaciju podataka i interakciju s njima. Vještine izvješćivanja zaokretne tablice omogućit će vam stvaranje složenih radnih knjiga na temelju složene analitike programa Excel.

ured.microsoft

U manje od sat vremena naučit ćete kako stvarati Excel Izvješća zaokretne tablice koja kombiniraju podatke iz više tablica. Prvi dio ovog vodiča pomoći će vam pri uvozu podataka i njihovom istraživanju. U 2. dijelu naučit ćete poboljšati podatkovni model koji stoji iza vašeg izvješća, dodati nove izračune i hijerarhije u izvješća Power Viewa i optimizirati ih pomoću dodatka Power Pivot.

Počnimo s uvozom podataka.

1. Preuzmite ogledne podatke (ContosoV2) za ovaj vodič. Ekstrahirajte podatkovne datoteke i spremite ih na prikladno mjesto, kao što je mapa Preuzimanja ili Moji dokumenti.
2. Otvorite praznu radnu knjigu u Excelu.
3. Izaberi Podaci > Primanje vanjskih podataka > Iz Accessa.
4. Potražite mapu koja sadrži uzorke podatkovnih datoteka i odaberite ContosoSales.
5. Klik Otvoren. Budući da se povezujete s datotekom baze podataka koja sadrži više tablica, pojavit će se dijaloški okvir Izbor stola, gdje možete odabrati tablice koje želite uvesti.

1. U dijaloškom okviru Odabir tablice odaberite potvrdni okvir Omogući odabir više tablica.
2. Odaberite sve tablice i kliknite gumb u redu.
3. Na kartici Uvoz podataka odaberite Izvješće zaokretne tablice i pritisnite tipku u redu.

BILJEŠKA.Čestitamo! Upravo ste izradili podatkovni model. Model je sloj integracije podataka koji se automatski stvara kada uvozite ili radite s više tablica istovremeno u jednom izvješću zaokretne tablice.

Model je praktički nevidljiv u Excelu, ali ga možete pregledavati i uređivati ​​izravno pomoću dodatka Power Pivot. U Excelu se prisutnost podatkovnog modela može vidjeti kada vidite skup tablica na popisu polja u zaokretnoj tablici. Postoji nekoliko načina za izradu modela.

Pregled podataka u zaokretnoj tablici

Da biste pregledali podatke na jasan način, možete povući polja u područja Vrijednosti, Stupci I Žice na popisu polja u zaokretnoj tablici.

1. Pomičite se kroz popis polja i pronađite tablicu FactSales.
2. Odaberite stupac SalesAmount. Budući da sadrži numeričke podatke, Excel će automatski smjestiti SalesAmount u područje vrijednosti.
3. Iz tablice DimDate povucite stupac CalendarYear u područje Columns.
4. Iz tablice DimProductSubcategory povucite stupac ProductSubcategoryName u područje redaka.
5. Iz tablice DimProduct povucite stupac BrandName u područje redaka i smjestite ga pod potkategoriju.

Zaokretna tablica bi trebala izgledati kao ova ispod.

Bez trošenja poseban napor, stvorili ste zaokretnu tablicu koja sadrži polja iz četiri različite tablice. Ovaj je zadatak bio tako jednostavan zahvaljujući unaprijed stvorenim odnosima između tablica. Budući da su odnosi između tablica postojali u izvoru podataka i vi ste uvezli sve tablice odjednom, Excel je mogao ponovno stvoriti te odnose u modelu.

Ali što ako podaci dolaze iz različitih izvora ili nisu uvezeni u isto vrijeme? Općenito, nove podatke možete dodati stvaranjem odnosa na temelju podudarnih stupaca. U sljedećem koraku uvest ćete dodatne tablice i naučiti o zahtjevima za nove odnose i koracima za njihovu izradu.

Dodavanje dodatnih tablica

Da biste naučili kako napraviti veze, potrebne su vam dodatne nepovezane tablice. U ovom trenutku dobit ćete preostale podatke korištene u ovom vodiču uvozom dodatne baze podataka i umetanjem podataka iz druge dvije radne knjige.

Dodavanje kategorija proizvoda

1. Otvorite novi list u knjizi. Pohranit će dodatne podatke.
2. Izaberi Podaci > Primanje vanjskih podataka > Iz Accessa.
3. Idite do mape koja sadrži uzorke podatkovnih datoteka i odaberite ProductCategories. Pritisnite gumb Otvoren.
4. Na kartici Uvoz podataka odaberite Stol i pritisnite tipku u redu.

Dodavanje geografskih podataka

1. Dodajte još jedan list.
2. Iz oglednih podatkovnih datoteka otvorite Geography.xlsx, postavite kursor u polje A1, zatim pritisnite CTRL+SHIFT+END za odabir svih podataka.
3. Kopirajte podatke u međuspremnik.
4. Zalijepite podatke u onaj koji ste upravo dodali. prazan list.
5. Izaberi Formatiraj kao tablicu s bilo kojim stilom. Formatiranje vaših podataka kao tablice omogućuje vam da im date naziv, što je vrlo korisno kada definirate odnose u sljedećem koraku.
6. Provjerite je li okvir Format Table označen Tablica s zaglavljima. Pritisnite gumb u redu.
7. Nazovite tablicu Geografija. Odaberite karticu Rad s tablicama > Konstruktor a u polje Naziv tablice upišite naziv Geografija.
8. Zatvorite datoteku Geography.xlsx da biste je uklonili iz svog radnog prostora.

Dodavanje podataka o trgovini

• Ponovite prethodne korake za datoteku Stores.xlsx - zalijepite njezin sadržaj na prazan list. Nazovite tablicu Stores.

Sada postoje četiri lista. Sheet1 sadrži zaokretnu tablicu, Sheet2 sadrži Kategorije proizvoda, Sheet3 sadrži geografiju, a Sheet4 sadrži trgovine. Budući da ste već imenovali sve tablice, sljedeći korak - stvaranje odnosa - bit će puno lakši.

Korištenje polja iz novih uvezenih tablica

Sada možete koristiti polja iz tablica koje ste upravo uvezli. Ako Excel ne može shvatiti kako dodati polje u izvješće zaokretne tablice, od vas će se tražiti da stvorite odnos između tablica. novi stol usporedit će se s onom koja je već dio modela.

1. Odaberite stavku svi na vrhu popisa polja zaokretne tablice za prikaz puni popis raspoloživi stolovi.
2. Pomaknite se niz popis. Postoje nove tablice koje ste upravo dodali.
3. Proširi Trgovine.
4. Povucite stupac StoreName u područje Filtri.
5. Imajte na umu da će Excel od vas tražiti da stvorite odnos. Ova se obavijest pojavljuje jer ste koristili polja u tablici koja nisu povezana s modelom.
6. Klik Stvoriti da biste otvorili dijaloški okvir Stvori vezu.
7. U području tablice odaberite FactSales. Tablica FactSales iz uzorka podataka sadrži pojedinosti podatke o prodaji i troškovima za Contoso, kao i druge ključeve tablice, uključujući kodove trgovina prisutne u datoteci Stores.xlsx uvezenoj u prethodnom koraku.
8. U području Column (External) odaberite StoreKey.
9. U području Povezane tablice odaberite Trgovine.
10. U području Povezani stupac (primarni) odaberite StoreKey.
11. Pritisnite gumb u redu.

Povezivanje potkategorije proizvoda s kategorijom proizvoda

1. U Excelu odaberite Podaci > Odnos > Stvoriti.
2. U području tablice odaberite DimProductSubcategory.
3. U području Stupac (vanjski) odaberite ProductCategoryKey.
4. U području Povezane tablice odaberite Table_ProductCategory.accdb.
5. U području Povezani stupac (Primarni) odaberite ProductCategoryKey.
6. Pritisnite gumb u redu.
7. Zatvorite dijaloški okvir Menadžment veza.

Dodavanje kategorija u zaokretnu tablicu

Iako su dodatne tablice i odnosi dodani podatkovnom modelu, oni se još ne koriste u zaokretnoj tablici. Ovaj zadatak dodaje ProductCategory na popis polja u zaokretnoj tablici.

1. svi za prikaz tablica prisutnih u podatkovnom modelu.
2. Pomaknite se niz popis.
3. U području redaka izbrišite BrandName.
4. Proširite Table_DimProductCategories.accdb.
5. Povucite ProductCategoryName u područje redaka, postavljajući ga iznad ProductSubcategory.
6. U području Polja zaokretne tablice odaberite Aktivan tako da će se tablice koje ste upravo koristili koristiti u zaokretnoj tablici.

Kontrolna točka: ponovite što ste naučili

Napravili ste zaokretnu tablicu koja sadrži podatke iz nekoliko tablica koje ste uvezli u prethodnom koraku. Objedinjavanje podataka zahtijevalo je stvaranje poveznica tablica, koje Excel koristi za usklađivanje redaka. Naučili ste da je za pronalaženje povezanih redaka neophodno imati podudarne stupce. U oglednim podacima sve tablice sadrže stupac koji se može koristiti u tu svrhu.

Iako zaokretna tablica radi, možda ste primijetili neke preostale probleme. Popis polja zaokretne tablice izgleda kao da sadrži dodatne tablice (DimEntity) i stupce (ETLLoadID) koji ni na koji način nisu povezani s tvrtkom Contoso. Osim toga, podaci iz tablice Geography još uvijek nisu integrirani.

Sljedeće: Pregledajte i proširite svoj model pomoću Power Pivota

U sljedećoj seriji zadataka proširit ćete model pomoću dodatka Microsoft Office Power Pivot u Microsoft Excel 2013. Otkrit ćete da je najlakši način za stvaranje odnosa korištenjem prikaza grafikona koji se nudi u ovom dodatku. Također ćete koristiti dodatak za stvaranje izračuna i hijerarhija, skrivanje stavki koje ne želite da se pojavljuju na popisu polja i optimiziranje podataka za dodatna izvješća.

BILJEŠKA. Dodatak Power Pivot u Microsoft Excelu 2013 dostupan je u Office ProPlus.

Dodati Power Pivot snimiti traku Excel omogućavanjem dodatka Power Pivot.

1. Idi na karticu Datoteka > Mogućnosti > Dodaci.
2. U polju Kontrolirati Izaberi COM dodaci> Ići.
3. Označite kućicu Microsoft Office Power Pivot u programu Microsoft Excel 2013 a zatim kliknite gumb u redu.

Na vrpci se pojavljuje kartica Power Pivot.

Dodajte odnos pomoću prikaza grafikona u Power Pivotu

1. U Excelu odaberite Sheet3 da biste ga učinili aktivnim. Sheet3 sadrži tablicu Geography koja je ranije uvezena.
2. Odaberite na vrpci Power Pivot > Dodaj u podatkovni model. U ovom trenutku modelu će se dodati tablica Geografija. Također će se otvoriti dodatak Power Pivot koji možete koristiti za dovršavanje preostalih koraka zadatka.
3. Primijetite da Power Pivot prozor prikazuje sve tablice u modelu, uključujući tablicu Geography. Pregledajte neke tablice. U ovom dodatku možete vidjeti sve podatke sadržane u modelu.
4. U prozoru Power Pivot, pod Pogled odaberite Prikaz grafikona.
5. Pomoću trake za pomicanje promijenite veličinu grafikona tako da možete vidjeti sve objekte u grafikonu. Imajte na umu da dvije tablice nisu povezane s ostatkom dijagrama: DimEntity i Geography.
6. Desnom tipkom miša kliknite DimEntity, a zatim kliknite Izbrisati. Ova je tablica dio izvorne baze podataka i nije potrebna u modelu.
7. Podesite mjerilo tablice Geography tako da sva njezina polja budu vidljiva. Tablični grafikon možete povećati pomoću klizača.
8. Imajte na umu da tablica Geography sadrži stupac GeographyKey. Ovaj stupac sadrži vrijednosti koje su jedinstveni identifikatori svaki red zemljopisne tablice. Utvrdimo koriste li druge tablice u ovom modelu isti ključ. Ako je tako, možemo stvoriti odnos koji povezuje tablicu s ostatkom modela.
9. Izaberi Pronaći.
10. U polje za pretraživanje metapodataka unesite GeographyKey.
11. Pritisnite gumb nekoliko puta Pronađite sljedeći. Vrijednost GeographyKey nalazi se u tablicama Geography i Stores.
12. Povucite tablicu Geography u tablicu Stores.
13. Povucite stupac GeographyKey u tablici Stores na stupac GeographyKey u tablici Geography. Power Pivot će povući liniju između dva stupca kako bi označio odnos.

U ovom problemu ste naučili novi put dodavanje tablice i stvaranje odnosa. Sada imate potpuno integrirani model gdje su sve tablice povezane i dostupne zaokretnoj tablici na Sheet1.

SAVJET. U prikazu sheme, neke sheme tablice potpuno su proširene kako bi prikazale stupce ETLLoadID, LoadDate i UpdateDate. Ta su specifična polja dio izvorne Contoso pohrane podataka i dodana su za podršku operacijama dohvaćanja i učitavanja podataka. Oni nisu potrebni u vašem modelu. Da biste ih se riješili, označite i desnom tipkom miša kliknite polja, a zatim kliknite Izbrisati .

Stvorite izračunati stupac

U dodatku Power Pivot možete koristiti DAX formule za dodavanje izračuna. U ovom ćete zadatku izračunati donju liniju i dodati izračunati stupac koji upućuje na vrijednosti podataka iz drugih tablica. Kasnije ćete naučiti kako koristiti referentne stupce za pojednostavljenje modela.

1. U prozoru Power Pivot vratite se na prikaz podataka.
2. Dajte accdb tablici Table_ProductCategories smisleniji naziv. Ovu ćete tablicu koristiti u kasnijim koracima, a kraći naziv olakšat će čitanje izračuna. Desnom tipkom miša kliknite naziv tablice, a zatim kliknite Preimenovati, unesite naziv ProductCategories, a zatim pritisnite Enter.
3. Odaberite tablicu FactSales.
4. Izaberi Konstruktor > Stupci > Dodati.
5. U traku formule iznad tablice unesite sljedeću formulu. Automatsko dovršavanje pomoći će vam da unesete pune nazive stupaca i tablica i pokazati vam dostupne funkcije. Također možete jednostavno kliknuti stupac i Power Pivot će dodati njegov naziv formuli.

= - -

Svi redovi izračunatog stupca bit će ispunjeni vrijednostima. Dok se pomičete tablicom prema dolje, vidjet ćete da retci mogu sadržavati različite vrijednosti za ovog stupca, na temelju podataka sadržanih u svakom retku.

1. Preimenujte stupac desnim klikom na CalculatedColumn1 i odabirom Preimenuj stupac. Upišite Profit i pritisnite ENTER.
2. Sada odaberite tablicu DimProduct.
3. Izaberi Konstruktor > Stupci > Dodati.
4. U traku formule iznad tablice unesite sljedeću formulu.

POVEZANO(Kategorije proizvoda)

Funkcija RELATED vraća vrijednost iz povezane tablice. U našem slučaju, tablica ProductCategories sadrži nazive kategorija proizvoda koje ćete morati dodati u tablicu DimProduct kada budete gradili hijerarhiju koja uključuje informacije o kategoriji.

1. Kada završite s unosom formule, pritisnite ENTER za potvrdu.

Svi redovi izračunatog stupca bit će ispunjeni vrijednostima. Dok se pomičete tablicom prema dolje, vidjet ćete da svaki redak sadrži naziv kategorije proizvoda.

1. Preimenuj stupac. Upišite ProductCategory i pritisnite Enter.
2. Izaberi Konstruktor > Stupci > Dodati.
3. U traku formule iznad tablice unesite sljedeću formulu, a zatim pritisnite Enter za potvrdu.

POVEZANO(DimProizvodPotkategorija)

1. Preimenujte stupac desnim klikom na CalculatedColumn1 i odabirom Preimenuj stupac. Upišite ProductSubcategory i pritisnite ENTER.

Stvaranje hijerarhije

Većina modela sadrži podatke koji su hijerarhijske prirode. Uobičajeni primjeri uključuju kalendarske podatke, geografske podatke i kategorije proizvoda. Stvaranje hijerarhija je korisno jer vam omogućuje da povučete element (hijerarhiju) u izvješće, umjesto prikupljanja i raspoređivanja istih polja svaki put.

1. U Power Pivotu idite na Prikaz grafikona. Proširite tablicu DimDate kako biste lakše radili s njezinim poljima.
2. Pritisnite i držite tipku CTRL i kliknite stupce CalendarYear, CalendarQuarter i CalendarMonth (morat ćete se pomaknuti prema dolje u tablici).
3. S odabrana tri stupca desnom tipkom miša kliknite jedan od njih i kliknite Stvorite hijerarhiju. Čvor nadređene hijerarhije, Hijerarhija 1, bit će kreiran na dnu tablice, a odabrani stupci će se kopirati u hijerarhiju kao podređeni čvorovi.
4. Unesite datume kao naziv nove hijerarhije.
5. Dodajte stupac FullDateLabel u hijerarhiju. Desnom tipkom miša kliknite FullDateLabel i odaberite Dodaj u hijerarhiju. Odaberite vrstu datuma. Stupac FullDateLabel sadrži datum u punom formatu, uključujući godinu, mjesec i dan. Provjerite pojavljuje li se stupac FullDateLabel na samom dnu hijerarhije. Sada imate hijerarhiju na više razina koja uključuje godinu, kvartal, mjesec i pojedinačne kalendarske dane.
6. Dok ste još u prikazu grafikona, odaberite tablicu DimProduct i kliknite Stvorite hijerarhiju u zaglavlju tablice. Prazan nadređeni čvor hijerarhije pojavljuje se na dnu tablice.
7. Unesite kategorije proizvoda kao naziv nove hijerarhije.
8. Da biste stvorili podređene čvorove hijerarhije, povucite stupce ProductCategory i ProductSubcategory u hijerarhiju.
9. Desnom tipkom miša kliknite ProductName i odaberite Dodaj u hijerarhiju. Odaberite Kategorije proizvoda.

Sada kada znate nekoliko načina za stvaranje hijerarhije, upotrijebimo ih u zaokretnoj tablici.

1. Vrati se na Excel.
2. Na Sheet1 (list koji sadrži zaokretnu tablicu), izbrišite polja u području Redovi.
3. Zamijenite ih novom hijerarhijom kategorija proizvoda u DimProductu.
4. Slično, zamijenite CalendarYear u području Stupci hijerarhijom Datumi iz DimDate.

Prednosti hijerarhija sada je lako vidjeti kada pregledavate svoje podatke. Možete neovisno proširiti i zatvoriti različita područja zaokretne tablice, dajući vam veću kontrolu nad načinom na koji se koristi raspoloživi prostor. Dodatno, dodavanje jedne hijerarhije za područja redaka i stupaca omogućuje trenutnu granularnost bez potrebe za gomilanjem više polja za postizanje istog učinka.

Skrivanje stupaca

Nakon što se stvori hijerarhija kategorija proizvoda i smjesti u DimProduct, DimProductCategory ili DimProductSubcategory na popisu polja zaokretne tablice više nisu potrebni. U ovom zadatku naučit ćete kako sakriti suvišne tablice i stupce koji zauzimaju prostor na popisu polja zaokretne tablice. Skrivanje tablica i stupaca omogućuje vam pojednostavljenje izvješćivanja bez utjecaja na model koji pruža odnose i izračune na podacima.

Možete sakriti pojedinačne stupce, njihov raspon ili cijelu tablicu. Nazivi stupaca i tablica postaju neaktivni, odražavajući njihovo skriveno stanje za klijentske renderere koji koriste model. Skriveni stupci izgledaju zasivljeni u modelu, ali ostaju vidljivi u podatkovnom prikazu tako da možete raditi s njima.

1. Provjerite jeste li u Power Pivotu odabrali Prikaz podataka.
2. Na karticama na dnu zaslona desnom tipkom miša kliknite DimProductSubcategory i odaberite.
3. Ponovite za ProductCategories.
4. Otvorite okruženje DimProduct.
5. Desnom tipkom miša kliknite sljedeće stupce i odaberite Sakrij u alatima za klijente.

• Šifra proizvoda
• Oznaka proizvoda
• Potkategorija proizvoda

1. Odaberite nekoliko susjednih stupaca, počevši s ClassID i završavajući s ProductSubcategory. Kliknite desnom tipkom da ih sakrijete.
2. Ponovite ovaj korak s drugim tablicama, uklanjajući ID-ove, ključeve i druge detalje koji ne pripadaju izvješću.

Sada razumijete da stvaranjem izračunatih polja možete analizirati podatke na bezbroj načina. učinkovite načine. Naučimo kako stvoriti ta polja.

Stvaranje izračunatih polja u Power Pivotu jednostavno je pomoću Autosum.

1. U stolu FactSales odaberite stupac Dobit.
2. Izaberi Izračuni > Autosum. Primijetite da je stvoreno novo izračunato polje pod nazivom Zbroj dobiti u ćeliji područja izračuna neposredno ispod stupca Dobit.
3. U Excelu na Sheet1 u popisu polja odaberite u tablici FactSales izračunato polje Zbroj dobiti.

Spreman! Kao što možete vidjeti, koristeći standardne agregatne funkcije, stvorili smo izračunato polje Suma profita u Power Pivotu u samo nekoliko minuta i dodali ga u zaokretnu tablicu. Sada možete brzo analizirati svoju zaradu primjenom različitih filtara. U ovom slučaju, vidjet ćete stupac Suma profita čije su vrijednosti filtrirane prema hijerarhiji Kategorija proizvoda i Datumi.

Ali što ako trebate napraviti detaljniju analizu, kao što je brojanje prodaje za određeni kanal, proizvod ili kategoriju? Da biste to učinili, morate stvoriti drugo izračunato polje koje izračunava broj redaka, jedan za svaku prodaju iz tablice FactSales, ovisno o filtrima koje koristite.

1. U tablici FactSales odaberite stupac SalesKey.
2. U području Izračuni kliknite strelicu prema dolje ispod gumba Autosum > ČEK.
3. Preimenujte novo izračunato polje desnim klikom na stupac Broj SalesKey u području Compute i odabirom naredbe Preimenovati. Unesi Računati i pritisnite ENTER.
4. U Excelu, na Sheet1, na popisu polja odaberite FactSales i pritisnite Računati.

Imajte na umu da je novi stupac dodan u zaokretnu tablicu Računati, koji prikazuje broj prodaja ovisno o primijenjenim filterima. Kao i kod izračunatog stupca Zbroj dobiti, izračunato polje Broj filtrira se prema hijerarhiji Kategorija proizvoda i Datumi.

Vježbajmo još malo. Ovaj put ćete stvoriti izračunato polje koje izračunava postotak prodaje na temelju određenog konteksta ili filtra. Međutim, za razliku od prethodnih izračunatih polja koja ste izradili pomoću automatskog zbroja, ovaj put ćete formulu morati unijeti ručno.

1. U tablici FactSales, u području izračuna, odaberite praznu ćeliju. Savjet: najprikladnije je postaviti izračunata polja počevši od ćelije u gornjem lijevom kutu. Tako ćete ih lakše pronaći. Možete se kretati do bilo kojeg izračunatog polja u području izračuna.
2. U traku formule unesite sljedeću formulu koristeći IntelliSense: Postotak svih proizvoda:=/IZRAČUNAJ(, SVE(DimProizvod))
3. Pritisnite ENTER za potvrdu formule.
4. U Excelu na Sheet1 na popisu polja u tablici FactSales Izaberi Postotak svih proizvoda.
5. U zaokretnoj tablici odaberite više stupaca Postotak svih proizvoda.
6. Na kartici Dom Izaberi Broj > Format postotka. Za oblikovanje novih stupaca upotrijebite dva decimalna mjesta nakon decimalne točke.

Ovo novo izračunato polje izračunava postotak prodaje za dati kontekst filtra. U našem slučaju, filtri su još uvijek hijerarhije Kategorija proizvoda i Datumi. Između ostalog, možete vidjeti da se postotak računala u prodaji proizvoda s vremenom povećao.

Bit će vam jednostavno stvoriti formule za izračunate stupce i polja ako ste upoznati s načinom stvaranja Excel formula. No bez obzira jeste li upoznati s formulama programa Excel ili ne, imate izvrsnu priliku naučiti osnovne formule DAX-a uzimajući lekcije iz e-knjige Vodič za brzi početak: Osnove DAX-a u 30 minuta.

Spremanje posla

Sačuvajte knjigu kako biste je mogli koristiti s drugim udžbenicima ili za buduće učenje.

Sljedeći koraci

Iako možete jednostavno uvesti podatke iz Excela, često je brže i učinkovitije uvesti pomoću dodatka Power Pivot. Možete filtrirati uvezene podatke izuzimanjem nepotrebnih stupaca. Također možete odlučiti hoćete li dohvatiti podatke pomoću alata za sastavljanje upita ili naredbe upita. Kao sljedeći korak, istražite sljedeće alternative: Dohvaćanje podataka iz feeda podataka u Power Pivot i uvoz podataka iz Analysis Services ili Power Pivot.

Izvješća Power Viewa dizajnirana su za rad s modelima podataka sličnim onome koji ste upravo izradili. Pročitajte više o bogatom prikazu koji Power View pruža u Excelu: Omogućite Power View u programu Excel 2013 i Power View: Istražite, vizualizirajte i predstavite svoje podatke.

Pokušajte poboljšati svoj model podataka kako biste u budućnosti izradili moćnija izvješća Power Viewa tako da pogledate sljedeći vodič. Optimiziranje modela podataka za Power View izvješća.

Power Pivot je dodatak koji se može koristiti za izvođenje dubinske analize u Excelu. Dodatak je ugrađen u neke verzije sustava Office, ali nije omogućen prema zadanim postavkama.

Za popis verzija sustava Office, uključujući Power Pivot, kao i popis verzija koje se ne preporučuju, pogledajte članak: Gdje je PowerPivot?

U nastavku je opisano kako omogućiti Power Pivot prije prve upotrebe.

Idi na karticu Datoteka > Mogućnosti > Dodaci.

U polju Kontrolirati Izaberi COM dodaci i pritisnite Ići.

Označite kućicu Microsoft Office Power Pivot i pritisnite tipku u redu. Ako su instalirane druge verzije Power Pivota, one će također biti navedene na popisu COM dodataka. Odaberite dodatak Power Pivot za Excel.

Na vrpci se pojavljuje kartica Power Pivot.

Otvorite prozor Power Pivot.

Otvorit će se prozor Power Pivot. Ovdje možete kliknuti gumb Vanjski podaci da biste pomoću Čarobnjaka za uvoz tablica filtrirali podatke dok ih dodajete u datoteku, stvorili odnose između tablica, obogatili podatke izračunima i izrazima te izradili zaokretne tablice i zaokretne grafikone iz njih.

Rješavanje problema: vrpca Power Pivot nestaje

U nekim će se slučajevima vrpca Power Pivot pojaviti u izborniku ako Excel otkrije da je dodatak za stabilnost u programu Microsoft Excel. To se može dogoditi ako se Excel sruši kada otvoren prozor Power Pivot. Da biste se vratili na izbornik Power Pivot, učinite sljedeće:

Izaberi Datoteka > Mogućnosti > Dodaci.

U polju Kontrolirati Izaberi Onesposobljeni objekti > Ići.

Izaberi Microsoft OfficePower Pivot i pritisnite tipku Upaliti.

Ako ne možete vratiti Power Pivot vrpcu pomoću gornjih koraka ili vrpca nestane kada zatvorite i ponovno otvorite Excel, učinite sljedeće:

zatvori Excel;

otvorite izbornik Početak > Izvršiti i unesite naredbu regedit;

U uređivaču registra proširite sljedeći odjeljak:

Za Excel 2013: HKEY_CURRENT_USER > Softver > Microsoft > Ured > 15.0 > Korisničke postavke.

Za Excel 2016: HKEY_CURRENT_USER > Softver > Microsoft > Ured > 16.0 > Korisničke postavke

desni klik PowerPivotExcelAddin a zatim kliknite Izbrisati;

povratak na vrh uređivača registra;

proširite odjeljak HKEY_CURRENT_USER > Softver > Microsoft > Ured > Excel > Dodaci;

desni klik PowerPivotExcelClientAddIn.NativeEntry.1 a zatim kliknite Izbrisati;

zatvorite uređivač registra;

otvoriti Excel;

Omogućite dodatak slijedeći korake na početku ovog članka.

[povodom kontroverznog prijenosa 1. dijela posta na geektimes (unatoč tome što je 2. dio ostao na Habréu), vraćam 1. dio na njegovo mjesto]

Rad na području analitike i monitoringa razni instrumenti BI prije ili kasnije naiđete na recenziju ili spomen dodatka Power Pivot Excel. U mom slučaju, upoznao sam ga na konferenciji Microsoft Data Day.

Nakon prezentacije alat nije ostavio neke posebne dojmove: Da, besplatan je (pod Office licencom), da - ima neke ETL funkcionalnosti u smislu dobivanja podataka iz različitih izvora (DB, csv, xls, itd.) , Spajanjem ovih izvora i unosom u RAM bilježe se redovi veličina veći od 1 milijuna redaka u Excelu. Ukratko, pogledao sam i zaboravio.

I morao sam se sjetiti kada se pojavila potreba za identificiranjem određenih pojava u podacima

Ono što me potaknulo da napišem ovaj članak je to što na Runetu nema puno detaljnih informacija o ovom alatu o određenim metodama rada, uglavnom o zvijezdama, pa sam odlučio napisati ovu recenziju dok sam proučavao ovaj alat.

Zapravo, izjava problema (upotrebom bezličnog primjera) je sljedeća:

U originalu csv podaci datoteka:

Postoje maloprodajna mjesta detaljizirana do redova računa, dok je dozvoljeno da poslovnice istog naziva imaju različite adrese samo ako se nalaze u različitim gradovima, ali u izvornom nizu podataka postoje točke koje imaju različite adrese u istom gradu. unatoč tome što su nazivi točaka isti (naziv prodajnog mjesta je jedinstven, tj. jedinica mreže ili zasebno mjesto). Kao poseban slučaj u agregiranom obliku:

Pretraživanje i čišćenje podataka redovnim sredstvima ured otežavaju sljedeće okolnosti:

Detaljni podaci do redaka fakture
Broj zapisa u nekoliko milijuna redaka
Nedostatak sql alata (Na primjer: Access - nije uključen)

Naravno, možete učitati bilo koji besplatni DBMS (bilo da se radi o verziji za stolno računalo ili verziji poslužitelja), ali za to su vam, prvo, potrebna administratorska prava, a kao drugo, članak više ne bi bio o Power Pivotu.

Zadatak: za svaki atomski zapis potrebno je dodatno izračunato polje koje će za svaki naziv prodajnog mjesta izračunati jedinstveni broj adresa unutar istog grada. Ovo polje je potrebno za brzo pronalaženje svih naziva prodajnih mjesta u gradu u kojem postoji više od 1 adrese.

Mislim da je najprikladnije rješavati i pričati iterativno, uz pretpostavku da je naše znanje o DAX-u na rudimentarnoj razini.
Stoga predlažem da se za sada odvojimo od zadatka i razmotrimo neke osnovne aspekte.

Korak 1: Kako se izračunati stupac razlikuje od izračunate mjere?
Evo primjera izračunatog stupca za izdvajanje PDV-a iz polja PDV-a pošiljke pomoću ugrađenih DAX formula:

ROUND([Pošiljka s PDV-om]*POWER(1,18,-1)*0,18,2)

Kao što možete vidjeti iz primjera, izračunati stupac (nazovimo ga PDV) radi sa svakim atomskim zapisom vodoravno.
Dodajmo sada izračunato polje za cijenu po komadu bez PDV-a:

ROUND([Pošiljka s PDV-om]*POWER(1,18,-1)/[Pošiljka kom];2)

Sada, za usporedbu, dodajmo mjerilu izračun prosječne cijene po komadu:

Prosječna cijena po komadu bez PDV-a: =ROUND(AVERAGE([Field_Price po komadu bez PDV-a]);2)

Kao što se može vidjeti iz formule, mjera radi sa stupcem izvornih podataka okomito, tako da uvijek treba sadržavati neku vrstu funkcije koja radi sa skupom (Zbroj, prosjek, varijanca itd.)

Pri povratku na sažetak Excel proračunska tablica izgleda ovako:

Napominjemo da ako polje za obračun PDV-a na svakoj razini podataka (zelena crta na razini maloprodajnog mjesta, grada ili tablice ukupno) prikazuje iznos, što je u načelu ispravno, tada zbroj cijena obračunskog polja “ Cijena po komadu bez PDV-a” (crvena linija) izaziva pitanja.
Ali izračunata mjera “Prosječna cijena po komadu bez PDV-a” ima pravo na život u okviru ove analitičke kocke.

Odavde zaključujemo da je obračunsko polje “Cijena po komadu bez PDV-a” pomoćni alat za izračun mjere “Prosječna cijena po komadu bez PDV-a” i da ne bismo zbunili korisnika s ovim poljem, sakriti ćemo ga s popisa. alata za klijente, ostavljajući mjeru prosječne cijene.

Još jedna razlika između mjere i stupca je ta što vam omogućuje dodavanje vizualizacije:

Na primjer, izradimo KPI za stupanj disperzije cijena s ciljnim ograničenjem od 35% dijeljenjem korijena disperzije aritmetičkom sredinom.

K_var:=STDEV.P([Field_Price po komadu bez PDV-a])/AVERAGE([Field_Price po komadu bez PDV-a])

Kao rezultat toga, vidimo sljedeću tablicu u Excelu (usput, polje izračunate pomoćne cijene više nije na popisu dostupnih polja s desne strane):

Dvostruki klik na koeficijent od 80% pokazuje da cijene stvarno variraju oko prosjeka:

Jače nego s koeficijentom od 15%:

Tako dalje ovaj korak Pogledali smo glavne razlike između mjera i polja unutar PowerPivota.

Korak 2. Komplicirajmo: Izračunajmo udio svakog unosa u ukupnoj prodaji.
Ovo je prvi primjer koji uspoređuje pristup funkciji prozora MS SQL Server i DAX:

Jasno je da se u okviru zaokretnih tablica to radi doslovno u 2 klika mišem bez dodirivanja tipkovnice, ali da bismo razumjeli, pokušajmo to izravno u PowerPivotu pomoću formula.

U sql-u bih to napisao ovako (nemojte me kriviti za pogreške, jer Word ne provjerava sintaksu SQL Servera):

Početak Odaberite "t1.TT ime", "t1.Grad", "t1.Adresa", "t1.Proizvod", "t1.TTN br", "t1.TTN datum", "t1.Pošiljka, kom", " t1.Pošiljka s PDV-om", "t1.Pošiljka, kom"/sum("t1.Pošiljka, kom") preko () kao udio iz tablice kao t1 poredak po "t1.Pošiljka, kom"/sum("t1.Pošiljka , kom") desc

Ovdje se, kao što vidite, otvara prozor kroz sve zapise skupa podataka, pokušajmo sličnu stvar u PowerPivotu:

=[komad pošiljke]/IZRAČUNAJ(SUM([komad pošiljke]);SVE("Tablica1"))

Usredotočimo se na nazivnik: već sam spomenuo da je glavna razlika između izračunatog polja i mjere u tome što se u polju formule one broje vodoravno (unutar jednog zapisa), a mjere okomito (unutar jednog atributa). Ovdje smo mogli ukrstiti svojstva polja i svojstva mjere putem metode CALCULATE. A ako smo prilagodili širinu prozora u SQL-u koristeći Over(), onda smo to ovdje učinili kroz All().

Pokušajmo sada, imajući ovu vještinu, učiniti nešto korisno s našim podacima, na primjer, imajući na umu da je indikator disperzije cijena oko prosjeka varirao u širokom rasponu, pokušajmo identificirati statističke odstupanja u cijenama pomoću pravila 3-sigma.

Funkcije prozora u sql-u izgledat će ovako:

Odaberite "t1.TT ime", "t1.Grad", "t1.Adresa", "t1.Proizvod", "t1.TTN br", "t1.TTN datum", "t1.Pošiljka, kom", "t1 .Pošiljka s PDV-om", "t1.Cijena po komadu bez PDV-a", CASE WHEN ABS("t1.Cijena po komadu bez PDV-a" - AVG("t1.Cijena po komadu bez PDV-a") OVER()) > 3 * STDEV ("t1.Cijena po komadu bez PDV-a") OVER() THEN 1 ELSE 0 END kao Outlier iz tablice kao t1 Go

I evo iste stvari u DAX-u:

If(ABS([Field_Price po komadu bez PDV-a]-CALCULATE(AVERAGE([Field_Price po komadu bez PDV-a]);ALL("Tablica1")))>(3*CALCULATE(STDEV.P([Field_Price po komadu bez PDV-a] );sve("Tablica1")));1;0)

Kao što vidite, cijena je donekle visoka s aritmetičkim prosjekom od 40,03 rubalja.

Korak 3. Suzite prozore.
Pokušajmo sada u izračunatom polju svakog zapisa izbrojati ukupan broj zapisa unutar grada kojem taj zapis pripada.
Na MS SQL poslužitelj funkcije prozora će izgledati ovako:

Odaberite "t1.TT ime", "t1.Grad", "t1.Adresa", "t1.Proizvod", "t1.TTN br", "t1.TTN datum", "t1.Pošiljka, kom", "t1 .Pošiljka s PDV-om", "t1.Cijena po komadu bez PDV-a", count("t1.*) OVER(particija po "t1.City") kao cnt iz tablice kao t1 Idi

U DAX-u:
=CALCULATE(COUNTROWS("Tablica1");ALLEXCEPT("Tablica1","Tablica1"[Grad]))

Obratite pažnju na razliku u prikazu podataka u tablici, posebno sam ubacio adrese u područje mjera kako bih izbrojao njihov broj i usporedio ih s novim poljem koje sam stavio u zaglavlje retka iza naziva poslovnice.

Razlika je jasno vidljiva: ako se uobičajeni izračun broja adresa provodi za svaku točku u gradu, a zatim prikazuje samo međuzbroj za agregat "Grad", tada vam upotreba prozorskih funkcija omogućuje dodjeljivanje svake atomske zabilježite vrijednost bilo kojeg agregata ili ga upotrijebite u nekim međuizračunima izračunatog polja (kao što je prikazano gore).

Povratak na izvorni problem
Dakle, dopustite mi da vas podsjetim, početna izjava problema: za svaki atomski zapis potrebno je dodatno izračunato polje, koje će izračunati za svaki naziv prodajnog mjesta jedinstveni broj adresa unutar istog grada. Ne zaboravite da je naš skup podataka detaljan sve do redaka fakture, tako da prije izračunavanja adresa unutar prozora, potrebno ih je grupirati.

Upit prema SQL poslužitelju:

S a1 kao (odaberite "t1.TT Naziv", "t1.Grad", "t1.Address", "t1.Proizvod", "t1.TTN br", "t1.TTN datum", "t1.Pošiljka, kom.) ", "t1.Pošiljka s PDV-om", "t1.Cijena po komadu bez PDV-a", count(Distinct "t1.Address") OVER(particija prema "t1.City", "t1.TT Name") kao adrcnt iz tablice kao t1) Odaberite * iz a1 gdje je adrcnt>1

Sada nas ništa ne sprječava da to učinimo u DAX-u:

CALCULATE(DISTINCTCOUNT("Tablica1"[Adresa]);ALLEXCEPT("Tablica1","Tablica1"[Grad],"Tablica1"[TT naziv]))

Kao rezultat toga, mogli smo odabrati sumnjive zapise u kojima je bilo više od 1 adrese na istoj točki u jednom gradu.

Naravno, u procesu proučavanja (pregledom drugih formula) postaje jasno da je DAX u PowerPivotu puno moćniji nego što je prikazano u ovoj temi, ali definitivno nije moguće odjednom shvatiti neizmjernost.

Nadam se da je bilo zanimljivo.
Nastavak članka