Beneficiile Pivot by Data Model

Când construim un tabel pivot în Excel, în prima casetă de dialog, în care ni se cere să setăm intervalul inițial și să alegem un loc pentru a insera tabelul pivot, mai jos există o casetă de selectare discretă, dar foarte importantă - Adăugați aceste date la Modelul de date (Adăugați aceste date la modelul de date) și, puțin mai sus, comutatorul Utilizați modelul de date al acestei cărți (Utilizați modelul de date al acestui registru de lucru):

Din păcate, mulți utilizatori care sunt familiarizați cu tabelele pivot de mult timp și le folosesc cu succes în munca lor, uneori, nu înțeleg cu adevărat sensul acestor opțiuni și nu le folosesc niciodată. Și degeaba. La urma urmei, crearea unui tabel pivot pentru modelul de date ne oferă câteva avantaje foarte importante în comparație cu tabelul pivot clasic Excel.

Cu toate acestea, înainte de a lua în considerare aceste „chile” de aproape, să înțelegem mai întâi ce este, de fapt, acest model de date?

Ce este un model de date

Model de date (abreviat ca MD sau DM = Data Model) este o zonă specială din interiorul unui fișier Excel în care puteți stoca date tabulare – unul sau mai multe tabele legate, dacă doriți, între ele. De fapt, aceasta este o bază de date mică (cub OLAP) încorporată într-un registru de lucru Excel. În comparație cu stocarea clasică a datelor sub formă de tabele obișnuite (sau inteligente) pe foile Excel, Modelul de date are câteva avantaje semnificative:

• Mesele pot fi de până la 2 miliarde de linii, iar o foaie Excel poate încăpea puțin mai mult de 1 milion.
• În ciuda dimensiunii gigantice, procesarea unor astfel de tabele (filtrare, sortare, calcule pe ele, rezumatul clădirii etc.) este efectuată foarte rapid Mult mai rapid decât Excel în sine.
• Cu datele din Model, puteți efectua calcule suplimentare (dacă doriți, foarte complexe) folosind limbaj DAX încorporat.
  
• Toate informațiile încărcate în modelul de date sunt foarte puternic comprimat folosind un arhivator special încorporat și crește destul de moderat dimensiunea fișierului Excel original.
  

Modelul este gestionat și calculat printr-un add-in special încorporat în Microsoft Excel - powerpivotdespre care am scris deja. Pentru a-l activa, pe fila dezvoltator clic Suplimente COM (Dezvoltator — Suplimente COM) și bifați caseta corespunzătoare:

Dacă file dezvoltator (Dezvoltator)nu o poți vedea pe panglică, o poți porni prin Fișier – Opțiuni – Configurare panglică (Fișier — Opțiuni — Personalizare panglică). Dacă în fereastra afișată mai sus în lista de programe de completare COM nu aveți Power Pivot, atunci acesta nu este inclus în versiunea dvs. de Microsoft Office 🙁

Pe fila Power Pivot care apare, va apărea un buton mare, verde deschis Management (Administra), făcând clic pe care se va deschide fereastra Power Pivot din partea de sus a Excel, unde vom vedea conținutul Modelului de date al cărții curente:

O notă importantă pe parcurs: un registru de lucru Excel poate conține doar un model de date.

Încărcați tabele în modelul de date

Pentru a încărca date în Model, mai întâi transformăm tabelul într-o scurtătură dinamică „inteligentă” de la tastatură Ctrl+T și dă-i un nume prietenos pe filă Constructor (Proiecta). Acesta este un pas necesar.

Apoi puteți folosi oricare dintre cele trei metode din care să alegeți:

• apasa butonul Adaugă la model (Adăugați la modelul de date) fila powerpivot fila Acasă (Acasă).
  
• Alegerea echipelor Insert – PivotTable (Inserați — Tabel Pivot) și activați caseta de selectare Adăugați aceste date la Modelul de date (Adăugați aceste date la modelul de date). În acest caz, conform datelor încărcate în Model, se construiește imediat și un tabel pivot.
  
• În fila Avansat Date (Data) faceți clic pe buton Din masă/gamă (De la masă/gamă)pentru a încărca tabelul nostru în editorul Power Query. Această cale este cea mai lungă, dar, dacă se dorește, aici puteți efectua curățare suplimentară a datelor, editare și tot felul de transformări, în care Power Query este foarte puternic.

  Apoi datele pieptănate sunt încărcate în Model prin comandă Acasă — Închideți și încărcați — Închideți și încărcați în... (Acasă — Închide&Încărcare — Închide&Încărcare la...). În fereastra care se deschide, selectați opțiunea Doar creați o conexiune (Creați doar conexiune) și, cel mai important, puneți o căpușă Adăugați aceste date la Modelul de date (Adăugați aceste date la modelul de date).
  

