Power Pivot este un add-in introdus pentru prima dată în Excel 2010 și acum o parte de bază a Excelului modern. A schimbat modul în care putem lucra și manipula volume mari de date în Excel.

utilizează

În acest articol, nu vom răspunde doar la întrebarea ce este Power Pivot? Dar și de ce și cum să utilizați PowerPivot cu cazuri reale de utilizare a afacerii.

Ce este Power Pivot și de ce este util?

Deși o foaie de lucru Excel poate gestiona 1.048.576 rânduri de date. În realitate, se poate lupta pe măsură ce ajungeți la 100.000 sau chiar înainte, în funcție de ceea ce aveți în registrul de lucru.

Power Pivot ne permite să lucrăm cu date mari dincolo de limita de 1.048.576 și să producem în continuare registre de lucru mai mici, mai slabe și mai rapide decât un tabel pivot standard.

Face acest lucru încărcând datele în modelul de date intern al Excel și nu pe o foaie de lucru. Relațiile pot fi apoi create între diferitele tabele de date. Gata cu VLOOKUP-urile pentru a strânge datele într-o listă mare.

Putem crea tabele pivot pe baza acestui model pentru a analiza mai multe tabele de date.

De asemenea, puteți utiliza un limbaj de formulare puternic în Power Pivot numit DAX. Aceasta înseamnă expresii de analiză a datelor.

Limbajul DAX este vast și ne permite să efectuăm calcule mai complexe decât putem face cu un tabel pivot standard.

Deci, ce este Power Pivot? Este într-adevăr o combinație între utilizarea tabelelor pivot și a calculelor DAX cu modelul intern de date Excel pentru analiza datelor mari.

Vedeți acest scurt videoclip care explică de ce avem nevoie de Power Pivot:

Un caz de utilizare Power Pivot

Să vedem un exemplu de utilizare a afacerii pentru a vedea unde ne va ajuta Power Pivot și voi explica cum să folosim PowerPivot în acest caz.

Să ne imaginăm un scenariu în care exportăm date despre vânzări din baza noastră de date. Aceasta include un fișier CSV cu toate tranzacțiile de vânzare pentru o perioadă de timp specificată.

De asemenea, include un fișier CSV cu toți clienții noștri și detaliile acestora și unul cu toate detaliile produsului nostru.

Am dori să importăm aceste 3 fișiere într-un registru de lucru Excel pentru a le analiza și a găsi primele 5 produse vândute, precum și ce țări am primit peste 10 milioane de lire sterline.

Anterior, am fi importat fișierele în trei foi diferite și apoi am fi folosit VLOOKUP-uri pentru a extrage datele într-o listă mare pentru a fi utilizate într-un tabel pivot.

Dar cu Power Pivot, le vom importa direct în modelul de date pentru stocare eficientă. Apoi creați relații între tabele (în loc de mii de VLOOKUP-uri). Și efectuați analize cu un tabel pivot și DAX.

Cum se obține și se instalează programul de completare Power Pivot

În Excel 2013, 2016 și 365 Power Pivot este inclus ca parte a experienței native Excel. Va dura doar câteva secunde să îl instalați din programele de completare COM prima dată când doriți să îl utilizați.

Faceți clic pe Fișier> Opțiuni> Adăugare ins.

Selectați Suplimente COM din lista Gestionare și faceți clic pe Go.

Bifați caseta pentru Microsoft Power Pivot pentru Excel și faceți clic pe Ok.

Cum să importați fișiere CSV în modelul de date

Vom parcurge acum scenariul nostru de caz de utilizare.

Puteți descărca fișierele și le puteți urmări pentru o practică practică.

În primul rând avem nevoie de câteva date. Aceste date ar putea fi deja în Excel. Dar, de multe ori, dacă lucrați cu seturi de date mari, obțineți date dintr-o bază de date, un folder sau mai multe fișiere text/CSV.

Cel mai bun mod de a aduce aceste date în Excel este folosind Power Query. Power Query este un instrument încorporat în Excel pentru a simplifica importarea și transformarea datelor externe.

