Tutorial Power Pivot pentru Excel: Cele mai bune cazuri de utilizare și exemple

Rezumat

Descărcați setul de date aici pentru a-l urma împreună cu tutorialul.

Hainele noi ale împăratului: tutorial Power Pivot

În diferitele domenii și sub-domenii care cuprind finanțe, analiză financiară, piețe financiare și investiții financiare, Microsoft Excel este rege. Odată cu apariția și creșterea exponențială a datelor mari, cu toate acestea, determinate de zeci de ani de agregare și acumulare de date, apariția stocării ieftine în cloud și apariția internetului lucrurilor - adică comerțul electronic, rețelele sociale și interconectarea dispozitivelor - Excel. funcționalitățile și capacitățile vechi au fost împinse la limitele lor.

Mai precis, infrastructura Excel de generație mai veche și limitările de procesare, cum ar fi limita de rânduri de 1.048.576 de rânduri, sau încetinirea inevitabilă a procesării în ceea ce privește seturile mari de date, tabelele de date și foile de calcul interconectate, au redus capacitatea sa de utilizare ca instrument eficient de date mari. Cu toate acestea, în 2010, Microsoft a adăugat o nouă dimensiune la Excel, numită Power Pivot. Power Pivot a oferit ultimă generație de business intelligence și funcționalitate de analiză de afaceri pentru Excel prin capacitatea sa de a extrage, combina și analiza seturi de date aproape nelimitate fără a afecta viteza de procesare. Cu toate acestea, în ciuda lansării sale în urmă cu opt ani, majoritatea analiștilor financiari încă nu știu cum să folosească Power Pivot și mulți nici nu știu că există.

În acest articol, vă voi arăta cum să utilizați Power Pivot pentru a depăși problemele obișnuite ale Excel și vă voi arunca o privire asupra avantajelor cheie suplimentare ale software-ului folosind câteva exemple. Acest tutorial Power Pivot este menit să servească drept ghid pentru ceea ce puteți realiza cu acest instrument și, la sfârșit, va explora câteva exemple de cazuri de utilizare în care Power Pivot se dovedește adesea neprețuit.

Ce este Power Pivot și de ce este util?

Power Pivot este o caracteristică Microsoft Excel care a fost introdusă ca supliment pentru Excel 2010 și 2013 și este acum o caracteristică nativă pentru Excel 2016 și 365. După cum explică Microsoft, Power Pivot pentru Excel „vă permite să importați milioane de rânduri de date din mai multe surse de date într-un singur registru de lucru Excel, creați relații între date eterogene, creați coloane calculate și măsuri folosind formule, construiți tabele pivot și diagrame pivot și apoi analizați în continuare datele, astfel încât să puteți lua decizii de afaceri în timp util, fără a necesita asistență IT. ”

Limbajul de expresie principal pe care Microsoft îl folosește în Power Pivot este DAX (Expresii de analiză a datelor), deși altele pot fi folosite în situații specifice. Din nou, după cum explică Microsoft, „DAX este o colecție de funcții, operatori și constante care pot fi utilizate într-o formulă sau expresie pentru a calcula și returna una sau mai multe valori. Mai simplu spus, DAX vă ajută să creați informații noi din datele aflate deja în modelul dvs..” Din fericire pentru cei deja familiarizați cu Excel, formulele DAX vor părea familiare, deoarece multe dintre formule au o sintaxă similară (de exemplu, SUM , AVERAGE , TRUNC ).

Pentru claritate, beneficiile cheie ale utilizării Power Pivot față de Excel de bază pot fi rezumate după cum urmează:

• Vă permite să importați și să manipulați sute de milioane de rânduri de date în care Excel are o constrângere strictă de puțin peste un milion de rânduri.
• Vă permite să importați date din mai multe surse într-un singur registru de lucru fără a fi nevoie să creați mai multe foi sursă care suferă de probleme de control al versiunii și transferabilitate.
• Vă permite să manipulați datele importate, să le analizați și să trageți concluzii fără a încetini computerul într-un ritm de melc.
• Vă permite să vizualizați datele cu PivotCharts și Power BI.

