Agregările sunt o modalitate de restrângere, sintetizare sau grupare a datelor. Când începeți cu date brute din tabele sau alte surse de date, datele sunt adesea plate, ceea ce înseamnă că există o mulțime de detalii, dar nu au fost organizate sau grupate în vreun fel. Această lipsă de rezumate sau de structură poate îngreuna descoperirea modelelor din date. O parte importantă a modelării datelor este definirea agregărilor care simplifică, abstractizează sau rezumă modele ca răspuns la o anumită întrebare de afaceri.
Agregările cele mai comune, cum ar fi cele care utilizează AVERAGE,COUNT,DISTINCTCOUNT, MAX, MIN sau SUM pot fi create automat într-o măsură utilizând Însumare automată. Alte tipuri de agregări, cum ar fi AVERAGEX, COUNTX, COUNTROWS sau SUMX returnează un tabel și necesită o formulă creată utilizând Data Analysis Expressions (DAX).
Înțelegerea agregărilor în Power Pivot
Alegerea grupurilor pentru agregare
Atunci când agregați date, le grupați după atributele, cum ar fi produsul, prețul, regiunea sau data, apoi definiți o formulă care funcționează pentru toate datele din grup. De exemplu, atunci când creați un total pentru un an, creați o agregare. Dacă creați apoi un raport al anului anterior față de anul precedent și îl prezentați ca procente, este un alt tip de agregare.
Decizia modului de grupare a datelor este determinată de întrebarea de business. De exemplu, agregările pot răspunde la următoarele întrebări:
Contorizează Câte tranzacții au fost într-o lună?
Medii Care au fost vânzările medii în această lună, după vânzător?
Valorile minime și maxime Care districte de vânzări au fost primele cinci în ceea ce privește unitățile vândute?
Pentru a crea un calcul care să răspundă la aceste întrebări, trebuie să aveți date detaliate care conțin numerele de contorizat sau de adunat și acele date numerice trebuie să fie corelate într-un fel sau altul cu grupurile pe care le veți utiliza pentru a organiza rezultatele.
Dacă datele nu conțin deja valori pe care să le utilizați pentru grupare, cum ar fi o categorie de produse sau numele regiunii geografice în care se află magazinul, se recomandă să introduceți grupuri la date adăugând categorii. Când construiți grupuri în Excel, trebuie să tastați manual sau să selectați grupurile pe care doriți să le utilizați dintre coloanele din foaia de lucru. Cu toate acestea, într-un sistem relațional, ierarhiile precum categoriile pentru produse sunt stocate adesea într-un alt tabel decât tabelul realitate sau valoare. De obicei, tabelul de categorii este legat la datele de informații printr-un fel de cheie. De exemplu, să presupunem că descoperiți că datele dvs. conțin ID-uri de produse, dar nu și numele produselor sau categoriile lor. Pentru a adăuga categoria într-o foaie de lucru Excel simplă, ar trebui să copiați coloana care conține numele categoriilor. Cu Power Pivot, puteți să importați tabelul de categorii de produse în modelul de date, să creați o relație între tabel cu datele de numere și lista de categorii de produse, apoi să utilizați categoriile pentru a grupa datele. Pentru mai multe informații, consultați Crearea unei relații între tabele.
Alegerea unei funcții pentru agregare
După ce ați identificat și ați adăugat grupările de utilizat, trebuie să decideți ce funcții matematice să utilizați pentru agregare. Adesea cuvântul agregare este utilizat ca sinonim pentru operațiile matematice sau statistice care sunt utilizate în agregări, cum ar fi sume, medii, minim sau contorizare. Totuși, Power Pivot vă permite să creați formule particularizate pentru agregare, în plus față de agregările standard din Power Pivot și Excel.
De exemplu, având același set de valori și grupări care au fost utilizate în exemplele anterioare, puteți crea agregări particularizate care răspund la următoarele întrebări:
Numere filtrate Câte tranzacții au fost într-o lună, excluzând fereastra de întreținere de la sfârșitul lunii?
Rapoarte utilizând medii în timp Care a fost creșterea procentuală sau scăderea vânzărilor comparativ cu aceeași perioadă a anului trecut?
Valorile minime și maxime grupate Care districte de vânzări s-au clasat pe primul loc pentru fiecare categorie de produse sau pentru fiecare promoție de vânzări?
Adăugarea de agregări la formule și rapoarte PivotTable
Când aveți o idee generală despre cum ar trebui grupate datele pentru a fi semnificative și despre valorile cu care doriți să lucrați, puteți decide dacă să construiți un raport PivotTable sau să creați calcule într-un tabel. Power Pivot extinde și îmbunătățește capacitatea nativă a Excel de a crea agregări precum sume, contoare sau medii. Puteți crea agregări particularizate în Power Pivot fie în fereastra Power Pivot, fie în zona PivotTable Excel.
- Într-o coloană calculată, puteți să creați agregări care iau în considerare contextul rândului curent pentru a regăsi rândurile asociate din alt tabel, apoi să însumați, să contorizați sau să faceți media valorilor din rândurile asociate.
- Într-o măsură, puteți crea agregări dinamice care utilizează atât filtre definite în formulă, cât și filtre impuse de proiectarea raportului PivotTable și de selecția de slicere, titluri de coloană și titluri de rând. Măsurile care utilizează agregări standard pot fi create în Power Pivot utilizând Însumare automată sau prin crearea unei formule. De asemenea, puteți crea măsuri implicite utilizând agregări standard într-un raport PivotTable din Excel.
Adăugarea grupărilor la un raport PivotTable
Când proiectați un raport PivotTable, glisați câmpuri care reprezintă grupări, categorii sau ierarhii în secțiunea de coloane și rânduri din raportul PivotTable, pentru a grupa datele. Apoi glisați câmpurile care conțin valori numerice în zona de valori, astfel încât să se poată număra, calcula media sau aduna.
Dacă adăugați categorii la un raport PivotTable, dar datele categoriei nu sunt corelate cu datele despre facturi, este posibil să primiți o eroare sau rezultate particulare. De obicei, Power Pivot va încerca să corecteze problema, detectând automat și sugerând relații. Pentru mai multe informații, consultați Lucrul cu relațiile în rapoartele PivotTable.
De asemenea, puteți glisa câmpuri în slicere, pentru a selecta anumite grupuri de date pentru vizualizare. Slicerele vă permit să grupați, să sortați și să filtrați interactiv rezultatele într-un raport PivotTable.
Lucrul cu grupările dintr-o formulă
De asemenea, puteți utiliza grupări și categorii pentru a agrega datele stocate în tabele, creând relații între tabele, apoi creând formule care utilizează acele relații pentru a căuta valori asociate.
Cu alte cuvinte, dacă doriți să creați o formulă care grupează valorile după o categorie, utilizați mai întâi o relație pentru a conecta tabelul care conține datele de detalii și tabelele care conțin categoriile, apoi construiți formula.
Pentru mai multe informații despre cum se creează formule care utilizează căutări, consultați Căutările în formulele Power Pivot.
Utilizarea filtrelor în agregări
O caracteristică nouă din Power Pivot este capacitatea de a aplica filtre la coloane și tabele de date, nu doar în interfața de utilizator și într-un PivotTable sau într-o diagramă, ci și în formulele pe care le utilizați pentru a calcula agregări. Filtrele pot fi utilizate în formule atât în coloane calculate, cât și în s.
De exemplu, în noile funcții de agregare DAX, în loc să specificați valorile peste care să adunați sau să contorizați, puteți specifica un întreg tabel ca argument. Dacă nu ați aplica niciun filtru la acel tabel, funcția de agregare ar funcționa în raport cu toate valorile din coloana specificată a tabelului. Totuși, în DAX puteți crea un filtru dinamic sau static în tabel, astfel încât agregarea să opereze în raport cu un alt subset de date, în funcție de condiția filtrului și de contextul curent.
Prin combinarea condițiilor și filtrelor din formule, puteți crea agregări care se modifică în funcție de valorile furnizate în formule sau care se modifică în funcție de selecția de titluri de rând și de titluri de coloană într-un raport PivotTable.
Pentru mai multe informații, consultați Filtrarea datelor în formule.
Comparație între funcțiile de agregare Excel și funcțiile de agregare DAX
Următorul tabel listează unele dintre funcțiile de agregare standard furnizate de Excel și oferă linkuri către implementarea acestor funcții în Power Pivot. Versiunea DAX a acestor funcții se comportă cam la fel ca versiunea Excel, cu unele diferențe minore de sintaxă și de tratare a anumitor tipuri de date.
Funcții de agregare Standard
| Funcție | Utilizați |
|---|---|
| AVERAGE | Returnează valoarea medie (media aritmetică) a tuturor numerelor dintr-o coloană. |
| AVERAGEA | Returnează valoarea medie (media aritmetică) a tuturor valorilor dintr-o coloană. Gestionează textul și valorile non-numerice. |
| COUNT | Contorizează numărul de valori numerice dintr-o coloană. |
| COUNTA | Contorizează valorile dintr-o coloană care nu sunt goale. |
| MAX | Returnează cea mai mare valoare numerică dintr-o coloană. |
| MAXX | Returnează cea mai mare valoare dintr-un set de expresii evaluate într-un tabel. |
| MIN | Returnează cea mai mică valoare numerică dintr-o coloană. |
| MINX | Returnează cea mai mică valoare dintr-un set de expresii evaluate într-un tabel. |
| SUM | Adună toate numerele dintr-o coloană. |
Funcții de agregare DAX
DAX include funcții de agregare care vă permit să specificați un tabel peste care va fi efectuată agregarea. Prin urmare, în loc să adăugați sau să calculați media valorilor dintr-o coloană, aceste funcții vă permit să creați o expresie care definește dinamic datele de agregat.
Următorul tabel listează funcțiile de agregare disponibile în DAX.
| Funcție | Utilizați |
|---|---|
| AVERAGEX | Calculează media unui set de expresii evaluate într-un tabel. |
| COUNTAX | Contorizează un set de expresii evaluate într-un tabel. |
| COUNTBLANK | Contorizează numărul de valori necompletate dintr-o coloană. |
| COUNTX | Contorizează numărul total de rânduri dintr-un tabel. |
| COUNTROWS | Contorizează rândurile returnate de o funcție de tabel imbricată, cum ar fi funcția de filtrare. |
| SUMX | Returnează suma unui set de expresii evaluate într-un tabel. |
Diferențele dintre funcțiile de agregare DAX și Excel
Deși aceste funcții au aceleași nume ca și cele pentru Excel, ele utilizează motorul de analiză în memorie Power Pivot și au fost rescrise pentru a funcționa cu tabele și coloane. Nu puteți utiliza o formulă DAX într-un registru de lucru Excel și invers. Acestea pot fi utilizate numai în fereastra Power Pivot și în rapoartele PivotTable bazate pe date Power Pivot. De asemenea, deși funcțiile au nume identice, comportamentul poate fi ușor diferit. Pentru mai multe informații, consultați subiectele de referință pentru funcțiile individuale.
Modul în care sunt evaluate coloanele dintr-o agregare este, de asemenea, diferit de modul în care Excel gestionează agregările. Un exemplu vă poate ajuta să ilustrați.
Să presupunem că doriți să obțineți o sumă a valorilor din coloana Cantitate din tabelul Vânzări, atunci creați următoarea formulă:
=SUM('Sales'[Amount])
În cel mai simplu caz, funcția obține valorile dintr-o singură coloană nefiltrată, iar rezultatul este același ca în Excel, care adună întotdeauna valorile din coloana Volum. Cu toate acestea, în Power Pivot, formula este interpretată ca "Obțineți valoarea în Cantitate pentru fiecare rând al tabelului Vânzări, apoi adunați acele valori individuale. Power Pivot evaluează fiecare rând pe care se efectuează agregarea și calculează o singură valoare scalară pentru fiecare rând, apoi efectuează o agregare a acestor valori. Prin urmare, rezultatul unei formule poate fi diferit dacă s-au aplicat filtre într-un tabel sau dacă valorile sunt calculate pe baza altor agregări care pot fi filtrate. Pentru mai multe informații, consultați Contextul în formulele DAX.
Funcțiile DAX Time Intelligence
În plus față de funcțiile de agregare a tabelelor descrise în secțiunea anterioară, DAX are funcții de agregare care funcționează cu datele și orele specificate, pentru a oferi informații despre timp încorporate. Aceste funcții utilizează intervale de date pentru a obține valori asociate și a agrega valorile. De asemenea, puteți compara valorile din intervale de date.
Următorul tabel listează funcțiile time intelligence care pot fi utilizate pentru agregare.
| Funcție | Utilizați |
|---|---|
|
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR |
Calculează o valoare la sfârșitul calendaristic al perioadei date. |
|
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR |
Calculează o valoare la sfârșitul calendaristic al perioadei anterioare perioadei date. |
|
TOTALMTD TOTALYTD TOTALQTD |
Calculează o valoare pentru intervalul care începe în prima zi a perioadei și se termină la data cea mai recentă din coloana de date specificată. |
Celelalte funcții din secțiunea funcțiilor Time Intelligence (funcții Time Intelligence) sunt funcții care pot fi utilizate pentru a regăsi date sau intervale particularizate de date care să fie utilizate în agregare. De exemplu, puteți utiliza funcția DATESINPERIOD pentru a returna un interval de date și acel set de date ca argument pentru altă funcție pentru a calcula o agregare particularizată doar pentru acele date.