Agregările sunt o modalitate de a restrânge, a rezuma sau a grupa datele. Atunci când începeți cu date brute din tabele sau din alte surse de date, datele sunt de obicei plate, ceea ce înseamnă că există multe detalii, dar nu au fost organizate sau grupate în niciun fel. Lipsa rezumatelor sau structurii ar putea îngreuna descoperirea modelelor de date. O parte importantă a modelării de date este definirea agregării care simplifică, abstractă sau rezumă modele ca răspuns la o anumită întrebare de afaceri.
Cele mai comune agregări, cum ar fi cele care utilizează AVERAGE, COUNT,DISTINCTCOUNT, MAX,MINsau SUM pot fi create într-o măsură automată, utilizând Însumare automată. Alte tipuri de agregări, cum ar fi AVERAGEX,COUNTX,COUNTROWSsau SUMX,returnează un tabel și necesită o formulă creată utilizând DAX (Data Analysis Expressions).
Înțelegerea agregării în Power Pivot
Alegerea grupurilor pentru agregare
Când agregă date, grupează datele după atribute cum ar fi produs, preț, regiune sau dată, 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 o proporție din acest an față de anul anterior și le prezentați ca procente, este un alt tip de agregare.
Decizia de grupare a datelor este guvernată de întrebarea de afaceri. De exemplu, agregările pot răspunde la următoarele întrebări:
Contorizează Câte tranzacții au existat într-o lună?
Medii Care au fost vânzările medie din această lună, după vânzător?
Valorile minime și maxime Care dintre districtele 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 contorat sau de însumat, iar datele numerice trebuie să fie corelate într-un mod cu grupurile pe care le veți utiliza pentru a organiza rezultatele.
Dacă datele nu conțin deja valori pe care le puteți utiliza pentru grupare, cum ar fi o categorie de produse sau numele regiunii geografice în care se află magazinul, este util să introduceți grupuri pentru datele dvs., adăugând categorii. Când construiți grupuri în Excel, trebuie să tastați sau să selectați manual grupurile pe care doriți să le utilizați din coloanele din foaia de lucru. Cu toate acestea, într-un sistem relațional, ierarhiile, cum ar fi categoriile pentru produse, sunt stocate deseori în alt tabel decât tabelul de date sau valori. De obicei, tabelul de categorii este legat la date după un anumit tip de cheie. De exemplu, să presupunem că descoperiți că datele conțin URI-uri de produs, dar nu numele produselor sau categoriile lor. Pentru a adăuga categoria la o foaie Excel de lucru, trebuie să copiați în coloana care conține numele de categorii. Cu Power Pivot, puteți să importați tabelul categorie de produse în modelul dvs. de date, să creați o relație între tabelul cu datele despre 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, agregarea cuvântului este utilizată ca sinonim pentru operațiunile matematice sau statistice utilizate în agregări, cum ar fi sume, medii, minime sau contorizare. Cu toate acestea, Power Pivot vă permite să creați formule particularizate pentru agregare, pe lângă agregările standard găsite atât în Power Pivot, cât și Excel.
De exemplu, date fiind același set de valori și grupări care s-au utilizat în exemplele anterioare, puteți crea agregări particularizate care răspund la următoarele întrebări:
Număr de filtrați Câte tranzacții au existat într-o lună, excluzând fereastra de întreținere de la sfârșitul lunii?
Rapoarte care utilizează medii în timp Care a fost creșterea procentuală sau refuzarea vânzărilor comparativ cu aceeași perioadă a anului trecut?
Valorile minime și maxime grupate Ce categorii de vânzări au fost ierarhizate ca fiind de top 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 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, cum ar fi sume, contori sau medii. Puteți crea agregări particularizate în Power Pivot din fereastra Power Pivot sau în zona Excel PivotTable.
-
Într-o coloană calculată,puteți să creați agregări care să ia în considerare contextul curent de rând pentru a regăsi rândurile asociate din alt tabel, apoi să însumați, să contorați sau să faceți media acelor valori 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 creând o formulă. De asemenea, puteți să creați măsuri implicite utilizând agregări standard într-un raport PivotTable 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 a raportului PivotTable pentru a grupa datele. Apoi glisați câmpurile care conțin valori numerice în zona de valori, astfel încât să poată fi numărate, medii sau însumate.
Dacă adăugați categorii într-un raport PivotTable, dar datele de categorie nu sunt legate de date de fapt, este posibil să obțineți o eroare sau rezultate excelente. De Power Pivot vor încerca să corecteze problema, detectând și sugerând automat relații. Pentru mai multe informații, consultați Lucrul cu relațiile în rapoarte 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 în mod interactiv rezultatele într-un raport PivotTable.
Lucrul cu grupările într-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 valorifică relațiile respective pentru a căuta valori asociate.
Cu alte cuvinte, dacă doriți să creați o formulă care grupează valorile după o categorie, mai întâi utilizaț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 să creați formule care utilizează căutare, consultați Căutare în formule 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 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 coloanele calculate, cât și în s.
De exemplu, în noile funcții de agregare DAX, în loc să specificați valori peste care să se însumeze sau să contoreze, 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 pentru toate valorile din coloana specificată a tabelului. Cu toate acestea, în DAX puteți crea un filtru dinamic sau static pe tabel, astfel încât agregarea să funcționeze pe un alt subset de date, în funcție de condiția de filtrare și de contextul curent.
Prin combinarea condițiilor și a filtrelor în 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ânduri și de titlurile de coloană dintr-un raport PivotTable.
Pentru mai multe informații, consultați Filtrarea datelor în formule.
Comparație între funcțiile Excel de agregare și funcțiile de agregare DAX
Următorul tabel listează unele dintre funcțiile de agregare standard furnizate de Excel și furnizează linkuri pentru 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 gestionare a anumitor tipuri de date.
Funcții de agregare standard
|
Funcție |
Utilizați |
|
Returnează valoarea medie (media aritmetică) a tuturor numerelor din coloană. |
|
|
Returnează valoarea medie (media aritmetică) a tuturor valorilor dintr-o coloană. Gestionează valori text și non-numerice. |
|
|
Contorizează numărul de valori numerice dintr-o coloană. |
|
|
Contorizează valorile dintr-o coloană care nu sunt goale. |
|
|
Returnează cea mai mare valoare numerică într-o coloană. |
|
|
Returnează cea mai mare valoare dintr-un set de expresii evaluate într-un tabel. |
|
|
Returnează cea mai mică valoare numerică într-o coloană. |
|
|
Returnează cea mai mică valoare dintr-un set de expresii evaluate într-un tabel. |
|
|
Adună toate numerele de pe coloană. |
Funcții de agregare DAX
DAX include funcții de agregare care vă permit să specificați un tabel asupra căruia se va efectua agregarea. Prin urmare, în loc să adăugați sau să faceți o medie a valorilor dintr-o coloană, aceste funcții vă permit să creați o expresie care definește dinamic datele pentru agrega.
Următorul tabel listează funcțiile de agregare disponibile în DAX.
|
Funcție |
Utilizați |
|
Face media unui set de expresii evaluate peste un tabel. |
|
|
Contorizează un set de expresii evaluate peste un tabel. |
|
|
Contorizează numărul de valori necompletate dintr-o coloană. |
|
|
Contorizează numărul total de rânduri dintr-un tabel. |
|
|
Contorizează rândurile returnate dintr-o funcție de tabel imbricat, cum ar fi funcția de filtrare. |
|
|
Returnează suma unui set de expresii evaluate într-un tabel. |
Diferențele dintre funcțiile DAX Excel de agregare
Deși aceste funcții au aceleași nume ca corespondentele Excel, utilizează motorul de analiză în memorie Power Pivot și au fost rescrise pentru a lucra cu tabele și coloane. Nu puteți utiliza o formulă DAX într-un registru Excel lucru și invers. Acestea pot fi utilizate numai în fereastra de Power Pivot și în rapoartele PivotTable care se bazează Power Pivot date. De asemenea, deși funcțiile au nume identice, comportamentul poate fi ușor diferit. Pentru mai multe informații, consultați subiectele individuale de referință pentru funcții.
Modul în care sunt evaluate coloanele într-o agregare este, de asemenea, diferit de modul Excel a gestiona agregările. Un exemplu poate fi de ajutor pentru ilustrare.
Să presupunem că doriți să obțineți o sumă de valori din coloana Volum din tabelul Vânzări, astfel încât să creați formula următoare:
=SUM('Sales'[Amount])
În cel mai simplu caz, funcția primește valorile dintr-o singură coloană nefiltrată, iar rezultatul este la fel ca în Excel, care adună întotdeauna valorile din coloană, Volum. Cu toate acestea, Power Pivot, formula este interpretată ca "Obțineți valoarea în Volum pentru fiecare rând al tabelului Vânzări, apoi adunați acele valori individuale. Power Pivot evaluează fiecare rând asupra căruia se efectuează agregarea și calculează o singură valoare scalară pentru fiecare rând, apoi efectuează o agregare a acelor valori. Prin urmare, rezultatul unei formule poate fi diferit dacă s-au aplicat filtre la un tabel sau dacă valorile sunt calculate pe baza altor agregări ce 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 funcții time intelligence predefinite. Aceste funcții utilizează zone de date pentru a obține valori asociate și agrega valorile. De asemenea, puteți compara valorile din intervalele de date.
Următorul tabel listează funcțiile time intelligence care pot fi utilizate pentru agregare.
|
Funcție |
Utilizați |
|
Calculează o valoare la sfârșitul calendarului perioadei date. |
|
|
Calculează o valoare la sfârșitul perioadei calendaristice anterioare perioadei date. |
|
|
Calculează o valoare peste intervalul care începe în prima zi a perioadei și se termină la cea mai recentă dată în coloana de date specificată. |
Alte funcții din secțiunea funcției Time Intelligence (Funcțiile Time Intelligence) sunt funcții care pot fi utilizate pentru a regăsi date sau intervale particularizate de date de utilizat în agregare. De exemplu, puteți să utilizați funcția DATESINPERIOD pentru a returna o zonă de date și să utilizați acel set de date ca argument al altei funcții pentru a calcula o agregare particularizată numai pentru acele date.