În secțiunile următoare, voi trece prin fiecare dintre cele de mai sus și vă voi arăta cum poate fi util Power Pivot pentru Excel.

Cum se utilizează Power Pivot

1) Importul seturi mari de date

După cum sa menționat anterior, una dintre limitările majore ale Excel se referă la lucrul cu seturi de date extrem de mari. Din fericire pentru noi, Excel se poate încărca acum cu mult peste limita de un milion de rânduri direct în Power Pivot.

Pentru a demonstra acest lucru, am generat un set de date eșantion de vânzări în valoare de doi ani pentru un comerciant cu amănuntul de articole sportive cu nouă categorii diferite de produse și patru regiuni. Setul de date rezultat este de două milioane de rânduri.

Folosind fila Date de pe panglică, am creat o interogare nouă din fișierul CSV (consultați Crearea unei interogări noi de mai jos). Această funcționalitate se numea PowerQuery, dar începând cu Excel 2016 și 365, era mai strâns integrată în fila Date din Excel.

Crearea unei noi interogări

De la un registru de lucru gol în Excel până la încărcarea tuturor celor două milioane de rânduri în Power Pivot, a durat aproximativ un minut! Observați că am reușit să efectuez o formatare ușoară a datelor promovând primul rând să devină numele coloanelor. În ultimii câțiva ani, funcționalitatea Power Query s-a îmbunătățit considerabil de la un program de completare Excel la o parte strâns integrată a filei Date din bara de instrumente. Power Query poate pivota, aplatiza, curăța și modela datele dvs. prin suita sa de opțiuni și propriul limbaj, M.

2) Importarea datelor din mai multe surse

Unul dintre celelalte beneficii cheie ale Power Pivot pentru Excel este capacitatea de a importa cu ușurință date din mai multe surse. Anterior, mulți dintre noi au creat mai multe foi de lucru pentru diversele noastre surse de date. Adesea, acest proces implica scrierea codului VBA și copierea/lipirea din aceste surse disparate. Din fericire pentru noi, totuși, Power Pivot vă permite să importați date din diferite surse de date direct în Excel fără a fi nevoie să întâlniți problemele menționate mai sus.

Folosind funcția de interogare din expoziția 1, putem extrage din oricare dintre următoarele surse:

• Microsoft Azure
• SQL Server
• Teradata
• Facebook
• Forta de vanzare
• Fișiere JSON
• Caiete de lucru Excel
• …si multe altele

În plus, mai multe surse de date pot fi combinate fie în funcția de interogare, fie în fereastra Power Pivot pentru a integra datele. De exemplu, puteți extrage date despre costurile de producție dintr-un registru de lucru Excel și rezultatele vânzărilor reale de pe serverul SQL prin Interogarea în Power Pivot. De acolo, puteți combina cele două seturi de date potrivind numerele lotului de producție pentru a produce marje brute pe unitate.

3) Lucrul cu seturi mari de date

Un alt avantaj cheie al Power Pivot pentru Excel este capacitatea de a manipula și de a lucra cu seturi mari de date pentru a trage concluzii și analize relevante. Voi trece prin câteva exemple comune mai jos pentru a vă oferi o idee despre puterea instrumentului.

Măsuri

Adrogatorii de Excel vor fi, fără îndoială, de acord că PivotTables sunt atât una dintre cele mai utile și, în același timp, una dintre cele mai frustrante sarcini pe care le realizăm. Frustant mai ales când vine vorba de lucrul cu seturi de date mai mari. Din fericire, Power Pivot pentru Excel ne permite să creăm ușor și rapid tabele pivot atunci când lucrăm cu seturi mai mari de date.

În imaginea de mai jos, intitulată Crearea de măsuri , observați cum fereastra Power Pivot este separată în două panouri. Panoul de sus conține datele, iar panoul de jos găzduiește măsurile. O măsură este un calcul care este efectuat pe întregul set de date. Am introdus o măsură tastând în celula evidențiată.

 Total Sales:=SUM('Accounting Data'[Amount])

Aceasta creează o nouă măsură care se însumează în coloana Sumă. În mod similar, pot introduce o altă măsură în celula de mai jos

 Average Sales:=AVERAGE('Accounting Data'[Amount])