Power Pivot poate fi apoi utilizat pentru a modela și analiza aceste date. Și totul poate fi reîmprospătat cu un clic pe un buton în viitor.

Power Query nu intră în sfera acestui articol, dar iată un exemplu rapid de obținere a datelor noastre de vânzări din fișiere CSV. Voi începe cu fișierul sales.csv.

Faceți clic pe Date> Din text/CSV

Se va încărca fereastra Power Query Editor. Există o mulțime de instrumente pe care le putem folosi aici pentru a transforma datele.

Faceți clic pe Acasă> Închidere și încărcare săgeată listă> Închidere și încărcare în

Apare fereastra Import Data. Selectați Creați doar conexiune și bifați caseta pentru a adăuga aceste date la modelul de date.

Imaginea de mai jos prezintă fișierul sales.csv încărcat. Conține 106.693 de rânduri. Acesta este o mulțime de rânduri, dar veți vedea că nu are impact asupra performanței calculelor în Power Pivot.

Vizualizarea modelului de date în Power Pivot

Să aruncăm o privire asupra aspectului datelor în Power Pivot. Faceți clic pe butonul Gestionați din fila Power Pivot.

Este afișată fereastra Power Pivot pentru Excel.

Vizualizarea inițială către care sunteți direcționat se numește Vizualizare date. Tabelele de date sunt afișate pe diferite file, similar cu foile de lucru. Cu toate acestea, acesta este doar un afișaj și nu modul în care sunt stocate.

Pe lângă vizualizarea datelor, există și o vizualizare diagramă. Puteți accesa acest lucru făcând clic pe butonul Vizualizare diagramă din fila Acasă.

Aceasta oferă o vizualizare mai bună a modelului și este excelentă pentru vizualizarea relațiilor dintre tabelele pe care le vom crea.

Creați relații între tabele

Cu tabelele încărcate în model, vom crea acum relații între ele. Acest lucru ne va permite să creăm tabele pivot folosind datele din toate cele trei tabele.

Vizualizarea diagramei este cel mai simplu mod de a configura acest lucru. Să începem prin a aranja fereastra mai eficient.

Trageți tabelul Vânzări sub tabelele Produse și Clienți.

Tabelul Produse și tabelul Clienți conțin informații despre grupuri de obiecte care interacționează cu datele și sunt denumite „căutare” sau „tabele de dimensiuni”.

Vom crea două relații „unu la mulți”. Unul dintre tabelul Clienți și tabelul Vânzări și altul între tabelul Produse și tabelul Vânzări.

Acest lucru se datorează faptului că un client ar putea face una sau mai multe vânzări la noi. Și același lucru pentru produse. Un produs ar putea fi vândut o dată sau de multe ori.

Pentru a crea relația dintre tabele vom face clic și vom trage între câmpul ID client din tabelul Vânzări în câmpul ID din tabelul Clienți.

Imaginea de mai jos prezintă relațiile finalizate. Direcția de filtrare a datelor este afișată printr-o săgeată, iar simbolurile 1 și asterisc (*) sunt de asemenea afișate pentru a arăta tipul relației.

Creați un tabel pivot din modelul de date

Odată cu configurarea modelului de date, putem crea un tabel pivot.

Faceți clic pe Inserare> PivotTable.

Excel detectează automat modelul de date și sugerează crearea unui tabel pivot din acesta. Specificați dacă doriți tabelul pivot pe o foaie nouă sau existentă și faceți clic pe Ok.

Deci, ce este Power Pivot? Este un tabel pivot care utilizează date din modelul intern.

Trageți câmpul Nume produs din tabelul Produse în zona Rânduri. Apoi trageți câmpul Vânzări totale din tabelul Vânzări în zona Valori.

Aceasta va însuma suma totală pentru fiecare produs din datele noastre.

Putem apoi să sortăm lista de la cea mai mare la cea mai mică în funcție de vânzările totale. Faceți clic dreapta pe o celulă care conține un total de vânzări și selectați Sortare> De la cel mai mare la cel mai mic.

Utilizarea DAX pentru a crea măsuri

Să începem să aruncăm o privire asupra limbajului DAX pentru a efectua calcule în Power Pivot.