Construim un rezumat al modelului de date

Pentru a construi un model de date rezumat, puteți utiliza oricare dintre cele trei abordări:

• apasa butonul tabel rezumativ (Masă rotativă) în fereastra Power Pivot.
• Selectați comenzi în Excel Insert – PivotTable și comutați în modul Utilizați modelul de date al acestei cărți (Inserați — Tabel Pivot — Folosiți modelul de date al acestui registru de lucru).
• Alegerea echipelor Insert – PivotTable (Inserați — Tabel Pivot) și activați caseta de selectare Adăugați aceste date la Modelul de date (Adăugați aceste date la modelul de date). Tabelul „inteligent” actual va fi încărcat în Model și va fi construit un tabel rezumat pentru întregul Model.

Acum că ne-am dat seama cum să încărcăm datele în modelul de date și să construim un rezumat pe el, haideți să explorăm beneficiile și avantajele pe care ni le oferă acest lucru.

Beneficiul 1: Relații între tabele fără a utiliza formule

Un rezumat obișnuit poate fi construit numai folosind date dintr-un tabel sursă. Dacă aveți mai multe dintre ele, de exemplu, vânzări, lista de prețuri, directorul clienților, registrul de contracte etc., atunci va trebui mai întâi să colectați date din toate tabelele într-un singur, folosind funcții precum CĂUTARE V. (CĂUTARE V), INDEX (INDEX), MAI EXPUS (MECI), SUMMESLIMN (SUMIFS) și altele asemenea. Acest lucru este lung, plictisitor și vă duce Excel într-un „gând” cu o cantitate mare de date.

În cazul unui rezumat al Modelului de date, totul este mult mai simplu. Este suficient să configurați relații între tabele o dată în fereastra Power Pivot – și este gata. Pentru a face acest lucru, pe fila powerpivot apasa butonul Management (Administra) iar apoi în fereastra care apare – butonul Vizualizarea graficului (Vizualizare diagramă). Rămâne să trageți nume de coloane (câmpuri) comune (cheie) între tabele pentru a crea legături:

După aceea, în rezumatul pentru Modelul de date, puteți arunca în zona de rezumat (rânduri, coloane, filtre, valori) orice câmpuri din orice tabel aferente - totul va fi legat și calculat automat:

Beneficiul 2: Numărați valorile unice

Un tabel pivot obișnuit ne oferă posibilitatea de a alege una dintre mai multe funcții de calcul încorporate: sumă, medie, numărare, minim, maxim etc. În rezumatul modelului de date, la această listă standard este adăugată o funcție foarte utilă pentru a număra numărul de unice (valori nerepetate). Cu ajutorul acestuia, de exemplu, puteți număra cu ușurință numărul de articole unice de mărfuri (gamă) pe care le vindem în fiecare oraș.

Faceți clic dreapta pe câmpul – comandă Opțiuni pentru câmpul de valoare iar pe filă Ziua Operației Alege Numărul de elemente diferite (număr distinct):

Beneficiul 3: Formule DAX personalizate

Uneori trebuie să efectuați diferite calcule suplimentare în tabelele pivot. În rezumatele obișnuite, acest lucru se face folosind câmpuri și obiecte calculate, în timp ce rezumatul modelului de date utilizează măsuri într-un limbaj DAX special (DAX = expresii de analiză a datelor).

Pentru a crea o măsură, selectați din filă powerpivot Comandă Măsuri – Creați măsură (Măsuri — măsură nouă) sau faceți clic dreapta pe tabel din lista Câmpuri pivot și selectați Adăugați măsura (Adăugați măsura) în meniul contextual:

În fereastra care se deschide, setați:

• Numele tabeluluiunde va fi stocată măsura creată.
• Numele măsurării – orice nume pe care îl înțelegeți pentru noul câmp.
• Descriere – optional.
• Formulă – cel mai important lucru, pentru că aici fie intrăm manual, fie facem clic pe buton fx și selectați o funcție DAX din listă, care ar trebui să calculeze rezultatul atunci când aruncăm măsura noastră în zona Valori.
• În partea de jos a ferestrei, puteți seta imediat formatul numeric pentru măsura din listă Categorii.
  

Limbajul DAX nu este întotdeauna ușor de înțeles deoarece operează nu cu valori individuale, ci cu coloane și tabele întregi, adică necesită o oarecare restructurare a gândirii după formulele clasice Excel. Cu toate acestea, merită, deoarece puterea capacităților sale de procesare a unor cantități mari de date este dificil de supraestimat.