Crearea de măsuri

De acolo, urmăriți cât de repede este să creați un PivotTable familiar pe un set de date mare.

Crearea unui tabel pivot

Tabelele de dimensiuni

În calitate de analiști financiari care folosesc Excel, devenim adepți în utilizarea formulelor complicate pentru a adapta tehnologia la voința noastră. VLOOKUP , SUMIF și chiar temutul INDEX(MATCH()) . Cu toate acestea, folosind Power Pivot, putem arunca o mare parte din asta pe fereastră.

Adăugarea unui tabel creat de utilizator la un model Power Pivot

Pentru a demonstra această funcționalitate, am creat un mic tabel de referință în care am atribuit fiecare categorie unui tip. Alegând „Adăugați la modelul de date”, acest tabel este încărcat în Power Pivot (consultați Adăugarea unui tabel creat de utilizator la un model Power Pivot de mai sus).

De asemenea, am creat un tabel de date pentru a-l folosi cu setul nostru de date (consultați Crearea unui tabel de date de mai jos). Power Pivot pentru Excel facilitează crearea rapidă a unui tabel de date, pentru a se consolida pe luni, trimestre și zile ale săptămânii. De asemenea, utilizatorul poate crea un tabel de date mai personalizat pentru a fi analizat în funcție de săptămâni, ani fiscali sau orice grupări specifice organizației.

Crearea unui tabel de date

Coloane calculate

Pe lângă măsuri, există un alt tip de calcul: coloane calculate. Utilizatorii Excel vor fi confortabil să scrie aceste formule, deoarece sunt foarte asemănătoare cu scrierea formulelor în tabelele de date. Am creat o nouă coloană calculată mai jos (consultați Crearea unei coloane calculate de mai jos) care sortează tabelul de date contabile după sumă. Vânzările sub 50 USD sunt etichetate „Mici”, iar toate celelalte sunt etichetate „Mare”. Formula nu pare intuitivă?

Crearea unei coloane calculate

Relații

Putem crea apoi o relație între câmpul Categorie din tabelul Date contabile și câmpul Categorie din tabelul Categorii folosind vizualizarea diagramă. În plus, putem defini o relație între câmpul Data vânzărilor din tabelul Date contabile și câmpul Data din tabelul Calendar.

Definirea relatiilor

Acum, fără a fi necesare funcții SUMIF sau VLOOKUP , putem crea un tabel pivot care a calculat vânzările totale pe an și tip, cu un slicer pentru dimensiunea tranzacției.

Tabel Pivot folosind relații

Sau, putem crea un grafic al vânzărilor medii pentru fiecare zi a săptămânii folosind noul tabel Calendar.

Grafic Pivot Folosind Relații

Deși acest grafic pare simplu, este impresionant că a durat mai puțin de zece secunde pentru a crea o consolidare pe două milioane de rânduri de date, fără a adăuga o nouă coloană la datele de vânzări.

Deși putem realiza toate aceste scenarii de raportare consolidată, putem oricând să analizăm elementele rând individuale. Reținem datele noastre extrem de granulare.

Funcții avansate

Până acum, majoritatea analizelor pe care le-am arătat sunt calcule relativ simple. Acum, vreau să demonstrez unele dintre capabilitățile mai avansate ale acestei platforme.

Inteligența timpului

Adesea, atunci când examinăm rezultatele financiare, dorim să le comparăm cu un interval de timp comparabil din anul precedent. Power Pivot are câteva funcții de inteligență temporală încorporate.

 Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date])) YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())

De exemplu, adăugarea a doar două măsuri de mai sus la tabelul de date contabile din Power Pivot îmi permite să produc următorul tabel pivot în câteva clicuri.

Time Intelligence PivotTable

Granularități nepotrivite

Ca analist financiar, o problemă pe care trebuie să o rezolv adesea este cea a granularităților nepotrivite. În exemplul nostru, datele de vânzări reale sunt afișate la nivel de categorie, dar haideți să pregătim un buget care este doar la nivel sezonier. Pentru a spori această nepotrivire, vom pregăti un buget trimestrial, chiar dacă datele de vânzări sunt zilnice.

Granularități nepotrivite – Tabelul bugetului