Există două tipuri de calcule DAX - Coloane calculate și Măsuri.

O coloană calculată este utilizată pentru a crea coloane suplimentare în modelul dvs. de date. Și aceste coloane pot fi apoi utilizate ca etichete în rândurile, coloanele și zonele Slicer ale unui tabel pivot.

Este încurajat să creați aceste coloane în datele originale sau în Power Query în loc de model, dacă este posibil. Aceste coloane pot fi cu adevărat utile pentru o detaliere ulterioară a datelor noastre, cum ar fi gruparea datelor în etichetele zilei de săptămână și de weekend.

În acest articol, vom crea celălalt tip de calcul DAX - numit Măsură. Măsurile sunt calcule care sunt trase în zona valorilor unui tabel pivot, cum ar fi Suma și Media.

Limbajul DAX este imens, depășind cu mult Suma și Media standard. Deci, crearea acestora în model oferă mult mai multă putere decât în ​​cadrul unui tabel pivot standard și măsuri implicite.

Măsurile create cu DAX pot fi, de asemenea, utilizate de mai multe ori și în mai multe tabele pivot (dar calculate doar o dată). Acest lucru îmbunătățește viteza de procesare. De asemenea, puteți atribui un format unei măsuri, astfel încât să nu fie nevoie să le formatați de fiecare dată când sunt utilizate.

Vom crea o măsură pentru a însuma câmpul Vânzări totale din tabelul Vânzări.

Faceți clic pe fila Power Pivot> Măsuri> Măsură nouă.

Apare fereastra Măsurare.

  1. Selectați tabelul din lista în care doriți să fie stocată noua măsură. Această măsură va fi stocată în tabelul Vânzări.
  2. Introduceți un nume pentru măsură. Această măsură se numește Suma vânzărilor.
  3. Puteți introduce o descriere pentru o măsură. Mai ales dacă este complex. Aici este omis, deoarece numele servește și rolului respectiv.
  4. Introduceți următoarea formulă în caseta furnizată: = SUM (Vânzări [Vânzări totale])
  5. Faceți clic pe butonul Verificați formula. Veți primi apoi confirmarea că nu există erori în formulă.
  6. Selectați o monedă din categoria de formatare. Apoi selectați simbolul valutar necesar și câte zecimale doriți să afișați. Faceți clic pe Ok.

Utilizarea unei măsuri într-un tabel pivot

Cu măsura creată o putem folosi în tabelele noastre pivot pentru analiză.

Folosind tabelul pivot pe care l-am creat mai devreme în tutorial, putem elimina măsura implicită Suma vânzărilor totale.

Noua noastră măsură este afișată în lista câmpurilor de tabel și poate fi trasă în zona Valori ca înlocuitor.

Introduceți un nou tabel pivot ca înainte și trageți câmpul Țară din tabelul Clienți în rânduri și măsura Suma vânzărilor din tabelul Vânzări în valori.

Sortați valorile de la cele mai mari la cele mai mici și apoi faceți clic pe butonul de filtrare, Filtre valoare> Mai mare decât.

Introduceți 10000000 în câmp și faceți clic pe Ok.

În acest articol, am răspuns la întrebarea ce este Power Pivot și am demonstrat două cazuri de utilizare a afacerii cu privire la modul de utilizare a PowerPivot pe parcursul întregului proces.

Am importat datele în model, am creat relații și o măsură, apoi le-am folosit în tabelele pivot.

Power Pivot este una dintre cele mai bune îmbunătățiri ale modului în care utilizăm Excel. Este un instrument extrem de puternic și acest articol este o introducere în ceea ce este capabil. Vă încurajez să învățați și să vă dezvoltați și mai mult abilitățile Power Pivot.

Alan este MVP Microsoft Excel, instructor și consultant Excel. În majoritatea zilelor, el poate fi găsit într-o sală de clasă, răspândindu-și dragostea și cunoștințele despre Excel. Când nu se află într-o clasă, scrie și predă online prin bloguri, YouTube și podcast-uri. Alan locuiește în Marea Britanie, este tată a doi copii și un alergător dornic.