Beneficiul 4: Ierarhii de câmpuri personalizate

Adesea, atunci când creați rapoarte standard, trebuie să aruncați aceleași combinații de câmpuri în tabele pivot într-o anumită secvență, de exemplu An-Sfert-Lună-Ziu, Sau Categorie-Produs, Sau Țara-Oraș-Client etc. În rezumatul modelului de date, această problemă este ușor de rezolvat prin crearea propriei dvs ierarhii — seturi de câmpuri personalizate.

În fereastra Power Pivot, comutați la modul diagramă cu butonul Vizualizarea graficului fila Acasă (Acasă — Vizualizare diagramă), selectați cu Ctrl câmpurile dorite și faceți clic dreapta pe ele. Meniul contextual va conține comanda Creați ierarhie (Creează ierarhie):

Ierarhia creată poate fi redenumită și trasă în ea cu mouse-ul câmpurile necesare, astfel încât mai târziu, într-o singură mișcare, să poată fi aruncate în rezumat:

Beneficiul 5: Șabloane personalizate

Continuând ideea paragrafului anterior, în rezumatul Modelului de date, vă puteți crea și propriile seturi de elemente pentru fiecare câmp. De exemplu, din întreaga listă de orașe, puteți face cu ușurință un set doar din cele care se află în zona dvs. de responsabilitate. Sau colectați doar clienții dvs., bunurile dvs. etc. într-un set special.

Pentru a face acest lucru, pe fila Analiza tabelului pivot în lista derulantă Câmpuri, articole și seturi există comenzi corespunzătoare (Analiza - Fields, Items & Sets — Creați un set pe baza elementelor rând/coloană):

În fereastra care se deschide, puteți elimina, adăuga sau modifica în mod selectiv poziția oricăror elemente și puteți salva setul rezultat sub un nou nume:

Toate seturile create vor fi afișate în panoul Câmpuri PivotTable într-un folder separat, de unde pot fi trase liber în zonele de rânduri și coloane ale oricărui nou Tabel Pivot:

Beneficiul 6: Ascundeți selectiv tabelele și coloanele

Deși acesta este un avantaj mic, dar foarte plăcut în unele cazuri. Făcând clic dreapta pe numele câmpului sau pe fila tabel din fereastra Power Pivot, puteți selecta comanda Ascundeți din setul de instrumente pentru client (Ascundeți din Instrumentele client):

Coloana sau tabelul ascuns vor dispărea din panoul Lista câmpurilor tabel pivot. Este foarte convenabil dacă trebuie să ascundeți de utilizator unele coloane auxiliare (de exemplu, calculate sau coloane cu valori cheie pentru crearea de relații) sau chiar tabele întregi.

Beneficiul 7. Detaliere avansată

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

În rezumatul modelului de date, acest instrument la îndemână funcționează mai subtil. Stând pe orice celulă cu rezultatul care ne interesează, puteți da clic pe pictograma cu o lupă care apare lângă ea (se numește Exprimați tendințele) și apoi selectați orice câmp care vă interesează din orice tabel asociat:

După aceea, valoarea curentă (Model = Explorer) va intra în zona de filtrare, iar rezumatul va fi construit de birouri:

Desigur, o astfel de procedură poate fi repetată de mai multe ori, aprofundând în mod constant în datele dvs. în direcția de care sunteți interesat.

Beneficiul 8: Convertiți funcțiile Pivot în Cub

Dacă selectați orice celulă din rezumat pentru modelul de date și apoi selectați din filă Analiza tabelului pivot Comandă Instrumente OLAP – Convertiți în formule (Analiza — Instrumente OLAP — Conversie în formule), atunci întregul rezumat va fi convertit automat în formule. Acum, valorile câmpului din zona rând-coloană și rezultatele din zona valorii vor fi preluate din modelul de date folosind funcțiile speciale ale cubului: CUBEVALUE și CUBEMEMBER:

Din punct de vedere tehnic, asta înseamnă că acum nu avem de-a face cu un rezumat, ci cu mai multe celule cu formule, adică putem face cu ușurință orice transformări cu raportul nostru care nu sunt disponibile în rezumat, de exemplu, inserăm rânduri sau coloane noi în mijloc. a raportului, faceți orice calcule suplimentare în interiorul rezumatului, aranjați-le în orice mod dorit etc.

În același timp, legătura cu datele sursă, desigur, rămâne și în viitor aceste formule vor fi actualizate când sursele se vor schimba. Frumusetea!

• Analiză plan-fapt într-un tabel pivot cu Power Pivot și Power Query
• Tabel pivot cu antet multilinie
• Creați o bază de date în Excel folosind Power Pivot