Cu Power Pivot pentru Excel, această inconsecvență este ușor de rezolvat. Prin crearea a două tabele de referință suplimentare sau tabele de dimensiuni în nomenclatura bazei de date, acum putem crea relațiile adecvate pentru a analiza vânzările noastre reale în raport cu sumele bugetate.

Granularități nepotrivite – Relații

În Excel, următorul tabel pivot se adună rapid.

Granularități nepotrivite – Buget vs. Rezultate reale

În plus, putem defini noi măsuri care calculează diferența dintre vânzările reale și vânzările bugetate, după cum urmează:

 Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1

Folosind această măsură, putem arăta varianța pe un PivotTable.

Granularități nepotrivite – Rezultate variații

Procent din total

În cele din urmă, să examinăm vânzările dintr-o anumită categorie ca procent din toate vânzările (de exemplu, contribuția categoriei la vânzările totale) și vânzările dintr-o anumită categorie ca procent din toate vânzările de același tip (de exemplu, contribuția categoriei la vânzările de tip sezonier). vânzări). Am creat cele două măsuri de mai jos:

 Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data')) Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))

Aceste măsuri pot fi acum implementate într-un nou PivotTable:

Procent din total

Observați cum sunt efectuate calculele atât la nivel de categorie, cât și de tip sezonier. Îmi place cât de repede și fără efort sunt efectuate aceste calcule pe un set de date atât de mare. Acestea sunt doar câteva exemple ale eleganței și puterii de calcul a Power Pivot.

Comprimare

Un alt avantaj este că dimensiunea fișierelor se micșorează. Dimensiunea originală a fișierului era de 91 MB, iar acum este sub 4 MB. Aceasta este o compresie de 96% din fișierul original.

Dimensiunile fișierelor

Cum se întâmplă asta? Power Pivot folosește motorul xVelocity pentru a comprima datele. În termeni simpli, datele sunt stocate mai degrabă în coloane decât în ​​rânduri. Această metodă de stocare permite computerului să comprima valori duplicate. În setul nostru de date exemplu, există doar patru regiuni care se repetă pe toate cele două milioane de rânduri. Power Pivot pentru Excel poate stoca mai eficient aceste date. Rezultatul este că, pentru datele care au multe valori care se repetă, costă mult mai puțin stocarea acestor date.

Un lucru de remarcat este că am folosit sume întregi în dolari în acest set de date eșantion. Dacă aș fi inclus două puncte zecimale pentru a reflecta cenți, efectul de compresie s-ar diminua la 80% încă impresionant din dimensiunea fișierului original.

SSAS Tabular

Modelele Power Pivot pot fi, de asemenea, scalabile pentru întreaga întreprindere. Să presupunem că construiți un model Power Pivot care începe să câștige mulți utilizatori în organizație sau datele cresc la zece milioane de rânduri sau ambele. În acest moment, este posibil să nu doriți ca treizeci de utilizatori diferiți să actualizeze modelul sau să facă modificări. Modelul poate fi convertit fără probleme în SSAS Tabular. Toate tabelele și relațiile sunt păstrate, dar acum puteți controla frecvența de reîmprospătare, puteți atribui roluri (de exemplu, numai citire, citire și procesare) diverșilor utilizatori și implementați doar un mic front-end Excel care se conectează la modelul tabelar. Rezultatul este că utilizatorii dvs. ar putea accesa modelul tabelar implementat cu un mic registru de lucru, dar nu au acces la formule și măsuri.

4) Vizualizarea și analiza datelor

Formule CUBE

Una dintre solicitările constante ale clienților mei este să creez raportări care să respecte un aspect strict definit. Am clienți care solicită lățimi de coloane specifice, coduri de culoare RGB și nume și dimensiuni de fonturi predefinite. Luați în considerare următorul tablou de bord:

Formule CUBE încorporate

Cum populăm numerele de vânzări fără a genera tabele pivot dacă toate vânzările noastre sunt găzduite cu Power Pivot pentru Excel? Folosind formule CUBE! Putem scrie formule CUBE în orice celulă Excel și va efectua calculul folosind modelul Power Pivot pe care l-am construit deja.

De exemplu, următoarea formulă este introdusă în celulă de sub „Vânzări totale 2016:”

=CUBEVALUE(" Acest WorkbookDataModel "," [Măsuri].[Vânzări totale] "," [Calendar].[Anul].[2016] ")

Prima parte a formulei, evidențiată cu galben, se referă la numele modelului Power Pivot. În general, este de obicei ThisWorkbookDataModel pentru versiunile mai noi de Power Pivot pentru Excel. Porțiunea în verde definește faptul că dorim să folosim măsura Vânzări totale. Partea în albastru indică Excel să filtreze numai rândurile care au o dată de vânzare cu un an egal cu 2016.

În culise, Power Pivot a construit un cub de procesare analitică online (OLAP) cu date, coloane calculate și măsuri. Acest design permite utilizatorului Excel să acceseze apoi datele prin preluarea directă cu funcțiile CUBE. Folosind formulele CUBE, am reușit să construiesc situații financiare complete care se conformează planurilor predefinite. Această capacitate este unul dintre punctele importante ale utilizării Power Pivot pentru Excel pentru analiza financiară.

Power BI

Un alt avantaj al Power Pivot pentru Excel este că puteți lua rapid orice registru de lucru Power Pivot pe care îl construiți și îl puteți converti rapid într-un model Power BI. Importând registrul de lucru Excel direct în aplicația Power BI Desktop sau Power BI Online, puteți analiza, vizualiza și partaja datele cu oricine din organizația dvs. În esență, Power BI este Power Pivot, PowerQuery și SharePoint, toate reunite într-unul singur. Mai jos, am creat un tablou de bord importând registrul de lucru Power Pivot pentru Excel anterior în aplicația desktop Power BI. Observați cât de interactivă este interfața:

Power BI

Un lucru grozav despre Power BI este Întrebările și răspunsurile în limbaj natural. Pentru a demonstra, am încărcat modelul Power BI în contul meu online Power BI. De pe site, pot pune întrebări și Power BI construiește analiza adecvată pe măsură ce scriu:

Întrebări și răspunsuri în limbaj natural

Acest tip de abilitate de interogare permite utilizatorului să pună întrebări despre modelul de date și să interacționeze cu datele într-un mod mai ușor decât în ​​Excel.

Un alt beneficiu al Power BI este că dezvoltatorii de la Microsoft lansează în mod constant actualizări ale acestuia. Noile funcții, multe solicitate de utilizator, sunt lansate lunar. Cel mai bine, este o tranziție fără probleme de la Power Pivot pentru Excel. Deci, timpul pe care l-ați investit în învățarea formulelor DAX poate fi implementat în Power BI! Pentru analistul care trebuie să-și împărtășească analiza multor utilizatori pe diferite dispozitive, Power BI poate merita explorat.

Cele mai bune practici

Odată ce ați început, există câteva bune practici pe care ar trebui să le urmați.

Primul este să decideți cu grijă ce să importați în primul rând. Veți folosi vreodată adresa de domiciliu a vânzătorului? Trebuie să cunosc adresa de e-mail a clientului meu în contextul acestui registru de lucru? Dacă scopul este de a agrega datele într-un tablou de bord, atunci unele dintre datele disponibile nu vor fi necesare pentru acele calcule. Petrecerea timpului cu gestionarea datelor care vin va atenua foarte mult problemele și utilizarea memoriei mai târziu, când setul de date se va extinde.

O altă practică bună este să vă amintiți că Power Pivot nu este Excel. În Excel, suntem obișnuiți să creăm calcule prin extinderea constantă a foilor de lucru spre dreapta. Power Pivot pentru Excel procesează cel mai eficient datele dacă limităm această dorință de destin manifest. În loc să creați continuu coloane calculate în partea dreaptă a datelor, învățați să scrieți măsuri în panoul de jos. Acest obicei va asigura dimensiuni mai mici ale fișierelor și calcule mai rapide.

În cele din urmă, aș sugera să folosiți nume simple în engleză pentru măsuri. Mi-a luat mult timp să îl adopt pe acesta. Mi-am petrecut primii ani inventând nume precum SumExpPctTotal , dar odată ce alți oameni au început să folosească aceleași registre de lucru, am avut multe de făcut. Acum, când încep un nou registru de lucru, folosesc nume de măsură, cum ar fi Expense Line Item as Percent of Total Expenses . Deși numele este mai lung, este mult mai ușor de utilizat pentru altcineva.

Cazuri de utilizare din lumea reală

În acest articol am prezentat doar câteva dintre modalitățile prin care Power Pivot pentru Excel vă permite să faceți un pas important dincolo de Excel-ul simplu. M-am gândit că ar fi util să evidențiez câteva cazuri de utilizare din lumea reală în care am găsit că Power Pivot pentru Excel este extrem de util.

Aici sunt câteva:

• Analizați performanța unui portofoliu mare de active pe diferite intervale de timp: deoarece Power Pivot pentru Excel ne permite să definim măsuri care compară o perioadă de timp cu una anterioară, putem avea rapid trimestru peste trimestru, an peste an, și performanța de la o lună la lună, totul în mod continuu, scriind doar câteva măsuri.
• Rezumați datele contabile folosind niveluri de agregare personalizate: prin identificarea fiecărui element rând din registrul general după nume, categorie și situație financiară, se pot crea rapid rapoarte care includ elementele rând adecvate.
• Lanțurile pot identifica vânzările la același magazin: folosind un tabel care mapează când magazinele intră online, rezultatele financiare pot fi comparate pe baza aceluiași magazin.
• Identificați persoanele cu performanțe superioare și sub performanțe în vânzări: pot fi create tabele pivot care evidențiază primele cinci SKU-uri și cele mai de jos cinci SKU-uri după vânzări, marje brute, intervale de timp de producție etc.
• Comercianții cu amănuntul pot defini tabele calendaristice care utilizează o configurație 4-4-5: folosind un tabel personalizat de date, un comerciant cu amănuntul poate atribui cu ușurință fiecare zi unei anumite luni 4-4-5, apoi rezultatele zilnice ale vânzărilor pot fi transferate în luna corespunzătoare.

De la foile de calcul grele la cărțile de lucru moderne

În calitate de analiști financiari, ni se cere să efectuăm calcule complexe pe seturi de date în continuă expansiune. Deoarece Excel este deja instrumentul analitic implicit, curba de învățare Power Pivot este ușoară, iar multe dintre funcții oglindesc funcțiile native ale Excel.

Prin utilizarea funcțiilor CUBE, Power Pivot pentru Excel se îmbină perfect în registrele de lucru Excel existente. Creșterea eficienței de calcul nu poate fi trecută cu vederea. Presupunând o viteză de procesare cu 20% mai mare, ceea ce este conservator, analistul financiar care petrece șase ore pe zi în Excel poate economisi 300 de ore pe an!

În plus, acum putem analiza seturi de date care sunt mult mai mari decât am putea anterior cu Excelul nostru tradițional. Cu modele proiectate eficient, putem avea cu ușurință de 10 ori cantitatea de date pe care ni le era permisă anterior în Excel tradițional, păstrând în același timp agilitatea analitică rapidă. Cu capacitatea de a converti modelele din Power Pivot în SSAS Tabular, cantitatea de date care poate fi procesată este de 100–1.000 de ori mai mare decât putem obține în Excel.

Capacitatea Power Pivot pentru Excel de a efectua calcule fulgerătoare pe cantități mari de date și de a păstra în continuare capacitatea de a se scufunda în detalii poate transforma analiza financiară din foi de calcul greoaie în registre de lucru moderne.

Dacă sunteți interesat să încercați Power Pivot pentru Excel, mai jos sunt câteva materiale utile pentru a începe.

Referințe și ghiduri utile

Collie, R. și Singh, A. (2016). Power Pivot și Power BI: Ghidul utilizatorului Excel pentru DAX, Power Query, Power BI și Power Pivot în Excel 2010-2016. Statele Unite ale Americii: Holy Macro! Cărți.

Ferrari, A. și Russo, M. (2015). Ghidul definitiv pentru DAX: Business intelligence cu Microsoft Excel, SQL Server Analysis Services și Power BI. Statele Unite ale Americii: Microsoft Press, SUA.